更新时间:2022-12-26 12:57:24
请参考以下链接: ^ ]
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