c#访问Oracle数据库，执行数据库操作

ゝ一纸荒年。 2022-06-06 05:12 476阅读 0赞

第一步：应用服务器安装Oracle11g客户端

第二步:在Oracle数据库中新增表：

20171031115637580

第三步，在C#程序中访问数据库进行数控操作：

在程序中引用：System.Data.OracleClient

1.执行sql 语句返回受影响的行数：

  1. //执行sql 语句返回受影响的行数
  2. new MyOracleDBHelper().ExecuteNonQuery("insert into tb_user(userid,username) values (2,'张三');");

2.执行存储过程，返回受影响的行数

在Oracle数据库中新增存储过程：

  1. create or replace
  2. procedure padduser(v_ID IN NUMBER, v_NAME IN VARCHAR2) is
  3. begin
  4.       insert into tb_user(userid, username) values(v_ID, v_NAME);
  5. end padduser;

调用：

  1. OracleParameter[] param1 = new OracleParameter[2];
  2. param1[0] = new OracleParameter("v_ID", 6);
  3. param1[1] = new OracleParameter("v_NAME", "test123456");
  4. new MyOracleDBHelper().ExecuteNonQuery("padduser", param1);

3.执行SQL语句,返回DataSet

  1. DataSet ds1 = new MyOracleDBHelper().ExecuteDataSet("select USERID,USERNAME from TB_USER t ORDER BY t.userid asc");
  2. foreach (DataRow item in ds1.Tables[0].Rows)
  3. {
  4.     System.Console.WriteLine(string.Format("{0}:{1}", item["USERID"].ToString(), item["USERNAME"].ToString()));
  5. }

4.执行存储过程,返回DataSet

在Oracle数据库中新增存储过程：

  1. create or replace
  2. procedure Demo(data out sys_refcursor) as
  3. begin
  4.    open data for select* from tb_user;
  5. end Demo;

调用：

  1. OracleParameter[] param2 = new OracleParameter[1];
  2. param2[0] = new OracleParameter("data", OracleType.Cursor);
  3. param2[0].Direction = ParameterDirection.Output;
  4. DataSet ds2 = new MyOracleDBHelper().ExecuteDataSet("Demo", param2);
  5. foreach (DataRow item in ds2.Tables[0].Rows)
  6. {
  7.     System.Console.WriteLine(string.Format("{0}:{1}", item["USERID"].ToString(), item["USERNAME"].ToString()));
  8. }

附MyOracleDBHelper.cs:

  1. public class MyOracleDBHelper
  2.     {
  3.         //private static readonly string OracleConnStr = ConfigurationManager.AppSettings["TEST_OracleConnStr"];
  4.         private static readonly string OracleConnStr = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.217)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)));Persist Security Info=True;User ID=system;Password=System123456;";
  6.         /// <summary>
  7.         /// 执行SQL语句
  8.         /// </summary>
  9.         /// <param name="strSql"></param>
  10.         /// <returns>返回受影响的行数</returns>
  11.         public int ExecuteNonQuery(string strSql)
  12.         {
  13.             int result = 0;
  14.             using (OracleConnection con=new OracleConnection (OracleConnStr))
  15.             {
  16.                 if (con.State == System.Data.ConnectionState.Closed)
  17.                     con.Open();
  18.                 using (OracleCommand cmd = new OracleCommand(strSql, con))
  19.                 {
  20.                     result = cmd.ExecuteNonQuery();
  21.                 }
  23.                 con.Close();
  24.             }
  25.             return result;
  26.         }
  28.         /// <summary>
  29.         /// 执行存储过程
  30.         /// </summary>
  31.         /// <param name="strProcedure"></param>
  32.         /// <param name="param"></param>
  33.         /// <returns>返回受影响的行数</returns>
  34.         public int ExecuteNonQuery(string strProcedure, OracleParameter[] param)
  35.         {
  36.             int result = 0;
  37.             using (OracleConnection con = new OracleConnection(OracleConnStr))
  38.             {
  39.                 if (con.State == System.Data.ConnectionState.Closed)
  40.                     con.Open();
  41.                 using (OracleCommand cmd=new OracleCommand())
  42.                 {
  43.                     cmd.CommandType = System.Data.CommandType.StoredProcedure;
  44.                     cmd.CommandText = strProcedure;
  45.                     cmd.Connection = con;
  47.                     foreach (var item in param)
  48.                     {
  49.                         cmd.Parameters.Add(item);
  50.                     }
  52.                     result = cmd.ExecuteNonQuery();
  53.                 }
  55.                 con.Close();
  56.             }
  57.             return result;
  58.         }
  60.         /// <summary>
  61.         /// 执行SQL语句
  62.         /// </summary>
  63.         /// <param name="strSql"></param>
  64.         /// <returns>返回DataSet 结果集</returns>
  65.         public DataSet ExecuteDataSet(string strSql)
  66.         {
  67.             using (DataSet ds = new DataSet())
  68.             {
  69.                 using (OracleConnection con = new OracleConnection(OracleConnStr))
  70.                 {
  71.                     if (con.State == ConnectionState.Closed)
  72.                         con.Open();
  73.                     using (OracleCommand cmd = new OracleCommand(strSql, con))
  74.                     {
  75.                         using (OracleDataAdapter sda = new OracleDataAdapter())
  76.                         {
  77.                             sda.SelectCommand = cmd;
  78.                             sda.Fill(ds);
  79.                         }
  80.                     }
  82.                     con.Close();
  83.                 }
  85.                 return ds;
  86.             }
  87.         }
  89.         /// <summary>
  90.         /// 执行存储过程
  91.         /// </summary>
  92.         /// <param name="strProcedure"></param>
  93.         /// <param name="param"></param>
  94.         /// <returns>返回DataSet 结果集</returns>
  95.         public DataSet ExecuteDataSet(string strProcedure, OracleParameter[] param)
  96.         {
  97.             using (DataSet ds=new DataSet ())
  98.             {
  99.                 using (OracleConnection con = new OracleConnection(OracleConnStr))
  100.                 {
  101.                     if (con.State == ConnectionState.Closed)
  102.                         con.Open();
  104.                     using (OracleCommand cmd=new OracleCommand ())
  105.                     {
  106.                         cmd.CommandType = CommandType.StoredProcedure;
  107.                         cmd.CommandText = strProcedure;
  108.                         cmd.Connection = con;
  110.                         foreach (var item in param)
  111.                         {
  112.                             cmd.Parameters.Add(item);
  113.                         }
  115.                         using (OracleDataAdapter dap=new OracleDataAdapter ())
  116.                         {
  117.                             dap.SelectCommand = cmd;
  118.                             dap.Fill(ds);
  119.                         }
  120.                     }
  122.                     con.Close();
  123.                 }
  125.                 return ds;
  126.             }
  127.         }
  128.     }

