none
Programmatically creating a typed DataTable from a specified stored procedure RRS feed

  • Question

  • I would like to be able to programatically analyze a SQL Server stored procedure and determine the appropriate properties of a DataTable to hold the returned data. I know that this must be possible because the IDE does this when creating a typed DataTable from a stored procedure. Can anyone point me at some examples or relevant documentation? Thanks.

    Thursday, January 3, 2008 10:42 PM

Answers

  • The GetSchemaTable method of a DataReader will return the schema informatoin about the columns you are selecting in a query so it should work for you. Below is a sample .If you want to use a different approach, look for the GetSchema method of the SqlConnection object.

     

    SqlConnection cn = new SqlConnection("Data Source=.;initial catalog=YourDb;Integrated security=true");
    cn.Open();
    SqlCommand cmd = new SqlCommand("YourStoredProceName, cn);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlDataReader rd = cmd.ExecuteReader();
    DataTable tbl = rd.GetSchemaTable();
    Monday, January 7, 2008 3:32 PM

All replies

  • Hi jrferrill,

     

    From your description, you would like to analyze a SQL Server stored procedure and determine the appropriate properties of a DataTable to hold the returned data. Could you please give us a broader description about it? As I understand, you can try to create an object class inherited from DataSet to hold the return data through a sored procedure.

     

    If I have misunderstood you, please correct me and clarify it for more details. Thanks.

     

    Regards,

    Monday, January 7, 2008 10:09 AM
  •  

    I understand how to create a typed DataTable. My question is how to programatically determine what the DataTable should look like given only a stored procedure name. Is there code available to let me map columns in the result to columns in the source database? Thanks.
    Monday, January 7, 2008 2:16 PM
  • The GetSchemaTable method of a DataReader will return the schema informatoin about the columns you are selecting in a query so it should work for you. Below is a sample .If you want to use a different approach, look for the GetSchema method of the SqlConnection object.

     

    SqlConnection cn = new SqlConnection("Data Source=.;initial catalog=YourDb;Integrated security=true");
    cn.Open();
    SqlCommand cmd = new SqlCommand("YourStoredProceName, cn);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlDataReader rd = cmd.ExecuteReader();
    DataTable tbl = rd.GetSchemaTable();
    Monday, January 7, 2008 3:32 PM
  • In addition to William's suggestion, you can check out this thread about "Retrieve Schema of a Table/View/Sproc" for reference - http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=445457&SiteID=1

     

    Tuesday, January 8, 2008 3:17 AM