网上购物系统（Task004）——通用数据库访问函数

源代码：13033480群共享

一、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 * 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 * 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 * 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; 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 * 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); } } } }

五、使用Reapter显示分类名称

1、窗体代码：

<asp:Repeater ID="repCategories" runat="server"> <HeaderTemplate> <table cellspacing="0" border="0" style="border-collapse: collapse;"> </HeaderTemplate> <ItemTemplate> <tr> <td> <asp:HyperLink runat="server" ID="lnkCategory" NavigateUrl='<%# string.Format("~/Items.aspx?page=0&categoryId={0}", Eval("CategoryId")) %>' Text='<%# Eval("Name") %>' /><asp:HiddenField runat="server" ID="hidCategoryId" Value='<%# Eval("CategoryId") %>' /> </td> </tr> </ItemTemplate> <FooterTemplate> </table> </FooterTemplate> </asp:Repeater>

2、代码页绑定代码：

repCategories.DataSource = catogories; repCategories.DataBind();

版权所有©2012,西园电脑工作室.欢迎转载,转载请注明出处.更多文章请参阅博客http://blog.csdn.com/yousuosi