CSharp: Oracle Stored Procedure query table

存储过程查询postgreSQL,Oracle 和sql server,Mysql 有区别。程序调用也是有区别。

oracle sql script:

CREATE OR REPLACE PROCEDURE procSelectSchool(paramSchoolId IN char,p_cursor OUT SYS_REFCURSOR
) AS
BEGINOPEN p_cursor FORSELECT *FROM SchoolWHERE SchoolId = paramSchoolId;
END procSelectSchool;
/-- 查询所有
CREATE OR REPLACE PROCEDURE SelectSchoolAll(p_cursor OUT SYS_REFCURSOR
) AS
BEGINOPEN p_cursor FORSELECT *FROM School;
END SelectSchoolAll;
/

        /// <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来填充DataSetusing (OracleDataAdapter adapter = new OracleDataAdapter(cmd)){DataSet dataSet = new DataSet();// 你可以指定一个表名，也可以不指定，让系统自动生成一个表名adapter.Fill(dataSet, "ds");dt = dataSet.Tables[0];}return dt;}

DAL:

       ///<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.NCharpar.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;}

调用：

    /// <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();}}

输出：