Esempio n. 1
0
        /// <summary>
        /// Create entity - used for tables don't have primary key
        /// </summary>
        public static bool Add <T>(IDbConnection db, T entity)
        {
            List <string> rootFields = new List <string>();

            PropertyInfo[] pinfos = entity.GetType().GetProperties();
            foreach (PropertyInfo prop in pinfos)
            {
                object[] attrs      = prop.GetCustomAttributes(true);
                bool     isExternal = attrs.Any(a => (a as External) != null);
                if (isExternal)
                {
                    continue;
                }
                rootFields.Add(prop.Name);
            }

            List <string> dapperFields = new List <string>();

            rootFields.ForEach(f => { dapperFields.Add("@" + f); });
            string        tableName = EXTable.GetTableName <T>();
            StringBuilder query     = new StringBuilder();

            query.AppendLine("INSERT INTO " + tableName + " (");
            query.AppendLine(string.Join(",", rootFields) + ")");
            query.AppendLine("VALUES(" + string.Join(",", dapperFields) + ")");

            string sqlQuery = query.ToString();

            return(1 == db.Execute(sqlQuery, entity));
        }
Esempio n. 2
0
        //Get all related object
        //public static decimal? Min(this IEnumerable<decimal?> source);
        //public static decimal? Sum<TSource>(this IEnumerable<TSource> source, Func<TSource, int> selector);
        //public static decimal? Max<TSource>(this IEnumerable<TSource> source, Func<TSource, decimal?> selector);
        #endregion

        #region Update by Id //TODO: Later, can use expression to get conditon for updating
        /// <summary>
        /// Update entity
        /// </summary>
        public static bool Update <T>(IDbConnection db, T entity)
        {
            List <string> rootFields = new List <string>();

            PropertyInfo[] pinfos = entity.GetType().GetProperties();
            foreach (PropertyInfo prop in pinfos)
            {
                object[] attrs = prop.GetCustomAttributes(true);
                if (attrs.Any(a => (a as External) != null || (a as PrimaryKey) != null))
                {
                    continue;
                }

                rootFields.Add(prop.Name + " = @" + prop.Name);
            }

            StringBuilder query = new StringBuilder();

            query.AppendLine("UPDATE " + EXTable.GetTableName <T>());
            query.AppendLine("SET");
            query.AppendLine(string.Join(",", rootFields));
            query.AppendLine("WHERE");
            string primaryKey = GetPrimaryIdColumn(typeof(T));

            query.AppendLine(primaryKey + " = @" + primaryKey);

            string sqlQuery = query.ToString();

            return(1 == db.Execute(sqlQuery, entity));
        }
Esempio n. 3
0
        /// <summary>
        /// Create entity - used for tables have primary key
        /// </summary>
        public static int Add <T>(T entity, IDbConnection db)
        {
            List <string> rootFields = new List <string>();

            PropertyInfo[] pinfos = entity.GetType().GetProperties();
            foreach (PropertyInfo prop in pinfos)
            {
                object[] attrs     = prop.GetCustomAttributes(true);
                bool     isIgnored = attrs.Any(a => (a as External) != null || (a as PrimaryKey) != null);
                if (isIgnored)
                {
                    continue;
                }
                rootFields.Add(prop.Name);
            }

            List <string> dapperFields = new List <string>();

            rootFields.ForEach(f => { dapperFields.Add("@" + f); });
            string        tableName = EXTable.GetTableName <T>();
            StringBuilder query     = new StringBuilder();

            query.AppendLine("INSERT INTO " + tableName + " (");
            query.AppendLine(string.Join(",", rootFields) + ")");
            query.AppendLine("Output Inserted." + GetPrimaryIdColumn(typeof(T)));
            query.AppendLine("VALUES(" + string.Join(",", dapperFields) + ")");

            string sqlQuery = query.ToString();

            return(db.Query <int>(sqlQuery, entity).FirstOrDefault());
        }
