}//end update function
public bool is_in_db() {
string query=String.Format("SELECT TOP 1 * FROM {0} WHERE {1} ",this._table,this.get_pk());
parameters q_param=generate_crud_parameters();
data_set res=db.fetch("titan",query,q_param);
if (null!=res) return true;
return false;
}
} //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 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);
}
public void run_crons() {
string query="SELECT * FROM titanDWS WITH (NOLOCK) WHERE cron=cast(1 as bit)"; //get all methods that are cron's
data_set rows=db.fetch_all("titan",query);
foreach(row row in rows) {
lambda i=new lambda();
i.group =(string)row["group"];
i.method=(string)row["method"];
string cron=(string)row["schedule"];
string[] tokens=cron.Split(' ');
if(tokens.Length!=5) continue; //we need 5 pieces.
string minute =tokens[0];
string hour =tokens[1];
string day =tokens[2];
string month =tokens[3];
string day_of_week =tokens[4];
DateTime time=DateTime.Now;
if(minute !="*" && time.Minute.ToString() !=minute) continue;
if(hour !="*" && time.Hour.ToString() !=hour) continue;
if(day !="*" && time.Day.ToString() !=day) continue;
if(month !="*" && time.Month.ToString() !=month) continue;
if(day_of_week !="*" && time.DayOfWeek.ToString() !=day_of_week) continue;
//we only get here if we have passed all the checks.
//web_service w=new web_service();
method m=new method();
m.execute(i,true,null,true);
}
}
public string execute_query(query q,bool noLinks) {
string extracted_query="";
try{
if(this.count) {
data_set res=null;
/*if(this.query_is_sp) {
res=db.sp_fetch(this.connection_string,q.getTotalQuery,q.totalParameters);
} else {*/
column_data res_count=db.execute_scalar(this.connection_string.Trim(),q.getTotalQuery,q.totalParameters);
//}
if(null!=res_count) {
this.results.total_rows=res_count;
}
}
data_set rows=null;
if(this.query_is_sp) {
rows=db.sp_fetch_all(this.connection_string,q.getResultsQuery,q.resultsParameters);
extracted_query=db.extract_query(new query_params(connection_string,
q.getResultsQuery,
q.resultsParameters,
true,
query_types.sp_multiple));
} else {
if(this.query_is_single) {
rows=db.fetch(this.connection_string,q.getResultsQuery,q.resultsParameters);
extracted_query=db.extract_query(new query_params(connection_string,
q.getResultsQuery,
q.resultsParameters,
true,
query_types.single));
} else {
rows=db.fetch_all(this.connection_string,q.getResultsQuery,q.resultsParameters);
extracted_query=db.extract_query(new query_params(connection_string,
q.getResultsQuery,
q.resultsParameters,
true,
query_types.multiple));
}
}
if(null!=rows) {
this.results.rows.Clear();
foreach(row row in rows) {
process_data_row(q,row,noLinks);
}
}//while reader has rows
} //try
catch (Exception e){
//Log.Write(LogSeverity.Error, "mango WS: execute err: "+ e.Message);
}
return extracted_query;
}
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"]);
}
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);
}
public static string map_table(string connection_target, string database, string schema, string table)
{
data_set data = db.fetch_all(connection_target, string.Format("SELECT TOP 1 * FROM [{0}].[{1}].[{2}]", database, schema, table), null, true);
StringBuilder o = new StringBuilder();
database = safe_name(database);
schema = safe_name(schema);
table = safe_name(table);
o.AppendLine(map_columns(data.columns, database, schema, table)); //the column meta classes
o.AppendLine(string.Format("namespace ncdb.{0}.{1} {{", database, schema));
o.AppendLine(string.Format("\t public class {0} :nocodedb.data.crud <{0}> {{", table));
o.AppendLine("\t\t//Internal variables");
int index = 0;
foreach (column_meta c in data.columns)
{
string internal_name = "_0000" + index.ToString();
o.AppendLine(string.Format("\t\t private {0} __{1};",
c.DataType,
internal_name));
index++;
}
o.AppendLine("\t\t//Public Variables");
index = 0;
string column_namespace = string.Format("ncdb.{0}.{1}.{2}", database, schema, table);
foreach (column_meta c in data.columns)
{
string internal_name = "___0000" + index.ToString();
string private_name = safe_name(c.ColumnName);
o.AppendLine(string.Format("\t\t public {0} {1} {{ get {{ return {2}; }} set {{ {2}=value; }} }}", //if({3}.{1}.validate(value)) {{
c.DataType,
private_name,
internal_name,
column_namespace));
index++;
}
o.AppendLine(String.Format("\t\tpublic {0}() {{", table));
o.AppendLine("\t\t}");
o.AppendLine("\t} //end table class");
o.AppendLine("}//end namespace");
return(o.ToString());
}
public bool load() {
parameters q_param=generate_crud_parameters();
string query=String.Format("SELECT TOP 1 * FROM {0} WHERE {1} ",this._table,this.get_pk());
data_set res=db.fetch("titan",query,q_param);
if (null!=res) {
foreach (String field_name in res.Keys) {
this.set_property(field_name,res[0,field_name]);
//this.set_property(field_name,res.columns[field_name]);
}//loop through all keys
return true;
}//if it exist
return false;
}//if it exist
public bool load() {
parameters q_param=generate_crud_parameters();
string query=String.Format("SELECT TOP 1 * FROM {0} WHERE {1} ",this._table,this.get_pk());
data_set res=db.fetch("titan",query,q_param);
if (null!=res && null!=res.Keys && res.Keys.Length>0) {
foreach (String field_name in res.Keys) {
row r=res[0];
object val=r[field_name];
this.set_property(field_name,val);
}//loop through all keys
return true;
}//if it exist
return false;
}//if it exist
public static string map_database(string connection_target, string database)
{
StringBuilder tables = new StringBuilder();
data_set data = db.fetch_all(connection_target, string.Format("SELECT top 10 TABLE_SCHEMA,TABLE_NAME FROM {0}.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA='dbo' ORDER BY TABLE_NAME,TABLE_SCHEMA", database));
tables.AppendLine("using System;");
tables.AppendLine("using System.Data;");
foreach (row r in data)
{
string source_file = map_table(connection_target, database, r[0].ToString(), r[1].ToString());
tables.AppendLine(source_file);
// Console.ReadKey();
}
return(tables.ToString());
}
public static List <linked_method> get()
{
data_set results = db.fetch_all("titan", "SELECT [title],[method],[group],[owner],[description] FROM titanDWS WHERE [active]=1 ORDER BY [order],[group],[method],[owner] ");
List <linked_method> methods = new List <linked_method>();
linked_method def = new linked_method();
def.title = "CHOOSE Method";
methods.Add(def);
foreach (row result in results)
{
linked_method lm = new linked_method();
lm.title = (string)result["title"];
lm.name = (string)result["method"];
lm.group = (string)result["group"];
lm.owner = (string)result["owner"];
lm.description = (string)result["description"];
methods.Add(lm);
}
return(methods);
}
public MainWindow() : base(Gtk.WindowType.Toplevel)
{
Build();
textview2.Buffer.Text = "START";
mssql_adapter mssql = new mssql_adapter();
string conn = mssql.connection_string(@"10.0.0.229\ND", "monarch", "p825amwX$");
string query = "SELECT top 100 * FROM shipit.dbo.enr_txn_shipping";
Hashtable param = new Hashtable()
{
{ "id", "6" }
};
data_set ds = mssql.fetch_all(conn, query, param, true);
var serializer = new JavaScriptSerializer();
var serializedResult = serializer.Serialize(ds);
textview2.Buffer.Text = serializedResult;
}
public void buildOptions(parameters parameters=null) {
selectOptions.Clear();
if(selectData.ToUpper().Contains("SELECT")) {
data_set results=db.fetch_all("",selectData,parameters);
foreach(row res in results) {
if(results.ContainsKey("display") && results.ContainsKey("value") && results.ContainsKey("selected")) {
string v1=res["display"];
string v2=res["value"];
bool v3=res["selected"];
selectOptions.Add(new select_option(v1,v2,v3));
}
}
} else {
string[] options=selectData.Split(',');
if(options.Length>0)
foreach(string option in options){
string[]tokens=option.Split('|');
if(tokens.Length==1 && !String.IsNullOrWhiteSpace(tokens[0])) selectOptions.Add(new select_option(tokens[0],tokens[0],tokens[0][0]=='*'?true:false));
if(tokens.Length>=2 && !String.IsNullOrWhiteSpace(tokens[0])
&& !String.IsNullOrWhiteSpace(tokens[1])) selectOptions.Add(new select_option(tokens[0],tokens[1],tokens[0][0]=='*'?true:false));
}
}
}
public string export(lambda i,string web_token) {
string query="SELECT TOP 1 json,token FROM titanDWS_exports WITH (NOLOCK) WHERE [id]=@export_id AND [web_token]=@token";
parameters param=new parameters();
param.add("@token" ,web_token);
param.add("@export_id" ,i.export_id);
System.Web.Script.Serialization.JavaScriptSerializer jss=new System.Web.Script.Serialization.JavaScriptSerializer();
lambda export_input=null;
security.titan_token s_token=null;
data_set result=db.fetch("titan",query,param);
if(null!=results) {
string json= (string)result[0,"json"];
string token=(string)result[0,"token"];
export_input=jss.Deserialize<lambda>(json);
s_token=jss.Deserialize<security.titan_token>(token);
} else {
return null; //nothing... lets exit
}
if(null==export_input) return null;
string name=String.Format("{0}-{1}-{2}.csv",export_input.group,export_input.method,DateTime.Now.ToLongTimeString());
/*HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ClearHeaders();
HttpContext.Current.Response.ClearContent();
HttpContext.Current.Response.AddHeader("content-disposition", "attachment; filename=" + name);
HttpContext.Current.Response.ContentType = "text/csv";
HttpContext.Current.Response.AddHeader("Pragma", "public");
*/
export_input.pageLength=0;
export_input.page=0;
this.input=export_input;
StringBuilder sb=new StringBuilder();
method m=new method();
m.execute(input,true,s_token,false);
int index=0;
foreach (query.column c in results.columns) {
core.column mc=m.data_schema.Find(x=>x.name==c.name);
if(null!=mc && mc.export) {
if (index!=0) {
sb.Append(",");
}
index++;
sb.Append(String.Format("\"{0}\"",c.name));
}
}
sb.Append("\r\n");
foreach (string[] row in results.rows) { //row
index=0;
foreach (string column in row) { //column
if (index!=0) {
sb.Append(",");
}
index++;
sb.Append(String.Format("\"{0}\"",column));
}
sb.Append("\r\n");
}
return sb.ToString();
//HttpContext.Current.Response.Flush();
//HttpContext.Current.Response.End();
}//end export
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 method from_json(string text, security.titan_token token, bool save = true)
{
method m = JsonConvert.DeserializeObject <method>(text, new JsonSerializerSettings {
Error = delegate(object sender, ErrorEventArgs args){
Console.WriteLine(args.ErrorContext.Error.Message);
args.ErrorContext.Handled = true;
}
});
if (null == m.data_schema)
{
m.data_schema = new List <titan.core.column>();
}
if (null == m.parameters)
{
m.parameters = new List <titan.core.parameter>();
}
m.parameters.RemoveAll(item => item == null);
m.parameters.RemoveAll(item => String.IsNullOrWhiteSpace(item.name));
parameters param = m.execute_load_parameters(token, null, true);
if (m.regenerate_columns)
{
m.data_schema.Clear();
data_set row = db.fetch(m.connection_string, strip_comments(m.query), param); //no conn string denotes localhost, aything else is treated as a conn string.. (titan)
int index = 0;
if (null != row) //only update the titan.core.columns if we have atlest 1 result.
{
foreach (string key in row.Keys)
{
index++;
titan.core.column c = new titan.core.column(key, "text", index, true);
m.data_schema.Add(c); //only add if it doesnt exist...
}
}
// if(null!=titan.core.columns) titan.core.columns.RemoveAll(item=> !row.ContainsKey(item.name) );
} //
//global.reload();
if (save)
{
if (!string.IsNullOrWhiteSpace(m.data_mapping_name))
{
if (null == m.sig_init)
{
m.sig_init = new external_data_defaults();
m.sig_init.group = m.group;
m.sig_init.method = m.method;
m.sig_init.owner = m.owner;
}
m.sig_init.save();
}
m.save();
m.generate_queries();
}
else
{
m.generate_queries();
}
return(m);
}
public void init()
{
types = db.fetch_all("titan", "SELECT display,name,format,reference1,reference2,reference3,reference4 FROM titanDWS_data_types WHERE active='1' order by [order]");
}
