DataBase:
ALTER procedure [dbo].[sp_emp_dept_exam]
(
@empno int=0,
@ename nvarchar(50)=null,
@sal nvarchar(50)=null,
@deptno int=0,
@dname nvarchar(50)=null,
@opr int=0
)
as
begin
if @opr=1
begin
Insert into emp_exam(ename,sal,deptno)values(@ename,@sal,@deptno)
end
else if @opr=2
begin
SELECT dname,deptno FROM dept_exam
end
else if @opr=3
begin
select ename,dname,sal from emp_exam e inner join dept_exam d on e.deptno=d.deptno
end
end
//Code Behind:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
namespace DemoExample
{
public partial class test_exam : System.Web.UI.Page
{
SqlCommand cmd;
SqlDataReader dr;
SqlDataAdapter da;
DataSet ds=new DataSet();
SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["connstring2"].ToString());
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
cmd = new SqlCommand("sp_emp_dept_exam", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@opr", SqlDbType.Int).Value = 2;
da = new SqlDataAdapter(cmd);
da.Fill(ds);
ddldname.DataTextField = "dname";
ddldname.DataValueField = "deptno";
ddldname.DataSource = ds;
ddldname.DataBind();
}
}
protected void btnsave_Click(object sender, EventArgs e)
{
cmd = new SqlCommand("sp_emp_dept_exam", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("ename", SqlDbType.NVarChar, 50).Value= txtename.Text;
cmd.Parameters.Add("@sal", SqlDbType.NVarChar, 50).Value = txtsal.Text;
cmd.Parameters.Add("@deptno", SqlDbType.Int).Value = ddldname.SelectedValue;
cmd.Parameters.Add("@opr", SqlDbType.Int).Value = 1;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
lblmsg.Text = "Record Successfully Inserted";
txtename.Text = txtsal.Text = "";
cmd.Parameters.Clear();
cmd.Parameters.Clear();
cmd.Parameters.Add("@opr", SqlDbType.Int).Value = 3;
con.Open();
da = new SqlDataAdapter(cmd);
da.Fill(ds);
gvexam.DataSource = ds;
gvexam.DataBind();
con.Close();
}
}
}