Esempio n. 4
0
        /// <summary>
        /// "Any" keyword is used without expression
        /// Way to use:
        /// Condition[Table].Any()
        /// </summary>
        public static bool Any <T>(IDbConnection db)
        {
            SQlQuery sql    = new SQlQuery(EXTable.GetTableName <T>());
            string   newSql = @"SELECT CASE WHEN ( EXISTS ( " + sql.Query + " )) THEN cast(1 as bit) ELSE cast(0 as bit) END";
            var      result = db.Query <bool>(newSql).FirstOrDefault();

            return(result);
        }
Esempio n. 5
0
        /// <summary>
        /// "Count" keyword is used without expression --
        /// Way to use: [Table].Count()
        /// </summary>
        public static int Count <T>(IDbConnection db)
        {
            SQlQuery query  = new SQlQuery(EXTable.GetTableName <T>());
            string   newSql = @"SELECT COUNT(1) FROM ( " + query.Query + " ) as Extend";
            var      result = db.Query <int>(newSql).FirstOrDefault();

            return(result);
        }
Esempio n. 6
0
        public static QueryResult <T> Get <T>(Expression <Func <T, bool> > expression)
        {
            var      body  = expression.Body as BinaryExpression;
            SQlQuery query = new SQlQuery(EXTable.GetTableName <T>());

            WalkTree <T>(expression, ref query);

            return(new QueryResult <T>(query));
        }
Esempio n. 7
0
        /// <summary>
        /// "Any" keyword is used with expression
        /// Way to use:
        /// Any[Table](x=> x.id = 10);
        /// Find[Table](x=> x.id = 10).Any(x=>x.id = 10);
        /// </summary>
        public static bool Any <T>(IDbConnection db, Expression <Func <T, bool> > expression)
        {
            var      body  = expression.Body as BinaryExpression;
            SQlQuery query = new SQlQuery(EXTable.GetTableName <T>());

            WalkTree <T>(expression, ref query);

            string newSql = @"SELECT CASE WHEN ( EXISTS ( " + query.Query + " )) THEN cast(1 as bit) ELSE cast(0 as bit) END";
            var    result = db.Query <bool>(newSql, (object)query.Param).FirstOrDefault();

            return(result);
        }
Esempio n. 8
0
        /// <summary>
        /// "Count" keyword is used without expression --
        /// Way to use: [Table].Count(expression)
        /// </summary>
        public static int Count <T>(Expression <Func <T, bool> > expression, IDbConnection db)
        {
            var      body  = expression.Body as BinaryExpression;
            SQlQuery query = new SQlQuery(EXTable.GetTableName <T>());

            WalkTree <T>(expression, ref query);

            string newSql = @"SELECT COUNT(1) FROM ( " + query.Query + " ) as Extend";
            var    result = db.Query <int>(newSql, (object)query.Param).FirstOrDefault();

            return(result);
        }
Esempio n. 9
0
        /// <summary>
        /// Delete entity
        /// </summary>
        public static bool Delete <T>(int id, IDbConnection db)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("DELETE FROM ");
            query.AppendLine(EXTable.GetTableName <T>());
            query.AppendLine(" WHERE ");
            string primaryKey = GetPrimaryIdColumn(typeof(T));

            query.AppendLine(primaryKey + " = @" + primaryKey);
            string sqlQuery = query.ToString();

            return(1 == db.Execute(sqlQuery, new { primaryKey = id }));
        }