附程序代码:

  1. class Program
  2.     {
  3.         static void Main(string[] args)
  4.         {
  5.             try
  6.             {
  7.                 //执行sql 语句返回受影响的行数
  8.                 new MyOracleDBHelper().ExecuteNonQuery("insert into tb_user(userid,username) values (2,'张三');");
  10.                 //执行存储过程，返回受影响的行数
  11.                 //create or replace
  12.                 //procedure padduser(v_ID IN NUMBER, v_NAME IN VARCHAR2) is
  13.                 //begin
  14.                 //    insert into tb_user(userid, username) values(v_ID, v_NAME);
  15.                 //end padduser;
  17.                 OracleParameter[] param1 = new OracleParameter[2];
  18.                 param1[0] = new OracleParameter("v_ID", 6);
  19.                 param1[1] = new OracleParameter("v_NAME", "test123456");
  20.                 new MyOracleDBHelper().ExecuteNonQuery("padduser", param1);
  23.                 //执行SQL语句,返回DataSet
  24.                 DataSet ds1 = new MyOracleDBHelper().ExecuteDataSet("select USERID,USERNAME from TB_USER t ORDER BY t.userid asc");
  25.                 foreach (DataRow item in ds1.Tables[0].Rows)
  26.                 {
  27.                     System.Console.WriteLine(string.Format("{0}:{1}", item["USERID"].ToString(), item["USERNAME"].ToString()));
  28.                 }
  30.                 //执行存储过程,返回DataSet
  31.                 //create or replace
  32.                 //procedure Demo(data out sys_refcursor) as
  33.                 //begin
  34.                 //  open data for select* from tb_user;
  35.                 //end Demo;
  36.                 OracleParameter[] param2 = new OracleParameter[1];
  37.                 param2[0] = new OracleParameter("data", OracleType.Cursor);
  38.                 param2[0].Direction = ParameterDirection.Output;
  39.                 DataSet ds2 = new MyOracleDBHelper().ExecuteDataSet("Demo", param2);
  40.                 foreach (DataRow item in ds2.Tables[0].Rows)
  41.                 {
  42.                     System.Console.WriteLine(string.Format("{0}:{1}", item["USERID"].ToString(), item["USERNAME"].ToString()));
  43.                 }
  45.             }
  46.             catch (Exception ex)
  47.             {
  48.                 System.Console.WriteLine(ex.Message);
  49.             }
  51.             System.Console.Read();
  52.         }
  53.     }

C#针对Oracle数据库的简单常见操作完成！

发表评论

表情：
评论列表 （有 0 条评论，476人围观）

还没有评论，来说两句吧...

相关阅读

    相关 Oracle数据库-02 操作数据库

    一、创建表空间、自定义用户管理 1、创建表空间 （1）什么是表空间 Oracle数据库包含物理结构和逻辑结构。 数据库的物理结构是指构成数据库的一组操作系统文件。

    悠悠悠悠/ 0 赞/ 1096 阅读