C# 连接Oracle，并调用存储过程（存在返回值），C# 调用sql存储过程

最新推荐文章于 2021-04-15 20:56:54 发布
最新推荐文章于 2021-04-15 20:56:54 发布
阅读量266 收藏 1
点赞数
文章标签： 数据库 网络 c#
原文链接：http://www.cnblogs.com/hanke123/p/6731837.html
版权

1.获取Oracle表格信息

        public OracleHelpers(string ConnStr)
        {
            ConnectionString = ConnStr;
            conn = new OracleConnection(ConnectionString);
            error = "";
             //String connString = @"Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.210)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)));User Id=TEST;Password=TEST123"; 
             //Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.50.23)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=未知)));User ID=gadata0004;Password=gold;
        
        }
        public bool CheckConn(out string err)
        {
            try
            {
                conn.Open();
                err = "";
                return true;
            }
            catch (Exception ex)
            {
                err = ex.Message;
                return false;
            }
            finally
            {
                conn.Close();
            }
        }
        //获取表名列表
        public DataTable GetConnectionTables()
        {
            return GetDataTable("SELECT TABLE_NAME 表名 FROM USER_ALL_TABLES");
        }





        //获取表名列表
        public DataTable GetConnectionTables(string sqlStr)
        {
            return GetDataTable(sqlStr);
        }

        //获取Oracle某连接下的所有视图名
        public DataTable GetConnectionViews()
        {
            return GetDataTable("select View_Name from  user_views");
        }

        //根据sql语句返回datatable
        public DataTable GetDataTable(string sqlStr)
        {
            DataTable dt = null;
            try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                OracleDataAdapter da = new OracleDataAdapter(sqlStr, conn);

                dt = new DataTable();
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                error += "GetConnectionViews:" + ex.Message + "\r\n";
                MessageBox.Show(error);
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        /// <summary>
        /// 返回DataSet对象
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <returns>DataSet对象</returns>
        public DataSet GetDataSet(string sqlStr)
        {
            DataSet ds = null;
            try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                OracleDataAdapter da = new OracleDataAdapter(sqlStr, conn);

                ds = new DataSet();
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                error += "GetConnectionViews:" + ex.Message + "\r\n";
                MessageBox.Show(error);
            }
            finally
            {
                conn.Close();
            }
            return ds;
        }

        /// <summary>
        /// 获取表的字段名
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns>DataTable</returns>
        public DataTable GetColumns(string tableName)
        {
            //string sSql = "SELE   CT USER_TAB_COLS.COLUMN_NAME as 列名 FROM USER_TAB_COLS " 
            //+ "inner join user_col_comments on user_col_comments.TABLE_NAME = USER_TAB_COLS.TABLE_NAME "
            //+ "and user_col_comments.COLUMN_NAME = USER_TAB_COLS.COLUMN_NAME and USER_TAB_COLS.TABLE_NAME = '" + tableName + "'";
            string sSql = "select distinct COLUMN_NAME as 列名 from all_TAB_COLUMNS where table_name = '" + tableName + "'";
            return GetDataTable(sSql);


            ////获取表信息的T-SQL语句备用
            //string sql = "SELECT USER_TAB_COLS.TABLE_NAME as 表名, USER_TAB_COLS.COLUMN_NAME as 列名, "
            //    + "USER_TAB_COLS.DATA_TYPE as 数据类型, USER_TAB_COLS.DATA_LENGTH as 长度, "
            //    + "USER_TAB_COLS.NULLABLE as 是否为空, USER_TAB_COLS.COLUMN_ID as 列序号, user_col_comments.comments as 备注 "
            //    + "inner join user_col_comments on user_col_comments.TABLE_NAME = USER_TAB_COLS.TABLE_NAME "
            //    + "and user_col_comments.COLUMN_NAME = USER_TAB_COLS.COLUMN_NAME and USER_TAB_COLS.TABLE_NAME = 'T_COMPANY_DETAIL" + "'";
        }

 

