public int execute_non_query(string connection_string, string query, parameters parameters = null)
{
query_params q = new query_params(connection_string, query, parameters, false, query_types.non);
data_set ds = this.sql_query(q);
return(ds.affected_rows);
}
} //end function
public override fk.fk_members get_fk_from_table(string connection_string, string database, string table, string schema)
{
// Parameter: @database @table
// Columns // fk // db // table // schema // column // fk_table // fk_schema // fk_column // delete_action // update_ac
string query =
@"SELECT f.name AS fk,
OBJECT_NAME(f.parent_object_id) AS [table],
SCHEMA_NAME(f.schema_id) AS [schema],
COL_NAME(fc.parent_object_id,
fc.parent_column_id) AS [column],
OBJECT_NAME (f.referenced_object_id) AS [fk_table],
SCHEMA_NAME(o.schema_id) AS [fk_schema],
COL_NAME(fc.referenced_object_id,
fc.referenced_column_id) AS [fk_column],
delete_referential_action_desc AS [delete_action],
update_referential_action_desc AS [update_action],
DB_NAME() AS db
FROM sys.foreign_keys f
JOIN sys.foreign_key_columns fc ON f.object_id = fc.constraint_object_id
JOIN sys.objects o ON f.referenced_object_id = o.object_id
WHERE OBJECT_NAME (f.parent_object_id) = @table
AND SCHEMA_NAME(o.schema_id)=@schema
AND DB_NAME()=@database";
parameters p = new parameters();
p.add("@database", database);
p.add("@table", table);
p.add("@schema", schema);
query_params q = new query_params(connection_string, query, p, true, query_types.multiple);
data_set res = sql_query(q);
fk.fk_members obj = new fk.fk_members(res);
return(obj);
} //end function
public virtual string extract_query(query_params q)
{
StringBuilder o = new StringBuilder();
if (null != q.parameters)
{
List <string> lst = new List <string>();
foreach (var key2 in q.parameters)
{
lst.Add(key2.ToString());
}
lst.Sort(delegate(string x, string y) { return(y.Length.CompareTo(x.Length)); });
foreach (string name in lst)
{
string value = "";
if (null != q.parameters[name])
{
value = q.parameters[name].ToString().Replace("'", "''");
}
if (name[0] != '@')
{
// query=query.Replace("@"+name,"'"+value+"'");
}
else
{
// query=query.Replace(name,"'"+value+"'");
}
int n;
bool isNumeric = int.TryParse(value, out n);
if (value == "True")
{
o.Append(String.Format("DECLARE {0,-30} bit=1; --TRUE\r\n", name));
}
else if (value == "False")
{
o.Append(String.Format("DECLARE {0,-30} bit=0; --FALSE\r\n", name));
}
else if (isNumeric == true)
{
o.Append(String.Format("DECLARE {0,-30} int={1};\r\n", name, value));
}
else
{
if (value.Length >= 4000)
{
o.Append(String.Format("DECLARE {0,-30} varchar({2})='{1}';\r\n", name, value, value.Length + 10));
}
else
{
o.Append(String.Format("DECLARE {0,-30} nchar({2})='{1}';\r\n", name, value, value.Length + 10));
}
}
}
}
o.Append("\r\n--" + q.connection_string + "\r\n");
o.Append(q.query);
return(o.ToString());
}
public column_data execute_scalar(string connection_string, string query, parameters parameters = null)
{
query_params q = new query_params(connection_string, query, parameters, false, query_types.scalar);
data_set ds = this.sql_query(q);
if (ds.Count == 0 || String.IsNullOrWhiteSpace(ds[0, "count"]))
{
return(new column_data(0));
}
return(ds[0, "count"]);
}
protected virtual void log(query_params q, log_type type = log_type.Info, string message = null)
{
EventHandler <query_params> handler = LogEvent;
if (handler != null)
{
q.message = message;
q.log_type = type;
q.function = GetCurrentMethod();
handler(this, q); //dont lockup because of logging. Toss an event and run.
}
}
public data_set fetch_all(string connection_string, string query, parameters parameters = null, bool meta = false)
{
query_params q = new query_params(connection_string, query, parameters, meta, query_types.multiple);
data_set results = this.sql_query(q);
if (meta)
{
results.fk_from = get_fk_from_table(q.connection_string, results.columns[0].BaseCatalogName, results.columns[0].BaseTableName, results.columns[0].BaseSchemaName);
// results.fk_to =get_fk_to_table (q.connection_string,results.columns[0].BaseCatalogName,results.columns[0].BaseTableName,results.columns[0].BaseSchemaName);
}
return(results);
}
MySqlParameter[] parse_parameters(query_params q) //just looping through the collection and setting the params up.
{
List <MySqlParameter> parameterCollection = new List <MySqlParameter>();
try{
foreach (parameter parameter in q.parameters)
{
if (((string)parameter.key)[0] != '@')
{
parameterCollection.Add(new MySqlParameter("@" + parameter.key, parameter.value));
}
else
{
parameterCollection.Add(new MySqlParameter((string)parameter.key, (string)parameter.value));
}
}
}catch (Exception e) {
this.log(q, log_type.Error, e.ToString());
}
return(parameterCollection.ToArray());
}
public virtual data_set sql_query(query_params q)
{
throw new NotImplementedException();
}
public data_set sp_fetch_all(string connection_string, string query, parameters parameters = null, bool meta = false)
{
query_params q = new query_params(connection_string, query, parameters, meta, query_types.sp_multiple);
return(this.sql_query(q));
}
public override data_set sql_query(query_params q)
{
data_set results = new data_set();
try{
this.log(q, log_type.Info);
using (MySqlConnection conn = new MySqlConnection(q.connection_string)) {
conn.Open();
if (conn.State != System.Data.ConnectionState.Open)
{
return(null); //no connection DIE
}
if (q.type == query_types.multiple || q.type == query_types.single) //only a placebo to resolve paramneter sniffing for dynamic querys that suck. not for SP's
{
using (MySqlCommand comm = new MySqlCommand("SET ARITHABORT ON", conn)) {
comm.ExecuteNonQuery();
}
}
MySqlDataReader reader = null;
MySqlCommand command = new MySqlCommand(q.query, conn);
if (null != q.parameters)
{
MySqlParameter[] param_collection = parse_parameters(q); //load params into array
if (param_collection.Length > 0)
{
command.Parameters.AddRange(param_collection); //add params to sql command
}
}
CommandBehavior command_behavior = CommandBehavior.CloseConnection;
if (q.meta)
{
command_behavior |= CommandBehavior.KeyInfo;
}
if (q.type == query_types.non)
{
results.affected_rows = command.ExecuteNonQuery();
}
if (q.type == query_types.scalar)
{
var firstColumn = command.ExecuteScalar();
if (firstColumn != null)
{
results.scalar_results = new column_data(firstColumn);
}
}
if (q.type == query_types.multiple || q.type == query_types.single || q.type == query_types.sp_single || q.type == query_types.sp_multiple)
{
reader = command.ExecuteReader(command_behavior);
if (q.meta)
{
DataTable schema = reader.GetSchemaTable();
int dbFields = schema.Columns.Count;;
for (int i = 0; i < dbFields; i++)
{
results.columns.Add(new column_meta(schema.Columns, schema.Rows[i]));
}
}
if (reader.HasRows)
{
bool first_row = true;
this.log(q, log_type.Info, "Rows Returned");
while (reader.Read())
{
//we dont want to pull the extra meta Q
if (first_row)
{
for (int a = 0; a < reader.FieldCount; a++)
{
results.columns.Add(new column_meta(a, reader.GetName(a), reader[a].GetType()));
}
}
first_row = false;
}
row result = new row(results.columns);
for (int i = 0; i < reader.FieldCount; i++)
{
try{
if (reader[i].GetType() == typeof(string))
{
result.Add(reader.GetName(i), reader.GetString(i).Trim());
}
else
{
result.Add(reader.GetName(i), reader[i]);
}
}catch (Exception e) {
this.log(q, log_type.Error, e.ToString());
}
if (q.type == query_types.single || q.type == query_types.sp_single) //only 1 row
{
results.Add(result);
break;
}
else
{
results.Add(result);
}
} //end while
} //end if reader
else
{
this.log(q, log_type.Info, "NO Rows Returned");
}
}//end data collection for query types multiple or single
reader.Close(); //close this out as well..
conn.Close(); //close it out
conn.Dispose(); //clear it (using does this...)
}//end using
}catch (Exception e) {
this.log(q, log_type.Error, e.ToString());
}
return(results);
}
public static string extract_query(query_params q)
{
IData a = get_adapter(q.connection_string);
return(a.extract_query(q));
}
static void HandleEventHandler(object sender, query_params q)
{
log.write("data", q.log_type, q.ToString());
}
