你的位置:首页 > ASP.net教程

[ASP.net教程]csharp: ODP.NET,System.Data.OracleClient(.net 4.0) and System.Data.OleDb读取Oracle g 11.2.0的区别


ODP.NET:

引用:

using Oracle.DataAccess; //Oracle g 11.2.0using Oracle.DataAccess.Client;using Oracle.DataAccess.Types;//下载 http://www.oracle.com/technetwork/topics/dotnet/downloads/net-downloads-160392.html//引用:D:\app\geovindu\product\11.2.0\dbhome_1\ODP.NET\bin//用法参考////http://docs.oracle.com/cd/B28359_01/appdev.111/b28844/procedures_dot_net.htm//http://docs.oracle.com/cd/B19306_01/win.102/b14307/OracleDataAdapterClass.htm //.net 4.0//https://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm

  数据库连接字符串:

 public string connectionString = @"DATA SOURCE=oracle11g;USER ID=geovin;password=geovindu;";

  

/// <summary>  /// 20160918 涂聚文  /// Geovin Du  /// </summary>  public class BookKindListDAL : IBookKindList  {        ///<summary>    /// 追加记录    ///</summary>    ///<param name="BookKindListInfo"></param>    ///<returns></returns>    public int InsertBookKindList(BookKindListInfo bookKindList)    {      int ret = 0;      try      {        OracleParameter[] par = new OracleParameter[]{				new OracleParameter("temTypeName",OracleDbType.NVarchar2,1000),				new OracleParameter("temParent",OracleDbType.Int32,4),				};        par[0].Value = bookKindList.BookKindName;        par[1].Value = bookKindList.BookKindParent;        ret = OracleHelper.ExecuteSql("proc_Insert_BookKindList", CommandType.StoredProcedure, par);      }      catch (OracleException ex)      {        throw ex;      }      return ret;    }    /// <summary>    /// 追加记录返回    /// </summary>    /// <param name="authorList"></param>    /// <param name="authorID"></param>    /// <returns></returns>    public int InsertBookKindOutput(BookKindListInfo bookKindList, out int bookKindLID)    {      bookKindLID = 0;      int ret = 0;      try      {        OracleParameter[] par = new OracleParameter[]{				new OracleParameter("temTypeName",OracleDbType.NVarchar2,1000),        new OracleParameter("temParent",OracleDbType.Int32,4),        new OracleParameter("temId",OracleDbType.Int32,4),				};        par[0].Value = bookKindList.BookKindName;        par[1].Value = bookKindList.BookKindParent;        par[2].Direction = ParameterDirection.Output;        ret = OracleHelper.ExecuteSql("proc_Insert_BookKindOut", CommandType.StoredProcedure, par);        if (ret > 0)        {          bookKindLID =int.Parse(par[2].Value.ToString());        }      }      catch (OracleException ex)      {        throw ex;      }      return ret;    }    ///<summary>    ///修改记录    ///</summary>    ///<param name="BookKindListInfo"></param>    ///<returns></returns>    public int UpdateBookKindList(BookKindListInfo bookKindList)    {      int ret = 0;      try      {        OracleParameter[] par = new OracleParameter[]{				new OracleParameter("BookKindID",OracleDbType.Int32,4),				new OracleParameter("BookKindName",OracleDbType.NVarchar2,1000),				new OracleParameter("BookKindParent",OracleDbType.Int32,4),				};        par[0].Value = bookKindList.BookKindID;        par[1].Value = bookKindList.BookKindName;        par[2].Value = bookKindList.BookKindParent;        ret = OracleHelper.ExecuteSql("proc_Update_BookKindList", CommandType.StoredProcedure, par);      }      catch (OracleException ex)      {        throw ex;      }      return ret;    }    ///<summary>    /// 删除记录    ///</summary>    ///<param name="bookKindIDInfo"></param>    ///<returns></returns>    public bool DeleteBookKindList(int bookKindID)    {      bool ret = false;      try      {        OracleParameter par = new OracleParameter("BookKindID", bookKindID);        int temp = 0;        temp = OracleHelper.ExecuteSql("proc_Delete_BookKindList", CommandType.StoredProcedure, par);        if (temp != 0)        {          ret = true;        }      }      catch (OracleException ex)      {        throw ex;      }      return ret;    }    ///<summary>    /// 查询记录    ///</summary>    ///<param name="bookKindIDInfo"></param>    ///<returns></returns>    public BookKindListInfo SelectBookKindList(int bookKindID)    {      BookKindListInfo bookKindList = null;      try      {        OracleParameter par = new OracleParameter("BookKindID", bookKindID);        using (OracleDataReader reader = OracleHelper.GetReader("proc_Select_BookKindList", CommandType.StoredProcedure, par))        {          if (reader.Read())          {            bookKindList = new BookKindListInfo();            bookKindList.BookKindID = (!object.Equals(reader["BookKindID"], null)) ? (int)reader["BookKindID"] : 0;            bookKindList.BookKindName = (!object.Equals(reader["BookKindName"], null)) ? (string)reader["BookKindName"] : "";            bookKindList.BookKindParent = (!object.Equals(reader["BookKindParent"], null)) ? (int)reader["BookKindParent"] : 0;          }        }      }      catch (OracleException ex)      {        throw ex;      }      return bookKindList;    }    ///<summary>    /// 查询所有记录    ///</summary>    ///<returns></returns>    public List<BookKindListInfo> SelectBookKindListAll()    {      List<BookKindListInfo> list = new List<BookKindListInfo>();      BookKindListInfo bookKindList = null;      try      {        using (OracleDataReader reader = OracleHelper.GetReader("proc_Select_BookKindListAll", CommandType.StoredProcedure, null))        {          while (reader.Read())          {            bookKindList = new BookKindListInfo();            bookKindList.BookKindID = (!object.Equals(reader["BookKindID"], null)) ? (int)reader["BookKindID"] : 0;            bookKindList.BookKindName = (!object.Equals(reader["BookKindName"], null)) ? (string)reader["BookKindName"] : "";            bookKindList.BookKindParent = (!object.Equals(reader["BookKindParent"], null)) ? (int)reader["BookKindParent"] : 0;            list.Add(bookKindList);          }        }      }      catch (OracleException ex)      {        throw ex;      }      return list;    }    ///<summary>    /// 查询所有记录    ///</summary>    ///<returns></returns>    public DataTable SelectBookKindListDataTableAll()    {      DataTable dt = new DataTable();      try      {        using (DataTable reader = OracleHelper.GetTable("proc_Select_BookKindListAll", CommandType.StoredProcedure, null))        {          dt = reader;        }      }      catch (OracleException ex)      {        throw ex;      }      return dt;    }  }

  System.Data.OracleClient(.net 4.0)

