C#对SQLite、Access数据库操作的封装，很好用的~
2024-08-24 04:57:47
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 = ; 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 = -;
}
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",""); //连接到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[]; //构造并绑定参数
string[] tag = { "name", "sex", "telephone" };
string[] value = { "马兆瑞","男",""};
for (int i = ; i < ; 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 = ; 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 = -;
}
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",""); //连接到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[]; //构造并绑定参数
string[] tag = { "name", "sex", "telephone" };
string[] value = { "马兆瑞","男",""};
for (int i = ; i < ; i++)
{
para[i] = new OleDbParameter(tag[i], value[i]);
}
int ret = helper.ExecuteNonQuery(insert_sql, para); //执行插入操作
本人是IT菜鸟，代码有很多不足之处，望大家多多指教
最新文章
- Atitit 数据存储的数据表连接attilax总结
- 【Python】django表单与提交
- SQL Server备份事务日志结尾(Tail)
- 【转载】Kafka High Availability
- java.lang.IllegalStateException: Required view 'text1' with ID 2131492943 for field 'mText' was not found. If this view is optional add '@Nullable' annotation
- 函数递归时，递归次数到900多时，就是抛出异常exception RuntimeError('maximum recursion depth exceeded',)
- .NET(C#)：分析IL中的if-else，while和for语句并用Emit实现
- 【SICP读书笔记（五）】练习2.32 --- 递归求集合子集
- 【ZJOI2008】树的统计（树链剖分）
- 《Language Implementation Patterns》之 解释器
- 预处理指令--C语言
- An internal error occurred during: Initializing Java Tooling.
- python - 中文编码/ASCII
- HDU 1541 STAR(树状数组）
- jQuery页面替换+php代码实现搜索后分页
- 项目中使用同一dll的不同版本
- NOIp2017D2T2(luogu3959) 宝藏 (状压dp)
- mongodb批量操作， bulk_write，
- Linux基础命令---find
- linux下安装/升级openssl