asp.net三层架构连接Oracle 11g详解

asp.net三层架构连接Oracle 11g

连接Oracle时使用微软的Oracle连接组件；

一 DAL层

using System;using System.Collections.Generic;using System.Text;using System.Collections;using System.Data;using System.Data.OracleClient;namespace SystemDAL{    public class DAL    {        //连接字符串        public static string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["db"].ToString();        public DAL()        {            //connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["db"].ToString();        }        /// <summary>        /// 执行单条语句        /// </summary>        /// <param name="sql"></param>        /// <returns></returns>        public static int ExecuteNonQuery(string sql)        {            int x = 0;            try            {                // Open a connection to the DB.                OracleConnection connOra = new OracleConnection(connectionString);                connOra.Open();                OracleTransaction tran = connOra.BeginTransaction();                // Create a command to execute the sql statement.                OracleCommand cmdOra = connOra.CreateCommand();                cmdOra.CommandText = sql;                x = cmdOra.ExecuteNonQuery();                tran.Commit();                connOra.Close();                connOra.Dispose();                cmdOra.Dispose();            }            catch (Exception ex)            {                //log.Error(ex.StackTrace);            }            return x;        }        public static DataTable ExecuteDataTable(String cmdText)        {            DataTable dt = new DataTable();            //DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);            try            {                // Open a connection to the DB.                //DbConnection connOra = factory.CreateConnection();                OracleConnection connOra = new OracleConnection(connectionString);                //connOra.ConnectionString = connectionString;                connOra.Open();                // Create a command to execute the sql statement.                //DbCommand cmd = factory.CreateCommand();                OracleCommand cmd = connOra.CreateCommand();                cmd.CommandText = cmdText;                OracleDataAdapter ada = new OracleDataAdapter();                //DbDataAdapter ada = factory.CreateDataAdapter();// new OracleDataAdapter(cmd1);                                ada.SelectCommand = cmd;                ada.Fill(dt);                connOra.Close();                connOra.Dispose();                cmd.Dispose();            }            catch (Exception ex)            {                string str = ex.Message;            }            return dt;        }    }}

类DAL；


执行单条语句ExecuteNonQuery：
传入sql语句，返回值int；
打开Oracle连接，构造Oracle命令对象，设置命令对象的命令文本等于传入的sql；
执行sql，返回执行的int型结果；


获取数据表ExecuteDataTable:
传入sql语句，返回数据表；
打开Oracle连接；
构造Oracle命令对象，设置命令对象的命令文本等于传入的sql；
new一个Oracle适配器对象，设置适配器对象的SelectCommand等于命令对象；
调用适配器对象的Fill方法填充数据表对象，返回数据表对象；

二 BLL层

using System;using System.Collections.Generic;using System.Text;using SystemModel;using SystemDAL;using System.Data;namespace SystemBLL{    public class BLL    {        public BLL()        {        }        /// <summary>        /// 插入方法        /// </summary>        /// <param name="M"></param>        /// <returns></returns>        public static int InsertData(LoginModel M)        {            string sql = "INSERT INTO Login VALUES(@LoginName,@LoginPassword)";            try            {                DAL dal = new DAL();                return DAL.ExecuteNonQuery(sql);            }            catch (Exception E)            {                throw E;            }        }        /// <summary>        /// 修改方法        /// </summary>        /// <param name="M"></param>        /// <returns></returns>        public static int UpdateTData(LoginModel M)        {            string sql = "UPDATE Login SET LoginName=@LoginName,LoginPassword=@LoginPassword WHERE ID=@ID";            try            {                DAL dal = new DAL();                return DAL.ExecuteNonQuery(sql);            }            catch (Exception E)            {                throw E;            }        }        /// <summary>        /// 删除方法        /// </summary>        /// <param name="M"></param>        /// <returns></returns>        public static int DeleteData(LoginModel M)        {            string sql = "DELETE  FROM Login WHERE ID=@ID";            try            {                DAL dal = new DAL();                return DAL.ExecuteNonQuery(sql);            }            catch (Exception E)            {                throw E;            }        }        /// <summary>        /// 登录方法        /// </summary>        /// <param name="M"></param>        /// <returns></returns>        public static DataTable Login(string LoginName, string LoginPassword)        {            string sql = "SELECT * FROM Logins WHERE UserName=@LoginName AND Password=@LoginPassword";            try            {                DAL dal = new DAL();                return DAL.ExecuteDataTable(sql);            }            catch (Exception E)            {                throw E;            }        }        /// <summary>        /// 查询所有用户        /// </summary>        /// <param name="M"></param>        /// <returns></returns>        public static DataTable GetUser(LoginModel M)        {            try            {                string sql = "SELECT * FROM Login";                DAL dal = new DAL();                return DAL.ExecuteDataTable(sql);            }            catch (Exception E)            {                 throw E;            }        }        /// <summary>        /// 查询单个用户        /// </summary>        /// <param name="M"></param>        /// <returns></returns>        public static DataTable GetUserID(LoginModel M)        {            try            {                string sql = "SELECT * FROM Login WHERE ID=@ID";                DAL dal = new DAL();                return DAL.ExecuteDataTable(sql);            }            catch (Exception E)            {                                throw E;            }        }    }}

引用SystemModel，SystemDAL；
类BLL;


插入数据InsertData：
传入LoginModel对象M，返回int；
调用DAL.ExecuteNonQuery在Login表中插入值；


修改数据UpdateTData：
传入LoginModel对象M，返回int；
调用DAL.ExecuteNonQuery更新Login表;


删除数据DeleteData：
传入LoginModel对象M，返回int；
调用DAL.ExecuteNonQuery从Login表删除数据;


登录方法Login：
调用DAL.ExecuteNonQuery从Login表返回匹配的记录；
返回类型为DataTable；


查询所有用户，返回DataTable；
查询单个用户，根据ID返回DataTable；

三 实体层

using System;using System.Collections.Generic;using System.Text;namespace SystemModel{    public class LoginModel    {        public LoginModel()        {        }        private int _ID;        public int ID        {            get { return _ID; }            set { _ID = value; }        }        private string _LoginName;        public string LoginName        {            get { return _LoginName; }            set { _LoginName = value; }        }        private string _LoginPassword;        public string LoginPassword        {            get { return _LoginPassword; }            set { _LoginPassword = value; }        }    }}

四 前端

login.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Login.aspx.cs" Inherits="_Default" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head runat="server">    <title>登录界面|- 51aspx.com</title></head><body style="text-align: center">    <form id="form1" runat="server">        <asp:ScriptManager ID="ScriptManager1" runat="server" />        <asp:Panel ID="Panel1" runat="server" Height="13px" Width="359px" style="font-weight: bold; font-size: small">            <table style="width: 372px">                <tr>                    <td style="width: 81px">                        <asp:Label ID="Label1" runat="server" Text="用户名"></asp:Label></td>                    <td style="width: 146px">                        <asp:TextBox ID="TextBox1" runat="server" Height="18px"></asp:TextBox></td>                    <td style="width: 116px">                        <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="TextBox1"                            ErrorMessage="用户名不能为空" ForeColor="DarkGray" Width="126px"></asp:RequiredFieldValidator></td>                </tr>                <tr>                    <td style="width: 81px; height: 28px;">                        <asp:Label ID="Label2" runat="server" Text="密    码"></asp:Label></td>                    <td style="width: 146px; height: 28px;">                        <asp:TextBox ID="TextBox2" runat="server" TextMode="Password" Width="149px"></asp:TextBox></td>                    <td style="width: 116px; height: 28px;">                        <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="TextBox2"                            ErrorMessage="密码不能为空" ForeColor="DarkGray"></asp:RequiredFieldValidator></td>                </tr>                <tr>                    <td style="width: 81px">                    </td>                    <td style="width: 146px">                        <asp:LinkButton ID="LinkButton1" runat="server" Font-Underline="False" ForeColor="Black"                            OnClick="LinkButton1_Click">登 录</asp:LinkButton>                                                             <asp:LinkButton ID="LinkButton2" runat="server" Font-Underline="False" ForeColor="Black"                            OnClick="LinkButton2_Click">重  置</asp:LinkButton></td>                    <td style="width: 116px">                    </td>                </tr>            </table>        </asp:Panel>    </form></body></html>

login.aspx.cs

using System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using SystemModel;using SystemBLL;public partial class _Default : System.Web.UI.Page {    protected void Page_Load(object sender, EventArgs e)    {    }    protected void LinkButton1_Click(object sender, EventArgs e)    {        DataTable table1 = BLL.Login(this.TextBox1.Text, this.TextBox2.Text);        if (table1.Rows.Count > 0)        {            Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('登录成功！');</script>");            Response.Redirect("test.aspx");        }        else        {            Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('登录失败！');</script>");        }    }    protected void LinkButton2_Click(object sender, EventArgs e)    {        this.TextBox1.Text = null;        this.TextBox2.Text = null;    }}

五 web.config

<span style="white-space:pre"></span><connectionStrings><add name="db" connectionString="Data Source=ORCL;User Id=scott;Password=123dd654ca"/></connectionStrings>