首先是建表
public static string CreateTable(string tableName, System.Data.DataTable table)
{
string sqlsc;
sqlsc = "CREATE TABLE " + tableName + "(";
for (int i = 0; i < table.Columns.Count; i++)
{
sqlsc += "\n" + table.Columns[i].ColumnName;
if (table.Columns[i].DataType.ToString().Contains("System.Int32"))
sqlsc += " int ";
else if (table.Columns[i].DataType.ToString().Contains("System.DateTime"))
sqlsc += " datetime ";
else if (table.Columns[i].DataType.ToString().Contains("System.String"))
sqlsc += " nvarchar(" + table.Columns[i].MaxLength.ToString() + ") ";
else if (table.Columns[i].DataType.ToString().Contains("System.Single"))
sqlsc += " single ";
else if (table.Columns[i].DataType.ToString().Contains("System.Double"))
sqlsc += " double ";
else
sqlsc += string.Format(" nvarchar( {0} ) ", table.Columns[i].MaxLength == -1 ? "max" : table.Columns[i].MaxLength.ToString());
if (table.Columns[i].AutoIncrement)
sqlsc += " IDENTITY(" + table.Columns[i].AutoIncrementSeed.ToString() + "," + table.Columns[i].AutoIncrementStep.ToString() + ") ";
if (!table.Columns[i].AllowDBNull)
sqlsc += " NOT NULL ";
sqlsc += ",";
}
return sqlsc + ")";
}
/// <summary>
/// Creates a SQL script that creates a table where the columns matches that of the specified DataTable.
/// </summary>
public static string BuildCreateTableScript(DataTable Table)
{
StringBuilder result = new StringBuilder();
result.AppendFormat("CREATE TABLE [{1}] ({0} ", Environment.NewLine, Table.TableName);
bool FirstTime = true;
foreach (DataColumn column in Table.Columns.OfType<DataColumn>())
{
if (FirstTime) FirstTime = false;
else
result.Append(" ,");
result.AppendFormat("[{0}] {1} {2} {3}",
column.ColumnName, // 0
GetSQLTypeAsString(column.DataType), // 1
column.AllowDBNull ? "NULL" : "NOT NULL", // 2
Environment.NewLine // 3
);
}
result.AppendFormat(") ON [PRIMARY]{0}GO{0}{0}", Environment.NewLine);
// Build an ALTER TABLE script that adds keys to a table that already exists.
if (Table.PrimaryKey.Length > 0)
result.Append(BuildKeysScript(Table));
return result.ToString();
}
/// <summary>
/// Builds an ALTER TABLE script that adds a primary or composite key to a table that already exists.
/// </summary>
private static string BuildKeysScript(DataTable Table)
{
// Already checked by public method CreateTable. Un-comment if making the method public
// if (Helper.IsValidDatatable(Table, IgnoreZeroRows: true)) return string.Empty;
if (Table.PrimaryKey.Length < 1) return string.Empty;
StringBuilder result = new StringBuilder();
if (Table.PrimaryKey.Length == 1)
result.AppendFormat("ALTER TABLE {1}{0} ADD PRIMARY KEY ({2}){0}GO{0}{0}", Environment.NewLine, Table.TableName, Table.PrimaryKey[0].ColumnName);
else
{
List<string> compositeKeys = Table.PrimaryKey.OfType<DataColumn>().Select(dc => dc.ColumnName).ToList();
string keyName = compositeKeys.Aggregate((a, b) => a + b);
string keys = compositeKeys.Aggregate((a, b) => string.Format("{0}, {1}", a, b));
result.AppendFormat("ALTER TABLE {1}{0}ADD CONSTRAINT pk_{3} PRIMARY KEY ({2}){0}GO{0}{0}", Environment.NewLine, Table.TableName, keys, keyName);
}
return result.ToString();
}
/// <summary>
/// Returns the SQL data type equivalent, as a string for use in SQL script generation methods.
/// </summary>
private static string GetSQLTypeAsString(Type DataType)
{
switch (DataType.Name)
{
case "Boolean": return "[bit]";
case "Char": return "[char]";
case "SByte": return "[tinyint]";
case "Int16": return "[smallint]";
case "Int32": return "[int]";
case "Int64": return "[bigint]";
case "Byte": return "[tinyint] ";
case "Byte[]": return "[varbinary] (max)";
case "UInt16": return "[smallint] ";
case "UInt32": return "[int] ";
case "UInt64": return "[bigint] ";
case "Single": return "[float]";
case "Double": return "[double]";
case "Decimal": return "[decimal]";
case "DateTime": return "[datetime]";
case "Guid": return "[uniqueidentifier]";
case "Object": return "[variant]";
case "String": return "[nvarchar](max)";
default: throw new Exception( "未处理的类型" + DataType.Name ) ;
}
}
}
然后是批量导入
private void SqlBulkCopyByDatatable(string connectionString, string TableName, DataTable dt)
{
dt = CreateDataTable();
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction))
{
try
{
sqlbulkcopy.DestinationTableName = TableName;
for (int i = 0; i < dt.Columns.Count; i++)
{
sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
}
sqlbulkcopy.WriteToServer(dt);
//textBox3.Text = "导入成功,记录数 " + dt.Rows.Count;
}
catch (System.Exception ex)
{
//tabControl1.SelectedIndex = 1;
//textBox3.Text = ex.Message + "\r\n\r\n" + ex.StackTrace;
}
}
}
}