using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;namespace Food.DAL
{
public static class DBHelper
{
private static SqlConnection connection;
private static void OpenConnection()
{
try
{
string connectionString = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
if (connection == null)
{
connection = new SqlConnection(connectionString);
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Broken)
{
connection.Close();
connection.Open();
}
}
catch
{
throw new ApplicationException("数据库连接失败!");
}
} private static void CloseConnection()
{
if (connection != null)
{
connection.Close();
}
} public static int ExecuteCommand(string safeSql)
{
OpenConnection();
SqlCommand cmd = new SqlCommand(safeSql, connection);
int result = cmd.ExecuteNonQuery();
CloseConnection();
return result;
} public static int ExecuteCommand(string sql, params SqlParameter[] values)
{
try
{
OpenConnection();
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddRange(values);
return cmd.ExecuteNonQuery();
}
finally
{
CloseConnection();
}
} public static int GetScalar(string safeSql)
{
try
{
OpenConnection();
SqlCommand cmd = new SqlCommand(safeSql, connection);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
finally
{
CloseConnection();
}
} public static int GetScalar(string sql, params SqlParameter[] values)
{
try
{
OpenConnection();
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddRange(values);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
finally
{
CloseConnection();
}
} /// <summary>
/// 查看新的数量
/// </summary>
/// <param name="sql"></param>
/// <param name="values"></param>
/// <returns></returns>
public static object GetScalarWithNewCon(string sql, params SqlParameter[] values)
{
using (SqlConnection con = new SqlConnection())
{
string connectionString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
con.ConnectionString = connectionString;
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddRange(values);
return cmd.ExecuteScalar();
}
} public static string GetScalarString(string sql)
{
try
{
OpenConnection();
SqlCommand cmd = new SqlCommand(sql, connection);
string result = cmd.ExecuteScalar().ToString();
return result;
}
finally
{
CloseConnection();
}
} public static SqlDataReader GetReader(string safeSql)
{
OpenConnection();
SqlCommand cmd = new SqlCommand(safeSql, connection);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
} public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
{
OpenConnection();
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
} public static DataTable GetTable(string safeSql)
{
try
{
OpenConnection();
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(safeSql, connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
finally
{
CloseConnection();
}
}
public static DataTable GetTable(string sql, params SqlParameter[] values)
{
try
{
OpenConnection();
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
finally
{
CloseConnection();
}
} public static DataSet GetDataSet(string sql)
{
try
{
OpenConnection();
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds;
}
finally
{
CloseConnection();
}
} public static DataSet GetDataSet(string sql, params SqlParameter[] values)
{
try
{
OpenConnection();
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds;
}
finally
{
CloseConnection();
}
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
{ string connectionString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
SqlConnection connection = new SqlConnection(connectionString);
SqlDataReader returnReader;
connection.Open();
SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure;
returnReader = command.ExecuteReader();
return returnReader;
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static DataTable RunProcedureTable(string storedProcName, IDataParameter[] parameters)
{
DataSet dsSet = new DataSet();
string connectionString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
SqlConnection cnn = new SqlConnection(connectionString);
SqlDataAdapter sqlDa = new SqlDataAdapter(storedProcName, cnn);
sqlDa.SelectCommand.CommandType = CommandType.StoredProcedure;
sqlDa.SelectCommand.Parameters.AddRange(parameters);
sqlDa.Fill(dsSet); return dsSet.Tables[0];
}
/// <summary>
/// 为存储过程添加参数
/// </summary>
/// <param name="connection"></param>
/// <param name="storedProcName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand(storedProcName, connection);
command.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
return command;
} }
}-------------这是网站dal中的数据库公用类
一个人测试的时候,不会出现异常
发布到网上会出现,n个人浏览时 会出现‘ read关闭时读取无效的异常’我在调用的时候这样写:Dbhelper.GetReader(string safeSql)我想问的是,这个问题跟这个类是静态有关系么?
是不是调用dbhelper的时候,全部用new就行了?
public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
{
OpenConnection();
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader(); return reader;
}
这里使用using,出现的错误更多,请大侠们帮忙了
由于页面好多,请有识之士帮小弟这个忙,谢谢了!
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;namespace Food.DAL
{
public static class DBHelper
{
private static SqlConnection connection;
private static void OpenConnection()
{
try
{
string connectionString = ConfigurationManager.ConnectionStrings["constring"].ConnectionString;
if (connection == null)
{
connection = new SqlConnection(connectionString);
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Broken)
{
connection.Close();
connection.Open();
}
}
catch
{
throw new ApplicationException("数据库连接失败!");
}
} private static void CloseConnection()
{
if (connection != null)
{
connection.Close();
}
} public static int ExecuteCommand(string safeSql)
{
OpenConnection();
SqlCommand cmd = new SqlCommand(safeSql, connection);
int result = cmd.ExecuteNonQuery();
CloseConnection();
return result;
} public static int ExecuteCommand(string sql, params SqlParameter[] values)
{
try
{
OpenConnection();
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddRange(values);
return cmd.ExecuteNonQuery();
}
finally
{
CloseConnection();
}
} public static int GetScalar(string safeSql)
{
try
{
OpenConnection();
SqlCommand cmd = new SqlCommand(safeSql, connection);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
finally
{
CloseConnection();
}
} public static int GetScalar(string sql, params SqlParameter[] values)
{
try
{
OpenConnection();
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddRange(values);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
finally
{
CloseConnection();
}
} /// <summary>
/// 查看新的数量
/// </summary>
/// <param name="sql"></param>
/// <param name="values"></param>
/// <returns></returns>
public static object GetScalarWithNewCon(string sql, params SqlParameter[] values)
{
using (SqlConnection con = new SqlConnection())
{
string connectionString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
con.ConnectionString = connectionString;
con.Open();
SqlCommand cmd = new SqlCommand(sql, con);
cmd.Parameters.AddRange(values);
return cmd.ExecuteScalar();
}
} public static string GetScalarString(string sql)
{
try
{
OpenConnection();
SqlCommand cmd = new SqlCommand(sql, connection);
string result = cmd.ExecuteScalar().ToString();
return result;
}
finally
{
CloseConnection();
}
} public static SqlDataReader GetReader(string safeSql)
{
OpenConnection();
SqlCommand cmd = new SqlCommand(safeSql, connection);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
} public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
{
OpenConnection();
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
} public static DataTable GetTable(string safeSql)
{
try
{
OpenConnection();
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(safeSql, connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
finally
{
CloseConnection();
}
}
public static DataTable GetTable(string sql, params SqlParameter[] values)
{
try
{
OpenConnection();
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
finally
{
CloseConnection();
}
} public static DataSet GetDataSet(string sql)
{
try
{
OpenConnection();
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds;
}
finally
{
CloseConnection();
}
} public static DataSet GetDataSet(string sql, params SqlParameter[] values)
{
try
{
OpenConnection();
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds;
}
finally
{
CloseConnection();
}
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters)
{ string connectionString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
SqlConnection connection = new SqlConnection(connectionString);
SqlDataReader returnReader;
connection.Open();
SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure;
returnReader = command.ExecuteReader();
return returnReader;
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="storedProcName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static DataTable RunProcedureTable(string storedProcName, IDataParameter[] parameters)
{
DataSet dsSet = new DataSet();
string connectionString = ConfigurationManager.ConnectionStrings["conString"].ConnectionString;
SqlConnection cnn = new SqlConnection(connectionString);
SqlDataAdapter sqlDa = new SqlDataAdapter(storedProcName, cnn);
sqlDa.SelectCommand.CommandType = CommandType.StoredProcedure;
sqlDa.SelectCommand.Parameters.AddRange(parameters);
sqlDa.Fill(dsSet); return dsSet.Tables[0];
}
/// <summary>
/// 为存储过程添加参数
/// </summary>
/// <param name="connection"></param>
/// <param name="storedProcName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand(storedProcName, connection);
command.CommandType = CommandType.StoredProcedure;
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
return command;
} }
}-------------这是网站dal中的数据库公用类
一个人测试的时候,不会出现异常
发布到网上会出现,n个人浏览时 会出现‘ read关闭时读取无效的异常’我在调用的时候这样写:Dbhelper.GetReader(string safeSql)我想问的是,这个问题跟这个类是静态有关系么?
是不是调用dbhelper的时候,全部用new就行了?
public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
{
OpenConnection();
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader(); return reader;
}
这里使用using,出现的错误更多,请大侠们帮忙了
由于页面好多,请有识之士帮小弟这个忙,谢谢了!
应该怎么设计啊?是不是用到这个类的时候使用:
dbhelper db=new dahelper();
db.getreader();