Jalena Blog

最后在写一个项目，数据源自NC接口，需要将NC接口返回的数据处理后导入Sqlite数据库。

在实际操作中需要直接写入Datatable的对象进数据库，或直接将一行数据库写入数据，但每次构建SQL这些太麻烦。因此才有了这样一个处理类。

构建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