Monday, 6 January 2014

Asp.Net GridView – How to Update Delete Insert in GridView in c#.net

In last C# tutorials we learned to bind Gridview with SQL datasource in code behind. In previous article, we explained what is asp.net Gridview? And why we need it?

Now, in this article we are going to learn in simple way to update, delete and insert in Gridview using C#.net & framework 4.0.

We are taking here the example of "Northwind" database which comes with SQL server 2005. In this asp.net gridview example we would perform operations like insert, update and delete on this table thru Gridview.

Steps to update delete insert in Gridview in C#.net

  1. Create a new web application and name it "GridviewUpdateInsertDelete". Select .Net Framework 4.0 as shown in below image and click on "Ok" button.
    Gridview - Create a new web application
    Gridview - Create a new web application
  2. It would open "Default.aspx" in Source mode. Click on "Design" as shown below.
    Gridview - Selecting design mode
    Gridview - Selecting design mode
  3. Now, take Gridview control on your page. To take GridView control on your page click on "Toolbox" and drag & drop GridView to your page.
    Gridview - Adding asp.net gridview
    Gridview - Adding asp.net gridview
  4. Once you place aspx GridView control on your page, it would look like below screenshot.
    Gridview on a aspx page
    Gridview on a aspx page
  5. Change the name property of asp gridview from "Gridview1" to "grdEmployee". You can change the name of the gridview thru Properties window.
  6. Now it’s time to bind some datasource to Gridview to show data on Gridview. To accomplish this, click on Gridview and then click on link button as shown in below image.
    Gridview - Link button to add properties
    Gridview - Link button to add properties
  7. It would open "Gridview task" window.
    1. Click on dropdown button of "Choose Data Source".
    2. And select "New Data Source".
    Gridview - Select SqlDataSource at design time.
    Gridview - Select SqlDataSource at design time.
  8. It would open a "Data Source Configuration Wizard" to select a datasource. Click on "Database" and specify name of the datasource as "sqlDSEmployee" and click on "ok" button.
    Gridview - Select SQL Database to bind with Gridview
    Gridview - Select SQL Database to bind with Gridview
  9. Click on "New Connection".
    Gridview - SQL datasource connectionstring
    Gridview - SQL datasource connectionstring
  10. Enter your server name and click on "Refresh" button. Now select your "Database" name and click on "Ok" button.
    Gridview - Select Database
    Gridview - Select Database
  11. It would show the connection string details. Verify and click on "Next" button.
    Gridview - Connection string
    Gridview - Connection string
  12. Save the connection string and click on "Next" button.
    Gridview - Save Connection string
    Gridview - Save Connection string
  13. Now we need to select the database Table from which we need to retrieve the data. Select "Employee" table and select fields like "LastName", "FirstName" and "Title". And click on "Next" button.
    Gridview -Select table and table columns
    Gridview -Select table and table columns
  14. Click on "Finish" button.
  15. Now your Gridview would look like below image.
    Gridview - bind with SQLdatasource in design time
    Gridview - bind with SQLdatasource in design time
  16. We are done with database connection. Now the next steps are related to insert, update and delete in Gridview in asp.net. Click on Gridview control and open properties window. Change the values of "AutoGenerateDeleteButton" and "AutoGenerateEditButton" properties to "True" as shown in below image. It would add "Edit" and "Cancel" link buttons on your gridview.
    Gridview Properties - AutoGenerateDeleteButton & AutoGenerateEditButton set to true
    Gridview Properties - AutoGenerateDeleteButton & AutoGenerateEditButton
  17. We are half done with gridview edit and update. To make it complete, we need to add "Update" and "Delete" commands in SQLDatasource in source window. Go to "Source" and add below lines under "asp:SqlDataSource" tag.
    UpdateCommand="UPDATE [Employees] Set [LastName]=@LastName, [FirstName]=@FirstName, [Title]=@Title Where [EmployeeID]=@EmployeeID"
    DeleteCommand="DELETE FROM [Employees] Where [EmployeeID]=@EmployeeID"
    So the final complete source code of "asp:SqlDataSource" tag would look like -
    <asp:SqlDataSourceID="sqlDSEmployee"runat="server"ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"
    SelectCommand="SELECT [EmployeeID], [LastName], [FirstName], [Title] FROM [Employees]"
    UpdateCommand="UPDATE [Employees] Set [LastName]=@LastName, [FirstName]=@FirstName, [Title]=@Title Where [EmployeeID]=@EmployeeID"
    DeleteCommand="DELETE FROM [Employees] Where [EmployeeID]=@EmployeeID">
    </asp:SqlDataSource>
    Above source code includes all three command i.e gridview edit command, gridview update command and gridview delete command.
  18. Now we are done with Insert and Update in GridView in C#.net. The only part left is now Insertion. To insert a new record we need to place some textboxes and label to accept LastName, FirstName and Title to insert into the gridview. Place four labels and three textboxes and name them as shown in below image.
    Gridview - Adding labels and textboxes to add record.
    Gridview - Adding labels and textboxes to add record.

    The source code to add above button, 4 labels and 3 textboxes is given below. You can use below source code.
    <p>
    <asp:ButtonID="btnInsert"runat="server"OnClick="btnInsert_Click"Text="Add New"/>
    </p>
    <p>
    <asp:LabelID="lblLastName"runat="server"Text="Last Name"></asp:Label>&nbsp;&nbsp;
    <asp:TextBoxID="txtLastName"runat="server"Width="260px"Enabled="False"MaxLength="20"></asp:TextBox>&nbsp;&nbsp;
    <asp:LabelID="lblFirstName"runat="server"Text="First Name"></asp:Label>&nbsp;&nbsp;
    <asp:TextBoxID="txtFirstName"runat="server"Width="135px"Enabled="False"MaxLength="10"></asp:TextBox>&nbsp;&nbsp;
    <asp:LabelID="lblTitle"runat="server"Text="Title"></asp:Label>&nbsp;&nbsp;
    <asp:TextBoxID="txtTitle"runat="server"Width="270px"Enabled="False"MaxLength="30"></asp:TextBox>
    </p>
    <p>
    <asp:LabelID="lblError"runat="server"ForeColor="Red"></asp:Label>
    </p>
  19. Now double click on "Add New" button. It would open a "Default.aspx.cs" file with btnInsert_Click event. Enter below code in this event.
    protectedvoid btnInsert_Click(object sender, EventArgs e)
            {
    // If Button.Text = Add new then Enable all three textboxes.
    if (btnInsert.Text.Equals("Add New"))
                {
                    txtLastName.Enabled = true;
                    txtFirstName.Enabled = true;
                    txtTitle.Enabled = true;
    // Change the text of button to "Insert".
                    btnInsert.Text = "Insert";
                }
    elseif (btnInsert.Text.Equals("Insert"))
                {
    // If any of the textbox is empty then show error to enter into all three boxes.
    if (txtLastName.Text.Equals(string.Empty) || txtFirstName.Text.Equals(string.Empty) || txtTitle.Text.Equals(string.Empty))
                    {
                        lblError.Text = "Please enter values for Last Name, First Name and Title fields.";
                    }
    else
                    {
    // Insert command
                        sqlDSEmployee.InsertCommand = string.Format("INSERT INTO [Employees] ([LastName], [FirstName], [Title]) VALUES ('{0}', '{1}', '{2}')", txtLastName.Text, txtFirstName.Text, txtTitle.Text);
                        sqlDSEmployee.Insert();
    // Bind the data to GridView
                        grdEmployee.DataBind();
    //Disable all textboxes.
                        txtLastName.Enabled = false;
                        txtFirstName.Enabled = false;
                        txtTitle.Enabled = false;
    //Remove the contents of textboxes.
                        txtLastName.Text = string.Empty;
                        txtFirstName.Text = string.Empty;
                        txtTitle.Text = string.Empty;
    //Remove error if any and change the button.Text = "Add New"
                        lblError.Text = string.Empty;
                        btnInsert.Text = "Add New";
                    }
                }
            }
  20. Now we are done with Update, delete and insert in Gridview. Execute the application by pressing F5 button and it would show below page.
    Gridview - Insert Update Delete final page.
    Gridview - Insert Update Delete
