C# 代码积累 与 sqlite3数据文件 通信

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;
            }