把DataTable导入SqlServer中

首先是建表

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}",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}",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 + "rnrn" + ex.StackTrace;
                    }
                }
            }
        }

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!