/*
         *  要分两步来处理：
            1、mysql数据库开启允许本地导入数据的配置，命令如下：
            SET GLOBAL local_infile=1；//1表示开启，0表示关闭
            查看该配置的状态命令如下：

            SHOW VARIABLES LIKE '%local%';

            2、第二步就是在项目里面的数据库连接字符串做设置
            数据库连接字符串要加上”AllowLoadLocalInfile=true“

            如下：

            const string ConnectionString = "server=localhost;port=3306;user=root;password=123456;database=mysql;SslMode = none;AllowLoadLocalInfile=true";
            总结：
            经过以上两步操作即可解决The used command is not allowed with this MySQL version的错误。

            3、因为数据库并不在项目所在的服务器，所以MySqlBulkLoader中要设置Local = true 读取本地文件，进行导入
         * 
         * 
         * */

        /// <summary>
        /// 生成datatable
        /// </summary>
        static public void fnBatchInsert2()
        {
            string connStr = "server=127.0.0.1;uid=root;pwd=Root;database=testdb;CharSet=utf8;Allow User Variables=True;AllowLoadLocalInfile=true";

            DataTable oDtData = new DataTable();
            oDtData.TableName = "t_compareresult";//对应数据库表名
            oDtData.Columns.AddRange(new DataColumn[] {
                 new DataColumn("Id",typeof(long)),//主键列占位，没有可能会列数据串了
                 new DataColumn("CompareTime",typeof(DateTime)),
                 new DataColumn("TargetUrl",typeof(string)),
                 new DataColumn("ContrastUrl",typeof(string)),
                 new DataColumn("Similarity",typeof(float)),
                 new DataColumn("CompareResult",typeof(string))
             });

            string sFileFullPath = @"E:/AppsDemo/WinFace1/bin/Debug/201211_0_0.txt";

            using (StreamReader oReader = new StreamReader(sFileFullPath, Encoding.UTF8))
            {
                string sLine = "";

                while ((sLine = oReader.ReadLine()) != null)
                {
                    string[] sArrStr = sLine.Split('\t');//每行数据列以\t分割的，可以修改自己的逻辑

                    if (null != sArrStr && sArrStr.Length > 4)
                    {
                        DataRow oDr = oDtData.NewRow();
                        oDr["CompareTime"] = sArrStr[0];
                        oDr["TargetUrl"] = sArrStr[1];
                        oDr["ContrastUrl"] = sArrStr[2];
                        oDr["Similarity"] = sArrStr[3];
                        oDr["CompareResult"] = sArrStr[4];
                        oDtData.Rows.Add(oDr);
                    }
                }
                oReader.Close();
                oReader.Dispose();
            }

            var result = fnBulkInsert(connStr, oDtData);

        }

        /// <summary>  
        /// 将DataTable转换为标准的CSV  
        /// </summary>  
        /// <param name="_oDt">数据表</param>  
        /// <returns>返回标准的CSV</returns>  
        static private string fnDataTableToCsv(DataTable _oDt)
        {
            //以半角逗号（即,）作分隔符，列为空也要表达其存在。  
            //列内容如存在半角逗号（即,）则用半角引号（即""）将该字段值包含起来。  
            //列内容如存在半角引号（即"）则应替换成半角双引号（""）转义，并用半角引号（即""）将该字段值包含起来。  
            StringBuilder oStrBd = new StringBuilder();
            DataColumn oColum;
            foreach (DataRow oRow in _oDt.Rows)
            {
                for (int i = 0; i < _oDt.Columns.Count; i++)
                {
                    oColum = _oDt.Columns[i];
                    if (i != 0) oStrBd.Append(",");
                    if (oColum.DataType == typeof(string) && oRow[oColum].ToString().Contains(","))
                    {
                        oStrBd.Append("\"" + oRow[oColum].ToString().Replace("\"", "\"\"") + "\"");
                    }
                    else oStrBd.Append(oRow[oColum].ToString());
                }
                oStrBd.AppendLine();
            }
            return oStrBd.ToString();
        }

        /// <summary>
        /// 批量插入mysql
        /// </summary>
        /// <param name="_sConnectionString"></param>
        /// <param name="_oDt"></param>
        /// <returns></returns>
        static public int fnBulkInsert(string _sConnectionString, DataTable _oDt)
        {
            if (string.IsNullOrEmpty(_oDt.TableName)) throw new Exception("请给DataTable的TableName属性附上表名称");
            if (_oDt.Rows.Count == 0) return 0;
            int nInsertCount = 0;
            string sTmpPath = Path.Combine(Directory.GetCurrentDirectory(), "Temp.csv"); //Path.GetTempFileName();
            string sCsv = fnDataTableToCsv(_oDt);
            File.WriteAllText(sTmpPath, sCsv);
            using (MySqlConnection oConn = new MySqlConnection(_sConnectionString))
            {
                try
                {
                    System.Diagnostics.Stopwatch oStopwatch = new System.Diagnostics.Stopwatch();
                    oStopwatch.Start();
                    oConn.Open();
                    MySqlBulkLoader oBulk = new MySqlBulkLoader(oConn)
                    {
                        FieldTerminator = ",",
                        FieldQuotationCharacter = '"',
                        EscapeCharacter = '"',
                        LineTerminator = "\r\n",
                        FileName = sTmpPath,
                        NumberOfLinesToSkip = 0,
                        TableName = _oDt.TableName,
                        Local = true
                        ,ConflictOption = MySqlBulkLoaderConflictOption.Ignore//忽略主键冲突的任何行
                    };
                    nInsertCount = oBulk.Load();
                    oStopwatch.Stop();
                    Console.WriteLine("耗时:{0}", oStopwatch.ElapsedMilliseconds);
                }
                catch (MySqlException ex)
                {
                    throw ex;
                }
            }
            //File.Delete(tmpPath);
            return nInsertCount;
        }

        /// <summary>
        /// MySqlBulkLoader批量写入
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btnBulkWrite2_Click(object sender, EventArgs e)
        {
            fnBatchInsert2();
        }