Wednesday, 16 January 2013

Calling Stored Procedure with params in ado.net C#

Calling Stored Procedure with params in ado.net C#


This example explains if SQL-Server "Language" is different from Current UI culture. In this case datetime params needs to convert to SQL-Server Culture.


  void Calling_Stored_Procedures_using_adonet(DateTime begingdate,DateTime enddate)
        {
        try
        {
         SqlConnection conn = new SqlConnection(@"Server=.\sqlexpress2012;
                                               database=northwind;trusted_connection=yes;");
       
        String beginDate = begingdate.ToString(
                            new System.Globalization.CultureInfo("en-US"));
        String endDate = enddate.ToString(
                          new System.Globalization.CultureInfo("en-US"));
        String stroredProc = "Exec [dbo].[Sales by Year] @Beginning_Date='"
                             + beginDate + "',@Ending_Date='" + endDate+"'";

        SqlCommand sel = new SqlCommand(stroredProc);
        sel.Connection = conn;
        SqlDataAdapter ad = new SqlDataAdapter(sel);

        conn.Open();
        ad.Fill(dt);

        }

Calling at Client Side

        System.Threading.Thread.CurrentThread.CurrentCulture =
new System.Globalization.CultureInfo("te-IN");
        System.Threading.Thread.CurrentThread.CurrentUICulture=
new System.Globalization.CultureInfo("te-IN");
        Calling_Stored_Procedures_using_adonet(
new DateTime(1996,1,1),new DateTime(1998,12,31));