c#操作access数据库类
public class AccessConnect
{
private static string OleDbConnectionString = @"provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\App_Data\Database.mdb"; //数据库连接
/// <summary>
/// 初始化类 使用指定的连接字符串
/// </summary>
/// <param name="connectionString">连接字符串</param>
public AccessConnect(string connectionString)
{
OleDbConnectionString = connectionString;
}
/// <summary>
/// 打开数据库连接
/// </summary>
/// <param name="cnn">数据库连接对象</param>
private static void Open(OleDbConnection cnn)
{
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
}
}
/// <summary>
/// 关闭数据库连接
/// </summary>
/// <param name="cnn">数据库连接对象</param>
private static void Close(OleDbConnection cnn)
{
if (cnn != null)
{
if (cnn.State == ConnectionState.Open)
{
cnn.Close();
}
cnn.Dispose();
}
}
/// <summary>
/// 查询一个结果
/// </summary>
/// <param name="strSql">sql语句</param>
/// <param name="pms">参数</param>
/// <returns>返回结果</returns>
public static object ExecuteScalar(string strSql, params OleDbParameter[] pms)
{
OleDbConnection cnn = new OleDbConnection(OleDbConnectionString);
OleDbCommand cmd = new OleDbCommand();
try
{
Open(cnn);
cmd = new OleDbCommand(strSql, cnn);
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
return cmd.ExecuteScalar();
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show(e.Message);
}
finally
{
cmd.Dispose();
Close(cnn);
}
return null;
}
/// <summary>
/// 执行查询语句(返回OleDbDataReader)
/// </summary>
/// <param name="strSql">sql语句</param>
/// <param name="pms">参数</param>
/// <returns>OleDbDataReader</returns>
public static OleDbDataReader ExecuteReader(string strSql, params OleDbParameter[] pms)
{
OleDbConnection cnn = new OleDbConnection(OleDbConnectionString);
OleDbCommand cmd = new OleDbCommand();
try
{
Open(cnn);
cmd = new OleDbCommand(strSql, cnn);
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
}
}
/// <summary>
/// 查询(DataSet)
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns>查询结果:DataSet</returns>
/// foreach (DataRow item in dt.Rows){ }
public static DataTable GetDataTable(string strSql, params OleDbParameter[] pms)
{
OleDbConnection cnn = new OleDbConnection(OleDbConnectionString);
OleDbDataAdapter sda = new OleDbDataAdapter();
try
{
Open(cnn);
sda = new OleDbDataAdapter(strSql, cnn);
if (pms != null)
{
sda.SelectCommand.Parameters.AddRange(pms);
}
DataTable dt = new DataTable();
sda.Fill(dt);
return dt;
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show(e.Message);
return null;
}
finally
{
sda.Dispose();
Close(cnn);
}
}
/// <summary>
/// 增加,删除,修改
/// </summary>
/// <param name="strSql">sql语句</param>
/// <param name="pms">参数</param>
/// <returns>返回受影响的行数。</returns>
public static int ExecuteNonQuery(string strSql, params OleDbParameter[] pms)
{
OleDbConnection cnn = new OleDbConnection(OleDbConnectionString);
OleDbCommand cmd = new OleDbCommand();
try
{
Open(cnn);
cmd = new OleDbCommand(strSql, cnn);
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
return cmd.ExecuteNonQuery();
}
catch (Exception e)
{
System.Windows.Forms.MessageBox.Show(e.Message);
return 0;
}
finally
{
cmd.Dispose();
Close(cnn);
}
}
}