示例#1
0
        /// <summary>
        /// Provides low level functionality to delete entities from a given table
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="hopper"></param>
        /// <param name="tableName"></param>
        /// <param name="whereClause"></param>
        /// <exception cref="HopWhereClauseParseException"></exception>
        public static void Delete <T>(this IHop hopper, string tableName = "", string whereClause = "")
        {
            tableName = string.IsNullOrWhiteSpace(tableName) ? HopBase.GetTypeToTableNameService(typeof(T)) : tableName;
            var cmdText = string.Format("DELETE FROM {0}", tableName);

            if (!string.IsNullOrWhiteSpace(whereClause))
            {
                cmdText += string.Format(" WHERE {0}", whereClause);
            }

            using (var dbCommand = hopper.Connection.CreateCommand())
            {
                dbCommand.CommandText = cmdText;
                try
                {
                    dbCommand.Connection.Open();
                    dbCommand.ExecuteNonQuery();
                }
                catch (SqlException sqlException)
                {
                    throw new HopWhereClauseParseException(dbCommand.CommandText, sqlException);
                }
                finally
                {
                    dbCommand.Connection.Close();
                }
            }
        }
示例#2
0
        /// <summary>
        /// Insert a custom query into the database
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="hopper"></param>
        /// <param name="intoClause">Tablename and optional column names to insert to</param>
        /// <param name="insertClause">Values part of the query</param>
        /// <returns>Last inserted Id</returns>
        /// <exception cref="HopInsertClauseParseException"></exception>
        public static int Insert <T>(this IHop hopper, string intoClause = "", string insertClause = "")
        {
            using (var dbCommand = hopper.Connection.CreateCommand())
            {
                intoClause            = string.IsNullOrWhiteSpace(intoClause) ? HopBase.GetTypeToTableNameService(typeof(T)) : intoClause;
                dbCommand.CommandText = string.Format("INSERT INTO {0} VALUES {1}; SELECT @@IDENTITY AS 'Identity'", intoClause, insertClause);

                var lastId = 0;

                try
                {
                    hopper.Connection.Open();
                    lastId = (int)(decimal)dbCommand.ExecuteScalar();
                }
                catch (SqlException exception)
                {
                    throw new HopInsertClauseParseException(exception)
                          {
                              InsertClause = dbCommand.CommandText
                          };
                }
                finally
                {
                    if (hopper.Connection.State == ConnectionState.Open)
                    {
                        hopper.Connection.Close();
                    }
                }

                return(lastId);
            }
        }
示例#3
0
        public static void AddTypeToCache <T>(IDbConnection connection) where T : new()
        {
            if (VerifiedTypesCache.Contains(typeof(T)))
            {
                return;
            }

            var sqlConnection = connection as SqlConnection;

            if (sqlConnection == null)
            {
                return;
            }

            VerifiedTypesCache.Add(typeof(T));

            var tableName = HopBase.GetTypeToTableNameService(typeof(T));

            var columns =
                connection
                .Hop()
                .ReadTuples <Tuple <string, int>, T>("column_name, ordinal_position", string.Format("table_name = '{0}'", tableName), "information_schema.columns")
                .ToList();

            var unMatchedProperties = TypeCache.Get <T>().Properties.Where(x => columns.All(y => y.Item1 != x.Name)).ToList();

            if (!unMatchedProperties.Any())
            {
                return;
            }

            var sb = new StringBuilder();

            //create table if no column found
            if (!columns.Any())
            {
                var propertyInfo = TypeCache.Get <T>().IdProperty;
                unMatchedProperties = unMatchedProperties.Where(x => x != TypeCache.Get <T>().IdProperty).ToList();
                sb.AppendLine(string.Format("CREATE TABLE {0}({1} {2} PRIMARY KEY {3});", tableName, propertyInfo.Name, GetSqlString(GetSqlType(propertyInfo.PropertyType)), propertyInfo.PropertyType == typeof(int) ? "IDENTITY" : string.Empty));
            }

            foreach (var source in unMatchedProperties)
            {
                sb.Append(string.Format("ALTER TABLE  {0} ", tableName));
                var sqlDbType = GetSqlType(source.PropertyType);
                sb.AppendLine(string.Format(" ADD {0} {1}", source.Name, GetSqlString(sqlDbType)));
            }

            using (var dbCommand = connection.CreateCommand())
            {
                dbCommand.CommandText = sb.ToString();

                connection.Open();
                dbCommand.ExecuteNonQuery();
                connection.Close();
            }
        }
