C# Use a stored procedure to fill a dataset/table

When you fill a DataSet/Table in C# you use a SQL Data Adapter. The code for this is geared towards a SQL SELECT statement. However I'm not a big fan of raw SQL in my code so I found a way to use a stored procedure instead.

 public static DataTable GetTable()  
     {  
       DataTable dt = null;  
       try  
       {  
         using( SqlConnection connection = new SqlConnection( "your connection string" ) )  
         {  
           using( SqlDataAdapter sda = new SqlDataAdapter() )  
           {  
             using( SqlCommand command = new SqlCommand( "your stored procedure", connection ) )  
             {  
               command.CommandType = CommandType.StoredProcedure;
               
               // this line needs to be added for every parameter the SP expects
               command.Parameters.AddWithValue( "parametere value", paramValue ); 
               
               sda.SelectCommand = command;  
               dt = new DataTable();  
               connection.Open();  
               sda.Fill( dt );  
             }  
           }  
         }  
       }  
       catch( Exception ex )  
       {  
         Debug.WriteLine( ex.ToString() );  
       }  
       return dt;  
     }  

Comments

Popular Posts