Web Design that taps into the haromny and vision of your dreams.

Connecting to a SQL Server Database with C#

How to connect to SQL Server from C#

Written By on in C#

840 words, estimated reading time 4 minutes.

ADO.Net makes connections to SQL databases very easy. Visual Studio ships with SQL Express for development, but can only be used on the local machine. SQL Server and SQL Express are essentially the same.

Active Data Objects (ADO.Net) Series
  1. What are Active Data Objects?
  2. Connecting to a SQL Server Database with C#
  3. Consuming Data in Windows Forms C# Applications
  4. Using ADO.Net Controls in ASP.Net Pages
  5. Modifying Data and Updating Databases with C#
  6. Connecting to MySql Databases with C# / ASP.Net
  7. Reading Excel Spreadsheets with C# ADO.Net
  8. Creating SQL Server Stored Procedures in C#
  9. Calling SQL Server Stored Procedures from C#
  10. Importing and Exporting XML from a DataSet

In this short example, we create the objects necessary to connect to a SQL database, query a table, retrieve the results and display them. You may have to change the database path depending on the location, and contents, of your SQL Express sample databases. Sample databases for SQL Express can be downloaded from the Microsoft Website.

I have used the "@" symbol to denote a verbatim string, a string that is taken as is. The only character that is interpreted by the compiler is the double quote (") as this marks the end of the string. If you want a double quote in a verbatim string you can use a double quote (as shown below).

Verbatim strings allow for multi-line strings and we do not have to escape characters such as backslash in file names. It also means that the connection strings are more readable when separated onto multiple lines.

Connected Mode

string connectionString = @"Data Source=.SQLEXPRESS;AttachDbFilename=""C:\SQL Server 2000\Sample Databases\NORTHWND.MDF"";Integrated Security=True;Connect Timeout=30;User Instance=True";
 
SqlConnection sqlCon = new SqlConnection(connectionString);
sqlCon.Open();
 
string commandString = "SELECT * FROM Customers";
SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
SqlDataReader dataReader = sqlCmd.ExecuteReader();
 
while (dataReader.Read())
{
  Console.WriteLine(String.Format("{0} {1}", dataReader["CompanyName"], dataReader["ContactName"]));
}
dataReader.Close();
sqlCon.Close();

As we have said previously, the SqlDataReader works in connected mode, so each call to SqlDataReader.Read will stream data from the server. It is also sequential, so once a record has been read, you can't access it again without executing the sqlCommand.ExecuteReader method again. The time between sqlCon.Open() and sqlCon.Close() there is a connection to the server. If your application opens the connection on start-up and doesn't close it until it exits you can cause the unnecessary record or table locks. The way around this is by using a DataSet which will download a local copy of a subset of the main database that can be manipulated and changes sent back to the server later on.

Disconnected Mode

The disconnected mode will execute a query on the server and return the results in a DataSet object. This DataSet can then be used to display data, modify, insert or delete records without a database connection. When all changes have been made, the changes are sent back to the server.

string connectionString = @"Data Source=.SQLEXPRESS;AttachDbFilename=""C:\SQL Server 2000\Sample Databases\NORTHWND.MDF"";Integrated Security=True;Connect Timeout=30;User Instance=True";
 
string commandString = "SELECT * FROM Customers";
 
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(commandString, connectionString);
 
da.Fill(ds,"Customers"); // Customers is the name of the DataTable
 
foreach (DataRow dr in ds.Tables[0].Rows)
{
  Console.WriteLine(String.Format("{0} {1}", dr[1].ToString(), dr[2].ToString())); 
}

Database Explorer

You can also use the Database Explorer to add data elements to an application. To show the database explorer, click on the view menu and select Database Explorer or press Ctrl+Alt+S. You can add a reference to a database by creating a new connection. The process is described in more detail in the tutorial which covers Data and Windows Forms.

From the Database Explorer you can, in most cases, just drag and drop tables to a form to create a data-bound grid view or control.

Updating in Connected Mode

The important thing to remember about the connected mode is that updates are processed as a command is executed, whereas using Disconnected methods allow the data to be previewed and further amended before being committed to the server.

Additional SQL commands must be created after the first reader has been closed, or you will have to create new objects.

string connectionString = @"Data Source=.SQLEXPRESS;AttachDbFilename=""C:\SQL Server 2000\Sample Databases\NORTHWND.MDF"";Integrated Security=True;Connect Timeout=30;User Instance=True";
 
SqlConnection sqlCon = new SqlConnection(connectionString);
sqlCon.Open();
 
string commandString = "SELECT * FROM Customers";
SqlCommand sqlCmd = new SqlCommand(commandString, sqlCon);
SqlDataReader dataReader = sqlCmd.ExecuteReader();
 
while (dataReader.Read())
{
  Console.WriteLine(String.Format("{0} {1}", dataReader["CompanyName"], dataReader["ContactName"]));
}
dataReader.Close();
 
commandString = "UPDATE Customers SET ContactName = 'Jane Doe' WHERE CustomerID = 'ALFKI'";
sqlCmd = new SqlCommand(commandString,sqlCon);
sqlCmd.ExecuteNonQuery();
 
sqlCon.Close();

Updating in Disconnected Mode

Disconnected updates are processed by manipulating the DataSet object. The Rows indexers access the record and the field.

ds.Tables[0].Rows[0][2] = "Jane Doe";

You will also need to specify and Update or Insert command to the data adaptor to instruct the server how it should update the table. Luckily there is a built-in tool to help with this called the SqlCommandBuilder.

SqlCommandBuilder myBuilder = new SqlCommandBuilder(da);
da.UpdateCommand = myBuilder.GetUpdateCommand();

Changes to the DataSet are not committed to the server until the Update method is called from the DataAdaptor.

da.Update(ds, "Customers");

In the next tutorial, we will look at how to use data in Windows Forms applications.

Last updated on: Friday 23rd June 2017

 

Comments
Shubendra Verma

Shubendra Verma

This is one of the best article so far I've read online. thanks for sharing with us. You made it very simple and understandable. Over the internet, I was some good article too which also explained very well on this topic, for more discussion on these post check out following links....

http://mindstick.com/Blog/50/Database%20Connection
http://social.msdn.microsoft.com/forums/en-US/sqltools/thread/a42fc25d-8fb1-4867-b9c8-f56923741205

At last! I would like to thanks everyone for yours valuable post.

Reply to Shubendra Verma
gold

gold

Which mode is good n appropriate to use connected or disconnect?

Reply to gold
chitranjan

chitranjan

use the following querry

SqlCommand command = new SqlCommand(”INSERT serviceStation ( siteID, codeID, stationName, address1, address2, town, county, postcode, telNo, siteTypeID, statusID, salesAreaID, areaID, brandID, typeID, roadTypeM, roadTypeJ, roadTypeA, roadtypeAB, openHours, costCentre, asset, deliveryPoint, deliveryDepotID, pamAreaID, isCurrent, nrvFitted, tanksDetailsID, pumpID, compressorID, cathodicProtectionID, siteCount, ERP, shell, telemetryInstalled, temperatureCompensationDtvcInstalled, cocaWwcUpgradeInstalled) VALUES ( @siteID, @codeID, @stationName, @address1, @address2, @town, @county, @postcode, @telNo, @siteTypeID, @statusID, @salesAreaID, @areaID, @brandID, @typeID, @roadTypeM, @roadTypeJ, @roadTypeA, @roadtypeAB, @openHours, @costCentre, @asset, @deliveryPoint, @deliveryDepotID, @pamAreaID, @isCurrent, @nrvFitted, @tanksDetailsID, @pumpID, @compressorID, @cathodicProtectionID, @siteCount, @ERP, @shell, @telemetryInstalled, @temperatureCompensationDtvcInstalled, @cocaWwcUpgradeInstalled)”, myDBSQLConnection);

i hope this probleb is short out

Reply to chitranjan
Stapes

Stapes

Updating in Disconnected Mode

I tried using this technique. However, a lot of extra bits are required that are not shown here.

The first time I ran:
dataAdaptor2.Update(dsServiceStation, "[serviceStation]");

I got the error:
Update requires a valid InsertCommand when passed DataRow collection with new rows.

So I added an insert command:

SqlCommand command = new SqlCommand("INSERT INTO serviceStation ( siteID, codeID, stationName, address1, address2, town, county, postcode, telNo, siteTypeID, statusID, salesAreaID, areaID, brandID, typeID, roadTypeM, roadTypeJ, roadTypeA, roadtypeAB, openHours, costCentre, asset, deliveryPoint, deliveryDepotID, pamAreaID, isCurrent, nrvFitted, tanksDetailsID, pumpID, compressorID, cathodicProtectionID, siteCount, ERP, shell, telemetryInstalled, temperatureCompensationDtvcInstalled, cocaWwcUpgradeInstalled) VALUES ( @siteID, @codeID, @stationName, @address1, @address2, @town, @county, @postcode, @telNo, @siteTypeID, @statusID, @salesAreaID, @areaID, @brandID, @typeID, @roadTypeM, @roadTypeJ, @roadTypeA, @roadtypeAB, @openHours, @costCentre, @asset, @deliveryPoint, @deliveryDepotID, @pamAreaID, @isCurrent, @nrvFitted, @tanksDetailsID, @pumpID, @compressorID, @cathodicProtectionID, @siteCount, @ERP, @shell, @telemetryInstalled, @temperatureCompensationDtvcInstalled, @cocaWwcUpgradeInstalled)", myDBSQLConnection);

dataAdaptor2.InsertCommand = command;

Now I get the error:

Must declare the scalar variable "@siteID".

How come I need to declare it, when it should be explicit in the command:

dataAdaptor2.Update(dsServiceStation, "[serviceStation]");

Reply to Stapes
Tim Trott

Tim Trott

Stapes,

You are correct in that a vital piece of information was missing, but you do not need to write the query statements yourself, you can use a SqlCommandBuilder instead.

I have updated the article to include this information, you should be able to substitute Update for Insert.

Hope that helps.

Reply to Tim Trott

 

Leave a Reply

Your email address will not be published.





If you find something abusive or that does not comply with our terms or guidelines please flag it as inappropriate.

Copyright © 2001-2018 Tim Trott, all rights reserved. Web Design by Azulia Designs

This web page is licensed for your personal, private, non-commercial use only.

Disclaimer, Privacy & LegalSitemapContact Me