I hope this gridview example in asp.net helps you to configure the GridView with insert, update and delete operations. If you find this article helpful, then could you please share the article on your social network?

Following articles might be of your interest

Protected by Copyscape Plagiarism Check

Sharing is Caring...



Monday, 14 October 2013

C#.Net GridView Example - How To: Bind Gridview in Asp.net with SQLDataSource in Code behind

In one of previous article we have seen how to bind the Gridview with SQLDataSource at Design time. The article also explained what is GridView and why we need it?

However, sometimes it is needed to bind DataSource with GridView at runtime in code behind. In this article let's learn how to bind database with GridView in Code behind. Once the SQLDataSource is bound, you will be able to perform insert, update, delete in Gridview.

So in this article let's take an example of "NorthWind" database which comes with SQL Server 2005. We will bind "Employee" table from "NorthWind" database to "SqlDataSource" and then will bind that "SqlDataSource" to GridView in asp.net. Please note that "NorthWind" database doesn't come with SQL Server 2008. Hence, if you are using SQL server 2008 then please download "NorthWind" database backup from Microsoft's link and restore it in your machine.

How to bind GridView with SQLDataSource in code behind?

  1. Create a new Asp.net Web application.
  2. Add GridView in asp.net page. To add asp.net GridView in your page, drag & drop gridview control from toolbox as shown below -
    GridView control in toolbox
    GridView control in toolbox
  3. As soon as you drag & drop GridView in Asp.net, it would look like below –
    GridView on web page after drag & drop
    GridView on web page after drag & drop
    Observe that the Gridview is showing column name as "Column0", "Column1" and "Column2". The reason is DataSource not yet bind to GridView.
  4. Now go to aspx.cs page where you added the GridView. In this example I have added GridView in "Default.aspx" hence I have opened "Default.aspx.cs" page.
  5. Now write below code in Page_Load Event -
       protected void Page_Load(object sender, EventArgs e)
       {
           // ConnectionString to NorthWind Database.
           string connectionString = "Data Source=localhost;Initial Catalog=NorthWind;Integrated Security=True";

           // Create SQLDataSource.
           SqlDataSource sqlDataSource = new SqlDataSource();
           sqlDataSource.ID = "SqlDataSource123";
           this.Page.Controls.Add(sqlDataSource);

           // Bind ConnectionString to SQLDataSource.
           sqlDataSource.ConnectionString = connectionString;
           // Retrieve records with only 5 Columns from Employees table of NorthWind Database.
           sqlDataSource.SelectCommand = "SELECT EmployeeID, LastName, FirstName, Title, BirthDate  FROM Employees";

           // Bind SQLDataSource to GridView after retrieving the records.
           GridView1.DataSource = sqlDataSource;
           GridView1.DataBind();
       }
    That's it. You are done with the code part. Save the file.
  6. Now execute the Web Application and see if the records from NorthWind database are populating on your C# gridview. Final output would like below screen -
    GridView Showing records bind in Code Behind
    GridView - SQLDataSource bind in Code Behind
