如何通过单击“保存”按钮将多个记录插入到一​​个表中 [英] How to insert the multiple records into one table by clicking on save button

问题描述

其实我有一个网络形式的收费结构如下

费用类型|金额|年/月（复选框）

注册费：| TextBox1的| chekbox1

tution-fee：| textbox2 |复选框2

图书费|文本框3 |复选框3



认为上面是费用结构的表单，同时单击我想要的保存按钮将记录保存到下表方式



表费结构

student_id | fee_type |金额（文本框）|年/月（复选框） ）

1002 |注册费| 1001 | 1（如果检查的话）

1002 | tution-fee | 2500 | 0（如果没有检查意味着）

1002 |书费| 2300 | 1



//我的问题是插入每条记录我必须单独查询...如果不是我怎么写...我在下面给出我的代码

 SqlCommand cmdInsertFeeQuotation = DBManager.DataAccess.command（）; 
 cmdInsertFeeQuotation.Parameters.Add（ new  SqlParameter（  @option，SqlDbType.VarChar， 50 ））; 
 cmdInsertFeeQuotation.Parameters [  @ option]。Value =   InsertFeeQuotation; 
 
 cmdInsertFeeQuotation.Parameters.Add（ new  SqlParameter（  @ Fee_Type，SqlDbType.VarChar， 50 ））; 
 cmdInsertFeeQuotation.Parameters [  @ Fee_Type]。值=  注册费; 
 
 cmdInsertFeeQuotation.Parameters.Add（ new  SqlParameter（  @ Amount，SqlDbType.Money））; 
 cmdInsertFeeQuotation.Parameters [  @ Amount]。Value = txtRegistrationFee.Text; 
 
  if （ckboxRegistrationFee.Checked ==  true ）
 { 
 cmdInsertFeeQuotation.Parameters.Add（ new  SqlParameter（  @Annual_Month，SqlDbType.Bit））; 
 cmdInsertFeeQuotation.Parameters [  @ Annual_Month]。Value =  1 ; 
} 
  else  
 {
 cmdInsertFeeQuotation.Parameters.Add（ new  SqlParameter（  @ Annual_Month，SqlDbType.Bit））; 
 cmdInsertFeeQuotation.Parameters [  @ Annual_Month]。Value =  0 ; 
} 
 
 
  string  sqlquery1 =  < span class =code-string> SchoolProc; 
 DBManager.DataAccess.executeQuery（sqlquery1）;  //  上面的代码用于插入一条记录，但我想要inser三条记录...  
 

解决方案

用于同时插入多条记录你使用这种技术。

在一个数据表中添加你的记录，并用单个插入语句插入记录。

http://msdn.microsoft.com/en-us/library/system.data.sqlclient。 sqlbulkcopy.aspx [ ^ ] [ ]

您必须创建文本框的数组以插入多个记录一次保存点击。

尝试使用表值函数i n SQL Server并使用以下语句来表示：

  INSERT   INTO  TestTable（FirstName，LastName）
  SELECT  FirstName，LastName 

Actually i am having a webform for fee-structure like below
fee-type |amount |annual/month(checkbox)
registration-fee: | textbox1| chekbox1
tution-fee: | textbox2 | checkbox2
books-fee |textbox3 |checkbox3

think that above is a form for fee-structure while clicking on the save button i want to save the records into below table manner

table fee-structure
student_id|fee_type|amount(textbox)|annual/month(checkbox)
1002 | registration-fee |1001 | 1(if checked means)
1002 | tution-fee | 2500 | 0(if not checked means)
1002 | books-fee | 2300 | 1

//my question is for inserting every record i have to take separate query...if not how i can write...i am giving my code below

SqlCommand cmdInsertFeeQuotation = DBManager.DataAccess.command();
                   cmdInsertFeeQuotation.Parameters.Add(new SqlParameter("@option", SqlDbType.VarChar, 50));
                   cmdInsertFeeQuotation.Parameters["@option"].Value = "InsertFeeQuotation";

                   cmdInsertFeeQuotation.Parameters.Add(new SqlParameter("@Fee_Type", SqlDbType.VarChar, 50));
                   cmdInsertFeeQuotation.Parameters["@Fee_Type"].Value = "Registration Fee";

                   cmdInsertFeeQuotation.Parameters.Add(new SqlParameter("@Amount", SqlDbType.Money));
                   cmdInsertFeeQuotation.Parameters["@Amount"].Value = txtRegistrationFee.Text;

                   if (ckboxRegistrationFee.Checked == true)
                   {
                       cmdInsertFeeQuotation.Parameters.Add(new SqlParameter("@Annual_Month", SqlDbType.Bit));
                       cmdInsertFeeQuotation.Parameters["@Annual_Month"].Value = 1;
                   }
                   else
                   {
                       cmdInsertFeeQuotation.Parameters.Add(new SqlParameter("@Annual_Month", SqlDbType.Bit));
                       cmdInsertFeeQuotation.Parameters["@Annual_Month"].Value = 0;
                   }


                   string sqlquery1 = "SchoolProc";
                   DBManager.DataAccess.executeQuery(sqlquery1);//above code is for inserting one record but i want to inser three records at a time...

解决方案

for inserting more than one record at same time you use this technique.
Add your record in a datatable and insert record with single insert statement.
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx[^][]

you have to create array of the text box for inserting multiple record at one time save click.

try using Table valued functions in SQL Server and use the following statement for ex:

INSERT INTO TestTable (FirstName, LastName)
SELECT FirstName, LastName

这篇关于如何通过单击“保存”按钮将多个记录插入到一​​个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！