In this article, I will discuss how to communicate with SQL Server using C#. You can use this code for communicating with other databases too but the connection string may vary. SQL Server supports SQL queries and stored procedure, and we will take a look at how to run both of them.
I will assume that you know what queries are, what SQL Server is, etc.
When communicating with a database usually you want to do 4 things:
- Connecting to the database
- Writing to the database (insert/update/delete)
- Reading from the database
- Closing the connection when you are done
Before we begin, let’s assume that we already have a database which has a table called my_contacts. And, it looks like this:
[sql]
create table my_contacts (
name nvarchar(100),
phone nvarchar(20)
);
[/sql]
Our database server is localhost, the database’s name is database_name. The username required to connect is myuser and the password for that user is mypass.
Running Queries
[csharp]
try {
/*************************************************************
* First we make a connection. For this, you will need to
* have the hostname (with which you connect to the database),
* database name, username, password. You can obtain this
* information from you DBA or your network administrator
* SqlConnection is the class you use to make the connection
*************************************************************/
string connectionString = “Server=localhost;Database=database_name;uid=myuser;pwd=mypass;MultipleActiveResultSets=True;”;
SqlConnection con = new SqlConnection(connectionString);
/*************************************************************
* After a connection is made, you are free to read and write
* to your database using SQL queries. We will, first, take a
* look at how data is inserted into a table
*************************************************************/
string sql = “insert into my_contacts(name, phone) values (”
+ “‘john doe’, ‘123-123-1233’)”;
SqlCommand cmd = new SqlCommand(sql, con);
cmd.ExecuteNonQuery();
/*************************************************************
* Since insert,update and delete queries don’t return anything
* we use ExecuteNonQuery() method to execute the query for them.
* We will use a different method for reading (selecting) the
* data
*************************************************************/
sql = “select * from my_contacts”;
cmd.CommandText = sql; //This is another way of setting a query
SqlDataReader dr = cmd.ExecuteReader();
/*************************************************************
* Execute reader executes a query and returns a pointer to a
* reader. You can then traverse through the rows which are
* returned like this
*************************************************************/
while (dr != null && dr.Read())
{
Console.WriteLine(dr[“name”].ToString()+” : ”
+ dr[“phone”].ToString());
}
/*************************************************************
* Remember to close the connection and the reader after you
* are done with it.
* This is very important. You don’t want to fill your
* connection pool with unused connections.
*************************************************************/
dr.Close();
con.Close();
} catch (Exception e) {
System.Windows.Forms.MessageBox.Alert(e.ToString());
}
[/csharp]
You will have to include the namespace System.Data.SqlClient for the code above (and below) to work. You can do that by adding “using System.Data.SqlClient;” line at the top of the file.
Running Stored Procedures
A stored procedure is a query (or multiple queries) stored on the SQL Server which can be called from anywhere. An ideal situation where you want to use a stored procedure is when you want to have some of the data logic in your queries (like if statements). Running a stored procedure is very similar to running a SQL query except for a few minor differences.
[csharp]
try {
/*************************************************************
* We connect to the database just like we did for running a
* query. Or, if there is an open connection, we can use that.
* For this example, I will use a fresh connection
*************************************************************/
string connectionString = “Server=localhost;Database=database_name;uid=username;pwd=password;MultipleActiveResultSets=True;”;
SqlConnection con = new SqlConnection(connectionString);
/*************************************************************
* After a connection is made, you run the stored procedure
* just like a query
*************************************************************/
string spName = “dbo.StoredProcedureName”;
SqlCommand cmd = new SqlCommand(spName, con);
cmd.CommandType = SqlCommand.CommandTypes.StoredProcedure;
cmd.ExecuteNonQuery();
/*************************************************************
* In the above example, no parameters are passed to the
* stored procedure and it returns nothing back. What if you
* needed to pass parameters and read the records returned?
*************************************************************/
cmd.CommandText = “dbo.AddAContactAndGetContactList()”;
cmd.Parameters.Add(new SqlParameter(“name”, “Joe Briefcase”));
cmd.Parameters.Add(new SqlParameter(“phone”, “123-321-1233”));
SqlDataReader dr = cmd.ExecuteReader();
/*************************************************************
* The constructor for SqlParameter takes 2 parameters –
* the parameter name, and the parameter value. In our case,
* we passed 2 parameters – name and phone.
*
* Execute reader executes a query and returns a pointer to a
* reader. You can then traverse through the rows which are
* returned like this
*************************************************************/
while (dr != null && dr.Read())
{
Console.WriteLine(dr[“name”].ToString()+” : ”
+ dr[“phone”].ToString());
}
dr.Close();
con.Close();
} catch (Exception e) {
System.Windows.Forms.MessageBox.Alert(e.ToString());
}
[/csharp]
That’s all for now, folks 🙂
Leave a Reply