访问SQLite数据库，SQLiteHelper类

最近做项目，在网上查资料，写了一个访问sqlite数据库的帮助类，这里记录一下，方便以后查询。主要用到的就是这四个方法

//创建链接string
private static string CreateConnectionString()
{
     //数据库的名称
     string dbName = "demo.db";
     //数据库的地址
     string sqlLitePath = "data source=" + AppDomain.CurrentDomain.BaseDirectory + "\\" + dbName;
     return sqlLitePath;
}
/// <summary>
        /// 执行一条SQL语句
        /// </summary>
        /// <param name="sqlString">SQL语句</param>
        /// <returns>返回影响的记录数</returns>
        public static int ExecuteSql(string sqlString)
        {
            using (SQLiteConnection connection = new SQLiteConnection(connectionString))
            {
                using (SQLiteCommand cmd = new SQLiteCommand(sqlString, connection))
                {
                    try
                    {
                        connection.Open();
                        //执行UPDATE、INSERT INTO和DELETE，没有返回数值的存储过程、CREATE TABLE和CREATEINDEX之类的DDL语句
                        int rows = cmd.ExecuteNonQuery();
                        return rows;
                    }
                    catch (SQLiteException ex)
                    {
                        connection.Close();
                        throw new Exception(ex.Message);
                    }
                }
            }
        }

        /// <summary>
        /// 执行多条SQL语句
        /// </summary>
        /// <param name="sqlStringList">SQL语句</param>
        public static void ExecuteSqlTran(List<string> sqlStringList)
        {
            using (SQLiteConnection conn = new SQLiteConnection(connectionString))
            {
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.Connection = conn;
                conn.Open();
                SQLiteTransaction tran = conn.BeginTransaction();
                cmd.Transaction = tran;
                try
                {
                    for (int i = 0; i < sqlStringList.Count; i++)
                    {
                        string strsql = sqlStringList[i].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tran.Commit();
                }
                catch (SQLiteException ex)
                {
                    conn.Close();
                    throw new Exception(ex.Message);
                }
            }
        } 

        /// <summary>
        /// 执行查询语句，返回DataSet
        /// </summary>
        /// <param name="sqlString">查询SQL语句</param>
        /// <returns>返回DataSet</returns>
        public static DataSet Query(string sqlString)
        {
            using (SQLiteConnection conn = new SQLiteConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    conn.Open();
                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(sqlString, conn);
                    adapter.Fill(ds);
                }
                catch (SQLiteException ex)
                {
                    conn.Close();
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }  

下边是获取到的数据转换为想要的格式

        /// <summary>
        /// DataSet转换实体类
        /// </summary>
        /// <typeparam name="T">实体类</typeparam>
        /// <param name="ds">DataSet数据集</param>
        /// <returns>实体类</returns>
        public static List<T> FillModel<T>(DataSet ds)
        {
            List<T> list = new List<T>();
            T model = default(T);
            if (ds.Tables[0].Columns[0].ColumnName == "id")
            {
                ds.Tables[0].Columns.Remove("id");
            }
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                model = Activator.CreateInstance<T>();
                foreach (DataColumn dc in dr.Table.Columns)
                {
                    PropertyInfo pi = model.GetType().GetProperty(dc.ColumnName);
                    if (dr[dc.ColumnName] != DBNull.Value)
                    {
                        pi.SetValue(model, dr[dc.ColumnName]);
                    }
                    else
                    {
                        pi.SetValue(model, null);
                    }
                }
                list.Add(model);
            }
            return list;
        }

        /// <summary>
        /// DataSet转换数组
        /// </summary>
        /// <param name="ds">DataSet数据集</param>
        /// <returns>数组</returns>
        public static List<object> FillList(DataSet ds)
        {
            List<object> list = new List<object>();
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                foreach (DataColumn dc in dr.Table.Columns)
                {
                    if (dr[dc.ColumnName] != DBNull.Value)
                    {
                        list.Add(dr[dc.ColumnName]);
                    }
                }
            }
            return list;
        }