数据库访问通用类(Access)
连接字符串
<connectionStrings> <add name="DBaccess" providerName="System.Data.OleDb" connectionString="Provider=Microsoft.ACE.OLEDB.12.0; Data Source=E:\\DBaccess.accdb" /> </connectionStrings>
代码如下:
using System; using System.Collections.Generic; using System.Configuration; using System.Data; using System.Data.OleDb; using System.Linq; using System.Text; using System.Threading.Tasks; namespace DBAccessDemo { internal class DBHelper { //<connectionStrings> //<add name = "connStr" connectionString="server=.;database=TestBase;uid=lyc;pwd=123456" providerName="System.Data.SqlClient"/> //</connectionStrings> private static readonly string connStr = ConfigurationManager.ConnectionStrings["DBaccess"].ConnectionString; //1、执行增删改查任务,Insert、Delete、Update //2、执行返回一个结果的查询 //3、执行查询,返回sqldatareader //4、执行查询,返回dataset //5、执行查询,返回datatable /// <summary> /// 执行T-SQL ,返回受影响的行数 Insert Update Delete /// </summary> /// <param name="sql">slq语句</param> /// <param name="cmdType">sql命令类型,1=sql语句,2=存储过程</param> /// <param name="paras">sql参数</param> /// <returns></returns> public static int ExecuteNonQuery(string sql, int cmdType, params OleDbParameter[] paras) { int count = 0; using (OleDbConnection conn = new OleDbConnection(connStr)) { OleDbCommand cmd = new OleDbCommand(sql, conn); if (cmdType == 2) cmd.CommandType = CommandType.StoredProcedure; if (paras != null && paras.Length > 0) cmd.Parameters.AddRange(paras); conn.Open(); count = cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); conn.Close(); } return count; } /// <summary> /// 执行查询,返回结果集第一行第一列的值,忽略其他行或列 object /// </summary> /// <param name="sql"></param> /// <param name="cmdType"></param> /// <param name="paras"></param> /// <returns></returns> public static object ExecuteScalar(string sql, int cmdType, params OleDbParameter[] paras) { object result = null; using (OleDbConnection conn = new OleDbConnection(connStr)) { OleDbCommand cmd = new OleDbCommand(sql, conn); if (cmdType == 2) cmd.CommandType = CommandType.StoredProcedure; if (paras != null && paras.Length > 0) cmd.Parameters.AddRange(paras); conn.Open(); result = cmd.ExecuteScalar(); cmd.Parameters.Clear(); conn.Close(); } return result; } /// <summary> /// 执行查询,生成SqlDataReader /// </summary> /// <param name="sql"></param> /// <param name="cmdType"></param> /// <param name="paras"></param> /// <returns></returns> /// <exception cref="Exception"></exception> public static OleDbDataReader ExecuteReader(string sql, int cmdType, params OleDbParameter[] paras) { OleDbDataReader result = null; OleDbConnection conn = new OleDbConnection(connStr); OleDbCommand cmd = new OleDbCommand(sql, conn); if (cmdType == 2) cmd.CommandType = CommandType.StoredProcedure; if (paras != null && paras.Length > 0) cmd.Parameters.AddRange(paras); try { conn.Open(); result = cmd.ExecuteReader(CommandBehavior.CloseConnection); cmd.Parameters.Clear(); } catch (OleDbException ex) { conn.Close(); throw new Exception("查询异常", ex); } return result; } /// <summary> /// 填充DataSet 一个或多个结果集 /// </summary> /// <param name="sql"></param> /// <param name="cmdType"></param> /// <param name="paras"></param> /// <returns></returns> public static DataSet GetDataSet(string sql, int cmdType, params OleDbParameter[] paras) { DataSet dt = null; using (OleDbConnection conn = new OleDbConnection(connStr)) { OleDbCommand cmd = new OleDbCommand(sql, conn); if (cmdType == 2) cmd.CommandType = CommandType.StoredProcedure; if (paras != null && paras.Length > 0) cmd.Parameters.AddRange(paras); OleDbDataAdapter adapter = new OleDbDataAdapter(cmd); conn.Open(); adapter.Fill(dt); cmd.Parameters.Clear(); conn.Close(); } return dt; } public static DataTable GetDataTable(string sql, int cmdType, params OleDbParameter[] paras) { DataTable dt = new DataTable(); using (OleDbConnection conn = new OleDbConnection(connStr)) { OleDbCommand cmd = new OleDbCommand(sql, conn); if (cmdType == 2) cmd.CommandType = CommandType.StoredProcedure; if (paras != null && paras.Length > 0) cmd.Parameters.AddRange(paras); conn.Open(); OleDbDataAdapter da = new OleDbDataAdapter(cmd); da.Fill(dt); cmd.Parameters.Clear(); conn.Close(); } return dt; } } }
使用方法:
//查询
private void button1_Click(object sender, EventArgs e) { string sql = "select * from students"; DataTable dt = DBHelper.GetDataTable(sql, 1); this.dataGridView1.DataSource = dt; } //插入数据 private void btnAdd_Click(object sender, EventArgs e) { string sql = "Insert into students (StuName,StuAge,StuId) values (@StuName,@StuAge,@StuId)"; OleDbParameter[] paras = { new OleDbParameter ("@StuName","hanzq"), new OleDbParameter ("@StuAge",36), new OleDbParameter ("@StuId","1003" ) }; DBHelper.ExecuteNonQuery(sql, 1, paras); } //删除数据 private void btnDelete_Click(object sender, EventArgs e) { string sql = "Delete from students where StuId=@StuId"; OleDbParameter[] paras = { new OleDbParameter ("@StuId","1003" ) }; DBHelper.ExecuteNonQuery (sql, 1, paras); } }