CSharp: Oracle Stored Procedure query table

oracle sql script:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

CREATE OR REPLACE PROCEDURE procSelectSchool(     paramSchoolId IN char,     p_cursor OUT SYS_REFCURSOR ) AS BEGIN     OPEN p_cursor FOR         SELECT *         FROM School         WHERE SchoolId = paramSchoolId; END procSelectSchool; /    -- 查询所有 CREATE OR REPLACE PROCEDURE SelectSchoolAll(     p_cursor OUT SYS_REFCURSOR ) AS BEGIN     OPEN p_cursor FOR         SELECT *         FROM School; END SelectSchoolAll; /

　　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56

/// <summary> /// 查询存储过程 /// 20241225 /// </summary> /// <param name="sql">存储过程名称</param> /// <param name="cmdType"></param> /// <param name="pCursor">游标</param> /// <param name="sqlParams"></param> /// <returns></returns> public static OracleDataReader GetReaderCursor(string sql, CommandType cmdType, OracleParameter pCursor, params OracleParameter[] sqlParams) {     OracleCommand cmd = new OracleCommand();     cmd.CommandType = cmdType;     cmd.CommandText = sql;     cmd.CommandTimeout = 1000;//     if (sqlParams != null)         cmd.Parameters.AddRange(sqlParams);     cmd.Parameters.Add(pCursor);     OracleConnection conn = GetConnection(true);     cmd.Connection = conn;     cmd.ExecuteNonQuery();     return ((OracleRefCursor)pCursor.Value).GetDataReader(); }     /// <summary> /// 查询存储过程 /// </summary> /// <param name="sql">存储过程名称</param> /// <param name="cmdType"></param> /// <param name="pCursor">游标</param> /// <param name="sqlParams"></param> /// <returns></returns> public static DataTable GetDataTableCursor(string sql, CommandType cmdType, OracleParameter pCursor, params OracleParameter[] sqlParams) {     DataTable dt = new DataTable();     OracleCommand cmd = new OracleCommand();     cmd.CommandType = cmdType;     cmd.CommandText = sql;     cmd.CommandTimeout = 1000;//     if (sqlParams != null)         cmd.Parameters.AddRange(sqlParams);     cmd.Parameters.Add(pCursor);     OracleConnection conn = GetConnection(true);     cmd.Connection = conn;     // 使用OracleDataAdapter来填充DataSet     using (OracleDataAdapter adapter = new OracleDataAdapter(cmd))     {         DataSet dataSet = new DataSet();         // 你可以指定一个表名，也可以不指定，让系统自动生成一个表名         adapter.Fill(dataSet, "ds");         dt = dataSet.Tables[0];       }     return dt; }

　　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110

///<summary>    ///存储过程  查询记录    ///https://docs.oracle.com/en/database/oracle/oracle-data-access-components/19.3.2/odpnt/extenBoth.html    ///https://github.com/oracle/dotnet-db-samples/ ///</summary> ///<param name="schoolId">输入参数:SchoolId</param> ///<returns>返回SchoolInfo</returns> public SchoolInfo SelectSchool(string schoolId) {     SchoolInfo school = null;     try     {            //添加输入参数            OracleParameter par =new OracleParameter("paramSchoolId", OracleDbType.NChar); // 参数和函数名，都要用小写  OracleDbType.NChar            par.Value=schoolId;            // 添加输出参数（REF CURSOR）            OracleParameter p_cursor = new OracleParameter            {                ParameterName = "p_cursor",                OracleDbType = OracleDbType.RefCursor,                Direction = ParameterDirection.Output            };                         using (OracleDataReader reader = OracleHelper.GetReaderCursor(databaserole + "procSelectSchool", CommandType.StoredProcedure, p_cursor,par))         {             if (reader.Read())             {                 school = new SchoolInfo();                 school.SchoolId =(!DBNull.Equals(reader["SchoolId"],null))? (string) reader["SchoolId"].ToString():"";                 school.SchoolName =(!DBNull.Equals(reader["SchoolName"],null))? (string) reader["SchoolName"].ToString():"";                 school.SchoolTelNo =(!DBNull.Equals(reader["SchoolTelNo"],null))? (string) reader["SchoolTelNo"].ToString():"";                               }         }     }     catch (OracleException ex)     {         throw ex;     }     return school; }      ///<summary>    ///存储过程  查询所有记录    ///</summary>    ///<param name="schoolId">无输入参数</param>    ///<returns>返回表所有记录(List)SchoolInfo</returns>    public List<SchoolInfo> SelectSchoolAll() {     List<SchoolInfo> list = new List<SchoolInfo>();     SchoolInfo school = null;     try     {            // 添加输出参数（REF CURSOR）            OracleParameter p_cursor = new OracleParameter            {                ParameterName = "p_cursor",                OracleDbType = OracleDbType.RefCursor,                Direction = ParameterDirection.Output            };              using (OracleDataReader reader = OracleHelper.GetReaderCursor(databaserole + "procSelectSchoolAll", CommandType.StoredProcedure, p_cursor, null))         {             while (reader.Read())             {                 school = new SchoolInfo();                 school.SchoolId =(!DBNull.Equals(reader["SchoolId"],null))? (string) reader["SchoolId"].ToString():"";                 school.SchoolName =(!DBNull.Equals(reader["SchoolName"],null))? (string) reader["SchoolName"].ToString():"";                 school.SchoolTelNo =(!DBNull.Equals(reader["SchoolTelNo"],null))? (string) reader["SchoolTelNo"].ToString():"";                 list.Add(school);                               }         }     }     catch (OracleException ex)     {         throw ex;     }     return list; } ///<summary> ///存储过程  查询所有记录 ///</summary> ///<param name="schoolId">无输入参数</param> ///<returns>返回(DataTable)School表所有记录</returns> public DataTable SelectSchoolDataTableAll() {     DataTable dt = new DataTable();     try     {            // 添加输出参数（REF CURSOR）            OracleParameter p_cursor = new OracleParameter            {                ParameterName = "p_cursor",                OracleDbType = OracleDbType.RefCursor,                Direction = ParameterDirection.Output            };              using (DataTable reader = OracleHelper.GetDataTableCursor(databaserole + "SelectSchoolAll", CommandType.StoredProcedure, p_cursor, null))         {             dt = reader;                                         }     }     catch (OracleException ex)     {         throw ex;     }     return dt; }

　　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

/// <summary> /// /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void Form1_Load(object sender, EventArgs e) {     try     {         SchoolBLL bLL = new SchoolBLL();           this.dataGridView1.DataSource = bLL.SelectSchoolDataTableAll();         SchoolInfo info = bLL.SelectSchool("U0002");         if (info != null)         {             this.txtId.Text = info.SchoolId;             this.txtName.Text = info.SchoolName;             this.txtTel.Text = info.SchoolTelNo;           }     }     catch (Exception ex)     {         ex.Message.ToString();     } }