SQL Server Connection using ADO.NET data provider

Add a reference to the System.Data.dll in your project as below.

System.Data

Assembly: System.Data (in System.Data.dll)

Namespace: System.Data.SqlClient

Classes:

public sealed class SqlConnection : DbConnection, ICloneable
public sealed class SqlCommand : DbCommand, ICloneable
public class SqlDataReader : DbDataReader, IDataReader, IDisposable, IDataRecord

Sample Code

ExecuteNonQuery() using Query String

 string queryString = "UPDATE Student SET StudentName='Deepak' WHERE StudentID=15";  
   
 try  
 {  
      using (SqlConnection connection = new SqlConnection(connectionString))  
      {  
           connection.Open();  
   
           using (SqlCommand command = new SqlCommand(queryString, connection))  
           {  
                int rowsAffected = command.ExecuteNonQuery();  
                Console.WriteLine("Rows Affected: {0}", rowsAffected);  
           }  
      }  
 }  
 catch (SqlException ex)  
 {  
      Console.WriteLine("Error: " + ex.Message);  
 }  
 catch (Exception ex)  
 {  
      Console.WriteLine("Error: " + ex.Message);  
 }  

ExecuteNonQuery() using Query String and Parameters

 string queryString = "UPDATE Student SET StudentName=@name WHERE StudentID=@studentID";  
   
 try  
 {  
      using (SqlConnection connection = new SqlConnection(connectionString))  
      {  
           connection.Open();  
   
           using (SqlCommand command = new SqlCommand(queryString, connection))  
           {  
                SqlParameter param;  
   
                // Input Parameter  
                param = new SqlParameter("@name", "Deepak")  
                {  
                     SqlDbType = SqlDbType.VarChar,  
                     Direction = ParameterDirection.Input  
                };  
                command.Parameters.Add(param);  
   
                // Input Parameter  
                param = new SqlParameter("@studentID", 15)  
                {  
                     SqlDbType = SqlDbType.Int,  
                     Direction = ParameterDirection.Input  
                };  
                command.Parameters.Add(param);  
   
                int rowsAffected = command.ExecuteNonQuery();  
                Console.WriteLine("Rows Affected: {0}", rowsAffected);  
           }  
      }  
 }  
 catch (SqlException ex)  
 {  
      Console.WriteLine("Error: " + ex.Message);  
 }  
 catch (Exception ex)  
 {  
      Console.WriteLine("Error: " + ex.Message);  
 }  

ExecuteNonQuery() using Stored Procedure and Parameters

 string storedProcedure = "AddStudent";  
   
 try  
 {  
      using (SqlConnection connection = new SqlConnection(connectionString))  
      {  
           connection.Open();  
   
           using (SqlCommand command = new SqlCommand(storedProcedure, connection))  
           {  
                command.CommandType = System.Data.CommandType.StoredProcedure;  
   
                SqlParameter param;  
   
                // Input Parameter  
                param = new SqlParameter("@name", "Deepak")  
                {  
                     SqlDbType = SqlDbType.VarChar,  
                     Direction = ParameterDirection.Input  
                };  
                command.Parameters.Add(param);  
   
                // Input Parameter  
                param = new SqlParameter("@standardID", 3)  
                {  
                     SqlDbType = SqlDbType.Int,  
                     Direction = ParameterDirection.Input  
                };  
                command.Parameters.Add(param);  
   
                int rowsAffected = command.ExecuteNonQuery();  
                Console.WriteLine("Rows Affected: {0}", rowsAffected);  
           }  
      }  
 }  
 catch (SqlException ex)  
 {  
      Console.WriteLine("Error: " + ex.Message);  
 }  
 catch (Exception ex)  
 {  
      Console.WriteLine("Error: " + ex.Message);  
 }  

ExecuteReader() using Query String

 string queryString = "SELECT * FROM Student";

 try
 {
      using (SqlConnection connection = new SqlConnection(connectionString))
      {
           connection.Open();

           using (SqlCommand command = new SqlCommand(queryString, connection))
           {
                using (SqlDataReader reader = command.ExecuteReader())
                {
                     while (reader.Read())
                     {
                          Console.WriteLine("{0}\t{1}\t\t{2}", reader[0], reader[1], reader[2]);
                     }
                }
           }
      }
 }
 catch (SqlException ex)
 {
      Console.WriteLine("Error: " + ex.Message);
 }
 catch (Exception ex)
 {
      Console.WriteLine("Error: " + ex.Message);
 }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s