using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
namespace MyProjectDAL
{
public class AccessDB
{
private OleDbConnection connection;
private string connectionString;
//构造函数
public AccessDB()
{
string path = Application.ExecutablePath;
FileInfo exeInfo = new FileInfo(path);
path = exeInfo.DirectoryName + "\\";
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + "user data\\database\\MyProject.mdb";
connection = new OleDbConnection(connectionString);
}
//打开连接
private bool OpenConnection()
{
try
{
connection.Open();
return true;
}
catch (OleDbException ex)
{
MessageBox.Show("数据库连接失败 ErrorCode: " + (ex.ErrorCode).ToString());
return false;
}
}
//关闭连接
private bool CloseConnection()
{
try
{
connection.Close();
return true;
}
catch (OleDbException ex)
{
MessageBox.Show("关闭数据库失败 ErrorCode: " + (ex.ErrorCode).ToString());
return false;
}
}
//执行插入语句
public bool Insert(String strInsert)
{
string query = strInsert;
try
{
if (this.OpenConnection() == true)
{
OleDbCommand cmd = new OleDbCommand(query, connection);
cmd.ExecuteNonQuery();
this.CloseConnection();
return true;
}
else
{
return false;
}
}
catch (OleDbException ex)
{
MessageBox.Show("数据库操作失败 ErrorCode: " + (ex.ErrorCode).ToString());
return false;
}
}
//执行更新语句
public bool Update(String strUpdate)
{
string query = strUpdate;
try
{
if (this.OpenConnection() == true)
{
OleDbCommand cmd = new OleDbCommand();
cmd.CommandText = query;
cmd.Connection = connection;
cmd.ExecuteNonQuery();
this.CloseConnection();
return true;
}
else
{
return false;
}
}
catch (OleDbException ex)
{
MessageBox.Show("数据库更新失败 ErrorCode: " + (ex.ErrorCode).ToString());
return false;
}
}
//执行删除语句
public bool Delete(String sql)
{
string query = sql;
try
{
if (this.OpenConnection() == true)
{
OleDbCommand cmd = new OleDbCommand(query, connection);
cmd.ExecuteNonQuery();
this.CloseConnection();
return true;
}
else
{
return false;
}
}
catch (OleDbException ex)
{
MessageBox.Show("数据库操作失败 ErrorCode: " + (ex.ErrorCode).ToString());
return false;
}
}
//执行查询语句
public DataTable Query(string strQuery)
{
string query = strQuery;
DataTable dt = new DataTable();
try{
if (this.OpenConnection() == true)
{
OleDbDataAdapter da = new OleDbDataAdapter(query, connection);
da.Fill(dt);
}
this.CloseConnection();
}
catch (OleDbException ex)
{
MessageBox.Show("数据库操作失败 ErrorCode: " + (ex.ErrorCode).ToString());
}
return dt;
}
//执行查询计数语句
public int Count(string strCount)
{
string query = strCount;
int Count = -1;
try
{
if (this.OpenConnection() == true)
{
OleDbCommand cmd = new OleDbCommand(query, connection);
Count = int.Parse(cmd.ExecuteScalar().ToString());
this.CloseConnection();
}
}
catch (OleDbException ex)
{
MessageBox.Show("数据库操作失败 ErrorCode: " + (ex.ErrorCode).ToString());
}
return Count;
}
//执行求和语句
public double Sum(string strSum)
{
string query = strSum;
double Sum = 0.0;
try
{
if (this.OpenConnection() == true)
{
OleDbCommand cmd = new OleDbCommand(query, connection);
Sum = double.Parse(cmd.ExecuteScalar().ToString());
this.CloseConnection();
}
}
catch (OleDbException ex)
{
MessageBox.Show("数据库操作失败 ErrorCode: " + (ex.ErrorCode).ToString());
}
return Sum;
}
//transaction process
public bool RunTransaction(string[] strQuerys)
{
if (strQuerys.Length > 0)
{
try
{
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = this.connection;
OleDbTransaction transaction = null;
if (this.OpenConnection() == true)
{
transaction = connection.BeginTransaction();
cmd.Transaction = transaction;
for (int i = 0; i < strQuerys.Length; i++)
{
cmd.CommandText = strQuerys[i];
cmd.ExecuteNonQuery();
}
transaction.Commit();
this.CloseConnection();
return true;
}
else
{
return false;
}
}
catch (OleDbException ex)
{
MessageBox.Show("数据库操作失败 ErrorCode: " + (ex.ErrorCode).ToString());
return false;
}
}
else
{
return false;
}
}
///
}
}