//--------------------------------------------------------------------------
//
// Copyright (c) BUSHUOSX. All rights reserved.
//
// File: Orm4Sqlite.cs
//
// Version:1.0.0.0
//
// Datetime:20170811
//
//--------------------------------------------------------------------------- using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.Data.SQLite;
using System.Data; namespace BUSHUOSX.Helper
{
public class Orm4Sqlite
{
[Flags]
public enum MemberFlags
{
None = ,
公共字段 = ,
非公字段 = ,
公共属性 = ,
非公属性 = ,
属性可读 = ,
属性可写 = ,
Default = + +
}
public static Dictionary<string, object> GetFiledAndPropetyValue(object obj, HashSet<string> ignore = null, MemberFlags flags = MemberFlags.Default)
{
if (null == obj)
{
return null;
} Dictionary<string, object> tmp = new Dictionary<string, object>(); //var fields = getFields(obj.GetType(), flags);
foreach (var item in getFields(obj.GetType(), flags))
{
if (null != ignore && ignore.Contains(item.Name))
{
continue;
}
tmp[item.Name] = item.GetValue(obj);
} //var properties = getProperties(obj.GetType(), flags);
foreach (var item in getProperties(obj.GetType(), flags))
{
if (null != ignore && ignore.Contains(item.Name))
{
continue;
}
tmp[item.Name] = item.GetValue(obj, null);
} return tmp;
} private static List<MemberInfo> GetFiledsAndPropetys(Type type, HashSet<string> ignore = null, MemberFlags flags = MemberFlags.Default)
{
List<MemberInfo> result = new List<MemberInfo>();
foreach (var item in getFields(type, flags))
{
if (null != ignore && ignore.Contains(item.Name))
{
continue;
}
result.Add(item);
}
foreach (var item in getProperties(type, flags))
{
if (null != ignore && ignore.Contains(item.Name))
{
continue;
}
result.Add(item);
}
return result;
} private static List<PropertyInfo> getProperties(Type type, MemberFlags flags)
{
List<PropertyInfo> tmp = new List<PropertyInfo>();
BindingFlags bfs = BindingFlags.Default; if ((flags & MemberFlags.公共属性) == MemberFlags.公共属性) bfs |= BindingFlags.Public;
if ((flags & MemberFlags.非公属性) == MemberFlags.非公属性) bfs |= BindingFlags.NonPublic;
if (bfs != BindingFlags.Default)
{
bfs |= BindingFlags.Instance | BindingFlags.Static;
var properties = type.GetProperties(bfs);
foreach (var item in properties)
{
if ((flags & MemberFlags.属性可读) == MemberFlags.属性可读)
{
if (!item.CanRead)
{
continue;
}
}
if ((flags & MemberFlags.属性可写) == MemberFlags.属性可写)
{
if (!item.CanWrite)
{
continue;
}
}
tmp.Add(item);
}
}
return tmp;
} private static List<FieldInfo> getFields(Type type, MemberFlags flags)
{
List<FieldInfo> tmp = new List<FieldInfo>();
BindingFlags bfs = BindingFlags.Default;
if ((flags & MemberFlags.公共字段) == MemberFlags.公共字段) bfs |= BindingFlags.Public;
if ((flags & MemberFlags.非公字段) == MemberFlags.非公字段) bfs |= BindingFlags.NonPublic;
if (bfs != BindingFlags.Default)
{
bfs |= BindingFlags.Instance | BindingFlags.Static;
var fileds = type.GetFields(bfs);
tmp.AddRange(fileds);
}
return tmp;
} private static string netType2sqliteType(Type type)
{
if (type.IsValueType)
{
if (type == typeof(float) || type == typeof(double))
{
return "REAL";
}
if (typeof(DateTime) == type)
{
return "DATETIME";
}
return "INTEGER";
} if (typeof(string) == type)
{
return "TEXT";
} return "BLOB";
} public static string GenarateCreateTableSql(Type type, MemberFlags flags = MemberFlags.Default, HashSet<string> ignore = null, Dictionary<string, string> ext = null)
{
Dictionary<string, Type> clms = new Dictionary<string, Type>(); foreach (var item in getFields(type, flags))
{
clms[item.Name] = item.FieldType;
} foreach (var item in getProperties(type, flags))
{
clms[item.Name] = item.PropertyType;
} StringBuilder sb = new StringBuilder();
if (clms.Count != || (null != ext && ext.Count != ))
{
//删除忽略colume
if (null != ignore)
{
foreach (var item in ignore)
{
clms.Remove(item);
}
} sb.AppendFormat(@"CREATE TABLE {0} (", type.Name);
foreach (var item in clms)
{
//sb.AppendFormat(@"{0} {1},", item.Key, item.Value.ToString());//第二项为sqlite数据类型
sb.AppendFormat(@"{0} {1},", item.Key, netType2sqliteType(item.Value));
} if (null != ext)
{
foreach (var item in ext)
{
sb.AppendFormat(@"{0} {1},", item.Key, item.Value);
}
} sb.Replace(",", ")", sb.Length - , );
} return sb.ToString();
} public static string GenarateInsertSql(Type type, MemberFlags flags = MemberFlags.Default, HashSet<string> ignore = null, HashSet<string> ext = null)
{
HashSet<string> columns = new HashSet<string>(); //获取字段
foreach (var item in getFields(type, flags))
{
columns.Add(item.Name);
} //获取属性
foreach (var item in getProperties(type, flags))
{
columns.Add(item.Name);
} if (null != ignore)
{
foreach (var item in ignore)
{
columns.Remove(item);
}
}
if (null != ext)
{
foreach (var item in ext)
{
columns.Add(item);
}
} StringBuilder sql = new StringBuilder(); if ( != columns.Count)
{
sql.AppendFormat(@"INSERT INTO {0} (", type.Name); StringBuilder clms = new StringBuilder();
StringBuilder vals = new StringBuilder();
foreach (var item in columns)
{
clms.AppendFormat("{0},", item);
vals.AppendFormat("@{0},", item);
}
clms.Replace(",", ") VALUES (", clms.Length - , );
vals.Replace(",", ")", vals.Length - , ); sql.Append(clms).Append(vals);
} return sql.ToString();
} public struct ColumnInfo
{
public int cid;
public string name;
public string type;
public bool notnull;
public object dflt_value;
public bool pk;
}
public static List<ColumnInfo> GetColumnInfoFromTable(string conString, string tableName)
{
List<ColumnInfo> result = null;
SQLiteConnection con = new SQLiteConnection(conString);
try
{
SQLiteCommand cmd = new SQLiteCommand(con);
cmd.CommandText = string.Format(@"pragma table_info({0})", tableName); con.Open(); using (var reader = cmd.ExecuteReader())
{
result = new List<ColumnInfo>();
while (reader.Read())
{
result.Add(new ColumnInfo()
{
cid = Convert.ToInt32(reader["cid"]),
name = reader["name"] as string,
notnull = Convert.ToBoolean(reader["notnull"]),
type = reader["type"] as string,
dflt_value = reader["dflt_value"],
pk = Convert.ToBoolean(reader["pk"]),
});
}
}
}
catch (Exception e)
{
//throw;
}
con.Close(); return result;
}
public static List<T> GetDataFromTable<T>(string conString,string selectSql)
where T:new ()
{
List<T> result = null;
SQLiteConnection con = new SQLiteConnection(conString);
try
{
SQLiteDataAdapter sda = new SQLiteDataAdapter(selectSql, con);
DataTable dt = new DataTable();
con.Open();
var i = sda.Fill(dt);
List<PropertyInfo> validProperty = new List<PropertyInfo>();
foreach (var item in dt.Columns)
{
var p = typeof(T).GetProperty(item.ToString());
if (null != p)
{
var x =p.Attributes;
var y = p.GetCustomAttributes(false);
var z = p.GetCustomAttributesData();
validProperty.Add(p);
}
}
result = new List<T>();
foreach (DataRow row in dt.Rows)
{
T t = new T();
foreach (var vp in validProperty)
{
vp.SetValue(t, row[vp.Name], null);
}
result.Add(t);
} dt.Clear();
sda.Dispose();
}
catch (Exception e)
{
}
con.Close();
return result;
}
}
}