Database editing operations using Visual Edit in ASP.NET Datagrid

Posted On April 24, 2012 by Rose Mary filed under Enterprise

In this article author briefly describes about database editing operations using visual edit in ASP.NET Datagrid.

In real life, any database table will have thousands and thousands of records. It is tough to manage all these data. In the web tier, though many technologies like Java servlets/jsp/struts/jsf, php are used for managing records, ASP.net is the best technology for CRUD operations (which means create, read, update and delete).

Consider a payroll program in a big company. Each record may have dozens of columns like basic pay, allowances, deductions, spl.allowances and so on.

As all these are numbers, if we try to change the record by filling up textboxes and then updating, it is likely to create a lot of errors. The better thing is to bring the existing values into textfields and then edit and update. Ofcourse, we can write our own code to achieve this. I will shortly show how we can do that. But still, ASP.net DataGrid control provides special features with which we can do this work very very easily. I will call this 'visual editing'.

For example, if we want to edit a record there is no need to type all the data for each record. We can search that record by its key. It will show the datagrid, which contains datafields of the particular record, with edit, and update buttons. Only one thing we have to do is editing. After editing simply click on update. The corrected record will be automatically updated.

Adding record to the table is simpler than editing. There is no need to bind the add button with in the grid. Just provide a number of textboxes that we need and a button "ADD" in one panel.

Moreover it is impossible to view all the records in one page. It will slow down and clutter the memory space. For that we can use the paging concept, which helps us to go to the page that we want.
We can also sort the records by any desired column. The ASP.net datagrid has built in support for all these .

In ASP.net we can do data operations by two methods. One is using DataSet and the other is DataReader. DataReader is faster when compared to Dataset. When we use DataReader the connection be will maintained, so others cannot access. When we use DataSet, the entire data according to the query made will come into memory. It will slow down. This is the demerit of using DataSet. And the merit of using DataSet is the connection will be disconnected. For studying the result and for XML operations, dataset is useful.

Now I am going to demonstrate this visual editing in both DataSet and DataReader.For demo purpose I am using Access databse only. But, the classes for SqlServer database are only slightly different. Just the names are changed.

The name of my databse is 'KEYNAMEPLACE'and the name of the table is table1. In all the demos, we just have to mention the path to the databse. I will call that connection string. I will call the sql as query string.

I will begin with demo1 where I am doing the CRUD operations without using the visual-edit features of Datagrid. And I am using DataReader.

demo1.aspx

<%@ page language="c#" debug="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>

</script>

<html>
<head>
</head>
<body bgcolor="lightgreen">
<form runat="server">
<asp:textbox id="text1" runat="server"/>
<asp:textbox id="text2" runat="server"/>>
<asp:textbox id="text3" runat="server"/>>
<asp:dropdownlist id="combo1"
runat="server"/>
<asp:button onclick="job1" runat="server" text="exec"/>
<asp:button onclick="job2" runat="server" text="verify"/>
asp:datagrid id="grid1" runat="server"/>>
</form>
</body>
</html>

The above code should be easy to follow.
The combo has options for 'add', find','edit' and 'remove'. I have provided three textboxes for key, name and place.

If the user is adding a new record, he fills up the data in text1,text2 and text3 and selects 'add' in the combo. Then he clicks the 'exec' button. ( see fig-1).

The code on 'exec' button, just collects the data from the three textboxes first. Then we form the sql depending on the operation desired. If it is 'add', or 'edit' or 'remove', the command is executed (command.ExceuteNonQuery).

And the screen displays 'done'( see fig-2).

If we want to verify, we retain the key already entered in text1 and click the 'verify' button. Now the row for that key is displayed in the datagrid (see fig-3).

When I want to edit a record, I enter the key in text1 and select 'find' option in the combo. and click 'exec'.

The existing values for that key are shown in text2 and text3. I can now change these values as required and select 'edit' option in the combo. and click 'exec' button'. I get the message 'done'. I can now verify as before. The change is found to have occured.

Thus, it is not very difficult to create a program for CRUD in ASP.net by our own coding.

However, it will be easier still if I can directly edit in the grid itself. The grid is just a table . Then how can we edit the value in that cell? ASP.net magically, creates a textbox in that cell and populates the textbox with the cell value.

This time, I will use DataAdapter. The code is almost the same. I do not want all the records to be displayed. So I make use of paging facility availbale in datagrid. Moreover, it is more natural to look for a name than a key. So, I sort the records based on name. This is how we begin. (see fig-4). Note that we need not change the page sequentially but cna jump at random from any page to any other page. There is also possiblity to control the number of records shown per page. I have arranged to show 5 rows in each page. All such details are specified for datagrid control in the GUI section.

Note the link at the left side of the grid and delete button on the rightside for each row. Let us say, I want to edit 'swaminathan's record. I can search by first letter of the name and located it. (see fig-5).

I now click on the 'edit' link in the row showing swaminathan as name. Immediately, I get 'update' and 'cancel' links and two textboxes appear in the cells. ( see fig-6).

Let me change the name now as Swaminath and Salem. (see fig-7).Now, I click on the update link. The data is updated and the grid shows the modified values as in fig-8.

