Code Simplified – Viral Sarvaiya

Code Simplified – Viral Sarvaiya, Web Developer Friends, dot net Developer, Sql Server Developer

Posts Tagged ‘Delete’

Difference between Truncate and Delete

Posted by Viral Sarvaiya on October 7, 2009


Truncate and Delete both are used to delete data from the table. Both these commands will only delete the data of the specified table; they cannot remove the whole table: data along with structure. Now it’s ok that both the SQL statements are used to delete only the data from the table but they both differ from each other in many aspects like syntax, performance, resource uses, etc., so let’s take a look of both of these commands.

Truncate

The TRUNCATE command in SQL removes all rows from a table without logging the individual row deletions in the transaction log. The TRUNCATE statement has the sane functionality as the DELETE statement has in that it deletes the data from the table without modifying or deleting the structure of the table, however you can’t use the WHERE Clause with the TRUNCATE statement.

The Syntax for this statement is:
TRUNCATE TABLE [ { database_name.[ schema_name ]. | schema_name . } ] table_name

Table_name : Is the name of the table to truncate or from which all rows are removed.

To execute it, use a query like the one below:

TRUNCATE TABLE authors

The above command will delete all data from the table author.

Delete

The DELETE command in SQL also removes rows from a table, but it logs the individual row deletions in the transaction log. You can also use the WHERE Clause with the DELETE statement to qualify which rows are to be deleted.

Here I am showing just the simple syntax. For a more detailed explanation of the DELETE syntax, visit this link: DELETE (Transact-SQL)

Syntax:

DELETE FROM TABLE_NAME[ { database_name.[ schema_name ]. | schema_name . } ] table_name

Database_name: Is the name of the database in which the table exists. This is optional. If it is not included, the current database context is assumed.

Schema_name: Is the name of the schema in which the table exists. This is optional. If it is not included, the current database context is assumed.

Table_name : Is the name of the table to truncate or from which all rows are removed.

A simple command looks like this query:

DELETE FROM authors

The above query will delete all data from the table author.

In DELETE statements you can limit your DELETE query using the WHERE clause to delete only particular records that fulfill the condition of the WHERE clause. In this case, only those records matching the WHERE clause will be deleted, not the all records. A limited DELETE query is shown below.

DELETE FROM authors Where AuthorId IN (1,2,3)

This statement only deletes rows from the authors table which have the author ids of 1, 2, or 3.

The Differences between Truncate and Delete

Now above you have seen the DELETE and TRUNCATE statements. Both the statements are similar, but there are many differences that exist between them. Those similarities and differences are explaned below:

TRUNCATE and DELETE remove the data not the structure
Both commands remove rows from a table, but the table structure and its columns, constraints, indexes, and so on remain. To remove the table definition in addition to its data, use the DROP TABLE statement.

Conditional based deletion of data
Conditional based deletion of data means that not all rows are deleted. Let’s suppose I have a table authors and from this table I want to delete the authors that are living in Australia. Let’s examine what our options for doing this with each command.

  • TRUNCATE – In case of the TRUNCATE command we can’t perform the conditional based deletion because there is no WHERE clause allowed with this command.
  • DELETE – THe DELETE command provides the functionality of conditional based deletion of data from the table using the WHERE clause.

Delete and Truncate both are logged operations:

On most of the articles I have read on the Internet, I have seen this written: “delete is a logged operation and truncate is not a logged operation”, which means when we run the delete command it logs (records) the information about the deleted rows and when we run the truncate command it doesn’t log any data. But this is not true; truncate is also a logged operation but in a different way. It uses fewer system and transaction log resources than delete. The TRUNCATE command uses minimum logging resources, which is why it is faster than delete. So both delete and truncate are logged operations, but they work differently as shown below.

  • DELETE is a logged operation on a per row basis. The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. So, in case if you are deleting a huge number of records then it can cause your transaction log to grow. This means the deletion of a huge number of records will use more server resources as it logs each and every row that is deleted. That is why your transaction log will grow very rapidly. Since the delete statement records each deleted row it is also slow. Some people ask that if this is done for each row then why does not Microsoft modify the delete statement to not record each deleted row??? The answer is when you run your databases in full recovery mode, detailed logging is necessary for SQL Server to be able to recover your database to the most recent state.
  • TRUNCATE logs the deallocation of the data pages in which the data exists. TRUNCATE is faster than DELETE due to the way TRUNCATE “removes” rows from the table. It won’t log the deletion of each row; instead it logs the deallocation of the data pages of the table. The TRUNCATE statement removes the data by deallocating the data pages used to store the table data and records only the page deallocation in the transaction log. Actually, TRUNCATE does not remove data, but rather deallocates whole data pages and removes pointers to indexes. The data still exists until it is overwritten or the database is shrunk. This action does not require a lot of resources and is therefore very fast. It is a common mistake to think that TRUNCATE is not logged. This is wrong. The deallocation of the data pages is recorded in the log file. Therefore, “Books Online (BOL)” refers to TRUNCATE operations as “minimally logged” operations. You can use TRUNCATE within a transaction, and when this transaction is rolled-back, the data pages are reallocated again and the database is again in its original, consistent state.

