using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
namespace DAL
{
/// <summary>
/// Access数据库通用类
/// </summary>
public class OleDBHelper
{
/// <summary>
/// 创建连接数据库字符串
/// </summary>
private static string connectionString =
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0";
/// <summary>
/// 更新
/// </summary>
/// <returns>int</returns>
public static int UpdateSql(string sql)
{
OleDbConnection conn = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand(sql,conn);
try
{
conn.Open();
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 返回单一结果
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static object GetSingleResult(string sql)
{
OleDbConnection conn = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand(sql, conn);
try
{
conn.Open();
return cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 返回结果集
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static OleDbDataReader GetDataReader(string sql)
{
OleDbConnection conn = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand(sql, conn);
try
{
conn.Open();
return cmd.ExecuteReader();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 利用dataset获取数据集
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataSet GetDataSet(string sql)
{
OleDbConnection conn = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand(sql, conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
conn.Open();
da.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 将指定的excel导入到数据集中(重载)
/// </summary>
/// <param name="sql"></param>
/// <param name="path"></param>
/// <returns></returns>
public static DataSet GetDataSet(string sql,string path)
{
OleDbConnection conn = new OleDbConnection(string.Format(connectionString,path));
OleDbCommand cmd = new OleDbCommand(sql, conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
conn.Open();
da.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
}
}
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
namespace DAL
{
/// <summary>
/// Access数据库通用类
/// </summary>
public class OleDBHelper
{
/// <summary>
/// 创建连接数据库字符串
/// </summary>
private static string connectionString =
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0";
/// <summary>
/// 更新
/// </summary>
/// <returns>int</returns>
public static int UpdateSql(string sql)
{
OleDbConnection conn = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand(sql,conn);
try
{
conn.Open();
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 返回单一结果
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static object GetSingleResult(string sql)
{
OleDbConnection conn = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand(sql, conn);
try
{
conn.Open();
return cmd.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 返回结果集
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static OleDbDataReader GetDataReader(string sql)
{
OleDbConnection conn = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand(sql, conn);
try
{
conn.Open();
return cmd.ExecuteReader();
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 利用dataset获取数据集
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataSet GetDataSet(string sql)
{
OleDbConnection conn = new OleDbConnection(connectionString);
OleDbCommand cmd = new OleDbCommand(sql, conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
conn.Open();
da.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// 将指定的excel导入到数据集中(重载)
/// </summary>
/// <param name="sql"></param>
/// <param name="path"></param>
/// <returns></returns>
public static DataSet GetDataSet(string sql,string path)
{
OleDbConnection conn = new OleDbConnection(string.Format(connectionString,path));
OleDbCommand cmd = new OleDbCommand(sql, conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
conn.Open();
da.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
}
}