示例#4
0
        public static void Update <T>(this IHop hopper, IEnumerable <T> instances) where T : new()
        {
            SchemaVerifierService.AddTypeToCache <T>(hopper.Connection);

            IIdExtractorService idExtractorService = HopBase.GetIdExtractorService();
            List <T>            instanceList       = instances as List <T> ?? instances.ToList();

            var sb           = new StringBuilder();
            int paramCounter = 0;

            using (var dbCommand = new SqlCommand())
            {
                dbCommand.Connection = (SqlConnection)hopper.Connection;

                foreach (T inst in instanceList)
                {
                    sb.AppendLine(string.Format("UPDATE {0} SET ", HopBase.GetTypeToTableNameService(typeof(T))));

                    sb.AppendLine(
                        TypeCache.Get <T>().PropertiesWithoutId
                        .Select((x, i) =>
                    {
                        string paramName = string.Format("@param{0}{1}", paramCounter, i);
                        object value     = x.GetValue(inst, null);
                        if (value == null)
                        {
                            return(string.Empty);
                        }
                        dbCommand.Parameters.Add(new SqlParameter(paramName, value));
                        return(string.Format("{0} = {1}", x.Name, paramName));
                    })
                        .Where(x => x != string.Empty)
                        .Aggregate((set1, set2) => set1 + ", " + set2));

                    object instanceId = idExtractorService.GetId(inst);

                    if (instanceId == null || HopBase.GetDefault(instanceId.GetType()).Equals(instanceId))
                    {
                        throw new HopUpdateWithoutKeyException(inst);
                    }

                    sb.AppendLine(string.Format(" WHERE {0} = {1}", idExtractorService.GetIdField <T>(), instanceId));
                    paramCounter++;
                }

                dbCommand.CommandText = sb.ToString();
                dbCommand.Connection.Open();
                dbCommand.ExecuteNonQuery();
                dbCommand.Connection.Close();
            }
        }
示例#5
0
        /// <summary>
        /// Deletes a collection of instances in the database
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="hopper"></param>
        /// <param name="instances"></param>
        /// <exception cref="ArgumentNullException"></exception>
        public static void Delete <T>(this IHop hopper, ICollection <T> instances)
        {
            if (instances == null)
            {
                throw new ArgumentNullException("instances", "Please provide a non null value for parameter instances");
            }

            var tableName          = HopBase.GetTypeToTableNameService(typeof(T));
            var idExtractorService = HopBase.GetIdExtractorService();
            var idField            = idExtractorService.GetIdField <T>();
            var inClause           =
                idExtractorService
                .GetIds <T, int>(instances)
                .Select(x => x.ToString(CultureInfo.InvariantCulture))
                .Aggregate((id1, id2) => id1 + ", " + id2);

            Delete <T>(hopper, tableName, string.Format("{0} IN({1})", idField, inClause));
        }
示例#6
0
        /// <summary>
        /// Inserts a collection of entities to the database
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="hopper"></param>
        /// <param name="instances">Collection to insert</param>
        /// <exception cref="ArgumentNullException"></exception>
        public static void Insert <T>(this IHop hopper, ICollection <T> instances) where T : new()
        {
            if (instances == null)
            {
                throw new ArgumentNullException("instances", "Please provide a non null value to parameter instances");
            }

            if (!instances.Any())
            {
                return;
            }

            SchemaVerifierService.AddTypeToCache <T>(hopper.Connection);

            var propertyInfos = TypeCache.Get <T>().PropertiesWithoutId;
            var objects       =
                instances
                .Select(objToInsert =>
                        propertyInfos
                        .Select(prop => prop.GetValue(objToInsert, null).ToSqlString())
                        .Aggregate((field1, field2) => field1 + ", " + field2)
                        );

            var tableName  = HopBase.GetTypeToTableNameService(typeof(T));
            var columnList =
                propertyInfos
                .Select(x => x.Name)
                .Aggregate((field1, field2) => field1 + ", " + field2);

            var intoClause   = string.Format("{0} ({1})", tableName, columnList);
            var valuesClause =
                objects
                .Select(x => "(" + x + ")")
                .Aggregate((obj1, obj2) => obj1 + ", " + obj2);

            var lastId = hopper.Insert <T>(intoClause, valuesClause);

            foreach (T source in instances.Reverse())
            {
                HopBase.GetIdExtractorService().SetId(source, lastId--);
            }
        }
示例#7
0
        public static void Update <T>(this IHop hopper, string where = "", string setClause = "") where T : new()
        {
            SchemaVerifierService.AddTypeToCache <T>(hopper.Connection);

            using (var dbCommand = hopper.Connection.CreateCommand())
            {
                where = string.IsNullOrWhiteSpace(where) ? " 1 = 1" : where;
                dbCommand.CommandText = string.Format("UPDATE {0} SET {1} WHERE {2}", HopBase.GetTypeToTableNameService(typeof(T)), setClause, where);

                dbCommand.Connection.Open();
                dbCommand.ExecuteNonQuery();
                dbCommand.Connection.Close();
            }
        }
示例#8
0
        private static string SelectFrom <T>(string columnsToSelect = "*", string fromTable = null) where T : new()
        {
            var tableName = HopBase.GetTypeToTableNameService(typeof(T));

            return(string.Format("SELECT {1} FROM {0} ", fromTable ?? tableName, columnsToSelect));
        }