To be more specific lets take a look of an example, which will tell you that truncate is also a logged operation. So take a look of following example.

/*Create a dummy table in a non-production(dummy) database.*/
CREATE TABLE tranTest
(
Id int Identity(1,1),
Name Varchar(100)
)
/*Now insert the records in the tranTest table.*/
INSERT INTO tranTest(Name) VALUES('prashant')
INSERT INTO tranTest(Name) VALUES('prateek')
INSERT INTO tranTest(Name) VALUES('praveen')
INSERT INTO tranTest(Name) VALUES('prakash')
INSERT INTO tranTest(Name) VALUES('prabhat')
/*Then as you know delete is a looged operation that means
in a transaction if we rollback the transaction after deleting
the records from table, it will restore all deleted records.*/
BEGIN TRAN
DELETE FROM tranTest
SELECT * FROM tranTest
ROLLBACK
SELECT * FROM tranTest
/*Now delete all records from the table.*/
DELETE FROM tranTest
/*And Insert new fresh records in the table.*/
INSERT INTO tranTest(Name) VALUES('prashant')
INSERT INTO tranTest(Name) VALUES('prateek')
INSERT INTO tranTest(Name) VALUES('praveen')
INSERT INTO tranTest(Name) VALUES('prakash')
INSERT INTO tranTest(Name) VALUES('prabhat')
/*Then as you now you know that Truncate is also a logged
opertion so it must restore all records that are deleted from
the tranTest table in the below tansaction*/
BEGIN TRAN
TRUNCATE TABLE tranTest
SELECT * FROM tranTest
ROLLBACK
SELECT * FROM tranTest

So when we run the above command and TRUNCATE also restores all the records that means somewhere the logging of the TRUNCATE operation is also being done, but with minimal resources. Here I think minimal resources means you can restore the Truncate statements for that particular session but if you close the connection then i think it will not able to restore your data. BUT Delete will be able to restore your data later also.

Behavior of Delete and Truncate for identity columns
OK, now the case of identity columns. Both the TRUNCATE and DELETE commands behave differently against Identity columns. When we use truncate it will reset the counter used by an identity column for new rows to the seed value defined for the column. But in the case of DELETE it will not reset the counter of your identity column. Rather it maintains the same counter for new rows. In both the cases, if no seed was defined the default value 1 is used. As TRUNCATE resets the identity column counter, in the case where you want to retain the identity counter, use DELETE instead of TRUNCATE.

Why do these two behave differently? I don’t know, but T-SQL is providing you the two ways you can use as needed. In the case where you want all the data from the table deleted and the counter will restarting from 1, then truncate can help you. If you want to delete all the records but don’t want to reset you counter, then delete is there for you.

An example for this is here. :

/* First if you already created the tranTest table then delete it from your database.*/
DROP Table tranTest
/* Then Create a dummy table in a non-production(dummy) database with an Identity column
*/
CREATE TABLE tranTest
(
Id int Identity(1,1),
Name Varchar(100)
)
/* Now insert the records in the tranTest table. */
INSERT INTO tranTest(Name) VALUES('prashant')
INSERT INTO tranTest(Name) VALUES('prateek')
INSERT INTO tranTest(Name) VALUES('praveen')
INSERT INTO tranTest(Name) VALUES('prakash')
INSERT INTO tranTest(Name) VALUES('prabhat')

/* If you run the below querry you'll see that the
   max value of the identity column is 5 caz we have
   inserted only five records in this table. */
