ASP.NET操作Oracle知识记录（采用ODP.NET）

╰+哭是因爲堅強的太久メ 2022-05-12 23:14 45阅读 0赞

1:连接Oracle配置内容

2:对Oracle进行增删改

(1)：增加操作

![复制代码][copycode.gif]

public bool Create(LogInfoModel model)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("insert into LOGINFO(");
                strSql.Append(" ID,LOGSOURCE,LOGTYPE,LOGCONTENT,OPERATER,CREATEDATE,ISDELETE");
                strSql.Append(") values (");
                strSql.Append(" :ID,:LOGSOURCE,:LOGTYPE,:LOGCONTENT,:OPERATER,:CREATEDATE,:ISDELETE");
                strSql.Append(") ");
                OracleParameter[] parameters = {
                            new OracleParameter(":ID",  model.ID),           
                            new OracleParameter(":LOGSOURCE",  model.LOGSOURCE),           
                            new OracleParameter(":LOGTYPE",  model.LOGTYPE),           
                            new OracleParameter(":LOGCONTENT",  model.LOGCONTENT),           
                            new OracleParameter(":OPERATER",  model.OPERATER),           
                            new OracleParameter(":CREATEDATE",  model.CREATEDATE),           
                            new OracleParameter(":ISDELETE",  model.ISDELETE),           
                    };
                return OracleHelper.OracleHelper.ExecuteNonQuery(PubConnection.ConnectionString, CommandType.Text, strSql.ToString(), parameters) > 0;
            }

![复制代码][copycode.gif]

(2)：更新操作

![复制代码][copycode.gif]

public bool Update(LogInfoModel model)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("update LOGINFO set ");
                strSql.Append(" ID = :ID , ");
                strSql.Append(" LOGSOURCE = :LOGSOURCE , ");
                strSql.Append(" LOGTYPE = :LOGTYPE , ");
                strSql.Append(" LOGCONTENT = :LOGCONTENT , ");
                strSql.Append(" OPERATER = :OPERATER , ");
                strSql.Append(" ISDELETE = :ISDELETE ");
                strSql.Append(" where ID=:ID  ");
                OracleParameter[] parameters = {
                            new OracleParameter(":ID",  model.ID),           
                            new OracleParameter(":LOGSOURCE",  model.LOGSOURCE),           
                            new OracleParameter(":LOGTYPE",  model.LOGTYPE),           
                            new OracleParameter(":LOGCONTENT",  model.LOGCONTENT),           
                            new OracleParameter(":OPERATER",  model.OPERATER),                    
                            new OracleParameter(":ISDELETE",  model.ISDELETE),           
                };
                return OracleHelper.OracleHelper.ExecuteNonQuery(PubConnection.ConnectionString, CommandType.Text, strSql.ToString(), parameters) > 0;
            }

![复制代码][copycode.gif]

注意：Oracle更新要是用OracleParameter参数形式时要把条件也进行更新，否则会失败；这个跟MSSQL是不一样的；或者可以换另外一种写法：

![复制代码][copycode.gif]

public bool Update(LogInfoModel model)
            {
                string strSql = string.Format("update LOGINFO set LOGSOURCE = :LOGSOURCE ,LOGTYPE = :LOGTYPE ,LOGCONTENT = :LOGCONTENT ,OPERATER = :OPERATER ,ISDELETE = :ISDELETE where ID='{0}'", model.ID);
                OracleParameter[] parameters = {          
                            new OracleParameter(":LOGSOURCE",  model.LOGSOURCE),           
                            new OracleParameter(":LOGTYPE",  model.LOGTYPE),           
                            new OracleParameter(":LOGCONTENT",  model.LOGCONTENT),           
                            new OracleParameter(":OPERATER",  model.OPERATER),                      
                            new OracleParameter(":ISDELETE",  model.ISDELETE),           
                };
                return OracleHelper.OracleHelper.ExecuteNonQuery(PubConnection.ConnectionString, CommandType.Text, strSql, parameters) > 0;
            }

![复制代码][copycode.gif]

(3)：删除操作

