数据表创建及利用反射构建SQL语句、DataTable转实体

数据表的创建

private static void CreateNewDBTable()
{
    var dbDataReader = App.DB.ExecuteReader("select * from sqlite_master where  type='table'
    if (!dbDataReader.HasRows)
    {
        NameValueCollection nvc = new NameValueCollection() {

            { "ID","INTEGER PRIMARY KEY AUTOINCREMENT" },
            { "Fenlei","TEXT" },
            { "SrcFenlei","TEXT" },
            { "Records","INTEGER" },
            { "GroupItemCount","INTEGER" },
            { "SingRowDataCount","INTEGER" },
            { "Times6","INTEGER" },
            { "Times5","INTEGER" },
            { "Times4","INTEGER" },
            { "Times3","INTEGER" },
            { "Times2","INTEGER" },
            { "Times1","INTEGER" },
        };
        App.DB.NewTable("DT_CombinCondition", nvc);
    }
    else
    {  //业务需要，如果存在该数据表，则清空其中数据
        App.DB.ExecuteNonQuery("Delete from DT_CombinCondition");
    }

利用反射构造Insert_SQL语句

/// <summary>
/// 利用反射构造Insert_SQL语句
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="model"></param>
/// <param name="tableName"></param>
/// <param name="IDKey"></param>
/// <returns></returns>
private static string BuildInsertSql<T>(T model, string tableName, string IDKey)
{
    Type type = model.GetType();
    string sql = string.Empty;
    string values = string.Empty;
    string keys = string.Empty;
    foreach (var p in type.GetProperties())
    {
        if (p.Name == IDKey)//主键直接跳过
        {
            continue;
        }

        if (p.GetValue(model, null) != null)
        {
            keys += p.Name + " ,";
            if (p.PropertyType.Name.Contains("String") || p.PropertyType.FullName.Contains("DateTime"))
            {
                values += "'" + p.GetValue(model, null) + "',";
            }
            else
            {
                values += p.GetValue(model, null) + ",";
            }
        }
    }
    keys = keys.TrimEnd(',');
    values = values.TrimEnd(',');
    sql = string.Format("insert into {0}({1}) values({2});", tableName, keys, values);
    return sql;
}

利用反射将DataTable 数据写入实体

public List<CombinCondition> GetList()  
{
    var List = new List<CombinCondition>();
    var sql = "select * from DT_CombinCondition";
    var dt = App.DB.GetDataTable(sql);

    Type type = typeof(CombinCondition);
    foreach (DataRow dr in dt.Rows)
    {
        var entity = new CombinCondition();
        foreach (PropertyInfo p in type.GetProperties())
        {
            var tmpName = p.Name;
            if (dt.Columns.Contains(tmpName))
            {
                if (!p.CanWrite) // 判断此属性是否有Setter
                {
                    continue;  
                }
                var value = dr[tmpName];//取值  
                if (value != DBNull.Value) //如果非空，则赋给对象的属性 
                {
                    p.SetValue(entity, value, null);
                }
            }
        }
        List.Add(entity);
    }
    return List;
}