using System;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
//AInte都要修改为你的类名,下面所有函数都要改一下
namespace AInte
{
/// <summary>
/// DbAccess类,即进行数据库访问时需要调用的类
/// </summary>
public class DbAccess:System.Web.UI.Page
{
/// <summary>
/// DbAccess构造函数
/// </summary>
public DbAccess()
{
}
/// <summary>
/// 无条件查询操作,即查询表中所有记录
/// </summary>
/// <param name="strTableName">表名</param>
/// <param name="strColumn">列名组</param>
/// <returns>无条件查询结果</returns>
public static void SelectAll(ref DataSet ds,string strTableName, string[] strColumn)
{
AInte.DbAccess db = new DbAccess();
string constring = db.getconn();
OleDbConnection conn = new OleDbConnection(constring);
conn.Open();
OleDbCommand command = new OleDbCommand();
OleDbTransaction concreteDbTrans = conn.BeginTransaction();
command.Connection = conn;
command.Transaction = concreteDbTrans;
OleDbDataAdapter ada = new OleDbDataAdapter();
try
{
string strSql = "SELECT ";
for(int i = 0; i < strColumn.Length - 1; i++)
{
strSql += (strColumn[i] + ", ");
}
strSql += (strColumn[strColumn.Length - 1] + " FROM " + strTableName);
command.CommandText = strSql;
ada.SelectCommand = command;
ada.Fill(ds);
concreteDbTrans.Commit();
}
catch
{
concreteDbTrans.Rollback();
ds.Clear();
throw;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 条件查询guojin
/// </summary>
/// <param name="ds"></param>
/// <param name="strSql">SQL语句</param>
public static void SelectBySql(ref DataSet ds,string strSql)
{
AInte.DbAccess db = new DbAccess();
string constring = db.getconn();
OleDbConnection conn = new OleDbConnection(constring);
conn.Open();
OleDbCommand command = new OleDbCommand();
OleDbTransaction concreteDbTrans = conn.BeginTransaction();
command.Connection = conn;
command.Transaction = concreteDbTrans;
OleDbDataAdapter ada = new OleDbDataAdapter();
command.CommandText = strSql;
ada.SelectCommand = command;
ada.Fill(ds);
concreteDbTrans.Commit();
}
/// <summary>
/// 条件查询操作
/// </summary>
/// <param name="strTableName">表名</param>
/// <param name="strColumn">列名组</param>
/// <param name="strCondition">条件</param>
/// <returns>条件查询结果</returns>
public static DataSet Select(string tableName, string[] columnNameArray, string conditionString)
{
DataSet dataSet= new DataSet();
AInte.DbAccess db = new DbAccess();
string connectionString=db.getconn();
OleDbConnection concreteConnection=new OleDbConnection(connectionString);
concreteConnection.Open();
OleDbCommand concreteCommand=new OleDbCommand();
OleDbDataAdapter concreteDataAdapter;
//OleDbTransaction concreteTransaction=concreteConnection.BeginTransaction();
concreteCommand.Connection=concreteConnection;
//concreteCommand.Transaction=concreteTransaction;
try
{
string commandString = "SELECT ";
for(int i = 0; i < columnNameArray.Length - 1; i++)
{
commandString += (columnNameArray[i] + ", ");
}
commandString += (columnNameArray[columnNameArray.Length - 1] + " FROM " + "'" + tableName + "'" +" WHERE " + conditionString);
concreteCommand.CommandText = commandString;
concreteDataAdapter = new OleDbDataAdapter(commandString,concreteConnection);
concreteDataAdapter.Fill(dataSet);
//concreteTransaction.Commit();
}
catch(Exception eee)
{
string xxx=eee.Message;
//concreteTransaction.Rollback();
dataSet.Clear();
return null;
}
finally
{
//concreteConnection.Close();
}
return dataSet;
}
/// <summary>
/// 可排序的条件查询操作
/// </summary>
/// <param name="ds">引用</param>
/// <param name="strTableName">表名</param>
/// <param name="strColumn">列名组</param>
/// <param name="strCondition">条件</param>
/// <param name="orderColumn">排序列</param>
/// <param name="riseorfall">升序或降序</param>
public static void OrderSelect(ref DataSet ds, string strTableName, string[] strColumn, string strCondition, string orderColumn, bool riseorfall)
{
AInte.DbAccess db = new DbAccess();
string constring = db.getconn();
OleDbConnection conn = new OleDbConnection(constring);
conn.Open();
OleDbCommand command = new OleDbCommand();
OleDbTransaction concreteDbTrans = conn.BeginTransaction();
command.Connection = conn;
command.Transaction = concreteDbTrans;
OleDbDataAdapter ada = new OleDbDataAdapter();;
try
{
string strSql = "SELECT ";
for(int i = 0; i < strColumn.Length - 1; i++)
{
strSql += (strColumn[i] + ", ");
}
strSql += (strColumn[strColumn.Length - 1] + " FROM " + strTableName + " WHERE " + strCondition);
if(orderColumn != "")
{
strSql += " ORDER BY " + orderColumn;
if(riseorfall)
{
strSql += " ASC";
}
else
{
strSql += " DESC";
}
}
command.CommandText = strSql;
ada.SelectCommand = command;
ada.Fill(ds);
concreteDbTrans.Commit();
}
catch
{
concreteDbTrans.Rollback();
ds.Clear();
throw;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 单条记录的插入操作
/// </summary>
/// <param name="strTableName">表名</param>
/// <param name="strColumn">列名组</param>
/// <param name="strValue">值组</param>
public static void Insert(string strTableName, string[] strColumn, object[] strValue)
{
AInte.DbAccess db = new DbAccess();
string constring = db.getconn();
OleDbConnection conn = new OleDbConnection(constring);
conn.Open();
OleDbCommand command = new OleDbCommand();
OleDbTransaction concreteDbTrans = conn.BeginTransaction();
command.Connection = conn;
command.Transaction = concreteDbTrans;
OleDbDataAdapter ada = new OleDbDataAdapter();;
try
{
string strSql = "INSERT INTO " + strTableName + " (";
for(int i = 0; i < strColumn.Length - 1; i++)
{
strSql += (strColumn[i] + ", ");
}
strSql += (strColumn[strColumn.Length - 1] + ") VALUES ('");
for(int i = 0; i < strValue.Length - 1; i++)
{
strSql += (strValue[i] + "', '");
}
strSql += (strValue[strValue.Length - 1] + "')");
command.CommandText = strSql;
command.ExecuteNonQuery();
concreteDbTrans.Commit();
}
catch
{
concreteDbTrans.Rollback();
throw;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 批量记录的插入操作,即可一次向多张表中插入不同的批量记录
/// </summary>
/// <param name="ds">批量记录组成的DataSet,DataSet中的各个DataTable名为表名,各DataTable中的DataColumn名为列名</param>
public static void InsertSet(ref DataSet ds)
{
AInte.DbAccess db = new DbAccess();
string constring = db.getconn();
OleDbConnection conn = new OleDbConnection(constring);
conn.Open();
OleDbCommand command = new OleDbCommand();
OleDbTransaction concreteDbTrans = conn.BeginTransaction();
command.Connection = conn;
command.Transaction = concreteDbTrans;
OleDbDataAdapter ada = new OleDbDataAdapter();;
try
{
foreach(DataTable dt in ds.Tables)
{
foreach(DataRow dr in dt.Rows)
{
string strSql = "INSERT INTO " + dt.TableName + " (";
for(int i = 0; i < dt.Columns.Count - 1; i++)
{
strSql += (dt.Columns[i].Caption + ", ");
}
strSql += (dt.Columns[dt.Columns.Count - 1].Caption + ") VALUES ('");
for(int i = 0; i < dt.Columns.Count - 1; i++)
{
strSql += (dr[i] + "', '");
}
strSql += (dr[dt.Columns.Count - 1] + "')");
command.CommandText = strSql;
command.ExecuteNonQuery();
}
}
concreteDbTrans.Commit();
}
catch
{
concreteDbTrans.Rollback();
throw;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 无条件删除操作,即删除表中所有记录
/// </summary>
/// <param name="strTableName">表名</param>
public static void DeleteAll(string strTableName)
{
AInte.DbAccess db = new DbAccess();
string constring = db.getconn();
OleDbConnection conn = new OleDbConnection(constring);
conn.Open();
OleDbCommand command = new OleDbCommand();
OleDbTransaction concreteDbTrans = conn.BeginTransaction();
command.Connection = conn;
command.Transaction = concreteDbTrans;
OleDbDataAdapter ada = new OleDbDataAdapter();;
try
{
string strSql = "DELETE FROM " + strTableName;
command.CommandText = strSql;
command.ExecuteNonQuery();
concreteDbTrans.Commit();
}
catch
{
concreteDbTrans.Rollback();
throw;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 条件删除操作
/// </summary>
/// <param name="strTableName">表名</param>
/// <param name="strCondition">条件</param>
public static bool Delete(string strTableName, string strCondition)//修改成了Bool类型的返回值
{
AInte.DbAccess db = new DbAccess();
string constring = db.getconn();
OleDbConnection conn = new OleDbConnection(constring);
conn.Open();
OleDbCommand command = new OleDbCommand();
OleDbTransaction concreteDbTrans = conn.BeginTransaction();
command.Connection = conn;
command.Transaction = concreteDbTrans;
OleDbDataAdapter ada = new OleDbDataAdapter();;
try
{
string strSql = "DELETE FROM " + strTableName + " WHERE " + strCondition;
command.CommandText = strSql;
command.ExecuteNonQuery();
concreteDbTrans.Commit();
return true;
}
catch
{
concreteDbTrans.Rollback();
throw;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 无条件更新操作,即更新表中所有记录
/// </summary>
/// <param name="strTableName">表名</param>
/// <param name="strColumn">列名组</param>
/// <param name="strValue">值组</param>
public static void UpdateAll(string strTableName, string[] strColumn, object[] strValue)
{
AInte.DbAccess db = new DbAccess();
string constring = db.getconn();
OleDbConnection conn = new OleDbConnection(constring);
conn.Open();
OleDbCommand command = new OleDbCommand();
OleDbTransaction concreteDbTrans = conn.BeginTransaction();
command.Connection = conn;
command.Transaction = concreteDbTrans;
OleDbDataAdapter ada = new OleDbDataAdapter();;
try
{
string strSql = "UPDATE " + strTableName + " SET ";
for(int i = 0; i < strColumn.Length - 1; i++)
{
strSql += (strColumn[i] + " = '" + strValue[i] + "', ");
}
strSql += (strColumn[strColumn.Length - 1] + " = '" + strValue[strValue.Length - 1] + "' ");
command.CommandText = strSql;
command.ExecuteNonQuery();
concreteDbTrans.Commit();
}
catch
{
concreteDbTrans.Rollback();
throw;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 条件更新操作
/// </summary>
/// <param name="strTableName">表名</param>
/// <param name="strColumn">列名组</param>
/// <param name="strValue">值组</param>
/// <param name="strCondition">条件</param>
public static void Update(string strTableName, string[] strColumn, object[] strValue, string strCondition)
{
AInte.DbAccess db = new DbAccess();
string constring = db.getconn();
OleDbConnection conn = new OleDbConnection(constring);
conn.Open();
OleDbCommand command = new OleDbCommand();
OleDbTransaction concreteDbTrans = conn.BeginTransaction();
command.Connection = conn;
command.Transaction = concreteDbTrans;
OleDbDataAdapter ada = new OleDbDataAdapter();;
try
{
string strSql = "UPDATE " + strTableName + " SET ";
for(int i = 0; i < strColumn.Length - 1; i++)
{
strSql += (strColumn[i] + " = '" + strValue[i] + "', ");
}
strSql += (strColumn[strColumn.Length - 1] + " = '" + strValue[strValue.Length - 1] + "' " + " WHERE " + strCondition);
command.CommandText = strSql;
command.ExecuteNonQuery();
concreteDbTrans.Commit();
}
catch
{
concreteDbTrans.Rollback();
throw;
}
finally
{
conn.Close();
}
}
public string getconn()
{
string ConString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "+ Server.MapPath("") +"file://AInte.mdb/"; //需要修改为你的数据库名
return ConString;
}
}
}