Example #1
0
            }//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;
            }
Example #2
0
        }    //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
Example #3
0
        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);
        }
Example #4
0
            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);
                }
            }
Example #5
0
            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;
            }
Example #6
0
        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"]);
        }
Example #7
0
        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);
        }
Example #8
0
        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());
        }
Example #9
0
        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
Example #10
0
 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
Example #11
0
        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());
        }
Example #12
0
        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);
        }
Example #13
0
    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;
    }
Example #14
0
 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));
         }
     }
 }
Example #15
0
            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
Example #16
0
        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);
        }
Example #17
0
        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);
        }
Example #18
0
 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]");
 }