最后在写一个项目，数据源自NC接口，需要将NC接口返回的数据处理后导入Sqlite数据库。
在实际操作中需要直接写入Datatable的对象进数据库，或直接将一行数据库写入数据，但每次构建SQL这些太麻烦。因此才有了这样一个处理类。
/// <summary>
/// 根据DataTable构建插入语句
/// </summary>
/// <param name="dataTable"></param>
/// <returns></returns>
public static string builderSql(DataTable dataTable)
{
StringBuilder sb = new StringBuilder("insert into "+ dataTable.TableName + "(");
StringBuilder head = new StringBuilder();
StringBuilder values = new StringBuilder();
foreach (DataColumn col in dataTable.Columns)
{
head.Append(col.ColumnName + ",");
values.Append("@" + col.ColumnName + ",");
}
head.Remove(head.Length - 1, 1);
values.Remove(values.Length - 1, 1);
sb.Append(head).Append(") VALUES (").Append(values).Append(");");
sb.Append("select last_insert_rowid();");
return sb.ToString();
}
/// <summary>
/// 插入行
/// </summary>
/// <param name="row"></param>
/// <returns>数据库ID</returns>
public static int InsertDataRow(DataRow row)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
try
{
string SQLString = builderSql(row.Table);
List<SQLiteParameter> sqlitePars = new List<SQLiteParameter>();
foreach (DataColumn col in row.Table.Columns)
{
SQLiteParameter pars = new SQLiteParameter();
pars.ParameterName = string.Format("@{0}", col.ColumnName);
pars.Value = row[col.ColumnName];
pars.DbType = GetDbType(col.DataType);
sqlitePars.Add(pars);
}
PrepareCommand(cmd, connection, null, SQLString, sqlitePars.ToArray());
int rows = Convert.ToInt32(cmd.ExecuteScalar());
cmd.Parameters.Clear();
return rows;
}
catch (System.Data.SQLite.SQLiteException sqlex)
{
throw sqlex;
}
}
}
}
/// <summary>
/// 批量插入
/// </summary>
/// <param name="dataTable">需插入数据库的表</param>
/// <param name="DataBaseName">数据库名称</param>
/// <returns></returns>
public static void InsertDataTable(DataTable dataTable)
{
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
{
conn.Open();
using (SQLiteTransaction trans = conn.BeginTransaction())
{
SQLiteCommand cmd = new SQLiteCommand();
try
{
// 构建插入语句
string cmdText = builderSql(dataTable);
//循环
foreach (DataRow row in dataTable.Rows)
{
string BaseName = dataTable.TableName;
// 构建插入值
List<SQLiteParameter> sqlitePars = new List<SQLiteParameter>();
foreach (DataColumn col in dataTable.Columns)
{
SQLiteParameter pars = new SQLiteParameter();
pars.ParameterName = string.Format("@{0}", col.ColumnName);
pars.Value = row[col.ColumnName];
pars.DbType = GetDbType(col.DataType);
sqlitePars.Add(pars);
}
PrepareCommand(cmd, conn, trans, cmdText, sqlitePars.ToArray());
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
trans.Commit();
}
catch
{
trans.Rollback();
throw;
}
}
}
}自动获取对象数据类型
private static Dictionary typeMap;
#region 类型对应
static SQLite()
{
typeMap = new Dictionary();
typeMap[typeof(string)] = DbType.String;
typeMap[typeof(byte)] = DbType.Byte;
typeMap[typeof(int)] = DbType.Int32;
typeMap[typeof(Int16)] = DbType.Int16;
typeMap[typeof(Int32)] = DbType.Int32;
typeMap[typeof(Int64)] = DbType.Int64;
typeMap[typeof(DateTime)] = DbType.DateTime;
typeMap[typeof(decimal)] = DbType.Decimal;
typeMap[typeof(float)] = DbType.Double;
typeMap[typeof(double)] = DbType.Double;
}
public static DbType GetDbType(Type giveType)
{
giveType = Nullable.GetUnderlyingType(giveType) ?? giveType;
if (typeMap.ContainsKey(giveType))
{
return typeMap[giveType];
}
throw new ArgumentException(string.Format("{0} is not a supported .NET class", giveType.FullName));
}
public static DbType GetDbType()
{
return GetDbType(typeof(T));
}
#endregion