引用:

using System.Collections;using System.Data;using System.Configuration;using System.Data.OracleClient;//.net 4.0//用法参考//https://msdn.microsoft.com/en-us/library/system.data.oracleclient.oracledataadapter(v=vs.110).aspx//http://blog.csdn.net/chinawn/article/details/336904//C:\Program Files\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.0\System.Data.OracleClient.dll

  数据库连接字符串:

  public string connectionString = @"Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = oracle11g)));user id=geovin;password=geovindu;Persist Security Info=True;";    

  

/// <summary>  /// 20160918 涂聚文  /// Geovin Du  /// </summary>  public class BookKindListDAL : IBookKindList  {        ///<summary>    /// 追加记录    ///</summary>    ///<param name="BookKindListInfo"></param>    ///<returns></returns>    public int InsertBookKindList(BookKindListInfo bookKindList)    {      int ret = 0;      try      {        OracleParameter[] par = new OracleParameter[]{				new OracleParameter("temTypeName",OracleType.NVarChar,1000),				new OracleParameter("temParent",OracleType.Number,4),				};        par[0].Value = bookKindList.BookKindName;        par[1].Value = bookKindList.BookKindParent;        ret = OracleHelper.ExecuteSql("proc_Insert_BookKindList", CommandType.StoredProcedure, par);      }      catch (OracleException ex)      {        throw ex;      }      return ret;    }    /// <summary>    /// 追加记录返回    /// </summary>    /// <param name="authorList"></param>    /// <param name="authorID"></param>    /// <returns></returns>    public int InsertBookKindOutput(BookKindListInfo bookKindList, out int bookKindLID)    {      bookKindLID = 0;      int ret = 0;      try      {        OracleParameter[] par = new OracleParameter[]{				new OracleParameter("temTypeName",OracleType.NVarChar,1000),        new OracleParameter("temParent",OracleType.Number,4),        new OracleParameter("temId",OracleType.Number,4),				};        par[0].Value = bookKindList.BookKindName;        par[1].Value = bookKindList.BookKindParent;        par[2].Direction = ParameterDirection.Output;        ret = OracleHelper.ExecuteSql("proc_Insert_BookKindOut", CommandType.StoredProcedure, par);        if (ret > 0)        {          bookKindLID =int.Parse(par[2].Value.ToString());        }      }      catch (OracleException ex)      {        throw ex;      }      return ret;    }    ///<summary>    ///修改记录    ///</summary>    ///<param name="BookKindListInfo"></param>    ///<returns></returns>    public int UpdateBookKindList(BookKindListInfo bookKindList)    {      int ret = 0;      try      {        OracleParameter[] par = new OracleParameter[]{				new OracleParameter("BookKindID",OracleType.Number,4),				new OracleParameter("BookKindName",OracleType.NVarChar,1000),				new OracleParameter("BookKindParent",OracleType.Number,4),				};        par[0].Value = bookKindList.BookKindID;        par[1].Value = bookKindList.BookKindName;        par[2].Value = bookKindList.BookKindParent;        ret = OracleHelper.ExecuteSql("proc_Update_BookKindList", CommandType.StoredProcedure, par);      }      catch (OracleException ex)      {        throw ex;      }      return ret;    }    ///<summary>    /// 删除记录    ///</summary>    ///<param name="bookKindIDInfo"></param>    ///<returns></returns>    public bool DeleteBookKindList(int bookKindID)    {      bool ret = false;      try      {        OracleParameter par = new OracleParameter("BookKindID", bookKindID);        int temp = 0;        temp = OracleHelper.ExecuteSql("proc_Delete_BookKindList", CommandType.StoredProcedure, par);        if (temp != 0)        {          ret = true;        }      }      catch (OracleException ex)      {        throw ex;      }      return ret;    }    ///<summary>    /// 查询记录    ///</summary>    ///<param name="bookKindIDInfo"></param>    ///<returns></returns>    public BookKindListInfo SelectBookKindList(int bookKindID)    {      BookKindListInfo bookKindList = null;      try      {        OracleParameter par = new OracleParameter("BookKindID", bookKindID);        using (OracleDataReader reader = OracleHelper.GetReader("proc_Select_BookKindList", CommandType.StoredProcedure, par))        {          if (reader.Read())          {            bookKindList = new BookKindListInfo();            bookKindList.BookKindID = (!object.Equals(reader["BookKindID"], null)) ? (int)reader["BookKindID"] : 0;            bookKindList.BookKindName = (!object.Equals(reader["BookKindName"], null)) ? (string)reader["BookKindName"] : "";            bookKindList.BookKindParent = (!object.Equals(reader["BookKindParent"], null)) ? (int)reader["BookKindParent"] : 0;          }        }      }      catch (OracleException ex)      {        throw ex;      }      return bookKindList;    }    ///<summary>    /// 查询所有记录    ///</summary>    ///<returns></returns>    public List<BookKindListInfo> SelectBookKindListAll()    {      List<BookKindListInfo> list = new List<BookKindListInfo>();      BookKindListInfo bookKindList = null;      try      {        using (OracleDataReader reader = OracleHelper.GetReader("proc_Select_BookKindListAll", CommandType.StoredProcedure, null))        {          while (reader.Read())          {            bookKindList = new BookKindListInfo();            bookKindList.BookKindID = (!object.Equals(reader["BookKindID"], null)) ? (int)reader["BookKindID"] : 0;            bookKindList.BookKindName = (!object.Equals(reader["BookKindName"], null)) ? (string)reader["BookKindName"] : "";            bookKindList.BookKindParent = (!object.Equals(reader["BookKindParent"], null)) ? (int)reader["BookKindParent"] : 0;            list.Add(bookKindList);          }        }      }      catch (OracleException ex)      {        throw ex;      }      return list;    }    ///<summary>    /// 查询所有记录    ///</summary>    ///<returns></returns>    public DataTable SelectBookKindListDataTableAll()    {      DataTable dt = new DataTable();      try      {        using (DataTable reader = OracleHelper.GetTable("proc_Select_BookKindListAll", CommandType.StoredProcedure, null))        {          dt = reader;        }      }      catch (OracleException ex)      {        throw ex;      }      return dt;    }  }

  System.Data.OleDb

 string connString = "Provider=OraOLEDB.Oracle.1;User ID=geovin;Password=geovindu;Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = oracle11g)))";      OleDbConnection conn = new OleDbConnection(connString);      try      {        conn.Open();        MessageBox.Show(conn.State.ToString());        DataTable dt = conn.GetSchema(this.comboBox1.Text.Trim());        this.dataGridView1.DataSource = dt;        this.textBox1.Text = GetColumnNames(dt);      }      catch (Exception ex)      {        MessageBox.Show(ex.Message.ToString());      }      finally      {        conn.Close();      }