2.调用 三个存储过程  增、删、改 

        public void item_update(string myitemcode,
            string myitemcode_new, string myitemname, string myitemstyle,
            string myitemnaturename, string myitemtypename, string mypositionname,
            out int intmark, out string strreason)
        {
            OracleCommand cmd = new OracleCommand();
            OracleConnection conn = new OracleConnection(ConnectionString);
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "item_Update";
                OracleParameter[] parm = new OracleParameter[9];
                parm[0] = new OracleParameter("myitemcode", OracleType.VarChar);
                parm[1] = new OracleParameter("myitemcode_new", OracleType.VarChar);
                parm[2] = new OracleParameter("myitemname", OracleType.VarChar);
                parm[3] = new OracleParameter("myitemstyle", OracleType.VarChar);
                parm[4] = new OracleParameter("myitemnaturename", OracleType.VarChar);
                parm[5] = new OracleParameter("myitemtypename", OracleType.VarChar);
                parm[6] = new OracleParameter("mypositionname", OracleType.VarChar);
                parm[7] = new OracleParameter("intmark", OracleType.Int32);
                parm[8] = new OracleParameter("strreason", OracleType.VarChar,35);
                for (int i = 0; i < parm.Length - 2; i++)
                {
                    parm[i].Direction = ParameterDirection.Input;
                }
               //指定返回参数
                parm[7].Direction = ParameterDirection.Output;
                parm[8].Direction = ParameterDirection.Output;
               //传入参数赋值
                parm[0].Value = myitemcode;
                parm[1].Value = myitemcode_new;
                parm[2].Value = myitemname;
                parm[3].Value = myitemstyle;
                parm[4].Value = myitemnaturename;
                parm[5].Value = myitemtypename;
                parm[6].Value = mypositionname;
                //将对象放到cmd里面
                for (int i = 0; i < parm.Length; i++)
                {
                    cmd.Parameters.Add(parm[i]);
                }
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                cmd.Connection = conn;
                cmd.ExecuteNonQuery();

                //返回参数
                intmark = Convert.ToInt32(parm[7].Value);//res
                strreason = parm[8].Value.ToString();//err
            }
            catch
            {

                intmark = 3;
                strreason = "";
            }

            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Dispose();
                cmd.Parameters.Clear();
                cmd.Dispose();
            }
        }
        public void item_deleted(string itemcode, out int intmark, out string strreason)
        {
            OracleCommand cmd = new OracleCommand();
            OracleConnection conn = new OracleConnection(ConnectionString);
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "item_deleted";
                OracleParameter[] parm = new OracleParameter[3];
                parm[0] = new OracleParameter("itemcode", OracleType.VarChar);
                parm[1] = new OracleParameter("intmark", OracleType.Int32);
                parm[2] = new OracleParameter("strreason", OracleType.VarChar,35);
                for (int i = 0; i < parm.Length - 2; i++)
                {
                    parm[i].Direction = ParameterDirection.Input;
                }
                parm[1].Direction = ParameterDirection.Output;
                parm[2].Direction = ParameterDirection.Output;

                parm[0].Value = itemcode;
                for (int i = 0; i < parm.Length; i++)
                {
                    cmd.Parameters.Add(parm[i]);
                }
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                cmd.Connection = conn;
                cmd.ExecuteNonQuery();

                //返回参数
                intmark = Convert.ToInt32(parm[1].Value);//res
                strreason = parm[2].Value.ToString();//err
            }
            catch
            {
                intmark = 3;
                strreason = "";
            }

            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Dispose();
                cmd.Parameters.Clear();
                cmd.Dispose();
            }
        }

        public void item_insert(string myitemcode, string myitemname, string myitemstyle, string myitemnaturename,
            string myitemtypename, string mypositionname, string myitemunitgroupname, string myunitname, string mycustomname0, string mycustomname2,
            out int intmark, out string strreason)
        {
            //ConnectionString
            OracleCommand cmd = new OracleCommand();
            OracleConnection conn = new OracleConnection(ConnectionString);
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "item_insert";
                OracleParameter[] parm = new OracleParameter[12];
                parm[0] = new OracleParameter("myitemcode", OracleType.VarChar);
                parm[1] = new OracleParameter("myitemname", OracleType.VarChar);
                parm[2] = new OracleParameter("myitemstyle", OracleType.VarChar);
                parm[3] = new OracleParameter("myitemnaturename", OracleType.VarChar);
                parm[4] = new OracleParameter("myitemtypename", OracleType.VarChar);
                parm[5] = new OracleParameter("mypositionname", OracleType.VarChar);
                parm[6] = new OracleParameter("myitemunitgroupname", OracleType.VarChar);
                parm[7] = new OracleParameter("myunitname", OracleType.VarChar);
                parm[8] = new OracleParameter("mycustomname0", OracleType.VarChar);//out                                                    //out
                parm[9] = new OracleParameter("mycustomname2", OracleType.VarChar);//out
                intmark = 3;
                strreason = "";
                parm[10] = new OracleParameter("intmark", OracleType.Int32);
                parm[11] = new OracleParameter("strreason", OracleType.VarChar,35);
                for (int i = 0; i < parm.Length - 2; i++)
                {
                    parm[i].Direction = ParameterDirection.Input;
                }
                parm[10].Direction = ParameterDirection.Output;
                parm[11].Direction = ParameterDirection.Output;

                parm[0].Value = myitemcode;
                parm[1].Value = myitemname;
                parm[2].Value = myitemstyle;
                parm[3].Value = myitemnaturename;
                parm[4].Value = myitemtypename;
                parm[5].Value = mypositionname;
                parm[6].Value = myitemunitgroupname;
                parm[7].Value = myunitname;
                parm[8].Value = mycustomname0;
                parm[9].Value = mycustomname2;

                for (int i = 0; i < parm.Length; i++)
                {
                    cmd.Parameters.Add(parm[i]);
                }
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                cmd.Connection = conn;
                cmd.ExecuteNonQuery();

                //返回参数
                intmark = Convert.ToInt32(parm[10].Value);//res
                strreason = parm[11].Value.ToString();//err
            }
            catch
            {
                intmark = 3;
                strreason = "";
            }

            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Dispose();
                cmd.Parameters.Clear();
                cmd.Dispose();
            }

        }

