网上估计有很多版本了,自己仿照SqlHelper实现了这个,用了感觉不错,欢迎提意见,欢迎介绍项目。谢谢大家了
using
System;
using
System.Data;
using
System.Data.OleDb;
![None.gif](/Images/OutliningIndicators/None.gif)
namespace
DAL
![ExpandedBlockStart.gif](/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// AccessHelper 的摘要说明。
/// </summary>
public class AccessHelper
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
private AccessHelper()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
//静态构造函数,表示该类不可以被实例化
//所以的成员都是static类型的
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
private static string m_connection = "";
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// ConnectionString的属性
/// </summary>
public static string ConnectionString
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
set
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
m_connection = value;
}
get
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return m_connection;
}
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 判断OleDbConnection是否已经连接
/// </summary>
/// <returns>true 表示OleDbConnection已经连接,或者设置连接成功;
/// false 表示OleDbConnection尝试连接失败</returns>
public static bool IsConnected()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
OleDbConnection conn = new OleDbConnection(m_connection);
try
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
conn.Open();
return true;
}
catch
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return false;
}
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 执行一条Sql命令
/// </summary>
/// <param name="cmdText">Sql语句</param>
/// <returns>表示影响的行数</returns>
public static int ExecuteText(string cmdText)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
OleDbCommand cmd = new OleDbCommand();
try
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
PrepareCommand(cmd,CommandType.Text,cmdText);
try
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return cmd.ExecuteNonQuery();
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
catch (OleDbException e)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw (new Exception(e.Message));;
}
}
catch (Exception ex)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw ex;
}
finally
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
DisposeCmd(cmd);
}
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 执行一条Sql命令(ExecuteScalar),返回一个结果值(int, string, float等类型)。如果不存在记录,返回为null。
/// </summary>
/// <param name="cmdText">Sql语句</param>
/// <returns>返回值(int, string, float等类型)。如果不存在记录,返回为null。</returns>
public static object ExecuteTextRet(string cmdText)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
OleDbCommand cmd = new OleDbCommand();
try
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
PrepareCommand(cmd, CommandType.Text, cmdText);
try
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return cmd.ExecuteScalar();
}
catch (OleDbException e)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw (new Exception(e.Message));
}
}
catch (Exception ex)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw ex;
}
finally
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
DisposeCmd(cmd);
}
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 执行一个Sql命令,返回一个DataSet结果集。
/// </summary>
/// <param name="cmdText">Sql语句</param>
/// <param name="TableName">Fill入DataSet中的表名</param>
/// <returns>DataSet结果集</returns>
public static DataSet ExecuteTextRet(string cmdText, string TableName)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
DataSet ds = new DataSet();
OleDbDataAdapter adp = new OleDbDataAdapter();
OleDbCommand cmd = new OleDbCommand();
try
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
PrepareCommand(cmd, CommandType.Text, cmdText);
try
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
adp.SelectCommand = cmd;
adp.Fill(ds,TableName);
return ds;
}
catch (OleDbException e)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw (new Exception(e.Message));
}
}
catch (Exception ex)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw ex;
}
finally
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
DisposeCmd(cmd);
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 把一个DataSet更新入数据库(可以修改,添加,删除)
/// 但是,这个表的设计中必须要有主键
/// </summary>
/// <param name="cmdText">select语句,和要更新的表对应</param>
/// <param name="ds">要更新入数据库的DataSet</param>
/// <param name="TableName">DataSet中的源表名</param>
/// <returns>影响的行数</returns>
public static int ExecuteUpdate(string cmdText,DataSet ds,string TableName)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
OleDbDataAdapter adp = new OleDbDataAdapter();
OleDbCommand cmd = new OleDbCommand();
try
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
PrepareCommand(cmd, CommandType.Text, cmdText);
adp.SelectCommand = cmd;
OleDbCommandBuilder builder = new OleDbCommandBuilder(adp);
return adp.Update(ds,TableName);
}
catch (Exception ex)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw ex;
}
finally
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
DisposeCmd(cmd);
adp.Dispose();
}
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 设置OleDbCommand的参数
/// </summary>
/// <param name="cmd">OleDbCommand对象</param>
/// <param name="cmdType">类型</param>
/// <param name="cmdText">Sql语句</param>
private static void PrepareCommand(OleDbCommand cmd, CommandType cmdType, string cmdText)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
OleDbConnection conn = new OleDbConnection(m_connection);
try
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
conn.Open();
if (cmd != null)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
}
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
catch (OleDbException e)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
throw (new Exception(e.Message));
}
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 处理OleDbCommand,关闭连接,并且释放内存。
/// </summary>
/// <param name="cmd">要处理的OleDbCommand对象</param>
private static void DisposeCmd(OleDbCommand cmd)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (cmd.Connection != null)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
cmd.Connection.Close();
cmd.Connection.Dispose();
}
cmd.Dispose();
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
填加段#region 填加段
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 根据app.config获取连接字符串
/// </summary>
/// <returns></returns>
public static string GetConnectionStringFromConfigFile()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
DataSet ds = new DataSet();
ds.ReadXml(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile);
string result = ds.Tables["appSettings"].Rows[0]["DateSource"].ToString();
ds.Clear();
ds.Dispose();
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
result = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + result;
return result;
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 根据app.config获取数据源的信息
/// </summary>
/// <returns></returns>
public static string GetDataSourceFromConfigFile()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
DataSet ds = new DataSet();
ds.ReadXml(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile);
string result = ds.Tables["appSettings"].Rows[0]["DateSource"].ToString();
ds.Clear();
ds.Dispose();
return result;
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 设置app.config的数据源信息
/// </summary>
/// <param name="dataSource"></param>
public static void SetDataSourceToConfigFile(string dataSource)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
DataSet ds = new DataSet();
ds.ReadXml(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile);
ds.Tables["appSettings"].Rows[0]["DateSource"]=dataSource;
ds.AcceptChanges();
ds.WriteXml(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile);
ds.Clear();
ds.Dispose();
}
![InBlock.gif](/Images/OutliningIndicators/InBlock.gif)
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//// <summary>
/// 测试能否连接(打开并关闭)
/// </summary>
/// <returns></returns>
public static bool IsCanConnected()
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
OleDbConnection conn = new OleDbConnection(m_connection);
bool result = false;
try
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
conn.Open();
result = true;
}
catch
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
result = false;
}
finally
![ExpandedSubBlockStart.gif](/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
conn.Close();
}
return result;
}
#endregion
}
}