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

最新推荐文章于 2023-08-17 10:26:02 发布

bcbobo21cn

最新推荐文章于 2023-08-17 10:26:02 发布

阅读量2.3k

点赞数 1

分类专栏： .Net 文章标签： oracle asp.net三层架构

本文链接：https://blog.csdn.net/bcbobo21cn/article/details/52128027

版权

.Net 专栏收录该内容

381 篇文章 14 订阅

订阅专栏

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; }
        }
    }
}

四前端

<%@ 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>

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>

bcbobo21cn

关注

1
点赞
踩
3

收藏

觉得还不错? 一键收藏
0
评论
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.Oracle
复制链接

扫一扫