数据库操作类public class DBFun
{
protected static OleDbConnection conn = new OleDbConnection();
protected static OleDbCommand comm = new OleDbCommand(); public DBFun()
{
//
//TODO: 在此处添加构造函数逻辑
//
} /// <summary>
/// 打开数据库
/// </summary>
private static void openConnection()
{
if (conn.State.Equals(ConnectionState.Closed))
{
conn.ConnectionString = "Provider=Microsoft.Jet.OlEDB.4.0;Data Source=" +
System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.ConnectionStrings["DBPath"].ToString());
comm.Connection = conn;
try
{
conn.Open();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
} /// <summary>
/// 关闭数据库
/// </summary>
private static void closeConnection()
{
if (conn.State.Equals(ConnectionState.Open))
{
conn.Close();
conn.Dispose();
comm.Dispose();
}
} /// <summary>
/// 执行sql语句
/// </summary>
/// <param name="sqlstr">要执行的sql语句</param>
public static void ExecuteSql(string sqlstr)
{
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
comm.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
} /// <summary>
/// 执行更新
/// </summary>
/// <param name="sqlstr">传入要更新的SQL语句</param>
/// <returns></returns>
public static bool ExecuteUpdate(string sqlstr)
{
int isUpdateOk = 0;
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
isUpdateOk = Convert.ToInt32(comm.ExecuteNonQuery());
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
if (isUpdateOk > 0)
{
return true;
}
else
{
return false;
} } /// <summary>
/// 查询数据,取得首行
/// </summary>
/// <param name="sqlstr">要查询的sql语句</param>
/// <returns>返回第一行数据</returns>
public static DataRow GetDataRow(string sqlstr)
{
try
{
openConnection();
OleDbDataAdapter da = new OleDbDataAdapter(sqlstr, conn);
DataSet ds = new DataSet();
da.Fill(ds);
if (ds.Tables[0].Rows.Count != 0)
return ds.Tables[0].Rows[0];
else
return null;
}
catch
{
return null;
}
finally
{
closeConnection();
}
} /// <summary>
/// /查询数据,取得数据视图
/// </summary>
/// <param name="sqlstr">要查询的sql语句</param>
/// <returns>取得数据视图</returns>
public static DataView GetDataDiew(string sqlstr)
{
try
{
openConnection();
OleDbDataAdapter da = new OleDbDataAdapter(sqlstr, conn);
DataSet ds = new DataSet();
da.Fill(ds);
return ds.Tables[0].DefaultView;
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
} /// <summary>
/// 用SQL语句填充下拉列表
/// </summary>
/// <param name="ddlist">DropDownList</param>
/// <param name="sqlStr">填充的数据源</param>
public static void FillDwList(DropDownList ddlist, string sqlStr)
{
try
{
DataView dv = GetDataDiew(sqlStr);
ddlist.DataValueField = dv.Table.Columns[0].Caption.ToString();
ddlist.DataTextField = dv.Table.Columns[1].Caption.ToString();
ddlist.DataSource = dv;
ddlist.DataBind();
}
catch(Exception e)
{
throw new Exception(e.Message);
}
} /// <summary>
/// 执行Sql查询语句并返回第一行的第一条记录,返回值为object 使用时需要拆箱操作 -> Unbox
/// </summary>
/// <param name="sqlStr">传入的Sql语句</param>
/// <returns>object 返回值 </returns>
public static object ExecuteScalar(string sqlStr)
{
object obj = new object();
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlStr;
obj = comm.ExecuteScalar();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
return obj;
} /// <summary>
/// 返回指定Sql语句的SqlDataReader,请注意,在使用后请关闭本对象,同时将自动调用closeConnection()来关闭数据库连接
/// 方法关闭数据库连接
/// </summary>
/// <param name="sqlstr">传入的Sql语句</param>
/// <returns>SqlDataReader对象</returns>
public static OleDbDataReader DataReader(string sqlstr)
{
OleDbDataReader dr = null;
try
{
openConnection();
comm.CommandText = sqlstr;
comm.CommandType = CommandType.Text;
dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
}
catch
{
dr.Close();
}
finally
{
closeConnection();
}
return dr;
} /// <summary>
/// 返回指定Sql语句的DataTable
/// </summary>
/// <param name="sqlstr">传入的Sql语句</param>
/// <returns>DataTable</returns>
public static DataTable GetDataTable(string sqlStr)
{
DataTable dt=new DataTable();
try
{
openConnection();
OleDbDataAdapter da = new OleDbDataAdapter(sqlStr, conn);
da.Fill(dt);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
return dt;
} /// <summary>
/// 返回dataset
/// </summary>
/// <param name="sqlStr"></param>
/// <returns></returns>
public static DataSet GetDataSet(string sqlStr)
{
DataSet ds = new DataSet();
try
{
openConnection();
OleDbDataAdapter da = new OleDbDataAdapter(sqlStr, conn);
da.Fill(ds);
return ds;
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
} /// <summary>
/// 返回dataset
/// </summary>
/// <param name="sqlStr"></param>
/// <param name="table"></param>
/// <returns></returns>
public static DataSet GetDataSet(string sqlStr,string table)
{
DataSet ds = new DataSet();
try
{
openConnection();
OleDbDataAdapter da = new OleDbDataAdapter(sqlStr, conn);
da.Fill(ds,table);
return ds;
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
} } /// <summary>
/// 绑定分页aspnetpage控件
/// </summary>
/// <param name="Page">aspnetPage控件</param>
/// <param name="sql">要查询的sql语句</param>
/// <param name="table"></param>
/// <returns>返回DataSet</returns>
public static DataSet Pager(AspNetPager Page, string sql, string table)
{
try
{
openConnection();
OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds, Page.PageSize * (Page.CurrentPageIndex - 1), Page.PageSize, table);
return ds;
}
catch(Exception e)
{
throw new Exception(e.Message, e);
}
finally
{
closeConnection();
}
} /// <summary>
/// 记录数
/// </summary>
/// <param name="sql"></param>
/// <returns>返回记录数</returns>
public static int RecordCount(string sql)
{
try
{
openConnection();
OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
return ds.Tables[0].Rows.Count;
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
}
}
因为数据有点多,打开速度有点慢,每次一个页面还没打开就去打开另外一个页面,经常出现“已有打开的与此命令相关联的 DataReader,必须首先将它关闭。”
“ExecuteReader 要求已打开且可用的连接。连接的当前状态为打开。 ”等数据库打开未关闭错误。
{
protected static OleDbConnection conn = new OleDbConnection();
protected static OleDbCommand comm = new OleDbCommand(); public DBFun()
{
//
//TODO: 在此处添加构造函数逻辑
//
} /// <summary>
/// 打开数据库
/// </summary>
private static void openConnection()
{
if (conn.State.Equals(ConnectionState.Closed))
{
conn.ConnectionString = "Provider=Microsoft.Jet.OlEDB.4.0;Data Source=" +
System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.ConnectionStrings["DBPath"].ToString());
comm.Connection = conn;
try
{
conn.Open();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
}
} /// <summary>
/// 关闭数据库
/// </summary>
private static void closeConnection()
{
if (conn.State.Equals(ConnectionState.Open))
{
conn.Close();
conn.Dispose();
comm.Dispose();
}
} /// <summary>
/// 执行sql语句
/// </summary>
/// <param name="sqlstr">要执行的sql语句</param>
public static void ExecuteSql(string sqlstr)
{
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
comm.ExecuteNonQuery();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
} /// <summary>
/// 执行更新
/// </summary>
/// <param name="sqlstr">传入要更新的SQL语句</param>
/// <returns></returns>
public static bool ExecuteUpdate(string sqlstr)
{
int isUpdateOk = 0;
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlstr;
isUpdateOk = Convert.ToInt32(comm.ExecuteNonQuery());
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
if (isUpdateOk > 0)
{
return true;
}
else
{
return false;
} } /// <summary>
/// 查询数据,取得首行
/// </summary>
/// <param name="sqlstr">要查询的sql语句</param>
/// <returns>返回第一行数据</returns>
public static DataRow GetDataRow(string sqlstr)
{
try
{
openConnection();
OleDbDataAdapter da = new OleDbDataAdapter(sqlstr, conn);
DataSet ds = new DataSet();
da.Fill(ds);
if (ds.Tables[0].Rows.Count != 0)
return ds.Tables[0].Rows[0];
else
return null;
}
catch
{
return null;
}
finally
{
closeConnection();
}
} /// <summary>
/// /查询数据,取得数据视图
/// </summary>
/// <param name="sqlstr">要查询的sql语句</param>
/// <returns>取得数据视图</returns>
public static DataView GetDataDiew(string sqlstr)
{
try
{
openConnection();
OleDbDataAdapter da = new OleDbDataAdapter(sqlstr, conn);
DataSet ds = new DataSet();
da.Fill(ds);
return ds.Tables[0].DefaultView;
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
} /// <summary>
/// 用SQL语句填充下拉列表
/// </summary>
/// <param name="ddlist">DropDownList</param>
/// <param name="sqlStr">填充的数据源</param>
public static void FillDwList(DropDownList ddlist, string sqlStr)
{
try
{
DataView dv = GetDataDiew(sqlStr);
ddlist.DataValueField = dv.Table.Columns[0].Caption.ToString();
ddlist.DataTextField = dv.Table.Columns[1].Caption.ToString();
ddlist.DataSource = dv;
ddlist.DataBind();
}
catch(Exception e)
{
throw new Exception(e.Message);
}
} /// <summary>
/// 执行Sql查询语句并返回第一行的第一条记录,返回值为object 使用时需要拆箱操作 -> Unbox
/// </summary>
/// <param name="sqlStr">传入的Sql语句</param>
/// <returns>object 返回值 </returns>
public static object ExecuteScalar(string sqlStr)
{
object obj = new object();
try
{
openConnection();
comm.CommandType = CommandType.Text;
comm.CommandText = sqlStr;
obj = comm.ExecuteScalar();
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
return obj;
} /// <summary>
/// 返回指定Sql语句的SqlDataReader,请注意,在使用后请关闭本对象,同时将自动调用closeConnection()来关闭数据库连接
/// 方法关闭数据库连接
/// </summary>
/// <param name="sqlstr">传入的Sql语句</param>
/// <returns>SqlDataReader对象</returns>
public static OleDbDataReader DataReader(string sqlstr)
{
OleDbDataReader dr = null;
try
{
openConnection();
comm.CommandText = sqlstr;
comm.CommandType = CommandType.Text;
dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
}
catch
{
dr.Close();
}
finally
{
closeConnection();
}
return dr;
} /// <summary>
/// 返回指定Sql语句的DataTable
/// </summary>
/// <param name="sqlstr">传入的Sql语句</param>
/// <returns>DataTable</returns>
public static DataTable GetDataTable(string sqlStr)
{
DataTable dt=new DataTable();
try
{
openConnection();
OleDbDataAdapter da = new OleDbDataAdapter(sqlStr, conn);
da.Fill(dt);
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
return dt;
} /// <summary>
/// 返回dataset
/// </summary>
/// <param name="sqlStr"></param>
/// <returns></returns>
public static DataSet GetDataSet(string sqlStr)
{
DataSet ds = new DataSet();
try
{
openConnection();
OleDbDataAdapter da = new OleDbDataAdapter(sqlStr, conn);
da.Fill(ds);
return ds;
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
} /// <summary>
/// 返回dataset
/// </summary>
/// <param name="sqlStr"></param>
/// <param name="table"></param>
/// <returns></returns>
public static DataSet GetDataSet(string sqlStr,string table)
{
DataSet ds = new DataSet();
try
{
openConnection();
OleDbDataAdapter da = new OleDbDataAdapter(sqlStr, conn);
da.Fill(ds,table);
return ds;
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
} } /// <summary>
/// 绑定分页aspnetpage控件
/// </summary>
/// <param name="Page">aspnetPage控件</param>
/// <param name="sql">要查询的sql语句</param>
/// <param name="table"></param>
/// <returns>返回DataSet</returns>
public static DataSet Pager(AspNetPager Page, string sql, string table)
{
try
{
openConnection();
OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds, Page.PageSize * (Page.CurrentPageIndex - 1), Page.PageSize, table);
return ds;
}
catch(Exception e)
{
throw new Exception(e.Message, e);
}
finally
{
closeConnection();
}
} /// <summary>
/// 记录数
/// </summary>
/// <param name="sql"></param>
/// <returns>返回记录数</returns>
public static int RecordCount(string sql)
{
try
{
openConnection();
OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
DataSet ds = new DataSet();
da.Fill(ds);
return ds.Tables[0].Rows.Count;
}
catch (Exception e)
{
throw new Exception(e.Message);
}
finally
{
closeConnection();
}
}
}
因为数据有点多,打开速度有点慢,每次一个页面还没打开就去打开另外一个页面,经常出现“已有打开的与此命令相关联的 DataReader,必须首先将它关闭。”
“ExecuteReader 要求已打开且可用的连接。连接的当前状态为打开。 ”等数据库打开未关闭错误。
如果到你的while中套用了其他的方法的话。报这个错是很正常的,有必要试试datatable
这个方法是有问题的。
因为你这个方法是要返回一个OleDbDataReader。
当你方法执行完成之后,连接对象已经关闭。那么这个OleDbDataReader返回出去已经没有任何意义。
OleDbDataReader.Read()必须要保证连接对象是处于打开状态。
关闭就可以了
{ private DBFun()
{ }
private static object obj=new object();
private static DBFun db;
public static void CreateDB()
{
if(db==null)
{
lock(obj)
{
db=new DBFun();
}
}
return db;
}
}用单例模式试一下
protected OleDbConnection conn = new OleDbConnection();
protected OleDbCommand comm = new OleDbCommand();
----
上面的类属性不能用static修饰
using (SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=Test;User ID=sa;Password="))
{
SqlCommand cmd = new SqlCommand("select 2000 as c", conn);
conn.Open();
SqlDataReader dr = cmd.ExecuteReader();
dr.Read(); }