C# 连接Oracle，并调用存储过程（存在返回值），C# 调用sql存储过程
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); }
亲测成功
