Esempio n. 1
0
        public static List <object> ReloadSqlParameters(DbContext context, List <object> sqlParameters)
        {
            DbServer databaseType = GetDatabaseType(context);

            if (databaseType == DbServer.Sqlite)
            {
                var sqlParametersReloaded = new List <object>();
                foreach (var parameter in sqlParameters)
                {
                    var sqlParameter = (IDbDataParameter)parameter;
                    sqlParametersReloaded.Add(new SqliteParameter(sqlParameter.ParameterName, sqlParameter.Value));
                }
                return(sqlParametersReloaded);
            }
            else if (databaseType == DbServer.SqlServer)
            {
                // if SqlServer, might need to convert
                // Microsoft.Data.SqlClient to System.Data.SqlClient
                var sqlParametersReloaded = new List <object>();
                var c = context.Database.GetDbConnection();
                foreach (var parameter in sqlParameters)
                {
                    var sqlParameter = (IDbDataParameter)parameter;
                    sqlParametersReloaded.Add(SqlClientHelper.CorrectParameterType(c, sqlParameter));
                }
                return(sqlParametersReloaded);
            }
            else // for SqlServer return original
            {
                return(sqlParameters);
            }
        }
        protected void CheckAndSetParametersForConvertibles(DbContext context, IEnumerable <object> innerParameters, TableInfo tableInfo) // fix for enum 'int' Conversion to nvarchar
        {
            foreach (var innerParameter in innerParameters)
            {
                string parameterColumnName = ((Microsoft.Data.SqlClient.SqlParameter)innerParameter).ParameterName.Replace("@__", "");                              // @__column_N..
                parameterColumnName = parameterColumnName.Contains("_") ? parameterColumnName.Substring(0, parameterColumnName.IndexOf("_")) : parameterColumnName; // column
                parameterColumnName = parameterColumnName.ToLower();

                foreach (var convertibleProperty in tableInfo.ConvertibleProperties)
                {
                    if (convertibleProperty.Key.ToLower() == parameterColumnName)
                    {
                        if (convertibleProperty.Value.ProviderClrType.Name == nameof(String))
                        {
                            if (SqlClientHelper.IsSystemConnection(context.Database.GetDbConnection()))
                            {
                                ((System.Data.SqlClient.SqlParameter)innerParameter).DbType = System.Data.DbType.String;
                            }
                            else
                            {
                                ((Microsoft.Data.SqlClient.SqlParameter)innerParameter).DbType = System.Data.DbType.String;
                            }
                        }
                    }
                }
            }
        }
        protected int GetNumberUpdated(DbContext context)
        {
            var resultParameter = SqlClientHelper.CreateParameter(context.Database.GetDbConnection());

            resultParameter.ParameterName = "@result";
            resultParameter.DbType        = DbType.Int32;
            resultParameter.Direction     = ParameterDirection.Output;
            string sqlQueryCount = SqlQueryBuilder.SelectCountIsUpdateFromOutputTable(this);

            context.Database.ExecuteSqlRaw($"SET @result = ({sqlQueryCount});", resultParameter);
            return((int)resultParameter.Value);
        }
        protected async Task <int> GetNumberUpdatedAsync(DbContext context, CancellationToken cancellationToken)
        {
            var resultParameters = new List <IDbDataParameter>();
            var p = SqlClientHelper.CreateParameter(context.Database.GetDbConnection());

            p.ParameterName = "@result";
            p.DbType        = DbType.Int32;
            p.Direction     = ParameterDirection.Output;
            resultParameters.Add(p);
            string sqlQueryCount = SqlQueryBuilder.SelectCountIsUpdateFromOutputTable(this);
            await context.Database.ExecuteSqlRawAsync($"SET @result = ({sqlQueryCount});", resultParameters, cancellationToken).ConfigureAwait(false); // TODO cancellationToken if Not

            return((int)resultParameters.FirstOrDefault().Value);
        }
Esempio n. 5
0
        private static string GetSqlSetSegment(DbContext context, TableInfo tableInfo, Type updateValuesType, object updateValues, object defaultValues, List <string> updateColumns, List <object> parameters)
        {
            string sql = string.Empty;

            foreach (var propertyNameColumnName in tableInfo.PropertyColumnNamesDict)
            {
                string       propertyName = propertyNameColumnName.Key;
                string       columnName   = propertyNameColumnName.Value;
                var          pArray       = propertyName.Split(new char[] { '.' });
                Type         lastType     = updateValuesType;
                PropertyInfo property     = lastType.GetProperty(pArray[0]);
                if (property != null)
                {
                    object propertyUpdateValue  = property.GetValue(updateValues);
                    object propertyDefaultValue = property.GetValue(defaultValues);
                    for (int i = 1; i < pArray.Length; i++)
                    {
                        lastType            = property.PropertyType;
                        property            = lastType.GetProperty(pArray[i]);
                        propertyUpdateValue = propertyUpdateValue != null?property.GetValue(propertyUpdateValue) : propertyUpdateValue;

                        var lastDefaultValues = lastType.Assembly.CreateInstance(lastType.FullName);
                        propertyDefaultValue = property.GetValue(lastDefaultValues);
                    }

                    if (tableInfo.ConvertibleProperties.ContainsKey(columnName))
                    {
                        propertyUpdateValue = tableInfo.ConvertibleProperties[columnName].ConvertToProvider.Invoke(propertyUpdateValue);
                    }

                    bool isDifferentFromDefault = propertyUpdateValue != null && propertyUpdateValue?.ToString() != propertyDefaultValue?.ToString();
                    if (isDifferentFromDefault || (updateColumns != null && updateColumns.Contains(propertyName)))
                    {
                        sql += $"[{columnName}] = @{columnName}, ";
                        propertyUpdateValue = propertyUpdateValue ?? DBNull.Value;
                        var p = SqlClientHelper.CreateParameter(context.Database.GetDbConnection());
                        p.ParameterName = $"@{columnName}";
                        p.Value         = propertyUpdateValue;
                        parameters.Add(p);
                    }
                }
            }
            if (String.IsNullOrEmpty(sql))
            {
                throw new InvalidOperationException("SET Columns not defined. If one or more columns should be updated to theirs default value use 'updateColumns' argument.");
            }
            sql = sql.Remove(sql.Length - 2, 2); // removes last excess comma and space: ", "
            return($"SET {sql}");
        }