Hello friends, we know many ways to insert data into a database.
In this article we will see how to insert data into the SQL Database and GridView using a GridView TextBox control. GridView is an important control for any database record displayed in any web and desktop application.
In this example use an update panel control and CSS to design the GridView.
Now here we will see step-by-step how to insert data into the database and also display the inserted record in the GridView.
Step 1
Create a table.
Example
- create table EmployeeDB
- (
- EmpID int primary key identity(1,1),
- EmpName varchar(20),
- EmpGender varchar(10),
- EmpDOB date null,
- EmpDepartment varchar(20)
- )
Step 2
Create a SQL procedure to insert data into the database.
- Create procedure sp_EmpDataInsert
- @EmpName varchar(20),
- @EmpGender varchar(10),
- @EmpDOB date,
- @EmpDepartment varchar(20)
- as
- begin
- set nocount on;
- insert into EmployeeDB
- (EmpName,EmpGender,EmpDOB,EmpDepartment)
- values
- (@EmpName,@EmpGender,@EmpDOB,@EmpDepartment)
- End
Step 3
Now go to your project design (.aspx) page.
Add a page using Master page.
Step 4
Add a Script Manager as in the following:
- <asp:ScriptManager ID="ScriptManager1" runat="server" EnablePartialRendering="true" EnablePageMethods="true" >
- </asp:ScriptManager>
Step 5
Add an Update panel for the currently inserted record as in the following:
- <asp:UpdatePanel runat="server" UpdateMode="Always" >
- </asp:UpdatePanel>
Step 6
Add a GridView control inside the Update panel as in the following:
- <asp:UpdatePanel runat="server" UpdateMode="Always" >
- <ContentTemplate>
- <asp:GridView runat="server" ID="GridEmpData"
- CssClass="Grid"
- AutoGenerateColumns="false"
- ShowFooter="true" >
- </asp:GridView>
- </ContentTemplate>
-
- </asp:UpdatePanel>
Step 7
Add a CSS Style for the GridView Design as in the following:
- <style>
- body { font-size:65% }
-
- #Employee {
- font:11px Times New Roman;
- width:auto;
- display:block;
- padding:10px 0 0 0;
- }
-
- .text {
- width:auto;
- padding:2px 4px;
- font:inherit;
- font-weight:bold;
- text-align:left;
- border:solid 2px #BFBFBF;
- background:yellow;
- text-transform:uppercase;
- }
-
- .Grid {
- width:100%;
- font:inherit;
- margin:5px 0 10px 0;
- background-color:#FFF;
- border:solid 2px #525252;
- text-transform:uppercase;
- }
- .Grid td {
- font:inherit;
- padding:2px;
- border:solid 1px #C1C1C1;
- color:#333;
- text-align:center;
- }
- .Grid th {
- padding:5px;
- color:blue;
- background:#424234 border-left:solid 3px #525233;
- font:inherit;
- font-weight:bold;
- }
-
- .Gridbutton {
- cursor:pointer;
- text-align:center;
- color: white;
- font:inherit;
- background-color: blue;
- border:solid 1px #3079ED;
- -moz-border-radius:5px;
- -webkit-border-radius:5px;
- line-height:20px;
- }
- </style>
Step 8
Finally, create your .aspx design page for the GridView as in the following:
In this Design page add a GridView control and all database fields added in the GridView Template field and also in the last add a TextBox control for the record insert into the database and Button control inside the GridView Footer Template.
Also define an EmptyDataTemplate in the GridView design for the blank TextBox display and button control.
Step 9
Configure your database connection string in your project web.config file.
- <connectionStrings>
- <add name="connstr" connectionString="Data Source=RAKESH-PC;Initial Catalog=SqlServerTech;User ID=sa;Password=your SQL DB Password" providerName="System.Data.SqlClient"/>
-
- <add name="Pratical_testConnectionString" connectionString="Data Source=RAKESH-PC;Initial Catalog=Pratical_test;User ID=sa" providerName="System.Data.SqlClient"/>
- </connectionStrings>
Step 10
In the last step write the back-end code for the GridView data insert into the database and display on the grid view.
In the Page Design back-end write the following code.
First define the connection string as in the following:
- string conString = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
After writing the code for the Grid View Data Bind in EmployeeDataGrid().
The following is the back-end code.
- using System;
- using System.Collections.Generic;
- using System.Linq;
- using System.Web;
- using System.Web.UI;
- using System.Web.UI.WebControls;
- using System.Configuration;
- using System.Data;
- using System.Data.SqlClient;
- namespace Test_WebApplication.Form
- {
- public partial class WebForm1 : System.Web.UI.Page
- {
- string conString = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
- protected void Page_Load(object sender, EventArgs e)
- {
- if (!Page.IsPostBack)
- {
- EmployeeDataGrid();
- }
- }
- private void EmployeeDataGrid()
- {
- using (SqlConnection con = new SqlConnection(conString))
- {
- using (SqlCommand cmd = new SqlCommand("select * from EmployeeDB"))
- {
- SqlDataAdapter dt = new SqlDataAdapter();
- try
- {
- cmd.Connection = con;
- con.Open();
- dt.SelectCommand = cmd;
-
- DataTable dTable = new DataTable();
- dt.Fill(dTable);
-
- GridEmpData.DataSource = dTable;
- GridEmpData.DataBind();
- }
- catch(Exception)
- {
- lblmsg.Text = "record not found";
- }
- }
- }
- }
-
- protected void btnSave_Click(object sender, EventArgs e)
- {
- Button btn = (Button)sender;
- GridViewRow GrdRow = (GridViewRow)btn.Parent.Parent;
-
- TextBox txtEmpName = (TextBox)GrdRow.Cells[0].FindControl("txtEmpName");
- TextBox txtEmpGender = (TextBox)GrdRow.Cells[0].FindControl("txtEmpGender");
- TextBox txtEmpDOB = (TextBox)GrdRow.Cells[0].FindControl("txtEmpDOB");
- TextBox txtEmpDpt = (TextBox)GrdRow.Cells[0].FindControl("txtEmpDpt");
-
-
- using (SqlConnection con = new SqlConnection(conString))
- {
- using (SqlCommand cmd = new SqlCommand())
- {
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.CommandText = "sp_EmpDataInsert";
-
- cmd.Connection = con;
- con.Open();
-
- cmd.Parameters.AddWithValue("@EmpName",SqlDbType.VarChar).Value=txtEmpName.Text.Trim();
- cmd.Parameters.AddWithValue("@EmpGender",SqlDbType.VarChar).Value=txtEmpGender.Text.Trim();
- cmd.Parameters.AddWithValue("@EmpDOB",SqlDbType.Date).Value=txtEmpDOB.Text.Trim();
- cmd.Parameters.AddWithValue("@EmpDepartment",SqlDbType.VarChar).Value=txtEmpDpt.Text;
-
- cmd.ExecuteNonQuery();
- }
- }
- EmployeeDataGrid();
- }
-
- }
- }
Finally to see your GridView control on your web form press F5 and run your aspx page.
And also, first check the database EmployeeDB table all the record fields are null using the SQL Select Query command.
Now insert a record into the TextBox and press the button to insert a TextBox record in the database and display a Grid View.
First fill in the TextBox and then press the button SAVE NEW RECORD.
Next see insert a record into the Grid view.
And check also your inserted record in the database using the select Query command.
Finally check your inserted grid view record and that the database inserted records are the same. So the GridView example is complete. I hope you understand this example.
We are many more operation doing on TextBox data using GridView control as like data insert, update, delete. All types of these operations will be seen in a future article.