Ado.net（Oracle分页）

---恢复内容开始---

1、连接Oracle数据库

　　1.1 在Oracle数据库中

//拼接sql，values中的值要加 ' '
string sql = "insert into CUSTOMER_SAPLOG values('" + e.Enterprise_Code + "','" + e.Kunner + "','" + e.Name1 + "',to_date('" + e.CreateDate + "', 'yyyy-mm-dd'),'" + e.DataFlag + "','" + e.Other1 + "','" + e.Other2 + "','" + e.Other3 + "','" + e.Other4 + "','" + e.Other5 + "', '0' ,'0' ,'', to_date('" + DateTime.Now.ToShortDateString() + "', 'yyyy-mm-dd'))";


//数据库连接帮助类
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;

public abstract class DatabaseHelper
{
    AppSettingsReader AppRead = new AppSettingsReader();

      private static readonly string connectionString = ConfigurationSettings.AppSettings["ConnectionString"];
    #region "执行简单SQL语句"
    /// <summary>
    /// 执行多条SQL语句，实现数据库事务。
    /// </summary>
    /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的OracleParameter[]）</param>
    public static void ExecuteSqlTranByHash(Dictionary<string, OracleParameter[]> SQLStringList)
    {
        using (OracleConnection conn = new OracleConnection(connectionString))
        {
            conn.Open();
            using (OracleTransaction trans = conn.BeginTransaction())
            {
                OracleCommand cmd = new OracleCommand();
                try
                {
                    //循环
                    foreach (var myDE in SQLStringList)
                    {
                        string cmdText = myDE.Key.ToString();
                        //获得键值
                        OracleParameter[] cmdParms = myDE.Value;
                        //通过键值获得值
                        PrepareCommand(cmd, conn, trans, cmdText, cmdParms);
                        int val = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }
                    //事务提交
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    throw ex;
                }
            }
        }
    }
    /// <summary>
    /// 执行SQL语句，返回影响的记录数
    /// 例如数据添加、删除、更新操作
    /// </summary>
    /// <param name="SQLString">SQL语句</param>
    /// <returns>影响的记录数</returns>
    public static int ExecuteSql(string SQLString)
    {
        using (OracleConnection connection = new OracleConnection(connectionString))
        {
            using (OracleCommand cmd = new OracleCommand(SQLString, connection))
            {
                try
                {
                    connection.Open();
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (OracleException ex)
                {
                    connection.Close();
                    throw ex;
                }
            }
        }
    }
    /// <summary>
    /// 执行多条SQL语句，实现数据库事务。
    /// 当所有的执行语句全部执行成功，没有异常后才能进行事务的提交
    /// </summary>
    /// <param name="SQLStringList">多条SQL语句</param>        
    public static void ExecuteSqlTran(ArrayList SQLStringList)
    {

        using (OracleConnection conn = new OracleConnection(connectionString))
        {
            conn.Open();
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            OracleTransaction tx = conn.BeginTransaction();
            cmd.Transaction = tx;
            try
            {
                for (int i = 0; i < SQLStringList.Count; i++)
                {
                    string strSql = SQLStringList[i].ToString();
                    if (strSql.Trim().Length > 1)
                    {
                        cmd.CommandText = strSql;
                        cmd.ExecuteNonQuery();
                    }
                }
                //事务提交
                tx.Commit();
            }
            catch (OracleException ex)
            {
                tx.Rollback();
                //事务回滚
                throw ex;
            }
        }
    }
    /// <summary>
    /// 执行带一个存储过程参数的的SQL语句。
    /// </summary>
    /// <param name="strSQL">SQL语句/或者是存储过程</param>
    /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特殊符号，可以通过这个方式添加</param>
    /// <returns>影响的记录数</returns>
    public static int ExecuteSql(string strSQL, string content)
    {
        using (OracleConnection connection = new OracleConnection(connectionString))
        {
            OracleCommand cmd = new OracleCommand(strSQL, connection);
            OracleParameter myParameter = new OracleParameter("content", SqlDbType.Image);
            myParameter.Value = content;
            cmd.Parameters.Add(myParameter);
            try
            {
                connection.Open();
                int rows = cmd.ExecuteNonQuery();
                return rows;
            }
            catch (OracleException ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
                connection.Close();
            }
        }
    }
    /// <summary>
    /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)
    /// </summary>
    /// <param name="strSQL">SQL语句</param>
    /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>
    /// <returns>影响的记录数</returns>
    public static int ExecuteSqlInsertImg(string strSQL, Byte[] fs)
    {
        using (OracleConnection connection = new OracleConnection(connectionString))
        {
            OracleCommand cmd = new OracleCommand(strSQL, connection);
            OracleParameter myParameter = new OracleParameter("fs", SqlDbType.Image);
            myParameter.Value = fs;
            cmd.Parameters.Add(myParameter);
            try
            {
                connection.Open();
                int rows = cmd.ExecuteNonQuery();
                return rows;
            }
            catch (OracleException ex)
            {
                throw ex;
            }
            finally
            {
                cmd.Dispose();
                connection.Close();
            }
        }
    }