Now look at the format of the Birthdate column. The birthdates are showing in default datetime format of GridView. You can change the format of datetime column. Please refer previous post on how to change the format of DateTime column in GridView.

From this gridview example in asp.net we have learnt to bind the SQLdatasource at code behind. Here is another article that shows how to bind the Gridview with SQLDataSource at Design time.

I hope this gridview example in asp.net helps you to configure the GridView with SQLDataSource in code behind. If you find this article helpful, then could you please share the article on your social network?

Following articles might be of your interest

Protected by Copyscape Plagiarism Check

Sharing is Caring...



Thursday, 5 September 2013

C#.Net GridView Example - How To: Bind Gridview in Asp.net using SQLDataSource at Design time.

In previous articles we learned how to enable tracing in asp.net. Now in this article let's learn how to bind database with GridView in Design time.

What is C#.net Gridview?

GridView is most commonly used asp.net control that retrieves data from database and shows it on asp.net web page. The GridView shows the data in tabular format. This control is very versatile and so features rich for insertion, deletion and modifications of data from database that is showing in Gridview.

Gridview prepares it's columns and rows automatically with default options and then displays it immediately. The main advantage of this control is that it has a very rich set of automatic controls that let you perform basic CRUD operations i.e. "Create, Read, Update and Delete", which don't need much complex code.

Gridview in asp.net must be bound to any DataSource in order to show the data on webpage. SQLDatasource is one of the DataSource that GridView supports. The task of the SQLDataSource is to retrieve the data from SQL server Database.

We can bind SQLDataSource with GridView in two ways i.e either at Design time or in code behind. This article covers step by step procedure to bind the GridView in asp.net using SQLDataSource at Design Time. In this Gridview example the final output will be like below screenshot -
GridView bind with SQLDataSource
GridView: Bind with SQLDataSource : Showing Data
In above image, asp.net GridView is showing 6 columns and 3 rows of data. In this example I am showing information about an employee of an organization. You may want to see other article that explains simple steps to insert, update and delete in asp.net Gridview.

