| View previous topic :: View next topic |
| Author |
Message |
Stylo
Grandmaster Cheater Supreme
![]() Reputation: 3
Joined: 16 May 2007
Posts: 1073
Location: Israel
|
 Posted: Thu Mar 03, 2011 4:33 pm Post subject: Connecting to sql server via C# |
 |
|
How's that done?
All i ever done so far in databases connections was in MS Access,
Now I want to move to sql server, but in any connection example i can't figure out how to establish the connection
how should a connection string to a local database on my computer should look like?
Thanks.
_________________
Stylo |
|
| Back to top |
|
 |
AhMunRa
Grandmaster Cheater Supreme
![]() Reputation: 27
Joined: 06 Aug 2010
Posts: 1117
|
| Posted: Thu Mar 03, 2011 9:25 pm Post subject: |
|
|
http://www.codeproject.com/KB/database/sql_in_csharp.aspx
SqlConnection myConnection = new SqlConnection("user id=username;" +
"password=password;server=serverurl;" +
"Trusted_Connection=yes;" +
"database=database; " +
"connection timeout=30");
_________________
<Wiccaan> Bah that was supposed to say 'not saying its dead' lol. Fixing >.> |
|
| Back to top |
|
|
Stylo
Grandmaster Cheater Supreme
![]() Reputation: 3
Joined: 16 May 2007
Posts: 1073
Location: Israel
|
| Posted: Thu Mar 03, 2011 11:57 pm Post subject: |
|
|
Yea i'v seen this before only wasn't sure about windows authentication part.
I guess i missed the part that they say no user and pass needed in that option.
it is all working great thanks.
_________________
Stylo |
|
| Back to top |
|
|
atom0s
Moderator
 Reputation: 205
