Tag: c# sql server

Categories
C# Uncategorized

C# and SQL Server


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:

< View plain text >
code

  1. create table my_contacts (
  2. name nvarchar(100),
  3. phone nvarchar(20)
  4. );

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

< View plain text >
code

  1. try {
  2. /*************************************************************
  3. * First we make a connection. For this, you will need to
  4. * have the hostname (with which you connect to the database),
  5. * database name, username, password. You can obtain this
  6. * information from you DBA or your network administrator
  7. * SqlConnection is the class you use to make the connection
  8. *************************************************************/
  9. string connectionString = "Server=localhost;Database=database_name;uid=myuser;pwd=mypass;MultipleActiveResultSets=True;";
  10. SqlConnection con = new SqlConnection(connectionString);
  11.  
  12. /*************************************************************
  13. * After a connection is made, you are free to read and write
  14. * to your database using SQL queries. We will, first, take a
  15. * look at how data is inserted into a table
  16. *************************************************************/
  17. string sql = "insert into my_contacts(name, phone) values ("
  18. + "'john doe', '123-123-1233')";
  19. SqlCommand cmd = new SqlCommand(sql, con);
  20. cmd.ExecuteNonQuery();
  21.  
  22. /*************************************************************
  23. * Since insert,update and delete queries don't return anything
  24. * we use ExecuteNonQuery() method to execute the query for them.
  25. * We will use a different method for reading (selecting) the
  26. * data
  27. *************************************************************/
  28. sql = "select * from my_contacts";
  29. cmd.CommandText = sql; //This is another way of setting a query
  30. SqlDataReader dr = cmd.ExecuteReader();
  31.  
  32. /*************************************************************
  33. * Execute reader executes a query and returns a pointer to a
  34. * reader. You can then traverse through the rows which are
  35. * returned like this
  36. *************************************************************/
  37. while (dr != null &amp;&amp; dr.Read())
  38. {
  39. Console.WriteLine(dr["name"].ToString()+" : "
  40. + dr["phone"].ToString());
  41.  
  42. }
  43.  
  44. /*************************************************************
  45. * Remember to close the connection and the reader after you
  46. * are done with it.
  47. * This is very important. You don't want to fill your
  48. * connection pool with unused connections.
  49. *************************************************************/
  50. dr.Close();
  51. con.Close();
  52. } catch (Exception e) {
  53. System.Windows.Forms.MessageBox.Alert(e.ToString());
  54. }

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.

< View plain text >
code

  1. try {
  2. /*************************************************************
  3. * We connect to the database just like we did for running a
  4. * query. Or, if there is an open connection, we can use that.
  5. * For this example, I will use a fresh connection
  6. *************************************************************/
  7. string connectionString = "Server=localhost;Database=database_name;uid=username;pwd=password;MultipleActiveResultSets=True;";
  8. SqlConnection con = new SqlConnection(connectionString);
  9.  
  10. /*************************************************************
  11. * After a connection is made, you run the stored procedure
  12. * just like a query
  13. *************************************************************/
  14.  
  15. string spName = "dbo.StoredProcedureName";
  16. SqlCommand cmd = new SqlCommand(spName, con);
  17. cmd.CommandType = SqlCommand.CommandTypes.StoredProcedure;
  18. cmd.ExecuteNonQuery();
  19.  
  20. /*************************************************************
  21. * In the above example, no parameters are passed to the
  22. * stored procedure and it returns nothing back. What if you
  23. * needed to pass parameters and read the records returned?
  24. *************************************************************/
  25. cmd.CommandText = "dbo.AddAContactAndGetContactList()";
  26. cmd.Parameters.Add(new SqlParameter("name", "Joe Briefcase"));
  27. cmd.Parameters.Add(new SqlParameter("phone", "123-321-1233"));
  28. SqlDataReader dr = cmd.ExecuteReader();
  29.  
  30. /*************************************************************
  31. * The constructor for SqlParameter takes 2 parameters -
  32. * the parameter name, and the parameter value. In our case,
  33. * we passed 2 parameters - name and phone.
  34. *
  35. * Execute reader executes a query and returns a pointer to a
  36. * reader. You can then traverse through the rows which are
  37. * returned like this
  38. *************************************************************/
  39. while (dr != null &amp;&amp; dr.Read())
  40. {
  41. Console.WriteLine(dr["name"].ToString()+" : "
  42. + dr["phone"].ToString());
  43.  
  44. }
  45. dr.Close();
  46. con.Close();
  47.  
  48. } catch (Exception e) {
  49. System.Windows.Forms.MessageBox.Alert(e.ToString());
  50. }

That’s all for now, folks 🙂