MVC访问Oracle数据库示例

1.说明

    应用OracleClient访问Oracle数据库，采用简单三层的架构方式，如下图所示：

2.代码

Utility

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

namespace Ulitity
{
    /// <summary>
    /// Oracle操作数据库类
    /// </summary>
    public class OracleHelper
    {
        /// <summary>
        /// 连接数据库字符串
        /// </summary>
        private  string strDBConnection = ConfigurationManager.AppSettings["DBConnection"].ToString();
        private OracleConnection conn = null;

        /// <summary>
        /// 打开数据库
        /// </summary>
        private void Open()
        {          
            if (conn == null)
            {
                conn = new OracleConnection(strDBConnection);
            }

            if (conn.State == System.Data.ConnectionState.Closed)
            {
                conn.Open();
            }
        }

        /// <summary>
        /// 关闭数据库
        /// </summary>
        private void Close()
        {
            if (conn != null && conn.State == System.Data.ConnectionState.Open)
            {
                conn.Close();
            }
        }

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <returns>返回执行结果</returns>
        public int ExcuteNoQuery(string strSql)
        {
            int nResult = -1;
            Open();

            OracleCommand cmd = new OracleCommand(strSql, conn);
            nResult = cmd.ExecuteNonQuery();

            Close();

            return nResult;
        }

        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <returns>返回DataTable</returns>
        public DataTable GetTable(string strSql)
        {
            OracleDataAdapter adapter = new OracleDataAdapter(strSql, conn);
            DataTable dt = new DataTable();
            adapter.Fill(dt);

            return dt;
        }

        /// <summary>
        /// 获取泛型实体集数据
        /// </summary>
        /// <typeparam name="T">泛型实体</typeparam>
        /// <param name="strSql">sql语句</param>
        /// <returns>返回泛型实体集合</returns>
        public List<T> GetEntitys<T>(string strSql)
        {
            OracleDataAdapter adapter = new OracleDataAdapter(strSql, conn);
            DataTable dt = new DataTable();
            adapter.Fill(dt);

            return ConvertHelper.ConvertToList<T>(dt);
        }
    }
}

 

DAL

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

namespace DAL
{
    public class DalPostChangeBiz
    {
        /// <summary>
        /// 获取数据
        /// </summary>
        /// <returns></returns>
        public List<PostChangeBiz> GetEntitys()
        {
            string strSql = string.Format(@"SELECT T.SEQNO,T.BIZ_NO as BIZNO,T.CHANGE_MEMO as CHANGEMEMO,T.STATUS,T.APPROVE_MEMO as APPROVEMEMO,
            T.SNO,T.CREATED_BY as CREATEDBY,T.CREATION_DATE as CREATIONDATE,T.LAST_UPDATED_BY as LASTUPDATEDBY,
            T.LAST_UPDATED_DATE as LASTUPDATEDDATE,T.ENABLED,T.SUBMIT_BY SUBMITBY,T.APPROVE_BY as APPROVEBY FROM BIZ T");
           
            OracleHelper helper = new OracleHelper();
            return helper.GetEntitys<PostChangeBiz>(strSql);
        }
    }
}

BLL

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entitys;
using DAL;

namespace BLL
{
    public class BllPostChangeBiz
    {
        public List<PostChangeBiz> GetEntitys()
        {
            return new DalPostChangeBiz().GetEntitys();
        }
    }
}

 

Entitys

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Entitys
{
    public class PostChangeBiz
    {
        private string seqNo;

        public string SeqNo
        {
            get { return seqNo; }
            set { seqNo = value; }
        }
        private string bizNo;

        public string BizNo
        {
            get { return bizNo; }
            set { bizNo = value; }
        }
        private string changeMemo;

        public string ChangeMemo
        {
            get { return changeMemo; }
            set { changeMemo = value; }
        }
        private string status;

        public string Status
        {
            get { return status; }
            set { status = value; }
        }
        private string approveMemo;

        public string ApproveMemo
        {
            get { return approveMemo; }
            set { approveMemo = value; }
        }
        private int sNo;

        public int SNo
        {
            get { return sNo; }
            set { sNo = value; }
        }
        private string createdBy;

        public string CreatedBy
        {
            get { return createdBy; }
            set { createdBy = value; }
        }
        private DateTime creationDate;

        public DateTime CreationDate
        {
            get { return creationDate; }
            set { creationDate = value; }
        }
        private string lastUpdatedBy;

        public string LastUpdatedBy
        {
            get { return lastUpdatedBy; }
            set { lastUpdatedBy = value; }
        }
        private DateTime lastUpdatedDate;

        public DateTime LastUpdatedDate
        {
            get { return lastUpdatedDate; }
            set { lastUpdatedDate = value; }
        }
        private int enabled;

        public int Enabled
        {
            get { return enabled; }
            set { enabled = value; }
        }
        private string submitBy;

        public string SubmitBy
        {
            get { return submitBy; }
            set { submitBy = value; }
        }
        private string approveBy;

        public string ApproveBy
        {
            get { return approveBy; }
            set { approveBy = value; }
        }

    }
}

 

Controllers

 

