/*
* By :落落
* URL: Www.MyLuoLuo.Com
*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
namespace DBHelp
{
/// <summary>
/// Access Help
/// </summary>
public class Access
{
#region 小功能
/// <summary>
/// 数据库访问
/// </summary>
OleDbConnection conn;
string conString;
public OleDbConnection con
{
get {
if (conn == null)
{
conn = new OleDbConnection(conString);
}
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
return conn;
}
}
/// <summary>
/// 数据库连接字符串
/// </summary>
/// <param name="conString"></param>
public Access(string conString)
{
try
{
conn = new OleDbConnection(conString);
this.conString = conString;
conn.Open();
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 数据库连接字符串
/// </summary>
/// <param name="conString"></param>
public Access()
{
try
{
//Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data.love
this.conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=data.love";
conn = new OleDbConnection(this.conString);
conn.Open();
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 填充
/// </summary>
/// <param name="com"></param>
/// <param name="array"></param>
void SetParametersArray(ref OleDbCommand com, ParametersArray array)
{
foreach (OleDbParameter item in array.GetArray())
{
com.Parameters.Add(item);
}
}
#endregion
#region 执行SQL语句并返回受影响的行数
/// <summary>
/// 执行SQL语句并返回受影响的行数
/// </summary>
/// <returns></returns>
public int ExecuteNonQuery(string sql)
{
try
{
using (OleDbCommand com = new OleDbCommand(sql, con))
{
return com.ExecuteNonQuery();
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 执行SQL语句并返回受影响的行数
/// </summary>
public int ExecuteNonQuery(string sql, OleDbParameter par)
{
try
{
using (OleDbCommand com = new OleDbCommand(sql, con))
{
com.Parameters.Add(par);
return com.ExecuteNonQuery();
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 执行SQL语句并返回受影响的行数
/// </summary>
/// <param name="sql"></param>
/// <param name="array"></param>
/// <returns></returns>
public int ExecuteNonQuery(string sql, ParametersArray array)
{
try
{
OleDbCommand com = new OleDbCommand(sql, con);
SetParametersArray(ref com, array);
return com.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
}
#endregion
#region ExecuteReader
public OleDbDataReader ExecuteReader(string sql)
{
try
{
using (OleDbCommand com = new OleDbCommand(sql, con))
{
OleDbDataReader reader = com.ExecuteReader();
return reader;
}
}
catch (Exception)
{
throw;
}
}
public OleDbDataReader ExecuteReader(string sql, OleDbParameter par)
{
try
{
using (OleDbCommand com = new OleDbCommand(sql, con))
{
com.Parameters.Add(par);
OleDbDataReader reader = com.ExecuteReader();
return reader;
}
}
catch (Exception)
{
throw;
}
}
public OleDbDataReader ExecuteReader(string sql, ParametersArray array)
{
try
{
OleDbCommand com = new OleDbCommand(sql, con);
SetParametersArray(ref com, array);
OleDbDataReader reader = com.ExecuteReader();
return reader;
}
catch (Exception)
{
throw;
}
}
#endregion
#region 读取查询结果中的第一行第一列
/// <summary>
/// 读取查询结果中的第一行第一列
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public object ExecuteScalar(string sql)
{
try
{
using (OleDbCommand com = new OleDbCommand(sql, con))
{
return com.ExecuteScalar();
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 读取查询结果中的第一行第一列
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public object ExecuteScalar(string sql, OleDbParameter par)
{
try
{
using (OleDbCommand com = new OleDbCommand(sql, con))
{
com.Parameters.Add(par);
return com.ExecuteScalar();
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 读取查询结果中的第一行第一列
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public object ExecuteScalar(string sql, ParametersArray array)
{
try
{
OleDbCommand com = new OleDbCommand(sql, con);
SetParametersArray(ref com, array);
return com.ExecuteScalar();
}
catch (Exception)
{
throw;
}
}
#endregion
#region 执行Insert语句,并返回新添加的记录ID
/// <summary>
/// 执行Insert语句,并返回新添加的记录ID
/// </summary>
/// <returns></returns>
public object ExecuteNonQueryAndGetIdentity(string sql)
{
try
{
using (OleDbCommand com = new OleDbCommand(sql, con))
{
if (com.ExecuteNonQuery() >= )
{
com.CommandText = "select @@identity";
return com.ExecuteScalar();
}
else
{
throw new Exception("记录添加不成功！");
}
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 执行Insert语句,并返回新添加的记录ID
/// </summary>
public object ExecuteNonQueryAndGetIdentity(string sql, OleDbParameter par)
{
try
{
using (OleDbCommand com = new OleDbCommand(sql, con))
{
com.Parameters.Add(par);
if (com.ExecuteNonQuery() >= )
{
com.CommandText = "select @@identity";
return com.ExecuteScalar();
}
else
{
throw new Exception("记录添加不成功！");
}
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 执行Insert语句,并返回新添加的记录ID
/// </summary>
/// <param name="sql"></param>
/// <param name="array"></param>
/// <returns></returns>
public object ExecuteNonQueryAndGetIdentity(string sql, ParametersArray array)
{
try
{
OleDbCommand com = new OleDbCommand(sql, con);
SetParametersArray(ref com, array);
if (com.ExecuteNonQuery() >= )
{
com.CommandText = "select @@identity";
return com.ExecuteScalar();
}
else
{
throw new Exception("记录添加不成功！");
}
}
catch (Exception)
{
throw;
}
}
#endregion
#region 返回DataSet
/// <summary>
/// 返回DataSet
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataSet GetDataSet(string sql)
{
try
{
using (OleDbDataAdapter adpter = new OleDbDataAdapter(sql, con))
{
DataSet ds = new DataSet();
adpter.Fill(ds);
return ds;
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 返回DataSet
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataSet GetDataSet(string sql,OleDbParameter par)
{
try
{
using (OleDbCommand com = new OleDbCommand(sql,con))
{
com.Parameters.Add(par);
using (OleDbDataAdapter adpter = new OleDbDataAdapter(com))
{
DataSet ds = new DataSet();
adpter.Fill(ds);
return ds;
}
}
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// 返回DataSet
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public DataSet GetDataSet(string sql, ParametersArray array)
{
try
{
OleDbCommand com = new OleDbCommand(sql, con);
SetParametersArray(ref com,array);
using (OleDbDataAdapter adpter = new OleDbDataAdapter(com))
{
DataSet ds = new DataSet();
adpter.Fill(ds);
return ds;
}
}
catch (Exception)
{
throw;
}
}
#endregion
}
/// <summary>
/// 一组Parameters对象
/// </summary>
public class ParametersArray
{
List<System.Data.OleDb.OleDbParameter> par = new List<System.Data.OleDb.OleDbParameter>();
/// <summary>
/// 添加新参数
/// </summary>
/// <param name="par"></param>
public void Add(System.Data.OleDb.OleDbParameter par)
{
this.par.Add(par);
}
/// <summary>
/// 获取全部
/// </summary>
/// <returns></returns>
public List<System.Data.OleDb.OleDbParameter> GetArray()
{
return this.par;
}
}
}