none
ASP.NET Loading data Into DataSets From LINQ RRS feed

  • Question

  • Hi Experts,

    I have Stored Procedure, which returns result as :

                                                                                                                                                                                                                                                                                 AcademicDetails                              FirstName                                          MiddleName                                        LastName                                          
    --------------------------------------------------------------------------------------------------------------------------------------------------
                                                                                                                                                                                                                                                                   2008-2009                                       Vijay                                                 Laxmanrao                                          Jadhav                                            
                                                                                                                                                                                                                                                                    2008-2009                                       Sarang                                              Laxmanrao                                          Patil
                                                                                                                                                                                                                                                                    2008-2009                                       Amar                                                Laxmanrao                                          Jadhav                                            
                                                                                                                                                                                                                                                                    2008-2009                                       Pratap                                              Laxmanrao                                          Jadhav                                            


    I need to assign above result to DataSet. How to do it ?

    I have tried code something like :

            public static DataSet SearchStudentInfo(DataSet oData)
            {
                SisDataContext db = new SisDataContext();
                return db.usp_ATI_SearchStudent(oData.Tables[0].Rows[0]["AcademicDetails"].ToString(),
                                                oData.Tables[0].Rows[0]["FirstName"].ToString(),
                                                oData.Tables[0].Rows[0]["MiddleName"].ToString(),
                                                oData.Tables[0].Rows[0]["LastName"].ToString());
            }

    But, it didn't work.

    Sample code would be greatly appreciated! 

    Please help.
    Thursday, June 11, 2009 10:02 AM

Answers

  • There are two ways you can solve this problem:

    1) Use the DataSet Designer, steps below:
        a)Create a new DataSet item type:
        b)Using the Server Explorer create a connection to your database
        c)Find your stored procedure and drag and drop the stored procedure onto the design surface.
       
         This will created a typed DataTable and an adapter that is attached to this. THen all you need to do is add the code to your form to fill this. Below is a   code snippet on how to do this:

     

    DataSet1TableAdapters.ATI_SearchStudentTableAdapter adapter = new WindowsFormsApplication1.DataSet1TableAdapters.ATI_SearchStudentTableAdapter();
    DataSet1.ATI_SearchStudentDataTable table = new DataSet1.ATI_SearchStudentDataTable();
    adapter.Fill(table, "2008-2009");

     

      This way isn't all that hard. But there is another option, you can simply just hand code this. Behind the scenes the TypeDataSet desginer is generating code that will do execute a command to sql to get your data.

    2) Hand code the required pieces to fill a DataTable, code below

     

    //Create Connection
    SqlConnection connection = new SqlConnection(connectionString);
    //Create Stored Procedure, creating this from the connection automatically connects

     

    //this command to this connection
    SqlCommand storedProcedureCommand = connection.CreateCommand();
    storedProcedureCommand.CommandType =
    CommandType.StoredProcedure;
    //Name of the stored procedure
    storedProcedureCommand.CommandText ="dbo.ATI_SearchStudent";
    storedProcedureCommand.Parameters.Add(
    "AcademicYear", SqlDbType.NVarChar,30);

     

     

    DataTable table1 = new DataTable();
    //Adapter that will fill the datatable
    SqlDataAdapter sqlAdapter = new SqlDataAdapter();
    sqlAdapter.SelectCommand = storedProcedureCommand;
    //Parameter Value to use as input for the Store Procedure
    sqlAdapter.SelectCommand.Parameters[0].Value = "2008-2009";
    //Open the connection, get the data and close

     

    try
    {
         connection.Open();
         sqlAdapter.Fill(table1);
    }
    catch (Exception exc)
    {
          Console.WriteLine(exc.ToString());
    }
    finally
    {
          if(connection.State == ConnectionState.Open || connection.State == ConnectionState.Broken)
               connection.Close();
    }

     

     


    Let me know if this doesn't give you the information you need.

    Thanks
    Chris Robinson
    Program Manager - DataSet


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, June 11, 2009 3:19 PM

