三层架构示例代码

 

//(一)Dal层中的SqlHelper

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{
    public static class SqlHelper
    {
        //准备一个连接字符串
        private static string connStr = ConfigurationManager.ConnectionStrings["myconnStr"].ConnectionString;

        //private static string connStr = ConfigurationManager.ConnectionStrings["MyTestConnectionString1"].ConnectionString;

 

        /// <summary>
        /// (1)执行一个存储过程得到一个DataTable对象
        /// </summary>
        /// <param name="Procname"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static DataTable ExecuteProc(string Procname, params SqlParameter[] paras)
        {
            SqlConnection con = new SqlConnection(SqlHelper.connStr);
            SqlDataAdapter da = new SqlDataAdapter(Procname, con);
            da.SelectCommand.Parameters.AddRange(paras);

            da.SelectCommand.CommandType = CommandType.StoredProcedure;//设置使用存储过程
            DataTable table = new DataTable();

            da.Fill(table);//把得到的DataTable对象存放到适配器中
            return table;//返回得到的DataTable对象

        }

        /// <summary>
        /// (2)执行一个没有参数的存储过程得到一个DataTable对象
        /// </summary>
        /// <param name="Procname"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static DataTable ExecuteProcNoQuery(string Procname)
        {
            SqlConnection con = new SqlConnection(SqlHelper.connStr);
            SqlDataAdapter da = new SqlDataAdapter(Procname, con);
            //da.SelectCommand.Parameters.AddRange(paras);

            da.SelectCommand.CommandType = CommandType.StoredProcedure;//设置使用存储过程
            DataTable table = new DataTable();

            da.Fill(table);//把得到的DataTable对象存放到适配器中
            return table;//返回得到的DataTable对象

        }

 

        /// <summary>
        /// (3)执行一个没有参数的存储过程得到一个int返回值
        /// (之所以用返回值是int类型是为了在执行增删改的时候做个判断)
        /// </summary>
        /// <param name="Procname"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static int ExecuteProcNoQueryInt(string Procname)
        {
            SqlConnection con = new SqlConnection(SqlHelper.connStr);
            SqlDataAdapter da = new SqlDataAdapter(Procname, con);
            //da.SelectCommand.Parameters.AddRange(paras);

            da.SelectCommand.CommandType = CommandType.StoredProcedure;//设置使用存储过程
            DataTable table = new DataTable();

            da.Fill(table);//把得到的DataTable对象存放到适配器中
            return table.Rows.Count;//返回得到的DataTable中数据行数

        }

 

        /// <summary>
        /// (4)执行一个有参数的存储过程得到一个int返回值
        /// (之所以用返回值是int类型是为了在执行增删改的时候做个判断)
        /// </summary>
        /// <param name="Procname"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static int ExecuteNonQueryProcInt(string Procname, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {

                    cmd.CommandType = CommandType.StoredProcedure;//设置为存储过程
                    cmd.CommandText = Procname;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteNonQuery();
                }
            }
        }

 

 

        /// <summary>
        /// (5)如果从数据库中取到的数据是NULL的时候，则转化为C#中的null类型
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static object FromDbValue(object value)
        {
            if (value == DBNull.Value)
            {
                return null;
            }
            else
            {
                return value;
            }
        }

 

        /// <summary>
        /// (6)如果数据为Null的话，则把数据转化为数据库中的DBNULL类型
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static object ToDbValue(object value)
        {
            if (value == null)
            {
                return DBNull.Value;
            }
            else
            {
                return value;
            }
        }

 

        //(7)执行sql的增删改操作
        public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteNonQuery();
                }
            }
        }

 

              
        /// <summary>
        /// (8)执行带参数的查询操作
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteScalar();
                }
            }
        }

 

        /// <summary>
        /// (9)执行不带参数的查询操作
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    //cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteScalar();
                }
            }
        }

 

        //(10)返回一个DataTable的方法
        public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);
                    DataSet dataset = new DataSet();
                    SqlDataAdapter apdater = new SqlDataAdapter(cmd);
                    apdater.Fill(dataset);
                    return  dataset.Tables[0];
                }
            }
        }

 

 

 

        /// <summary>
        /// (11)执行存储过程，返回一个数据表
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static DataTable ExcuteProctest(string procName, params SqlParameter[] paras)
        {
            SqlConnection conn = new SqlConnection(SqlHelper.connStr);
            SqlDataAdapter da = new SqlDataAdapter(procName, conn);
            da.SelectCommand.Parameters.AddRange(paras);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;//设置 使用存储过程

            DataTable dt = new DataTable();
            //查询数据，并将返回的数据装入数据表dt
            da.Fill(dt);
            return dt;
        }

 

 

        /// <summary>
        /// (12)执行存储过程，返回一个数据表
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static DataTable ExcuteProctestTable(string procName, params SqlParameter[] paras)
        {
            SqlConnection conn = new SqlConnection(SqlHelper.connStr);
            SqlDataAdapter da = new SqlDataAdapter(procName, conn);
            da.SelectCommand.Parameters.AddRange(paras);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;//设置 使用存储过程

            DataTable dt = new DataTable();
            //查询数据，并将返回的数据装入数据表dt
            da.Fill(dt);
            return dt;
        }

 

        /// <summary>
        /// (16)执行存储过程，返回一个数据表
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static DataSet ExcuteProctestDataSet(string procName)
        {
            SqlConnection conn = new SqlConnection(SqlHelper.connStr);
            SqlDataAdapter da = new SqlDataAdapter(procName, conn);
            //da.SelectCommand.Parameters.AddRange(paras);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;//设置 使用存储过程

            //DataTable dt = new DataTable();
            //da.Fill(dt);
            //return dt;

            DataSet ds = new DataSet();
            //查询数据，并将返回的数据装入数据表dt
            da.Fill(ds);
            return ds;

        }

 

     //(13)连接数据库
        public static SqlConnection GetConnection()
        {
            //temp="data source = (local);initial catalog = bwg; user id = sa;password = 111";
            //StreamReader sr = File.OpenText(@"1.txt");
            //string temp = sr.ReadToEnd();

            SqlConnection myConn = new SqlConnection();
            myConn.ConnectionString = connStr;
            return myConn;
        }

 

        //(14)查询数据库
        public static DataSet GetDataSetStr(string sqlStr)
        {
            SqlConnection myConn = GetConnection();
            myConn.Open();
            DataSet ds = new DataSet();
            SqlDataAdapter adapt = new SqlDataAdapter(sqlStr, myConn);

            adapt.Fill(ds);
            myConn.Close();
            return ds;
        }

        //(15)非查询数据库，即增，删，改，查数据库
        public static bool notQuery(string sql)
        {
            //StreamReader sr = File.OpenText(@"1.txt");
            //string temp = sr.ReadToEnd();
            SqlConnection conn3 = new SqlConnection(connStr);
            conn3.Open();
            SqlCommand cmd = new SqlCommand(sql, conn3);
            try
            {
                cmd.ExecuteNonQuery();
                //MessageBox.Show("保存成功");
                //this.button3.Enabled = true;
                return true;
            }
            catch (Exception)
            {
                //MessageBox.Show("保存失败！");
                return false;
            }
            finally
            {
                conn3.Close();
            }
        }

 

   }
}

 

 

 

 

 