We can also delete any recod. Let us delete the above record now.Just click the delete button of that row. The record has now been deleted.

Edit and delete commands work for an existing record. Not so for a record to be added. Hence, I have provided a button for this requirement. This is not bound to the grid. When I click on that button, I get a panel having three textboxes. (fig-10).

I enter the data (say, 7777,ROBIN,MYSORE)and confirm.( fig-11).

Finally, we can verify that the record has been added. by entering the key as 7777 and find option. (fig-12).

I now give the full code. It is just a matter of noting the correct syntax.

<%@ Page Language="c#" Debug="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDb" %>

    //-------------------------------
void    showgrid()
{

String s1=
"provider=Microsoft.JET.OLEDB.4.0;data source=g:\\contacts.mdb";
String     sql =
"select * from table1 order by name";

OleDbDataAdapter adapter = new OleDbDataAdapter(sql,s1);
DataSet ds = new DataSet();
adapter.Fill(ds,"table1");

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

}
//------------------------------------

// edit
void job3
(Object o,DataGridCommandEventArgs e)
{
grid2.EditItemIndex=e.Item.ItemIndex;
showgrid();
}

// cancel
void job2
(Object o,DataGridCommandEventArgs e)
{
grid2.EditItemIndex=-1;
showgrid();
}

          // update
void   job1
(Object o,DataGridCommandEventArgs e)
{
String   a = e.Item.Cells[1].Text;
TextBox       text2=
(TextBox) e.Item.Cells[2].Controls[0];
TextBox       text3=
(TextBox)e.Item.Cells[3].Controls[0];

String b=text2.Text;
String c=text3.Text;

String s1=
"provider=Microsoft.JET.OLEDB.4.0;data source=G:\\contacts.mdb";

String     s2 =
"update table1 set table1.name='"+b+"',table1.place='"+c+"' where table1.key='"+a+"' ";

OleDbDataAdapter adapter = new OleDbDataAdapter(s2,s1);
DataSet       ds = new DataSet();
adapter.Fill(ds,"table1");

grid2.EditItemIndex=-1;

showgrid();
}

// paging
void job4(Object o, DataGridPageChangedEventArgs e)
{
grid2.CurrentPageIndex= e.NewPageIndex;
showgrid();
}

// add record
void job5(Object o, EventArgs e)
{
String s1=
"provider=Microsoft.JET.OLEDB.4.0;data source=G:\\contacts.mdb";

String a =   text4.Text;      // key
String b =   text5.Text;     // name
String c =   text6.Text;    // place

String     s2 =
"insert into table1 values('"+a+"','"+b+"', '"+c+"' )     ";

OleDbDataAdapter adapter=
new OleDbDataAdapter(s2,s1);
DataSet ds=new DataSet();
adapter.Fill(ds,"table1");

grid2.EditItemIndex=-1;

showgrid();
label1.Text="added";

panel1.Visible=false;
}

// delete record
void job6
(Object o,DataGridCommandEventArgs e)
{

String a = e.Item.Cells[1].Text;

            String s1=
"provider=Microsoft.JET.OLEDB.4.0;data source=G:\\contacts.mdb";

OleDbConnection connection=new OleDbConnection(s1);
connection.Open();

String     s2 =
"delete from table1    where table1.key='"+a+"' ";

OleDbCommand   command = new OleDbCommand(s2,connection);

command.ExecuteNonQuery();

connection.Close();

grid2.EditItemIndex=-1;

showgrid();
label1.Text="deleted";

}

void job7(Object o, EventArgs e)
{
panel1.Visible=true;
}

</script>

       <asp:datagrid   id=grid2
allowPaging='true'
onPageIndexChanged=job4
pagesize='5'
pagerstyle-mode="numericpages"
runat="server"

OnUpdateCommand    =     job1
OnCancelCommand    =     job2
OnEditCommand      =     job3
OnDeleteCommand    =     job6

AutoGenerateColumns="False">

<Columns>
<asp:EditCommandColumn
EditText="Edit" CancelText="Cancel" UpdateText="update" />

<asp:BoundColumn    HeaderText="key"
readonly=true   DataField="key" />
<asp:BoundColumn    HeaderText="Name" DataField="Name" />

<asp:BoundColumn HeaderText="place" DataField="place" />

<asp:ButtonColumn Text="delete" ButtonType='pushbutton' commandname="delete"/>
</Columns>
</asp:datagrid>
<br>
<asp:button Onclick=job7 runat="server" text="Do you want to ADD RECORD" />

<asp:panel    id='panel1'   runat=server>
key ? <asp:TextBox     id='text4'    runat='server'/>      <br>
name? <asp:textbox    id='text5'   runat='server' />       <br>
place? <asp:textbox    id='text6'    runat='server' /> <br>
<asp:button   text="shall we add?" onclick='job5'    runat=server/>
</asp:panel>
</form>
</body>
</html>

If we go through the code carefully, it is very easy to follow the flow. I think, this is the best method for CRUD operations in webpage.

About Author

Author is doing her MCA in Accet at Karaikudi.

Added on April 24, 2012 Comment

Database editing operations using Visual Edit in ASP.NET Datagrid

About Author

Comments

Post a comment