网上购物系统（Task003）——通用数据库访问函数集SqlHelper类

六、代码页中的类sqlhelper

[csharp]  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 default6 : 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"; 
 
            //使用静态类，可直接调用，不需要new。  
            sqldatareader rdr = sqlhelper.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(); 
 
            repcategories.datasource = catogories; 
            repcategories.databind(); 
        } 
    } 
 
    //这个类是静态的，应用程序启动时，自动驻留在内存中，所建连接，在使用过程中，  
    //也只是close()，放回到连接池中。并没有注销dispose()，可以下次连接时，快速使用。  
    public abstract class sqlhelper 
    { 
        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); 
            } 
        } 
    } 
} 
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 default6 : 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";

            //使用静态类，可直接调用，不需要new。
            sqldatareader rdr = sqlhelper.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();

            repcategories.datasource = catogories;
            repcategories.databind();
        }
    }

    //这个类是静态的，应用程序启动时，自动驻留在内存中，所建连接，在使用过程中，
    //也只是close()，放回到连接池中。并没有注销dispose()，可以下次连接时，快速使用。
    public abstract class sqlhelper
    {
        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);
            }
        }
    }
}
 

七、app_code文件夹中的类sqlhelper.cs

using system;
using system.configuration;
using system.data;
using system.data.sqlclient;
using system.collections;

namespace westgarden.web
{

    /// <summary>
    /// sqlhelper 的摘要说明
    /// </summary>
    //这个类是静态的，应用程序启动时，自动驻留在内存中，所建连接，在使用过程中，
    //也只是close()，放回到连接池中。并没有注销dispose()，可以下次连接时，快速使用。
    public abstract class sqlhelper
    {
        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);
            }
        }
    }
}

八、类库dbutility中的类sqlhelper.cs

[csharp] using system; 
using system.configuration; 
using system.data; 
using system.data.sqlclient; 
using system.collections; 
 
namespace westgarden.dbutility 
{ 
 
    /// <summary>  
    /// sqlhelper 的摘要说明  
    /// </summary>  
    //这个类是静态的，应用程序启动时，自动驻留在内存中，所建连接，在使用过程中，  
    //也只是close()，放回到连接池中。并没有注销dispose()，可以下次连接时，快速使用。  
    public abstract class sqlhelper 
    { 
        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); 
            } 
        } 
    } 
} 
using system;
using system.configuration;
using system.data;
using system.data.sqlclient;
using system.collections;

namespace westgarden.dbutility
{

    /// <summary>
    /// sqlhelper 的摘要说明
    /// </summary>
    //这个类是静态的，应用程序启动时，自动驻留在内存中，所建连接，在使用过程中，
    //也只是close()，放回到连接池中。并没有注销dispose()，可以下次连接时，快速使用。
    public abstract class sqlhelper
    {
        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);
            }
        }
    }
}
 

九、制作主界面default.x

[html] <%@ page language="c#" autoeventwireup="true" codefile="default.aspx.cs" inherits="westgarden.web.default" %> 
 
<!doctype html public "-//w3c//dtd xhtml 1.0 transitional//en" "http://www.w3.org/tr/xhtml1/dtd/xhtml1-transitional.dtd"> 
<html xmlns=""> 
<head runat="server"> 
    <title>肯德基订餐——西园工作室</title> 
</head> 
<body> 
    <form id="form1" runat="server"> 
        <asp:panel id="panfocus" runat="server" defaultbutton="btnsearch"> 
            <table align="center" border="0" cellpadding="0" cellspacing="0" width="780"> 
                <tr valign="top"> 
                    <td rowspan="2"> 
                        <img src="images/comm_images/logo_home.jpg" alt="home" /></td> 
                    <td height="25" width="141"> 
                        <asp:textbox id="txtsearch" runat="server" cssclass="homesearchbox" width="130px"></asp:textbox></td> 
                    <td width="50"> 
                        <asp:imagebutton id="btnsearch" runat="server" alternatetext="search" causesvalidation="false" 
                            cssclass="paddingsearchicon" imageurl="images/comm_images/button-search.gif" /></td> 
                    <td width="50"> 
                        <asp:loginstatus id="lgnstatus" runat="server" cssclass="homelink" logintext="登 录" 
                            logoutaction="redirect" logoutpageurl="~/default.aspx" logouttext="退 出" /> 
                    </td> 
                    <td width="64"> 
                    </td> 
                </tr> 
                <tr> 
                    <td colspan="3"> 
                        <img src="images/comm_images/kfc.jpg" /></td> 
                    <td width="64"> 
                    </td> 
                </tr> 
            </table> 
        </asp:panel> 
        <table align="center" border="0" cellpadding="0" cellspacing="0" width="780"> 
            <tr> 
                <td width="112"> 
                </td> 
                <td bgcolor="#ffffff" width="5"> 
                </td> 
                <td bgcolor="#ffffff" height="5" width="436"> 
                </td> 
                <td bgcolor="#ffffff" style="width: 5px"> 
                </td> 
                <td width="224"> 
                </td> 
            </tr> 
            <tr> 
                <td> 
                     </td> 
                <td bgcolor="#ffffff"> 
                     </td> 
                <td class="welcome"> 
                    欢迎光临肯德基订餐网站</td> 
                <td bgcolor="#ffffff" style="width: 5px"> 
                     </td> 
                <td> 
                     </td> 
            </tr> 
            <tr> 
                <td colspan="5" height="1"> 
                </td> 
            </tr> 
            <tr> 
                <td width="112"> 
                    <img height="290" src="images/comm_images/door.jpg" width="112" alt="sea horse" /></td> 
                <td bgcolor="#ffffff" width="5"> 
                     </td> 
                <td valign="top" width="436"> 
                    <table border="0" cellpadding="0" cellspacing="0"> 
                        <tr> 
                            <td> 
                                打开菜单，会有精彩的发现，各种美味在向您招手</td> 
                        </tr> 
                        <tr> 
                            <td> 
                                等待着您的品尝．</td> 
                        </tr> 
                        <tr> 
                            <td style="padding-left: 20px"> 
                                <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> 
                                <p> 
                                     </p> 
                                <br /> 
                            </td> 
                        </tr> 
                    </table> 
                </td> 
                <td bgcolor="#ffffff" valign="top" style="width: 5px"> 
                     </td> 
                <td valign="top"> 
                    <img src="images/comm_images/main_logo.gif" alt="西园软件出品" usemap="#mapwestgardensoftware" 
                        border="0" /></td> 
            </tr> 
            <tr> 
                <td> 
                     </td> 
                <td> 
                     </td> 
                <td> 
                    version 1.0 版权所有:西园电脑工作室.qq交流群:13033480</td> 
                <td style="width: 5px"> 
                     </td> 
                <td> 
                     </td> 
            </tr> 
        </table> 
        <map id="mapwestgardensoftware" name="西园软件"> 
            <area shape="rect" coords="240,235,140,300" " 
                target="_blank" alt="西园电脑工作室" /> 
        </map> 
    </form> 
</body> 
</html> 

作者 yousuosi