Exemple #1
0
        public static void log(string iText1, string iText2 = "", string conn = "")
        {
            if (conn == "")
            {
                conn = My.getMachineName() + "_PricingService";
            }
            string zText1 = "null"; string zText2 = "null";

            if (iText1 != null)
            {
                if (iText1.Length > 999)
                {
                    zText1 = iText1.Substring(0, 990);
                }
                else
                {
                    zText1 = iText1;
                }
            }
            if (iText2 != null)
            {
                if (iText2.Length > 1999)
                {
                    zText2 = iText2.Substring(0, 990);
                }
                else
                {
                    zText2 = iText2;
                }
            }
            dbCUD("IF OBJECT_ID('dbo.sLog', 'U') IS NOT NULL insert into [sLog] ([text1],[text2]) values (" + toSql(zText1) + "," + toSql(zText2) + ")", conn);
        }
Exemple #2
0
        public static System.Data.DataSet dbReadSet(string iSQL, string iConn)
        {
            // string zSwitch = getSystemName();
            // My.print(iConn);
            // My.log(iSQL, iConn);

            using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection())
            {
                if (iSQL == null || iSQL == "")
                {
                    logError("Warning in dbRead", "NULL value in SQL string");
                    // My.print("Warning in dbRead, NULL value in SQL string");
                    return(null);
                }
                else if (iSQL.ToLower().Contains("insert into") || iSQL.ToLower().Contains("u_pdate") || iSQL.ToLower().Contains("delete"))
                {
                    logError("Warning in dbRead: Use dbCUD instead.", iSQL);
                }

                conn.ConnectionString = getDbConnString(iConn);
                conn.Open();
                System.Data.SqlClient.SqlCommand     command = new System.Data.SqlClient.SqlCommand(iSQL, conn);
                System.Data.SqlClient.SqlDataAdapter da      = new System.Data.SqlClient.SqlDataAdapter(command);
                System.Data.DataSet dataSet = new System.Data.DataSet();
                try
                {
                    da.Fill(dataSet);
                    return(dataSet);
                }
                catch (Exception ex)
                {
                    My.logError("Error in dbReadSet:" + iSQL, ex.Message);
                    // My.print(ex.Message);
                    // return (My.dbReadSet("select " + My.toSql("error: " + ex.Message) + " as ERROR"));
                    throw new System.InvalidOperationException("dbReadSet error using: '" + iConn + "'\n\nFor sql: '" + iSQL + "' \n\n Error is:'" + ex + "'");
                }
                finally
                {
                    if (conn != null)
                    {
                        conn.Dispose();
                    }

                    if (command != null)
                    {
                        command.Dispose();
                    }
                }
            }
        }
Exemple #3
0
        public static string pagination(string view, string page = "", string rows = "", string sort = "", string order = "", string q = "", string filterRules = "", string preFilter = "")
        {
            /************************  Description ************************/
            // listen to easyui pagination and datagrid standard
            // returns: { rows:[{id:12,name:"a1"},{id:13,name:"a2"}], total:6, debug:""}

            /************************  Input's ************************/
            // here

            /************************  Set stuff ************************/
            string zSql      = "";
            string zSelect   = "*";
            string zKeyField = "";
            string zSchema   = view.Split('.')[0];
            string zTable    = view.Split('.')[1];

            if (view.Split('.').Length > 2)
            {
                zKeyField = view.Split('.')[2];
            }
            string zWhere = "";

            string  zConn = My.getMachineName() + "_pricingService";
            JObject jo    = new JObject();

            jo["zKeyField"] = zKeyField;

            /************************  Check stuff ************************/
            if (rows == "")
            {
                rows = "50";
            }
            if (order == "")
            {
                order = "1";
            }
            if (zKeyField != "" && q != "")
            {
                zWhere = My.addWhere(zWhere, string.Format("[{0}] like '{1}%'", zKeyField, q));
            }
            zWhere            = My.addWhere(zWhere, My.jsonFieldsToWhere2(filterRules));
            jo["filterRules"] = filterRules;

            /************************  Do stuff ************************/
            jo["q"] = q;
            int zOffset = (Int32.Parse(page) - 1) * Int32.Parse(rows);

            zSql  = "SELECT {0} FROM [{1}].[{2}] {7} ORDER BY {3} {4} OFFSET {5} ROWS FETCH NEXT {6} ROWS ONLY ;SELECT COUNT(*) FROM [{1}].[{2}] {7};";
            zSql += "SELECT COLUMN_NAME as field, COLUMN_NAME as title, len(COLUMN_NAME)*7+30 as width from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA='{1}' AND TABLE_NAME = '{2}' ;";
            zSql  = string.Format(zSql
                                  , zSelect // 0: SELECT
                                  , zSchema // 1: SCHEMA
                                  , zTable  // 2: FROM
                                  , sort    // 3: ORDER BY
                                  , order   // 4: ORDER BY
                                  , zOffset // 5: OFFSET
                                  , rows    // 6: FETCH NEXT
                                  , zWhere  // 7: WHERE
                                  );

            // return (zSql);

            DataSet ds = My.dbReadSet(zSql, zConn);

            jo["rows"]    = JArray.Parse(My.oToJson(ds.Tables[0]));
            jo["total"]   = ds.Tables[1].Rows[0][0].ToString();
            jo["columns"] = JArray.Parse("[" + My.oToJson(ds.Tables[2]) + "]");
            jo["zSql"]    = zSql;
            return(jo.ToString());
        }