//(二)Dal层中业务代码：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{
   
    //获得权限数据
    public class GetRightDal
    {
        /// <summary>
        /// (1)获得权限数据
        /// </summary>
        /// <returns></returns>
        public DataTable getRight()
        {
           // SqlParameter[] myParas = { new SqlParameter("getProc") };
            DataTable table= SqlHelper.ExecuteProcNoQuery("getProc");
            return table;
        }

        /// <summary>
        /// (2)获得权限数据
        /// </summary>
        /// <returns></returns>
        public DataTable getRightDataByID(MODEL.Right right)
        {
            SqlParameter[] myParams = { new SqlParameter("@ID",right.ID) };

            DataTable table = SqlHelper.ExecuteProc("GetRightDataByID",myParams);
            return table;
        }

        /// <summary>
        /// (3)添加数据的方法
        /// </summary>
        /// <returns></returns>
        public bool insertData(MODEL.Right right)
        {

            SqlParameter[] myparas = {
                                         new SqlParameter("@FName", SqlDbType.VarChar),
                                        new SqlParameter("@FDescription", SqlDbType.VarChar)

                                     };

            myparas[0].Value = right.FName;
            myparas[1].Value = right.FDescription;

            int i = SqlHelper.ExecuteNonQueryProcInt("insertData", myparas);
            return i>0?true:false;
        }

    }
}

 

 

//(三)BLL层代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace BLL
{
    public class RightBll
    {

        //(1)获得权限数据
        public DataTable getRight()
        {
            DAL.GetRightDal dal = new DAL.GetRightDal();
            DataTable table = dal.getRight();
            return table;
        }

        /// <summary>
        /// (2)获得权限数据
        /// </summary>
        /// <returns></returns>
        public DataTable getRightDataByID(MODEL.Right right)
        {
            DAL.GetRightDal dal = new DAL.GetRightDal();
            DataTable table = dal.getRightDataByID(right);
            return table;
        }

        /// <summary>
        /// (3)添加数据的方法
        /// </summary>
        /// <returns></returns>
        public bool insertData(MODEL.Right right)
        {
            DAL.GetRightDal dal = new DAL.GetRightDal();
            bool b=dal.insertData(right);
            return b;
        }

 

    }

}

 

 

