public SqlDataReader RunProcedure( string storedProcName, IDataParameter [] parameters)
{
SqlDataReader returnReader; try
{
this.Connection.Open();
SqlCommand command = this.BuildQueryCommand(storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure; returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception exc)
{
throw exc;
}
return returnReader;
}
CommandBehavior.CloseConnection 数据库的连接会随着DataReader对象的关闭而关闭,这样就可以调用完这个方法,用完reader后调用reader.Close(),现在问题是网站访问量非常大,会不会出现来不及关闭的现像?不知道大家是如何在数据层返回SqlDataReader对像
{
SqlDataReader returnReader; try
{
this.Connection.Open();
SqlCommand command = this.BuildQueryCommand(storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure; returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception exc)
{
throw exc;
}
return returnReader;
}
CommandBehavior.CloseConnection 数据库的连接会随着DataReader对象的关闭而关闭,这样就可以调用完这个方法,用完reader后调用reader.Close(),现在问题是网站访问量非常大,会不会出现来不及关闭的现像?不知道大家是如何在数据层返回SqlDataReader对像
一般用对象或对象list作为返回
{
public DBManager()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
//拼写连接字符串
private string conString = "server=.;database=BookShop;uid=sa;pwd=;";
//创建数据库连接
public SqlConnection CreateConnection()
{
SqlConnection conn = new SqlConnection();
conn.ConnectionString = conString;
conn.Open();
return conn;
} //执行增,删,改操作
public int ExecuteSql(string sql, params SqlParameter[] prams)
{
SqlConnection conn = CreateConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
cmd.Parameters.AddRange(prams);
return cmd.ExecuteNonQuery();
} //执行查询,返回数据集
public DataTable ExecuteQuery(string sql)
{
SqlConnection conn = CreateConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
DataTable dt = new DataTable();
SqlDataAdapter dap = new SqlDataAdapter(cmd);
dap.Fill(dt);
return dt;
} //执行查询,返回SqlDataReader
public SqlDataReader ExecuteReader(string sql)
{
SqlConnection conn = CreateConnection();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
return cmd.ExecuteReader();
}
}这个是数据层访问类的死油层,主要是负责各种数据库的语句,里面第二个方法是吧datareader包装成list
public class UserDAL:DBManager
{
public UserDAL()
{
//
// TODO: 在此处添加构造函数逻辑
//
} public static int AddUser(User user)
{
string sql = "insert into Users values " +
"(@UserId,@LoginId,@LoginPwd,@Name," +
"@Address,@Phone,@Mail,@RoleId,@StateId)";
return ExecuteSql(sql, new SqlParameter[]
{
new SqlParameter("@UserId",u.UserId),
new SqlParameter("@LoginId",u.LoginId),
new SqlParameter("@LoginPwd",u.LoginPwd),
new SqlParameter("@Name",u.Name),
new SqlParameter("@Address",u.Address),
new SqlParameter("@Phone",u.Phone),
new SqlParameter("@Mail",u.Mail),
new SqlParameter("@RoleId",u.RoleId),
new SqlParameter("@StateId",u.StateId)
});
} //查询用户信息,返回集合
public static List<Users> getUsers(params Condition[] conds)
{
string sql = "select * from Users where 1=1";
foreach (Condition t in conds)
sql += string.Format(" and {0}='{1}'", t.fidName, t.val);
SqlDataReader reader = ExecReader(sql);
List<Users> res = new List<Users>();
//结果集必须逐行逐列读取
while (reader.Read())
{
Users t = new Users();
t.UserId = Convert.ToInt32(reader["UserId"]);
t.LoginId= reader["LoginId"].ToString();
t.LoginPwd = reader["LoginPwd"].ToString();
t.Name = reader["Name"].ToString();
t.Address = reader["Address"].ToString();
t.Phone = reader["Phone"].ToString();
t.Mail = reader["Mail"].ToString();
t.RoleId= Convert.ToInt32(reader["RoleId"]);
t.StateId = Convert.ToInt32(reader["StateId"]);
res.Add(t);
}
return res;
}
}
其中的Condition[]是做一个结构体方便传值的然后在BLL里面做一下业务逻辑处理就OK了
[OperationContract]
public UserData getJghr(String id)
{
UserData data = new JghrData();
sql = ......;
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, sql))
{
if (reader.Read())
{
data.ID = reader["id"].tostring();
........
}
}
return data;
}
呵呵,可以参考看看
{
try
{
this.Connection.Open();
SqlCommand command = this.BuildQueryCommand(storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure;
using (SqlDataReader returnReader =new SqlDataReader())
{
returnReader = command.ExecuteReader();
}
}
catch (Exception exc)
{
throw exc;
}
return returnReader;
}
用using() {...}括起来
public SqlDataReader GetDataReader(String SqlString)
{
this.Connection.Open();
SqlCommand cmd = new SqlCommand(SqlString,Connection);
return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}这样不是更简单
public static SqlDataReader returnReader(string mySql, SqlParameter[] parameters)
{
SqlDataReader myRead = null;
SqlConnection myConn = new SqlConnection(strConn); using (SqlCommand myCmd = new SqlCommand(mySql, myConn))
{
try
{
myCmd.Parameters.AddRange(parameters); if (myConn.State == ConnectionState.Closed) myConn.Open(); myRead = myCmd.ExecuteReader(CommandBehavior.CloseConnection); return myRead;
}
catch
{
if (myConn.State == ConnectionState.Open) myConn.Close();
return myRead;
}
}
}
随便写一下~~~你自己完善~
比如有个用户类
Class Users
{
string Name;
string Password;
}public static List <Users> getUsers(params Condition[] conds)
{
string sql = "select * from Users where 1=1";
foreach (Condition t in conds)
sql += string.Format(" and {0}='{1}'", t.fidName, t.val);
SqlDataReader reader = ExecReader(sql);
List <Users> res = new List <Users>();
//结果集必须逐行逐列读取
while (reader.Read())
{
Users user = new Users();
user.Name = reader["Name"].ToString();
user.Password = reader["Password"].ToString();
res.Add(t);
}
return res;
} GridView.DataSource = List <Users>;List <Users>; 这里用到泛型
什么是泛型呢:数组 加强版是 集合
集合 加强版是 泛型3楼回答的很好了~~~只是怕你看不懂 所以我就解释一下 随便提到怎么了解泛型 我也是刚刚接触集合 泛型 不久~
希望对你有帮助
上面的方法确实不错 我们做项目就这样做
/// 公有方法,获取数据,返回一个DataReader
/// </summary>
/// <param name="Sqlstring">SQL语句</param>
/// <returns>DataReader</returns>
public SqlDataReader GetDataReader(string Sqlstring)
{
Open();
SqlCommand cmd = new SqlCommand(Sqlstring,conn);
return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); //利用SqlCommand里的ExecuteReader方法查询数据库,并返回DataReader
}
会的!这样的程序就是会在系统运营到最关键的时刻频繁出现“连接池已满”的异常。在你的RunProcedure结束时返回DataTable或者强类型的对象集合,要确保已经关闭了Connection,例如不是使用什么this.Connection.Open(),而是通过连接池创建新的(逻辑而并非物理)连接:DataTable result=new DataTable();
using(SqlConnection conn=this.CreateAndOpenNewConnection())
{
...... //读取DataReader并写入result
return result;
}
返回DataTable或者List<T>的类型(T为具体的业务模型class),这样来保证及时Close逻辑连接。
就像楼上说的用DataTable,或List<T>.
#region 委托
/// <summary>
/// 委托:在using里面执行绑定数据操作,绑定后可以及时释放IDataReader资源
/// </summary>
/// <param name="dr">IDataReader数据源</param>
public delegate void IDataReaderHandler(SqlDataReader dr);
#endregionpublic void RunProcedure( string storedProcName, IDataParameter [] parameters, IDataReaderHandler handler)
{
try
{
this.Connection.Open();
SqlCommand command = this.BuildQueryCommand(storedProcName, parameters);
command.CommandType = CommandType.StoredProcedure; using(SqlDataReader sdr=command.ExecuteReader(CommandBehavior.CloseConnection))
{
if(handler!=null)
{
handler(sdr);
}
} returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception exc)
{
throw exc;
}
}在业务层写个给委托调用的方法就可以了.