无助的时候,我曾读过N遍《金刚经》,讲到《金刚经》,我必须把爱因斯坦先拿来当我的档箭牌,不然,写这么个题目,我可能会被拍死:
“如果世界上有一个宗教不但不与科学相违,而且每一次的科学新发现都能够验证她的观点,这就是佛教。”《爱因斯坦文集》,第一卷。
“我不能设想真正的科学家会没有这样深挚的信仰。这情况可以用这样一个形象来比喻:科学没有宗教就象瘸子,宗教没有科学就象瞎子。”------------《爱因斯坦文集》,第三卷,商务印书馆,1979年,第182-183页。
我还要提到我们国家,有位工科出身的领导人,我印象中,他也曾有段时间,每天都读《金刚经》…
先引用一段吧:
所有一切众生之类,若卵生、若胎生、若湿生、若化生、若有色、若无色、若有想、若无想、若非有想非无想,我皆令入无余涅槃而灭度之。如是灭度无量无数无边众生,实无众生得灭度者。何以故?须菩提,若菩萨有我相、人相、众生相、寿者相,即非菩萨。
类是众生之类吗?若化生??若无想??若非有想非无想??
我不是个无知无欲的佛教徒,写这一段,我是非常非常伤感的,因为今天,我们就要把我们刚刚认识的,刚刚喜欢的,感觉已经有了很深的情感的几个类,几个象人一样可爱的类,给“灭度”掉。
请原谅我的任性,我不知道用这个词到底确切不确切,只是想这么用,就这么用了,原谅我的无知吧……
这几个类,早已让很多网友不耐烦了,它们是SqlConnection、SqlCommand、SqlDataReader…
让我们,慢慢地,把它们送走吧……
《十八相送》是越剧《梁山伯与祝英台》中的最精彩的片段,可惜,不会有多少人这么有耐心地倾听那悠悠的、凄美的爱情了….
那么,我们简化一下,分六步吧:
一相送,送到try…catch…finally结构中:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;
using WestGarden.Model;
namespace WestGarden.Web
{
public partial class Default1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
IList<CategoryInfo> catogories = new List<CategoryInfo>();
string connectionString = ConfigurationManager.ConnectionStrings["NetShopConnString"].ConnectionString;
string cmdText = "SELECT CategoryId, Name, Descn FROM Category";
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
try
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = cmdText;
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
CategoryInfo category = new CategoryInfo(rdr.GetString(0), rdr.GetString(1), rdr.GetString(2));
catogories.Add(category);
}
rdr.Close();
}
finally
{
conn.Close();
}
ddlCategories.DataSource = catogories;
ddlCategories.DataTextField = "Name";
ddlCategories.DataValueField = "CategoryId";
ddlCategories.DataBind();
}
}
}
二相送,送到using()结构中:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;
using WestGarden.Model;
namespace WestGarden.Web
{
public partial class Default2 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
IList<CategoryInfo> catogories = new List<CategoryInfo>();
string connectionString = ConfigurationManager.ConnectionStrings["NetShopConnString"].ConnectionString;
string cmdText = "SELECT CategoryId, Name, Descn FROM Category";
SqlCommand cmd = new SqlCommand();
//简单地说,using()结构等同于前面的try...finally结构,隐式关闭了conn。
using(SqlConnection conn = new SqlConnection(connectionString))
{
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = cmdText;
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
CategoryInfo category = new CategoryInfo(rdr.GetString(0), rdr.GetString(1), rdr.GetString(2));
catogories.Add(category);
}
rdr.Close();
}
ddlCategories.DataSource = catogories;
ddlCategories.DataTextField = "Name";
ddlCategories.DataValueField = "CategoryId";
ddlCategories.DataBind();
}
}
}
三相送,送到通用的数据库访问函数中:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;
using WestGarden.Model;
namespace WestGarden.Web
{
public partial class Default3 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
IList<CategoryInfo> catogories = new List<CategoryInfo>();
string connectionString = ConfigurationManager.ConnectionStrings["NetShopConnString"].ConnectionString;
string cmdText = "SELECT CategoryId, Name, Descn FROM Category";
SqlDataReader rdr = ExecuteReader(connectionString, CommandType.Text, cmdText);
while (rdr.Read())
{
CategoryInfo category = new CategoryInfo(rdr.GetString(0), rdr.GetString(1), rdr.GetString(2));
catogories.Add(category);
}
rdr.Close();
ddlCategories.DataSource = catogories;
ddlCategories.DataTextField = "Name";
ddlCategories.DataValueField = "CategoryId";
ddlCategories.DataBind();
}
public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
try
{
cmd.Connection = conn;
cmd.CommandType = cmdType;
cmd.CommandText = cmdText;
conn.Open();
//如果创建了 SqlDataReader 并将 CommandBehavior 设置为 CloseConnection,
//则关闭 SqlDataReader 会自动关闭此连接
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return rdr;
}
catch
{
conn.Close();
throw;
}
//finally
//{
// conn.Close();
//}
}
}
}
这个通用数据库访问函数可以进一步完善如下:
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections.Generic;
using WestGarden.Model;
namespace WestGarden.Web
{
public partial class Default4 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
IList<CategoryInfo> catogories = new List<CategoryInfo>();
string connectionString = ConfigurationManager.ConnectionStrings["NetShopConnString"].ConnectionString;
string cmdText = "SELECT CategoryId, Name, Descn FROM Category";
SqlDataReader rdr = ExecuteReader(connectionString, CommandType.Text, cmdText,null);
while (rdr.Read())
{
CategoryInfo category = new CategoryInfo(rdr.GetString(0), rdr.GetString(1), rdr.GetString(2));
catogories.Add(category);
}
rdr.Close();
ddlCategories.DataSource = catogories;
ddlCategories.DataTextField = "Name";
ddlCategories.DataValueField = "CategoryId";
ddlCategories.DataBind();
}
public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
try
{
//cmd.Connection = conn;
//cmd.CommandType = cmdType;
//cmd.CommandText = cmdText;
//conn.Open();
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
//如果创建了 SqlDataReader 并将 CommandBehavior 设置为 CloseConnection,
//则关闭 SqlDataReader 会自动关闭此连接。
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
//finally
//{
// conn.Close();
//}
}
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
if (conn.State != ConnectionState.Open)
conn.Open();
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
cmd.Transaction = trans;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
cmd.Parameters.Add(parm);
}
}
}
}
因为重点在过程,在结构,代码都比较简单,唯一值得一提的是SqlConnection的关闭问题,在最后比较完善的数据库访问函数中(这是SQLHelper中的源代码),没有使用using()结构,也没有显示关闭,主要原因是调用ExecuteReader方法时,使用了参数 CommandBehavior 并将其设置为 CloseConnection:
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
根据MSDN的说法:如果创建了 SqlDataReader 并将 CommandBehavior 设置为 CloseConnection,则关闭 SqlDataReader 会自动关闭此连接。
参考网址:http://msdn.microsoft.com/zh-cn/library/y6wy5a0f(v=vs.80).aspx