C# SqlClient Tutorial: SqlConnection, SqlCommand

Use the SqlClient namespace to store data in SQL Server and read it back again.

SqlClient. A namespace, SqlClient interacts with SQL Server. It allows the development of data-driven applications. It creates database connections with SqlConnection. It inserts data with SqlCommand. And it handles rows with SqlDataReader.

Intro. This tutorial is based on the local computer, but its steps could be easily applied on the network simply by using a remote database connection string. You will need to use an SQL database.

So: In Visual Studio, please click on Project, Add New Data Source. Select Database and click New Connection.

Using database connection to SQL Server. In the Data Source box, select Microsoft SQL Server. This will hook up the SQL Database on your local machine. In the Server Name pulldown menu, select SQLExpress.

Tip: This is present on new installations of Visual Studio 2008 Professional. Newer versions also include this option.

Then, save the connection string in the next dialog. You can access this string through your program's settings, which are automatically generated and easy to use. You do not need to manually create a connection string in this tutorial.

Add namespaces. If your are working in a console program, you will only need three namespaces. You need to add the System.Data.SqlClient namespace manually. This will give you easy access to the SqlConnection, SqlCommand and other SQL classes.

Namespaces: using System; using System.Collections.Generic; using System.Data.SqlClient;

Create table. You cannot INSERT a new database table in SQL Server if one by that name already exists. If you do that, the program will throw an exception. And this exception will look like the following one.

Error: Unhandled Exception: System.Data.SqlClient.SqlException: There is already an object named... in the database.

Working around the SqlException. The simplest way to work around this problem is to simply wrap the SqlCommand in a try-catch block. This will tell the runtime to ignore the exception.

Note: For more complex programs, you will prefer other techniques. Here is the method that creates the Dogs1 table.

TryCreateTable: We see the method will create the Dogs1 table with three columns. It stores the Weight, Name and Breed of the dog.

Info: Weight is stored as an int, while the other two fields are text fields (strings).

ExecuteNonQuery: This method is ideal for when you are inserting data, not reading it or querying for a specific result.

C# program that uses SqlCommand /// <summary> /// This method attempts to create the Dogs1 SQL table. /// If will do nothing but print an error if the table already exists. /// </summary> static void TryCreateTable() { using (SqlConnection con = new SqlConnection( ConsoleApplication1.Properties.Settings.Default.masterConnectionString)) { con.Open(); try { using (SqlCommand command = new SqlCommand( "CREATE TABLE Dogs1 (Weight INT, Name TEXT, Breed TEXT)", con)) { command.ExecuteNonQuery(); } } catch { Console.WriteLine("Table not created."); } } }

Using statements. The using statement in the C# language is excellent for handling important system resources. If you do not clean of the SqlConnection and SqlCommands, your program will have catastrophic failures over time.Using

Add variables. We extract the code to insert objects into your SQL database into a method. If your program uses objects, this method could accept those objects. The method here allows Dogs1 data to be inserted by simply passing parameters to AddDog.

Parameter: Every SqlCommand instance has a Parameter collection, which is an SqlParameterCollection and accessed with the Parameters property.

Add: We use the Add instance method to add new parameters. In the Add method, we call the new SqlParameter constructor.

SqlParameter: This constructor receives 2 arguments. First is the name of the variable in your command text, and second is the value object.

C# program that uses SqlParameter /// <summary> /// Insert dog data into the SQL database table. /// </summary> /// <param name="weight">The weight of the dog.</param> /// <param name="name">The name of the dog.</param> /// <param name="breed">The breed of the dog.</param> static void AddDog(int weight, string name, string breed) { using (SqlConnection con = new SqlConnection( ConsoleApplication1.Properties.Settings.Default.masterConnectionString)) { con.Open(); try { using (SqlCommand command = new SqlCommand( "INSERT INTO Dogs1 VALUES(@Weight, @Name, @Breed)", con)) { command.Parameters.Add(new SqlParameter("Weight", weight)); command.Parameters.Add(new SqlParameter("Name", name)); command.Parameters.Add(new SqlParameter("Breed", breed)); command.ExecuteNonQuery(); } } catch { Console.WriteLine("Count not insert."); } } }

Object parameters. In the C# language, every variable inherits from System.Object, so when you need an object parameter as in the SqlParameter constructor, you can use any variable instance.SqlParameter

SQL injection. The AddDog method shown above uses variables in the SqlCommand text. This means it is immune to SQL injection attacks. If you are running an ASP.NET website, you will get these every day.

Tip: Never insert data into a database table in raw form. Instead, please use SqlParameter and variable names, or question marks.

