public int executeQuery(String query)
{
SqlConnection conn = Conexion.configDBConnection();
int ret = -1;
try
{
SqlCommand cmd = new SqlCommand(query, conn);
openConnection(conn);
ret = (int)cmd.ExecuteNonQuery();
closeConnection(conn);
}
catch (SqlException ex)
{
for (int i = 0; i < ex.Errors.Count; i++)
{
errorMessages.Append("Index #" + i + "\n" +
"Message: " + ex.Errors[i].Message + "\n" +
"LineNumber: " + ex.Errors[i].LineNumber + "\n" +
"Source: " + ex.Errors[i].Source + "\n");
}
Console.WriteLine(errorMessages.ToString());
}
return(ret);
}
public void executeStoredTransaction(Tuple <string, List <string>, Object[]>[] procedures)
{
SqlConnection cnn = Conexion.configDBConnection();
openConnection(cnn);
SqlTransaction transaction = cnn.BeginTransaction();
foreach (Tuple <string, List <string>, Object[]> proc in procedures)
{
using (SqlCommand cmd = new SqlCommand(proc.Item1, cnn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
for (int i = 0; i < proc.Item3.Length; i++)
{
cmd.Parameters.AddWithValue(proc.Item2[i], proc.Item3[i]);
}
cmd.Connection = cnn;
cmd.Transaction = transaction;
cmd.ExecuteNonQuery();
}
}
transaction.Commit();
closeConnection(cnn);
}
public List <Object> executeAdvancedSelectQuery(string query)
{
List <Object> resultQuery = new List <Object>();
SqlConnection conn = Conexion.configDBConnection();
openConnection(conn);
SqlCommand cmd = new SqlCommand(query, conn);
SqlDataReader reader = cmd.ExecuteReader();
try
{
while (reader.Read())
{
resultQuery.Add(Convert.ToString(reader[0]));
}
}
catch (SqlException ex)
{
for (int i = 0; i < ex.Errors.Count; i++)
{
errorMessages.Append("Index #" + i + "\n" +
"Message: " + ex.Errors[i].Message + "\n" +
"LineNumber: " + ex.Errors[i].LineNumber + "\n" +
"Source: " + ex.Errors[i].Source + "\n");
}
Console.WriteLine(errorMessages.ToString());
}
closeConnection(conn);
return(resultQuery);
}
public DataTable selectReturnMultiplyRowsByQuery(String query)
{
SqlConnection conn = Conexion.configDBConnection();
DataTable dtResult = null;
openConnection(conn);
try
{
SqlDataAdapter myDataAdapter = new SqlDataAdapter(query, conn);
dtResult = new DataTable();
myDataAdapter.Fill(dtResult);
}
catch (SqlException ex)
{
for (int i = 0; i < ex.Errors.Count; i++)
{
errorMessages.Append("Index #" + i + "\n" +
"Message: " + ex.Errors[i].Message + "\n" +
"LineNumber: " + ex.Errors[i].LineNumber + "\n" +
"Source: " + ex.Errors[i].Source + "\n");
}
Console.WriteLine(errorMessages.ToString());
}
closeConnection(conn);
return(dtResult);
}
public DataTable selectReturnMultiplyRows(String nomTabla, params Object[] parametros)
{
string query = "SELECT ";
SqlConnection conn = Conexion.configDBConnection();
DataTable dtResult = null;
for (int i = 0; i < parametros.Length; i++)
{
query = query + parametros[i] + ",";
}
query = query.TrimEnd(',');
query = query + " from " + Properties.Settings.Default.Schema + "." + nomTabla;
openConnection(conn);
try
{
SqlDataAdapter myDataAdapter = new SqlDataAdapter(query, conn);
dtResult = new DataTable();
myDataAdapter.Fill(dtResult);
}
catch (SqlException ex)
{
for (int i = 0; i < ex.Errors.Count; i++)
{
errorMessages.Append("Index #" + i + "\n" +
"Message: " + ex.Errors[i].Message + "\n" +
"LineNumber: " + ex.Errors[i].LineNumber + "\n" +
"Source: " + ex.Errors[i].Source + "\n");
}
Console.WriteLine(errorMessages.ToString());
}
closeConnection(conn);
return(dtResult);
}
public ComboBox populateComboBox(ComboBox combo, string query)
{
SqlDataReader dr;
SqlConnection con = Conexion.configDBConnection();
this.openConnection(con);
SqlCommand cmd = new SqlCommand(query, con);
cmd.CommandType = CommandType.Text;
dr = cmd.ExecuteReader();
while (dr.Read())
{
combo.Items.Add(dr[0].ToString());
}
this.closeConnection(con);
return(combo);
}
public void executeProcedure(String nomProcedure, List <String> values, params Object[] parametros)
{
SqlConnection conn = Conexion.configDBConnection();
using (SqlCommand cmd = new SqlCommand(nomProcedure, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
for (int i = 0; i < parametros.Length; i++)
{
cmd.Parameters.AddWithValue(values[i], parametros[i]);
}
openConnection(conn);
cmd.Connection = conn;
cmd.ExecuteNonQuery();
closeConnection(conn);
}
}
//Param 1 nombre de la funcion, los restantes serian parametros propios de la funcion
public Object executeScalarFunction(String nomFunct, params Object[] parametros)
{
SqlConnection conn = Conexion.configDBConnection();
openConnection(conn);
string split = "";
for (int i = 0; i < parametros.Length; i++)
{
if (parametros[i] is string)
{
split += "'" + Convert.ToString(parametros[i]) + "'" + ",";
}
else
{
split += Convert.ToString(parametros[i]) + ",";
}
}
split = split.TrimEnd(',');
String query = "SELECT " + Properties.Settings.Default.Schema + "." + nomFunct + '(' + split + ')';
SqlCommand cmd = new SqlCommand(query, conn);
Object result = null;
try
{
cmd.CommandType = System.Data.CommandType.Text;
result = cmd.ExecuteScalar();
}
catch (SqlException ex)
{
for (int i = 0; i < ex.Errors.Count; i++)
{
errorMessages.Append("Index #" + i + "\n" +
"Message: " + ex.Errors[i].Message + "\n" +
"LineNumber: " + ex.Errors[i].LineNumber + "\n" +
"Source: " + ex.Errors[i].Source + "\n");
}
Console.WriteLine(errorMessages.ToString());
}
closeConnection(conn);
return(result);
}
//Params pueden ser las columnas a retornar como una funcion de sql
//Le mando la cant de parametros necesarios y el ultimo elemento es otra query
//Si no quiero un where u otra le mando null como ultimo
//tambien puede ser un where o un order
//por ejemplo select("usuario",2,"username","password","where username = pepito")
public List <Object> selectReturnList(String nomTabla, int numParametros, params Object[] parametros)
{
List <Object> resultQuery = new List <Object>();
String query = "SELECT ";
SqlConnection conn = Conexion.configDBConnection();
for (int i = 0; i < numParametros; i++)
{
query = query + parametros[i];
}
if (parametros[parametros.Length - 1] == null)
{
query = query + "from " + Properties.Settings.Default.Schema + "." + nomTabla + " " + parametros[parametros.Length];
}
query = query + "from " + Properties.Settings.Default.Schema + "." + nomTabla;
openConnection(conn);
SqlCommand cmd = new SqlCommand(query, conn);
SqlDataReader reader = cmd.ExecuteReader();
try
{
while (reader.Read())
{
resultQuery.Add(Convert.ToString(reader[0]));
}
}
catch (SqlException ex)
{
for (int i = 0; i < ex.Errors.Count; i++)
{
errorMessages.Append("Index #" + i + "\n" +
"Message: " + ex.Errors[i].Message + "\n" +
"LineNumber: " + ex.Errors[i].LineNumber + "\n" +
"Source: " + ex.Errors[i].Source + "\n");
}
Console.WriteLine(errorMessages.ToString());
}
closeConnection(conn);
return(resultQuery);
}