DataSet and DataAdapter Objects

cjo · November 14th, 2003, 03:54 PM

I'm using the following code to populate two DataGrid controls. As you can see, I create two DataSet objects and two DataAdapter objects. The code works fine, but I'm wondering if I could I have accomplished the same result with only one DataSet object and/or one DataAdapter object? If so, how?

Thanks in advance for your help!

<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script language="c#" runat="server">
void Page_Load()
{
    // declare assign string variables
    string strConnect = @"provider=Microsoft.Jet.OleDb.4.0;data source=c:\begaspnet\ch12\northwind.mdb";
    string strSql = "SELECT CompanyName, ContactName FROM Suppliers";
    string strSqlEmp = "SELECT LastName, FirstName FROM Employees";

    // create connection objects and DataSet objects
    OleDbConnection objConnect = new OleDbConnection(strConnect);
    DataSet objDataSet = new DataSet();
    DataSet objDataSet2 = new DataSet();

    // create the DataAdapter objects and us it to fill the DataSets object
    OleDbDataAdapter objDataAdapter = new OleDbDataAdapter(strSql, objConnect);
    OleDbDataAdapter objDataAdapter2 = new OleDbDataAdapter(strSqlEmp, objConnect);
    objDataAdapter.Fill(objDataSet, "SupplierContacts");
    objDataAdapter2.Fill(objDataSet2, "Employees");

    // assign dataset to the datagrid controls
    dgSupplierContacts.DataSource = objDataSet;
    dgEmployees.DataSource = objDataSet2;

    //bind data to the datagrid controls
    dgSupplierContacts.DataBind();
    dgEmployees.DataBind();

}
</script>

<html>
<body>
<asp:datagrid id="dgSupplierContacts" runat="server" cellpadding="5" font-name="verdana" font-size="10pt"
    headerstyle-backcolor="#dcdcdc" headerstyle-forecolor="blue" />
<br><br>
<asp:datagrid id="dgEmployees" runat="server" cellpadding="5" font-name="verdana" font-size="10pt"
    headerstyle-backcolor="#dcdcdc" headerstyle-forecolor="blue" />
</body>
</html>

CraigJones · November 14th, 2003, 04:26 PM

I dont have a great deal of expience with C# so you might need to change some of the syntax to the changes I made to ensure it works, but this shohuld do the trick.

A dataset can contain any number of datatables. About the only thinkg to note is that you need to specify the name of the table you wish to access when pulling the data out of the dataset.

{
    // declare assign string variables
    string strConnect = @"provider=Microsoft.Jet.OleDb.4.0;data source=c:\begaspnet\ch12\northwind.mdb";
    string strSql = "SELECT CompanyName, ContactName FROM Suppliers";
    string strSqlEmp = "SELECT LastName, FirstName FROM Employees";

    // create connection objects and DataSet objects
    OleDbConnection objConnect = new OleDbConnection(strConnect);
    DataSet objDataSet = new DataSet();
   //Delete this Line - DataSet objDataSet2 = new DataSet();

    // create the DataAdapter objects and us it to fill the DataSets object
    //Rearrange and modify the below lines
    OleDbDataAdapter objDataAdapter = new OleDbDataAdapter(strSql, objConnect);
    objDataAdapter.Fill(objDataSet, "SupplierContacts");
    OleDbDataAdapter objDataAdapter = new OleDbDataAdapter(strSqlEmp, objConnect);
    objDataAdapter.Fill(objDataSet, "Employees");

    // assign dataset to the datagrid controls
    //Modify the below lines
    dgSupplierContacts.DataSource = objDataSet.tables("SupplierContacts").DefaultView;
    dgEmployees.DataSource = objDataSet.tables("Employees").DefaultView;

    //bind data to the datagrid controls
    dgSupplierContacts.DataBind();
    dgEmployees.DataBind();
}

cjo · November 16th, 2003, 01:44 PM

Thank you Craig. I see that I can accomplish this task with only one DataSet object. Very helpful. Your code works beautifully.

bourne74 · January 10th, 2006, 10:29 AM

hiya.
i'v have kind of same problem, but instead of two datagrid i only have one but i want to populate it with data from two tables, is that possible?

        DBAnslutning();
        strSQL = "SELECT artNr, produktNamn, farg, langd, antal, pris FROM Kundvagn WHERE Kundvagn.kundNr ='" + Session["Knr"].ToString() + "';";
        strSQL2 += "SELECT Summa, Frakt FROM FraktKostnad WHERE FraktKostnad.kundNr ='" + Session["Knr"].ToString() + "';";
        objDataAdapter = new OleDbDataAdapter(strSQL, objConnection);
        objDataAdapter = new OleDbDataAdapter(strSQL2, objConnection);
        objDataSet = new DataSet();

        objDataAdapter.Fill(objDataSet, "Kundkorg");

        dgLista.DataSource = objDataSet;

        dgLista.DataBind();

        objConnection.Close();

        return objDataSet;

thus far it doesent work?

plz help.

chafor · January 13th, 2006, 03:42 PM

strSQL = "SELECT artNr, produktNamn, farg, langd, antal, pris FROM Kundvagn WHERE Kundvagn.kundNr ='" + Session["Knr"].ToString() + "';";
        strSQL2 += "SELECT Summa, Frakt FROM FraktKostnad WHERE FraktKostnad.kundNr ='" + Session["Knr"].ToString() + "';";
        objDataAdapter = new OleDbDataAdapter(strSQL, objConnection);
        objDataSet = new DataSet();
        objDataAdapter.Fill(objDataSet, "Kundkorg");

        objDataAdapter = new OleDbDataAdapter(strSQL2, objConnection);
        objDataAdapter.Fill(objDataSet, "FraktKostnad");

The problem was that you create your second dataAdapter over the first one... It destroyed the previous data...