    /// <summary>
    /// 执行一条计算查询结果语句，返回查询结果（object）。
    /// </summary>
    /// <param name="strSQL">计算查询结果语句</param>
    /// <returns>查询结果（object）</returns>
    public static object GetSingle(string strSQL)
    {
        using (OracleConnection connection = new OracleConnection(connectionString))
        {
            using (OracleCommand cmd = new OracleCommand(strSQL, connection))
            {
                try
                {
                    connection.Open();
                    object obj = cmd.ExecuteScalar();
                    //统计功能（一行一列）
                    if ((obj == null) || (obj == System.DBNull.Value))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (OracleException ex)
                {
                    connection.Close();
                    throw new Exception(ex.Message);
                }
            }
        }
    }
    /// <summary>
    /// 执行查询语句，返回OracleDataAdapter
    /// </summary>
    /// <param name="strSQL">查询语句</param>
    /// <returns>OracleDataAdapter</returns>
    public static OracleDataReader ExecuteReader(string strSQL)
    {
        OracleConnection connection = new OracleConnection(connectionString);
        OracleCommand cmd = new OracleCommand(strSQL, connection);
        try
        {
            connection.Open();
            OracleDataReader myReader = cmd.ExecuteReader();
            return myReader;
        }
        catch (OracleException ex)
        {
            connection.Close();
            throw new Exception(ex.Message);
        }
    }
    /// <summary>
    /// 执行查询语句，返回DataSet
    /// </summary>
    /// <param name="strSQL">查询语句</param>
    /// <returns>DataSet</returns>
    public static DataSet Query_ReturnDataSet(string strSQL)
    {
        using (OracleConnection connection = new OracleConnection(connectionString))
        {
            DataSet ds = new DataSet();
            try
            {
                connection.Open();
                OracleDataAdapter command = new OracleDataAdapter(strSQL, connection);
                command.Fill(ds, "ds");
            }
            catch (OracleException ex)
            {
                connection.Close();
                throw new Exception(ex.Message);
            }
            return ds;
        }
    }

    /// <summary>
    /// 执行查询语句，返回DataTable
    /// </summary>
    /// <param name="strSQL">查询语句</param>
    /// <returns>DataTable</returns>
    public static DataTable Query_ReturnDatTable_old(string strSQL)
    {      
        using (OracleConnection connection = new OracleConnection(connectionString))       
        {
            DataTable dt = new DataTable("dt");
            try
            {
                connection.Open();
                OracleDataAdapter command = new OracleDataAdapter(strSQL, connection);
                command.Fill(dt);
            }
            catch (OracleException ex)
            {
                connection.Close();
                throw new Exception(ex.Message);
            }
            return dt;
        }
    }




    ///// <summary>
    ///// 执行查询语句，返回DataTable
    ///// </summary>
    ///// <param name="strSQL">查询语句</param>
    ///// <returns>DataTable</returns>
    //public static DataTable Query_ReturnDatTable_old(string strSQL)
    //{

    //    using (OracleConnection connection = new OracleConnection("data source=KDMESDB;User Id=kdmesdb;Password=kdmesdb;"))       
    //    {
    //        DataTable dt = new DataTable();
    //        try
    //        {
    //            connection.Open();
    //            OracleDataAdapter command = new OracleDataAdapter(strSQL, connection);
    //            command.Fill(dt);
    //        }
    //        catch (OracleException ex)
    //        {
    //            connection.Close();
    //            throw new Exception(ex.Message);
    //        }
    //        return dt;
    //    }
    //}



    /// <summary>
    /// 带数据库连接参数执行查询语句，返回DataSet
    /// </summary>
    /// <param name="strSQL">查询语句</param>
    /// <returns>DataSet</returns>
    public static DataSet QueryByConn(string strSQL, string strConn)
    {
        using (OracleConnection connection = new OracleConnection(strConn))
        {
            DataSet ds = new DataSet("ds");
            try
            {
                connection.Open();
                OracleDataAdapter command = new OracleDataAdapter(strSQL, connection);
                command.Fill(ds, "ds");
            }
            catch (OracleException ex)
            {
                connection.Close();
                throw new Exception(ex.Message);
            }
            return ds;
        }
    }
    #endregion

