将数据表参数传递给存储过程 [英] Passing Datatable Parameter to Stored Procedure
问题描述
你好，
我在将数据表参数传递给存储过程时遇到了一些问题.
有两个表
tblQuestion和tblAnswerChoice
1. tblQuestion用于存储问题，其列为
a)Qid int主键身份
b)问题ntext
c)创建日期日期时间
2. tblAnswerChoice用于存储特定问题的答案选项，其列为
a)Ansid int主键身份
b)qid int
c)AnswerChoice ntext
用户可以自由添加任意数量的答案，但必须至少添加两个.
以下是我用于将数据存储在数据库中的代码
Hello,
I am facing some problem in passing data table parameter to stored procedure.
There are two table
tblQuestion and tblAnswerChoice
1. tblQuestion is for storing questions and its columns are
a)Qid int primary key identity
b)Question ntext
c)Createiondate datetime
2. tblAnswerChoice is for storing Answer Choices for the particular question and its columns are
a)Ansid int primary key identity
b)qid int
c)AnswerChoice ntext
User is free to add any number of answer choice but he must add a minimum of two.
Below is the code I am using to store the data in the database
protected void btnSubmit_Click(object sender, EventArgs e)
{
DataTable dataTable = new DataTable("SampleDataType");
//we create column names as per the type in DB
dataTable.Columns.Add("AnswerChoice", typeof(string));
if (Session["Panel"] != null)
{
for (int i = 1; i <= count; i++)
{
if (Request.Form["txtChoice" + i] != string.Empty)
{
dataTable.Rows.Add(Request.Form["txtChoice_" + i]); }
}
}
conn = new SqlConnection(ConfigurationManager.ConnectionStrings["conn"].ConnectionString);
cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = "sp_save_user_quiz_questions";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Question", txtQuestion.Text);
cmd.Parameters.AddWithValue("@CreationDate", DateTime.Now);
cmd.Parameters.AddWithValue("@AnswerChoice1", txtChoice1.Text);
cmd.Parameters.AddWithValue("@AnswerChoice2", txtChoice2.Text);
SqlParameter parameter = new SqlParameter();
//The parameter for the SP must be of SqlDbType.Structured
parameter.ParameterName = "@Sample";
parameter.SqlDbType = System.Data.SqlDbType.Structured;
parameter.Value = dataTable;
cmd.Parameters.Add(parameter);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
try
{
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
}
cmd.Dispose();
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
现在开始讨论问题
我将动态文本框创建的答案选择存储在数据表中.我将数据表作为参数传递给存储过程.
但是我在编写存储过程来存储数据即数据表时遇到了问题.我正在使用SQL Server2008.当我在Google中签入时，可以通过Table数据类型解决问题.但是如何使用它来解决我的问题?
需要注意的一件事是，在将数据保存到tblQuestion期间生成的qid存储在tblAnswerChoice中.因此，我们必须在存储过程中一次对两个表进行查询.
因为同时数据存储在两个表中.
谢谢，
迪帕克
[Edited]代码包装在"pre"标签中[/Edited]
Now about the problem
I stored the answerchoices created by dynamic text box in data table. And I am passing the data table as a parameter to stored procedure.
But I am facing the problem in writing stored procedure to store the data which are is the data table. I am using SQL Server 2008. As I checked in Google my problem can be solved by Table data type. But how can I use this to solve my problem?
And one thing to note is that the qid which is generated during saving the data in tblQuestion is stored in tblAnswerChoice. So we have to fire queries for both table at one time in stored procedure.
Because at the same time data is stored in both table.
Thanks,
Deepak
Code is wrapped in "pre" tag[/Edited]
推荐答案
解决方法
1.在数据库中为tblQuestion创建类型
Here the solution
1. Create a type for tblQuestion in the database
CREATE TYPE [dbo].[tblQuestionType] AS TABLE(
[question] ntext NULL,
[creationdate] datetime NULL
)
2.在数据库中为tblAnswerChoice创建类型
2.Create a type for the tblAnswerChoice in the database
create TYPE [dbo].[tblAnswerChoiceType] AS TABLE(
[answerchoice] ntext NULL
)
3.创建一个存储过程以接受两个表类型参数
3.Create a stored procedure to accept two table type parameters
create procedure [dbo].[spInsert] @dtquestion tblQuestionType READONLY,@dtanswerchoice tblAnswerChoiceType READONLY as
Begin
INSERT INTO [tblQuestion]
([question]
,[creationdate])
select [question],[creationdate] from @dtquestion
insert into tblAnswerChoice
(QID,AnswerChoice)
select @@IDENTITY,answerchoice
from @dtanswerchoice
End
GO
4.现在，您的前端应该是
4.Now, your front end should be
private void button1_Click(object sender, EventArgs e)
{
try
{
//CREATE A DATA TABLE FOR TBLQUETION AND POPULATE
DataTable odtQuestion = new DataTable();
odtQuestion.Columns.Add("qestion");
odtQuestion.Columns.Add("creationdate",typeof(DateTime));
odtQuestion.Rows.Add("Quesion A", dateTimePicker1.Value);
//CREATE A DATA TABLE FOR TBLQUETION AND POPULATE
DataTable odtAnswerChoice = new DataTable();
odtAnswerChoice.Columns.Add("answerchoice");
odtAnswerChoice.Rows.Add("Choice 1");
odtAnswerChoice.Rows.Add("Choice 2");
odtAnswerChoice.Rows.Add("Choice 3");
SqlConnection oConn = new SqlConnection();
oConn.ConnectionString = "Data Source=SOLAP;database=Sample;user=sa;password=admin1990";
oConn.Open();
SqlCommand oCmd = oConn.CreateCommand();
oCmd.CommandType = CommandType.StoredProcedure;
oCmd.CommandText = "SpInsert";
oCmd.Parameters.AddWithValue("@dtquestion", odtQuestion);
oCmd.Parameters.AddWithValue("@dtanswerchoice", odtAnswerChoice);
oCmd.ExecuteNonQuery();
MessageBox.Show("Suceess");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
我对此进行了测试，并将其插入tblquestion(一直希望它一直排成一行)并将相应的记录插入tblAnswerChoice.
请注意，@@ identity将在tblQuestion中插入最后一个自动编号，因此将在tblAnswerChoice中使用它.
另外，您必须在前端添加一个datetimepicker，而我已将其值用于创建日期
希望这能解决您的问题.
巴拉
I have tested this and it inserts tblquestion (hope its one row all the time) and the correspoding record into tblAnswerChoice.
Please note that the @@identity will have the last autonumber inserted into tblQuestion and hence its used in the tblAnswerChoice.
Also, you have to add a datetimepicker to the front end and i have used the value of it for the creation date
Hope this resolves you issue.
Bala
这篇关于将数据表参数传递给存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
