private dynamic BuildPagedResult(string primaryKeyField = "", string where = "", string orderBy = "", string columns = "*", int pageSize = 20, int currentPage = 1, params object[] args)
{
dynamic result = new ExpandoObject();
var countSQL = string.Format("SELECT COUNT({0}) FROM {1}", PrimaryKeyField.Split(KeyColSeparator).FirstOrDefault(), TableName);
if (String.IsNullOrEmpty(orderBy))
{
orderBy = string.IsNullOrEmpty(primaryKeyField) ? PrimaryKeyField : primaryKeyField;
}
if (!string.IsNullOrEmpty(where))
{
if (!where.Trim().StartsWith("where", StringComparison.CurrentCultureIgnoreCase))
{
where = " WHERE " + where;
}
}
var query = string.Format("SELECT {0} FROM (SELECT ROW_NUMBER() OVER (ORDER BY {1}) AS Row, {0} FROM {2} {3}) AS {2} ", columns, orderBy, TableName, where);
var pageStart = (currentPage - 1) * pageSize;
query += string.Format(" WHERE Row > {0} AND Row <={1}", pageStart, (pageStart + pageSize));
countSQL += where;
result.TotalRecords = Scalar(countSQL, args);
result.TotalPages = result.TotalRecords / pageSize;
if (result.TotalRecords % pageSize > 0)
{
result.TotalPages += 1;
}
result.Items = Query(string.Format(query, columns, TableName), args);
return(result);
}
public string getInsertScript(string[] Columns)
{
var cols = new List <string>();
//if (!isIdentity) cols.Add(PrimaryKey);
cols.AddRange(Columns);
StringBuilder sb1 = new StringBuilder();
sb1.AppendFormat("INSERT INTO {0} \n", TableName);
sb1.AppendLine("(");
int i = 0;
foreach (string s in cols)
{
if (isIdentity && s.ToLower() == PrimaryKeyField.ToLower())
{
continue;
}
if (i > 0)
{
sb1.Append(",");
}
sb1.Append(s);
sb1.AppendLine();
i++;
}
sb1.AppendLine(")");
sb1.AppendLine(" VALUES( ");
i = 0;
foreach (var s in cols)
{
if (isIdentity && s.ToLower() == PrimaryKeyField.ToLower())
{
continue;
}
if (i > 0)
{
sb1.Append(",");
}
sb1.AppendFormat("@{0}", s);
sb1.AppendLine();
i++;
}
sb1.AppendLine(" )");
if (isIdentity)
{
sb1.AppendLine("select Scope_identity()");
}
return(sb1.ToString());
}
private void initFields()
{
_id = new PrimaryKeyField <projectNS.User, intermediateNS.User, L2SNS.User, long>(this, t => t.Id, (t, val) => t.Id = val);
_accountId = new EntityField <projectNS.User, intermediateNS.User, L2SNS.User, long>(this, t => t.AccountId, (t, val) => t.AccountId = val);
_companyId = new EntityField <projectNS.User, intermediateNS.User, L2SNS.User, long>(this, t => t.CompanyId, (t, val) => t.CompanyId = val);
_account = new IntermediateEntityReference <projectNS.User, projectNS.Account, intermediateNS.User, intermediateNS.Account, L2SNS.User, L2SNS.Account>(this, __u => __u.Account, (__u, __account) => __u.Account = __account, __account => __account._user, false);
_company = new IntermediateEntityReference <projectNS.User, projectNS.Company, intermediateNS.User, intermediateNS.Company, L2SNS.User, L2SNS.Company>(this, __u => __u.Company, (__u, __company) => __u.Company = __company, __company => __company._users, false);
}
private void initFields()
{
_id = new PrimaryKeyField <projectNS.Company, intermediateNS.Company, L2SNS.Company, long>(this, t => t.Id, (t, val) => t.Id = val);
_name = new EntityField <projectNS.Company, intermediateNS.Company, L2SNS.Company, string>(this, t => t.Name, (t, val) => t.Name = val);
_type = new EntityField <projectNS.Company, intermediateNS.Company, L2SNS.Company, string>(this, t => t.Type, (t, val) => t.Type = val);
_portfolioId = new EntityField <projectNS.Company, intermediateNS.Company, L2SNS.Company, long>(this, t => t.PortfolioId, (t, val) => t.PortfolioId = val);
_users = new IntermediateEntityCollection <projectNS.Company, projectNS.User, intermediateNS.Company, intermediateNS.User, L2SNS.Company, L2SNS.User>(this, __user => __user._company);
_portfolio = new IntermediateEntityReference <projectNS.Company, projectNS.Portfolio, intermediateNS.Company, intermediateNS.Portfolio, L2SNS.Company, L2SNS.Portfolio>(this, __c => __c.Portfolio, (__c, __portfolio) => __c.Portfolio = __portfolio, __portfolio => __portfolio._company, false);
}
public int getID()
{
if (PrimaryKeyField.isEmpty() == false)
{
return(cmd.getIntValue(PrimaryKeyField));
}
else
{
return(g.parseInt(cmd[cmd.Count - 1].Value));
}
}
/// <summary>
/// Returns a single row from the database
/// </summary>
public virtual dynamic Single(string columns, params object[] key)
{
var sql = string.Format("SELECT {0} FROM {1}", columns, TableName);
var primaryKeyElem = PrimaryKeyField.Split(KeyColSeparator).Select(x => x.Trim()).ToArray();
for (var i = 0; i < primaryKeyElem.Length; i++)
{
if (i == 0)
{
sql = sql + " WHERE " + primaryKeyElem[i] + " = @" + i;
}
else
{
sql = sql + " AND " + primaryKeyElem[i] + " = @" + i;
}
}
var items = Query(sql, key).ToList();
return(items.FirstOrDefault());
}
public string getUpdateScript(params string[] columns)
{
StringBuilder sb1 = new StringBuilder();
sb1.AppendFormat("update {0} SET\n", TableName);
int i = 0;
foreach (var s in columns)
{
if (s.ToLower() != PrimaryKeyField.ToLower())
{
if (i > 0)
{
sb1.Append(",");
}
sb1.AppendFormat("{0} = @{0}", s);
sb1.AppendLine();
i++;
}
}
sb1.AppendFormat("\n WHERE {0} = @{0}", PrimaryKeyField);
return(sb1.ToString());
}
private void initFields()
{
_id = new PrimaryKeyField <projectNS.Project, intermediateNS.Project, L2SNS.Project, long>(this, t => t.Id, (t, val) => t.Id = val);
}
private void initFields()
{
_id = new PrimaryKeyField <projectNS.Account, intermediateNS.Account, L2SNS.Account, long>(this, t => t.Id, (t, val) => t.Id = val);
_user = new IntermediateEntityReference <projectNS.Account, projectNS.User, intermediateNS.Account, intermediateNS.User, L2SNS.Account, L2SNS.User>(this, __a => __a.User, (__a, __user) => __a.User = __user, __user => __user._account, false);
}
private void initFields()
{
_id = new PrimaryKeyField <projectNS.PortfolioEntry, intermediateNS.PortfolioEntry, L2SNS.PortfolioEntry, long>(this, t => t.Id, (t, val) => t.Id = val);
_portfolioId = new EntityField <projectNS.PortfolioEntry, intermediateNS.PortfolioEntry, L2SNS.PortfolioEntry, long>(this, t => t.PortfolioId, (t, val) => t.PortfolioId = val);
_portfolio = new IntermediateEntityReference <projectNS.PortfolioEntry, projectNS.Portfolio, intermediateNS.PortfolioEntry, intermediateNS.Portfolio, L2SNS.PortfolioEntry, L2SNS.Portfolio>(this, __p => __p.Portfolio, (__p, __portfolio) => __p.Portfolio = __portfolio, __portfolio => __portfolio._portfolioEntries, false);
}
private void initFields()
{
_id = new PrimaryKeyField <projectNS.Portfolio, intermediateNS.Portfolio, L2SNS.Portfolio, long>(this, t => t.Id, (t, val) => t.Id = val);
_portfolioEntries = new IntermediateEntityCollection <projectNS.Portfolio, projectNS.PortfolioEntry, intermediateNS.Portfolio, intermediateNS.PortfolioEntry, L2SNS.Portfolio, L2SNS.PortfolioEntry>(this, __portfolioentry => __portfolioentry._portfolio);
_company = new IntermediateEntityReference <projectNS.Portfolio, projectNS.Company, intermediateNS.Portfolio, intermediateNS.Company, L2SNS.Portfolio, L2SNS.Company>(this, __p => __p.Company, (__p, __company) => __p.Company = __company, __company => __company._portfolio, false);
}
public clsCmd getInsertCommand(DataTable t
, clsCmd cmd)
{
clsCmd cmd2 = new clsCmd();
int i = 0;
StringBuilder sb1 = new StringBuilder();
if (isIdentity == false && databaseType == "mssql")
{
sb1.AppendLine("declare @id int ");
sb1.AppendFormat("set @id = (select isnull(max({0}),0) + 1 from {1}) \n\r ", PrimaryKeyField, TableName);
}
sb1.AppendFormat("insert into {0} \n\r (", TableName);
if (isIdentity == false)
{
sb1.AppendFormat(" {0} ", PrimaryKeyField);
i++;
}
//simple column selection
foreach (DataColumn col in t.Columns)
{
if (cmd.ContainFields(col.ColumnName) && col.ColumnName.ToLower() != PrimaryKeyField.ToLower())
{
if (i > 0)
{
sb1.Append(",");
}
sb1.AppendFormat(" {0} ", col.ColumnName);
i++;
cmd2.setValue(col.ColumnName, cmd[col.ColumnName].Value);
}
}
//simeple column selection for filedata
foreach (FileData file in cmd.Files)
{
if (t.Columns.Contains(file.FieldName) && file.FieldName.ToLower() != PrimaryKeyField.ToLower())
{
if (i > 0)
{
sb1.Append(",");
}
sb1.AppendFormat(" {0} ", file.FieldName);
i++;
cmd2.setValue(file.FieldName, file.Data);
if (t.Columns.Contains(file.FieldName + "_contenttype"))
{
sb1.AppendFormat(", {0} ", file.FieldName + "_ContentType");
cmd2.setValue(file.FieldName + "_ContentType", file.ContentType);
}
}
}
i = 0;
sb1.AppendLine(") \n\r values(");
if (isIdentity == false)
{
sb1.AppendFormat(" @id ", PrimaryKeyField);
i++;
}
//simple value selection
foreach (DataColumn col in t.Columns)
{
if (cmd.ContainFields(col.ColumnName) && col.ColumnName.ToLower() != PrimaryKeyField.ToLower())
{
if (i > 0)
{
sb1.Append(",");
}
sb1.AppendFormat(" @{0} ", col.ColumnName);
i++;
}
}
//simple value selection for file.
foreach (FileData file in cmd.Files)
{
if (t.Columns.Contains(file.FieldName) && file.FieldName.ToLower() != PrimaryKeyField.ToLower())
{
if (i > 0)
{
sb1.Append(",");
}
sb1.AppendFormat(" @{0} ", file.FieldName);
i++;
if (t.Columns.Contains(file.FieldName + "_contenttype"))
{
sb1.AppendFormat(", @{0} ", file.FieldName + "_ContentType");
}
}
}
sb1.Append(" )");
if (databaseType == "mssql")
{
if (isIdentity)
{
sb1.AppendLine("select SCOPE_IDENTITY()");
}
else
{
sb1.AppendLine("select @id");
}
}
cmd2.SQL = sb1.ToString();
return(cmd2);
}
public clsCmd getUpdateCommand(DataTable t
, clsCmd cmd)
{
int iID = cmd.getIntValue(PrimaryKeyField);
if (iID == 0)
{
throw new Exception("Id value is 0, you can't update record !");
}
StringBuilder sb1 = new StringBuilder();
clsCmd cmd2 = new clsCmd();
int i = 0;
sb1.AppendFormat("update {0} set \r\n", TableName);
foreach (DataColumn col in t.Columns)
{
if (cmd.ContainFields(col.ColumnName) && col.ColumnName.ToLower() != PrimaryKeyField.ToLower())
{
if (i > 0)
{
sb1.Append(",");
}
sb1.AppendFormat(" {0} = @{0} ", col.ColumnName);
i++;
cmd2.setValue(col.ColumnName, cmd[col.ColumnName].Value);
}
}
foreach (FileData file in cmd.Files)
{
if (t.Columns.Contains(file.FieldName) && file.FieldName.ToLower() != PrimaryKeyField.ToLower())
{
if (i > 0)
{
sb1.Append(",");
}
sb1.AppendFormat(" {0} = @{0} ", file.FieldName);
i++;
cmd2.setValue(file.FieldName, file.Data);
if (t.Columns.Contains(file.FieldName + "_contenttype"))
{
sb1.AppendFormat(", {0} = @{0} ", file.FieldName + "_ContentType");
cmd2.setValue(file.FieldName + "_ContentType", file.ContentType);
}
}
}
sb1.AppendFormat(" where {0} = {1} \n\r", PrimaryKeyField, iID);
if (databaseType == "mssql")
{
sb1.AppendFormat("select {0}", iID);
}
cmd2.SQL = sb1.ToString();
return(cmd2);
}
