mssqlserver 批量插入示例

浏览数：35 / 时间：2015年06月12日

        public bool DoQuestionSqlBulkCopy(DataTable dtDoQuestion,
            string DoQuestionName,
            DataTable dtDoQuestionText,
            string DoQuestionTextName,
            DataTable dtDoQuestionRemark,
            string DoQuestionRemarkName)
        {
            using (System.Data.SqlClient.SqlBulkCopy sqlBC = new System.Data.SqlClient.SqlBulkCopy(DBConnectionConfig.ConnectionString))
            {
                #region 选项指量插入
                if (dtDoQuestion.Rows.Count > 0)
                {
                    //每10W条数据一个事物
                    sqlBC.BatchSize = dtDoQuestion.Rows.Count;

                    //超时时间
                    sqlBC.BulkCopyTimeout = 60;

                    //表名Users
                    sqlBC.DestinationTableName = "dbo." + DoQuestionName;

                    //字段对应，分表为原数据表字段名，和导入数据库的字段名
                    sqlBC.ColumnMappings.Add("Tid", "Tid");
                    sqlBC.ColumnMappings.Add("SId", "SId");
                    sqlBC.ColumnMappings.Add("OId", "OId");
                    sqlBC.ColumnMappings.Add("SN", "SN");
                    sqlBC.ColumnMappings.Add("Score", "Score");
                    sqlBC.ColumnMappings.Add("Selected", "Selected");
                    sqlBC.ColumnMappings.Add("OPTIME", "OPTIME");

                    //导入到数据库
                    sqlBC.WriteToServer(dtDoQuestion);
                    sqlBC.ColumnMappings.Clear();
                }
                #endregion

                #region 文字选项批量保存
                if (dtDoQuestionText.Rows.Count > 0)
                {
                    //每10W条数据一个事物
                    sqlBC.BatchSize = dtDoQuestionText.Rows.Count;

                    //超时时间
                    sqlBC.BulkCopyTimeout = 60;

                    //表名DoquestionText
                    sqlBC.DestinationTableName = "dbo." + DoQuestionName;

                    //字段对应，分表为原数据表字段名，和导入数据库的字段名
                    sqlBC.ColumnMappings.Add("Tid", "Tid");
                    sqlBC.ColumnMappings.Add("SId", "SId");
                    sqlBC.ColumnMappings.Add("OId", "OId");
                    sqlBC.ColumnMappings.Add("SN", "SN");
                    sqlBC.ColumnMappings.Add("MId", "MId");
                    sqlBC.ColumnMappings.Add("Content", "Content");
                    sqlBC.ColumnMappings.Add("OPTIME", "OPTIME");

                    //导入到数据库
                    sqlBC.WriteToServer(dtDoQuestionText);
                    sqlBC.ColumnMappings.Clear();
                }
                #endregion

                #region 文字选项批量保存
                if (dtDoQuestionRemark.Rows.Count > 0)
                {
                    //每10W条数据一个事物
                    sqlBC.BatchSize = dtDoQuestionRemark.Rows.Count;

                    //超时时间
                    sqlBC.BulkCopyTimeout = 60;

                    //表名DoquestionText
                    sqlBC.DestinationTableName = "dbo." + DoQuestionRemarkName;

                    //字段对应，分表为原数据表字段名，和导入数据库的字段名
                    sqlBC.ColumnMappings.Add("Tid", "Tid");
                    sqlBC.ColumnMappings.Add("SId", "SId");
                    sqlBC.ColumnMappings.Add("SN", "SN");
                    sqlBC.ColumnMappings.Add("Content", "Content");
                    sqlBC.ColumnMappings.Add("OPTIME", "OPTIME");

                    //导入到数据库
                    sqlBC.WriteToServer(dtDoQuestionRemark);
                    sqlBC.ColumnMappings.Clear();
                }
                #endregion

                return true;
            }
        }