- 需求 : 使用sqlite数据库, 将文件A.db 中某张数据表的数据,备份到文件B.db中。
解决方案: - 使用dataTableUpdate。不推荐
- 可以使用事务进行批量处理。
目的: 避免手动拼接大量insert into sql语句,直接将dataTable 作为参数,实现批量插入。
主要代码逻辑,及调用方式如下。
//建表
string createSql = $"CREATE TABLE {table}" +
"(EPC varchar(32),UID varchar(20),q_time varchar(8),flag INTEGER," +
" PRIMARY KEY(EPC, UID, q_time))";
SqlHelper.ExecuteNonQuery(createSql);
//insert 语句
string commandText = $"INSERT INTO {table}(EPC,UID,q_time,flag)VALUES(@EPC,@UID,@q_time,@flag)";
SqlHelper.ExecuteMutliQuery(commandText, dt);
调用方法
public int ExecuteMutliQuery(string commandText, DataTable dtData)
{
int res = 0;
if (Conn.State == ConnectionState.Closed)
Conn.Open();
using (SQLiteTransaction dbTrans = Conn.BeginTransaction())
{
try
{
foreach (DataRow row in dtData.Rows)
{
res += ExecuteNonQuery(dbTrans, commandText, row.ItemArray);
}
dbTrans.Commit();
}
catch (Exception ex)
{
res = -1;
dbTrans.Rollback();
throw ex;
}
finally
{
//Conn.Close();
}
}
return res;
}
///调用方法
public int ExecuteNonQuery(SQLiteTransaction transaction, string commandText, params object[] paramList)
{
if (transaction == null) throw new ArgumentNullException("transaction is null");
if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rolled back or committed,please provide an open transaction.", "transaction");
using (IDbCommand cmd = transaction.Connection.CreateCommand())
{
cmd.CommandText = commandText;
AttachParameters((SQLiteCommand)cmd, cmd.CommandText, paramList);
if (transaction.Connection.State == ConnectionState.Closed)
transaction.Connection.Open();
int result = cmd.ExecuteNonQuery();
return result;
}
}
#region AttachParameters(SQLiteCommand,commandText,object[] paramList)
/// <summary>
/// 增加参数到命令(自动判断类型)
/// </summary>
/// <param name="commandText">命令语句</param>
/// <param name="paramList">object参数列表</param>
/// <returns>返回SQLiteParameterCollection参数列表</returns>
private SQLiteParameterCollection AttachParameters(SQLiteCommand cmd, string commandText, params object[] paramList)
{
if (paramList == null || paramList.Length == 0) return null;
SQLiteParameterCollection coll = cmd.Parameters;
string parmString = commandText.Substring(commandText.IndexOf("@"));
// pre-process the string so always at least 1 space after a comma.
parmString = parmString.Replace(",", " ,");
// get the named parameters into a match collection
string pattern = @"(@)\S*(.*?)\b";
Regex ex = new Regex(pattern, RegexOptions.IgnoreCase);
MatchCollection mc = ex.Matches(parmString);
string[] paramNames = new string[mc.Count];
int i = 0;
foreach (Match m in mc)
{
paramNames[i] = m.Value;
i++;
}
// now let's type the parameters
int j = 0;
Type t = null;
foreach (object o in paramList)
{
t = o.GetType();
SQLiteParameter parm = new SQLiteParameter();
switch (t.ToString())
{
case ("DBNull"):
case ("Char"):
case ("SByte"):
case ("UInt16"):
case ("UInt32"):
case ("UInt64"):
throw new SystemException("Invalid data type");
case ("System.String"):
parm.DbType = DbType.String;
parm.ParameterName = paramNames[j];
parm.Value = (string)paramList[j];
coll.Add(parm);
break;
case ("System.Byte[]"):
parm.DbType = DbType.Binary;
parm.ParameterName = paramNames[j];
parm.Value = (byte[])paramList[j];
coll.Add(parm);
break;
case ("System.Int32"):
parm.DbType = DbType.Int32;
parm.ParameterName = paramNames[j];
parm.Value = (int)paramList[j];
coll.Add(parm);
break;
case ("System.Int64"):
parm.DbType = DbType.Int32;
parm.ParameterName = paramNames[j];
parm.Value = Convert.ToInt32(paramList[j]);
coll.Add(parm);
break;
case ("System.Boolean"):
parm.DbType = DbType.Boolean;
parm.ParameterName = paramNames[j];
parm.Value = (bool)paramList[j];
coll.Add(parm);
break;
case ("System.DateTime"):
parm.DbType = DbType.DateTime;
parm.ParameterName = paramNames[j];
parm.Value = Convert.ToDateTime(paramList[j]);
coll.Add(parm);
break;
case ("System.Double"):
parm.DbType = DbType.Double;
parm.ParameterName = paramNames[j];
parm.Value = Convert.ToDouble(paramList[j]);
coll.Add(parm);
break;
case ("System.Single"):
case ("System.Decimal"):
parm.DbType = DbType.Decimal;
parm.ParameterName = paramNames[j];
parm.Value = Convert.ToDecimal(paramList[j]);
coll.Add(parm);
break;
case ("System.Guid"):
parm.DbType = DbType.Guid;
parm.ParameterName = paramNames[j];
parm.Value = (System.Guid)(paramList[j]);
coll.Add(parm);
break;
case ("System.Object"):
parm.DbType = DbType.Object;
parm.ParameterName = paramNames[j];
parm.Value = paramList[j];
coll.Add(parm);
break;
default:
throw new SystemException("Value is of unknown data type");
} // end switch
j++;
}
return coll;
}
#endregion