C#对SQLite、Access数据库操作的封装，很好用的~

浏览数：196 / 时间：2015年06月12日

1、对SQLite的封装：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;

namespace DataBaseHelper
{
    class SQLiteHelper
    {
        private SQLiteConnection connection = null;

        //----创建连接串并连接数据库----
        public SQLiteHelper(string path, string password)
        {
            string conn_str = "data source=" + path + ";password=" + password;
            connection = new SQLiteConnection(conn_str); 
            connection.Open();
        }

        //----修改数据库密码----
        public bool ChangePassword(string newPassword)
        {
            bool ret = false;
            try
            {
                connection.ChangePassword(newPassword);
                ret = true;
            }
            catch (SQLiteException ex)
            {
                //log.Error("ChangeDBPwd occurs exceptions：" + ex.Message);
            }
            return ret;
        }

        //----关闭数据库连接----
        public void CloseConnection()
        {
            connection.Close();
            connection = null;
        }

        /// <summary> 
        /// 执行一个查询语句，返回一个包含查询结果的DataTable 
        /// </summary> 
        /// <param name="sql">要执行的查询语句</param> 
        /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param> 
        /// <returns></returns> 
        public DataTable ExecuteDataTable(string sql, SQLiteParameter[] parameters)
        {
            try
            {
                using (SQLiteCommand Command = new SQLiteCommand(sql, connection))
                {
                    if (parameters != null)
                    {
                        Command.Parameters.AddRange(parameters);
                    }
                    SQLiteDataAdapter adapter = new SQLiteDataAdapter(Command);
                    DataTable dataTable = new DataTable();
                    adapter.Fill(dataTable);
                    return dataTable;
                }
            }
            catch (SQLiteException ex)
            {
                System.Exception exc = ex;
                throw (exc);
            }
        }

        /// <summary> 
        /// 对SQLite数据库执行增删改操作，返回受影响的行数。 
        /// </summary> 
        /// <param name="sql">要执行的增删改的SQL语句</param> 
        /// <param name="parameters">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param> 
        /// <returns></returns> 
        public int ExecuteNonQuery(string sql, SQLiteParameter[] parameters)
        {
            int affectRows = 0;

            try
            {
                using (SQLiteTransaction Transaction = connection.BeginTransaction())
                {
                    using (SQLiteCommand Command = new SQLiteCommand(sql, connection, Transaction))
                    {
                        if (parameters != null)
                        {
                            Command.Parameters.AddRange(parameters);
                        }
                        affectRows = Command.ExecuteNonQuery();
                    }
                    Transaction.Commit();
                }
            }
            catch (SQLiteException ex)
            {
                affectRows = -1;
                //log.Error("ExecuteNonQuery occurs exception：" + ex.Message);
            }
            return affectRows;
        }
    }
}

调用示例：

SQLiteHelper helper = new SQLiteHelper("D:\\mysqlite.db","123456");     //连接到D盘下的mysqlite.db数据库,连接密码为123456
//bool ch = helper.ChangePassword("654321");                            //将密码修改为：654321
            
string select_sql = "select * from student";                            //查询的SQL语句
DataTable dt = helper.ExecuteDataTable(select_sql, null);               //执行查询操作,结果存放在dt中

//向数据库中student表中插入了一条(name = "马兆瑞"，sex = "男"，telephone = "15550008990")的记录
string insert_sql = "insert into student(name,sex,telephone) values(?,?,?)";        //插入的SQL语句(带参数)
SQLiteParameter[] para = new SQLiteParameter[3];                        //构造并绑定参数
string[] tag = { "name", "sex", "telephone" };
string[] value = { "马兆瑞","男","15550008990"};
for (int i = 0; i < 3; i++)
{
      para[i] = new SQLiteParameter(tag[i], value[i]);                    
}
int ret = helper.ExecuteNonQuery(insert_sql, para);                     //执行插入操作

2、对Access的封装：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace DataBaseHelper
{
    public class AccessHelper
    {
        private OleDbConnection connection = null;

        //----创建连接串并连接数据库----
        public AccessHelper(string path, string password)
        {
            string conn_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Jet OLEDB:Database Password= " + password;
            connection = new OleDbConnection(conn_str); 
            connection.Open();
        }

        //----关闭数据库连接----
        public void CloseConnection()
        {
            connection.Close();
            connection = null;
        }

        /// <summary> 
        /// 执行一个查询语句，返回一个包含查询结果的DataTable 
        /// </summary> 
        /// <param name="sql">要执行的查询语句</param> 
        /// <param name="parameters">执行SQL查询语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param> 
        /// <returns></returns> 
        public DataTable ExecuteDataTable(string sql, OleDbParameter[] parameters)
        {
            try
            {
                using (OleDbCommand Command = new OleDbCommand(sql, connection))
                {
                    if (parameters != null)
                    {
                        Command.Parameters.AddRange(parameters);
                    }
                    OleDbDataAdapter adapter = new OleDbDataAdapter(Command);
                    DataTable dataTable = new DataTable();
                    adapter.Fill(dataTable);
                    return dataTable;
                }
            }
            catch (OleDbException ex)
            {
                System.Exception exc = ex;
                throw (exc);
            }
        }

        /// <summary> 
        /// 对Access数据库执行增删改操作，返回受影响的行数。 
        /// </summary> 
        /// <param name="sql">要执行的增删改的SQL语句</param> 
        /// <param name="parameters">执行增删改语句所需要的参数，参数必须以它们在SQL语句中的顺序为准</param> 
        /// <returns></returns> 
        public int ExecuteNonQuery(string sql, OleDbParameter[] parameters)
        {
            int affectRows = 0;

            try
            {
                using (OleDbTransaction Transaction = connection.BeginTransaction())
                {
                    using (OleDbCommand Command = new OleDbCommand(sql, connection, Transaction))
                    {
                        if (parameters != null)
                        {
                            Command.Parameters.AddRange(parameters);
                        }
                        affectRows = Command.ExecuteNonQuery();
                    }
                    Transaction.Commit();
                }
            }
            catch (OleDbException ex)
            {
                affectRows = -1;
                //log.Error("ExecuteNonQuery occurs exception：" + ex.Message);
            }
            return affectRows;
        }
    }
}

调用示例：

AccessHelper helper = new AccessHelper("D:\\myaccess.mdb","123456789");     //连接到D盘下的myaccess.mdb数据库,密码为123456789
            
string select_sql = "select * from student";                            //查询的SQL语句
DataTable dt = helper.ExecuteDataTable(select_sql, null);               //执行查询操作,结果存放在dt中

//向数据库中student表中插入了一条(name = "马兆瑞"，sex = "男"，telephone = "15550008990")的记录
string insert_sql = "insert into student(name,sex,telephone) values(?,?,?)";        //插入的SQL语句(带参数)
OleDbParameter[] para = new OleDbParameter[3];                        //构造并绑定参数
string[] tag = { "name", "sex", "telephone" };
string[] value = { "马兆瑞","男","15550008990"};
for (int i = 0; i < 3; i++)
{
      para[i] = new OleDbParameter(tag[i], value[i]);                    
}
int ret = helper.ExecuteNonQuery(insert_sql, para);                     //执行插入操作