SELECT * FROM tranTest

/* Now delete all the records from the table using the delete command. */
DELETE FROM tranTest

/* Now by running the above command your table is empty,
   so insert the new 5 records in the table to see that after
   using the delete command from where identity will tart, fom 1 or from 6. */
INSERT INTO tranTest(Name) VALUES('prashant')
INSERT INTO tranTest(Name) VALUES('prateek')
INSERT INTO tranTest(Name) VALUES('praveen')
INSERT INTO tranTest(Name) VALUES('prakash')
INSERT INTO tranTest(Name) VALUES('prabhat')

/* After running the below query u'll see that your identity now
  starts from 6 because delete do not resets the counter of your
  identity column */
SELECT * FROM tranTest

/* Now drop your table again and create it again. */
DROP TABLE tranTest

/* Create a dummy table again in a non-production(dummy) database
   to see the effect of truncate command on identity columns */
CREATE TABLE tranTest
(
Id int Identity(1,1),
Name Varchar(100)
)

/* And Insert new fresh records in teh table. */
INSERT INTO tranTest(Name) VALUES('prashant')
INSERT INTO tranTest(Name) VALUES('prateek')
INSERT INTO tranTest(Name) VALUES('praveen')
INSERT INTO tranTest(Name) VALUES('prakash')
INSERT INTO tranTest(Name) VALUES('prabhat')

/* Now at this point the counter of this table's
  identity column is 5 taht is the max value of id column */
SELECT * FROM tranTest

/* Then truncate the table. */
TRUNCATE TABLE tranTest

/* and insert new records */
INSERT INTO tranTest(Name) VALUES('prashant')
INSERT INTO tranTest(Name) VALUES('prateek')
INSERT INTO tranTest(Name) VALUES('praveen')
INSERT INTO tranTest(Name) VALUES('prakash')
INSERT INTO tranTest(Name) VALUES('prabhat')

/* Now you'll see that after truncating the table the
  identity is reset to its seed value. */
SELECT * FROM tranTest

/* So this example explains the beahviour of both of these command for Identity columns. */

TRUNCATE is a DDL command whereas DELETE is a DML command
This is also a common difference you might have read in many articles. That is TRUNCATE is a DDL (data definition language) operation and DELETE is a DML (data manipulation language) operation. Yes according to SQL server it’s true. But why it is so, why is TRUNCATE DDL and DELETE DML? Let’s look at this;

When we run the TRUNCATE command it puts a “Schema modification (Sch-M)” lock on the table. What is “schema modification (Sch-M)”?

The Database Engine uses schema modification (Sch-M) locks during a table data definition language (DDL) operation, such as adding a column or dropping a table. During the time that it is held, the Sch-M lock prevents concurrent access to the table. This means the Sch-M lock blocks all outside operations until the lock is released. Now you’ll ask how it blocks any modification to the table when in the case of TRUNCATE we are performing modifications because we are deleting data? But deleting the data is the one side of coin only. What we see with the internal workings of truncate is because as you read above, that it doesn’t remove the data. Rather it deallocates the data pages. Because TRUNCATE doesn’t perform any data modification in the table that is why the DELETE TRIGGER is not called. I think we are not modifying the data of the table, BUT as you know TRUNCATE resets the Identity counter of the column in the table, which means the TRUNCATE is modifying the table definition or structure, which comes under the DDL operations. Also when you are truncating a table, you can’t modify or add any data to the table. So, to become a DDL operation you have to fulfill some of the conditions written below:

  • Modifying a table structure or definition comes under DDL operations, and
  • When you are modifying the table structure, you can’t access the table to do any data modification.

Since TRUNCATE is doing all the activities above, that proves that TRUNCATE is a DDL operation.

Now we move to the DELETE command. In case of the DELETE command I am not sure which lock is implemented, but as we know and you can read above that DELETE command deletes the rows one by one. It is modifying the data by deleting it from the table, and because DELETE performs data modifications that is why the DELETE TRIGGER is called. The DELETE command does not modify the table structure in any manner, such as like how TRUNCATE modifies the identity column by resetting its value.

To become a DML operation you have to fulfill some of the conditions written below:

  • Modifying the table data.
  • When you are modifying the table data in the mean time you can’t perform any table structure modification on the table.

