SQLite大量insert

001.using System;

002.using System.Collections.Generic;

003.using System.Data;

004.using System.Data.SQLite;

005.using System.Diagnostics;

006.using System.Text.RegularExpressions;

007.using Utility;

008. 

009.namespace DAL

010.{

011.    /// <summary>

012.    /// 数据存取层模块

013.    /// 用途：批次数据处理

014.    /// 作者：林大猫

015.    /// </summary>

016.    public class BatchWork

017.    {

018.        DataAccessLayerSqlite dal = new DataAccessLayerSqlite();

019.        /// <summary>

020.        /// 插入表格指令

021.        /// </summary>

022.        /// <param name="TableName">表格名</param>

023.        /// <param name="condition">where条件</param>

024.        /// <param name="InData">要输入的数据数组</param>

025.        public void InsertTable(string TableName, string[] InData)

026.        {

027.            string sql = string.Empty;

028.            try

029.            {

030.                if (InData.Length == 0)

031.                {

032.                    return;

033.                }

034.                string columnName = GetColumnName(TableName);

035.                string columnNameParm = GetColumnNameParm(TableName);

036.                string[] columnNameArray = GetColumnNameArrary(TableName);

037.                sql = " insert into " + TableName + "(" + columnName + " ) values ( " + columnNameParm + " )";

038.                List<SQLiteParameter> sqlParm = new List<SQLiteParameter>();

039.                for (int i = 0; i < InData.Length; i++)

040.                {

041.                    sqlParm.Add(new SQLiteParameter(columnNameArray[i], DbType.String));

042.                    sqlParm[i].Value = string.Copy(InData[i]);

043.                }

044.                dal.NonQueryResultNoTranc(sql, sqlParm.ToArray());

045.            }

046.            catch (Exception e)

047.            {

048.                throw e;

049.            }

050.        }

051. 

052. 

053. 

054.        /// <summary>

055.        /// 从文字文件写入表格.

056.        /// </summary>

057.        /// <param name="tableName">Name of the table.</param>

058.        /// <param name="fileName">Name of the file.</param>

059.        public void InsertTableFromTextFile(string tableName, string fileName)

060.        {

061.            string sql = string.Empty;

062.            if (fileName.Length == 0)

063.            {

064.                return;

065.            }

066.            int[] format = GetColumnLengths(tableName);

067.            int remainder = 1000;

068.            List<string> textContent = fileName.ImportText();

069.            string columnName = GetColumnName(tableName);

070.            string columnNameParm = GetColumnNameParm(tableName);

071.            string[] columnNameArray = GetColumnNameArrary(tableName);

072.            sql = " insert into " + tableName + "(" + columnName + " ) values ( " + columnNameParm + " )";

073.            using (SQLiteConnection Sql_Conn = new SQLiteConnection(dal.connectionString))

074.            {

075.                Sql_Conn.Open();

076.                SQLiteTransaction Sql_Transaction;

077.                Sql_Transaction = Sql_Conn.BeginTransaction(System.Data.IsolationLevel.Serializable);

078.                for (int i = 0; i < textContent.Count; i++)

079.                {

080.                    List<string> InData = textContent[i].SplitString(format);

081.                    List<SQLiteParameter> sqlParm = new List<SQLiteParameter>();

082.                    for (int j = 0; j < format.Length; j++)

083.                    {

084.                        sqlParm.Add(new SQLiteParameter(columnNameArray[j], DbType.String));

085.                        sqlParm[j].Value = string.Copy(InData[j]);

086.                    }

087.                    SQLiteCommand Sql_Command = new SQLiteCommand(sql, Sql_Conn);

088.                    Sql_Command.Transaction = Sql_Transaction;

089.                    dal.PrepareCommand(ref Sql_Command, Sql_Conn, sql, sqlParm.ToArray());

090.                    Sql_Command.ExecuteNonQuery();

091.                    try

092.                    {

093.                        if (i == textContent.Count - 1)

094.                        {

095.                            Sql_Transaction.Commit();

096.                        }

097.                        else if (i > remainder && i % remainder == 0)

098.                        {

099.                            Sql_Transaction.Commit();

100.                            Sql_Transaction = Sql_Conn.BeginTransaction(System.Data.IsolationLevel.Serializable);

101.                        }

102.                        else

103.                        {

104.                            ;

105.                        }

106.                    }

107.                    catch (Exception ex)

108.                    {

109.                        Debug.Write(ex.ToString());

110.                        Sql_Transaction.Rollback();

111.                    }

112.                }

113. 

114.            }

115.        }

116. 

117. 

118.        /// <summary>

119.        /// 取得字段名称

120.        /// </summary>

121.        /// <param name="TableName">表格名</param>

122.        /// <returns>表格名称字符串用逗号分隔</returns>

123.        public static string GetColumnName(string TableName)

124.        {

125.            DAL.DataAccessLayerSqlite dal = new DAL.DataAccessLayerSqlite();

126.            DataTable dtSource = dal.GetQueryResultToDataTable("PRAGMA table_info( '" + TableName + "' );", null);

127.            List<string> columnNames = new List<string>();

128.            foreach (DataRow tableInfo in dtSource.Rows)

129.            {

130.                columnNames.Add(tableInfo["name"].ToString());

131.            }

132.            return string.Join(" , ", columnNames.ToArray());

133.        }

134. 

135.        /// <summary>

136.        /// 取得字段名称

137.        /// </summary>

138.        /// <param name="TableName">表格名</param>

139.        /// <returns>表格名称字符串用逗号分隔</returns>

140.        public string[] GetColumnNameArrary(string TableName)

141.        {

142.            DAL.DataAccessLayerSqlite dal = new DAL.DataAccessLayerSqlite();

143.            DataTable dtSource = dal.GetQueryResultToDataTable("PRAGMA table_info( '" + TableName + "' );", null);

144.            List<string> columnNames = new List<string>();

145.            foreach (DataRow tableInfo in dtSource.Rows)

146.            {

147.                columnNames.Add(tableInfo["name"].ToString());

148.            }

149.            return columnNames.ToArray();

150.        }

151.        /// <summary>

152.        /// 取得字段名称参数

153.        /// </summary>

154.        /// <param name="TableName">表格名</param>

155.        /// <returns>表格名称字符串用逗号分隔</returns>

156.        public string GetColumnNameParm(string TableName)

157.        {

158.            DAL.DataAccessLayerSqlite dal = new DAL.DataAccessLayerSqlite();

159.            DataTable dtSource = dal.GetQueryResultToDataTable("PRAGMA table_info( '" + TableName + "' );", null);

160.            List<string> columnNames = new List<string>();

161.            foreach (DataRow tableInfo in dtSource.Rows)

162.            {

163.                columnNames.Add("@" + tableInfo["name"].ToString());

164.            }

165.            return string.Join(" , ", columnNames.ToArray());

166.        }

167. 

168.        /// <summary>

169.        /// 根据表格的Create sql取得字段长度.

170.        ///

171.        /// </summary>

172.        /// <param name="TableName">Name of the table.</param>

173.        /// <returns></returns>

174.        public int[] GetColumnLengths(string TableName)

175.        {

176.            /* 格式如下

177.             * CREATE TABLE Bank (

178.                [Filter1]       [CHAR](1),

179.                [UnitNo]        [CHAR](3),

180.                [GroupNo]       [CHAR](4),

181.                [Filter2]       [CHAR](1),

182.                [GroupName]     [CHAR](40),

183.                [GroupNickName] [CHAR](10),

184.                [Address]       [CHAR](40),

185.                [Filter3]       [CHAR](1),

186.                [PhoneSecNum]   [CHAR](3),

187.                [PhoneNum]      [CHAR](8),

188.                [Filter4]       [CHAR](1),

189.                [ValueDate]     [CHAR](7),

190.                [ChangeType]    [CHAR](1),

191.                [DefaultCode]   [CHAR](1),

192.                [Remark]        [CHAR](2),

193.                [Blank]         [CHAR](8)

194.            );*/

195.            Regex MyRegex = new Regex(

196.             ".*\\[(?<ColumnName>.*)\\]\\s+\\[(?<ColumnType>.*)\\]\\((?<Len" +

197.             "gth>\\d{1,})\\).*",

198.           RegexOptions.IgnoreCase

199.           | RegexOptions.CultureInvariant

200.           | RegexOptions.IgnorePatternWhitespace

201.           | RegexOptions.Compiled

202.           );

203. 

204.            SplitItems items = new SplitItems(MyRegex);

205. 

206.            DAL.DataAccessLayerSqlite dao = new DAL.DataAccessLayerSqlite();

207. 

208.            DataTable dt = dao.GetQueryResultToDataTable(DAL.DbInfoDao.GetAllDbInfromation(), null);

209.            //return;

210.            DataRow[] selectedrows = dt.Select("Trim(TABLE_NAME)='" + TableName + "'");

211.            if (selectedrows.Length > 1 || selectedrows.Length == 0)

212.            {

213.                throw new Exception("找不到表格名称!");

214.            }

215.            List<int> columnLengths = new List<int>();

216. 

217.            foreach (DataRow dr in selectedrows)

218.            {

219.                DataTable dbinformation = items.StartSplit(dr["sql"].ToString());

220.                foreach (DataRow dvitem in dbinformation.Rows)

221.                {

222.                    columnLengths.Add(int.Parse(dvitem["Length"].ToString()));

223.                }

224.            }

225.            return columnLengths.ToArray();

226.        }

227.    }

228.}