62,182
社区成员
发帖
与我相关
我的任务
分享
using System;
using System.Data;
using System.Data.OleDb;
using System.Web;
using System.Web.UI;
using System.Configuration;
namespace Com.Access.Database
{
/// <summary>
/// conn 的摘要说明。
///
///<configuration>
/// <appSettings/>
/// <connectionStrings>
/// <add name="ConnectionString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\TeacherSystem\App_Data\db.mdb;Jet OLEDB:Database Password=123"
/// providerName="System.Data.OleDb" />
/// </connectionStrings>
///</configuration>
///
/// </summary>
public class ConnDbForAcccess
{
/// <summary>
/// 连接数据库字符串
/// </summary>
private string connectionString;
/// <summary>
/// 存储数据库连接(保护类,只有由它派生的类才能访问)
/// </summary>
protected OleDbConnection Connection;
/// <summary>
/// 构造函数:数据库的默认连接
/// </summary>
public ConnDbForAcccess()
{
string connStr;
connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString.ToString();
//connStr = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"].ToString(); //从web.config配置中读取
connectionString = connStr;
//connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + HttpContext.Current.Request.PhysicalApplicationPath + connStr;
//connectionString = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"].ToString();
Connection = new OleDbConnection(connectionString);
}
/// <summary>
/// 构造函数:带有参数的数据库连接
/// </summary>
/// <param name="newConnectionString"></param>
public ConnDbForAcccess(string newConnectionString)
{
//connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + HttpContext.Current.Request.PhysicalApplicationPath + newConnectionString;
connectionString = newConnectionString;
Connection = new OleDbConnection(connectionString);
}
/// <summary>
/// 获得连接字符串
/// </summary>
public string ConnectionString
{
get
{
return connectionString;
}
}
/// <summary>
/// 执行SQL语句没有返回结果,如:执行删除、更新、插入等操作
/// </summary>
/// <param name="strSQL"></param>
/// <returns>操作成功标志</returns>
public bool ExeSQL(string strSQL)
{
bool resultState = false;
Connection.Open();
OleDbTransaction myTrans = Connection.BeginTransaction();
OleDbCommand command = new OleDbCommand(strSQL, Connection, myTrans);
try
{
command.ExecuteNonQuery();
myTrans.Commit();
resultState = true;
}
catch
{
myTrans.Rollback();
resultState = false;
}
finally
{
Connection.Close();
}
return resultState;
}
/// <summary>
/// 执行SQL语句返回结果到DataReader中
/// </summary>
/// <param name="strSQL"></param>
/// <returns>dataReader</returns>
private OleDbDataReader ReturnDataReader(string strSQL)
{
Connection.Open();
OleDbCommand command = new OleDbCommand(strSQL, Connection);
OleDbDataReader dataReader = command.ExecuteReader();
Connection.Close();
return dataReader;
}
/// <summary>
/// 执行SQL语句返回结果到DataSet中
/// </summary>
/// <param name="strSQL"></param>
/// <returns>DataSet</returns>
public DataSet ReturnDataSet(string strSQL)
{
Connection.Open();
DataSet dataSet = new DataSet();
OleDbDataAdapter OleDbDA = new OleDbDataAdapter(strSQL, Connection);
OleDbDA.Fill(dataSet, "objDataSet");
Connection.Close();
return dataSet;
}
/// <summary>
/// 执行一查询语句,同时返回查询结果数目
/// </summary>
/// <param name="strSQL"></param>
/// <returns>sqlResultCount</returns>
public int ReturnSqlResultCount(string strSQL)
{
int sqlResultCount = 0;
try
{
Connection.Open();
OleDbCommand command = new OleDbCommand(strSQL, Connection);
OleDbDataReader dataReader = command.ExecuteReader();
while (dataReader.Read())
{
sqlResultCount++;
}
dataReader.Close();
}
catch
{
sqlResultCount = 0;
}
finally
{
Connection.Close();
}
return sqlResultCount;
}
}
}
using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
/// <summary>
/// SqlHelper 的摘要说明。
/// </summary>
public class DBHelper
{
public static readonly string oleconstring = System.Configuration.ConfigurationSettings.AppSettings["oleconstring"];
public DBHelper()
{
}
/// <summary>
/// OleDb数据库增、删、改方法
/// </summary>
/// <param name="sql">执行数据库操作语句</param>
/// <param name="param">参数数组</param>
/// <returns>返回int类型,返回0则操作失败,返回数大于0则操作成功</returns>
public static int ExecuteNonquery(string sql, params OleDbParameter[] param)
{
int bFlag = 0;
OleDbCommand cmd = new OleDbCommand();
using (OleDbConnection con = new OleDbConnection(oleconstring))
{
cmd.Connection = con;
cmd.CommandText = sql;
if (param.Length > 0)
{
foreach (OleDbParameter p in param)
{
cmd.Parameters.Add(p);
}
}
try
{
if (con.State == ConnectionState.Closed)
con.Open();
bFlag = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
string msg = ex.Message;
}
finally
{
con.Close();
}
}
return bFlag;
}
/// <summary>
/// OleDb数据库增、删、改方法
/// </summary>
/// <param name="sql">执行数据库操作语句</param>
/// <param name="param">参数数组</param>
/// <returns>返回bool类型</returns>
public static bool ExecuteNonqueryBool(string sql, params OleDbParameter[] param)
{
bool bFlag = false;
OleDbCommand cmd = new OleDbCommand();
using (OleDbConnection con = new OleDbConnection(oleconstring))
{
cmd.Connection = con;
cmd.CommandText = sql;
if (param.Length > 0)
{
foreach (OleDbParameter p in param)
{
cmd.Parameters.Add(p);
}
}
try
{
if (con.State == ConnectionState.Closed)
con.Open();
if (cmd.ExecuteNonQuery() > 0)
{
bFlag = true;
}
}
catch (Exception ex)
{
string msg = ex.Message;
}
finally
{
con.Close();
}
}
return bFlag;
}
/// <summary>
/// OleDb数据库查询方法
/// </summary>
/// <param name="sql">执行数据库操作语句</param>
/// <param name="param">参数数组</param>
/// <returns>返回DataTable类型</returns>
public static DataTable ExecuteDataTable(string sql, params OleDbParameter[] param)
{
DataTable dt = new DataTable();
OleDbDataAdapter da = new OleDbDataAdapter();
OleDbCommand cmd = new OleDbCommand();
da.SelectCommand = cmd;
using (OleDbConnection con = new OleDbConnection(oleconstring))
{
cmd.Connection = con;
cmd.CommandText = sql;
if (param.Length > 0)
{
foreach (OleDbParameter p in param)
{
cmd.Parameters.Add(p);
}
}
try
{
da.Fill(dt);
}
catch (Exception ex)
{
string msg = ex.Message;
}
finally
{ }
}
return dt;
}
/// <summary>
/// OleDb数据库查询方法
/// </summary>
/// <param name="sql">执行数据库操作语句</param>
/// <param name="param">参数数组</param>
/// <returns>返回DataSet类型</returns>
public static DataSet ExecuteDataSet(string sql, params OleDbParameter[] param)
{
DataSet ds = new DataSet();
OleDbDataAdapter da = new OleDbDataAdapter();
OleDbCommand cmd = new OleDbCommand();
da.SelectCommand = cmd;
using (OleDbConnection con = new OleDbConnection(oleconstring))
{
cmd.Connection = con;
cmd.CommandText = sql;
if (param.Length > 0)
{
foreach (OleDbParameter p in param)
{
cmd.Parameters.Add(p);
}
}
try
{
da.Fill(ds);
}
catch (Exception ex)
{
string msg = ex.Message;
}
finally
{ }
}
return ds;
}
/// <summary>
/// OleDb数据库查询方法
/// </summary>
/// <param name="sql">执行数据库操作语句</param>
/// <param name="param">参数数组</param>
/// <returns>返回ArrayList类型</returns>
public static ArrayList ExecuteArrayList(string sql, params OleDbParameter[] param)
{
OleDbDataReader reader = null;
ArrayList al = new ArrayList();
OleDbCommand cmd = new OleDbCommand();
using (OleDbConnection con = new OleDbConnection(oleconstring))
{
cmd.Connection = con;
cmd.CommandText = sql;
if (param.Length > 0)
{
foreach (OleDbParameter p in param)
{
cmd.Parameters.Add(p);
}
}
try
{
if (con.State == ConnectionState.Closed)
con.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
al.Add(reader[0]);
}
//while (reader.Read())
//{
// //for (int i = 1; i < reader.FieldCount; i++)
// //{
// // al.Add(reader[i]);
// //}
// foreach (Object obj in reader)
// {
// al.Add(obj);
// }
//}
}
catch (Exception ex)
{
string msg = ex.Message;
}
finally
{ }
}
return al;
}
/// <summary>
/// OleDb数据库查询方法
/// </summary>
/// <param name="sql">执行数据库操作语句</param>
/// <param name="param">参数数组</param>
/// <returns>返回Object类型</returns>
public static Object ExecuteObject(string sql, params OleDbParameter[] param)
{
OleDbDataReader reader = null;
Object obj = null;
OleDbCommand cmd = new OleDbCommand();
using (OleDbConnection con = new OleDbConnection(oleconstring))
{
cmd.Connection = con;
cmd.CommandText = sql;
if (param.Length > 0)
{
foreach (OleDbParameter p in param)
{
cmd.Parameters.Add(p);
}
}
try
{
if (con.State == ConnectionState.Closed)
con.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
obj = reader[0];
}
//while (reader.Read())
//{
// //for (int i = 1; i < reader.FieldCount; i++)
// //{
// // al.Add(reader[i]);
// //}
// foreach (Object obj in reader)
// {
// al.Add(obj);
// }
//}
}
catch (Exception ex)
{
string msg = ex.Message;
}
finally
{ }
}
return obj;
}
/// <summary>
/// OleDb数据库验证方法
/// </summary>
/// <param name="sql">执行数据库操作语句</param>
/// <param name="param">参数数组</param>
/// <returns>返回bool类型</returns>
public static bool Exists(string sql, params OleDbParameter[] param)
{
OleDbDataReader reader = null;
bool flag = false;
OleDbCommand cmd = new OleDbCommand();
using (OleDbConnection con = new OleDbConnection(oleconstring))
{
cmd.Connection = con;
cmd.CommandText = sql;
if (param.Length > 0)
{
foreach (OleDbParameter p in param)
{
cmd.Parameters.Add(p);
}
}
try
{
if (con.State == ConnectionState.Closed)
con.Open();
reader = cmd.ExecuteReader();
if (reader.Read())
{
flag = true;
}
}
catch (Exception ex)
{
flag = false;
string msg = ex.Message;
}
finally
{ }
}
return flag;
}
}
//用petshop上面的好了,呵呵
/// <summary>
/// The OleDbHelper class is intended to encapsulate high performance,
/// scalable best practices for common uses of OleDbClient.
/// </summary>
public abstract class OleDbHelper {
//Database connection strings
public static readonly string connectionString = System.Configuration.ConfigurationManager.AppSettings["dbString"].ToString() + System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["dbPath"]) + ";";
public static DataTable FillDataSet(string connectionString, CommandType cmdType, string cmdText)
{
DataSet ds = new DataSet();
OleDbConnection conn = new OleDbConnection(connectionString);
conn.Open();
OleDbDataAdapter sda = new OleDbDataAdapter(cmdText, conn);
sda.Fill(ds);
conn.Close();
return ds.Tables[0];
}
// Hashtable to store cached parameters
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
/// <summary>
/// Execute a OleDbCommand (that returns no resultset) against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params OleDbParameter[] commandParameters) {
OleDbCommand cmd = new OleDbCommand();
using (OleDbConnection conn = new OleDbConnection(connectionString)) {
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// Execute a OleDbCommand (that returns no resultset) against an existing database connection
/// using the provided parameters.
/// </summary>
/// <remarks>
public static int ExecuteNonQuery(OleDbConnection connection, CommandType cmdType, string cmdText, params OleDbParameter[] commandParameters) {
OleDbCommand cmd = new OleDbCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// Execute a OleDbCommand (that returns no resultset) using an existing SQL Transaction
/// using the provided parameters.
/// </summary>
/// <remarks>
public static int ExecuteNonQuery(OleDbTransaction trans, CommandType cmdType, string cmdText, params OleDbParameter[] commandParameters) {
OleDbCommand cmd = new OleDbCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// Execute a OleDbCommand that returns a resultset against the database specified in the connection string
/// using the provided parameters.
/// </summary>
public static OleDbDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params OleDbParameter[] commandParameters) {
OleDbCommand cmd = new OleDbCommand();
OleDbConnection conn = new OleDbConnection(connectionString);
// we use a try/catch here because if the method throws an exception we want to
// close the connection throw code, because no datareader will exist, hence the
// commandBehaviour.CloseConnection will not work
try {
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
OleDbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch {
conn.Close();
throw;
}
}
/// <summary>
/// Execute a OleDbCommand that returns the first column of the first record against the database specified in the connection string
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new OleDbParameter("@prodid", 24));
/// </remarks>
/// <param name="connectionString">a valid connection string for a OleDbConnection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of OleDbParamters used to execute the command</param>
/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params OleDbParameter[] commandParameters) {
OleDbCommand cmd = new OleDbCommand();
using (OleDbConnection connection = new OleDbConnection(connectionString)) {
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// Execute a OleDbCommand that returns the first column of the first record against an existing database connection
/// using the provided parameters.
/// </summary>
/// <remarks>
/// e.g.:
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new OleDbParameter("@prodid", 24));
/// </remarks>
/// <param name="conn">an existing database connection</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of OleDbParamters used to execute the command</param>
/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
public static object ExecuteScalar(OleDbConnection connection, CommandType cmdType, string cmdText, params OleDbParameter[] commandParameters) {
OleDbCommand cmd = new OleDbCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// add parameter array to the cache
/// </summary>
/// <param name="cacheKey">Key to the parameter cache</param>
/// <param name="cmdParms">an array of OleDbParamters to be cached</param>
public static void CacheParameters(string cacheKey, params OleDbParameter[] commandParameters) {
parmCache[cacheKey] = commandParameters;
}
/// <summary>
/// Retrieve cached parameters
/// </summary>
/// <param name="cacheKey">key used to lookup parameters</param>
/// <returns>Cached OleDbParamters array</returns>
public static OleDbParameter[] GetCachedParameters(string cacheKey) {
OleDbParameter[] cachedParms = (OleDbParameter[])parmCache[cacheKey];
if (cachedParms == null)
return null;
OleDbParameter[] clonedParms = new OleDbParameter[cachedParms.Length];
for (int i = 0, j = cachedParms.Length; i < j; i++)
clonedParms[i] = (OleDbParameter)((ICloneable)cachedParms[i]).Clone();
return clonedParms;
}
/// <summary>
/// Prepare a command for execution
/// </summary>
/// <param name="cmd">OleDbCommand object</param>
/// <param name="conn">OleDbConnection object</param>
/// <param name="trans">OleDbTransaction object</param>
/// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
/// <param name="cmdText">Command text, e.g. Select * from Products</param>
/// <param name="cmdParms">OleDbParameters to use in the command</param>
private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, CommandType cmdType, string cmdText, OleDbParameter[] cmdParms) {
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
if (cmdParms != null) {
foreach (OleDbParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
}