C# 连接sqlite数据库以及操作方法的类

1.所要用到的dll:http://download.csdn.net/detail/pp880509/6930161


2.【连接sqlite的类】

public class SQLiteHelper

    {
        //代码导读:|DataDirectory|

        //对于用户计算机上的应用程序而言,|DataDirectory|变量代表的路径就是应用程序的.exe文件所在位置的路径。

        //对于使用ClickOnce所部署的应用程序而言,|DataDirectory|变量所代表的路径则是ClickOnce所建立的特殊文件目录。

        //对于Web应用程序而言,|DataDirectory|变量所代表的路径就是App_Date文件夹。

        //也可以根据自己的需要设置|DataDirectory|的默认值,代 码:AppDomain.CurrentDomain.SetData("DataDirectory",newpath)

        //public static readonly string SQLiteConnectionString = "Data Source=" + System.Windows.Forms.Application.StartupPath + "\\TaiSol.zhushipeng";
        public static readonly string SQLiteConnectionString = "Data Source=|DataDirectory|\\weixin.db";
        //public static readonly string SQLiteConnectionString = "Data Source=F:\\SourceCode\\CWVSweixin\\weixin.db";
        //轻量级数据库SQLite的连接字符串写法:"Data Source=D:\database\test.s3db"
        //轻量级数据库SQLite的加密后的连接字符串写法:"Data Source=D:"database\test.s3db;Version=3;Password=SQLite数据库密码;"
        public SQLiteHelper()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }
        / <summary>
        / 执行多条SQL语句,实现数据库事务。
        / </summary>
        / <param name="SQLStringList">多条SQL语句</param>
        //public static void ExecuteSqlTran(ArrayList SQLStringList)
        //{
        //    using (SQLiteConnection conn = new SQLiteConnection(connectionString))
        //    {
        //        conn.Open();
        //        SQLiteCommand cmd = new SQLiteCommand();
        //        cmd.Connection = conn;
        //        SQLiteTransaction tx = conn.BeginTransaction();
        //        cmd.Transaction = tx;
        //        try
        //        {
        //            for (int n = 0; n < SQLStringList.Count; n++)
        //            {
        //                string strsql = SQLStringList[n].ToString();
        //                if (strsql.Trim().Length > 1)
        //                {
        //                    cmd.CommandText = strsql;
        //                    cmd.ExecuteNonQuery();
        //                }
        //            }
        //            tx.Commit();
        //        }
        //        catch (System.Data.SQLite.SQLiteException E)
        //        {
        //            tx.Rollback();
        //            throw new Exception(E.Message);
        //        }
        //    }
        //}

        #region ExecuteNonQuery

        /// <summary>
        /// 对连接执行Transact-SQL语句并返回受影响的行数
        /// </summary>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第一个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string commandText, bool isProcedure, params SQLiteParameter[] paras)
        {
            SQLiteConnection con = new SQLiteConnection(SQLiteHelper.SQLiteConnectionString);
            SQLiteCommand cmd = new SQLiteCommand(commandText, con);

            if (isProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                cmd.CommandType = CommandType.Text;
            }

            cmd.Parameters.Clear();
            if (paras != null)
            {
                foreach (SQLiteParameter para in paras)
                {
                    cmd.Parameters.Add(para);
                }
            }

            try
            {
                con.Open();
                return cmd.ExecuteNonQuery();
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 对连接执行Transact-SQL语句并返回受影响的行数
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string commandText, params SQLiteParameter[] paras)
        {
            return ExecuteNonQuery(commandText, false, paras);
        }

        /// <summary>
        /// 对连接执行Transact-SQL语句并返回受影响的行数
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第二个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(SQLiteTransaction trans, string commandText, bool isProcedure, params SQLiteParameter[] paras)
        {

            SQLiteConnection con = trans.Connection;
            SQLiteCommand cmd = new SQLiteCommand(commandText, con);

            if (isProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                cmd.CommandType = CommandType.Text;
            }

            cmd.Parameters.Clear();
            foreach (SQLiteParameter para in paras)
            {
                cmd.Parameters.Add(para);
            }

            if (trans != null)
            {
                cmd.Transaction = trans;
            }

            try
            {
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                return cmd.ExecuteNonQuery();
            }
            finally
            {
                if (trans == null)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 对连接执行Transact-SQL语句并返回受影响的行数
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(SQLiteTransaction trans, string commandText, params SQLiteParameter[] paras)
        {
            return ExecuteNonQuery(trans, commandText, false, paras);
        }

        #endregion

        #region ExecuteQueryScalar

        /// <summary>
        /// 执行查询,并返回查询结果集中的第一行第一列,忽略其它行或列
        /// </summary>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第一个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
        /// <returns></returns>
        public static object ExecuteQueryScalar(string commandText, bool isProcedure, params SQLiteParameter[] paras)
        {
            SQLiteConnection con = new SQLiteConnection(SQLiteHelper.SQLiteConnectionString);
            SQLiteCommand cmd = new SQLiteCommand(commandText, con);

            if (isProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                cmd.CommandType = CommandType.Text;
            }

            foreach (SQLiteParameter para in paras)
            {
                cmd.Parameters.Add(para);
            }

            try
            {
                con.Open();
                return cmd.ExecuteScalar();
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 执行查询,并返回查询结果集中的第一行第一列,忽略其它行或列
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
        /// <returns></returns>
        public static object ExecuteQueryScalar(string commandText, params SQLiteParameter[] paras)
        {
            return ExecuteQueryScalar(commandText, false, paras);
        }

        /// <summary>
        /// 执行查询,并返回查询结果集中的第一行第一列,忽略其它行或列
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第二个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
        /// <returns></returns>
        public static object ExecuteQueryScalar(SQLiteTransaction trans, string commandText, bool isProcedure, params SQLiteParameter[] paras)
        {
            SQLiteConnection con = trans.Connection;
            SQLiteCommand cmd = new SQLiteCommand(commandText, con);

            if (isProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                cmd.CommandType = CommandType.Text;
            }
            cmd.Parameters.Clear();
            foreach (SQLiteParameter para in paras)
            {
                cmd.Parameters.Add(para);
            }

            if (trans != null)
            {
                cmd.Transaction = trans;
            }

            try
            {
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                return cmd.ExecuteScalar();
            }
            finally
            {
                if (trans == null)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 执行查询,并返回查询结果集中的第一行第一列,忽略其它行或列
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
        /// <returns></returns>
        public static object ExecuteQueryScalar(SQLiteTransaction trans, string commandText, params SQLiteParameter[] paras)
        {
            return ExecuteQueryScalar(trans, commandText, false, paras);
        }

        #endregion

        #region ExecuteDataReader

        /// <summary>
        /// 执行SQL,并返回结果集的只前进数据读取器
        /// </summary>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第一个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
        /// <returns></returns>
        public static SQLiteDataReader ExecuteDataReader(string commandText, bool isProcedure, params SQLiteParameter[] paras)
        {
            SQLiteConnection con = new SQLiteConnection(SQLiteHelper.SQLiteConnectionString);
            SQLiteCommand cmd = new SQLiteCommand(commandText, con);

            if (isProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                cmd.CommandType = CommandType.Text;
            }

            if (paras != null)
            {
                foreach (SQLiteParameter para in paras)
                {
                    cmd.Parameters.Add(para);
                }
            }

            try
            {
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch
            {
                con.Close();
                throw;
            }

        }

        /// <summary>
        /// 执行SQL,并返回结果集的只前进数据读取器
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
        /// <returns></returns>
        public static SQLiteDataReader ExecuteDataReader(string commandText, params SQLiteParameter[] paras)
        {
            return ExecuteDataReader(commandText, false, paras);
        }

        /// <summary>
        /// 执行SQL,并返回结果集的只前进数据读取器
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第二个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
        /// <returns></returns>
        public static SQLiteDataReader ExecuteDataReader(SQLiteTransaction trans, string commandText, bool isProcedure, params SQLiteParameter[] paras)
        {
            SQLiteConnection con = trans.Connection;
            SQLiteCommand cmd = new SQLiteCommand(commandText, con);

            if (isProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                cmd.CommandType = CommandType.Text;
            }

            cmd.Parameters.Clear();
            foreach (SQLiteParameter para in paras)
            {
                cmd.Parameters.Add(para);
            }

            if (trans != null)
            {
                cmd.Transaction = trans;
            }

            try
            {
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch
            {
                if (trans == null)
                {
                    con.Close();
                }
                throw;
            }

        }

        /// <summary>
        /// 执行SQL,并返回结果集的只前进数据读取器
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
        /// <returns></returns>
        public static SQLiteDataReader ExecuteDataReader(SQLiteTransaction trans, string commandText, params SQLiteParameter[] paras)
        {
            return ExecuteDataReader(trans, commandText, false, paras);
        }

        #endregion

        #region ExecuteDataSet

        /// <summary>
        /// 执行SQL,并返回DataSet结果集
        /// </summary>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第一个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(string commandText, bool isProcedure, params SQLiteParameter[] paras)
        {
            SQLiteConnection con = new SQLiteConnection(SQLiteHelper.SQLiteConnectionString);
            SQLiteCommand cmd = new SQLiteCommand(commandText, con);

            if (isProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                cmd.CommandType = CommandType.Text;
            }

            if (paras != null)
            {
                foreach (SQLiteParameter para in paras)
                {
                    cmd.Parameters.Add(para);
                }
            }
            try
            {
                con.Open();
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 执行SQL,并返回DataSet结果集
        /// </summary>
        /// <param name="commandText">SQL语句</param>
        /// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(string commandText, params SQLiteParameter[] paras)
        {
            return ExecuteDataSet(commandText, false, paras);
        }

        /// <summary>
        /// 执行SQL,并返回DataSet结果集
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句或存储过程名</param>
        /// <param name="isProcedure">第二个参数是否为存储过程名,true为是,false为否</param>
        /// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(SQLiteTransaction trans, string commandText, bool isProcedure, params SQLiteParameter[] paras)
        {
            SQLiteConnection con = trans.Connection;
            SQLiteCommand cmd = new SQLiteCommand(commandText, con);

            if (isProcedure)
            {
                cmd.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                cmd.CommandType = CommandType.Text;
            }

            cmd.Parameters.Clear();
            foreach (SQLiteParameter para in paras)
            {
                cmd.Parameters.Add(para);
            }

            if (trans != null)
            {
                cmd.Transaction = trans;
            }

            try
            {
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
                DataSet ds = new DataSet();
                adapter.Fill(ds);
                return ds;
            }
            finally
            {
                if (trans == null)
                {
                    con.Close();
                }
            }
        }

        /// <summary>
        /// 执行SQL,并返回DataSet结果集
        /// </summary>
        /// <param name="trans">传递事务对象</param>
        /// <param name="commandText">SQL语句</param>
        /// <param name="paras">SQLiteParameter参数列表,0个或多个参数</param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(SQLiteTransaction trans, string commandText, params SQLiteParameter[] paras)
        {
            return ExecuteDataSet(trans, commandText, false, paras);
        }

        #endregion
    }
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值