Thursday, September 3, 2015

Get System.Data.DataTable from SQL Server Stored Procedure.

  public DataTable GetDataTableFromSP(string strParams)  
     {  
            string strConnectionString = ".......";  
             string strSPName = ".....";  
       DataTable dt = null;  
       using (SqlConnection conn = new SqlConnection(strConnectionString))  
       {  
         try  
         {  
           using (SqlDataAdapter da = new SqlDataAdapter())  
           {  
             da.SelectCommand = new SqlCommand(strSPName , conn);  
             da.SelectCommand.CommandType = CommandType.StoredProcedure;  
             da.SelectCommand.Parameters.Add("@varCharParam", SqlDbType.VarChar).Value = strParams)  
             DataSet ds = new DataSet();  
             da.Fill(ds, "Return_Result");  
             dt = ds.Tables["Return_Result"];             
           }  
         }  
         catch (System.Data.SqlClient.SqlException ex)  
         {  
           throw ex;  
         }  
         catch (Exception e)  
         {  
           throw e;  
         }  
       }  
       return dt;      
     }  
   }