分享MSSQL、MySql、Oracle的良数目批量导入方法及编程手法细节

1:MSSQL

SQL语法篇:

BULK INSERT   
   [ database_name . [ schema_name ] . | schema_name . ] [ table_name | view_name ]   
      FROM 'data_file'   
     [ WITH   
    (   
   [ [ , ] BATCHSIZE = batch_size ]   
   [ [ , ] CHECK_CONSTRAINTS ]   
   [ [ , ] CODEPAGE = { 'ACP' | 'OEM' | 'RAW' | 'code_page' } ]   
   [ [ , ] DATAFILETYPE =   
      { 'char' | 'native'| 'widechar' | 'widenative' } ]   
   [ [ , ] FIELDTERMINATOR = 'field_terminator' ]   
   [ [ , ] FIRSTROW = first_row ]   
   [ [ , ] FIRE_TRIGGERS ]   
   [ [ , ] FORMATFILE = 'format_file_path' ]   
   [ [ , ] KEEPIDENTITY ]   
   [ [ , ] KEEPNULLS ]   
   [ [ , ] KILOBYTES_PER_BATCH = kilobytes_per_batch ]   
   [ [ , ] LASTROW = last_row ]   
   [ [ , ] MAXERRORS = max_errors ]   
   [ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]   
   [ [ , ] ROWS_PER_BATCH = rows_per_batch ]   
   [ [ , ] ROWTERMINATOR = 'row_terminator' ]   
   [ [ , ] TABLOCK ]   
   [ [ , ] ERRORFILE = 'file_name' ]   
    )]   

SQL示例:

 bulk insert 表名  from 'D:\mydata.txt' 
with 
 (fieldterminator=',', 
 rowterminator='\n', 
 check_constraints) 
select * from 表名 

由于C#提供了SqlBulkCopy,所以非DBA的我们,更多见面经过序来调用:

C#代码篇:

C#代码调用示例及细节,以下代码摘录自CYQ.Data:

using (SqlBulkCopy sbc = new SqlBulkCopy(con, (keepID ? SqlBulkCopyOptions.KeepIdentity : SqlBulkCopyOptions.Default) | SqlBulkCopyOptions.FireTriggers, sqlTran))
                    {
                        sbc.BatchSize = 100000;
                        sbc.DestinationTableName = SqlFormat.Keyword(mdt.TableName, DalType.MsSql);
                        sbc.BulkCopyTimeout = AppConfig.DB.CommandTimeout;
                        foreach (MCellStruct column in mdt.Columns)
                        {
                            sbc.ColumnMappings.Add(column.ColumnName, column.ColumnName);
                        }
                        sbc.WriteToServer(mdt);
                    }

有5个细节:

1:事务:

一旦仅仅是单个事务,构造函数可以是链接字符串。

只要用跟外部合成一个事情(比如事先去,再插入,这在和一个作业中)

就待自己组织Connection对象和Transaction,在内外文中传递来处理。

2:插入是否吸引触发器

通过SqlBulkCopyOptions.FireTriggers 引入

3:其它:批量往往、超时时间、是否刻画副主键ID。

莫不引发的数据库Down机的场面:

以史之长河中,我遇到过之一个大坑是:

当数码的尺寸过长,数据的字段过差,产生多少二进制截断时,数据库服务甚至停掉了(也许是特例,也许不是)。

所以小心用,尽力做好针对性表数据做好数据长度验证。

2:MySql

有关MySql的批量,这是平段落悲催的往事,有几个坑,直到今天,才发觉并缓解了。

SQL语法篇:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'data.txt'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [FIELDS
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char' ]
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number LINES]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...)]

示例篇:

LOAD DATA LOCAL INFILE 'C:\\Users\\cyq\\AppData\\Local\\Temp\\BulkCopy.csv' INTO TABLE `BulkCopy` CHARACTER SET utf8 FIELDS TERMINATED BY '$,$' LINES TERMINATED BY '
' (`ID`,`Name`,`CreateTime`,`Sex`)

尽管如此MySql.Data.dll 提供了MySqlBulkLoader,但是关押源码只是挺成了只Load
Data 并就此ADO.NET执行,

