sql-server - 使用 BulkCopy 将数据表复制到 Sql Server 新表或现有表

Question

有没有办法将内存数据表(vb.net)及其列(模式)复制到 sql server 新表或现有表中？如果一列已添加到临时表中，是否可以通过 BulkCopy 将新列添加到现有 sql server 表中的数据？

Answer 1

这是我用来将 DataTable 持久化到 SQL Server 的方法，它是用 C# 编写的，但您应该能够相当轻松地转换它:

public static string CreateCopyTableDataSQLServer(DataTable dt, string tableName, string connectionString)
{
    //Create the Destination Table based upon the structure of the DataTable
    string sql = string.Empty;
    string retValue = string.Empty;
    StringBuilder sbu;

    try
    {
        if (dt.Rows.Count == 0)
        {
            retValue += "The table " + tableName + " was NOT created because the source table contained zero (0) rows of data";
        }
        else
        {
            sbu = new StringBuilder(string.Format("IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'U')) DROP TABLE [dbo].[{0}] ", tableName));
            sbu.Append("Create Table " + tableName + " (");

            string dataType = string.Empty;

            foreach (DataColumn column in dt.Columns)
            {
                switch (column.DataType.Name)
                {
                    case "String":
                        dataType = " nvarchar(MAX) ";
                        break;
                    case "DateTime":
                        dataType = " nvarchar(MAX) ";
                        break;
                    case "Boolean":
                        dataType = " nvarchar(MAX) ";
                        break;
                    case "Int32":
                        dataType = " int ";
                        break;
                    case "Byte[]":
                        dataType = " varbinary(8000) ";
                        break;
                    default:
                        dataType = " nvarchar(MAX) ";
                        break;
                }
                string columnName = column.ColumnName.ToString();
                columnName = columnName.FormatProperNameCase();
                columnName = column.ColumnName.ToString().Replace(" ", "_").Replace("-", "_").Replace("#", "_").FormatRemoveNonLettersNumbers();
                sbu.Append("[" + columnName + "]" + dataType + " null, ");
            }

            sbu.Remove(sbu.Length - 2, 2);
            sbu.Append(")");
            sql = sbu.ToString();
            sql = sql.Replace("/", "_").Replace("\\", "_");

            //Copy the Data From the Data Table into the destination Table that was created above
            bool errorRetValue = SQLServerBulkCopy(dt, sql, tableName, connectionString);

            if (!errorRetValue)
            {
                retValue += " \r\n";
                retValue += "There was an error!";
            }
        }
        return retValue;
    }
    catch (Exception ex)
    {
        retValue = string.Format("Error - There was a problem with table {0} and thus it's data has NOT been transferred - {1}", tableName, ex.Message);
        return retValue;
    }
}

public static bool SQLServerBulkCopy(DataTable dt, string Sql, string TableName, string connectionString, bool connectionTypeSQL = true)
{
    try
    {
        if (connectionTypeSQL)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlBulkCopy sqlcpy = new SqlBulkCopy(conn))
                {
                    using (SqlCommand cmd = new SqlCommand(Sql, conn))
                    {
                        cmd.ExecuteNonQuery();
                        sqlcpy.DestinationTableName = TableName;  //copy the datatable to the sql table
                        sqlcpy.WriteToServer(dt);
                    }
                }
            }
            return true;
        }
        else
        {
            throw new ArgumentOutOfRangeException("This method is only for SQL Server Engines");
        }
    }
    catch (Exception ex)
    {
        return false;
    }
}