Esempio n. 10
0
        /// <summary>
        /// result[0] = result[1]
        /// </summary>
        private static string[] GetForeignKeyColumn(Type table1, Type table2)
        {
            string[]       result         = new string[2];
            PropertyInfo[] table1PInfos   = table1.GetProperties();
            string         table1NameInDb = EXTable.GetTableName(table1);
            string         table2NameInDb = EXTable.GetTableName(table2);

            PropertyInfo pForeignKeyInfosTable1 = table1PInfos.FirstOrDefault(p => p.GetCustomAttributes(true)
                                                                              .Any(a => (a as ForeignKeyAttribute) != null && (a as ForeignKeyAttribute).Name == table2NameInDb));

            if (pForeignKeyInfosTable1 != null)
            {
                result[0] = table1NameInDb + '.' + pForeignKeyInfosTable1.Name;
                result[1] = table2NameInDb + '.' + pForeignKeyInfosTable1.Name;
                return(result);
            }
            else
            {
                PropertyInfo[] table2PInfos           = table2.GetProperties();
                PropertyInfo   pForeignKeyInfosTable2 = table2PInfos.FirstOrDefault(p => p.GetCustomAttributes(true)
                                                                                    .Any(a => (a as ForeignKeyAttribute) != null && (a as ForeignKeyAttribute).Name == table1NameInDb));
                if (pForeignKeyInfosTable2 != null)
                {
                    result[0] = table1NameInDb + '.' + pForeignKeyInfosTable1.Name;
                    result[1] = table2NameInDb + '.' + pForeignKeyInfosTable1.Name;
                    return(result);
                }

                //External fields of table 1
                //search id of table 2 in table 1
                PropertyInfo primaryKeyTable2 = table2PInfos.FirstOrDefault(p => string.Compare(p.Name, "id", true) == 0);
                if (primaryKeyTable2 == null)
                {
                    primaryKeyTable2 = table2PInfos.FirstOrDefault(p => p.GetCustomAttributes(true).Any(a => (a as PrimaryKey) != null));
                }
                if (primaryKeyTable2 == null)
                {
                    throw new Exception("Could not found the primary key in entity: " + table1.FullName);
                }

                PropertyInfo allPExternalTable2InTable1 = table1PInfos.FirstOrDefault(p => string.Compare(p.Name, primaryKeyTable2.Name, true) == 0);

                if (allPExternalTable2InTable1 != null) // primary key of table 2 is existed in table 1
                {
                    result[0] = table1NameInDb + '.' + primaryKeyTable2.Name;
                    result[1] = table2NameInDb + '.' + primaryKeyTable2.Name;
                    return(result);
                }
                else
                {
                    //search id of table 1 in table 2
                    PropertyInfo primaryKeyTable1 = table1PInfos.FirstOrDefault(p => string.Compare(p.Name, "id", true) == 0);
                    if (primaryKeyTable1 == null)
                    {
                        primaryKeyTable1 = table1PInfos.FirstOrDefault(p => p.GetCustomAttributes(true).Any(a => (a as PrimaryKey) != null));
                    }
                    if (primaryKeyTable1 == null)
                    {
                        throw new Exception("Could not found the primary key in entity: " + table1.FullName);
                    }

                    PropertyInfo pExternalTable1InTable2 = table2PInfos.FirstOrDefault(p => string.Compare(p.Name, primaryKeyTable1.Name, true) == 0);
                    if (pExternalTable1InTable2 != null)
                    {
                        result[0] = table1NameInDb + '.' + primaryKeyTable1.Name;
                        result[1] = table2NameInDb + '.' + primaryKeyTable1.Name;
                        return(result);
                    }
                    else
                    {
                        throw new Exception("Could not found the foreign key between two entities: " + table1.FullName + " and " + table2.FullName);
                    }
                }
            }
        }
Esempio n. 11
0
        private static void GetJoinOperation(Type rootTableName, string propertyName, ref List <string> joins)
        {
            string[] elements  = propertyName.Split(new char[] { '.' });
            string   tableName = elements[0];

            PropertyInfo[] table2PInfos     = rootTableName.GetProperties();
            PropertyInfo   primaryKeyTable2 = table2PInfos.FirstOrDefault(p => string.Compare(EXTable.GetTableName(p.PropertyType), tableName, true) == 0);

            string[]      keyJoin     = GetForeignKeyColumn(rootTableName, primaryKeyTable2.PropertyType);
            StringBuilder joinComment = new StringBuilder();

            joinComment.Append("JOIN ");
            joinComment.Append(tableName);
            joinComment.Append(" ON " + keyJoin[0] + " = " + keyJoin[1]);
            joins.Add(joinComment.ToString());

            if (elements.Count() > 2)
            {
                string newPropertyName = elements[1] + "." + elements[2];
                Type   destinationType = rootTableName.GetProperty(tableName).PropertyType;

                GetJoinOperation(destinationType, newPropertyName, ref joins);
            }
        }
Esempio n. 12
0
        public static QueryResult <T> Get <T>()
        {
            SQlQuery query = new SQlQuery(EXTable.GetTableName <T>());

            return(new QueryResult <T>(query));
        }