    #region "执行带参数的SQL语句"
    /// <summary>
    /// 执行SQL语句，返回影响的记录数
    /// </summary>
    /// <param name="strSQL">SQL语句</param>
    /// <returns>影响的记录数</returns>
    public static int ExecuteSql(string strSQL, params OracleParameter[] cmdParms)
    {
        using (OracleConnection connection = new OracleConnection(connectionString))
        {
            using (OracleCommand cmd = new OracleCommand())
                try
                {
                    PrepareCommand(cmd, connection, null, strSQL, cmdParms);
                    int rows = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return rows;
                }
                catch (OracleException ex)
                {
                    throw new Exception(ex.Message);
                }
        }
    }
    /// <summary>
    /// 执行多条SQL语句，实现数据库事务。
    /// </summary>
    /// <param name="SQLStringList">SQL语句的哈希表（key为sql语句，value是该语句的OracleParameter[]）</param>
    public static void ExecuteSqlTran(Hashtable SQLStringList)
    {
        using (OracleConnection connection = new OracleConnection(connectionString))
        {
            connection.Open();
            using (OracleTransaction trans = connection.BeginTransaction())
            {
                OracleCommand cmd = new OracleCommand();
                try
                {
                    //循环
                    foreach (DictionaryEntry myDE in SQLStringList)
                    {
                        string cmdText = myDE.Key.ToString();
                        //获得键值
                        OracleParameter[] cmdParms = (OracleParameter[])myDE.Value;
                        //通过键值获得值
                        PrepareCommand(cmd, connection, trans, cmdText, cmdParms);
                        int val = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        //事务提交
                        trans.Commit();
                    }
                }
                catch (OracleException ex)
                {
                    trans.Rollback();
                    throw new Exception(ex.Message);
                }
            }
        }
    }
    /// <summary>
    /// 执行一条计算查询结果语句，返回查询结果（object）。
    /// </summary>
    /// <param name="strSQL">计算查询结果语句</param>
    /// <param name="cmdParms">查询参数</param>
    /// <returns>查询结果（object）</returns>
    public static object GetSingle(string strSQL, params OracleParameter[] cmdParms)
    {
        using (OracleConnection connection = new OracleConnection(connectionString))
        {
            using (OracleCommand cmd = new OracleCommand())
            {
                try
                {
                    PrepareCommand(cmd, connection, null, strSQL, cmdParms);
                    object obj = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    //统计功能（一行一列）
                    if ((obj == null) || (obj == System.DBNull.Value))
                    {
                        return null;
                    }
                    else
                    {
                        return obj;
                    }
                }
                catch (OracleException ex)
                {
                    throw new Exception(ex.Message);
                }

            }
        }
    }
    /// <summary>
    /// 执行查询语句，返回OracleDataAdapter
    /// </summary>
    /// <param name="strSQL">查询语句</param>
    /// <param name="cmdParms">查询参数</param>
    /// <returns>OracleDataAdapter</returns>
    public static OracleDataReader ExecuteReader(string strSQL, params OracleParameter[] cmdParms)
    {
        OracleConnection connection = new OracleConnection(connectionString);
        OracleCommand cmd = new OracleCommand();
        try
        {
            PrepareCommand(cmd, connection, null, strSQL, cmdParms);
            OracleDataReader myReader = cmd.ExecuteReader();
            cmd.Parameters.Clear();
            return myReader;
        }
        catch (OracleException ex)
        {
            throw new Exception(ex.Message);
        }
    }
    /// <summary>
    /// 执行查询语句，返回DataSet
    /// </summary>
    /// <param name="strSQL">查询语句</param>
    /// <param name="cmdParms">查询参数</param>
    /// <returns>DataSet</returns>
    public static DataSet Query(string strSQL, params OracleParameter[] cmdParms)
    {
        using (OracleConnection connection = new OracleConnection(connectionString))
        {
            OracleCommand cmd = new OracleCommand();
            PrepareCommand(cmd, connection, null, strSQL, cmdParms);

            using (OracleDataAdapter da = new OracleDataAdapter(cmd))
            {
                DataSet ds = new DataSet();
                try
                {
                    da.Fill(ds, "ds");
                    cmd.Parameters.Clear();
                }
                catch (OracleException ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }
    }
    /// <summary>
    ///在存在事务的情况下，新建数据库连接，执行sql语句，此处是关键，上面的所有功能操作都沿此而进行
    /// 此处的sql语句可能是增加、删除、更新、查询等等操作
    /// Prepare a command for execution
    /// </summary>
    /// <param name="cmd">OracleCommand对象</param>
    /// <param name="conn">OracleConnection对象</param>
    /// <param name="trans">OracleTransaction对象</param>
    /// <param name="cmdType">Cmd type e.g. 存储过程或者sql语句</param>
    /// <param name="cmdText">Command text, e.g. Select * from Products</param>
    /// <param name="cmdParms">OracleParameters to use in the command</param>
    private static void PrepareCommand(OracleCommand cmd, OracleConnection conn, OracleTransaction trans, string cmdText, OracleParameter[] cmdParms)
    {
        if (conn.State != ConnectionState.Open)
        {
            conn.Open();
        }
        cmd.Connection = conn;
        cmd.CommandText = cmdText;
        if (trans != null)
        {
            cmd.Transaction = trans;
        }
        cmd.CommandType = CommandType.Text;
        //cmdType;
        if (cmdParms != null)
        {
            foreach (OracleParameter parm in cmdParms)
            {
                cmd.Parameters.Add(parm);
            }
        }
    }
    #endregion

    #region "存储过程操作"
    /// <summary>
    /// 执行存储过程
    /// </summary>
    /// <param name="storedProcName">存储过程名</param>
    /// <param name="parameters">存储过程参数</param>
    /// <returns>OracleDataAdapter</returns>
    public static OracleDataReader RunProcedure(string strProcedureName, IDataParameter[] parameters)
    {
        OracleConnection connection = new OracleConnection(connectionString);
        OracleDataReader returnReader;
        connection.Open();
        OracleCommand command = BuildQueryCommand(connection, strProcedureName, parameters);
        command.CommandType = CommandType.StoredProcedure;
        returnReader = command.ExecuteReader();
        return returnReader;
    }
    /// <summary>
    /// 执行存储过程
    /// </summary>
    /// <param name="storedProcName">存储过程名</param>
    /// <param name="parameters">存储过程参数</param>
    /// <param name="tableName">DataSet结果中的表名</param>
    /// <returns>DataSet</returns>
    public static DataSet RunProcedure(string strProcedureName, IDataParameter[] parameters, string tableName)
    {
        
        try
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                OracleDataAdapter da = new OracleDataAdapter();
                da.SelectCommand = BuildQueryCommand(connection, strProcedureName, parameters);
                da.Fill(dataSet, tableName);
                connection.Close();
                //trans.Dispose();
                return dataSet;
            }
        }

        catch (Exception ex)
        {
            throw ex;
        }
    }
    
