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