C# SqlClient

SQL: Structured Query Language

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.

Note:SqlClient is an essential tool for building programs that interact with databases.

Intro

Steps

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 Data > Add New Data Source. Select "Database" and click "New Connection."

Note

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: C#

using System;                     // For system functions like Console.
using System.Collections.Generic; // For generic collections like List.
using System.Data.SqlClient;      // For the database connections and objects.

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.
Programming tip

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.

Program that uses SqlCommand: C#

/// <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.");
	}
    }
}
Method

We see the method will create the Dogs1 table with three columns.
It stores the Weight,
Name
and Breed of the dog. Weight is stored as an int, while the other two fields are text fields (strings).

Description of ExecuteNonQuery. The ExecuteNonQuery method is ideal for when you are inserting data, not reading it or querying for a specific result. Also, look at how we call the Open instance method on the SqlConnection.

Using keyword

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.

Also:Please note that the second parameter to the SqlCommand constructor is the opened connection.

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.

Program that uses SqlParameter: C#

/// <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.");
	}
    }
}
Logo

Every SqlCommand instance has a Parameter collection, which is an SqlParameterCollection and accessed with the Parameters property. It is empty by default, so use the Add instance method to add new parameters.

Tip:In the Add method, we call the new SqlParameter constructor. This is how we create the parameters.

Notes on SqlParameter constructor. One of the overloads of SqlParameter, and the one shown here, receives two arguments. First is the name of the variable in your command text, and second is the value object.

Using 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

Warning: exclamation mark

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

Class: shapes

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.

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. It is not critical to the database logic in this tutorial.

ToString Method
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.

Program that uses SqlDataReader: C#

/// <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);
    }
}

In this example, we use the SqlConnection and SqlCommand ADO.NET patterns. 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.

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.

SqlDataReaderSqlDataAdapterObject keyword

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

Console screenshot

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.

Program that calls AddData: C#

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");
	}
    }
}
Main method

We see the Main method, which is where the program begins. Main first makes sure the database table we are using has been created. This is done by calling the custom TryCreateTable method, which is shown above.

The parsing code. The next few lines in the Main method above parse the Console input from the ReadLine method. You should be familiar with the basics of the Split method and the int.Parse method.

Note:See the relevant articles on the site for more information. These methods are useful in many programs.

Splitint.Parse

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

Below, 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

The C# programming language

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.

Review:These patterns will result in secure code that cannot be hacked. You can conserve system resources.


C#: Data