Here the DELETE command is modifying the data of the table and also when delete statement is running you can’t modify the table structure. So we can say that DELETE is a DML operation.

Behavior of Truncate and Delete for Triggers
Triggers are important topic in SQL Server, and here I am talking about how both TRUNCATE and DELETE behave differently for Triggers. As you all know triggers fire whenever any data modification happens in the table. In case of TRUNCATE and DELETE, because they are deleting data from the table, the DELETE TRIGGER will fire if present and also the INSTEAD OF and AFTER triggers can be triggered if present. The INSERT and UPDATE triggers will not be fired here.

So let’s take both one by one:

  • TRUNCATE – When we run the TRUNCATE command to delete all the rows of a table it actually doesn’t remove any row rather it deallocates the data pages. So in the case of the TRUNCATE command, triggers will not be fired because here no modification takes place. As we know that TRUNCATE is a DDL command, and DDL commands doesn’t modify your data; instead they modify your table structure and definition.
  • DELETE – In case of DELETE the DELETE trigger will be fired if present and also if the INSTEAD OF and AFTER triggers for DELETE TRIGGER is present then they also will be fired. As we know that delete command is a DML command and it deletes the data on row-by-row basis. So that means delete is modifying the data by deleting it from the table. As we know delete is a DML command and trigger will be fired whenever any DML operation on the table takes place.

Where we can use these statements
There are some restrictions on the use of both of these statements as follows:

For Delete

  • The DELETE statement may fail if it violates a trigger or tries to remove a row referenced by data in another table with a FOREIGN KEY constraint. If the DELETE removes multiple rows, and any one of the removed rows violates a trigger or constraint, the statement is canceled, an error is returned, and no rows are removed.

For Truncate
You cannot use TRUNCATE TABLE on tables that:

  • Are referenced by a FOREIGN KEY constraint.
  • Participate in an indexed view.
  • Are published using transactional replication or merge replication.

Permissions of performing TRUNCATE or DELETE operation
For using both the statements you need some permissions on the server, which decides whether you can perform the Delete or Truncate action on the table or not. To truncate a table, you need at least ALTER permissions on the table (which is granted by default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles). To delete rows using the delete statement, you only need the DELETE permission.

for more information click here

more link..

http://www.sql-server-performance.com/faq/delete_truncate_difference_p1.aspx

Advertisements

Posted in Sql Server | Tagged: , , | 1 Comment »

Gridview – insert, update, delete

Posted by Viral Sarvaiya on July 27, 2009


Pre-requisites

Your project or website must be ASP.NET AJAX enabled website. Because we are going to add the GridView in an UpdatePanel. So your GridView control will be look smart without unnecessary postbacks. You need to create a Customer Table with 6 columns for Customer Code[Code], Name[Name], Gender[Gender], City[City], State[State] and Customer Type[Type], with your desired data types. Then create a class file in your App_Code folder and create a Default.aspx along with code-behind file Default.aspx.cs.

Step 1. Create Class File ‘CustomersCls.cs’

We need to create a class file to do database manipulations such as select, insert, delete and update data in the Customer Table. So we add a class file as ‘CustomersCls.cs’ in App_Code section.

Let us write five methods in the class file as follows

public void Insert(string CustomerName, string Gender, string City, string State, string CustomerType)
{
// Write your own Insert statement blocks
}
public DataTable Fetch()
{
// Write your own Fetch statement blocks, this method should return a DataTable
}
public DataTable FetchCustomerType()
{
// Write your own Fetch statement blocks to fetch Customer Type from its master table and this method

//should return a DataTable
}
public void Update(int CustomerCode, string CustomerName, string Gender, string City, string State,string CustomerType)
{
// Write your own Update statement blocks.
}
public void Delete(int CustomerCode)
{

// Write your own Delete statement blocks.
}

Step 2: Make Design File ‘Default.aspx’

In the Default.aspx page, add an UpdatePanel control. Inside the UpdatePanel, add a GridView, set AutoGenerateColumns as False. Change the ShowFooter Flag to True and set the DataKeyNames your column name for Customer Code and Customer Type, in our case it is Code and Type. Then click on the Smart Navigation Tag of the GridView control, choose Add New Column and add 5 BoundField columns with DataField values as Name, Gender, City, State and Type, plus 2 CommandField columns with one for Edit/Update and another for Delete functions. Now your GridView control is ready. But as first step, we need to add some new records into the database. For that we need to place the controls in the Footer row. So we have to convert all these BoundField columns as TemplateField columns. To do this again, click on the Smart Navigation Tag on the GridView choose Edit Columns, the Field’s property window will open. Select column by column from Name to Customer Type, include also Edit column, and select ‘Convert this field into a TemplateField’. Now all the BoundField columns will be
converted to TemplateField columns except the Delete column.

