Пример #1
0
        public static string GetInsertStatement(System.Type type)
        {
            Dictionary <string, string> columnNames = DatabaseUtilities.getColumns(type);

            columnNames.Remove(DatabaseUtilities.GetKeyColumn(type).Key);
            return(string.Format("INSERT INTO {0}({1}) VALUES({2})", DatabaseUtilities.GetTableName(type), string.Join(",", columnNames.Keys), string.Join(",", columnNames.Values)));
        }
Пример #2
0
        public int Insert(object obj)
        {
            System.Type type      = obj.GetType();
            DbCommand   command   = this.getCommand(type, new Func <System.Type, string>(DatabaseUtilities.GetInsertStatement));
            string      returnSql = command.CommandText;
            string      newId     = "InternalNewId";

            if (command is System.Data.SqlClient.SqlCommand)
            {
                returnSql = "; SET @InternalNewId = SCOPE_IDENTITY()";
                newId     = "@" + newId;
            }
            else
            {
                returnSql = string.Format("RETURNING {0} INTO :InternalNewId", DatabaseUtilities.GetKeyColumn(type).Key);
                newId     = ":" + newId;
            }
            DbCommand expr_7A = command;

            expr_7A.CommandText += returnSql;
            this.AddParametersFrom <object>(command, obj);
            this.AddOutParameter(command, newId, System.Data.DbType.Int32, 10);
            this.ExecuteNonQueryCommand(command);
            return((int)command.Parameters[newId].Value);
        }
Пример #3
0
        public static List <string> PopulateListWithMatchingColumns <T>(this IDataReader reader, Action <T, IDataRecord> customMapper, List <string> columnNames, ColumnNameMapping[] customNameMappings, List <T> listToPopulate)
        {
            Dictionary <PropertyInfo, string> propertyMapping = DatabaseUtilities.getPropertyMapping <T>(columnNames, customNameMappings);

            while (reader.Read())
            {
                T temp = System.Activator.CreateInstance <T>();
                foreach (PropertyInfo property in propertyMapping.Keys)
                {
                    string columnName = propertyMapping[property];
                    if (columnName != null)
                    {
                        property.SetValue(temp, Utilities.ConvertTo(property.PropertyType, reader[columnName]), null);
                    }
                }
                if (customMapper != null)
                {
                    customMapper(temp, reader);
                }
                listToPopulate.Add(temp);
            }
            return((
                       from p in propertyMapping.Values
                       where p != null
                       select p).ToList <string>());
        }
Пример #4
0
        public static void ValidateBindVariables(this System.Data.Common.DbCommand command)
        {
            if (command.CommandType != System.Data.CommandType.Text)
            {
                throw new Exception("Cannot call ValidateBindVariables if not parameterized query");
            }
            List <string> bindVariables = DatabaseUtilities.ExtractBindVariables(command.CommandText);

            System.Data.Common.DbParameterCollection dbParameters = command.Parameters;
            foreach (string variable in bindVariables)
            {
                bool bound = false;
                foreach (System.Data.Common.DbParameter parameter in dbParameters)
                {
                    if (parameter.ParameterName.Equals(variable, System.StringComparison.CurrentCultureIgnoreCase))
                    {
                        bound = true;
                        break;
                    }
                }
                if (!bound)
                {
                    throw new Exception(string.Format("{0} is not bound", variable));
                }
            }
        }
Пример #5
0
 public void AddParametersFrom <T>(DbCommand command, T input)
 {
     if (command.CommandType != System.Data.CommandType.Text)
     {
         throw new Exception("The feature is only supported for inline SQL");
     }
     string[] parametersToAdd = DatabaseUtilities.ExtractBindVariables(command.CommandText).ToArray();
     this.AddParametersFrom <T>(command, input, parametersToAdd);
 }
Пример #6
0
        public static string GetUpdateStatement(System.Type type)
        {
            Dictionary <string, string> columnNames = DatabaseUtilities.getColumns(type);

            columnNames.Remove(DatabaseUtilities.GetKeyColumn(type).Key);
            return(string.Format("UPDATE {0} SET {1}", DatabaseUtilities.GetTableName(type), string.Join(",",
                                                                                                         from x in columnNames
                                                                                                         select string.Format("{0} = {1}", x.Key, x.Value))));
        }
Пример #7
0
        public T Get <T>(int id)
        {
            DbCommand command     = this.getCommand(typeof(T), new Func <System.Type, string>(DatabaseUtilities.GetSelectStatement));
            string    whereClause = string.Format(" WHERE {0} = {1}", DatabaseUtilities.GetKeyColumn(typeof(T)).Key, (command is System.Data.SqlClient.SqlCommand) ? "@id" : ":id");
            DbCommand expr_56     = command;

            expr_56.CommandText += whereClause;
            this.AddInParameter(command, (command is System.Data.SqlClient.SqlCommand) ? "@id" : ":id", System.Data.DbType.Int64, id);
            return(this.ExecuteAndReturn <T>(command).FirstOrDefault <T>());
        }