Prerequisite of the article:

  1. This article is based on VS-2010 and SQL server 2008/2005.
  2. Before proceed with the article; your database should be ready. If you do not have any yet, then execute below queries on your SQL server 2008/2005 to create a Database "EmployeeDatabase" with table "Employee".
USE [master]
GO

/****** Object: Database [EmployeeDatabase] Script Date: 08/30/2013 10:24:17 ******/
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'EmployeeDatabase')
DROP DATABASE [EmployeeDatabase]
GO

CREATE DATABASE [EmployeeDatabase]

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/****** Object: Table [Employee] Description: Drop the Table EMPLOYEE if already exist ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[EmployeeDatabase].[dbo].[Employee]') AND type in (N'U'))
DROP TABLE [EmployeeDatabase].[dbo].[Employee]
GO

/****** Object: Table [Employee] Description: Create the Table EMPLOYEE ******/CREATE TABLE [EmployeeDatabase].[dbo].[Employee](
   [EmployeeID] [int] NOT NULL IDENTITY(1,1) PRIMARY KEY,
   [First_Name] [nvarchar](50) NOT NULL,
   [Last_Name] [nvarchar](50) NOT NULL,
   [Gender] [nvarchar](1) NOT NULL,
   [Birth_Date] [date],
   [Department] [nvarchar](50) NOT NULL,
) ON [PRIMARY]
GO

