当我在后面调用代码时，找不到CRUD的存储过程，proc名称 [英] Stored procedure for CRUD , proc name is not found when I call in code behind
本文介绍了当我在后面调用代码时，找不到CRUD的存储过程，proc名称的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
嗨
我是sql的新手，我想在一个SP中绑定我的表并在项目中进行调用。
我尝试过：
Hi
I am new to sql ,I want to bind my table in a single SP and make a call in project.
What I have tried:
Create PROCEDURE [gvempdetails]
@Action VARCHAR (10),
@EMPLOYEEID INT ,
@EMPLOYEENAME VARCHAR(100)= NULL ,
@DEPARTMENT VARCHAR(100)= NULL ,
@AGE VARCHAR(30)= NULL,
@SALARY INT = NULL
AS
BEGIN
SET NOCOUNT ON
IF @Action = 'SELECT'
BEGIN
SELECT EmpId,EmpName,Dep,Age,Sal
FROM gvdetails17
END
IF @Action='INSERT'
BEGIN
INSERT INTO gvdetails17(EmpId,EmpName,Dep,Age,Sal) VALUES(@EMPLOYEEID,@EMPLOYEENAME,@DEPARTMENT,@AGE,@SALARY)
END
IF @Action ='UPDATE'
BEGIN
UPDATE gvdetails17 SET EmpName=@EMPLOYEENAME,Dep=@DEPARTMENT,Age=@AGE,Sal=@SALARY WHERE EmpId=@EMPLOYEEID
END
IF @Action='DELETE'
BEGIN
DELETE FROM gvdetails17 where EmpId=@EMPLOYEEID
END
SET NOCOUNT OFF
END
<pre lang="c#"><pre>SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlconnection"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
bind();
BindAge();
BindSalary();
}
}
protected void bind()
{
cn.Open();
SqlCommand cmd = new SqlCommand("Select * from [gvempdetails]", cn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
cn.Close();
if (ds.Tables[0].Rows.Count > 0)
{
gvDetails.DataSource = ds;
gvDetails.DataBind();
}
else
{
ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
gvDetails.DataSource = ds;
gvDetails.DataBind();
int columncount = gvDetails.Rows[0].Cells.Count;
gvDetails.Rows[0].Cells.Clear();
gvDetails.Rows[0].Cells.Add(new TableCell());
gvDetails.Rows[0].Cells[0].ColumnSpan = columncount;
gvDetails.Rows[0].Cells[0].Text = "Enter the details";
}
}
protected void ddlAge_SelectedIndexChanged(object sender, EventArgs e)
{
DataTable dt = new DataTable();
cn.Open();
if (ddlAge.SelectedValue != "")
{
string[] ages = ddlAge.SelectedValue.Split('-');
string from = ages[0];
string to = ages[1];
SqlCommand cmd = new SqlCommand("select * from [gvempdetails] WHERE @AGE between @from and @to", cn);
cmd.Parameters.AddWithValue("@from", from);
cmd.Parameters.AddWithValue("@to", to);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
}
else
{
SqlCommand cmd = new SqlCommand("select * from [gvempdetails]", cn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
}
cn.Close();
gvDetails.DataSource = dt;
gvDetails.DataBind();
}
protected void gvDetails_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("Add"))
{
TextBox txtEmpId = (TextBox)gvDetails.FooterRow.FindControl("txtempid");
TextBox txtEmpName = (TextBox)gvDetails.FooterRow.FindControl("txtempname1");
TextBox txtEmpDep = (TextBox)gvDetails.FooterRow.FindControl("txtdep1");
TextBox txtAge = (TextBox)gvDetails.FooterRow.FindControl("txtage1");
TextBox txtSal = (TextBox)gvDetails.FooterRow.FindControl("txtsal1");
cn.Open();
SqlCommand cmd =
new SqlCommand(
"Insert into [gvempdetails](@EMPLOYEEID,@EMPLOYEENAME,@DEPARTMENT,@AGE,@SALARY) values('" + txtEmpId.Text + "','" + txtEmpName.Text + "','" +
txtEmpDep.Text + "','" + txtAge.Text + "','" + txtSal.Text + "')", cn);
int result = cmd.ExecuteNonQuery();
cn.Close();
if (result == 1)
{
bind();
}
else
{
}
}
}
protected void gvDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int EmpId = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Values["EmpId"].ToString());
string username = gvDetails.DataKeys[e.RowIndex].Values["EmpName"].ToString();
cn.Open();
SqlCommand cmd = new SqlCommand("Delete from [gvempdetails] where @EMPLOYEEID=" + EmpId, cn);
int result = cmd.ExecuteNonQuery();
cn.Close();
if (result == 1)
{
bind();
//lblresult.Text = username + " details deleted successfully";
}
}
protected void gvDetails_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
int EmpId = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Value.ToString());
string username = gvDetails.DataKeys[e.RowIndex].Values["EmpName"].ToString();
TextBox txtDep = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtdep");
TextBox txtAge = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtage");
TextBox txtSal = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtsal");
cn.Open();
SqlCommand cmd = new SqlCommand("Update [gvempdetails] set @DEPARTMENT='" + txtDep.Text + "',@AGE='" + txtAge.Text + "',@SALARY='" + txtSal.Text + "' where @EMPLOYEEID=" + EmpId, cn);
cmd.ExecuteNonQuery();
cn.Close();
// lblresult.Text = username + " Details Updated successfully";
gvDetails.EditIndex = -1;
bind();
}
protected void gvDetails_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
gvDetails.EditIndex = -1;
bind();
}
protected void gvDetails_RowEditing(object sender, GridViewEditEventArgs e)
{
gvDetails.EditIndex = e.NewEditIndex;
bind();
}
protected void gvDetails_OnRowDataBound(object sender, GridViewRowEventArgs e)
{
}
protected void BindAge()
{
DataTable dt = new DataTable();
cn.Open();
SqlCommand cmd = new SqlCommand("Select * from Age", cn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
//DataSet ds = new DataSet();
da.Fill(dt);
cn.Close();
ddlAge.DataSource = dt;
ddlAge.DataTextField = "Age";
ddlAge.DataValueField = "Age";
ddlAge.DataBind();
ddlAge.Items.Insert(0, new ListItem("--Select--"));
}
protected void BindSalary()
{
DataTable dt = new DataTable();
cn.Open();
SqlCommand cmd = new SqlCommand("Select * from Salary1", cn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
//DataSet ds = new DataSet();
da.Fill(dt);
cn.Close();
ddlAddSalary1.DataSource = dt;
ddlAddSalary1.DataTextField = "Salary";
ddlAddSalary1.DataValueField = "Salary";
ddlAddSalary1.DataBind();
ddlAddSalary1.Items.Insert(0, new ListItem("--Select--"));
}
protected void ddlAddSalary1_SelectedIndexChanged1(object sender, EventArgs e)
{
DataTable dt = new DataTable();
cn.Open();
if (ddlAddSalary1.SelectedValue != "")
{
string[] sal = ddlAddSalary1.SelectedValue.Split('-');
string from = sal[0];
string to = sal[1];
SqlCommand cmd = new SqlCommand("Select * from [gvempdetails] WHERE @SALARY between @from and @to", cn);
cmd.Parameters.AddWithValue("@from", from);
cmd.Parameters.AddWithValue("@to", to);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
}
else
{
SqlCommand cmd = new SqlCommand("select * from [gvempdetails]", cn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
}
cn.Close();
gvDetails.DataSource = dt;
gvDetails.DataBind();
}
protected void OnSelectedIndexChanged(object sender, EventArgs e)
{
//txtEmpId.Text = gvDetails.SelectedRow.Cells[0].Text;
//lblempname.Text = gvDetails.SelectedRow.Cells[1].Text;
//lbldep.Text = gvDetails.SelectedRow.Cells[2].Text;
//lblage.Text = gvDetails.SelectedRow.Cells[3].Text;
//lblsal.Text = gvDetails.SelectedRow.Cells[4].Text;
}
protected void OnPaging(object sender, GridViewPageEventArgs e)
{
gvDetails.PageIndex = e.NewPageIndex;
this.bind();
}
}
}
推荐答案
您的存储过程似乎很好，但根据您的代码需要一定的变化behide。
我们采取的措施一个接一个：
1. Bind（）函数： -
在这里你必须改变这个代码
Hi,
Your stored procedure seems to be good but need certain chenges as per your code behide.
Let's take one by one:
1. Bind() function :-
Here you have to change this code
cn.Open();
//SqlCommand cmd = new SqlCommand("Select * from [gvempdetails]", cn);
//SqlDataAdapter da = new SqlDataAdapter(cmd);
//DataSet ds = new DataSet();
//da.Fill(ds);
//cn.Close();
to -
cn.Open();
SqlCommand cmd = new SqlCommand("gvempdetails", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Action","SELECT");
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
cn.Close();
2. ddlAge_SelectedIndexChanged function
看似这里，您希望过滤年龄介于您的范围之间的员工，即来自和之后
如果您想在存储过程（SP）中使用这些参数，则必须将它们作为输入参数在SP中使用
喜欢：
2. ddlAge_SelectedIndexChanged function
As it seems here, you want to filter the employee with Age between your range i.e. from and to
As you want to use these paramters in Stored Procedure(SP) , you have to take them as input parametrs in SP
Like :
@Action VARCHAR (10),
@EMPLOYEEID INT ,
@EMPLOYEENAME VARCHAR(100)= NULL ,
@DEPARTMENT VARCHAR(100)= NULL ,
@AGE VARCHAR(30)= NULL,
@SALARY INT = NULL,
@FrOM INT = NULL,
@TO INT = NULL
并需要再为此添加一个动作
and need to add one more Action for this as
IF @Action = 'SELECTAGE'
BEGIN
SELECT EmpId,EmpName,Dep,Age,Sal where age >= @FROM and age <= @TO
FROM gvdetails17
END
现在是codebehid代码，
Now the codebehid code,
cn.open();
SqlCommand cmd = new SqlCommand("gvempdetails", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ACTION", "SELECTAGE");
cmd.Parameters.AddWithValue("@FROM", from);
cmd.Parameters.AddWithValue("@TO", to);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
我希望你能通过上面的例子理解如何从代码中使用存储过程
仅供参考，
创建SqlCommand对象时，我们需要传递commandText和连接。
所以在SP的情况下，你必须只传递SP的名称作为commandText在我们的情况下gvempdetails
以及你必须传递的其他东西作为参数，如@ ACTION，@ FROM在我们的例子中。
你的存储过程中你可能已经观察到一些参数初始化为NULL，如@SALARY INT = NULL
这里的NULL用于使参数可选，否则你有在所有的电话上传递这个参数
关于最后的建议 - @Action VARCHAR（10）在这里，在动作类型的参数中你应该至少拿30个字符，因为它有机会
你把动作名称当作'SELECTAGEBYRANGE'这样的长字符串
希望它会帮助你！
谢谢，
Ejaz Waquif
I hope you will understand by above example how to use Stored Procedure from code
just for information,
While creating SqlCommand object we need to pass the commandText and connection.
So in case of SP you have to pass only the name of SP as commandText in our case "gvempdetails"
and rest of the thing you have to pass as parameter like @ACTION,@FROM in our case.
in your stored procedrue you may have observed that some parameters are initialize with NULL like @SALARY INT = NULL
Here the NULL is use to make the parameter optional otherwise you have to pass this parameter on all call
On last suggestion - @Action VARCHAR (10) here, in action type of parametrs you should take atleast 30 char coz there ia a chance
you to take Action name as long string like 'SELECTAGEBYRANGE'
Hope it will help you out!
Thanks,
Ejaz Waquif
嗨。，
For Sql您使用输入/输出参数创建存储过程。对于那个输入参数，你有声明类型变量。依赖于那种类型，你会写信给你。
例如
创建程序sp_test（@type varchar（100），@ testname varchar（100）= null，@ testaddress varchar（100））
as
begin
if（@ type ='I'）
begin
插入testtable（testname，testaddress）值（@ testname，@ testaddress）
end
if（@ type ='SA'）
开始
从testtable中选择testaddress
end
结束
从上面的程序你必须通过类型，然后你将返回查询结果。
在前端（Asp.net）而不是查询，你将传递商店程序名称。
我希望你能得到它..
谢谢
Hi.,
For Sql you create store procedure with input/output parameters. for that input parameters you have declare type variable. depend on that type you will write you queries.
for example
create procedure sp_test(@type varchar(100), @testname varchar(100)=null, @testaddress varchar(100))
as
begin
if(@type='I')
begin
insert into testtable (testname,testaddress) values(@testname,@testaddress)
end
if(@type='SA')
begin
select testaddress from testtable
end
end
from above procedure you will must pass type, then you will return the query result.
In front end (Asp.net) instead of query you will pass store procedure name.
I hope you will get it..
Thanks
这篇关于当我在后面调用代码时，找不到CRUD的存储过程，proc名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
