--------------------------------------------------------------------------
//
// 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 =
0,
公共字段 =
1,
非公字段 =
2,
公共属性 =
4,
非公属性 =
8,
属性可读 =
16,
属性可写 =
32,
Default =
1 +
4 +
16
}
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 !=
0 || (
null != ext && ext.Count !=
0))
{
//删除忽略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 -
1,
1);
}
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 (
0 !=
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 -
1,
1);
vals.Replace(",",
")", vals.Length -
1,
1);
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;
}
}
}
orm4sqlite
标签：where sel name 数据类型 pen getc read uos connect
