C#对SQLite、Access数据库操作的封装，很好用的~
1、对SQLite的封装：
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.SQLite; namespace DataBaseHelper { public class SQLiteHelper { SQLiteConnection connection = null; SQLiteTransaction transaction = null; string conn_str = ""; //----创建连接串并连接数据库---- public SQLiteHelper(string path, string password) { conn_str = "data source=" + path + ";password=" + password; } public bool Connect() { try { if (connection != null) { connection.Close(); connection = null; } connection = new SQLiteConnection(conn_str); connection.Open(); if (connection == null) { return false; } return true; } catch (SQLiteException ex) { return false; } } //----修改数据库密码---- public bool ChangePassword(string newPassword) { try { connection.ChangePassword(newPassword); return true; } catch (SQLiteException ex) { return false; } } //----关闭数据库连接---- public bool DisConnect() { try { if (connection != null) { connection.Close(); connection = null; } return true; } catch (SQLiteException ex) { return false; } } /// <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) { return null; } } /// <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; } return affectRows; } //收缩数据库 VACUUM public bool Vacuum() { try { using (SQLiteCommand Command = new SQLiteCommand("VACUUM", connection)) { Command.ExecuteNonQuery(); } return true; } catch (System.Data.SQLite.SQLiteException ex) { return false; } } public void BeginTransaction() { try { transaction = connection.BeginTransaction(); } catch (SQLiteException ex) { } } public void CommitTransaction() { try { transaction.Commit(); } catch (SQLiteException ex) { } } public void RollbackTransaction() { try { transaction.Rollback(); } catch (SQLiteException ex) { } } public void test() { SQLiteHelper helper = new SQLiteHelper("D:\\mysqlite.db","123456"); //连接到D盘下的mysqlite.db数据库,连接密码为123456 //bool ch = helper.ChangePassword("654321"); //将密码修改为：654321 helper.Connect(); string select_sql = "select * from student"; //查询的SQL语句 DataTable dt = helper.ExecuteDataTable(select_sql, null); //执行查询操作,结果存放在dt中 //向数据库中student表中插入了一条(name = "马兆瑞"，sex = "男"，telephone = "15550000000")的记录 string insert_sql = "insert into student(name,sex,telephone) values(?,?,?)"; //插入的SQL语句(带参数) SQLiteParameter[] para = new SQLiteParameter[3]; //构造并绑定参数 string[] tag = { "name", "sex", "telephone" }; string[] value = { "马兆瑞","男","15550000000"}; 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 { OleDbConnection connection = null; OleDbTransaction transaction = null; string conn_str = ""; //----创建连接串并连接数据库---- public AccessHelper(string path, string password) { conn_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Jet OLEDB:Database Password= " + password; } public bool Connect() { try { if (connection != null) { connection.Close(); connection = null; } connection = new OleDbConnection(conn_str); connection.Open(); if (connection == null) { return false; } return true; } catch (OleDbException ex) { return false; } } ////----修改数据库密码---- //public bool ChangePassword(string newPassword) //{ // try // { // connection.ChangePassword(newPassword); // return true; // } // catch (OleDbException ex) // { // return false; // } //} //----关闭数据库连接---- public bool DisConnect() { try { if (connection != null) { connection.Close(); connection = null; } return true; } catch (OleDbException ex) { return false; } } /// <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) { return null; } } /// <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; } return affectRows; } //收缩数据库 VACUUM public bool Vacuum() { try { using (OleDbCommand Command = new OleDbCommand("VACUUM", connection)) { Command.ExecuteNonQuery(); } return true; } catch (OleDbException ex) { return false; } } public void BeginTransaction() { try { transaction = connection.BeginTransaction(); } catch (OleDbException ex) { } } public void CommitTransaction() { try { transaction.Commit(); } catch (OleDbException ex) { } } public void RollbackTransaction() { try { transaction.Rollback(); } catch (OleDbException ex) { } } } }
调用示例：
AccessHelper helper = new AccessHelper("D:\\myaccess.mdb","123456789"); //连接到D盘下的myaccess.mdb数据库,密码为123456789 helper.Connect();
string select_sql = "select * from student"; //查询的SQL语句 DataTable dt = helper.ExecuteDataTable(select_sql, null); //执行查询操作,结果存放在dt中 //向数据库中student表中插入了一条(name = "马兆瑞"，sex = "男"，telephone = "15550000000")的记录 string insert_sql = "insert into student(name,sex,telephone) values(?,?,?)"; //插入的SQL语句(带参数) OleDbParameter[] para = new OleDbParameter[3]; //构造并绑定参数 string[] tag = { "name", "sex", "telephone" }; string[] value = { "马兆瑞","男","15550000000"}; for (int i = 0; i < 3; i++) { para[i] = new OleDbParameter(tag[i], value[i]); } int ret = helper.ExecuteNonQuery(insert_sql, para); //执行插入操作
本人是IT菜鸟，代码有很多不足之处，望大家多多指教
