public static ParametersContainer RetrieveOUTPUTParametersData(DBQueryDef queryDefinition) { ParametersContainer _OUTPUTcontainer = new ParametersContainer(); using (System.Data.SqlClient.SqlConnection cnn = new System.Data.SqlClient.SqlConnection(queryDefinition.ConnectionString)) { cnn.Open(); using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(queryDefinition.CommandText, cnn)) { try { switch (queryDefinition.QueryType) { case DataBaseLibrary.Definitions.queryType.Text: cmd.CommandType = CommandType.Text; break; case DataBaseLibrary.Definitions.queryType.StoredProcedure: cmd.CommandType = CommandType.StoredProcedure; System.Collections.IEnumerator listEnumerator = default(System.Collections.IEnumerator); QueryParameter parameter = default(QueryParameter); //gets the parameters from the definition listEnumerator = queryDefinition.Parameters.GetEnumerator(); while (listEnumerator.MoveNext()) { parameter = (QueryParameter)listEnumerator.Current; System.Data.SqlClient.SqlParameter param = new System.Data.SqlClient.SqlParameter(parameter.Name, parameter.sqlType); param.SqlDbType = parameter.sqlType; param.Direction = parameter.Direction; param.Size = parameter.Size; if (param.Direction == ParameterDirection.Output) { _OUTPUTcontainer.AddParameter(parameter); } cmd.Parameters.Add(param); } break; } cmd.ExecuteNonQuery(); //transferrs the valur retrieved to the parameters in the result set foreach (QueryParameter param in _OUTPUTcontainer) { param.Value = cmd.Parameters[param.Name].Value; } } catch (Exception ex) { throw (ex); } } } return(_OUTPUTcontainer); }
public static void ExecuteQuery(DBQueryDef queryDefinition) { using (System.Data.SqlClient.SqlConnection cnn = new System.Data.SqlClient.SqlConnection(queryDefinition.ConnectionString)) { cnn.Open(); using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(queryDefinition.CommandText, cnn)) { try { switch (queryDefinition.QueryType) { case DataBaseLibrary.Definitions.queryType.Text: cmd.CommandType = CommandType.Text; break; case DataBaseLibrary.Definitions.queryType.StoredProcedure: cmd.CommandType = CommandType.StoredProcedure; System.Collections.IEnumerator listEnumerator = default(System.Collections.IEnumerator); QueryParameter parameter = default(QueryParameter); //gets the parameters from the definition listEnumerator = queryDefinition.Parameters.GetEnumerator(); while (listEnumerator.MoveNext()) { parameter = (QueryParameter)listEnumerator.Current; System.Data.SqlClient.SqlParameter param = new System.Data.SqlClient.SqlParameter(parameter.Name, parameter.Value); param.Direction = parameter.Direction; param.SqlDbType = parameter.sqlType; param.Size = parameter.Size; cmd.Parameters.Add(param); } break; } cmd.ExecuteNonQuery(); } catch (Exception ex) { throw (ex); } } } }
public static DBData RetrieveData(DBQueryDef queryDefinition) { using (System.Data.SqlClient.SqlConnection cnn = new System.Data.SqlClient.SqlConnection(queryDefinition.ConnectionString)) { cnn.Open(); using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(queryDefinition.CommandText, cnn)) { DBData DBData = new DBData(); switch (queryDefinition.QueryType) { case DataBaseLibrary.Definitions.queryType.Text: cmd.CommandType = CommandType.Text; DBData.FillData(cmd); break; case DataBaseLibrary.Definitions.queryType.StoredProcedure: cmd.CommandType = CommandType.StoredProcedure; System.Collections.IEnumerator listEnumerator = default(System.Collections.IEnumerator); QueryParameter parameter = default(QueryParameter); //gets the parameters from the definition listEnumerator = queryDefinition.Parameters.GetEnumerator(); while (listEnumerator.MoveNext()) { parameter = (QueryParameter)listEnumerator.Current; System.Data.SqlClient.SqlParameter param = new System.Data.SqlClient.SqlParameter(parameter.Name, parameter.Value); param.SqlDbType = parameter.sqlType; param.Direction = parameter.Direction; param.Size = parameter.Size; cmd.Parameters.Add(param); } DBData.FillData(cmd); break; } return(DBData); } } }