Column[0] – Name

Right click on the GridView control, select Edit Template, choose column[0] – Name, you can view a label placed in the ItemTemplate section and a TextBox placed in the EditItemTemplate section. Add another Texbox in the FooterTemplate section and name it as txtNewName.

Column[1] – Gender

Now again select Edit Template, choose column[1] – Gender, replace the TextBox with a DropDownList,name it as cmbGender, add Male and Female as their ListItem values. On the Edit DataBindings of the cmbGender, add Eval(”Gender”) to its selectedvalue. Add another DropDownList in the FooterTemplate section and name it as cmbNewGender.

Column[2] –City & Column[3] – State

Add Texboxes in both column’s FooterTemplate section and name it as txtNewCity and txtNewState respectively.

Column[4] – Type

In this column’s EditItemTemplate section, replace the TextBox with a DropDownList, name it as cmbType. Also add another DropDownList in the FooterTemplate section and name it as cmbNewType. Both these DropDownList’s we are going to fill with dynamic data from database. So specify both DropDownList’s DataTextField and DataValueField as Type.

Column[5] – Edit

Just add a link button into the FooterTemplate section, specify its CommandName property as ‘AddNew’.

For your persual, we have provided the complete source code of the GridView control below. The State column in our sample is read-only. So you cannot find TextBox for that column in the EditItemTemplate section.

Source Code of the GridView Control

<asp:GridView ID=”GridView1″ runat=”server” AutoGenerateColumns=”False” DataKeyNames=”Code, Type”

OnRowCancelingEdit=”GridView1_RowCancelingEdit” OnRowDataBound=”GridView1_RowDataBound”

OnRowEditing=”GridView1_RowEditing” OnRowUpdating=”GridView1_RowUpdating”

OnRowCommand=”GridView1_RowCommand” ShowFooter=”True” OnRowDeleting=”GridView1_RowDeleting”>
<Columns>

<asp:TemplateField HeaderText=”Name” SortExpression=”Name”> <EditItemTemplate>
<asp:TextBox ID=”txtName” runat=”server” Text=’<%# Eval(”Name”) %>’></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID=”txtNewName” runat=”server”></asp:TextBox> </FooterTemplate>
<ItemTemplate>
<asp:Label ID=”Label2″ runat=”server” Text=’<%# Bind(”Name”) %>’></asp:Label>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText=”Gender”>
<EditItemTemplate>
<asp:DropDownList ID=”cmbGender” runat=”server” SelectedValue=’<%# Eval(”Gender”) %>’>
<asp:ListItem Value=”Male” Text=”Male”></asp:ListItem>
<asp:ListItem Value=”Female” Text=”Female”></asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID=”Label2″ runat=”server” Text=’<%# Eval(”Gender”) %>’></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:DropDownList ID=”cmbNewGender” runat=”server” >
<asp:ListItem Selected=”True” Text=”Male” Value=”Male”></asp:ListItem>
<asp:ListItem Text=”Female” Value=”Female”></asp:ListItem> </asp:DropDownList>
</FooterTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText=”City”>
<EditItemTemplate>
<asp:TextBox ID=”txtCity” runat=”server” Text=’<%# Bind(”City”) %>’></asp:TextBox>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID=”txtNewCity” runat=”server” ></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID=”Label3″ runat=”server” Text=’<%# Bind(”City”) %>’></asp:Label>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText=”State” SortExpression=”State”>
<EditItemTemplate>
<asp:Label ID=”Label1″ runat=”server” Text=’<%# Eval(”State”) %>’></asp:Label>
</EditItemTemplate>
<FooterTemplate>
<asp:TextBox ID=”txtNewState” runat=”server” ></asp:TextBox>
</FooterTemplate>
<ItemTemplate>
<asp:Label ID=”Label4″ runat=”server” Text=’<%# Bind(”State”) %>’></asp:Label>
</ItemTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText=”Type”>
<EditItemTemplate>
<asp:DropDownList ID=”cmbType” runat=”server” DataTextField=”Type” DataValueField=”Type”>

