Post
Ask Question

Example Of SqlConnection, SqlCommand, SqlDataReader With Real Time Example in C#

onlDescription

SqlConnection: This ADO.NET class helps to connect database connection,  which is in web.config file.
SqlCommand: This ADO.NET class helps call SQL Store procedure or SQL query.
SqlDataReader: This ADO.NET class helps to retrieve/fetch the data from the query.

Code

  1. public class shoppingCartDetails  
  2. {  
  3.     public int cartID  
  4.     {  
  5.         get;  
  6.         set;  
  7.     }  
  8.     public string description  
  9.     {  
  10.         get;  
  11.         set;  
  12.     }  
  13.     public shoppingCartDetails GetShoppingDetails()  
  14.     {  
  15.         shoppingCartDetails sCD = new shoppingCartDetails();  
  16.         int pcartID = 0;  
  17.         string pdescription = string.Empty;  
  18.         string connstring = ConfiguratonManager.ConnectionString["connectionName"];  
  19.         using(SqlConnection con = new SqlConnection(connstring))  
  20.         {  
  21.             using(SqlCommand cmd = new SqlCommand("dbo.SPName", con))  
  22.             {  
  23.                 try  
  24.                 {  
  25.                     cmd.CommandType = CommandType.StoredProcedure;  
  26.                     con.Open();  
  27.                     SqlDataReader reader = cmd.ExecuteReader();  
  28.                     if (reader.HasRows)  
  29.                     {  
  30.                         while (reader.Read())  
  31.                         {  
  32.                             pcartID = Int32.Parse(reader["cartID"].ToString());  
  33.                             pdescription = reader["description"].ToString();  
  34.                         }  
  35.                         break;  
  36.                     }  
  37.                 }  
  38.                 reader.Close();  
  39.                 sCD.cartID = pcartID;  
  40.                 sCD.description = pdescription;  
  41.                 sCD.success = true;  
  42.             }  
  43.             catch (Exception ex)  
  44.             {  
  45.                 EventLogging.LogEvent("Error getting details", System.Diagnostics.EventLogEntryType.Error);  
  46.                 sCD.errorMessage = ex.ToString();  
  47.                 sCD.success = false;  
  48.             } finally  
  49.             {  
  50.                 con.Close();  
  51.             }  
  52.         }  
  53.     }  
  54.     return sCD;  
  55. }  
  56. }