如何编写存储过程 [英] How to write stored procedure

问题描述

大家好我刚刚开始学习存储过程。因为我能够为简单的查询创建SP（存储过程）但是遇到了一些查询。

如下所示

Hi Everyone i have just started learning about stored procedure. As i was able to create SP(Stored Procedure) for simple query but got stuck into some queries.
as Shown below

引用：

var collection = Repeater0.Items;



foreach（收集中的RepeaterItem项目）

{

HiddenField QID =（HiddenField）item.FindControl（QID）;



int q = int.Parse（QID.Value ）;



string answer = Request.Form [q.ToString（）];

string id = Convert.ToString（Session [ UserID]）;

string id2 = Convert.ToString（Session [ID]）;

string connection = ConfigurationManager.AppSettings [connection]。ToString（ ）;

SqlConnection connec = new SqlConnection（连接）;

connec.Open（）;

SqlCommand cmd = new SqlCommand（插入到t_AnswerSheet（用户Id，QId，AnswerId，AnswerResult）值（'+ id +'，'+ q +'，（从t_Answers中选择AID，其中QId ='+ q +'和AOptions ='+ answer + '），（从t_Answers中选择AnswerResult，其中QId ='+ q +'和AOptions ='+ answer +'）），connec）;

//cmd.Parameters.Add （@ sqlpara，id）;

//cmd.Parameters.Add(\"@sqlparb\",q）;

//cmd.Parameters.Add（ @sqlparc，回答）;



cmd.ExecuteNonQuery（）;

connec.Close（）;

}

Response.Redirect（Successfullsubmit.aspx）;

}

var collection = Repeater0.Items;

foreach (RepeaterItem item in collection)
{
HiddenField QID = (HiddenField)item.FindControl("QID");

int q = int.Parse(QID.Value);

string answer = Request.Form[q.ToString()];
string id = Convert.ToString(Session["UserID"]);
string id2 = Convert.ToString(Session["ID"]);
string connection = ConfigurationManager.AppSettings["connection"].ToString();
SqlConnection connec = new SqlConnection(connection);
connec.Open();
SqlCommand cmd = new SqlCommand("Insert into t_AnswerSheet(UserId,QId, AnswerId,AnswerResult) values('" + id + "','" + q + "',(select AID from t_Answers where QId='" + q + "' and AOptions ='" + answer + "'),(select AnswerResult from t_Answers where QId='" + q + "' and AOptions ='" + answer + "'))", connec);
//cmd.Parameters.Add("@sqlpara", id);
//cmd.Parameters.Add("@sqlparb",q);
//cmd.Parameters.Add("@sqlparc", answer);

cmd.ExecuteNonQuery();
connec.Close();
}
Response.Redirect("Successfullsubmit.aspx");
}

所以，如果有人可以帮助我



我尝试了什么：



我正在使用

So if anyone can help me

What I have tried:

I am using

create procedure BtnSubmit_Click	
AS
Insert into t_AnswerSheet(UserId,QId, AnswerId,AnswerResult) values('" + id + "','" + q + "',(select AID from t_Answers where QId='" + q + "' and AOptions ='" + answer + "'),(select AnswerResult from t_Answers where QId='" + q + "' and AOptions ='" + answer + "'))
go

推荐答案

否。

对于初学者，永远不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。改为使用参数化查询。

当你把它作为存储过程写它时它非常特别无法工作，因为它在SQL Server中执行，它没有对你的C＃的任何访问权限代码变量。



创建一个简单的存储过程来插入：

No.
For starters, never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.
And it very particularly won't work at all when you write it as a stored procedure, because that is executed in SQL Server, which doesn't have any access to your C# code variables.

Create a simple stored procedure to insert:

CREATE PROC [dbo].Sample
@ID varchar(100)
@VAL varchar(100)
AS
BEGIN
    INSERT INTO MyTable (Id, ValueColumnName) VALUES (@ID, @VAL)
END

然后在C＃代码中使用它：

And then use it in your C# code:

using (SqlConnection con = new SqlConnection(strConnect))
    {
    con.Open();
    using (SqlCommand com = new SqlCommand("Sample", con))
        {
        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.AddWithValue("@ID", id);
        com.Parameters.AddWithValue("@VAL", "This goes in the DB");
        com.ExecuteNonQuery();
        }
    con.Close();
    }

试试吧，让它在测试数据库上运行，然后使用相同的原则创建一个适合你的任务。

Try that, get it working on a test DB, and then use the same principles to create one suitable for your task.

这篇关于如何编写存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！