public sealed class SqliteHelper
{
private readonly string _connstring = "Data Source={0};Initial Catalog=sqlite;Integrated Security=True;Max Pool Size=10";
public SqliteHelper(string dbPath)
{
this._connstring = string.Format(_connstring, dbPath);
}
public DataTable Select(string sql, DbParameter[] parameters = null)
{
using (var conn = new SQLiteConnection(this._connstring))
{
using (var cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = sql;
if (parameters != null)
{
cmd.Parameters.AddRange(parameters);
}
var adp = new SQLiteDataAdapter(cmd);
var dt = new DataTable();
adp.Fill(dt);
return dt;
}
}
}
/// <summary>
/// 查询数据库
/// </summary>
/// <returns></returns>
public DataTable ShowDataBase()
{
return Select("PRAGMA database_list");
}
/// <summary>
/// 查询所有表
/// </summary>
/// <returns></returns>
public DataTable GetTableList()
{
return Select("select * from sqlite_master where type='table'");
}
public DataTable GetColumn(string tableName)
{
return Select($"PRAGMA table_info(`{tableName}`);");
}
}
public sealed class DbTypeMap
{
private static Dictionary<string, Type> DbList = null;
private static void GetSQLiteDbTypeMap()
{
if (DbList != null) return;
DbList = new Dictionary<string, Type>
{
{"BIGINT", typeof(Int64)},
{"BIGUINT", typeof(UInt64)},
{"BINARY", typeof(byte)},
{"BIT", typeof(bool)},
{"BLOB", typeof(byte)},
{"BOOL", typeof(bool)},
{"BOOLEAN", typeof(bool)},
{"CHAR", typeof(string)},
{"CLOB", typeof(string)},
{"COUNTER", typeof(Int64)},
{"CURRENCY",typeof(decimal)},
{"DATE", typeof(DateTime)},
{"DATETIME", typeof(DateTime)},
{"DECIMAL", typeof(decimal)},
{"DOUBLE", typeof(double)},
{"FLOAT", typeof(double)},
{"GENERAL", typeof(Byte)},
{"GUID", typeof(string)},
{"IDENTITY",typeof(Int64)},
{"IMAGE", typeof(byte[])},
{"INT", typeof(Int64)},
{"INT8", typeof(byte)},
{"INT16", typeof(Int16)},
{"INT32", typeof(Int32)},
{"INT64", typeof(Int64)},
{"INTEGER", typeof(Int64)},
{"INTEGER8", typeof(byte)},
{"INTEGER16", typeof(Int16)},
{"INTEGER32", typeof(Int32)},
{"INTEGER64", typeof(Int64)},
{"LOGICAL", typeof(bool)},
{"LONG", typeof(Int64)},
{"LONGCHAR", typeof(String)},
{"LONGTEXT", typeof(String)},
{"LONGVARCHAR", typeof(String)},
{"MEMO", typeof(String)},
{"MONEY", typeof(Decimal)},
{"NCHAR", typeof(String)},
{"NOTE", typeof(String)},
{"NTEXT", typeof(String)},
{"NUMBER", typeof(Decimal)},
{"NUMERIC", typeof(Decimal)},
{"NVARCHAR", typeof(String)},
{"OLEOBJECT", typeof(byte[])},
{"RAW", typeof(byte[])},
{"REAL", typeof(double)},
{"SINGLE", typeof(Single)},
{"SMALLDATE", typeof(DateTime)},
{"SMALLINT", typeof(Int16)},
{"SMALLUINT", typeof(UInt16)},
{"STRING", typeof(String)},
{"TEXT", typeof(String)},
{"TIME", typeof(DateTime)},
{"TIMESTAMP", typeof(DateTime)},
{"TINYINT", typeof(Int16)},
{"TINYSINT", typeof(byte)},
{"UINT", typeof(Int64)},
{"UINT8", typeof(byte)},
{"UINT16", typeof(Int16)},
{"UINT32",typeof(UInt32)},
{"UINT64", typeof(UInt64)},
{"ULONG", typeof(UInt64)},
{"VARCHAR", typeof(string)},
{"VARCHAR2", typeof(string)},
{"YESNO", typeof(bool)}
};
}
public static string FindType(string typeName)
{
GetSQLiteDbTypeMap();
int index = typeName.IndexOf("(", StringComparison.InvariantCultureIgnoreCase);
if (index > 0)
{
typeName = typeName.Substring(0, index);
}
Type db = null;
if (DbList.TryGetValue(typeName, out db))
{
return db.Name;
}
return typeof(string).Name;
}
用法
DbTypeMap.FindType(c.Type);