//(四)UI前台界面代码

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="权限管理页面.aspx.cs" Inherits="WebApplicationUI.权限管理页面" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>

    <style type="text/css">
        #tbList
        {
            border-top: 1px solid #0088F7;
            border-left: 1px solid #0088F7;
            width: 530px;
        }
       
        #tbList td, th
        {
            border-bottom: 1px solid #0088F7;
            border-right: 1px solid #0088F7;
            font-size: 16px;
        }
       
        .textbox
        {
            border-left: solid 0 #ffffff;
            border-bottom: solid 1px #716E6F;
            border-right: solid 0 #ffffff;
            border-top: solid 0 #ffffff;
        }
    </style>

</head>
<body>
    <form id="form1" runat="server">
    <div>
            <br />
            <asp:ScriptManager ID="ScriptManager1" runat="server">
            </asp:ScriptManager>
            <br />
            <br />
            <asp:UpdatePanel ID="UpdatePanel1" runat="server">
                <ContentTemplate>

                  </ContentTemplate>
            </asp:UpdatePanel>

 

            <br />
          
                    <table ID="tbList">
                        <tr>
                            <td>
                                请求内容
                            </td>
                        </tr>
                        <tr>
                            <td>
                                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
                                    EnableModelValidation="True" onrowcommand="GridView1_RowCommand"
                                    onrowediting="GridView1_RowEditing"
                                    onselectedindexchanged="GridView1_SelectedIndexChanged">
                                    <Columns>
                                        <asp:BoundField DataField="ID" HeaderText="编号" />
                                        <asp:BoundField DataField="FName" HeaderText="权限名称" />
                                        <asp:BoundField DataField="FDescription" HeaderText="权限描述" />
                                        <asp:CommandField HeaderText="编辑" ShowEditButton="True" />
                                    </Columns>
                                </asp:GridView>
                            </td>
                        </tr>
                    </table>
                    <div style="background-color:Blue; width:530px;">
                        权限名称
                        <asp:DropDownList ID="ddlRight" runat="server">
                            <asp:ListItem>新增</asp:ListItem>
                            <asp:ListItem>删除</asp:ListItem>
                            <asp:ListItem>修改</asp:ListItem>
                            <asp:ListItem>更新</asp:ListItem>
                        </asp:DropDownList>
                        &nbsp;&nbsp; 权限描述<asp:TextBox ID="txtDescription" runat="server"></asp:TextBox>
                        &nbsp;
                        <asp:Button ID="btnAdd" runat="server" ForeColor="Blue" οnclick="btnAdd_Click"
                            Text="添加按钮" />

                        <asp:Label ID="lbSuccess" Visible="false" runat="server" Text="添加成功"></asp:Label>
            <br />
            <br />
           
                    </div>

        <br />
           
             
            <br />
            <br />
            <br />
            <br />
            <br />
            <br />
        <br />

    </div>
    </form>
</body>
</html>

 

 

//(五)UI后台cs代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;

namespace WebApplicationUI
{
    public partial class 权限管理页面 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            getData();
        }

 

        //获得数据
        void getData()
        {
            BLL.RightBll bll = new BLL.RightBll();
            DataTable table = bll.getRight();
            GridView1.DataSource = table;
            GridView1.DataBind();
        }

        //行命令
        protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            //Response.Write(Convert.ToInt32( e.CommandArgument));

            int id = Convert.ToInt32(e.CommandArgument);//得到行的行标

            BLL.RightBll bll = new BLL.RightBll();

            MODEL.Right right = new MODEL.Right();
            right.ID = id+1;

            DataTable table = bll.getRightDataByID(right);//得到权限数据源

            //绑定数据项
            txtDescription.Text = table.Rows[0]["FDescription"].ToString();
            ddlRight.SelectedItem.Text = table.Rows[0]["FName"].ToString();

            btnAdd.Text = "更新按钮";

        }

        protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
        {
            //Response.Write(e.NewEditIndex);

           
        }

        //提交或更新按钮
        protected void btnAdd_Click(object sender, EventArgs e)
        {
            if (btnAdd.Text=="添加按钮")//如果为提交按钮的文本为提交的话
            {
                string nameDescription=txtDescription.Text;
                string nameStr = ddlRight.SelectedItem.ToString();//取到下拉文本选中的值
           
                //调用添加的方法

                MODEL.Right right = new MODEL.Right();
                right.FName = nameStr;
                right.FDescription = nameDescription;
                BLL.RightBll bll = new BLL.RightBll();
                bool boolRight = bll.insertData(right);

                if (boolRight)
                {
                    //ClientScript.RegisterClientScriptBlock(GetType(), "onload","alert('添加成功')", true);
                   
                    lbSuccess.Visible = true;//显示出来保存成功的提示标签

                    getData();//再次绑定数据

                }
                else
                {
                    //ScriptManager.RegisterStartupScript(this.Page, GetType(), "test", "alert('添加失败')", true);
                    lbSuccess.Visible = false;
                }

            }

 

        }

     
    }
}