3.三个存储过程整合成一个

 

 

    public void OrecalNew(string CunChuName, OracleParameter[] parm, int OutCount, out  List<string> Outlist)
        {
            OracleCommand cmd = new OracleCommand();
            OracleConnection conn = new OracleConnection(ConnectionString);
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = CunChuName;
                for (int i = 0; i < parm.Length; i++)
                {
                    cmd.Parameters.Add(parm[i]);
                }
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                cmd.Connection = conn;
                cmd.ExecuteNonQuery();

                //返回参数
                if (OutCount != 0)
                {
                    List<string> listOut = new List<string>();
                    for (int a = parm.Length - OutCount; a < parm.Length; a++)
                    {
                        listOut.Add(parm[a].Value.ToString().Trim());
                    }
                    Outlist = listOut;
                }
                else
                {
                    Outlist = new List<string>();
                }
            }
            catch
            {
                Outlist = new List<string>();
            }

            finally
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Dispose();
                cmd.Parameters.Clear();
                cmd.Dispose();
            }

        }

 4.C# 调取本地SQL server  存储过程

注释：C#  在调取SQL存储过程时，与调取Oracle存储过程完全一致

SQL 存储过程

ALTER PROCEDURE [dbo].[Get_Data]    
        @QQ int, 
        @WW int, 
        @EE VARCHAR(50), 
        @RR VARCHAR(50), 
        @TT int out,
        @YY int out
        as 
        begin     
         set  @TT = @QQ * @WW 
         set  @YY = @WW
     end

调取

        public void Begin()
        {
            SqlConnection conn = new SqlConnection("server=.;uid=sa;pwd=123;database=DB_Config");
            //建立一个空的SqlCommand对象
            SqlCommand comm = new SqlCommand();
            //制定调用类型为存储过程
            comm.CommandType = CommandType.StoredProcedure;
            //存储过程的名称
            comm.CommandText = "Get_Data";
            //定义数组
            SqlParameter[] parm = new SqlParameter[6];
            //指定变量的类型
            parm[0] = new SqlParameter("QQ", SqlDbType.BigInt);
            parm[1] = new SqlParameter("WW", SqlDbType.BigInt);
            parm[2] = new SqlParameter("EE", SqlDbType.VarChar);
            parm[3] = new SqlParameter("RR", SqlDbType.VarChar);
            parm[4] = new SqlParameter("TT", SqlDbType.BigInt);
            parm[5] = new SqlParameter("YY", SqlDbType.BigInt);
            //设置变量为传入参数还是传出参数
            parm[0].Direction = ParameterDirection.Input;
            parm[1].Direction = ParameterDirection.Input;
            parm[2].Direction = ParameterDirection.Input;
            parm[3].Direction = ParameterDirection.Input;
            parm[4].Direction = ParameterDirection.Output;
            parm[5].Direction = ParameterDirection.Output;
            //给传入参数赋值
            parm[0].Value = TT1.Text.ToString();
            parm[1].Value = TT2.Text.ToString();
            parm[2].Value = TT3.Text.ToString();
            parm[3].Value = TT4.Text.ToString();

            //将对象放到Comm里面
            for (int a = 0; a < parm.Length; a++)
            {
                comm.Parameters.Add(parm[a]);
            }
            //执行
            if (conn.State != ConnectionState.Open)
                conn.Open();

            comm.Connection = conn;
            comm.ExecuteNonQuery();
            //执行完接收返回参数
            MessageBox.Show("信息："+ parm[4].Value +"and"+parm[5].Value);
        }


亲测成功 

 

转载于:https://www.cnblogs.com/hanke123/p/6731837.html

