一次在数据库中插入多个记录 [英] insert multiple records at once in database

问题描述

你好朋友，
我在C#中有一个项目，我需要插入多个学生姓名并将所有这些姓名存储在按钮单击上.
我已将数据加载到gridview中，但是可以将记录从gridview中存储到数据库中.
如果是，请告诉我如何?
如果不是，应该采用哪种替代方法.请提出验证码.

关于
bishnu

hello friends,
i have a project in c#, i need to insert multiple names of students and store all of those names on button click.
i have load data into gridview but is it possible to store records into database from gridview.
if yes, please tell me how ?
if no, what should be the alternate method. Please suggest with the code.

with regards
bishnu

推荐答案

请参考以下链接: ^ ]

Refer the below link: http://blogs.msdn.com/b/mattdotson/archive/2005/11/09/real-world-gridview-bulk-editing.aspx[^]

您可以在SQL数据库表中插入多个值.以下是查询语法

You can insert multiple values in SQL database table. following is the Query syntax

--in SQL 2008
insert into TABLENAME (COLUMNNAME) values (VALUE1), values (VALUE2), values (VALUE3)

--in SQL 2005
insert into TABLENAME (COLUMNNAME) select 'VALUE1' union select 'VALUE2' union select 'VALUE3'

或

您可以从Gridview获取Datatable并直接更新到数据库

使用SQLServer管理对象和SqlBulkCopy将数据从数据表复制到SQLServer数据库 [ http://social.msdn.microsoft.com/Forums/zh/csharpgeneral/thread/7ab88e7d-2a7c-4554-a955-c9863c605a7d [

OR

You can fetch Datatable from Gridview and update to database directly

Copy Data from a DataTable to a SQLServer Database using SQLServer Management Objects and SqlBulkCopy[^]

http://social.msdn.microsoft.com/Forums/zh/csharpgeneral/thread/7ab88e7d-2a7c-4554-a955-c9863c605a7d[^]

您可以采用多种方式..

1)使用直接调用表Source

You can do it multiple ways..

1) With Direct call form Source

foreach (Employee item in employees)
{

   if (iCounter == 0)
  {
    cmd.BeginTransaction;
  }
  string sql = @"INSERT INTO Mytable (id, name, salary) 
    values (''@id'', ''@name'', ''@salary'')";
 //add parameters
  cmd.CommandText = sql; 
  cmd.ExecuteNonQuery();
  iCounter ++;
  if(iCounter >= 500)
  {
     cmd.CommitTransaction;
     iCounter = 0;
  }
}

if(iCounter > 0)
   cmd.CommitTransaction;

2)Linq

2) Linq

var db = new StoreDataContext();
using(var scope = new TransactionScope()){
    foreach(var emp in employee){
        db.employee.Insert();
        db.SubmitChanges(ConflictMode.FailOnFirstConflict);
    }
    scope.Complete();
}

3)使用Xml插入

构建xml字符串，或将数据集转换为xml，然后将其传递到数据库，然后在xml中进行迭代

3) Using Xml insert

Build an xml string, or convert the dataset to xml, and pass it to the database, and iterate in xml

CREATE PROCEDURE [dbo].[multipleInsert]
(
	@xmlData NVARCHAR(max)
)
AS
BEGIN
INSERT INTO employee (id, name, dob)
	SELECT		
	node.trnl.value(''id'', ''varchar(10)'') id,
	node.trnl.value(''name'', ''nvarchar(100)'') name,
	node.trnl.value(''dob'', ''nvarchar(8)'') dob,
FROM @xml.nodes(''/root/employee'') node(trnl)

<root>
		<employee id="00163500C6" xml:space="preserve">
		<name>Rafeeq</name>
		<dob>12-12-2011</dob>    
	  </empoloyee>
	 </root>
END

这篇关于一次在数据库中插入多个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！