</asp:DropDownList>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID=”Label5″ runat=”server” Text=’<%# Eval(”Type”) %>’></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:DropDownList ID=”cmbNewType” runat=”server” DataTextField=”Type” DataValueField=”Type”>

</asp:DropDownList>
</FooterTemplate>
</asp:TemplateField>

<asp:TemplateField HeaderText=”Edit” ShowHeader=”False”>
<EditItemTemplate>
<asp:LinkButton ID=”LinkButton1″ runat=”server” CausesValidation=”True” CommandName=”Update”

Text=”Update”></asp:LinkButton>
<asp:LinkButton ID=”LinkButton2″ runat=”server” CausesValidation=”False” CommandName=”Cancel”

Text=”Cancel”></asp:LinkButton>
</EditItemTemplate>
<FooterTemplate>
<asp:LinkButton ID=”LinkButton2″ runat=”server” CausesValidation=”False” CommandName=”AddNew”

Text=”Add New”></asp:LinkButton>
</FooterTemplate>
<ItemTemplate>
<asp:LinkButton ID=”LinkButton1″ runat=”server” CausesValidation=”False” CommandName=”Edit”
Text=”Edit”></asp:LinkButton>
</ItemTemplate>
</asp:TemplateField>
<asp:CommandField HeaderText=”Delete” ShowDeleteButton=”True” ShowHeader=”True” />
</Columns>
</asp:GridView>

Step 3: Make Code-behind File ‘Default.aspx.cs’

Now we are going to do the code-behind part of this page. Les us explain you event by event coding on each methods. In the code-behind page, create an instance for the Customer class as follows

CustomersCls customer=new CustomersCls();

Then create a private method ‘FillCustomerInGrid’ to retrieve the existing customer list from the database and bind it to the GridView. The CustomersCls class’s Fetch() method is used and it returns the data to a DataTable. On first stage it will return empty rows. So you cannot see any header,data or even footer rows of the GridView control. You can only see an empty space or you see only the EmptyDataText. So you cannot add any new data from the footer row.

private void FillCustomerInGrid()
{
DataTable dtCustomer= customer.Fetch();

if (dtCustomer.Rows.Count>0)
{
GridView1.DataSource = dtCustomer;
GridView1.DataBind();
}
else
{
dtCustomer.Rows.Add(dtCustomer.NewRow());
GridView1.DataSource = dtCustomer;
GridView1.DataBind();</span>

int TotalColumns = GridView1.Rows[0].Cells.Count;
GridView1.Rows[0].Cells.Clear();
GridView1.Rows[0].Cells.Add(new TableCell());
GridView1.Rows[0].Cells[0].ColumnSpan = TotalColumns;
GridView1.Rows[0].Cells[0].Text = “No Record Found”;
}
}

In this article, we have provided a workaround to fix this problem. Closely look at the method FillCustomerInGrid, there is a conditional statement to check the rows exists in DataTable or not.Now go to the else part of the if statement, see the block of code we provided there. Simply we have added an empty row to the DataTable. Then bind it to the GridView control. To give a professional look to the GridView control, we do little bit more by providing ColumnSpan and set a Text as “No Record Found”, this text will be displayed if the GridView is empty without any rows and you can see
both the Header and Footer of the GridView control.

Initialize GridView control

In the page load event, we have to call this FillCustomerInGrid method as follows,

protected void Page_Load(object sender, EventArgs e)
{
If (!IsPostBack)
{
FillCustomerInGrid();
}
}

Fill DropDownList in GridView with dynamic values

In column[4] – Type, there are two DropDownList controls, one in the EditItemTemplate section (cmbType) and another in FooterTemplate (cmbNewType). We have to fill both these DropDownList controls with some dynamic data. If you look at our CustomersCls class, we have a separate method called FetchCustomerType. In the RowDataBound event of the GridView control insert the following code.

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
DropDownList cmbType = (DropDownList)e.Row.FindControl(”cmbType”);

if (cmbType != null)
{
cmbType.DataSource = customer.FetchCustomerType();
cmbType.DataBind();
cmbType.SelectedValue = GridView1.DataKeys[e.Row.RowIndex].Values[1].ToString();
}
}