weixin_30512043
关注
C# oracle 存储过程中返回值
我笔记
09-17 620
存储过程 CREATE OR REPLACE PROCEDURE ADMIN.INSERT_OBJ ( OBJEFIRT_parms IN NVARCHAR2, OBJEDATT_parms IN NVARCHAR2, OBJESECT_parms IN NVARCHAR2, OBJECONTENT_parms IN NVARCHAR2, ISRELEASE_parms IN INTEGER, .
c oracle存储过程返回值,C#调用存储过程详解(带返回值、参数输入输出等)
weixin_42311427的博客
04-05 728
本文实例讲述了C#调用存储过程的方法。分享给大家供大家参考，具体如下：CREATE PROCEDURE [dbo].[GetNameById]@studentid varchar(8),@studentname nvarchar(50) OUTPUTASBEGINSELECT @studentname=studentname FROM studentWHERE studentid=@student...
C#调用存储过程详解(带返回值、参数输入输出等)
09-02
主要介绍了C#调用存储过程的方法,结合实例形式详细分析了各种常用的存储过程调用方法,包括带返回值、参数输入输出等,需要的朋友可以参考下
c#调用带参数或返回值的存储过程
qq_28018731的博客
10-19 447
CREATE PROCEDURE [dbo].[GetNameById] @studentid varchar(8), @studentname nvarchar(50) OUTPUT AS BEGIN SELECT @studentname=studentname FROM student WHERE studentid=@studentid if @...
C#中调用oracle存储过程返回数据集
03-18
C#中调用oracle存储过程返回数据集
C#调用ORACLE存储过程返回结果集及函数
lcmj520的专栏
06-01 847
C#调用ORACLE存储过程返回结果集及函数 ORACLE段： 首先在ORACLE建立PACKAGE和PACKAGE BODY，将在这里面定义函数和存储过程返回结果集。 1：建立PACKAGE： CREATE OR REPLACE package SCOTT.pk_wt is type mytype is ref cursor; procedure p_wt(mycs out mytype); function f_get(str in varchar2) retur
C#调用oracle存储过程
09-23 4998
Oracle方面１．创建Oracle过程存储create or replace procedure proce_test(paramin in varchar2,paramout out varchar2,paraminout in out varchar2)as  varparam varchar2(28);begin  varparam:=paramin;  p
C#调用ORACLE存储过程
12-22
调用 conn.Open(); OracleCommand cmd = new OracleCommand(); cmd.CommandType = CommandType.StoredProcedure;//设置cmd的类型为存储过程 cmd.CommandText = "EMP_COUNT"; OracleParameter param = new OracleParameter("paraminout"， OracleType.VarChar， 20); param.Direction = Parameter
c# 调用oracle 返回结果集,C# 调用Oracle 存储过程返回数据集 实例
weixin_30800961的博客
04-07 586
C# 调用Oracle 存储过程返回数据集 实例1. 在oracle 下创建表t_user(ID varchar(20),Name varchar(20))2. 添加数据'1' , '张三''2' , '李四'3. 创建包并且定义变量和声明存储过程CREATE OR REPLACE PACKAGE pkg_user AS--定义返回值(游标类型)TYPE myrctype IS REF CURS...
C#调用oracle方法（包括调用存储过程）
11-01
详细的记录了C#如何调用oracle以及带有存储过程输出变量的方法，适合初学者。
C#调用Oracle存储过程分页
weixin_30888413的博客
10-31 91
　　直接调用Oracle中的存储过程完成对表的分页,对代码封闭程度高,执行高效.以下为C#分页类代码,包括Oracle数据库端存储过程PLSQL代码. // 程序调用示例: //　int totalrows = 0; //总记录数//int totalpages = 0; //总页数 //　string tableName ="emp"; //Oracle表名...
oracle c# 连接字符串
weixin_30273175的博客
01-16 41
<connectionStrings> <add name="OracleConn" connectionString="Data Source=202.102.116.13/数据库主机名;User Id=111;Password=111;Integrated Security=no;" providerName="System.Data.OracleClient"/...
c#怎么调用oracle存储过程,C#调用Oracle存储过程的方法
weixin_30062415的博客
04-15 794
create or replace procedure proce_test(paramin in varchar2,paramout out varchar2,paraminout in out varchar2)asvarparam varchar2(28);beginvarparam:=paramin;paramout:=varparam|| paraminout;end;c#调用代码如下:...
C# 调用oracle存储过程
MichaelHu的专栏
12-14 1049
以下是一段C#中连接Oracle存储过程的代码:        //调用存储过程        private string CallProcdure(string data,DateTime time,string type)        ...{            OracleConnection myconn;            OracleCommand cmd;    
C#调用存储过程带输出参数或返回值
diehe5608的博客
05-26 301
CREATE PROCEDURE [dbo].[GetNameById] @studentid varchar(8), @studentname nvarchar(50) OUTPUT AS BEGIN SELECT @studentname=studentname FROM student WHERE studentid=@studentid if @@E...
C#调用带输入输出参数及结果集Oracle存储过程
weixin_42888250的博客
06-08 1005
数据库存储过程如图： winfrom后端代码 引用using Oracle.ManagedDataAccess.Client; public DataTable GetCommadDECSData(string oppType, string data) { //参数 OracleParameter[] par = new OracleParameter[] { new O
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值