How to Fill Dataset with Multiple Tables

How to fill Dataset with multiple tables?

If you are issuing a single command with several select statements, you might use NextResult method to move to next resultset within the datareader: http://msdn.microsoft.com/en-us/library/system.data.idatareader.nextresult.aspx

I show how it could look bellow:

public DataSet SelectOne(int id)
{
    DataSet result = new DataSet();
    using (DbCommand command = Connection.CreateCommand())
    {
        command.CommandText = @"
select * from table1
select * from table2
        ";

        var param = ParametersBuilder.CreateByKey(command, "ID", id, null);
        command.Parameters.Add(param);

        Connection.Open();
        using (DbDataReader reader = command.ExecuteReader())
        {
            result.MainTable.Load(reader);
            reader.NextResult();
            result.SecondTable.Load(reader);
            // ...
        }
        Connection.Close();
    }
    return result;
}

ImportExcel for Dataset with Multiple tables

This was solved by using the below code.

$sqlCommand = @"
SELECT * FROM SalesLT.Address
SELECT * FROM SalesLT.Customer
SELECT * FROM SalesLT.CustomerAddress
SELECT * FROM SalesLT.Product
SELECT * FROM SalesLT.ProductCategory
SELECT * FROM SalesLT.ProductDescription
SELECT * FROM SalesLT.ProductModel
"@

$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$command = New-Object System.Data.Sqlclient.Sqlcommand($sqlCommand, $connection)
$connection.Open()
$adapter = New-Object System.Data.sqlclient.SqlDataAdapter $command
$dataset = New-Object System.Data.DataSet
$null=$adapter.Fill($dataSet) 
$connection.Close()

$xlfile = "$env:TEMP\tables.xlsx"
rm $xlfile -ErrorAction SilentlyContinue

$count=1
foreach($table in $dataset.Tables) {
    $name = "Table$($count)"
    $table| Export-Excel $xlfile -WorksheetName $name -AutoSize -TableName $name
    $count+=1
}

Invoke-Item $xlfile

DataSet with multiple tables

As far as im aware you cant automagically fill a full dataset it this way. Youd have to populate each table in it. To do so, if your using the visual dataset, just right click on the table adapter, and add query. From here you can either add SQL straight into the table adapter, or use a stored procedure.

For the example of a select, the select must match the columns in your dataset.

So if we have a DataTable called CustomersTable, and we have added a func called "GetNewCustomers() you could do CustomersTable dtCustomers = adapter.GetNewCustomers()

See a much better description and tutorials start with #1 and #2 here

Datasets are .NET 2.0 however. I would recommend maybe getting to grips with them and then looking at LINQ2Entities to map your database.

Hope this helps.

How can I read multiple tables into a dataset?

Adapted from MSDN:

using (SqlConnection conn = new SqlConnection(connection))
{
    SqlDataAdapter adapter = new SqlDataAdapter();
    adapter.SelectCommand = new SqlCommand(query, conn);
    adapter.Fill(dataset);
    return dataset;
}

Return multiple datasets from sql server stored procedure

If you are going to get multiple tables then you have to write multiple select statements into your stored procedure like below:

CREATE PROCEDURE SPName
(
/*Declare your parameters*/
@parm1 dataType
)
AS
BEGIN
/*Write your select statements below*/
-- SELECT * FROM tblName
-- SELECT * FROM tblName2

END

You have to fill these records into your DataSet, DataSet supports multiple table into ADO.net.

Please refer below code to fill your DataSet:

SqlConnection con=new SqlConnection("YourConnection String");
SqlCommand cmd=new SqlCommand();
SqlDataAdapter da=new SqlDataAdapter();
DataSet ds = new DataSet();
cmd = new SqlCommand("SPName", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@parm1", id);//if you have parameters.
da = new SqlDataAdapter(cmd);
da.Fill(ds);
con.Close();

After this you can take advantage of different multiple recordsets using

ds.Tables[0]
ds.Tables[1]
..

Hope it will helps you

Thanks

---

Related Topics