        public ActionResult PostChangeBiz()
        {
            ViewData["PostChangeBizList"] = new BllPostChangeBiz().GetEntitys();
            return View("PostChangeBiz");
        }

 

Views

@using Entitys;
@model Entitys.PostChangeBiz

@{
    ViewBag.Title = "审批流程过程";
    Layout = null;
}
   
<!DOCTYPE html>

<html>
<head>
    <title>审批</title>
    <style type="text/css">       
        .tb_datalist {
         BORDER-BOTTOM: silver 1px solid; BORDER-RIGHT: silver 1px solid; BACKGROUND: #C6D5F5; WIDTH: 100%;
        }
        .tb_datalist .tr_title td {
         BORDER-TOP: silver 1px solid; BORDER-LEFT: silver 1px solid; BACKGROUND: #D2E1FD;
        }
        .tb_datalist .tr_content td {
         PADDING-LEFT: 2px; PADDING-BOTTOM: 1px; PADDING-RIGHT: 1px; PADDING-TOP: 3px;
         BORDER-TOP: silver 1px solid; BORDER-LEFT: silver 1px solid; BACKGROUND: white;
        }
    </style>
</head>
<body>   
    <div>
    <table cellpadding="1" cellspacing="0" class="tb_datalist">
        <tr class="tr_title">
            <td width="100">审批编号</td>
            <td width="100">变更说明</td>
            <td width="120">审批状态</td>
            <td width="100">审批意见</td>
            <td width="100">单据序号</td>
            <td width="150">创建人</td>
            <td width="140">创建时间</td>
            <td width="150">最后修改人</td>
            <td width="140">最后修改时间</td>
            <td width="100">单据状态</td>
            <td width="150">提交人</td>
            <td width="150">审批人</td>
        </tr>
        @foreach (Entitys.PostChangeBiz item in ViewData["PostChangeBizList"] as List<Entitys.PostChangeBiz>)
        {                   
            <tr class="tr_content">
            <td>@item.BizNo</td>
            <td>@item.ChangeMemo</td>
            <td>@item.Status</td>
            <td>@item.ApproveMemo</td>
            <td>@item.SNo.ToString()</td>
            <td>@item.CreatedBy</td>
            <td>@item.CreationDate.ToString("yyyy-MM-dd")</td>
            <td>@item.LastUpdatedBy</td>
            <td>@item.LastUpdatedDate.ToString("yyyy-MM-dd")</td>
            <td>@item.Enabled.ToString()</td>
            <td>@item.SubmitBy</td>
            <td>@item.ApproveBy</td>
            </tr>
        }
    </table>
    </div>
</body>
</html>