DataSet

Filling dataset with data from more than one table
------------------------------------------------------------
To Fill dataset with data from more than one table from database and to
bind that data to datagrid we need following code

1. In .aspx file you need to write

<div>
<asp:datagrid id="grid1" runat="server" autogeneratecolumns="True">
</asp:datagrid>
<asp:datagrid id="grid2" runat="server" autogeneratecolumns="True">
</asp:datagrid>
</div>

Here we are specifying 2 datagrids to display data from 2 Tables.

2. In .aspx.cs file

1. First we need to open a connection

SqlConnection newconn = new SqlConnection(“Server=xxx;
InitialCatalog=xxx; UserId=xx;Password=xxx”);
Newconn.open();

In the above statement server= xxx means we are specifying server
name.InitialCatalog means database name from which we want to
retrive data. Userid, password are userid and passwords of user
for sql server.

2. Next we need to specify the sql statements(required operation)

DataAdapter da = new SqlDataAdapter("select * from firsttablensme;
Select * from secondtablename”, newconn)

note: here instead of command object we are directly using
DataAdapter.If you want you can use command object also.

In the above statement we are selecting data from two tables.
Newconn is the connection object.

3. Now, we need to fill the dataset

ds = new DataSet();
da.Fill(ds, “firsttablename”);
da.Fill(ds, “secondtablename”);

Here we are using Fill() method of dataadapter to fill the Dataset.

4. Now to bind this data to datagrid we need following code

grid1.DataSource = ds;
grid1.DataSource = ds.Tables[0];
grid1.DataBind();

grid2.DataSource = ds.Tables[1];
grid2.DataBind();

Here DataSource property represents the datasource of the datagrid and
DataBind() method used to bind data to the datagrid.

Note: you can also use caption property of datagrid to specify the table caption
Ex: grid1.caption = “xxx”
here Xxx represents cation for the table displayed by datagrid.

That’s it.

Filling data from Database in to Dataset and then to Datagrid

LOADING DATA FROM DATABASE IN TO DATASET AND BINDING IT TO DATAGRID
---------------------------------------------------------------------------------
Dataset can not directly retrieve data from database. For this purpose we should use DataAdapter. DataAdapter acts as a bridge between database and dataset. This is used to retrieve data from Database into Dataset as well as to Update Database through Dataset.It supports insert, delete, update, select operations. It supports disconnected model.

Now we will see the steps required to fill data from database into dataset and then populate that data into datagrid.
For that

In .aspx page, in <div> section we should write
<div>
<asp:datagrid id="exdatagrid" autogeneratecolumns="true" runat="server">
</asp:datagrid>
</div>

Here ID represents the id of the datagrid, runat represents that the datagrid is a server control, AutoGenerateColumns automatically creates the columns for the datagrid.

In the .aspx.cs file

1. First we should open a connection
SqlConnection newconn = new SqlConnection(“Server=xxx;InitialCatalog=xxx;
UserId=xx;Password=xxx”);
Newconn.open();

In the above statement server = xxx means we are specifying server name. InitialCatalog means database name from which we want to retrive data. Userid, password are userid and passwords of user for sql server.

2. Next we need to specify the sql statement(required operation)

SqlCommand cmd = new SqlCommand(“select * from xxx”, newconn);

In this statement first argument specifies the sql select statement(required operation) and second statement specifies the connection object. Here xxx represents Table name in database.

3 Now, we need to declare the DataAdapter

SqlDataAdapter da = new SqlDataAdapter(cmd);

In this statement dataadapter contains the command object as its argument to perform the required operation.

4. Now, we need to fill the dataset

ds = new DataSet();
da.Fill(ds);

In these statements first we declare dataset and then using the Fill() method of dataadapter we fill the dataset with the data from Database.

so now dataset is filled with the data from database.

5. Now we fill datagrid with the information/data in the dataset

exdatagrid.DataSource = ds;
exdatagrid.DataBind();

Here exdatagrid is the id of the datagrid we have declared in .aspx page.
DataSource property represents the datasource(means dataset) for the datagrid. DataBind method is used to bind data to the datagrid.

That’s it. We will see the data populated in to the datagrid.