using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Collections.Specialized;
namespace Helpers
{
public static class DBHelper
{
#if DEBUG
private static string defaultConnectionString = "Data Source=localhost;Initial Catalog=GLOBOBH_MapaAntenas;Integrated Security=SSPI";
#else
private static string defaultConnectionString = "";
#endif
public static string DefaultConnectionString {
get {
return defaultConnectionString;
}
}
public static DataTable ExecuteProcedure(string PROC_NAME, params object[] parameters)
{
try
{
if (parameters.Length % 2 != 0)
throw new ArgumentException("Wrong number of parameters sent to procedure. Expected an even number.");
DataTable a = new DataTable();
List filters = new List();
string query = "EXEC " + PROC_NAME;
bool first = true;
for (int i = 0; i < parameters.Length; i += 2)
{
filters.Add(new SqlParameter(parameters[i] as string, parameters[i + 1]));
query += (first ? " " : ", ") + ((string)parameters[i]);
first = false;
}
a = Query(query, filters);
return a;
}
catch (Exception ex)
{
throw ex;
}
}
public static DataTable ExecuteQuery(string query, params object[] parameters)
{
try
{
if (parameters.Length % 2 != 0)
throw new ArgumentException("Wrong number of parameters sent to procedure. Expected an even number.");
DataTable a = new DataTable();
List filters = new List();
for (int i = 0; i < parameters.Length; i += 2)
filters.Add(new SqlParameter(parameters[i] as string, parameters[i + 1]));
a = Query(query, filters);
return a;
}
catch (Exception ex)
{
throw ex;
}
}
public static int ExecuteNonQuery(string query, params object[] parameters)
{
try
{
if (parameters.Length % 2 != 0)
throw new ArgumentException("Wrong number of parameters sent to procedure. Expected an even number.");
List filters = new List();
for (int i = 0; i < parameters.Length; i += 2)
filters.Add(new SqlParameter(parameters[i] as string, parameters[i + 1]));
return NonQuery(query, filters);
}
catch (Exception ex)
{
throw ex;
}
}
public static object ExecuteScalar(string query, params object[] parameters)
{
try
{
if (parameters.Length % 2 != 0)
throw new ArgumentException("Wrong number of parameters sent to query. Expected an even number.");
List filters = new List();
for (int i = 0; i < parameters.Length; i += 2)
filters.Add(new SqlParameter(parameters[i] as string, parameters[i + 1]));
return Scalar(query, filters);
}
catch (Exception ex)
{
throw ex;
}
}
#region Private Methods
private static DataTable Query(String consulta, IList parametros)
{
try
{
DataTable dt = new DataTable();
SqlConnection connection = new SqlConnection(defaultConnectionString);
SqlCommand command = new SqlCommand();
SqlDataAdapter da;
try
{
command.Connection = connection;
command.CommandText = consulta;
if (parametros != null)
{
command.Parameters.AddRange(parametros.ToArray());
}
da = new SqlDataAdapter(command);
da.Fill(dt);
}
finally
{
if (connection != null)
connection.Close();
}
return dt;
}
catch (Exception)
{
throw;
}
}
private static int NonQuery(string query, IList parametros)
{
try
{
DataSet dt = new DataSet();
SqlConnection connection = new SqlConnection(defaultConnectionString);
SqlCommand command = new SqlCommand();
try
{
connection.Open();
command.Connection = connection;
command.CommandText = query;
command.Parameters.AddRange(parametros.ToArray());
return command.ExecuteNonQuery();
}
finally
{
if (connection != null)
connection.Close();
}
}
catch (Exception ex)
{
throw ex;
}
}
private static object Scalar(string query, List parametros)
{
try
{
DataSet dt = new DataSet();
SqlConnection connection = new SqlConnection(defaultConnectionString);
SqlCommand command = new SqlCommand();
try
{
connection.Open();
command.Connection = connection;
command.CommandText = query;
command.Parameters.AddRange(parametros.ToArray());
return command.ExecuteScalar();
}
finally
{
if (connection != null)
connection.Close();
}
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
}
}
