I will show you about how to insert the data into data table from user interface page using Stored Procedures and view the data in GridView.
UI page is like this:
Designing code (.aspx) and Code-behind page (.aspx.cs) are available in Download Link:
After inserting all the fields click on the "Save" button. The GridView will show all the records.
Now I will tell you the process. After click in "Save" button, then the Button Click Event Fires.
protected void Button1_Click(object sender, EventArgs e)
{
if (Session["update"].ToString() == ViewState["update"].ToString())
{
InsertingtheData();
Clearall();
Session["update"] = Server.UrlEncode(System.DateTime.Now.ToString());
}
first(1);
}
public void InsertingtheData()
{
//creating a variable and insert the field values into it.
int employee_id= Convert.ToInt32(txtEmployeeId.Text);
-------------
------------
//Sql Stuff
SqlConnection sqlconn;
SqlCommand sqlcomm;
sqlconn = new SqlConnection(ConfigurationManager.AppSettings["Sql_server"]);
sqlconn.Open();
sqlcomm = new SqlCommand();
sqlcomm.Connection = sqlconn;
//Here I am definied command type is Stored Procedure.
sqlcomm.CommandType = CommandType.StoredProcedure;
//Here I mentioned the Stored Procedure Name.
sqlcomm.CommandText = "EMP_MASTER_TESTING_I";
//Here I fix the variable values to Stored Procedure Parameters. You can easily understand if you can see the Stored Procedure Code.
sqlcomm.Parameters.Add(new SqlParameter("@emp_id", SqlDbType.Int)).Value = employee_id;
sqlcomm.Parameters.Add(new SqlParameter("@emp_desg_id", SqlDbType.Int)).Value = employee_designation_id;
sqlcomm.Parameters.Add(new SqlParameter("@emp_dob",SqlDbType.DateTime)).Value = employee_dob;
sqlcomm.ExecuteNonQuery();
sqlcomm.Dispose();
sqlconn.Close();
}
//Clear all fields values like this.
public void Clearall()
{
txtBankaccNo.Text = "";
txtBankname.Text = "";
}
----------------------
//This is the Stored Procedure Code.Availableà DownLoad Link
ALTER PROCEDURE dbo.EMP_MASTER_TESTING_I
(
@emp_id int,
@emp_desg_id int,
@emp_dob Datetime,
@emp_doj Datetime,
@emp_fname varchar(100),
@emp_lname varchar(100),
@emp_gender varchar(1),
--@emp_p_street varchar(50),
@emp_p_city varchar(50),
@emp_p_state varchar(50),
@emp_p_country varchar(50),
@emp_p_pin Varchar(50),
@emp_phone varchar(50),
@emp_loc_id int,
@emp_bank_name varchar(20),
@emp_bank_acno varchar(20),
@pan_no varchar(20),
@emp_off_email varchar(50),
@emp_dor Datetime,
--@created_on Datetime,
@created_by int
--@status bit,
--@updated_on int
)
AS
Begin
Insert into
EMP_MASTER_TESTING
(
EMP_ID,
EMP_DESG_ID,
EMP_DOB,
EMP_DOJ,
EMP_FNAME,
EMP_LNAME,
EMP_GENDER,
--EMP_P_STREET,
EMP_P_CITY,
EMP_P_STATE,
EMP_P_COUNTRY,
EMP_P_PIN,
EMP_Phone,
EMP_LOC_ID,
EMP_BANK_NAME,
EMP_BANK_ACNO,
PAN_NO,
EMP_OFF_EMAIL,
EMP_DOR,
CREATED_ON,
CREATED_BY
)
Values
(
@emp_id,
@emp_desg_id,
@emp_dob,
@emp_doj,
@emp_fname,
@emp_lname,
@emp_gender,
--@emp_p_street,
@emp_p_city,
@emp_p_state,
@emp_p_country,
@emp_p_pin,
@emp_phone,
@emp_loc_id,
@emp_bank_name,
@emp_bank_acno,
@pan_no,
@emp_off_email,
@emp_dor,
convert(Datetime, getdate(), 103),
@created_by
)
End
After that first () method calls. GridView will show all the columns.
public void first(int a)
{
if (a == 0)
{
tblInsertPage.Visible = true;
tblgv.Visible = false;
}
if (a == 1)
{
tblgv.Visible = true;
tblInsertPage.Visible = false;
}
}
When page Refresh occurs the values are again inserted into the database. So to avoid that I wrote these lines in Page load event, Button Click event and PreRender event.
//In Page_Load Event
if (Session["update"]==null)
{
Session["update"] = Server.UrlEncode(System.DateTime.Now.ToString());
}
//In Button Click Event
if (Session["update"].ToString() == ViewState["update"].ToString())
{
InsertingtheData();
Clearall();
Session["update"] = Server.UrlEncode(System.DateTime.Now.ToString());
}
//In PreRender Event
private void Page_PreRender(object sender, EventArgs e)
{
ViewState["update"] = Session["update"];
}
Before you write a Stored Procedure you have the Data table that you are using in the SP. So I wrote an insert command. Insert command values are Stored Procedure input parameters. And Run the Stored Procedure.
I wrote a single command in stored procedure, but you can write as many commands as you like. We can write conditional statements like If, while etc and looping statements like for-loop and switch case etc.
We can see GridView like this.
I hope you like this article. Let me know if you have any concerns.