SQLite数据库模块化查询

以一个简单的登录为案例：通过姓名跟密码模拟查询游戏者的游戏ID账号

这里我一共分为4个模块来建立对应的查询结构：分别定义用户信息函数、用户管理函数、数据库查询函数、用户查询语句建立函数。 

1、SqlExeccute.cs ：这里主要实现数据库的连接建立跟查询函数

  /// <summary>
        /// 多条结果查询类方法
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public static SQLiteDataReader GetReader(string sql)
        {
            SQLiteConnection conn = new SQLiteConnection(ConnString);
            SQLiteCommand cmd = conn.CreateCommand();
            try
            {
                cmd.CommandText = sql;
                conn.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                conn.Close();
                throw new Exception(ex.Message);
            }

        }

 //生成连接字符串
        private static string CreateConnectionString()
        {
            SQLiteConnectionStringBuilder connectionString = new SQLiteConnectionStringBuilder
            {
                DataSource = @"Login.db"
            };

            string conStr = connectionString.ToString();
            return conStr;
        }

    (2)带参数的查询，返回多条查询结果

 /// <summary>
        /// 多条结果查询类方法
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="param">SQL参数</param>
        /// <returns></returns>
        public static SQLiteDataReader getReader(string sql, SQLiteParameter[] param)
        {
            SQLiteConnection conn = new SQLiteConnection(ConnString);
            SQLiteCommand cmd = conn.CreateCommand();
            try
            {
                cmd.CommandText = sql;
                conn.Open();
                cmd.Parameters.AddRange(param);
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                conn.Close();
                throw new Exception(ex.Message);
            }

        }

        (3)多参数的查询，返回查询结果

   /// <summary>
        /// 返回单条结果查询类方法
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="param">SQL参数</param>
        /// <returns></returns>
        public static int GetSingleResult(string sql, SQLiteParameter[] param)
        {
            SQLiteConnection conn = new SQLiteConnection(ConnString);
            SQLiteCommand cmd = conn.CreateCommand();
            try
            {
                cmd.CommandText = sql;
                conn.Open();
                cmd.Parameters.AddRange(param);
               // cmd.ExecuteNonQuery();
                if (Convert.ToInt32(cmd.ExecuteScalar())==0)
                        return -1;
                return 1;
            }
            catch (Exception ex)
            {

                throw new Exception(ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

       (4) 不带参数的查询方法

  /// <summary>
        /// 多条结果查询类方法
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns></returns>
        public static SQLiteDataReader GetReader(string sql)
        {
            SQLiteConnection conn = new SQLiteConnection(ConnString);
            SQLiteCommand cmd = conn.CreateCommand();
            try
            {
                cmd.CommandText = sql;
                conn.Open();
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                conn.Close();
                throw new Exception(ex.Message);
            }

        }

2、User.cs :用户的信息类

  [Serializable]
    public class User
    {
        public int Id { get; set; }
        public String username { get; set; }
        public String password { get; set; }

    }

3、UserManner.cs: 用户管理类方法

 //创建数据访问对象
        private UserService objUserService = new UserService();

        public User UserLogin(User objUser)
        {
            return objUserService.UserLogin(objUser);
        }

4、UserService.cs :用户执行类

        /// <summary>
        /// 根据账号和密码比对用户信息
        /// </summary>
        /// <param name="objUser">包含用户名和密码的用户对象</param>
        /// <returns>返回用户对象信息（若无用户信息则对象为null）</returns>
        public User UserLogin(User objUser)
        {
            String sql = "SELECT ID FROM USER WHERE USERNAME=@USERNAME and PASSWORD=@PASSWORD";
            SQLiteParameter[] param = new SQLiteParameter[] {
                new SQLiteParameter("@USERNAME",objUser.username),
                new SQLiteParameter("@PASSWORD", objUser.password)
            };
            SQLiteDataReader objReader = SqlExecute.getReader(sql, param);
            if (objReader.Read())
            {
                objUser.Id = Convert.ToInt32(objReader["ID"]);
            }
            else
            {
                objUser = null;
            }
            objReader.Close();
            return objUser;
        }

这样就实现了一个登陆类的编写，如果相应的增加类模块，SqlExeccute.cs 可以通用，不必去再建立查询什么的。具体的功能实现函数可以一层一层的封装起来，使代码看起来更加的简洁。