62,074
社区成员
发帖
与我相关
我的任务
分享ACCESS 数据访问层代码。。。。。。。。谁发一份。
ACCESS数据访问层代码。C#版的，要带参数那种，谢谢大家啊
我知道或许我发这个帖会被挨砖，
但是我真的是很急着要。
找了半天找不到，微软那个 sqlhelper也看过了，不符合我的。。。。。。
谁能给我一个啊。
付出全部分。
。。。。。虽然不到一百。
我知道或许我发这个帖会被挨砖，
但是我真的是很急着要。
找了半天找不到，微软那个 sqlhelper也看过了，不符合我的。。。。。。
谁能给我一个啊。
付出全部分。
。。。。。虽然不到一百。
...全文
请发表友善的回复…
发表回复
mengxj85 2009-06-19
- 打赏
- 举报
下几个开源项目在从中选合适的
xiaojing7 2009-06-19
- 打赏
- 举报
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;
}
}
}
wenblue7 2009-06-19
- 打赏
- 举报
这是DBHelper：
有了这个就随你调用了：
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.OleDb;
namespace XjbjDal
{
///<summary>
/// 数据库访问通用方法类
/// </summary>
public static class DBHelper
{
private static OleDbConnection connection;//连接对象
public static OleDbConnection Connection
{
get
{
//string connectionString = ConfigurationManager.ConnectionStrings["dbstr"].ConnectionString;
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=E:\\迅捷报价\\DB\\xunjie.mdb";
if (connection == null)
{
connection = new OleDbConnection(connectionString);
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Broken)
{
connection.Close();
connection.Open();
}
return connection;
}
}
//执行SQl语句
public static int ExecuteCommand(string safeSql)
{
OleDbCommand cmd = new OleDbCommand(safeSql, Connection);
int result = cmd.ExecuteNonQuery();
return result;
}
//带参数值的sql
public static int ExecuteCommand(string sql, params OleDbParameter[] values)
{
OleDbCommand cmd = new OleDbCommand(sql, Connection);
cmd.Parameters.AddRange(values);
return cmd.ExecuteNonQuery();
}
//执行SQl语句的
public static int GetScalar(string safeSql)
{
OleDbCommand cmd = new OleDbCommand(safeSql, Connection);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
//返回第一行第一列
public static int GetScalar(string sql, params OleDbParameter[] values)
{
OleDbCommand cmd = new OleDbCommand(sql, Connection);
cmd.Parameters.AddRange(values);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
//查询返回记录集对象
public static OleDbDataReader GetReader(string safeSql)
{
OleDbCommand cmd = new OleDbCommand(safeSql, Connection);
OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
//带参数值的查询返回记录集对象
public static OleDbDataReader GetReader(string sql, params OleDbParameter[] values)
{
OleDbCommand cmd = new OleDbCommand(sql, Connection);
cmd.Parameters.AddRange(values);
OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
//返回表格的查询
public static DataTable GetDataSet(string safeSql)
{
DataSet ds = new DataSet();
OleDbCommand cmd = new OleDbCommand(safeSql, Connection);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
//带参数的查询返回表
public static DataTable GetDataSet(string sql, params OleDbParameter[] values)
{
DataSet ds = new DataSet();
OleDbCommand cmd = new OleDbCommand(sql, Connection);
cmd.Parameters.AddRange(values);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
//新增方法
//返回第一行第一列
public static String GetScalarToStr(string safeSql)
{
OleDbCommand cmd = new OleDbCommand(safeSql, Connection);
string result = cmd.ExecuteScalar().ToString();
return result;
}
}
}
有了这个就随你调用了：
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Data.OleDb;
namespace XjbjDal
{
///<summary>
/// 数据库访问通用方法类
/// </summary>
public static class DBHelper
{
private static OleDbConnection connection;//连接对象
public static OleDbConnection Connection
{
get
{
//string connectionString = ConfigurationManager.ConnectionStrings["dbstr"].ConnectionString;
string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=E:\\迅捷报价\\DB\\xunjie.mdb";
if (connection == null)
{
connection = new OleDbConnection(connectionString);
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Broken)
{
connection.Close();
connection.Open();
}
return connection;
}
}
//执行SQl语句
public static int ExecuteCommand(string safeSql)
{
OleDbCommand cmd = new OleDbCommand(safeSql, Connection);
int result = cmd.ExecuteNonQuery();
return result;
}
//带参数值的sql
public static int ExecuteCommand(string sql, params OleDbParameter[] values)
{
OleDbCommand cmd = new OleDbCommand(sql, Connection);
cmd.Parameters.AddRange(values);
return cmd.ExecuteNonQuery();
}
//执行SQl语句的
public static int GetScalar(string safeSql)
{
OleDbCommand cmd = new OleDbCommand(safeSql, Connection);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
//返回第一行第一列
public static int GetScalar(string sql, params OleDbParameter[] values)
{
OleDbCommand cmd = new OleDbCommand(sql, Connection);
cmd.Parameters.AddRange(values);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
//查询返回记录集对象
public static OleDbDataReader GetReader(string safeSql)
{
OleDbCommand cmd = new OleDbCommand(safeSql, Connection);
OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
//带参数值的查询返回记录集对象
public static OleDbDataReader GetReader(string sql, params OleDbParameter[] values)
{
OleDbCommand cmd = new OleDbCommand(sql, Connection);
cmd.Parameters.AddRange(values);
OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
//返回表格的查询
public static DataTable GetDataSet(string safeSql)
{
DataSet ds = new DataSet();
OleDbCommand cmd = new OleDbCommand(safeSql, Connection);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
//带参数的查询返回表
public static DataTable GetDataSet(string sql, params OleDbParameter[] values)
{
DataSet ds = new DataSet();
OleDbCommand cmd = new OleDbCommand(sql, Connection);
cmd.Parameters.AddRange(values);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
//新增方法
//返回第一行第一列
public static String GetScalarToStr(string safeSql)
{
OleDbCommand cmd = new OleDbCommand(safeSql, Connection);
string result = cmd.ExecuteScalar().ToString();
return result;
}
}
}
xupeihuagudulei 2009-06-19
- 打赏
- 举报
数据访问基类。
xupeihuagudulei 2009-06-19
- 打赏
- 举报
[Quote=引用 7 楼 wuyq11 的回复:]
参考
还可到51aspx.com上看看很多。
[/Quote]你让我看的那网站上的就是微软那sqlhelp改过来的吧。
参考
还可到51aspx.com上看看很多。
[/Quote]你让我看的那网站上的就是微软那sqlhelp改过来的吧。
xupeihuagudulei 2009-06-19
- 打赏
- 举报
[Quote=引用 7 楼 wuyq11 的回复:]
参考
还可到51aspx.com上看看很多。
[/Quote]
51aspx 上没有。
你给的那个CNBLOG的好像也不符合我的要求，我需要是带参数进去的，可是看了下没有，
或许是本人太菜，不知道如何去使用那带参数的，
谁能告诉我啊，
参考
还可到51aspx.com上看看很多。
[/Quote]
51aspx 上没有。
你给的那个CNBLOG的好像也不符合我的要求，我需要是带参数进去的，可是看了下没有，
或许是本人太菜，不知道如何去使用那带参数的，
谁能告诉我啊，
xingzhiasa 2009-06-19
- 打赏
- 举报
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;
}
}
xupeihuagudulei 2009-06-19
- 打赏
- 举报
[Quote=引用 2 楼 xupeihuagudulei 的回复:]
在线等，现在大家是不是都在休息。
郁闷。。。。。。
[/Quote]
在线等，现在大家是不是都在休息。
郁闷。。。。。。
[/Quote]
来看外星人 2009-06-19
- 打赏
- 举报
csdn说我回复内容过长！我又懶得一段一段发．加QQ我发给你．285923436
xupeihuagudulei 2009-06-19
- 打赏
- 举报
急急急，谁发个带参数的来吧
xupeihuagudulei 2009-06-19
- 打赏
- 举报
在线等，现在大家是不是都在休息。
郁闷。。。。。。
郁闷。。。。。。
xupeihuagudulei 2009-06-19
- 打赏
- 举报
SF
谁来帮帮偶啊
谁来帮帮偶啊
xupeihuagudulei 2009-06-19
- 打赏
- 举报
[Quote=引用 18 楼 xingzhiasa 的回复:]
难道我那个不能用吗？我一直都用这个的……
[/Quote]看了下你的应该能用的。但是我不知道如何使用，
丢人哪，能不能给我一个例子。不知道如何传参数进去sqlparamers[],这种的
params OleDbParameter[] param
要调用有这种参数的方法如何传参数进去。这个我不会使用。。。。。。
难道我那个不能用吗？我一直都用这个的……
[/Quote]看了下你的应该能用的。但是我不知道如何使用，
丢人哪，能不能给我一个例子。不知道如何传参数进去sqlparamers[],这种的
params OleDbParameter[] param
要调用有这种参数的方法如何传参数进去。这个我不会使用。。。。。。
xingzhiasa 2009-06-19
- 打赏
- 举报
难道我那个不能用吗？我一直都用这个的……
oo_2008 2009-06-19
- 打赏
- 举报
//用petshop上面的好了，呵呵
龙宜坡 2009-06-19
- 打赏
- 举报
[Quote=引用 13 楼 mengxj85 的回复:]
下几个开源项目在从中选合适的
[/Quote]
高见！
下几个开源项目在从中选合适的
[/Quote]
高见！
xupeihuagudulei 2009-06-19
- 打赏
- 举报
[Quote=引用 12 楼 xiaojing7 的回复:]
C# codeusing 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.OL…
[/Quote]
小井，你这个都没带参数，直接执行sql语句的，
C# codeusing 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.OL…
[/Quote]
小井，你这个都没带参数，直接执行sql语句的，
零点不睡觉 2009-06-19
- 打赏
- 举报
/// <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);
}
}
}
