将 DataTable 转换为 C# 中 SQL 的 CREATE TABLE + INSERT 脚本 [英] Converting a DataTable to an CREATE TABLE + INSERT script for SQL in C#
问题描述
我需要从 DataTable 和其中的 DATA 生成一个 TSQL 脚本.这不是一个单一的插入.除此之外,我还需要创建表(相同的数据表结构)
I need to generate a TSQL script from a DataTable along with the DATA in it. It's not a single insert. Beside that, I need the create the table too (same datatable structure)
所以:我有一个充满数据的数据表.我想要在 SQL SERVER (CREATE TABLE + INSERT) 中创建此 DataTable 结构的 TSQL 脚本以及其中的数据
so: I have a DataTable filled with data. I want the TSQL script that creates the structure of this DataTable along with the data in it in SQL SERVER (CREATE TABLE + INSERT)
先谢谢你并特别感谢 John Saunders 帮助我纠正问题.
Thank you in advance And Special thanks to John Saunders for helping me to correct the question.
推荐答案
首先,我会使用这个对象来构建您的 CREATE TABLE 命令.
First, I would use this object to build your CREATE TABLE command.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace Toolkit
{
public class SqlTableCreator
{
#region Instance Variables
private SqlConnection _connection;
public SqlConnection Connection
{
get { return _connection; }
set { _connection = value; }
}
private SqlTransaction _transaction;
public SqlTransaction Transaction
{
get { return _transaction; }
set { _transaction = value; }
}
private string _tableName;
public string DestinationTableName
{
get { return _tableName; }
set { _tableName = value; }
}
#endregion
#region Constructor
public SqlTableCreator() { }
public SqlTableCreator(SqlConnection connection) : this(connection, null) { }
public SqlTableCreator(SqlConnection connection, SqlTransaction transaction)
{
_connection = connection;
_transaction = transaction;
}
#endregion
#region Instance Methods
public object Create(DataTable schema)
{
return Create(schema, null);
}
public object Create(DataTable schema, int numKeys)
{
int[] primaryKeys = new int[numKeys];
for (int i = 0; i < numKeys; i++)
{
primaryKeys[i] = i;
}
return Create(schema, primaryKeys);
}
public object Create(DataTable schema, int[] primaryKeys)
{
string sql = GetCreateSQL(_tableName, schema, primaryKeys);
SqlCommand cmd;
if (_transaction != null && _transaction.Connection != null)
cmd = new SqlCommand(sql, _connection, _transaction);
else
cmd = new SqlCommand(sql, _connection);
return cmd.ExecuteNonQuery();
}
public object CreateFromDataTable(DataTable table)
{
string sql = GetCreateFromDataTableSQL(_tableName, table);
SqlCommand cmd;
if (_transaction != null && _transaction.Connection != null)
cmd = new SqlCommand(sql, _connection, _transaction);
else
cmd = new SqlCommand(sql, _connection);
return cmd.ExecuteNonQuery();
}
#endregion
#region Static Methods
public static string GetCreateSQL(string tableName, DataTable schema, int[] primaryKeys)
{
string sql = "CREATE TABLE [" + tableName + "] (
";
// columns
foreach (DataRow column in schema.Rows)
{
if (!(schema.Columns.Contains("IsHidden") && (bool)column["IsHidden"]))
{
sql += " [" + column["ColumnName"].ToString() + "] " + SQLGetType(column);
if (schema.Columns.Contains("AllowDBNull") && (bool)column["AllowDBNull"] == false)
sql += " NOT NULL";
sql += ",
";
}
}
sql = sql.TrimEnd(new char[] { ',', '
' }) + "
";
// primary keys
string pk = ", CONSTRAINT PK_" + tableName + " PRIMARY KEY CLUSTERED (";
bool hasKeys = (primaryKeys != null && primaryKeys.Length > 0);
if (hasKeys)
{
// user defined keys
foreach (int key in primaryKeys)
{
pk += schema.Rows[key]["ColumnName"].ToString() + ", ";
}
}
else
{
// check schema for keys
string keys = string.Join(", ", GetPrimaryKeys(schema));
pk += keys;
hasKeys = keys.Length > 0;
}
pk = pk.TrimEnd(new char[] { ',', ' ', '
' }) + ")
";
if (hasKeys) sql += pk;
sql += ")";
return sql;
}
public static string GetCreateFromDataTableSQL(string tableName, DataTable table)
{
string sql = "CREATE TABLE [" + tableName + "] (
";
// columns
foreach (DataColumn column in table.Columns)
{
sql += "[" + column.ColumnName + "] " + SQLGetType(column) + ",
";
}
sql = sql.TrimEnd(new char[] { ',', '
' }) + "
";
// primary keys
if (table.PrimaryKey.Length > 0)
{
sql += "CONSTRAINT [PK_" + tableName + "] PRIMARY KEY CLUSTERED (";
foreach (DataColumn column in table.PrimaryKey)
{
sql += "[" + column.ColumnName + "],";
}
sql = sql.TrimEnd(new char[] { ',' }) + "))
";
}
//if not ends with ")"
if ((table.PrimaryKey.Length == 0) && (!sql.EndsWith(")")))
{
sql += ")";
}
return sql;
}
public static string[] GetPrimaryKeys(DataTable schema)
{
List<string> keys = new List<string>();
foreach (DataRow column in schema.Rows)
{
if (schema.Columns.Contains("IsKey") && (bool)column["IsKey"])
keys.Add(column["ColumnName"].ToString());
}
return keys.ToArray();
}
// Return T-SQL data type definition, based on schema definition for a column
public static string SQLGetType(object type, int columnSize, int numericPrecision, int numericScale)
{
switch (type.ToString())
{
case "System.String":
return "VARCHAR(" + ((columnSize == -1) ? "255" : (columnSize > 8000) ? "MAX" : columnSize.ToString() ) + ")";
case "System.Decimal":
if (numericScale > 0)
return "REAL";
else if (numericPrecision > 10)
return "BIGINT";
else
return "INT";
case "System.Double":
case "System.Single":
return "REAL";
case "System.Int64":
return "BIGINT";
case "System.Int16":
case "System.Int32":
return "INT";
case "System.DateTime":
return "DATETIME";
case "System.Boolean":
return "BIT";
case "System.Byte":
return "TINYINT";
case "System.Guid":
return "UNIQUEIDENTIFIER";
default:
throw new Exception(type.ToString() + " not implemented.");
}
}
// Overload based on row from schema table
public static string SQLGetType(DataRow schemaRow)
{
return SQLGetType(schemaRow["DataType"],
int.Parse(schemaRow["ColumnSize"].ToString()),
int.Parse(schemaRow["NumericPrecision"].ToString()),
int.Parse(schemaRow["NumericScale"].ToString()));
}
// Overload based on DataColumn from DataTable type
public static string SQLGetType(DataColumn column)
{
return SQLGetType(column.DataType, column.MaxLength, 10, 2);
}
#endregion
}
}
然后我会使用 SQL BulkCopy 而不是单独的 INSERT 语句.
Then I would use SQL BulkCopy rather than separate INSERT statements.
public static void BulkInsertDataTable(string connectionString, string tableName, DataTable table)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlBulkCopy bulkCopy =
new SqlBulkCopy
(
connection,
SqlBulkCopyOptions.TableLock |
SqlBulkCopyOptions.FireTriggers |
SqlBulkCopyOptions.UseInternalTransaction,
null
);
bulkCopy.DestinationTableName = tableName;
connection.Open();
bulkCopy.WriteToServer(table);
connection.Close();
}
}
这篇关于将 DataTable 转换为 C# 中 SQL 的 CREATE TABLE + INSERT 脚本的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!