示例#1
0
        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);
        }
示例#2
0
        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());
        }
示例#3
0
 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);
 }
示例#4
0
 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);
 }
示例#5
0
 public int getID()
 {
     if (PrimaryKeyField.isEmpty() == false)
     {
         return(cmd.getIntValue(PrimaryKeyField));
     }
     else
     {
         return(g.parseInt(cmd[cmd.Count - 1].Value));
     }
 }
示例#6
0
        /// <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());
        }
示例#7
0
        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());
        }
示例#8
0
 private void initFields()
 {
     _id = new PrimaryKeyField <projectNS.Project, intermediateNS.Project, L2SNS.Project, long>(this, t => t.Id, (t, val) => t.Id = val);
 }
示例#9
0
 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);
 }
示例#10
0
 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);
 }
示例#11
0
 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);
 }
示例#12
0
        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);
        }
示例#13
0
        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);
        }