public bool Delete(string ID)
            {
                string sql = "delete  from LOGINFO where ID=:ID";
                OracleParameter[] parm = { new OracleParameter(":ID", ID) };
                return OracleHelper.OracleHelper.ExecuteNonQuery(PubConnection.ConnectionString, CommandType.Text, sql, parm) > 0;
            }

(4)：查找操作

![复制代码][copycode.gif]

public List<LogInfoModel> GetListForwhere(string strWhere)
            {
                string sql = string.Empty;
                if (string.IsNullOrEmpty(strWhere))
                {
                    sql = "select * from LOGINFO";
                }
                else
                {
                    sql = "select *  from LOGINFO where " + strWhere + "";
                }
                DataTable dt = OracleHelper.OracleHelper.ExecuteDataset(PubConnection.ConnectionString, CommandType.Text, sql).Tables[0];
                return Convert(dt);
            }

![复制代码][copycode.gif]

3:创建自增字段

Oracle因为没有像MSSQL那么简单的创建自增字段，所以必需通过SQL脚本进行设置（先创建序列再创建触发器），实例如下：

(1)创建表

Create  table  t_user(
    Id number(6),userid varchar2(20)
    );

(2)创建序列

create sequence user_seq
    increment by 1  
    start with 1
    nomaxvalue
    nominvalue
    nocache

(3)创建触发器

create or  replace trigger tr_user
    before insert on t_popedom_user
    for each row
    begin
    select user_seq.nextval into :new.id from dual;
    end;

4:使用存储过程进行分页:

(1):首先在Oracle新建一个存储过程,脚本如下(此脚本源于网络):

![复制代码][copycode.gif]

create or replace package p_page is
      TYPE type_cur IS REF CURSOR; --定义游标变量用于返回记录集
    
      PROCEDURE Pagination(Pindex in number, --分页索引
                           Psql   in varchar2, --产生dataset的sql语句
                           Psize  in number, --页面大小
                           Pcount out number, --返回分页总数
                           Prcount out number,--返回总条数
                           v_cur  out type_cur --返回当前页数据记录
                           );
    end p_page;
    /
    create or replace package body p_page is
    
    PROCEDURE Pagination(
      Pindex in number,
      Psql in varchar2,
      Psize in number,
      Pcount out number,
      Prcount out number,
      v_cur out type_cur
     )
     AS
    
      v_sql VARCHAR2(1000);
      v_count number;
      v_Plow number;
      v_Phei number;
      v_prcount number;
      
     Begin
      ------------------------------------------------------------取分页总数
      v_sql := 'select count(*) from (' || Psql || ')';
      execute immediate v_sql into v_count;
      Pcount := ceil(v_count/Psize);
      ------------------------------------------------------------显示总条数
      v_sql := 'select count(*) from (' || Psql || ')';
       execute immediate v_sql into v_prcount;
       Prcount := v_prcount;                  --返回记录总数  
      ------------------------------------------------------------显示任意页内容
      v_Phei := Pindex * Psize + Psize;
      v_Plow := v_Phei - Psize + 1;
      --Psql := 'select rownum rn,t.* from zzda t' ;            --要求必须包含rownum字段
      v_sql := 'select * from (' || Psql || ') where rn between ' || v_Plow || ' and ' || v_Phei ;
    
      open v_cur for v_sql;
    
     End Pagination;
    
     --**************************************************************************************
    end p_page;

![复制代码][copycode.gif]

(2):把分页进行简单封装,返回DataTable

![复制代码][copycode.gif]

