如何将记录集插入SQL表 [英] How to insert a recordset into SQL table

问题描述

大家好，

我正在处理一个找不到更好的方法将记录集插入sql表的问题.

目前，我正在循环每个记录集以进行插入.目前它可以正常工作，但是当记录集大小将来扩展时，它不是一个好的解决方案.

因此，这里的任何人都可以帮助我做一次插入"表之类的事情.

我被卡住了，见下文

Hi guys,

I''m working with a problem where I couldn''t find a better way insert a recordset to sql table.

Currently, I''m looping each recordset to insert. It is working fine for now but it is not a good solution when the recordset size expands future.

Thus, anyone here can help me to do something like one time "insert into" table.

I got stuck in half way, see below

'rstemp-record set
'SQLTable - Sql Table

tmpSql = ""
tmpSql = tmpSql & "Insert Into SQLTable (field1, field2, field3, field4) "
tmpSql = tmpSql & "Select field1, field2, field3, field4 From xxxxx "
Cmd.CommandText = tmpSql
Cmd.Execute

如何在xxxxx上说明记录集.有人可以帮忙吗?

如果有与此类似的线程，请张贴网址.

谢谢，
Skunkhead:)

How to state the recordset at the xxxxx. Can anyone help?

Kindly post the url if there is similar thread to this.

Thanks,
Skunkhead :)

推荐答案

看看通过

Look at doing a bulk insert via SqlBulkCopy[^]

Here is one way to do it, inserting a whole DataTable:

Dim strConnect As String = "Data Source=GRIFFPC\SQLEXPRESS;Initial Catalog=Testing;Integrated Security=True"
Using con As New SqlConnection(strConnect)
	con.Open()
	Using bulk As New SqlBulkCopy(con)
		bulk.DestinationTableName = "Test"
		Dim dt As New DataTable()
		dt.Columns.Add("Id")
		dt.Columns.Add("Data")
		dt.Rows.Add("ID 1", "DATA 1")
		dt.Rows.Add("ID 2", "DATA 2")
		dt.Rows.Add("ID 3", "DATA 3")
		dt.Rows.Add("ID 4", "DATA 4")
		bulk.WriteToServer(dt)
	End Using
End Using

hi 在sql server 2008中

hi in sql server 2008

create database solution
go

create table test
(
filed1 int identity primary key,
filed2 nvarchar(50),
filed3 nvarchar(50),
filed4 bigint)
)
go

create proc ups_test
@a nvarchar(50),
@b nvarchar(50),
@c bigint
as
begin
insert dbo.test
values(@a,@b,@c)
end

在上面的代码中，首先创建数据库解决方案，然后创建表测试，最后全部创建proc ups_test

然后去创建C#代码
在click事件中编写此代码，并用3个文本框值填充过程值

in the above code first create database solution then create table test and at the end of all create proc ups_test

then go and create c# code
in the click event write this and fill the procedure value with 3 text box value

private void btninsert_Click(object sender, EventArgs e)
       {
           try
           {
               SqlCommand sqm = new SqlCommand("ups_insert",               new SqlConnection("data source=.;database=test;uid=your sql username;pwd=your sql password));
               sqm.Connection.Open();
               sqm.CommandType = CommandType.StoredProcedure;
               sqm.Parameters.Add("@a", SqlDbType.NVarChar, 5).Value = txtyear.Text;
               sqm.Parameters.Add("@b", SqlDbType.NVarChar, 5).Value = txtmonth.Text;
               sqm.Parameters.Add(@c, SqlDbType.BigInt).Value = txtqty.Text;
               sqm.ExecuteNonQuery();
               sqm.Connection.Close();
           }
           catch (Exception ex)
           {

               MessageBox.Show(ex.Message);
           }



       }

这篇关于如何将记录集插入SQL表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！