public static DataTable genericQuery(TSQLCommands command, string query)
{
DataTable table = null;
DataSet ds = null;
try
{
ds = new DataSet();
table = new DataTable();
ds.Tables.Add(table);
ds.EnforceConstraints = false;
MySqlConnection cnn;
cnn = new MySqlConnection(WebApiConfig.DbCnnCnfg["CnnString"]);
MySqlCommand cmd = new MySqlCommand(query, cnn);
cnn.Open();
MySqlDataReader dr = cmd.ExecuteReader();
table.Load(dr);
dr.Close();
cmd.Dispose();
cnn.Close();
}
catch (System.Exception ex)
{
Logger logger = LogManager.GetCurrentClassLogger();
logger.Error(ex, ex.Message);
throw;
}
return(table);
}
public static DataTable genericQuery(TSQLCommands command, string query)
{
DataTable table = null;
DataSet ds = null;
try
{
ds = new DataSet();
table = new DataTable();
ds.Tables.Add(table);
ds.EnforceConstraints = false;
MySqlConnection cnn;
cnn = new MySqlConnection(WebApiConfig.DbCnnCnfg["CnnString"]);
MySqlCommand cmd = new MySqlCommand(query, cnn);
cnn.Open();
MySqlDataReader dr = cmd.ExecuteReader();
table.Load(dr);
dr.Close();
cmd.Dispose();
cnn.Close();
}
catch (System.Exception ex)
{
Logger logger = LogManager.GetCurrentClassLogger();
logger.Error(ex, ex.Message);
throw;
}
return table;
}
public static Boolean query(string query, TSQLCommands ignoreCommand)
{
return(valida.query(new List <string> {
query
}, new List <TSQLCommands> {
ignoreCommand
}));
}
private service invoque(TSQLCommands command, string query)
{
try
{
WebApiConfig.DbCnnCnfg.Clear();
WebApiConfig.DbCnnCnfg.Add("Provider", valida.header(Request, "Provider"));
WebApiConfig.DbCnnCnfg.Add("CnnString", System.Text.Encoding.UTF8.GetString(System.Convert.FromBase64String(valida.header(Request, "CnnString"))));
if (valida.parameter(query))
{
new System.ArgumentException(Resources.REST_Service_Parametros_Incorrectos);
}
query = HttpContext.Current.Server.UrlDecode(query);
List <string> querys = new List <string>(query.Split(';'));
if (!valida.query(querys, command))
{
throw new System.FormatException(Resources.REST_Service_Message_PERMISO_DENEGADO);
}
List <Models.structs.response> dataResults = new List <Models.structs.response>();
foreach (string item in querys)
{
if (!string.IsNullOrEmpty(item.Trim()))
{
Models.structs.response data = new Models.structs.response();
data.Command = item.Replace(System.Environment.NewLine, string.Empty);
switch (WebApiConfig.DbCnnCnfg["Provider"].Trim().ToLower())
{
case "sqlserver":
data.Result = getSqlServer.genericQuery(command, item);
break;
case "mysqlserver":
data.Result = getMySqlServer.genericQuery(command, item);
break;
}
dataResults.Add(data);
}
}
return(Libs.response.make(dataResults));
}
catch (Exception ex)
{
return(Libs.response.make(Resources.REST_Service_Status_code_ERROR, Resources.REST_Service_Status_response_ERROR, ex.Message));
}
}
public static DataTable genericQuery(TSQLCommands command, string query)
{
DataTable table = null;
try
{
table = new DataTable();
SqlConnection connection = new SqlConnection(WebApiConfig.DbCnnCnfg["CnnString"]);
SqlCommand cmd = new SqlCommand(query, connection);
switch (command)
{
case TSQLCommands.create:
case TSQLCommands.alter:
case TSQLCommands.drop:
case TSQLCommands.delete:
case TSQLCommands.insert:
case TSQLCommands.update:
case TSQLCommands.trunctate:
case TSQLCommands.truncate:
cmd.ExecuteNonQuery();
break;
case TSQLCommands.select:
case TSQLCommands.execute:
case TSQLCommands.dynamic:
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
connection.Open();
adapter.Fill(table);
break;
}
cmd.Dispose();
cmd = null;
connection.Close();
connection.Dispose();
connection = null;
}
catch (System.Exception ex)
{
Logger logger = LogManager.GetCurrentClassLogger();
logger.Error(ex, ex.Message);
throw;
}
return(table);
}
private service invoque(TSQLCommands command, string query)
{
try
{
WebApiConfig.DbCnnCnfg.Clear();
WebApiConfig.DbCnnCnfg.Add("Provider", valida.header(Request, "Provider"));
WebApiConfig.DbCnnCnfg.Add("CnnString",System.Text.Encoding.UTF8.GetString(System.Convert.FromBase64String(valida.header(Request, "CnnString"))));
if (valida.parameter(query))
new System.ArgumentException(Resources.REST_Service_Parametros_Incorrectos);
query = HttpContext.Current.Server.UrlDecode(query);
List<string> querys = new List<string>(query.Split(';'));
if (!valida.query(querys, command))
throw new System.FormatException(Resources.REST_Service_Message_PERMISO_DENEGADO);
List<Models.structs.response> dataResults = new List<Models.structs.response>();
foreach (string item in querys)
{
if (!string.IsNullOrEmpty(item.Trim()))
{
Models.structs.response data = new Models.structs.response();
data.Command = item.Replace(System.Environment.NewLine, string.Empty);
switch (WebApiConfig.DbCnnCnfg["Provider"].Trim().ToLower())
{
case "sqlserver":
data.Result = getSqlServer.genericQuery(command, item);
break;
case "mysqlserver":
data.Result = getMySqlServer.genericQuery(command, item);
break;
}
dataResults.Add(data);
}
}
return Libs.response.make(dataResults);
}
catch (Exception ex)
{
return Libs.response.make(Resources.REST_Service_Status_code_ERROR, Resources.REST_Service_Status_response_ERROR, ex.Message);
}
}
public static DataTable genericQuery(TSQLCommands command, string query)
{
DataTable table = null;
try
{
table = new DataTable();
SqlConnection connection = new SqlConnection(WebApiConfig.DbCnnCnfg["CnnString"]);
SqlCommand cmd = new SqlCommand(query, connection);
switch (command)
{
case TSQLCommands.create:
case TSQLCommands.alter:
case TSQLCommands.drop:
case TSQLCommands.delete:
case TSQLCommands.insert:
case TSQLCommands.update:
case TSQLCommands.trunctate:
case TSQLCommands.truncate:
cmd.ExecuteNonQuery();
break;
case TSQLCommands.select:
case TSQLCommands.execute:
case TSQLCommands.dynamic:
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
connection.Open();
adapter.Fill(table);
break;
}
cmd.Dispose();
cmd = null;
connection.Close();
connection.Dispose();
connection = null;
}
catch (System.Exception ex)
{
Logger logger = LogManager.GetCurrentClassLogger();
logger.Error(ex, ex.Message);
throw;
}
return table;
}
public static Boolean query(List <string> querys, TSQLCommands ignoreCommand)
{
return(valida.query(querys, new List <TSQLCommands> {
ignoreCommand
}));
}
public static Boolean query(List<string> querys, TSQLCommands ignoreCommand)
{
return valida.query(querys, new List<TSQLCommands> { ignoreCommand });
}
public static Boolean query(string query, TSQLCommands ignoreCommand)
{
return valida.query(new List<string> { query }, new List<TSQLCommands> { ignoreCommand });
}