Joined: 25 Jan 2006
Posts: 8585
Location: 127.0.0.1
|
| Posted: Fri Mar 04, 2011 1:13 am Post subject: |
|
|
MySQL provides a binding that works flawlessly, which you can get here:
http://dev.mysql.com/downloads/connector/net/
You're better off using something made by the developers rather then a third-party class. (Just my opinion.) I personally use the connector for work, and have never had any issues with it either.
_________________
- Retired. |
|
| Back to top |
|
|
Stylo
Grandmaster Cheater Supreme
![]() Reputation: 3
Joined: 16 May 2007
Posts: 1073
Location: Israel
|
| Posted: Fri Mar 04, 2011 1:23 am Post subject: |
|
|
Is it for MSSQL too or just mysql?
I'm currently working with mssql
_________________
Stylo |
|
| Back to top |
|
|
AhMunRa
Grandmaster Cheater Supreme
![]() Reputation: 27
Joined: 06 Aug 2010
Posts: 1117
|
| Posted: Fri Mar 04, 2011 11:36 am Post subject: |
|
|
This is a project I did at work. There was nothing used other than what is included with Visual Studio. It connects to MSSQL.
Can send you a complete project if you would like to see it.
Form1.cs
| Code: | using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.IO;
using System.Configuration;
namespace SQL_Test
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
public void doDataTable()
{
/* BEGIN SECTION DON'T TOUCH ME
*
* Read my csv file and drop data from it into a datatable works great don't change
* yet
* Will need to make it so it can be based off the current file.
* Eventually data will not be dumped to a csv file but stored
* totally in a datatable for insertion into SQL
*
*/
OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\\6-2009\\; Extended Properties='text;HDR=yes;Format=Delimited';");
OleDbDataAdapter da = new OleDbDataAdapter("SELECT * FROM cc2.csv", con);
DataTable dt = new DataTable("cc2");
da.Fill(dt);
sqlFu(dt, "cc2");
// END DON'T TOUCH ME SECTION
}
private void sqlFu(DataTable dt, string Tbl)
{
string tblName = Tbl;
// Read my app settings SQL db, user, pass
string strCon = ConfigurationSettings.AppSettings["ConnectionString"];
string[] valueHolder = new String[dt.Columns.Count];
string[] columnHeader = new String[dt.Columns.Count];
string tblCols = "";
string valCols = "";
// counters
int cols = dt.Columns.Count;
int madea = dt.Rows.Count;
int x = 0;
// My SQL command that I want to execute, this will
SqlConnection SQLConn = new SqlConnection();
int max = madea / 100;
progressBar1.Maximum = 100;
progressBar1.Minimum = 0;
progressBar1.Step = 1;
try
{
SQLConn.ConnectionString = strCon;
SQLConn.Open();
}
catch (Exception Ex)
{
if (SQLConn != null)
{
SQLConn.Dispose();
}
string ErrorMessage = "An error occurred while trying to connect to the server.";
ErrorMessage += Environment.NewLine;
ErrorMessage += Environment.NewLine;
ErrorMessage += Ex.Message;
MessageBox.Show(this, ErrorMessage, "Connection Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
foreach (DataColumn col in dt.Columns)
{
tblCols = tblCols + col.ToString() + ",";
valCols = valCols + "@" + col.ToString() + ",";
columnHeader[x] = "@" + col.ToString();
x = x + 1;
}
string c = ",";
char comma = Convert.ToChar(c);
tblCols = tblCols.TrimEnd(comma);
valCols = valCols.TrimEnd(comma);
string sqlInsert = "INSERT INTO " + tblName + " ( " + tblCols + " ) VALUES ( " + valCols + " )";
int i = 0;
int j = 1;
foreach (DataRow rows in dt.Rows)
{
if (i == max)
{
if (j == progressBar1.Maximum - 1)
{
label1.Text = "100%";
}
else
{
progressBar1.PerformStep();
double xx = (progressBar1.Value * 100) / progressBar1.Maximum;
label1.Text = String.Format("{0}%", xx);
label1.Refresh();
j = j + 1;
}
i = 0;
}
else
{
i = i + 1;
}
SqlCommand cmdIns = new SqlCommand(sqlInsert, SQLConn);
for ( x = 0; x < cols; x++ )
{
cmdIns.Parameters.Add(columnHeader[x], SqlDbType.NVarChar);
valueHolder[x] = dt.Rows[dt.Rows.IndexOf(rows)][x].ToString();
cmdIns.Parameters[columnHeader[x]].Value = valueHolder[x];
}
cmdIns.ExecuteNonQuery();
cmdIns.Dispose();
cmdIns = null;
x = 0;
}
SQLConn.Close();
SQLConn.Dispose();
}
private void button1_Click(object sender, EventArgs e)
{
doDataTable();
}
private void button2_Click(object sender, EventArgs e)
{
Close();
}
private void label1_Click(object sender, EventArgs e)
{
}
private void progressBar1_Click(object sender, EventArgs e)
{
}
private void timer1_Tick(object sender, System.EventArgs e)
{
if (progressBar1.Value == progressBar1.Maximum)
{
timer1.Stop();
label1.Text = "100%";
return;
}
progressBar1.PerformStep();
double x = (progressBar1.Value * 100) / progressBar1.Maximum;
label1.Text = String.Format("{0}%", x);
}
private void Form1_Load(object sender, EventArgs e)
{
}
}
}
|
App.Config
| Code: | <?xml version="1.0" encoding="utf-8"?>
<configuration>
<appSettings>
<add key="NADA Parser" value="NADADB Config" />
<add key="ConnectionString" value="Server=<<<<<<SERVER NAME>>>>>>>;Database=<<<<<<<DATABASE>>>>>>>>;Integrated Security=false;User Id=<<<<<<<<USERNAME>>>>>>>;Password=<<<<<<<<<<<<<PASSWORD HERE>>>>>>>>>>>" />
<add key="ClientSettingsProvider.ServiceUri" value="" />
</appSettings>
<system.web>
<membership defaultProvider="ClientAuthenticationMembershipProvider">
<providers>
<add name="ClientAuthenticationMembershipProvider" type="System.Web.ClientServices.Providers.ClientFormsAuthenticationMembershipProvider, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" serviceUri="" />
</providers>
</membership>
<roleManager defaultProvider="ClientRoleProvider" enabled="true">
<providers>
<add name="ClientRoleProvider" type="System.Web.ClientServices.Providers.ClientRoleProvider, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" serviceUri="" cacheTimeout="86400" />
</providers>
</roleManager>
</system.web>
</configuration> |
I wrote this app to parse Nada Blue Book files and aggregate the data into DataTables for later manipulation by another app.
_________________
<Wiccaan> Bah that was supposed to say 'not saying its dead' lol. Fixing >.> |
|
| Back to top |
|
|
Stylo
Grandmaster Cheater Supreme
![]() Reputation: 3
Joined: 16 May 2007
Posts: 1073
Location: Israel
|
| Posted: Fri Mar 04, 2011 12:03 pm Post subject: |
|
|
Looks great, I'll take a look at it too,
Thanks.
_________________
Stylo |
|
| Back to top |
|
|
atom0s
Moderator
Reputation: 205
Joined: 25 Jan 2006
Posts: 8585
Location: 127.0.0.1
|
| Posted: Fri Mar 04, 2011 4:32 pm Post subject: |
|
|
| Stylo wrote: | Is it for MSSQL too or just mysql?
I'm currently working with mssql |
Ah over-read the MS Access in your first post. Stick to the internal .NET classes then for connecting to them.
_________________
- Retired. |
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
