C#访问Oracle或SqlServer数据库遍历添加参数
访问数据库层：
public void GetBaseInfo(string strB_ID, DataSet myData) { OracleCommand cmd = dbOperation.OracleGetProcedureParameter("PK_INTELLECT", "P_GetBaseInfo", new object[] { strB_ID }); cmd.CommandType = CommandType.StoredProcedure; myData = dbOperation.OracleGetProcedureData(cmd, myData, "temp"); }
SqlServer写法
public static SqlCommand SqlServerGetProcedureParameter(string DbConnectString, String ProcedureName, params object[] parameters) { String sql = string.Empty; SqlCommand command = new SqlCommand(); SqlCommand retCommand = new SqlCommand(); ProcedureName = ProcedureName.ToUpper(); sql = "select * from sys.procedures where name = '" + ProcedureName + "'"; command = new SqlCommand(sql, new SqlConnection(DbConnectString)); //command = new SqlCommand(sql, new SqlConnection(dbOperation.ConnectionString)); command.CommandType = CommandType.Text; command.Connection.Open(); SqlDataReader reader = command.ExecuteReader(); if (!reader.Read()) //未找到些名称的存储过程 { reader.Close(); command.Connection.Close(); throw new Exception("未发现名称为“" + ProcedureName + "”的存储过程!"); } else //找到此存储过程后，检察是否存在参数 { reader.Close(); retCommand = new SqlCommand(ProcedureName, new SqlConnection(DbConnectString)); retCommand.CommandType = CommandType.StoredProcedure; //select* from syscolumns where ID in (SELECT id FROM sysobjects as a WHERE OBJECTPROPERTY(id, N'IsProcedure') = 1 and id = object_id(N'[dbo].[apluphratedetailsel_sp]')) sql = "SELECT PARAMETER_NAME,DATA_TYPE,ISNULL(CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION),NUMERIC_SCALE, PARAMETER_MODE FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_SCHEMA = 'dbo' AND SPECIFIC_NAME = '" + ProcedureName + "'"; //sql = "select * from ALL_ARGUMENTS where OBJECT_NAME = '" + ProcedureName + "' and OWNER = USER and PACKAGE_NAME = '" + PackageName + "' order by SEQUENCE"; command.CommandText = sql; reader = command.ExecuteReader(); int i = 0; while (reader.Read()) { //OracleParameter para = new OracleParameter(); //para.ParameterName = reader["ARGUMENT_NAME"].ToString(); SqlParameter para = null; String paramName = reader["PARAMETER_NAME"].ToString(); string a = reader["DATA_TYPE"].ToString(); switch (reader["DATA_TYPE"].ToString().ToUpper()) { case "INT": //para = new OracleParameter(paramName, OracleDbType.Int32); para = new SqlParameter(paramName, SqlDbType.Decimal); //myj,2009-3-16,edit //para.OracleDbType = OracleDbType.Int32; para.Value = parameters[i]; break; case "VARCHAR": para = new SqlParameter(paramName, SqlDbType.VarChar); //para.OracleDbType = OracleDbType.Varchar2; para.Value = parameters[i]; String sValue = para.Value.ToString(); if (sValue == String.Empty) { para.Size = 4000; } else { para.Size = sValue.Length; } break; case "DATE": para = new SqlParameter(paramName, SqlDbType.Date); //para.OracleDbType = OracleDbType.Date; para.Value = parameters[i]; break; //case "CLOB": // if (string.IsNullOrEmpty(parameters[i].ToString())) // { // para = new SqlParameter(paramName, SqlDbType.Varchar2); // } // else // { // para = new SqlParameter(paramName, SqlDbType.Clob); // } // //para.OracleDbType = OracleDbType.Clob; // para.Value = parameters[i]; // break; //case "REF CURSOR": // para = new SqlParameter(paramName, SqlDbType.RefCursor); // //para.OracleDbType = OracleDbType.RefCursor; // break; } string b = reader["PARAMETER_MODE"].ToString(); switch (reader["PARAMETER_MODE"].ToString()) { case "IN": para.Direction = ParameterDirection.Input; break; case "OUT": para.Direction = ParameterDirection.Output; break; case "IN/OUT": para.Direction = ParameterDirection.InputOutput; break; } i++; retCommand.Parameters.Add(para); } reader.Close(); SqlServerCloseConnection(command); return retCommand; } }
oracle写法
自定义dbOperation.OracleGetProcedureParameter方法
/// <summary> /// 根据传入的“包名称”、“存储过程名称”、“参数变量值”，生成 OracleCommand 对象并放置在 dbOperation.oracleAdapter.SelectCommand 中。 /// </summary> /// <param name="PackageName">包名称</param> /// <param name="ProcedureName">存储过程名称</param> /// <param name="parameters">按定义顺序填充的参数值数组</param> /// <returns>操作是否成功</returns> public static OracleCommand OracleGetProcedureParameter(String PackageName,String ProcedureName,params object[] parameters) { String sql=string.Empty; OracleCommand command=new OracleCommand(); OracleCommand retCommand=new OracleCommand(); PackageName = PackageName.ToUpper(); ProcedureName = ProcedureName.ToUpper(); sql = "select * from ALL_PROCEDURES where OBJECT_NAME = '" + PackageName + "' and OWNER=USER and PROCEDURE_NAME = '" + ProcedureName + "'"; command = new OracleCommand(sql,new OracleConnection(dbOperation.ConnectionString)); command.CommandType = CommandType.Text; command.Connection.Open(); OracleDataReader reader = command.ExecuteReader(); if (!reader.Read()) //未找到些名称的存储过程 { reader.Close(); command.Connection.Close(); throw new OracleObjectNotFoundException("未发现名称为“" + PackageName + "." + ProcedureName + "”的存储过程!"); } else //找到此存储过程后，检察是否存在参数 { reader.Close(); retCommand = new OracleCommand(PackageName + "." +ProcedureName,new OracleConnection(dbOperation.ConnectionString)); retCommand.CommandType = CommandType.StoredProcedure; sql = "select * from ALL_ARGUMENTS where OBJECT_NAME = '" + ProcedureName + "' and OWNER = USER and PACKAGE_NAME = '" + PackageName + "' order by SEQUENCE"; command.CommandText = sql; reader = command.ExecuteReader(); int i = 0; while (reader.Read()) { //OracleParameter para = new OracleParameter(); //para.ParameterName = reader["ARGUMENT_NAME"].ToString(); OracleParameter para = null; String paramName = reader["ARGUMENT_NAME"].ToString(); switch (reader["DATA_TYPE"].ToString()) { case "NUMBER": //para = new OracleParameter(paramName, OracleDbType.Int32); para = new OracleParameter(paramName, OracleDbType.Decimal); //myj,2009-3-16,edit //para.OracleDbType = OracleDbType.Int32; para.Value = parameters[i]; break; case "VARCHAR2": para = new OracleParameter(paramName, OracleDbType.Varchar2); //para.OracleDbType = OracleDbType.Varchar2; para.Value = parameters[i]; String sValue = para.Value.ToString(); if (sValue == String.Empty) { para.Size = 4000; } else { para.Size = sValue.Length; } break; case "DATE": para = new OracleParameter(paramName, OracleDbType.Date); //para.OracleDbType = OracleDbType.Date; para.Value = parameters[i]; break; case "CLOB": if (string.IsNullOrEmpty(parameters[i].ToString())) { para = new OracleParameter(paramName, OracleDbType.Varchar2); } else { para = new OracleParameter(paramName, OracleDbType.Clob); } //para.OracleDbType = OracleDbType.Clob; para.Value = parameters[i]; break; case "REF CURSOR": para = new OracleParameter(paramName, OracleDbType.RefCursor); //para.OracleDbType = OracleDbType.RefCursor; break; } switch (reader["IN_OUT"].ToString()) { case "IN": para.Direction = ParameterDirection.Input; break; case "OUT": para.Direction = ParameterDirection.Output; break; case "IN/OUT": para.Direction = ParameterDirection.InputOutput; break; } i ++; retCommand.Parameters.Add(para); } reader.Close(); dbOperation.OracleCloseConnection(command); return retCommand; } }
自定义dbOperation.OracleCloseConnection方法
public static void OracleCloseConnection(OracleCommand cmd) { if (cmd.Connection != null) { if (cmd.Connection.State != ConnectionState.Closed) { cmd.Connection.Close(); } cmd.Connection.Dispose(); cmd.Connection = null; } }
自定义dbOperation.OracleGetProcedureData方法：
/// <summary> /// 根据传入的 Command，执行填充数据集的方法，返回 DataSet /// </summary> /// <param name="cmd">配置好的 Command 对象</param> /// <returns>返回填充好的数据集</returns> public static DataSet OracleGetProcedureData(OracleCommand cmd, DataSet retData, String tableName) { if (cmd.Connection == null || cmd.Connection.State != ConnectionState.Open) { cmd.Connection = new OracleConnection(dbOperation.ConnectionString); cmd.Connection.Open(); } OracleDataAdapter oracleAdapter = new OracleDataAdapter(String.Empty,dbOperation.ConnectionString); oracleAdapter.SelectCommand = cmd; try { if (retData.Tables.Count >0) retData.Tables[tableName].Rows.Clear(); } catch { } try { oracleAdapter.Fill(retData,tableName); } catch (OracleException ex) { throw new Exception(ex.Message); } finally { dbOperation.OracleCloseConnection(cmd); } return retData; }
自定义dbOperation.OracleGetFunctionParameter方法：
/// <summary> /// 根据转入的 functionName 及 参数值数组生成 OracleCommand 对象并存入 oracleAdapter 对象的 SelectCommand 中。 /// </summary> /// <param name="functionName">要调用的函数名称</param> /// <param name="parameters">参数数组</param> /// <returns>操作是否成功</returns> public static OracleCommand OracleGetFunctionParameter(String functionName,params object[] parameters) { String sql; OracleCommand command,retCommand; functionName = functionName.ToUpper(); sql = "select * from ALL_OBJECTS where OBJECT_NAME = '" + functionName + "' and OWNER = USER and OBJECT_TYPE = 'FUNCTION' and STATUS = 'VALID'"; command = new OracleCommand(sql,new OracleConnection(dbOperation.ConnectionString)); command.CommandType = CommandType.Text; command.Connection.Open(); OracleDataReader reader = command.ExecuteReader(); if (!reader.Read()) //未找到此名称的函数 { reader.Close(); command.Connection.Close(); throw new OracleObjectNotFoundException("未发现名称为“" + functionName + "”的函数!"); } else //找到此存储过程后，检察是否存在参数 { reader.Close(); retCommand = new OracleCommand(functionName,new OracleConnection(dbOperation.ConnectionString)); retCommand.CommandType = CommandType.StoredProcedure; sql = "select * from ALL_ARGUMENTS where OBJECT_NAME = '" + functionName + "' and OWNER = USER order by SEQUENCE"; command.CommandText = sql; command.CommandType = CommandType.Text; reader = command.ExecuteReader(); int i = 0; while (reader.Read()) { OracleParameter para = new OracleParameter(); if (reader["ARGUMENT_NAME"] == System.DBNull.Value) { para.Direction = ParameterDirection.ReturnValue; para.Size = (reader["DATA_LENGTH"] == System.DBNull.Value)? 1000:Convert.ToInt32(reader["DATA_LENGTH"]); } else { para.ParameterName = reader["ARGUMENT_NAME"].ToString(); //para.Direction = (reader["IN_OUT"].ToString() == "IN")? ParameterDirection.Input:ParameterDirection.Output; //myj屏蔽 2008-4-26 //myj add 2008-4-26 switch (reader["IN_OUT"].ToString()) { case "IN": para.Direction = ParameterDirection.Input; break; case "OUT": para.Direction = ParameterDirection.Output; break; case "IN/OUT": para.Direction = ParameterDirection.InputOutput; break; } //\myj add 2008-4-26 switch (reader["DATA_TYPE"].ToString()) { case "NUMBER": para.OracleDbType = OracleDbType.Double; para.Value = parameters[i]; break; case "VARCHAR2": para.OracleDbType = OracleDbType.Varchar2; para.Value = parameters[i]; String sValue = Convert.ToString(para.Value); if (sValue == String.Empty) { para.Size = 3000; } else { para.Size = sValue.Length; } break; case "REF CURSOR": para.OracleDbType = OracleDbType.RefCursor; break; } } i ++; retCommand.Parameters.Add(para); } reader.Close(); dbOperation.OracleCloseConnection(command); return retCommand; } }