    /// <summary>
    /// 构建 OracleCommand 对象(用来返回一个结果集，而不是一个整数值)
    /// </summary>
    /// <param name="connection">数据库连接</param>
    /// <param name="storedProcName">存储过程名</param>
    /// <param name="parameters">存储过程参数</param>
    /// <returns>OracleCommand</returns>
    private static OracleCommand BuildQueryCommand(OracleConnection connection, string strProcedureName, IDataParameter[] parameters)
    {
        OracleCommand command = new OracleCommand(strProcedureName, connection);
        command.CommandType = CommandType.StoredProcedure;
        foreach (OracleParameter parameter in parameters)
        {
            command.Parameters.Add(parameter);
        }
        return command;
    }
    /// <summary>
    /// 执行存储过程，返回影响的行数        
    /// </summary>
    /// <param name="storedProcName">存储过程名</param>
    /// <param name="parameters">存储过程参数</param>
    /// <param name="rowsAffected">影响的行数</param>
    /// <returns></returns>
    public static int RunProcedure(string strProcedureName, IDataParameter[] parameters, ref int rowsAffected)
    {
        int result = 0;
        try
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                connection.Open();
                OracleCommand command = BuildIntCommand(connection, strProcedureName, parameters);
                OracleTransaction trans = connection.BeginTransaction(IsolationLevel.ReadCommitted);
                if (trans != null)
                {
                    command.Transaction = trans;
                    result = command.ExecuteNonQuery();
                    trans.Commit();
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        rowsAffected = result;
        return result;
    }
    /// <summary>
    /// 执行存储过程，查询，并且有输出参数    
    /// </summary>
    /// <param name="storedProcName">存储过程名</param>
    /// <param name="parameters">存储过程参数</param>
    /// <param name="rowsAffected">影响的行数</param>
    /// <returns></returns>
    public static string RunProcedureQuery(string strProcedureName, IDataParameter[] parameters, ref int rowsAffected)
    {
        string outString = "";
        try
        {
            using (OracleConnection connection = new OracleConnection(connectionString))
            {
                connection.Open();
                OracleCommand command = BuildIntCommand(connection, strProcedureName, parameters);
                OracleTransaction trans = connection.BeginTransaction(IsolationLevel.ReadCommitted);
                if (trans != null)
                {
                    command.Transaction = trans;
                    command.ExecuteNonQuery();
                    trans.Commit();
                }
                //输出参数
                outString = command.Parameters["TempID"].Value.ToString();
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
        rowsAffected = 0;
        return outString;
    }
    /// <summary>
    /// 创建 OracleCommand 对象实例(用来返回一个整数值)    
    /// </summary>
    /// <param name="storedProcName">存储过程名</param>
    /// <param name="parameters">存储过程参数</param>
    /// <returns>OracleCommand 对象实例</returns>
    private static OracleCommand BuildIntCommand(OracleConnection connection, string strProcedureName, IDataParameter[] parameters)
    {
        OracleCommand command = BuildQueryCommand(connection, strProcedureName, parameters);
        return command;
    }
    /// <summary>
    /// 执行存储过程，没有返回任何信息，如插入数据等
    /// </summary>
    /// <param name="procname"></param>
    /// <param name="cmdParms"></param>
    public static void ExecuteProc(string procname, params OracleParameter[] cmdParms)
    {
        using (OracleConnection SqlConn = new OracleConnection(connectionString))
        {
            if (SqlConn.State == ConnectionState.Closed)
            {
                SqlConn.Open();
            }
            OracleDataAdapter da = new OracleDataAdapter();
            da.SelectCommand = new OracleCommand();
            da.SelectCommand.Connection = SqlConn;
            da.SelectCommand.CommandText = procname;
            da.SelectCommand.CommandType = CommandType.StoredProcedure;
            if (cmdParms != null)
            {
                foreach (OracleParameter parm in cmdParms)
                {
                    da.SelectCommand.Parameters.Add(parm);
                }
            }
            int i = da.SelectCommand.ExecuteNonQuery();
            da.Dispose();
        }
    }
    /// <summary>
    /// 执行参数化的Sql语句,返回DataTable
    /// </summary>
    /// <param name="p_ProcedureName">sql语句</param>
    /// <param name="p_OracleParameterArray">Parm数组</param>
    /// <returns>返回DataTable</returns>
    public static DataTable ExeParameDataTable(string p_ProcedureName, OracleParameter[] p_OracleParameterArray)
    {
        try
        {
            using (OracleConnection SqlConn = new OracleConnection(connectionString))
            {
                if (SqlConn.State == ConnectionState.Closed)
                {
                    SqlConn.Open();
                }
                DataTable dt = new DataTable();
                OracleDataAdapter da = new OracleDataAdapter(p_ProcedureName, SqlConn);
                foreach (OracleParameter Sq in p_OracleParameterArray)
                {
                    da.SelectCommand.Parameters.Add(Sq);
                }
                da.Fill(dt);
                da.SelectCommand.Parameters.Clear();
                return dt;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

    /// <summary>
    /// 批量执行Command对象列表
    /// </summary>
    public static void ExecCmdListTrans(List<OracleCommand> list)
    {
        using (OracleConnection connection = new OracleConnection(connectionString))
        {
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
            OracleTransaction trans = connection.BeginTransaction();
            try
            {
                for (int i = 0; i < list.Count; i++)
                {
                    list[i].Connection = connection;
                    list[i].Transaction = trans;
                    list[i].ExecuteNonQuery();
                }
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
        }
    }
    #endregion

    #region oracle select
    /// <summary>
    /// 执行查询语句，返回DataTable
    /// </summary>
    /// <param name="strSQL">查询语句</param>
    /// <returns>DataTable</returns>
    public static DataTable Query_ReturnDatTable(string strSQL)
    {

        using (  OracleConnection connection = new OracleConnection(connectionString))
        {
            DataTable dt = new DataTable();
            try
            {
                connection.Open();
                OracleDataAdapter command = new OracleDataAdapter(strSQL, connection);
                command.Fill(dt);
            }
            catch (OracleException ex)
            {
                connection.Close();
                throw new Exception(ex.Message);
            }
            return dt;
        }
    }

    #endregion
}

SqlHepler类

<?xml version="1.0"?>
<configuration>
  <appSettings>
    <add key="ConnectionString" value="Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)
    (HOST =172.16.7.177)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = MES)));User ID=camstaruser;Password=camstaruser"/>
    <add key="ServerHost" value="172.16.7.176"/>
    <add key="ServerPort" value="2881"/>
    <add key="mUserName" value="CamstarAdmin"/>
    <add key="mPassword" value="123.abc"/>    
    <add key="Domain" value="."/>
    <add key="LogPath" value="C:\Log\"/>
    <add key="barTenderPathexe" value="C:\\Program Files (x86)\\Seagull\\BarTender Suite\\bartend.exe"/>
    <add key="BarTenderPath" value="C:\\zplprintread\\data.txt"/>
    <add key="DefaultIssueDifferenceReason" value="部分装配"/>
    <add key="DefaultFeederAdjustReason" value="接料"/>
    <add key="DefaultUOM" value="个"/>
    <add key="SpecType_Completion" value="完工"/>
    <add key="SpecType_Stock" value="线边仓"/>
    <add key="SAP_DestinationName" value="RL700"/>
    <add key="SAP_USER" value="huyadong"/>
    <add key="SAP_PASSWD" value="123321"/>
    <add key="SAP_CLIENT" value="300"/>
    <add key="SAP_LANG" value="ZH"/>
    <add key="SAP_ASHOST" value="172.16.45.234"/>
    <add key="SAP_SYSNR" value="00"/>
    <add key="SAP_MAX_POOL_SIZE" value="10"/>
    <add key="SAP_IDLE_TIMEOUT" value="10"/>
    <add key="SAP_SAPROUTER" value=""/>
  </appSettings>
  <!--
    有关 .NET 4.5 的 web.config 更改的说明，请参见 http://go.microsoft.com/fwlink/?LinkId=235367。

    可在 <httpRuntime> 标记上设置以下特性。
      <system.Web>
        <httpRuntime targetFramework="4.5" />
      </system.Web>
  -->
  <system.web>
    <!-- 
            设置 compilation debug="true" 可将调试符号插入
            已编译的页面中。但由于这会 
            影响性能，因此只在开发过程中将此值 
            设置为 true。
        -->
    <compilation debug="true" targetFramework="4.5.1"/>
    <!--
            通过 <authentication> 节可以配置 ASP.NET 用来 
            识别进入用户的
            安全身份验证模式。 
       -->
    <authentication mode="Windows"/>
    <!--
            如果在执行请求的过程中出现未处理的错误，
            则通过 <customErrors> 节可以配置相应的处理步骤。具体说来，
            开发人员通过该节可以配置
            要显示的 html 错误页
            以代替错误堆栈跟踪。

        <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
            <error statusCode="403" redirect="NoAccess.htm" />
            <error statusCode="404" redirect="FileNotFound.htm" />
        </customErrors>
        -->
    <pages controlRenderingCompatibilityVersion="4.0" clientIDMode="AutoID"/>
  </system.web>
  <!-- 
        在 Internet 信息服务 7.0 下运行 ASP.NET AJAX 需要 system.webServer
        节。对早期版本的 IIS 来说则不需要此节。
    -->
</configuration>

　　　　连接字符串：

 <connectionStrings>
    <add name="ConStr" connectionString="Data Source=(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1 )(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = XE))) ;User Id=xx;Password=123;" providerName="Oracle.DataAccess.Client" />
  </connectionStrings>


--------在WPFClient中：
<!--直接启动-->
  <!--<platform currentCulture="zh-CN" collectDevLanguages="IsDebugging">-->
    <!--用host启动,并且要用管理员启动vs-->
    <platform currentCulture="zh-CN" dataPortalProxy="SIE.DataPortal.WCF.ClientProxy, SIE">

　　　　连接方法：　

public bool RecordeCutomerLog(CustomerInfo e)
    {
        //自己拼接sql的时候，这里的values('值1', '值2')；
        //string sql = "insert into CUSTOMER_SAPLOG values('" + e.Enterprise_Code + "','" + e.Kunner + "','" + e.Name1 + "',to_date('" + e.CreateDate + "', 'yyyy-mm-dd hh24:mi:ss'),'" + e.DataFlag + "','" + e.Other1 + "','" + e.Other2 + "','" + e.Other3 + "','" + e.Other4 + "','" + e.Other5 + "', '0' ,'0' ,'', to_date('" + DateTime.Now.ToShortDateString() + "', 'yyyy-mm-dd'))";
        try
        {
            string sql = "insert into CUSTOMER_SAPLOG (enterprise_code, name1, dataflag, kunner, createdate, ishandled)
            values(:enterprise_code, :name1, :dataflag, :kunner, :createdate, :ishandled)";
            OracleParameter[] pars = {
                new OracleParameter(":enterprise_code", OracleDbType.Varchar2, 100) { Value = e.Enterprise_Code == null ? null : e.Enterprise_Code },
                new OracleParameter(":name1", e.Name1),
                //char类型可以直接赋值字符串类型
                new OracleParameter(":dataflag",OracleDbType.Char,1) {Value = e.DataFlag == null ? null : e.DataFlag},
                new OracleParameter(":createdate", OracleDbType.Date) {Value = e.CreateDate == null ? new DateTime(): DateTime.Parse(e.CreateDate)},
            };
            int count = DatabaseHelper.ExecuteSql(sql, pars);
            if (count > 0)
                return true;
            else
                return false;
        }
        catch (Exception ex)
        {
            throw new Exception("添加客户日志信息出错:" + ex.Message);
        }

//1、SqlHelper类
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OracleClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CMS.DAL
{
   public class SqlHelper
    {
        private static readonly string conStr = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="type"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public static DataTable GetTable(string sql, CommandType type, params OracleParameter[] parms)
        {
            using(OracleConnection conn = new OracleConnection(conStr))
            {
                using(OracleDataAdapter adapter = new OracleDataAdapter(sql, conn))
                {
                    adapter.SelectCommand.CommandType = type;//设置数据类型
                    if (parms != null)
                        adapter.SelectCommand.Parameters.AddRange(parms);
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);
                    return dt;
                }
            }
        }

        /// <summary>
        /// 增删改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="type"></param>
        /// <param name="pars"></param>
        /// <returns>影响的行数</returns>
        public static int ExecuteNonQuery(string sql, CommandType type, params OracleParameter[] pars)
        {
            using(OracleConnection conn = new OracleConnection(conStr))
            {
                using(OracleCommand cmd = new OracleCommand(sql, conn))
                {
                    cmd.CommandType = type;
                    if (pars != null)
                        cmd.Parameters.AddRange(pars);
                    conn.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }
    }
}

//2、Dal层========================================
 public class UserInfoDal
    {
        public UserInfo GetUserInfo(string userName, string userPwd)
        {
            string sql = "select * from UserInfo where UserName = :userName and UserPwd = :userPwd";
            OracleParameter[] pars = {
                new OracleParameter(":userName", OracleType.NVarChar, 50),
                new OracleParameter(":userPwd", OracleType.NVarChar,50)};
            pars[0].Value = userName;
            pars[1].Value = userPwd;

            //调用方法查询
            DataTable dt = SqlHelper.GetTable(sql, CommandType.Text, pars);
            UserInfo user = null;
            if(dt.Rows.Count > 0)
            {
                user = new UserInfo();
                LoadEntity(user, dt.Rows[0]); //加载一行数据
            }
            return user;
        }

        private void LoadEntity(UserInfo user, DataRow row)
        {
            user.UserId = Convert.ToInt32(row["ID"]); //数据库中不允许为空的不用判断
            //数据库中允许为空的，如果为空则给它赋值一个空字符串
            user.UserName = row["UserName"] != DBNull.Value ? row["UserName"].ToString() : string.Empty;
            user.UserPwd = row["UserPwd"] != DBNull.Value ? row["UserPwd"].ToString() : string.Empty;
            user.UserMail = row["UserMail"] != DBNull.Value ? row["UserMail"].ToString() : string.Empty;
            user.RegTime = Convert.ToDateTime(row["RegTime"]);
        }
    }

　　1.2 在其他数据库中

//1、在SqlServer数据库中
 public bool InsertAdmin(string userName, string password, string remark, string mail, int departId, int power)
    {
        string sql = "insert into S_Admin(UserName,Password,Remark,Mail,DepartId,Power)values(@UserName,@Password,@Remark,@Mail,@DepartId,@Power)";
        SqlConnection connection = new SqlConnection();
        connection.ConnectionString = "";//此处设置链接字符串
        SqlCommand command = new SqlCommand(sql, connection);
        command.Parameters.Add("@UserName",SqlDbType.NVarChar, 60).Value = userName;
        command.Parameters.Add("@Password", SqlDbType.NVarChar, 60).Value = password;
        command.Parameters.Add("@Remark", SqlDbType.NVarChar, 60).Value = remark;
        command.Parameters.Add("@Mail", SqlDbType.NVarChar, 60).Value = mail;
        command.Parameters.Add("@DepartId", SqlDbType.Int, 4).Value = departId;
        command.Parameters.Add("@Power", SqlDbType.Int, 4).Value = power;
        connection.Open();
        int rowsAffected = command.ExecuteNonQuery();
        connection.Close();
        command.Dispose();
        return rowsAffected > 0;
    }

//2、在MySql数据库中====================================
public bool InsertAdmin(string userName, string password, string remark, string mail, int departId, int power)
    {
        string sql = "insert into S_Admin(UserName,Password,Remark,Mail,DepartId,Power)values(?UserName,?Password,?Remark,?Mail,?DepartId,?Power)";
        MySqlConnection connection = new MySqlConnection();
        connection.ConnectionString = "";//此处设置链接字符串
        MySqlCommand command = new MySqlCommand(sql, connection);
        command.Parameters.Add("?UserName", MySqlDbType.VarChar, 60).Value = userName;
        command.Parameters.Add("?Password", MySqlDbType.VarChar, 60).Value = password;
        command.Parameters.Add("?Remark", MySqlDbType.VarChar, 60).Value = remark;
        command.Parameters.Add("?Mail", MySqlDbType.VarChar, 60).Value = mail;
        command.Parameters.Add("?DepartId", MySqlDbType.Int32, 4).Value = departId;
        command.Parameters.Add("?Power", MySqlDbType.Int32, 4).Value = power;
        connection.Open();
        int rowsAffected = command.ExecuteNonQuery();
        connection.Close();
        command.Dispose();
        return rowsAffected > 0;
    }

//3、在Access数据库中===============================
 public bool InsertAdmin(string userName, string password, string remark, string mail, int departId, int power)
    {
        string sql = "insert into S_Admin(UserName,Password,Remark,Mail,DepartId,Power)values(?,?,?,?,?,?)";
        OleDbConnection connection = new OleDbConnection();
        connection.ConnectionString = "";//此处设置链接字符串
        //注意下面参数的顺序一定要按照sql语句中的插入的列的顺序赋值，否则一定会报异常
        OleDbCommand command = new OleDbCommand(sql, connection);
        command.Parameters.Add("?", OleDbType.LongVarWChar, 60).Value = userName;
        command.Parameters.Add("?", OleDbType.LongVarWChar, 60).Value = password;
        command.Parameters.Add("?", OleDbType.LongVarWChar, 60).Value = remark;
        command.Parameters.Add("?", OleDbType.LongVarWChar, 60).Value = mail;
        command.Parameters.Add("?", OleDbType.Integer, 4).Value = departId;
        command.Parameters.Add("?", OleDbType.Integer, 4).Value = power;
        connection.Open();
        int rowsAffected = command.ExecuteNonQuery();
        connection.Close();
        command.Dispose();
        return rowsAffected > 0;
    }

2、Oracle数据库分页

　　2.1 Oracle分页查询三种方法

select a1.* from (select newinfo.*,rownum rn from newinfo) a1 where rn between 3 and 5;
select a1.* from (select newinfo.*,rownum rn from newinfo where rownum <=5) a1 where rn >=3;
select * from (select a1.*,rownum rn from (select * from newinfo) a1 where rownum <=17) where rn>=2;

--rownum, rowid 都是伪列，只在数据库内部存在

　　2.2 分页的其它参数

//-----------------------------Controller----------------------------------
 /// <summary>
        /// 一共有5个参数
        /// pageIndex：当前页数 
        /// pageSize：每页显示的记录数
        /// totalPageCount：总页数   【Convert.ToInt32(Math.Ceiling((double)totalCount / pageSize));】
        /// totalCount：总记录数     【sql语句来查询】
        /// start:起始条数           【start = (pageIndex - 1) * pageSize + 1;】
        /// end: 结尾条数            【end = pageIndex * pageSize;】
        /// </summary>
        /// <returns></returns>
        public ActionResult Index()
        {
            //pageIndex当前页数
            int pageIndex = Request["pageIndex"] != null ? int.Parse(Request["pageIndex"]) : 1;
            int pageSize = 5; //每页有多少条数据
            int pageCount = bll.GetTotalPage(pageSize); //总的页数

            //需要判断下当前页的大小
            pageIndex = pageIndex < 1 ? 1 : pageIndex;  
            pageIndex = pageIndex > pageCount ? pageCount : pageIndex;
            List<NewInfo> list = bll.GetNewInfoList(pageIndex, pageSize);
            ViewData["list"] = list;
            return View();
        }

//----------------------------BLL--------------------------------------
/// <summary>
        /// 数据库分页
        /// </summary>
        /// <param name="pageIndex">当前页面值</param>
        /// <param name="pageSize">每页显示的记录数</param>
        /// <returns></returns>
        public  List<NewInfo> GetNewInfoList(int pageIndex, int pageSize)
        {
            int start = (pageIndex - 1) * pageSize + 1;
            int end = pageIndex * pageSize;
            return dal.GetNewInfo(start, end);
        }

//-----------------------------DAL----------------------------------------
 public List<NewInfo> GetNewInfo(int start, int end)
        {
            string sql = "select a1.* from (select newinfo.*,rownum rn from newinfo) a1 where rn between :a and :b";
            OracleParameter[] pars = { new OracleParameter(":a", OracleType.Number) { Value = start},
                                       new OracleParameter(":b", OracleType.Number) { Value = end} };
            DataTable dt = SqlHelper.GetTable(sql, CommandType.Text, pars);
            List<NewInfo> list = null;
            if(dt.Rows.Count > 0)
            {
                list = new List<NewInfo>();
                NewInfo info = null;
                foreach(DataRow row in dt.Rows)
                {
                    info = new NewInfo();
                    LoadEntity(row, info);
                    list.Add(info);
                }
            }
            return list;
        }

        private void LoadEntity(DataRow row, NewInfo info)
        {
            info.Id = Convert.ToInt32(row["Id"]);
            info.ImagePath = row["ImagePath"] != DBNull.Value ? row["ImagePath"].ToString() : string.Empty;
            info.Title = row["Title"] != DBNull.Value ? row["Title"].ToString() : string.Empty;
            info.Msg = row["Msg"] != DBNull.Value ? row["Msg"].ToString() : string.Empty;
            info.Author = row["Author"] != DBNull.Value ? row["Author"].ToString() : string.Empty;
            info.SubDateTime = row["SubDateTime"] != DBNull.Value ? Convert.ToDateTime(row["SubDateTime"]) : new DateTime();
        }

        /// <summary>
        /// 获得总的条数
        /// </summary>
        /// <returns></returns>
        public int GetTotalCount()
        {
            string sql = "select count(*) from newinfo ";
            return SqlHelper.ExecuteScalar(sql, CommandType.Text);
        }

//--------------------------------SQLHeler----------------------------------
 private static readonly string conStr = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString;
public static DataTable GetTable(string sql, CommandType type, params OracleParameter[] parms)
        {
            using(OracleConnection conn = new OracleConnection(conStr))
            {
                using(OracleDataAdapter adapter = new OracleDataAdapter(sql, conn))
                {
                    adapter.SelectCommand.CommandType = type;//设置数据类型
                    if (parms != null)
                        adapter.SelectCommand.Parameters.AddRange(parms);
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);
                    return dt;
                }
            }
        }

 　　2.3 显示分页条

 @if (ViewData["list"] != null)
        {
            <table width="100%">
                <tr><th>编号</th><th>标题</th><th>作者</th><th>时间</th><th>详细</th><th>删除</th></tr>
                @foreach (NewInfo newInfo in (List<NewInfo>)ViewData["list"])
                {
                    <tr>
                        <td>@newInfo.Id</td>
                        <td>@newInfo.Title</td>
                        <td>@newInfo.Author</td>
                        <td>@newInfo.SubDateTime</td>
                        <td><a href="javascript:void(0)" class="details" ids="@newInfo.Id">详细</a></td>
                        <td><a href="javascript:void(0)" class="deletes" ids="@newInfo.Id">删除</a></td>
                    </tr>
                }

            </table>
            <p class="page_nav">
                @*MvcHtmlString.Create，输出结果不是html*@
                @MvcHtmlString.Create(PageBar.GetPageBar((int)ViewData["pageIndex"], (int)ViewData["pageCount"]))
            </p>
        }
        else
        {
            <span>暂无数据</span>
        }

 /// <summary>
        /// 产生页面条码
        /// </summary>
        /// <param name="pageIndex">当前页码值</param>
        /// <param name="pageCount">总的页数</param>
        /// <returns></returns>
        public static string GetPageBar(int pageIndex, int pageCount)
        {
            if (pageCount == 1) //如果总页数为1，则不显示下面的页数
                return string.Empty;
            int start = pageIndex - 5; //起始位置，前提pageSize = 10
            if (start < 1)
                start = 1;
            int end = start + 9; //终止位置
            if (end > pageCount)
                end = pageCount;
            StringBuilder sb = new StringBuilder();
            for(int i=start; i<=end; i++)
            {
                if (i == pageIndex)
                    sb.Append(i);
                else
                    sb.Append(string.Format("<a href='?pageIndex={0}'>{0}</a>", i));
            }
            return sb.ToString();
        }

3、总结

　　3.1、写sql语句的时候一定要仔细检查，出现ORA-...大多是sql语句问题。解决的办法：把sql语句复制到数据库中执行、写 简单的sql语句看是否连通数据库。

　　3.2、在DAL层对于同一个实体类，可以写复用的方法，比如LoadEntity(DataRow row , Entity e);