Question

我有数据可查,需要把全部材料推向数据库表。

我可以在那里拿到一张 for子,同时插入每行。这虽然非常缓慢,因为只有几千人。

是否有办法尽快完成整个数据表?

该表的栏目少于表。其余部分应留任民族解放军。

Answer 1

我发现SqlBulkCopy是这样做的方便之举,不需要在服务器上书写储存的程序。

这方面的一个例子是我如何执行:

// take note of SqlBulkCopyOptions.KeepIdentity , you may or may not want to use this for your situation.  

using (var bulkCopy = new SqlBulkCopy(_connection.ConnectionString, SqlBulkCopyOptions.KeepIdentity))
{
      // my DataTable column names match my SQL Column names, so I simply made this loop. However if your column names don t match, just pass in which datatable name matches the SQL column name in Column Mappings
      foreach (DataColumn col in table.Columns)
      {
          bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
      }

      bulkCopy.BulkCopyTimeout = 600;
      bulkCopy.DestinationTableName = destinationTableName;
      bulkCopy.WriteToServer(table);
}

Answer 2

Since you have a DataTable already, and since I am assuming you are using SQL Server 2008 or better, this is probably the most straightforward way. First, in your database, create the following two objects:

CREATE TYPE dbo.MyDataTable -- you can be more speciifc here
AS TABLE
(
  col1 INT,
  col2 DATETIME
  -- etc etc. The columns you have in your data table.
);
GO

CREATE PROCEDURE dbo.InsertMyDataTable
  @dt AS dbo.MyDataTable READONLY
AS
BEGIN
  SET NOCOUNT ON;

  INSERT dbo.RealTable(column list) SELECT column list FROM @dt;
END
GO

现在,在您的C#法典中:

DataTable tvp = new DataTable();
// define / populate DataTable

using (connectionObject)
{
    SqlCommand cmd = new SqlCommand("dbo.InsertMyDataTable", connectionObject);
    cmd.CommandType = CommandType.StoredProcedure;
    SqlParameter tvparam = cmd.Parameters.AddWithValue("@dt", tvp);
    tvparam.SqlDbType = SqlDbType.Structured;
    cmd.ExecuteNonQuery();
}

如果你在你的提问中提供了更具体的细节,我将作出更具体的答复。

Answer 3

考虑。

using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
{
    bulkCopy.DestinationTableName = 
        "dbo.BulkCopyDemoMatchingColumns";

    try
    {
        // Write from the source to the destination.
        bulkCopy.WriteToServer(ExistingSqlTableName);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

Answer 4

如果能够脱离数据可检索的直线途径——和数据表,也可以通过物体清单:

1> 数据->

public static DataTable ConvertTo<T>(IList<T> list) { DataTable table = CreateTable<T>(); Type entityType = typeof(T); PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(entityType); foreach (T item in list) { DataRow row = table.NewRow(); foreach (PropertyDescriptor prop in properties) { row[prop.Name] = prop.GetValue(item); } table.Rows.Add(row); } return table; }
来源和更多细节见。遗失的财产将保留到其违约数值上(ints>0,不适用参考类型等)。

2) 将物体列入数据库

一种办法是使用<代码>EntityFramework.BulkInsert推广。但是,需要一个电子格式数据基。

它产生快速插入所需的BULK INSERT指挥(用户确定的表格类型解决办法比这一办法要慢得多)。

虽然不是直截了当的方法,但它有助于建立一个与物体清单合作的基础,而不是<编码>DataTable,。

Answer 5

You can do this with a table value parameters.

查阅以下条款:

http://www.codeproject.com/Articles/39161/C-and-Table-Value-Paramiles

Answer 6

我倾向于用户定义数据类型:数据是超快的。

第1步:在Sql服务器行创建用户定义表

CREATE TYPE [dbo].[udtProduct] AS TABLE( [ProductID] [int] NULL, [ProductName] [varchar](50) NULL, [ProductCode] [varchar](10) NULL ) GO

第2步:与用户建立定型式储存程序

CREATE PROCEDURE ProductBulkInsertion @product udtProduct readonly AS BEGIN INSERT INTO Product (ProductID,ProductName,ProductCode) SELECT ProductID,ProductName,ProductCode FROM @product END

步骤3:从c#中删除储存程序

SqlCommand sqlcmd = new SqlCommand("ProductBulkInsertion", sqlcon); sqlcmd.CommandType = CommandType.StoredProcedure; sqlcmd.Parameters.AddWithValue("@product", productTable); sqlcmd.ExecuteNonQuery();

www.un.org/spanish/ecosoc 可能的话题:使用者定义表

Actually there is no sql server command to alter user defined type But in management studio you can achieve this from following steps

1.generate script for the type.(in new query window or as a file) 2.delete user defied table. 3.modify the create script and then execute.

友情链接