Return of the Stored Procedure

We have seen a couple examples of how to use LINQ to SQL in the previous posts  LINQ to SQL Part 1, LINQ to SQL Part 2, and how easy it is to use. The next question now becomes, What if your company strictly uses stored procedures for all of its data accessing?  Companies today can have stored procedures number from the hundreds to the thousands. With LINQ to SQL, you can still call your stored procedures and even mix and match the type styles.

First off, if you are using SQLMETAL, you will have to add an extra parameter to your DataContext build. Here is the needed extra parameter hi-lighted in red. This will again build the needed class to enable you to access the database.

SQLMETAL /SERVER:ORCASBETA2_VSTS\SQLEXPRESS /DATABASE:NORTHWIND /CODE:NWIND.CS /SPROCS

Once we have the class built and added to our project, we can get down to work. Here is a some sample code using the Northwind database.

   1: static void TestWithAdo()

   2:        {

   3:            SqlConnection conn = new SqlConnection(Properties.Settings.Default.NorthwindConnectString);

   4:  

   5:            SqlCommand spCommand = new SqlCommand("SalesByCategory", conn);

   6:            spCommand.CommandType = CommandType.StoredProcedure;

   7:            spCommand.Parameters.Add("@CategoryName", "Seafood");

   8:            spCommand.Parameters.Add("@OrdYear", "2007");

   9:  

  10:            conn.Open();

  11:  

  12:            SqlDataReader reader = spCommand.ExecuteReader();

  13:            while (reader.Read())

  14:            {

  15:                Console.WriteLine("{0,-20}\t{1,10:c}",

  16:                            reader["ProductName"].ToString().PadRight(20, ' ').Substring(0, 20),

  17:                            reader["TotalPurchase"]);

  18:            };

  19:  

  20:            reader.Close();

  21:            conn.Close();

  22:        }

First, let's look at the standard ADO.NET way of accessing a stored procedure in .NET.  We create a SqlConnection object, followed by a SqlCommand Object. We than have to add parameters and open the connection. Finally, we need to create an SqlDataReader object before we can retrieve the first row of data from the database. Quite a few steps for each a every stored procedure we wish to call. 

Now lets take a look at LINQ to SQL 

   1: static void TestWithLinq()

   2:         {

   3:             Northwind db = new Northwind(Properties.Settings.Default.NorthwindConnectString);

   4:  

   5:             var SalesReport = db.SalesByCategory("Seafood", "2007");

   6:  

   7:             foreach (var SalesItem in SalesReport)

   8:             {

   9:                 Console.WriteLine("{0,-20}\t{1,10:c}",

  10:                         SalesItem.ProductName.PadRight(20, ' ').Substring(0, 20),

  11:                         SalesItem.TotalPurchase);

  12:             }

  13:         }

With LINQ we can really reduce most of the needed ADO.NET code down to just a line or two.  Once the Northwind object has been instantiated, we can simply call the stored procedure like any other method in the class as observed in the above code.  I don't know any programmer that uses SQL day in and out that won't be foaming at the mouth for this.