如何将数据表插入到SQL Server数据库表中?

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 Server database.

我从某个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 OLE or SQL Server objects (like dataAdapter or connection)?
我应该使用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文件中读取每周工时报告，并将其保存到保存所有报表的数据库表中(每周用新记录更新db)。

The Excel file contains reports only for the current week.

Excel文件只包含当前周的报告。

8 个解决方案

#1

Create a User-Defined TableType in your database:

在数据库中创建用户定义的表类型:

CREATE TYPE [dbo].[MyTableType] AS TABLE(    [Id] int NOT NULL,    [Name] [nvarchar](128) NULL)

and define a parameter in your Stored Procedure:

并在存储过程中定义一个参数:

CREATE PROCEDURE [dbo].[InsertTable]    @myTableType MyTableType readonlyASBEGIN    insert into [dbo].Records select * from @myTableType END

and send your DataTable directly to sql server:

并将您的数据表直接发送到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'

#2

If it's the first time for you to save your datatable

如果这是您第一次保存您的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 stringSqlDataAdapter 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.

然后使用这个新的datatable再次重复bulkcopy函数。

#3

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”(您必须注意，在您的数据库中不存在这个名称的表)。如果我的答案对你有效，请投我一票!!!:)

#4

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 server OpenXML函数批量插入数据

#5

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;}

#6

    //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")

#7

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

这是我的存储过程

CREATE PROCEDURE dbo.usp_InsertRowsIntoTable@Year       int,@TeamName   nvarchar(50),ASINSERT 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();        }    }

#8

-1

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

#1