SqlClient. This interacts with SQL Server. It allows the development of data-driven applications. The types we use include SqlConnection, SqlCommand, and SqlDataReader.
This tutorial is based on the local computer. But its steps can be applied on the network by using a remote database connection string. A database is required.
Add namespaces. If you are working in a console program, you will only need 3 namespaces. You should add the System.Data.SqlClient namespace.
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.
Unhandled Exception: System.Data.SqlClient.SqlException:
There is already an object named... in the database.
SqlException fix. The way to work around this problem is to 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.
Detail We see the method will create the Dogs1 table with 3 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).
/// <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.");
}
}
}
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.
Info The method here allows Dogs1 data to be inserted by simply passing parameters to AddDog.
Tip Every SqlCommand instance has a Parameter collection, which is an SqlParameterCollection and accessed with the Parameters property.
Tip 2 We use the Add instance method to add new parameters. In the Add method, we call the new SqlParameter constructor.
Note This constructor receives 2 arguments. First is the name of the variable in your command text, and second is the value object.
/// <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.");
}
}
}
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.
Detail This class has 3 public properties, which correspond to the 3 columns in the database.
Detail 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.
/// <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 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.
/// <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);
}
}
Console example. 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.
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.
Detail The next few lines in the Main method above parse the Console input from the ReadLine method. It uses Split and int.Parse.
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");
}
}
}
Input, output. Lines from the output of the program demonstrate how the rows are input into the database, and then selected and iterated.
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:
Output, continued. I entered 5 dogs into the database. Next, I exited the program. The output demonstrates that the data was persisted successfully in the database.
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:
Complete example code. Here we see the compete program source. Before you can use the source code, you will need to change the namespace for the program.
Also You will also need to follow the steps to create your connection string.
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);
}
}
Using statements. The using statement in the C# language helps handle important system resources. This statement will prevent catastrophic failures.
Object parameters. In C# every variable inherits from System.Object. So when you need an object as in the SqlParameter constructor, you can use any variable.
Security note. The AddDog method shown above uses variables in the SqlCommand text. This means it is immune to SQL injection attacks.
Tip Never insert data into a database table in raw form. Instead, please use SqlParameter and variable names, or question marks.
Other types. In Windows Forms, where you usually bind database tables to Controls, you can use SqlDataAdapter and the DataSource property on the Controls.
A summary. We used the System.Data.SqlClient namespace and Visual Studio to develop a data-driven console program. We also built an object model for the data.
Dot Net Perls is a collection of tested code examples. Pages are continually updated to stay current, with code correctness a top priority.
Sam Allen is passionate about computer languages. In the past, his work has been recommended by Apple and Microsoft and he has studied computers at a selective university in the United States.
This page was last updated on Aug 21, 2024 (edit).