C# 如何将数据表插入到 SQL Server 数据库表中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9075159/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert a data table into SQL Server database table?
提问by Asaf Gilad
I have imported data from some Excel file and I have saved it into a datatable. Now I'd like to save this information in my SQL Serverdatabase.
我从某个 Excel 文件中导入了数据，并将其保存到datatable. 现在我想将此信息保存在我的SQL Server数据库中。
I saw a lot of information on the web but I cannot understand it:
我在网上看到很多信息，但我无法理解：
- Someone said insert line by line another suggested bulk update... etc: what it better?
- Should I use
OLEorSQL Serverobjects (likedataAdapterorconnection)?
- 有人说逐行插入另一个建议的批量更新......等等：它更好吗？
- 我应该使用
OLE或SQL Server对象（如dataAdapter或connection）吗？
My need is to read the employee weekly hours report, from his Excel file and save it to a database table where all the reports are saved (updating the db with new records every week).
我需要从他的 Excel 文件中读取员工每周工作时间报告并将其保存到保存所有报告的数据库表中（每周用新记录更新数据库）。
The Excel file contains reports only for the current week.
Excel 文件仅包含本周的报告。
采纳答案by Navid Farhadi
Create a User-Defined TableTypein your database:
User-Defined TableType在您的数据库中创建一个：
CREATE TYPE [dbo].[MyTableType] AS TABLE(
[Id] int NOT NULL,
[Name] [nvarchar](128) NULL
)
and define a parameter in your Stored Procedure:
并在您的 中定义一个参数Stored Procedure：
CREATE PROCEDURE [dbo].[InsertTable]
@myTableType MyTableType readonly
AS
BEGIN
insert into [dbo].Records select * from @myTableType
END
and send your DataTabledirectly to sql server:
并将您的DataTable直接发送到 sql server：
using (var command = new SqlCommand("InsertTable") {CommandType = CommandType.StoredProcedure})
{
var dt = new DataTable(); //create your own data table
command.Parameters.Add(new SqlParameter("@myTableType", dt));
SqlHelper.Exec(command);
}
To edit the values inside stored-procedure, you can declare a local variable with the same type and insert input table into it:
要编辑存储过程中的值，您可以声明一个具有相同类型的局部变量并将输入表插入其中：
DECLARE @modifiableTableType MyTableType
INSERT INTO @modifiableTableType SELECT * FROM @myTableType
Then, you can edit @modifiableTableType:
然后，您可以编辑@modifiableTableType：
UPDATE @modifiableTableType SET [Name] = 'new value'
回答by Pranay Rana
I would suggest you go for bulk insert as suggested in this article : Bulk Insertion of Data Using C# DataTable and SQL server OpenXML function
我建议您按照本文中的建议进行批量插入： 使用 C# DataTable 和 SQL 服务器 OpenXML 函数批量插入数据
回答by sachin
public bool BulkCopy(ExcelToSqlBo objExcelToSqlBo, DataTable dt, SqlConnection conn, SqlTransaction tx)
{
int check = 0;
bool result = false;
string getInsert = "";
try
{
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
if (dr != null)
{
if (check == 0)
{
getInsert = "INSERT INTO [tblTemp]([firstName],[lastName],[Father],[Mother],[Category]" +
",[sub_1],[sub_LG2])"+
" select '" + dr[0].ToString() + "','" + dr[1].ToString() + "','" + dr[2].ToString() + "','" + dr[3].ToString() + "','" + dr[4].ToString().Trim() + "','" + dr[5].ToString().Trim() + "','" + dr[6].ToString();
check += 1;
}
else
{
getInsert += " UNION ALL ";
getInsert += " select '" + dr[0].ToString() + "','" + dr[1].ToString() + "','" + dr[2].ToString() + "','" + dr[3].ToString() + "','" + dr[4].ToString().Trim() + "','" + dr[5].ToString().Trim() + "','" + dr[6].ToString() ;
check++;
}
}
}
result = common.ExecuteNonQuery(getInsert, DatabasesName, conn, tx);
}
else
{
throw new Exception("No row for insertion");
}
dt.Dispose();
}
catch (Exception ex)
{
dt.Dispose();
throw new Exception("Please attach file in Proper format.");
}
return result;
}
回答by rofans91
If it's the first time for you to save your datatable
如果这是您第一次保存数据表
Do this (using bulk copy). Assure there are no PK/FK constraint
这样做（使用批量复制）。确保没有 PK/FK 约束
SqlBulkCopy bulkcopy = new SqlBulkCopy(myConnection);
//I assume you have created the table previously
//Someone else here already showed how
bulkcopy.DestinationTableName = table.TableName;
try
{
bulkcopy.WriteToServer(table);
}
catch(Exception e)
{
messagebox.show(e.message);
}
Now since you already have a basic record. And you just want to check new record with the existing one. You can simply do this.
现在因为你已经有了一个基本的记录。而您只想用现有记录检查新记录。你可以简单地做到这一点。
This will basically take existing table from database
这基本上将从数据库中获取现有表
DataTable Table = new DataTable();
SqlConnection Connection = new SqlConnection("ConnectionString");
//I assume you know better what is your connection string
SqlDataAdapter adapter = new SqlDataAdapter("Select * from " + TableName, Connection);
adapter.Fill(Table);
Then pass this table to this function
然后将此表传递给此函数
public DataTable CompareDataTables(DataTable first, DataTable second)
{
first.TableName = "FirstTable";
second.TableName = "SecondTable";
DataTable table = new DataTable("Difference");
try
{
using (DataSet ds = new DataSet())
{
ds.Tables.AddRange(new DataTable[] { first.Copy(), second.Copy() });
DataColumn[] firstcolumns = new DataColumn[ds.Tables[0].Columns.Count];
for (int i = 0; i < firstcolumns.Length; i++)
{
firstcolumns[i] = ds.Tables[0].Columns[i];
}
DataColumn[] secondcolumns = new DataColumn[ds.Table[1].Columns.Count];
for (int i = 0; i < secondcolumns.Length; i++)
{
secondcolumns[i] = ds.Tables[1].Columns[i];
}
DataRelation r = new DataRelation(string.Empty, firstcolumns, secondcolumns, false);
ds.Relations.Add(r);
for (int i = 0; i < first.Columns.Count; i++)
{
table.Columns.Add(first.Columns[i].ColumnName, first.Columns[i].DataType);
}
table.BeginLoadData();
foreach (DataRow parentrow in ds.Tables[0].Rows)
{
DataRow[] childrows = parentrow.GetChildRows(r);
if (childrows == null || childrows.Length == 0)
table.LoadDataRow(parentrow.ItemArray, true);
}
table.EndLoadData();
}
}
catch (Exception ex)
{
throw ex;
}
return table;
}
This will return a new DataTable with the changed rows updated. Please ensure you call the function correctly. The DataTable first is supposed to be the latest.
这将返回一个新的 DataTable，其中更新了更改的行。请确保您正确调用该函数。DataTable 首先应该是最新的。
Then repeat the bulkcopy function all over again with this fresh datatable.
然后使用这个新的数据表再次重复批量复制功能。
回答by Luv
I am giving a very simple code, which i used in my solution (I have the same problem statement as yours)
我给出了一个非常简单的代码，我在我的解决方案中使用了它（我和你的问题陈述相同）
SqlConnection con = connection string ;
//new SqlConnection("Data Source=.;uid=sa;pwd=sa123;database=Example1");
con.Open();
string sql = "Create Table abcd (";
foreach (DataColumn column in dt.Columns)
{
sql += "[" + column.ColumnName + "] " + "nvarchar(50)" + ",";
}
sql = sql.TrimEnd(new char[] { ',' }) + ")";
SqlCommand cmd = new SqlCommand(sql, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
cmd.ExecuteNonQuery();
using (var adapter = new SqlDataAdapter("SELECT * FROM abcd", con))
using(var builder = new SqlCommandBuilder(adapter))
{
adapter.InsertCommand = builder.GetInsertCommand();
adapter.Update(dt);
// adapter.Update(ds.Tables[0]); (Incase u have a data-set)
}
con.Close();
I have given a predefined table-name as "abcd" (you must take care that a table by this name doesn't exist in your database). Please vote my answer if it works for you!!!! :)
我已将预定义的表名指定为“abcd”（您必须注意数据库中不存在具有该名称的表）。如果对你有用，请投票我的答案！！！！:)
回答by jade290
I found that it was better to add to the table row by row if your table has a primary key. Inserting the entire table at once creates a conflict on the auto increment.
我发现如果您的表有主键，最好逐行添加到表中。一次插入整个表会在自动增量上产生冲突。
Here's my stored Proc
这是我存储的 Proc
CREATE PROCEDURE dbo.usp_InsertRowsIntoTable
@Year int,
@TeamName nvarchar(50),
AS
INSERT INTO [dbo.TeamOverview]
(Year,TeamName)
VALUES (@Year, @TeamName);
RETURN
I put this code in a loop for every row that I need to add to my table:
对于需要添加到表中的每一行，我都将此代码放入循环中：
insertRowbyRowIntoTable(Convert.ToInt16(ddlChooseYear.SelectedValue), name);
And here is my Data Access Layer code:
这是我的数据访问层代码：
public void insertRowbyRowIntoTable(int ddlValue, string name)
{
SqlConnection cnTemp = null;
string spName = null;
SqlCommand sqlCmdInsert = null;
try
{
cnTemp = helper.GetConnection();
using (SqlConnection connection = cnTemp)
{
if (cnTemp.State != ConnectionState.Open)
cnTemp.Open();
using (sqlCmdInsert = new SqlCommand(spName, cnTemp))
{
spName = "dbo.usp_InsertRowsIntoOverview";
sqlCmdInsert = new SqlCommand(spName, cnTemp);
sqlCmdInsert.CommandType = CommandType.StoredProcedure;
sqlCmdInsert.Parameters.AddWithValue("@Year", ddlValue);
sqlCmdInsert.Parameters.AddWithValue("@TeamName", name);
sqlCmdInsert.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlCmdInsert != null)
sqlCmdInsert.Dispose();
if (cnTemp.State == ConnectionState.Open)
cnTemp.Close();
}
}
回答by masoud Cheragee
//best way to deal with this is sqlbulkcopy
//but if you dont like it you can do it like this
//read current sql table in an adapter
//add rows of datatable , I have mentioned a simple way of it
//and finally updating changes
Dim cnn As New SqlConnection("connection string")
cnn.Open()
Dim cmd As New SqlCommand("select * from sql_server_table", cnn)
Dim da As New SqlDataAdapter(cmd)
Dim ds As New DataSet()
da.Fill(ds, "sql_server_table")
Dim cb As New SqlCommandBuilder(da)
//for each datatable row
ds.Tables("sql_server_table").Rows.Add(COl1, COl2)
da.Update(ds, "sql_server_table")
回答by C.Poh
From my understanding of the question,this can use a fairly straight forward solution.Anyway below is the method i propose ,this method takes in a data table and then using SQL statements to insert into a table in the database.Please mind that my solution is using MySQLConnection and MySqlCommand replace it with SqlConnection and SqlCommand.
根据我对问题的理解，这可以使用一个相当直接的解决方案。无论如何，下面是我提出的方法，该方法接收一个数据表，然后使用 SQL 语句插入到数据库中的一个表中。请注意我的解决方案正在使用 MySQLConnection 和 MySqlCommand 将其替换为 SqlConnection 和 SqlCommand。
public void InsertTableIntoDB_CreditLimitSimple(System.Data.DataTable tblFormat)
{
for (int i = 0; i < tblFormat.Rows.Count; i++)
{
String InsertQuery = string.Empty;
InsertQuery = "INSERT INTO customercredit " +
"(ACCOUNT_CODE,NAME,CURRENCY,CREDIT_LIMIT) " +
"VALUES ('" + tblFormat.Rows[i]["AccountCode"].ToString() + "','" + tblFormat.Rows[i]["Name"].ToString() + "','" + tblFormat.Rows[i]["Currency"].ToString() + "','" + tblFormat.Rows[i]["CreditLimit"].ToString() + "')";
using (MySqlConnection destinationConnection = new MySqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ToString()))
using (var dbcm = new MySqlCommand(InsertQuery, destinationConnection))
{
destinationConnection.Open();
dbcm.ExecuteNonQuery();
}
}
}//CreditLimit