All replies

  • There are two ways you can solve this problem:

    1) Use the DataSet Designer, steps below:
        a)Create a new DataSet item type:
        b)Using the Server Explorer create a connection to your database
        c)Find your stored procedure and drag and drop the stored procedure onto the design surface.
       
         This will created a typed DataTable and an adapter that is attached to this. THen all you need to do is add the code to your form to fill this. Below is a   code snippet on how to do this:

     

    DataSet1TableAdapters.ATI_SearchStudentTableAdapter adapter = new WindowsFormsApplication1.DataSet1TableAdapters.ATI_SearchStudentTableAdapter();
    DataSet1.ATI_SearchStudentDataTable table = new DataSet1.ATI_SearchStudentDataTable();
    adapter.Fill(table, "2008-2009");

     

      This way isn't all that hard. But there is another option, you can simply just hand code this. Behind the scenes the TypeDataSet desginer is generating code that will do execute a command to sql to get your data.

    2) Hand code the required pieces to fill a DataTable, code below

     

    //Create Connection
    SqlConnection connection = new SqlConnection(connectionString);
    //Create Stored Procedure, creating this from the connection automatically connects

     

    //this command to this connection
    SqlCommand storedProcedureCommand = connection.CreateCommand();
    storedProcedureCommand.CommandType =
    CommandType.StoredProcedure;
    //Name of the stored procedure
    storedProcedureCommand.CommandText ="dbo.ATI_SearchStudent";
    storedProcedureCommand.Parameters.Add(
    "AcademicYear", SqlDbType.NVarChar,30);

     

     

    DataTable table1 = new DataTable();
    //Adapter that will fill the datatable
    SqlDataAdapter sqlAdapter = new SqlDataAdapter();
    sqlAdapter.SelectCommand = storedProcedureCommand;
    //Parameter Value to use as input for the Store Procedure
    sqlAdapter.SelectCommand.Parameters[0].Value = "2008-2009";
    //Open the connection, get the data and close

     

    try
    {
         connection.Open();
         sqlAdapter.Fill(table1);
    }
    catch (Exception exc)
    {
          Console.WriteLine(exc.ToString());
    }
    finally
    {
          if(connection.State == ConnectionState.Open || connection.State == ConnectionState.Broken)
               connection.Close();
    }

     

     


    Let me know if this doesn't give you the information you need.

    Thanks
    Chris Robinson
    Program Manager - DataSet


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, June 11, 2009 3:19 PM
  • Hi Chris,

    Thanks for your reply.

    Up to  following steps :

    1) Use the DataSet Designer, steps below:
        a)Create a new DataSet item type:
        b)Using the Server Explorer create a connection to your database
        c)Find your stored procedure and drag and drop the stored procedure onto the design surface.


    I am agree.

    But code snippet like :

    DataSet1TableAdapters.ATI_SearchStudentTableAdapter adapter = new WindowsFormsApplication1.DataSet1TableAdapters.ATI_SearchStudentTableAdapter();
    DataSet1.ATI_SearchStudentDataTable table = new DataSet1.ATI_SearchStudentDataTable();
    adapter.Fill(table, "2008-2009");

    I am not getting you. Does DataSet1TableAdapter creates automatically, while doing option 1) ?

    Possibly, try to forward full snippet.

    Option 2), I am using LINQ. (SQL Server 2008). I think, we don't have to provide connection String and remaining things. 

    Any solution Chris ?
    Friday, June 12, 2009 6:19 AM
  • Hey Vijay,

    The DataTableAdapter name for my code will likely be different for your code that is generated. But there will be a table Adapter generated when you drag a table from the server explorer to the DataSet design service. You will have to review the classes that are created and figure out which one it is. Typically all the adapters are prefixed by the (DataSetName).(TableName)TableAdapter. Though it was slightly different in older versions of VS. The classes produced for me were on VS 2008. If you are using a version prior to that the TableAdapter may be in the same namespace as the Typed DataTable.

    Here is a link that talks about what a typed DataSet is
    http://msdn.microsoft.com/en-us/library/esbykkzb(VS.71).aspx

    Thanks,
    Chris Robinson
    Program Manager.


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, June 12, 2009 9:28 PM
  • Hi Chris,

    Thanks for your reply.

    I am using C#.NET (.NET FW 3.5, VS 2008) and SQL Server 2008. I have looked in classes that are created, but I didn't seen anywhere the signature you are provide for

    TableAdapter.

    Any solution ?

    Thanks.


    Monday, June 15, 2009 7:23 AM
  • I don't know much else to say other than if you created the Table's and Table adapters by dragging from a server explorer the code is there. You need to look in the designer files underneath the DataSet.xsd file.

    Thanks
    Chris Robinson
    Program Manager - DataSet
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Monday, June 15, 2009 8:53 PM
  • Hi Chris,

    Thanks for your reply.
    Tuesday, June 16, 2009 5:56 AM