} //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 void autoMethod(string group,string method) {
* method=method.Replace(' ','_');
* method=method.Replace('\'','_');
* method=method.Replace('\"','_');
* method=method.Replace(';','_');
*
* this.display=method;
* this.group=group;
* this.query=String.Format("SELECT * from [dbo].[{0}]",method);
* this.export=true;
* this.parameters=new List<parameter>();
* this.aggregates=new List<aggregate>();
*
*
* this.name = "System Table -> "+method;
* this.desc = "This is an automatic live system method. It will change anytime a table changes.";
* this.type = "JSON";
* this.visible = true;
* this._internal =true;
* this.external =false;
* this.sort =0;
* this.menuID = 0;
* this.created = DateTime.Now;
* this.modified = DateTime.Now;
* this.active = true;
* //this.template=getDefaultTemplate();
* //this.template_id=this.template.id;
* string query=@"SELECT c.[name],t.[name] as 'type',cast(case when coalesce(t.collation_name,'NO')='NO' then 0 else 1 END as bit) as searchable
* FROM syscolumns c
* LEFT JOIN sys.types t ON c.xusertype = t.user_type_id
* WHERE id = (SELECT id FROM sysobjects WHERE type = 'U' AND [Name] = @table)";
* using (DatabaseAdapter da = new DatabaseAdapter(AdapterType.DEFAULT)) {
*
* SqlParameter[] param = { new SqlParameter("@table",method) };
* using (SqlDataReader reader = da.ExecuteReader(query, param)) {
* int index=0;
* if(reader.HasRows) {
* while( reader.Read()) {
* string name= reader.GetString("name");
* string underlyingType= reader.GetString("type");
* bool searchable= reader.GetBoolean("searchable");
* column c=new column(name,searchable,underlyingType,type,index.ToString(),true,String.Format("Column {0}",index),"",true,true,true,true,index,"100px","",false);
* this.columns.Add(c);
* if (c.custom_aggregate) aggregates.Add(new aggregate(c.name,c.alias,c.groupOrder));
*
* index++;
* }//end while read
* }//end hasrows
* }//end reader
*
* }//end using da
* }//end func
*/
public parameters get_parameter_from_html_name(string name, string value)
{
parameters param = new parameters();
core.parameter matchedP = this.parameters.Find(x => x.htmlName == name); //find the parameter in the list with lamba find
if (null != matchedP && null != matchedP.name && value != null) //ok we got a match...
{
if (String.IsNullOrWhiteSpace(matchedP.name))
{
return(null);
}
if (matchedP.type == "daterange")
{
string to = "";
string from = "";
if (!String.IsNullOrWhiteSpace(value))
{
string[] tokens = Regex.Split(value, " - ");
if (tokens.Length > 0)
{
from = tokens[0];
}
if (tokens.Length > 1)
{
to = tokens[1];
}
}
if (!string.IsNullOrWhiteSpace(from))
{
param.add("@" + matchedP.name + "_from", from);
}
if (!string.IsNullOrWhiteSpace(to))
{
param.add("@" + matchedP.name + "_to", to);
}
}
else //end if daterange
{
if (null != value)
{
param.add("@" + matchedP.name + "_from", value);
}
}
}
return(param);
}
public int create_export_link_id(string web_token,security.titan_token s_token) {
string query=@"INSERT INTO titanDWS_exports ([token],[json],[web_token])
VALUES (@token,@json,@web_token);
SELECT CAST(SCOPE_IDENTITY() AS int) AS ProfileKey;";
System.Web.Script.Serialization.JavaScriptSerializer jss=new System.Web.Script.Serialization.JavaScriptSerializer();
string json=jss.Serialize(this.input);
string token=jss.Serialize(s_token);
if(string.IsNullOrWhiteSpace(web_token)) web_token="error";
parameters param=new parameters();
param.add("@web_token" ,web_token);
param.add("@token" ,token);
param.add("@json" ,json);
column_data res=db.execute_scalar("titan",query,param);
int res_id=0;
Int32.TryParse((string)res.ToString(),out res_id);
return res_id;
//return 0;
}
public parameters generate_crud_parameters() {
parameters param=new parameters();
PropertyInfo[] properties = typeof(T).GetProperties();
Type t=this.GetType();
foreach (PropertyInfo pi in t.GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic)) {
string field_name = pi.Name;
if(field_name=="_table" || field_name=="_pk" || field_name=="_json") continue; //dont paramaterize the base properties
object property_value = pi.GetValue(this, null);
if(null!=_json) {
bool found=false;
foreach(string s in _json) {
if (s==field_name) {
string serial=JsonConvert.SerializeObject(property_value);
if(pi.GetValue(this, null)!=null) {
param.add("@"+field_name,serial);
found=true;
break;
}
}
}
if(found) continue;
}
if(property_value is Guid) {
if(((Guid)property_value) == Guid.Empty) continue;
param.add("@"+field_name,(Guid)property_value);
continue;
}
if (property_value is DateTime) {
if((DateTime)property_value==DateTime.MinValue) continue;
param.add("@"+field_name,(DateTime)property_value);
continue;
}
if(property_value is string) {
param.add("@"+field_name,(string)property_value);
continue;
}
if(property_value is int) {
param.add("@"+field_name,(int)property_value);
continue;
}
if(property_value is bool) {
param.add("@"+field_name,(bool)property_value);
continue;
}
if (pi.GetValue(this, null)!=null) {
param.add("@"+field_name,property_value);
continue;
}
}
return param;
}
public parameters generate_crud_parameters() {
/*d.parameters.RemoveAll(item => item == null);
int p;
while ((p=parameters.FindIndex(x=>String.IsNullOrWhiteSpace(x.name) ))>0) {
parameters.RemoveAt(p);
}*/
/*string serial_linked_methods=jss.Serialize(d.linked_methods);
string serial_columns =jss.Serialize(d.);
string serial_param =jss.Serialize(d.parameters);
*/
parameters param=new parameters();
PropertyInfo[] properties = typeof(method).GetProperties();
Type t=this.GetType();
foreach (PropertyInfo pi in t.GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic)) {
string field_name = pi.Name;
if(field_name=="_table" || field_name=="_pk" || field_name=="_json") continue; //dont paramaterize the base properties
object property_value = pi.GetValue(this, null);
if(null!=_json) {
bool found=false;
foreach(string s in _json) {
if (s==field_name) {
string serial=JsonConvert.SerializeObject(property_value);
if(pi.GetValue(this, null)!=null) {
param.add("@"+field_name,serial);
found=true;
break;
}
}
}
if(found) continue;
}
if(property_value is Guid) {
if(((Guid)property_value) == Guid.Empty) continue;
param.add("@"+field_name,(Guid)property_value);
continue;
}
if (property_value is DateTime) {
if((DateTime)property_value==DateTime.MinValue) continue;
param.add("@"+field_name,(DateTime)property_value);
continue;
}
if(property_value is string) {
param.add("@"+field_name,(string)property_value);
continue;
}
if(property_value is int) {
param.add("@"+field_name,(int)property_value);
continue;
}
if(property_value is bool) {
param.add("@"+field_name,(bool)property_value);
continue;
}
if (pi.GetValue(this, null)!=null) {
param.add("@"+field_name,property_value);
continue;
}
}
return param;
}
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
private parameters generate_crud_parameters(object o)
{
parameters param = new parameters();
Type T = o.GetType();
PropertyInfo[] properties = T.GetProperties();
foreach (PropertyInfo pi in T.GetProperties(BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic))
{
string field_name = pi.Name;
//if (field_name == "_table" || field_name == "_pk" || field_name == "_json") continue; //dont paramaterize the base properties
object property_value = pi.GetValue(o, null);
/*if (null != _json) {
* bool found = false;
* foreach (string s in _json) {
* if (s == field_name) {
*
* string serial = JsonConvert.SerializeObject(property_value);
* if (pi.GetValue(this, null) != null) {
* param.add("@" + field_name, serial);
* found = true;
* break;
* }
* }
* }
* if (found) continue;
* }*/
if (property_value is Guid)
{
if (((Guid)property_value) == Guid.Empty)
{
continue;
}
param.add("@" + field_name, (Guid)property_value);
continue;
}
if (property_value is DateTime)
{
if ((DateTime)property_value == DateTime.MinValue)
{
continue;
}
param.add("@" + field_name, (DateTime)property_value);
continue;
}
if (property_value is string)
{
param.add("@" + field_name, (string)property_value);
continue;
}
if (property_value is int)
{
param.add("@" + field_name, (int)property_value);
continue;
}
if (property_value is bool)
{
param.add("@" + field_name, (bool)property_value);
continue;
}
if (pi.GetValue(this, null) != null)
{
param.add("@" + field_name, property_value);
continue;
}
}
return(param);
}
//borrowed from the internet.... stackoverflow
/*public void backup_method() {
* JavaScriptSerializer jss=new JavaScriptSerializer();
* string method_json=jss.Serialize(this);
* db.execute_non_query("titan","INSERT INTO titanDWS_history ([titanDWS_id],[json]) VALUES (@id,@json)",new string[,]{ { "id",this.id.ToString()},{ "json",method_json} } );
* }*/
public parameters execute_load_parameters(security.titan_token token, Dictionary <string, string> form_parameters, bool init)
{
parameters paramList = new parameters();
if (init) //caching...
{
get_signature();
get_signature_defaults();
}
models.lambda input = new models.lambda();
if (null != token)
{
input.reference1 = token.r1; //good place for the defaults
input.reference2 = token.r2;
input.reference3 = token.r3;
input.reference4 = token.r4;
input.reference5 = token.r5;
input.reference6 = token.r6;
input.reference7 = token.r7;
input.reference8 = token.r8;
input.reference9 = token.r9;
input.reference10 = token.r10;
input.reference11 = token.r11;
input.reference12 = token.r12;
input.reference13 = token.r13;
input.reference14 = token.r14;
input.reference15 = token.r15;
}
input.parameters = new Dictionary <string, string>();
if (init) //testing data? i remember this then i did something
{
foreach (titan.core.parameter p in this.parameters) //and then im like... important but move? <-Im not sure?
{
string value = p.test; //Rewrite test data injection (pretty sure I cleaned this up)
if (String.IsNullOrWhiteSpace(p.test))
{
value = "";
}
parameters range = get_parameter_from_html_name(p.htmlName, p.test);
if (null != range)
{
paramList.AddRange(range);
}
}
if (null != this.external_map &&
null != this.sig_init)
{
if (!string.IsNullOrWhiteSpace(external_map.reference1))
{
paramList.add("@" + external_map.reference1, sig_init.r1);
}
if (!string.IsNullOrWhiteSpace(external_map.reference2))
{
paramList.add("@" + external_map.reference2, sig_init.r2);
}
if (!string.IsNullOrWhiteSpace(external_map.reference3))
{
paramList.add("@" + external_map.reference3, sig_init.r3);
}
if (!string.IsNullOrWhiteSpace(external_map.reference4))
{
paramList.add("@" + external_map.reference4, sig_init.r4);
}
if (!string.IsNullOrWhiteSpace(external_map.reference5))
{
paramList.add("@" + external_map.reference5, sig_init.r5);
}
if (!string.IsNullOrWhiteSpace(external_map.reference6))
{
paramList.add("@" + external_map.reference6, sig_init.r6);
}
if (!string.IsNullOrWhiteSpace(external_map.reference7))
{
paramList.add("@" + external_map.reference7, sig_init.r7);
}
if (!string.IsNullOrWhiteSpace(external_map.reference8))
{
paramList.add("@" + external_map.reference8, sig_init.r8);
}
if (!string.IsNullOrWhiteSpace(external_map.reference9))
{
paramList.add("@" + external_map.reference9, sig_init.r9);
}
if (!string.IsNullOrWhiteSpace(external_map.reference10))
{
paramList.add("@" + external_map.reference10, sig_init.r10);
}
if (!string.IsNullOrWhiteSpace(external_map.reference11))
{
paramList.add("@" + external_map.reference11, sig_init.r11);
}
if (!string.IsNullOrWhiteSpace(external_map.reference12))
{
paramList.add("@" + external_map.reference12, sig_init.r12);
}
if (!string.IsNullOrWhiteSpace(external_map.reference13))
{
paramList.add("@" + external_map.reference13, sig_init.r13);
}
if (!string.IsNullOrWhiteSpace(external_map.reference14))
{
paramList.add("@" + external_map.reference14, sig_init.r14);
}
if (!string.IsNullOrWhiteSpace(external_map.reference15))
{
paramList.add("@" + external_map.reference15, sig_init.r15);
}
} //end if external_data_maping exists
}
else //end p1 init
{
input.parameters = form_parameters;
//System variables
if (null != this.external_map)
{
if (!string.IsNullOrWhiteSpace(external_map.reference1))
{
paramList.add("@" + external_map.reference1, sig_init.r1);
}
if (!string.IsNullOrWhiteSpace(external_map.reference2))
{
paramList.add("@" + external_map.reference2, sig_init.r2);
}
if (!string.IsNullOrWhiteSpace(external_map.reference3))
{
paramList.add("@" + external_map.reference3, sig_init.r3);
}
if (!string.IsNullOrWhiteSpace(external_map.reference4))
{
paramList.add("@" + external_map.reference4, sig_init.r4);
}
if (!string.IsNullOrWhiteSpace(external_map.reference5))
{
paramList.add("@" + external_map.reference5, sig_init.r5);
}
if (!string.IsNullOrWhiteSpace(external_map.reference6))
{
paramList.add("@" + external_map.reference6, sig_init.r6);
}
if (!string.IsNullOrWhiteSpace(external_map.reference7))
{
paramList.add("@" + external_map.reference7, sig_init.r7);
}
if (!string.IsNullOrWhiteSpace(external_map.reference8))
{
paramList.add("@" + external_map.reference8, sig_init.r8);
}
if (!string.IsNullOrWhiteSpace(external_map.reference9))
{
paramList.add("@" + external_map.reference9, sig_init.r9);
}
if (!string.IsNullOrWhiteSpace(external_map.reference10))
{
paramList.add("@" + external_map.reference10, sig_init.r10);
}
if (!string.IsNullOrWhiteSpace(external_map.reference11))
{
paramList.add("@" + external_map.reference11, sig_init.r11);
}
if (!string.IsNullOrWhiteSpace(external_map.reference12))
{
paramList.add("@" + external_map.reference12, sig_init.r12);
}
if (!string.IsNullOrWhiteSpace(external_map.reference13))
{
paramList.add("@" + external_map.reference13, sig_init.r13);
}
if (!string.IsNullOrWhiteSpace(external_map.reference14))
{
paramList.add("@" + external_map.reference14, sig_init.r14);
}
if (!string.IsNullOrWhiteSpace(external_map.reference15))
{
paramList.add("@" + external_map.reference15, sig_init.r15);
}
} //end if
if (null != input.parameters && 0 < input.parameters.Count)
{
foreach (KeyValuePair <string, string> p in input.parameters)
{
core.parameter matchedP = this.parameters.Find(x => x.htmlName == p.Key); //find the parameter in the list with lamba find
if (null != matchedP && null != p.Value && null != matchedP.name) //ok we got a match...
{
if (String.IsNullOrWhiteSpace(matchedP.name))
{
continue;
}
if (matchedP.type == "daterange")
{
string to = null;
string from = null;
if (!String.IsNullOrWhiteSpace(p.Value))
{
string[] tokens = Regex.Split(p.Value, " - ");
if (tokens.Length > 0)
{
from = tokens[0];
}
if (tokens.Length > 1)
{
to = tokens[1];
}
}
if (string.IsNullOrWhiteSpace(to))
{
paramList.add("@" + matchedP.name + "_from", from);
}
if (string.IsNullOrWhiteSpace(from))
{
paramList.add("@" + matchedP.name + "_to", to);
}
}
else
{
if (null != p.Value)
{
paramList.add("@" + matchedP.name, p.Value);
}
}
} //end if matched
} //end parameter Loop
} //end if params
}//end else
return(paramList);
} //end create parameters