Пример #8
0
        public int Update(object obj)
        {
            System.Type type = obj.GetType();
            System.Collections.Generic.KeyValuePair <string, string> key = DatabaseUtilities.GetKeyColumn(type);

            DbCommand command     = this.getCommand(type, new Func <System.Type, string>(DatabaseUtilities.GetUpdateStatement));
            string    whereClause = string.Format(" WHERE {0} = {1}", key.Key, (command is System.Data.SqlClient.SqlCommand) ? ("@" + key.Value) : (":" + key.Value));
            DbCommand expr_6B     = command;

            expr_6B.CommandText += whereClause;
            this.AddParametersFrom <object>(command, obj);
            return(this.ExecuteNonQueryCommand(command));
        }
Пример #9
0
        public int Delete <T>(int id)
        {
            System.Type type    = typeof(T);
            DbCommand   command = this.GetSqlStringCommand("DELETE FROM " + DatabaseUtilities.GetTableName(type));

            System.Collections.Generic.KeyValuePair <string, string> key = DatabaseUtilities.GetKeyColumn(type);
            string    prefix      = (command is System.Data.SqlClient.SqlCommand) ? "@" : ":";
            string    whereClause = string.Format(" WHERE {0} = {1}{2}", key.Key, prefix, key.Value);
            DbCommand expr_5C     = command;

            expr_5C.CommandText += whereClause;
            this.AddInParameter(command, prefix + key.Value, System.Data.DbType.Int32, id);
            return(this.ExecuteNonQueryCommand(command));
        }
Пример #10
0
        public static List <T> AutoMap <T>(this IDataReader reader)
        {
            System.Type t = typeof(T);
            List <T>    result;

            if (t.IsPrimitive || t.Equals(typeof(string)) || (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(System.Nullable <>)))
            {
                result = DatabaseUtilities.autoMapSimpleType <T>(reader);
            }
            else
            {
                result = reader.AutoMap <T>(new ColumnNameMapping[0]);
            }
            return(result);
        }
Пример #11
0
        private void addInParameterFrom <T>(DbCommand command, T input, string parameter, System.Reflection.PropertyInfo property)
        {
            object value = DatabaseUtilities.GetDbValue(property.GetValue(input, null), command);

            this.AddInParameter(command, parameter, typeof(System.DateTime?).IsAssignableFrom(property.PropertyType) ? System.Data.DbType.DateTime : System.Data.DbType.AnsiString, value);
        }
Пример #12
0
        private System.Collections.Generic.List <string> addInParametersFrom <T>(DbCommand command, T input, params string[] parametersToAdd)
        {
            if (parametersToAdd == null || parametersToAdd.Length == 0)
            {
                throw new Exception("parametersToAdd is empty");
            }
            System.Collections.Generic.IEnumerable <System.Reflection.PropertyInfo> properties =
                from p in Utilities.GetProperties(input)
                where p.CanRead
                select p;

            System.Collections.Generic.List <string> addedParameters = new System.Collections.Generic.List <string>();
            System.Array.ForEach <string>(parametersToAdd, delegate(string parameter)
            {
                System.Reflection.PropertyInfo property = properties.FirstOrDefault((System.Reflection.PropertyInfo p) => p.Name.Equals(DatabaseUtilities.GetMatchingPropertyName(parameter), System.StringComparison.CurrentCultureIgnoreCase));
                if (property != null)
                {
                    this.addInParameterFrom <T>(command, input, parameter, property);
                    addedParameters.Add(parameter);
                }
            });
            return(addedParameters);
        }
Пример #13
0
        public static string GetSelectStatement(System.Type type)
        {
            Dictionary <string, string> columnNames = DatabaseUtilities.getColumns(type);

            return(string.Format("SELECT {0} FROM {1}", DatabaseUtilities.buildSelectColumns(columnNames), DatabaseUtilities.GetTableName(type)));
        }
Пример #14
0
        public static string GetMatchingPropertyName(string parameter)
        {
            if (string.IsNullOrEmpty(parameter))
            {
                throw new System.ArgumentException("parameter cannot be null or empty");
            }
            string result;

            lock (DatabaseUtilities._matchingProperties.SyncRoot)
            {
                if (DatabaseUtilities._matchingProperties["DBParameter" + parameter] == null)
                {
                    DatabaseUtilities._matchingProperties["DBParameter" + parameter] = DatabaseUtilities.RemoveParameterPrefix(parameter).Pascalize();
                }
                result = (string)DatabaseUtilities._matchingProperties["DBParameter" + parameter];
            }
            return(result);
        }