Class. Your C# programs will likely use objects to encapsulate data. This means that in our data-driven dog database, we need a Dog class. This class has three public properties, which correspond to the three columns in the database.

ToString: We see the ToString() method in this class. This is overrided and makes it easy for us to display the contents of the class Dog.

ToString

Definition of Dog class: C# /// <summary> /// Encapsulates data for dog objects. /// </summary> public class Dog { public int Weight { get; set; } public string Name { get; set; } public string Breed { get; set; } public override string ToString() { return string.Format("Weight: {0}, Name: {1}, Breed: {2}", Weight, Name, Breed); } }

SqlDataReader. SqlDataReader is a fast way to read table data. It lets you extract individual cells from the database. Because our data is ordered with Weight first, which is an int, we can call GetInt32() with the parameter of 0.

Here: In this example, we use the SqlConnection and SqlCommand ADO.NET patterns in the C# language.

Note: Look at the string parameter to the SqlCommand constructor, and it is an SQL command that selects all cells from each SQL table row.

Tip: The star in the command means "all". The "Dogs1" table from the database is also specified.

C# program that uses SqlDataReader /// <summary> /// Read in all rows from the Dogs1 table and store them in a List. /// </summary> static void DisplayDogs() { List<Dog> dogs = new List<Dog>(); using (SqlConnection con = new SqlConnection( ConsoleApplication1.Properties.Settings.Default.masterConnectionString)) { con.Open(); using (SqlCommand command = new SqlCommand("SELECT * FROM Dogs1", con)) { SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { int weight = reader.GetInt32(0); // Weight int string name = reader.GetString(1); // Name string string breed = reader.GetString(2); // Breed string dogs.Add(new Dog() { Weight = weight, Name = name, Breed = breed }); } } } foreach (Dog dog in dogs) { Console.WriteLine(dog); } }

SqlReader is fast, but is not ideal for all situations. In Windows Forms, where you usually bind database tables to Controls, you can use SqlDataAdapter and the DataSource property on the Controls.SqlDataReader SqlDataAdapter

Finally: The foreach near the end of the example above displays the List collection that was filled with the data in the "Dogs1" table.

When you execute the program, you will see lines of each dog's data that have put into the database. The ToString() method is called implicitly when you specify the parameter to Console.WriteLine.

Console example. For the tutorial here, we use a Console program with an event loop. This allows you to input data into the program and have it dynamically inserted into the SQL Server database. This code is only useful for this demonstration.

However: In other data-driven applications, you can use similar logic for inserting user data, such as that entered on web pages.

Here: Main first makes sure the database table we are using has been created. This is done by calling the custom TryCreateTable method.

ReadLine: The next few lines in the Main method above parse the Console input from the ReadLine method. It uses Split and int.Parse.

Split int.Parse

C# program that calls AddData static void Main() { TryCreateTable(); while (true) { Console.WriteLine("INPUT TYPE:\tWeight,Name,Breed\tor\tSELECT:"); string[] input = Console.ReadLine().Split(','); try { char c = char.ToLower(input[0][0]); if (c == 's') { DisplayDogs(); continue; } int weight = int.Parse(input[0]); // The dog weight. string name = input[1]; // The name string. string breed = input[2]; // The breed string. AddDog(weight, name, breed); // Add the data to the SQL database. } catch { Console.WriteLine("Input error"); } } }

The methods shown here could be applied to any new data-driven application. If your program uses lots of data, you can use Visual Studio to add indexes on columns that can be selected on. Doing this can vastly improve speed.

Input, output. The program described and developed in the first steps of this tutorial is fun to test. Next, lines from the output of the program demonstrate how the rows are input into the database, and then selected and iterated.

Input INPUT TYPE: Weight,Name,Breed or SELECT: 57,Koko,Shar Pei INPUT TYPE: Weight,Name,Breed or SELECT: 130,Fido,Bullmastiff INPUT TYPE: Weight,Name,Breed or SELECT: 93,Alex,Anatolian Shepherd Dog INPUT TYPE: Weight,Name,Breed or SELECT: 25,Charles,Cavalier King Charles Spaniel INPUT TYPE: Weight,Name,Breed or SELECT: 7,Candy,Yorkshire Terrier INPUT TYPE: Weight,Name,Breed or SELECT: s Weight: 57, Name: Koko, Breed: Shar Pei Weight: 130, Name: Fido, Breed: Bullmastiff Weight: 93, Name: Alex, Breed: Anatolian Shepherd Dog Weight: 25, Name: Charles, Breed: Cavalier King Charles Spaniel Weight: 7, Name: Candy, Breed: Yorkshire Terrier INPUT TYPE: Weight,Name,Breed or SELECT:

I entered five dogs into the database. Next, I exited the program. The text that we see next demonstrates that the data was persisted successfully in the database. The dogs are now in Dog objects.

Output Table not created. INPUT TYPE: Weight,Name,Breed or SELECT: s Weight: 57, Name: Koko, Breed: Shar Pei Weight: 130, Name: Fido, Breed: Bullmastiff Weight: 93, Name: Alex, Breed: Anatolian Shepherd Dog Weight: 25, Name: Charles, Breed: Cavalier King Charles Spaniel Weight: 7, Name: Candy, Breed: Yorkshire Terrier INPUT TYPE: Weight,Name,Breed or SELECT:

Example. Here the compete program source is available. Before you can use the source code, you will need to change the namespace for the program. You will also need to follow the steps to create your connection string.

Complete program: C# using System; using System.Collections.Generic; using System.Data.SqlClient; class Program { static void Main() { TryCreateTable(); while (true) { Console.WriteLine("INPUT TYPE:\tWeight,Name,Breed\tor\tSELECT:"); string[] input = Console.ReadLine().Split(','); try { char c = char.ToLower(input[0][0]); if (c == 's') { DisplayDogs(); continue; } int weight = int.Parse(input[0]); string name = input[1]; string breed = input[2]; AddDog(weight, name, breed); } catch { Console.WriteLine("Input error"); } } } /// <summary> /// This method attempts to create the Dogs1 SQL table. /// If will do nothing but print an error if the table already exists. /// </summary> static void TryCreateTable() { using (SqlConnection con = new SqlConnection( ConsoleApplication1.Properties.Settings.Default.masterConnectionString)) { con.Open(); try { using (SqlCommand command = new SqlCommand( "CREATE TABLE Dogs1 (Weight INT, Name TEXT, Breed TEXT)", con)) { command.ExecuteNonQuery(); } } catch { Console.WriteLine("Table not created."); } } } /// <summary> /// Insert dog data into the SQL database table. /// </summary> /// <param name="weight">The weight of the dog.</param> /// <param name="name">The name of the dog.</param> /// <param name="breed">The breed of the dog.</param> static void AddDog(int weight, string name, string breed) { using (SqlConnection con = new SqlConnection( ConsoleApplication1.Properties.Settings.Default.masterConnectionString)) { con.Open(); try { using (SqlCommand command = new SqlCommand( "INSERT INTO Dogs1 VALUES(@Weight, @Name, @Breed)", con)) { command.Parameters.Add(new SqlParameter("Weight", weight)); command.Parameters.Add(new SqlParameter("Name", name)); command.Parameters.Add(new SqlParameter("Breed", breed)); command.ExecuteNonQuery(); } } catch { Console.WriteLine("Count not insert."); } } } /// <summary> /// Read in all rows from the Dogs1 table and store them in a List. /// </summary> static void DisplayDogs() { List<Dog> dogs = new List<Dog>(); using (SqlConnection con = new SqlConnection( ConsoleApplication1.Properties.Settings.Default.masterConnectionString)) { con.Open(); using (SqlCommand command = new SqlCommand("SELECT * FROM Dogs1", con)) { SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { int weight = reader.GetInt32(0); // Weight int string name = reader.GetString(1); // Name string string breed = reader.GetString(2); // Breed string dogs.Add(new Dog() { Weight = weight, Name = name, Breed = breed }); } } } foreach (Dog dog in dogs) { Console.WriteLine(dog); } } } /// <summary> /// Encapsulates data for dog objects. /// </summary> public class Dog { public int Weight { get; set; } public string Name { get; set; } public string Breed { get; set; } public override string ToString() { return string.Format("Weight: {0}, Name: {1}, Breed: {2}", Weight, Name, Breed); } }

Summary. We saw a detailed tutorial for using the System.Data.SqlClient namespace and Visual Studio to develop a data-driven console program. We also built object models that can be used in other C# code for data processing and logic.

The Dev Codes

.Net

.NET Array Dictionary List String 2D Async DataTable Dates DateTime Enum File For Foreach Format IEnumerable If IndexOf Lambda LINQ Parse Path Process Property Regex Replace Sort Split Static StringBuilder Substring Switch Tuple

Java

Core Array ArrayList HashMap String 2D Cast Character Console Deque Duplicates File For Format HashSet If IndexOf Lambda Math ParseInt Process Random Regex Replace Sort Split StringBuilder Substring Switch Vector While

TheDeveloperBlog.com

C# SqlClient Tutorial: SqlConnection, SqlCommand

Related Links:

.Net

Java

Related Links