/// <summary>
            /// 内容描述：使用存储过程，查询分页后的数据。
            /// </summary>
            /// <param name="strConStr">连接oracle数据库字符串</param>
            /// <param name="strSql">查询sql语句(要求必须包含rownum字段,别名是rn 如:select rownum rn,ID from TableName)</param>
            /// <param name="curPage">当前显示页数</param>
            /// <param name="strPageSize">每页条数</param>
            /// <param name="strCount">返回分页数</param>
            /// <param name="strRowCount">返回记录数</param>
            /// <returns>分页结果数据集</returns>
            public static DataTable getDataTable(string strConStr, string strSql, int curPage, string strPageSize, out string strCount, out string strRowCount)
            {
                DataTable dt = new DataTable();
                OracleParameter[] parameters = { 
                                               new OracleParameter("Pindex",curPage-1),
                                               new OracleParameter("Psql",strSql),
                                               new OracleParameter("Psize",strPageSize),
                                               new OracleParameter("Pcount",OracleDbType.Int32),
                                               new OracleParameter("Prcount",OracleDbType.Int32),
                                               new OracleParameter("v_cur",OracleDbType.RefCursor),
                                               };
                parameters[3].Direction = ParameterDirection.Output;
                parameters[4].Direction = ParameterDirection.Output;
                parameters[5].Direction = ParameterDirection.Output;
                dt = OracleHelper.ExecuteDataset(strConStr, CommandType.StoredProcedure, "p_page.Pagination", parameters).Tables[0];
                strCount = parameters[3].Value.ToString();
                strRowCount = parameters[4].Value.ToString();
                return dt;
            }
    
            /// <summary>
            /// 内容描述：使用存储过程，查询分页后的数据。
            /// </summary>
            /// <param name="strConStr">连接oracle数据库字符串</param>
            /// <param name="strSql">查询sql语句(要求必须包含rownum字段,别名是rn 如:select rownum rn,ID from TableName)</param>
            /// <param name="curPage">当前显示页数</param>
            /// <param name="strPageSize">每页条数</param>
            /// <param name="strCount">返回分页数</param>
            /// <param name="strRowCount">返回记录数</param>
            /// <param name="Strwhere">带条件 没有则为空</param>
            /// <returns>分页结果数据集</returns>
            public static DataTable getDataTable(string strConStr, string strSql, int curPage, string strPageSize, out string strCount, out string strRowCount,string Strwhere)
            {
                string StrSqlByWhere;
                if (!string.IsNullOrEmpty(Strwhere))
                {
                    StrSqlByWhere = strSql + " where " + Strwhere;
                }
                else
                {
                    StrSqlByWhere = strSql;
                }
                return getDataTable(strConStr, StrSqlByWhere, curPage, strPageSize, out strCount, out strRowCount);
            }

![复制代码][copycode.gif]

(3):分页测试代码

![复制代码][copycode.gif]

public static void PageGetDataSet()
            {
                string HangSh = "0";//行数
                string YeSh = "0";//页数
                string connectStr = ConfigurationManager.AppSettings["OracleConString"];
                string sqlStr = "select rownum rn,ID from wujy.Db_Tables";
                DataTable tb = getDataTable(connectStr, sqlStr, 1, "10", out YeSh, out HangSh);
                Console.WriteLine("总共为" + YeSh + "页" + "|每页为10条|数据总条数为" + HangSh);
                Console.WriteLine("第一页:");
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    Console.WriteLine(tb.Rows[i]["ID"].ToString());
                }
            }

![复制代码][copycode.gif]

(4):运行效果:

![30114535-afa57159eef44744afa7bd5ea839c749.png][]

5:其它常见问题：

(1):创建完Oracle后我们一般会新建一个用户来,语句如下:

create user wujy identified by admin

创建完这个用户它是没有权限的,要对它进行赋相应角色

grant connect,resource to wujy

(2):Oracle中执行SQL时,如果被操作的表不是此用户创建则要在表的前面加上其所有者,否则将会提示没有此表,wujy就是这表的所有者

delete from wujy.Db\_Tables where ID=14

(3):连接Oracle一直报：ORA-12154：TNS：无法解析指定的连接标识符

这个问题是因为我电脑里面装的几个版本的oracle客户端，我把连接的语句配置在9i版本上（tnsnames），却一直用10G的Oracle Sql\*Plus去连接，肯定会报错的；

若是用PL/SQL Developer去连时也会报这个错误，要在"工具"--"首选项"--"连接"--"Oracle 主目录名(自动检测为空)"选择相应的版本

(4):我们平常都是运用CodeSmithGenerator来进行代码的生成;创建Oracle连接语句如下(一些参数自行替换):

Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.12.15)(PORT=1521))(CONNECT\_DATA=(SERVICE\_NAME=Orcl)));User Id=system;Password=123456;

[copycode.gif]: /images/20220513/8925f2c23ea041ccb925640907ee0426.png
[30114535-afa57159eef44744afa7bd5ea839c749.png]: /images/20220513/baa2d2940ef1448890fc999952a813dd.png