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

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

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

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

![20171031115637580][]

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

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

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

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

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

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

create or replace
    procedure padduser(v_ID IN NUMBER, v_NAME IN VARCHAR2) is
    begin
          insert into tb_user(userid, username) values(v_ID, v_NAME);
    end padduser;

调用：

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

3.执行SQL语句,返回DataSet

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

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

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

create or replace
    procedure Demo(data out sys_refcursor) as
    begin
       open data for select* from tb_user;
    end Demo;

调用：

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

附MyOracleDBHelper.cs:

public class MyOracleDBHelper
        {
            //private static readonly string OracleConnStr = ConfigurationManager.AppSettings["TEST_OracleConnStr"];
            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;";
    
            /// <summary>
            /// 执行SQL语句
            /// </summary>
            /// <param name="strSql"></param>
            /// <returns>返回受影响的行数</returns>
            public int ExecuteNonQuery(string strSql)
            {
                int result = 0;
                using (OracleConnection con=new OracleConnection (OracleConnStr))
                {
                    if (con.State == System.Data.ConnectionState.Closed)
                        con.Open();
                    using (OracleCommand cmd = new OracleCommand(strSql, con))
                    {
                        result = cmd.ExecuteNonQuery();
                    }
    
                    con.Close();
                }
                return result;
            }
    
            /// <summary>
            /// 执行存储过程
            /// </summary>
            /// <param name="strProcedure"></param>
            /// <param name="param"></param>
            /// <returns>返回受影响的行数</returns>
            public int ExecuteNonQuery(string strProcedure, OracleParameter[] param)
            {
                int result = 0;
                using (OracleConnection con = new OracleConnection(OracleConnStr))
                {
                    if (con.State == System.Data.ConnectionState.Closed)
                        con.Open();
                    using (OracleCommand cmd=new OracleCommand())
                    {
                        cmd.CommandType = System.Data.CommandType.StoredProcedure;
                        cmd.CommandText = strProcedure;
                        cmd.Connection = con;
    
                        foreach (var item in param)
                        {
                            cmd.Parameters.Add(item);
                        }
    
                        result = cmd.ExecuteNonQuery();
                    }
    
                    con.Close();
                }
                return result;
            }
    
            /// <summary>
            /// 执行SQL语句
            /// </summary>
            /// <param name="strSql"></param>
            /// <returns>返回DataSet 结果集</returns>
            public DataSet ExecuteDataSet(string strSql)
            {
                using (DataSet ds = new DataSet())
                {
                    using (OracleConnection con = new OracleConnection(OracleConnStr))
                    {
                        if (con.State == ConnectionState.Closed)
                            con.Open();
                        using (OracleCommand cmd = new OracleCommand(strSql, con))
                        {
                            using (OracleDataAdapter sda = new OracleDataAdapter())
                            {
                                sda.SelectCommand = cmd;
                                sda.Fill(ds);
                            }
                        }
    
                        con.Close();
                    }
    
                    return ds;
                }
            }
    
            /// <summary>
            /// 执行存储过程
            /// </summary>
            /// <param name="strProcedure"></param>
            /// <param name="param"></param>
            /// <returns>返回DataSet 结果集</returns>
            public DataSet ExecuteDataSet(string strProcedure, OracleParameter[] param)
            {
                using (DataSet ds=new DataSet ())
                {
                    using (OracleConnection con = new OracleConnection(OracleConnStr))
                    {
                        if (con.State == ConnectionState.Closed)
                            con.Open();
    
                        using (OracleCommand cmd=new OracleCommand ())
                        {
                            cmd.CommandType = CommandType.StoredProcedure;
                            cmd.CommandText = strProcedure;
                            cmd.Connection = con;
    
                            foreach (var item in param)
                            {
                                cmd.Parameters.Add(item);
                            }
    
                            using (OracleDataAdapter dap=new OracleDataAdapter ())
                            {
                                dap.SelectCommand = cmd;
                                dap.Fill(ds);
                            }
                        }
    
                        con.Close();
                    }
    
                    return ds;
                }
            }
        }

附程序代码:

class Program
        {
            static void Main(string[] args)
            {
                try
                {
                    //执行sql 语句返回受影响的行数
                    new MyOracleDBHelper().ExecuteNonQuery("insert into tb_user(userid,username) values (2,'张三');");
    
                    //执行存储过程，返回受影响的行数
                    //create or replace
                    //procedure padduser(v_ID IN NUMBER, v_NAME IN VARCHAR2) is
                    //begin
                    //    insert into tb_user(userid, username) values(v_ID, v_NAME);
                    //end padduser;
    
                    OracleParameter[] param1 = new OracleParameter[2];
                    param1[0] = new OracleParameter("v_ID", 6);
                    param1[1] = new OracleParameter("v_NAME", "test123456");
                    new MyOracleDBHelper().ExecuteNonQuery("padduser", param1);
    
    
                    //执行SQL语句,返回DataSet
                    DataSet ds1 = new MyOracleDBHelper().ExecuteDataSet("select USERID,USERNAME from TB_USER t ORDER BY t.userid asc");
                    foreach (DataRow item in ds1.Tables[0].Rows)
                    {
                        System.Console.WriteLine(string.Format("{0}:{1}", item["USERID"].ToString(), item["USERNAME"].ToString()));
                    }
    
                    //执行存储过程,返回DataSet
                    //create or replace
                    //procedure Demo(data out sys_refcursor) as
                    //begin
                    //  open data for select* from tb_user;
                    //end Demo;
                    OracleParameter[] param2 = new OracleParameter[1];
                    param2[0] = new OracleParameter("data", OracleType.Cursor);
                    param2[0].Direction = ParameterDirection.Output;
                    DataSet ds2 = new MyOracleDBHelper().ExecuteDataSet("Demo", param2);
                    foreach (DataRow item in ds2.Tables[0].Rows)
                    {
                        System.Console.WriteLine(string.Format("{0}:{1}", item["USERID"].ToString(), item["USERNAME"].ToString()));
                    }
    
                }
                catch (Exception ex)
                {
                    System.Console.WriteLine(ex.Message);
                }
    
                System.Console.Read();
            }
        }

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

[20171031115637580]: /images/20220606/6c674959bafd42d29d76b6bfc45db183.png