Posts

Showing posts from July, 2012

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 Da

C# execute a stored procedure using SQL Server

Below we have some code that opens a connection to a database using a connection string then executes a stored procedure. This obviously needs to be placed in some method or class. You will need to add your own connection string and parameter values. try { using( SqlConnection connection = new SqlConnection("some connection string" ) ) { using( SqlCommand command = new SqlCommand( "stored procedure name", connection ) ) { command.CommandType = CommandType.StoredProcedure; // this line needs to be added for every parameter the SP expects command.Parameters.AddWithValue( "parametere value", paramValue ); connection.Open(); // based on the what the SP does, call the execute non query, execute scalar, or execute reader method command.ExecuteNonQuery(); } } } cat