Warning: Illegal string offset 'language' in /app/wp-content/plugins/igsyntax-hiliter/classes/frontend.php on line 510
Warning: ksort() expects parameter 1 to be array, string given in /app/wp-content/plugins/igsyntax-hiliter/classes/frontend.php on line 513
Warning: Illegal string offset 'language' in /app/wp-content/plugins/igsyntax-hiliter/classes/frontend.php on line 510
Warning: ksort() expects parameter 1 to be array, string given in /app/wp-content/plugins/igsyntax-hiliter/classes/frontend.php on line 513
Warning: Illegal string offset 'language' in /app/wp-content/plugins/igsyntax-hiliter/classes/frontend.php on line 510
Warning: ksort() expects parameter 1 to be array, string given in /app/wp-content/plugins/igsyntax-hiliter/classes/frontend.php on line 513
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:
- create table my_contacts (
- name nvarchar(100),
- phone nvarchar(20)
- );
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
- 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());
- }
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.
- 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());
- }
That’s all for now, folks 🙂