/****** Object: Table [Employee] Description: Insert few rows in Table EMPLOYEE ******/
INSERT INTO [EmployeeDatabase].[dbo].[Employee]([First_Name],[Last_Name],[Gender],[Birth_Date],[Department]VALUES ('FIRSTNAME1','LASTNAME1','M','19750320','ENGG')

INSERT INTO [EmployeeDatabase].[dbo].[Employee]([First_Name],[Last_Name],[Gender],[Birth_Date],[Department]VALUES ('FIRSTNAME2','LASTNAME2','F','19901030','ENGG')I

NSERT INTO [EmployeeDatabase].[dbo].[Employee]([First_Name],[Last_Name],[Gender],[Birth_Date],[Department]) VALUES ('FIRSTNAME3','LASTNAME3','M','19880714','QUALITY')
GO

SELECT * FROM [EmployeeDatabase].[dbo].[Employee]

How to bind Asp.net GridView?

  1. Create a new Asp.net Web application.
  2. Add C#.Net GridView in your asp.net page. To add GridView in your page, drag & drop Asp.net gridview control from toolbox as shown below -
    GridView control in toolbox
    GridView control in toolbox
  3. As soon as you drag & drop GridView in Asp.net, it would look like below image –
    GridView on web page after drag & drop
    GridView on web page after drag & drop
    Observe that the Gridview is showing column name as "Column0", "Column1" and "Column2". The reason is DataSource not yet bind to GridView.
  4. To bind the SQLDataSource with GridView, click on down arrow of "Choose Data Source" and select "<New data source>".
    Create a new DataSource for Gridview
    Create a new DataSource for Gridview
  5. It would open "Data Source Configuration Wizard". Please select here "SQL Database", then specify an ID of the DataSource and click on "Ok" button.
    GridView Datasource Configuration Wizard
    GridView Datasource Configuration Wizard
    Now, specify an ID for the data source. By default "Data source configuration wizard" prompts with default data source name "SqlDataSource1". I have kept it as it is, you can change it to anything. Click on Ok button.
  6. "Configure Data Source – SqlDataSource1" window will open. Click on "New Connection" button and it will open "Add connection" window.
  7. Enter here all the details for ex : server name, database name etc. All these details will create a ConnectionString for us.
    GridView Add Connection Wizard
    GridView Add Connection Wizard
    • Data source: It shows DataSource name. I have used "Microsoft SQL server (Sqlclient)".
    • Server Name: If you not know the server name/instance, you can click on down arrow on "Server name" combo box. It will show you all the available SQL server instances. My Server instance name is "localhost" hence I have entered "localhost" in this box.
    • Log on to the server: Select the appropriate option. I am using windows Authentication.
    • Connect to a database: Now select here database name to which you want to connect. I have already created a Database "EmployeeDatabase" before starting these steps. Hence I am using "EmployeeDatabase" here. Clicking on down arrow of this combo box will prompt you all the available Databases, choose whatever you want.
    • Click on "Test Connection". If everything is correct then it would prompt you a message "Test connection succeeded." Verify all settings again if you do not see this message.
    • Click on Ok button.
  8. It will now show you the complete connection string as shown below. Click on "Next" button.
    GridView Choosing Data connection
    GridView Choosing Data connection
  9. Now a window will open to save the connection string with a name. If you save it with a name then it will act as a shortcut for the connection and then you can use it anywhere also.
    GridView Save Connection String
    GridView Save Connection String
    Click on next button.
  10. It will open a window where you can customize your "select" statement (query). I have kept it as it is. Click on "Next" button.
    GridView Configure Select Statement
    GridView Configure Select Statement
  11. A test query windows will open where you can test your select statement (query). Click on "Test Query" button and all data will appear on the same window. Click on "Finish" button to complete/close the SQL data source configuration wizard.
  12. Now, your gridview is configured with connection string to retrieve the data from database. Execute the application to see the data in GridView on web page. Press F5 button to see below output –
    GridView Output: Showing data from database
    GridView Output: Showing data from database
I hope this gridview example in asp.net helps you to configure the GridView with SQLDataSource at design time. If you find this article helpful, then could you please share the article on your social network?

Following articles might be of your interest

Protected by Copyscape Plagiarism Check

Sharing is Caring...



C#.Net GridView Example - How To: Format DateTime column in ASP.Net Gridview

In previous article we learned to bind asp.net GridView with SQLDataSource at Design Time and in Code behind. The article also explained what is GridView and why we need it?

Now, in this article let's learn how to format DateTime column.

Why it is needed to format the DateTime column in Gridview?

Suppose, you developed a web application which uses C# GridView control to display data from database to a web page. Actually, the GridView control doesn't care about the columns that are returned with DataSource and it shows them on the page as it is. This is the default behavior of GridView in asp.net.

If your DataSource is returned with few DateTime columns, then you can observe that the data shown in these columns will be in format "MM/DD/YYYY HH:MM:SS". This is default format for DateTime column in asp.net GridView.

Now, what if you want to change the format of any columns? For ex. If you want to show the DateTime column in "MMMM d, yyyy", then specifying this format in "DataFormatString" property of a GridView column will solve your problem.

You can change the format in two ways i.e. in Design time and/or another way is thru code behind. Let's learn to format the DateTime column in both these ways.

How to change the format of DateTime column in design time?

We are assumung that your DataGrid is already configured with DataSource. We are considering here same GridView example taken in previous post in which output is as below –

GridView Output: Showing data from database
GridView Output: Showing data from database

Observe here "Birth_Date" column, by default the date values are showing in format "MM/DD/YYYY HH:MM:SS". Yes, you can change the date format.
  1. To change the Date format click on the GridView and then click on arrow showing at right top of the GridView. It will open a dropdown window, select Edit columns from the list.
    GridView Edit Columns
    Gridview - Edit Columns
  2. A "Fields" window will open where you can customize the settings of all your columns. Now click on "Birth_Date" field, and enter "{0:MMMM d, yyyy}" in "DataFormatString" property as shown in below image. Now click on "Ok" button to finish.
    GridView - Properties of Fields
    GridView - Properties of Fields
  3. Now execute the application and see the output.
    GridView: DateTime Formatted output
    GridView: DateTime Formatted output

How to change the format of DateTime column in code behind?

  1. Open a page (.aspx) on which GridView control is placed.
  2. Go to Source of the page.
    ASP.Net open source page
    ASP.Net open source page
  3. Locate your asp.net GridView and find out a code of column for which a format needs to be changed. For example in my case, it would look like below -
    <asp:BoundField DataField="Birth_Date" HeaderText="Birth_Date"
    SortExpression="Birth_Date" />
  4. Now, add DataFormatString" property in above code. An example is given below, observe the highlighted part –
    <asp:BoundField DataField="Birth_Date" HeaderText="Birth_Date"
    SortExpression="Birth_Date" DataFormatString="&quot;{0:MMMM d, yyyy}&quot;" />
  5. Your C# GridView with improved DateTime column is now ready. Save the file and execute the application.
    GridView: DateTime Formatted output
    GridView: DateTime Formatted output
I hope this gridview example in asp.net helps you to format DateTime column in GridView. Please share the article on your online social network if you think is worth to share.

Following articles might be of your interest

Protected by Copyscape Plagiarism Check

Sharing is Caring...