Geek Answers Handbook

Good way to access stored procedure in SQL Server 2008 with C #

I am trying to access stored procedures from my ASP.NET/C# web application.

Is there a particularly good way to do this (also a good way to pass parameters to it)?

I followed these steps and absolutely dislike this approach, as you enter the procedure as a string (un-debugable): http://www.c-sharpcorner.com/UploadFile/dclark/InsOutsinCS11302005072332AM/InsOutsinCS.aspx

+4
source share
4 answers

If you were using LinqToSQL, ScottGU has some excellent blog posts on this subject.

+6
source

I like SubSonic . SubSonic will generate a DAL for you based on your database schema. Most people use generated SubSonic objects with an activerecord or simpleerepository template to work with the database, but Subsonic also generates strongly typed methods to transfer your stored procedures and views.

This is a fairly simple example, but you can make the stored procedure call look like the following:

SubSonic.StoredProcedure sp = Dal.StoredProcedures.UpdatePassword(id, pw); DataSet ds = sp.GetDataSet();

In the above code, the StoredProcedures class includes a strongly typed method for the UpdatePassword stored procedure. This works great for me.

SubSonic Docs are here.

+1
source

Using LINQ to SQL or Entity Framework really gives you good, strongly typed access to your parameters and results, but there are a lot of considerations to make before using any of them. LINQ to SQL is a lifetime, and the Entity Framework will overhaul the .NET Framework 4.0.

To make a conscious consideration, I thought that I would use the ADO.NET low-development method:

 void CallMyStoredProcedure(string connectionString, string paramValue) { string spName = "MyStoredProcedure"; string columnValue; using (SqlConnection conn = new SqlConnection(connectionString)) using (SqlCommand cmd = new SqlCommand(spName, conn)) { cmd.CommandType = CommandType.StoredProcedure; // Set parameter values cmd.Parameters.AddWithValue("@inputParam", (object)paramValue ?? DBNull.Value); // Parameter to store return value of your SP SqlParameter returnParam = cmd.Parameters.Add( new SqlParameter("RETURN_VALUE", SqlDbType.Int)); returnParam.Direction = ParameterDirection.ReturnValue; // Execute SP and read results conn.Open(); using (SqlDataReader dr = cmd.ExecuteReader()) { // Do something with return value int? returnValue = cmd.Parameters["RETURN_VALUE"].Value as int?; while (dr.Read()) { // Suggested way to read results if you use // "SELECT * FROM" in your SP. If you return columns // in a fixed order, it fairly safe to be reading // in that order without looping for (int field = 0; field < dr.FieldCount; field++) { switch (dr.GetName(field)) { case "StringColumn": columnValue = dr.GetValue(field) as string; break; default: // Column not recognized by your code. // You might choose to complain about it here. break; } } } } } }

Yes, this is very little code, so you might want to write some helper methods if you want to switch to vanilla ADO.NET.

+1
source

The best way is to use LINQ to SQL or ADO.NET Entities. Visual Studio will create appropriate strongly typed methods for stored procedures.

0
source

More articles:


All Articles