/// <summary> /// This routine is for the execution of code that will not return any values. /// Note: That this is a copy past from MSSSQL_ExecuteQuery with just the returns changed. /// </summary> public void ExecuteNonQuery() { var parms = new List <Param>(); foreach (StoredProcedureParameter parm in _params) { //JBienvenu 19202 2012-12-28 switch: Implemented ParameterTypeAdapter system. switch (parm.DBValueType) { case ParameterType.Structured: parms.Add(Param.createParam(parm.Name, new StructuredAdapter(), parm.Value, ParameterDirection.In)); break; case ParameterType.DBString: parms.Add(Param.createParam(parm.Name, new StringAdapter(), parm.Value, ParameterDirection.In)); break; case ParameterType.DBDateTime: parms.Add(Param.createParam(parm.Name, new DateTimeAdapter(), parm.Value, ParameterDirection.In)); break; case ParameterType.DBNvar: parms.Add(Param.createParam(parm.Name, new NvarAdapter(), parm.Value, ParameterDirection.In)); break; case ParameterType.DBVarBinary: parms.Add(Param.createParam(parm.Name, new VarBinaryAdapter(), parm.Value, ParameterDirection.In)); break; //handles any case that is not specified above with default of appending to the string default: parms.Add(Param.createParam(parm.Name, new StringAdapter(), parm.Value, ParameterDirection.In)); break; } } using (var sqlConn = new SqlConnection(ReadConnectionString())) { try { sqlConn.Open(); } catch { throw; } using (var sqlCommand = new SqlCommand(_storedProcedureName, sqlConn)) { //JBienvenu 19202 2012-12-28 foreach: Changed to use parms (which were not used at all before). Implemented ParameterTypeAdapter system. parms.ForEach((parm) => parm.ToSqlParameter(sqlCommand)); sqlCommand.CommandTimeout = _commandTimeOut; sqlCommand.CommandType = CommandType.StoredProcedure; sqlCommand.ExecuteNonQuery(); } } }
/// <summary> /// Create the dataset. This should be only visible to the DAL layer. For MS SQL DBs /// </summary> /// <returns></returns> public DataSet ExecuteMultipleDataSet() { var ret = new DataSet(); var parms = new List <Param>(); foreach (StoredProcedureParameter parm in _params) { //JBienvenu 19202 2012-12-28 switch: Implemented ParameterTypeAdapter system. switch (parm.DBValueType) { case ParameterType.Structured: parms.Add(Param.createParam(parm.Name, new StructuredAdapter(), parm.Value, ParameterDirection.In)); break; case ParameterType.DBString: if (parm.Direction == ParameterDirection.In) { parms.Add(Param.createParam(parm.Name, new StringAdapter(), parm.Value, ParameterDirection.In)); } else { parms.Add(Param.createParam(parm.Name, new StringAdapter(), parm.Value, ParameterDirection.Out)); } break; case ParameterType.DBDateTime: parms.Add(Param.createParam(parm.Name, new DateTimeAdapter(), parm.Value, ParameterDirection.In)); break; case ParameterType.DBNvar: parms.Add(Param.createParam(parm.Name, new NvarAdapter(), parm.Value, ParameterDirection.In)); break; case ParameterType.DBInteger: parms.Add(Param.createParam(parm.Name, new IntegerAdapter(), parm.Value, ParameterDirection.In)); break; //handles any case that is not specificed above with default of appaneding to the string default: parms.Add(Param.createParam(parm.Name, new StringAdapter(), parm.Value, ParameterDirection.In)); break; } } using (var sqlConn = new SqlConnection(ReadConnectionString())) { try { sqlConn.Open(); } catch { throw; } using (var sqlCommand = new SqlCommand(_storedProcedureName, sqlConn)) { //JBienvenu 19202 2012-12-28 foreach: Changed to use parms (which were not used at all before). Implemented ParameterTypeAdapter system. parms.ForEach((parm) => parm.ToSqlParameter(sqlCommand)); sqlCommand.CommandTimeout = _commandTimeOut; sqlCommand.CommandType = CommandType.StoredProcedure; var mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = sqlCommand; mySqlDataAdapter.Fill(ret); } } return(ret); }
/// <summary> /// Create the dataset. This should be only visible to the DAL layer. For MS SQL DBs /// </summary> /// <returns></returns> public void ExecuteDataSetWithOutputParam(out DataTable dtRet, out String sID) { DataTable ret = null; String ID = string.Empty; var parms = new List <Param>(); foreach (StoredProcedureParameter parm in _params) { //JBienvenu 19202 2012-12-28 switch: Implemented ParameterTypeAdapter system. switch (parm.DBValueType) { case ParameterType.Structured: parms.Add(Param.createParam(parm.Name, new StructuredAdapter(), parm.Value, ParameterDirection.In)); break; case ParameterType.DBString: parms.Add(Param.createParam(parm.Name, new StringAdapter(), parm.Value, ParameterDirection.In)); break; case ParameterType.DBDateTime: parms.Add(Param.createParam(parm.Name, new DateTimeAdapter(), parm.Value, ParameterDirection.In)); break; case ParameterType.DBNvar: parms.Add(Param.createParam(parm.Name, new NvarAdapter(), parm.Value, ParameterDirection.In)); break; //handles any case that is not specificed above with default of appaneding to the string default: parms.Add(Param.createParam(parm.Name, new StringAdapter(), parm.Value, ParameterDirection.In)); break; } } using (var sqlConn = new SqlConnection(ReadConnectionString())) { try { sqlConn.Open(); } catch { throw; } using (var sqlCommand = new SqlCommand(_storedProcedureName, sqlConn)) { //JBienvenu 19202 2012-12-28 foreach: Changed to use parms (which were not used at all before). Implemented ParameterTypeAdapter system. parms.ForEach((parm) => parm.ToSqlParameter(sqlCommand)); SqlParameter myparam3 = sqlCommand.Parameters.Add("@referenceTableType", SqlDbType.VarChar, 10); myparam3.Direction = System.Data.ParameterDirection.Output; sqlCommand.CommandTimeout = _commandTimeOut; sqlCommand.CommandType = CommandType.StoredProcedure; SqlDataReader reader = sqlCommand.ExecuteReader(); ret = GetDataTableFromDataReader(reader); reader.Close(); ID = (string)sqlCommand.Parameters["@referenceTableType"].Value; reader.Close(); } } dtRet = ret; sID = ID; }
/// <summary> /// Create the dataset. This should be only visible to the DAL layer. For MS SQL DBs /// Note: THe core functionality for this procedure has been cloned to executeNonQuery() to handle routines that /// do not expect any type of return value. /// </summary> /// <returns></returns> private DataTable MSSQL_executeDataSet() { var ret = new DataTable(); var parms = new List <Param>(); foreach (StoredProcedureParameter parm in _params) { //JBienvenu 19202 2012-12-28 switch: Implemented ParameterTypeAdapter system. switch (parm.DBValueType) { case ParameterType.Structured: parms.Add(Param.createParam(parm.Name, new StructuredAdapter(), parm.Value, ParameterDirection.In)); break; case ParameterType.DBString: parms.Add(Param.createParam(parm.Name, new StringAdapter(), parm.Value, ParameterDirection.In)); break; case ParameterType.DBDateTime: parms.Add(Param.createParam(parm.Name, new DateTimeAdapter(), parm.Value, ParameterDirection.In)); break; case ParameterType.DBNvar: parms.Add(Param.createParam(parm.Name, new NvarAdapter(), parm.Value, ParameterDirection.In)); break; case ParameterType.DBVarBinary: parms.Add(Param.createParam(parm.Name, new VarBinaryAdapter(), parm.Value, ParameterDirection.In)); break; default: //handles any case that is not specificed above with default of appaneding to the string parms.Add(Param.createParam(parm.Name, new StringAdapter(), parm.Value, ParameterDirection.In)); break; } } using (var sqlConn = new SqlConnection(ReadConnectionString())) { try { sqlConn.Open(); } catch { throw; } using (var sqlCommand = new SqlCommand(_storedProcedureName, sqlConn)) { //JBienvenu 19202 2012-12-28 foreach: Changed to use parms (which were not used at all before). Implemented ParameterTypeAdapter system. parms.ForEach((parm) => parm.ToSqlParameter(sqlCommand)); sqlCommand.CommandTimeout = _commandTimeOut; sqlCommand.CommandType = CommandType.StoredProcedure; using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader()) { if (sqlDataReader.VisibleFieldCount > 0) { foreach (DataRow dRow in sqlDataReader.GetSchemaTable().Rows) { if (!(dRow["ColumnName"].ToString().Trim().Equals(""))) { ret.Columns.Add(dRow["ColumnName"].ToString()); ret.Columns[dRow["ColumnName"].ToString()].ReadOnly = false; ret.Columns[dRow["ColumnName"].ToString()].AllowDBNull = true; ret.Columns[dRow["ColumnName"].ToString()].DataType = Type.GetType(dRow["DataType"].ToString()); } } ret.Load(sqlDataReader); } } } } return(ret); }