基本大坑的变化*.csv数据文件的竟然从未提供,所以自己生成语句并履行就好了,不需因此她。

C#代码篇:

以下代码摘自CYQ.Data,是均等截今天才修正好的代码:

 private static string MDataTableToFile(MDataTable dt, bool keepID, DalType dalType)
        {
            string path = Path.GetTempPath() + dt.TableName + ".csv";
            using (StreamWriter sw = new StreamWriter(path, false, new UTF8Encoding(false)))
            {
                MCellStruct ms;
                string value;
                foreach (MDataRow row in dt.Rows)
                {
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        #region 设置值
                        ms = dt.Columns[i];
                        if (!keepID && ms.IsAutoIncrement)
                        {
                            continue;
                        }
                        else if (dalType == DalType.MySql && row[i].IsNull)
                        {
                            sw.Write("\\N");//Mysql用\N表示null值。
                        }
                        else
                        {
                            value = row[i].ToString();
                            if (ms.SqlType == SqlDbType.Bit)
                            {
                                int v = (value.ToLower() == "true" || value == "1") ? 1 : 0;
                                if (dalType == DalType.MySql)
                                {
                                    byte[] b = new byte[1];
                                    b[0] = (byte)v;
                                    value = System.Text.Encoding.UTF8.GetString(b);//mysql必须用字节存档。
                                }
                                else
                                {
                                    value = v.ToString();
                                }

                            }
                            else
                            {
                                value = value.Replace("\\", "\\\\");//处理转义符号
                            }
                            sw.Write(value);
                        }

                        if (i != dt.Columns.Count - 1)//不是最后一个就输出
                        {
                            sw.Write(AppConst.SplitChar);
                        }
                        #endregion
                    }
                    sw.WriteLine();
                }
            }
            if (Path.DirectorySeparatorChar == '\\')
            {
                path = path.Replace(@"\", @"\\");
            }
            return path;
        }

上述代码是生一个csv文件,用于给调用,有少只为主之坑,费了自不少年华:

1:Bit类型数据导不进入?

2:第1执行数据自增ID被重置为1?

当时片单问题,网上搜不到答案,放纵到今,觉的应当解决了,然后就把它解决了。

釜底抽薪的思路是这般的:

A:先用Load Data OutFile导出一个文件,再用Load Data InFile导入文本。

平开始自我因此记事本打开看了一晃,又顺手Ctrl+S了瞬间,结果发现题目及自我之同,让自家怀疑竟然不支持?

直至今天,重新导出,中间不扣了,直接导入,发现它们还是还要健康的,于是,思维一样转:

B:把温馨别的公文以及下令产生的文件,进行了十六进制比对,结果发现:

图片 1

Bit类型自己变的底数额:是0,1,在十六向前制下显示是30、31。

命产生的数量在十六进制是00、01,查了产资料,发现MySql的Bit存档的Bit是二进制。

于是乎,把0,1之所以字节表示,再转字符串,再存档,就吓了。

于是乎这样一段落代码有了(网上的DataTable转CSV代码都是不曾处理的,都非知底他们是怎跑的,难道还没有定义Bit类型?):

if (ms.SqlType == SqlDbType.Bit)
{
     int v = (value.ToLower() == "true" || value == "1") ? 1 : 0;
     if (dalType == DalType.MySql)
     {
           byte[] b = new byte[1];
           b[0] = (byte)v;
           value = System.Text.Encoding.UTF8.GetString(b);//mysql必须用字节存档。
       }
      else
       {
            value = v.ToString();
       }
}

除此以外关于Null值,用\N表示。

缓解得了第一个问题,剩下就是是第二只问题了,为什么第一个行代码的主键会受置为1?

或者比对十六进制,结果惊人的意识:

图片 2

举凡BOM头,让它错识别了第一只主键值,所以叫忽视主键,用了第1个自增值1代表了。

旋即吗说明了干吗要复保存之数码都生Bug的原因。

于是乎,解决的法门就是是StreaWrite的时节,不生成BOM头,怎么处理啊?

于是乎便起了以下的代码:

 using (StreamWriter sw = new StreamWriter(path, false, new UTF8Encoding(false)))
{
       ...................
}

