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()); }