首先是建表
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 + ")"; } /// /// Creates a SQL script that creates a table where the columns matches that of the specified DataTable. /// 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 ()) { 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(); } /// /// Builds an ALTER TABLE script that adds a primary or composite key to a table that already exists. /// 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 compositeKeys = Table.PrimaryKey.OfType ().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(); } /// /// Returns the SQL data type equivalent, as a string for use in SQL script generation methods. /// 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; } } } }