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.