SQLITE3 与sqlite3产生的数据库文件交互 C#代码 实现类和数据表的互相转换
首选需要数据库通讯函数
public class SQLiteDBHelper
{
private string connectionString = string.Empty;
/// <summary>
/// 构造函数
/// </summary>
/// <param name="dbPath">SQLite数据库文件路径</param>
public SQLiteDBHelper(string dbPath)
{
this.connectionString = "Data Source=" + dbPath;
}
public DataTable ExecuteDataTable(string sql, SQLiteParameter[] parameters)
{
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
{
using (SQLiteCommand command = new SQLiteCommand(sql, connection))
{
if (parameters != null)
{
command.Parameters.AddRange(parameters);
}
SQLiteDataAdapter adapter = new SQLiteDataAdapter(command);
DataTable data = new DataTable();
adapter.Fill(data);
return data;
}
}
}
}
读取
/// <summary>
/// 从db文件读取出类 前提是列名与类属性定义名一致
/// </summary>
/// <typeparam name="T"> 类的类型</typeparam>
/// <param name="path">db文件路径</param>
/// <param name="schema"> 类实例 可以是空的 </param>
/// <returns></returns>
public static List<T> ReadClassFormSQLite<T>(string path, T schema ) where T : new()
{
string tableName = schema.GetType().Name;
List<T> ts = new List<T>();
DataSet ds = new DataSet();
List<string> cloumnNames = schema.GetType().GetProperties().Select(t => t.Name).ToList();
SQLiteDBHelper sqliteDBHelper = new SQLiteDBHelper(path);
string sqlDeleteString = " SELECT * FROM " + tableName;
DataTable dtSQLite = sqliteDBHelper.ExecuteDataTable(sqlDeleteString, null);
foreach (DataRow row in dtSQLite.Rows)
{
T tt = new T();
foreach (string cloumnName in cloumnNames)
{
schema.GetType().GetProperty(cloumnName).SetValue(tt,row[cloumnName].ToString(), null);
}
ts.Add(tt);
}
return ts;
}
写入
/// <summary>
/// 写入数据文件 前提是列名与类属性定义名一致
/// </summary>
/// <typeparam name="T"> 类的类型</typeparam>
/// <param name="path">db文件路径</param>
/// <param name="schema"> 类实例 不能为空的 </param>
public static void WriteClassToSQLite<T>(string path, List<T> schemas) where T : new()
{
// List<string> cloumnNames = schemas[0].GetType().GetProperties().Select(t => t.Name).ToList();
try
{
string tableName = string.Empty;
if (schemas.Count != 0)
{
tableName = schemas[0].GetType().Name;
if (!File.Exists(path))
{
// File.Delete(path);
// MessageBox.Show("no file");
SQLiteDBHelper.CreateDB(path);
}
//判断表是否存在同名
string showTableNamesSqlString = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name ";
SQLiteDBHelperX sqliteDBHelperX = new SQLiteDBHelperX(path, tableName);
SQLiteDBHelper sqliteDBHelper = new SQLiteDBHelper(path);
DataTable tables = sqliteDBHelper.ExecuteDataTable(showTableNamesSqlString, null);
List<string> tableNames = new List<string>();
foreach (DataRow dataRow in tables.Rows)
{ tableNames.Add(dataRow["name"].ToString()); }
ListClassAndSQL listClassAndSQL = new ListClassAndSQL(tableName);
if (!tableNames.Any(t => t == tableName))
{
//需要创建表
string sqlCreateString = listClassAndSQL.ListClassToSqlsCreateTable<T>(schemas);
sqliteDBHelper.ExecuteNonQuery(sqlCreateString, null);
}
//else
//{
// //表已经存在
//}
List<SqliteConnPack> sqliteConnPack
= listClassAndSQL.ListClassToSqliteConnPack<T>(schemas);
foreach (SqliteConnPack pack in sqliteConnPack)
{
// MessageBox.Show("向sqlite文件添加数据\n" + pack.sqlString);
sqliteDBHelper.ExecuteNonQuery(pack.sqlString, pack.sqliteParameter);
}
}
}
catch (Exception excc)
{
MessageBox.Show(excc.ToString()); ;
}
}
/// <summary>
/// 根据类列表创建数据库文件
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="list"></param>
/// <returns></returns>
public string ListClassToSqlsCreateTable<T>(List<T> list)
{
try
{
string sql = null;
//sql = "CREATE TABLE " + typeof(T).DeclaringType.Name + " (id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,";
sql = "CREATE TABLE " + tableName + " ( ";
// MessageBox.Show(list.Count.ToString());
List<PropertyInfo> props = list[0].GetType().GetProperties().Select(t => t).ToList();
string strr = null;
foreach (PropertyInfo prop in props)
{
if (prop.Name == "id")
{
strr += "id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,";
}
else
{
string type = "varchar(255)";
strr = strr + prop.Name + " " + type + ",";
}
//switch (prop.GetType().Name)
//{
// case "double":
// type = "DOUBLE";
// break;
// case "string":
// type = "VARCHAR(255)";
// break;
//}
}
strr = strr.Substring(0, strr.Length - 1);
sql = sql + strr + ")";
return sql;
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
return null;
throw;
}
return null;
}