经过New一个Encoding,并指定参数为false,替代我们健康的System.Text.Encoding.UTF8Encoding。

这些细节颇隐秘,不说您都猜不道。。。

3:Oracle

SQL语法篇

LOAD[DATA]
[ { INFILE | INDDN } {file | * }
[STREAM | RECORD | FIXED length [BLOCKSIZE size]|
VARIABLE [length] ]
[ { BADFILE | BADDN } file ]
{DISCARDS | DISCARDMAX} integr ]
[ {INDDN | INFILE} . . . ]
[ APPEND | REPLACE | INSERT ]
[RECLENT integer]
[ { CONCATENATE integer |
CONTINUEIF { [THIS | NEXT] (start[: end])LAST }
Operator { 'string' | X 'hex' } } ]
INTO TABLE [user.]table
[APPEND | REPLACE|INSERT]
[WHEN condition [AND condition]...]
[FIELDS [delimiter] ]
(
column {
RECNUM | CONSTANT value |
SEQUENCE ( { integer | MAX |COUNT} [, increment] ) |
[POSITION ( { start [end] | * [ + integer] }
) ]
datatype
[TERMINATED [ BY ] {WHITESPACE| [X] 'character' } ]
[ [OPTIONALLY] ENCLOSE[BY] [X]'charcter']
[NULLIF condition ]
[DEFAULTIF condotion]
}
[ ,...]
)

上述配置存档成一个CTL文件,再由以下的命调用:

Sqlldr userid=用户名/密码@数据库 control=文件名.ctl 

C#语法篇:

.NET里大概发生三种操作Oracle的一手:

1:System.Data.OracleClient
(需要安装客户端)没有带来批量艺术(还区分x86与x64)。

2:Oracle.DataAccess  (需要设置客户端)带批量主意(也区分x86以及x64)。

3:Oracle.ManagedDataAccess
(不需装客户端)没带批量智(不区分x86跟x64,但单支持.NET
4.0或者以上)

Oracle.DataAccess
带的批量艺术为:OracleBulkCopy,由于采取方式以及SqlBulkCopy几乎一样,就非介绍了。

图片 3

要调用程序所当的服务器安装了Oracle客户端,可以开展以下办法的调用:

流程如下:

1:产生*.cvs数据文件,见MySql中的代码,一样用的。

2:产生*.ctl控制文件,把转变的Load Data 语句存档成一个*.ctl文件即可。

3:用sqlidr.exe执行CTL文件,这里悲催的少数是,不可知为此ADO.NET调用,只能用经过调用,所以,这个批量不得不单独采取。

调用进程的连锁代码:

 bool hasSqlLoader = false;
        private bool HasSqlLoader() //检测是否安装了客户端。
        {
            hasSqlLoader = false;
            Process proc = new Process();
            proc.StartInfo.FileName = "sqlldr";
            proc.StartInfo.CreateNoWindow = true;
            proc.StartInfo.UseShellExecute = false;
            proc.StartInfo.RedirectStandardOutput = true;

            proc.OutputDataReceived += new DataReceivedEventHandler(proc_OutputDataReceived);
            proc.Start();
            proc.BeginOutputReadLine();
            proc.WaitForExit();
            return hasSqlLoader;
        }

        void proc_OutputDataReceived(object sender, DataReceivedEventArgs e)
        {
            if (!hasSqlLoader)
            {
                hasSqlLoader = e.Data.StartsWith("SQL*Loader:");
            }
        }
        //已经实现,但没有事务,所以暂时先不引入。
        private bool ExeSqlLoader(string arg)
        {
            try
            {
                Process proc = new Process();
                proc.StartInfo.FileName = "sqlldr";
                proc.StartInfo.Arguments = arg;
                proc.Start();
                proc.WaitForExit();
                return true;
            }
            catch
            {

            }
            return false;
        }

总结:

趁大数目的推广,数据中的批量活动必然越来频繁之为提到,所以管是因此SQL脚本,还是要好写代码,或是用DBImport工具,都将化必需技能有了!

鉴于此,分享一下自家当即时无异于块费了的力和填过的坑,供大家参考!

相关文章