if (e.Row.RowType == DataControlRowType.Footer)
{
DropDownList cmbNewType = (DropDownList)e.Row.FindControl(”cmbNewType”);
cmbNewType.DataSource = customer.FetchCustomerType();
cmbNewType.DataBind();
}
}

Previously in this article, we have set the DataKeyNames values as Code, Type. If you see in the above code, we use one of the DataKeyNames value as the SelectedValue for the cmbType control, this is to retain the value of the cmbType in EditMode. The index value of Code is 0 and Type is 1. So we use as follows

cmbType.SelectedValue = GridView1.DataKeys[e.Row.RowIndex].Values[1].ToString();

So far we have initialized the GridView control with the datatable and also make some values to be filled in the Footer DropDownList cmbNewType. Run the application, you can see the GridView only with the Footer row and data in the cmbNewType control. Let us start to code for adding new records into the database when we click ‘Add New’ linkbutton.

Add New Records from GridView control

Create an event for the GridView’s RowCommand and add the following code in it.

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals(”AddNew”))
{
TextBox txtNewName=(TextBox)GridView1.FooterRow.FindControl(”txtNewName”);
DropDownList cmbNewGender = (DropDownList)GridView1.FooterRow.FindControl(”cmbNewGender”);
TextBox txtNewCity = (TextBox)GridView1.FooterRow.FindControl(”txtNewCity”);
TextBox txtNewState = (TextBox)GridView1.FooterRow.FindControl(”txtNewState”);
DropDownList cmbNewType = (DropDownList)GridView1.FooterRow.FindControl(”cmbNewType”);

customer.Insert(txtNewName.Text, cmbNewGender.SelectedValue, txtNewCity.Text, txtNewState.Text,
cmbNewType.SelectedValue) ;
FillCustomerInGrid();
}
}

In the above code, we are declaring and finding the controls in the GridView’s footer section and use the CustomersCls class insert method to add the new data into the database. Then we are calling the FillCustomerInGrid method to fill the GridView control with the newly inserted values. Now save everything and run your application. Put some test data in the Textboxes and select some values in the DropDownLists and click on the Add New linkbutton. You can see data inserted into the database
and listed in the GridView control.

Edit and Update in GridView

In the RowEditing event of the GridView, add the following lines of code. This will switch a specific row of the GridView to Edit Mode.

protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex = e.NewEditIndex;
FillCustomerInGrid();
}

After the GridView swithes to Edit Mode, you can view the TextBoxes and DropDownlList controls along with Update and Cancel linkbuttons in the Edit mode. To cancel this action, add the following two lines of code in the GridView’s RowCancelingEdit event.

protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
GridView1.EditIndex = -1;
FillCustomerInGrid();
}

You can update the data to the customer table, by adding the following lines of code in the GridView’s RowUpdating event.

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
TextBox txtName = (TextBox)GridView1.Rows[e.RowIndex].FindControl(”txtName”);
DropDownList cmbGender = (DropDownList)GridView1.Rows[e.RowIndex].FindControl(”cmbGender”);
TextBox txtCity = (TextBox)GridView1.Rows[e.RowIndex].FindControl(”txtCity”);
DropDownList cmbType = (DropDownList)GridView1.Rows[e.RowIndex].FindControl(”cmbType”);

customer.Update(GridView1.DataKeys[e.RowIndex].Values[0].ToString(),txtName.Text,

cmbGender.SelectedValue,txtCity.Text,CustomerType.SelectedValue);
GridView1.EditIndex = -1;
FillCustomerInGrid();
}

The above block of codes in RowUpdating event, finds the control in the GridView, takes those values in pass it to the CustomersCls class Update method.

The first parameter GridView1.DataKeys[e.RowIndex].Values[0].ToString() will return the Code of the Customer. That is the unique code for each customer to perform update function.

Delete in GridView

To delete a row from the customer table, add the following lines of code in the GridView’s RowDeleting event. Here you have to pass the unique Code of customer which is in GridView1.DataKeys[e.RowIndex].Values[0].ToString() to the Delete method of the CustomersCls class.

protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
customer.Delete(GridView1.DataKeys[e.RowIndex].Values[0].ToString());
FillCustomerInGrid();
}

Posted in ASP.NET | Tagged: , , , , , | 2 Comments »

 
%d bloggers like this: