Tuesday, January 27, 2009

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.

6 comments:

Kadmiel said...

As a kid, I remember waking up on many a cold morning and stumbling into the kitchen with my eyes half-closed, looking forward to whatever Mom had prepared for breakfast, only to find an anticlimactic bowl of steaming hot just-add-boiling-water instant oatmeal waiting for me on the table. At least I wasn't like the more unfortunate kids whose mothers force-fed them that white silt of death, powdered milk. I am absolutely certain that something must go seriously awry in the dehydration process of milk because upon rehydration, that stuff is just plain nasty.

Be that as it may, I think that at least one of the developers involved in creating the .NET Framework must have been one of those abused children. I see powdered milk fingerprints all over some of the new data management techniques in .NET. Then again, in an age of dehydrated/rehydrated food products, what could be more logical than dehydrated/rehydrated data?

robingreen said...

Kadmiel, this is the first time I have ever heard it put this way. It has often crossed my mind as to why this frame work was not created in an easier format.

I am sure that those who understand the language find it easy to work with but someone like me finds it unnerving to even think about.

It is good to see someone explaining this in such an understanding that even I can get.

Bunda Sekar said...

your idea about this blog great,..

KangJoko said...

good job and great idea

Anonymous said...

I dabble in different internet coding languages so this is a really cool way to be able to stop by a blog and pick up pieces of code and how they work. Good Job!

sriman said...

hai,this is siva.wow!what a great blog.really good.it will help someone like me who are much intrested to grip subjet in their hand.really great job.keep going on............all the best.