public SqlBuilderSettings(SqlQueryType queryType, bool withoutHeader, bool allowNullValue, bool appendDeleteQuery) { QueryType = queryType; AllowNullValue = allowNullValue; WriteWithoutHeader = withoutHeader; AppendDeleteQuery = appendDeleteQuery; }
protected override void AddParameter(DbCommand command, QueryParameter parameter, object value) { SqlQueryType sqlType = (SqlQueryType)parameter.QueryType; if (sqlType == null) { sqlType = (SqlQueryType)this.Provider.Language.TypeSystem.GetColumnType(parameter.Type); } int len = sqlType.Length; if (len == 0 && SqlTypeSystem.IsVariableLength(sqlType.SqlType)) { len = Int32.MaxValue; } var p = ((SqlCommand)command).Parameters.Add("@" + parameter.Name, (System.Data.SqlDbType)(int) sqlType.SqlType, len); if (sqlType.Precision != 0) { p.Precision = (byte)sqlType.Precision; } if (sqlType.Scale != 0) { p.Scale = (byte)sqlType.Scale; } p.Value = value ?? DBNull.Value; }
// Constructor public Query(SqlQueryType queryType) { SetStartupValues(); // Set property values this.QueryType = queryType; }
// Constructor public Query(SqlQueryType queryType, string sqlQueryString) { SetStartupValues(); // Set property values this.QueryType = queryType; this.QueryString = sqlQueryString; }
/// <summary> /// Sets the class properties to default values /// </summary> private void SetStartupValues() { mParameterCollection = new Parameters(); mDbType = DatabaseTypes.SQLServer; // Default to SQL Server mQueryType = SqlQueryType.Sql; mQueryString = string.Empty; mQueryTimeout = 60; mTableName = "Table1"; }
protected override OleDbType GetOleDbType(QueryType type) { SqlQueryType sqlType = type as SqlQueryType; if (sqlType != null) { return(ToOleDbType(sqlType.SqlType)); } return(base.GetOleDbType(type)); }
/// <summary> /// Instantiates this class. /// NOTE: The constructor has been made private intentionally. /// </summary> private ParameterizedSqlQuery() { // Initialize the query. this.queryType = SqlQueryType.NotSetYet; // Prepare the lists and dictionaries. this.tableAliasesDictionary = new Dictionary <string, Type> (); this.columnsToSelect = new List <ColumnInfo> (); this.orderByColumns = new List <ColumnInfo> (); this.columnAliasesDictionary = new List <KeyValuePair <ColumnInfo, string> > (); this.fromJoins = new List <JoinInfo> (); this.sqlParameters = new List <SqlQueryParameter> (); }
/// <summary> /// Initial Sql builder /// </summary> /// <param name="tableName">Table name (like creature_template, creature etc.)</param> /// <param name="keyName">Key name (like entry, id, guid etc.)</param> public SqlBuilder(string tableName, string keyName, SqlQueryType type = SqlQueryType.InsertOrUpdate) { _tableName = tableName; _keyName = keyName; WriteWithoutHeader = false; // Settings.Default.WithoutHeader; AllowNullValue = true; // Settings.Default.AllowEmptyValues; QueryType = type; // (SqlQueryType)Settings.Default.QueryType; if (QueryType <= SqlQueryType.None || QueryType >= SqlQueryType.Max) { throw new InvalidQueryTypeException(QueryType); } }
protected override void AddParameter(IDbCommand command, QueryParameter parameter, object value) { SqlQueryType sqlType = (SqlQueryType)parameter.QueryType; if (sqlType == null) { sqlType = (SqlQueryType)this.provider.Language.TypeSystem.GetColumnType(parameter.Type); } var p = ((MySqlCommand)command).Parameters.Add(parameter.Name, ToMySqlDbType(sqlType.SqlType), sqlType.Length); if (sqlType.Precision != 0) { p.Precision = (byte)sqlType.Precision; } if (sqlType.Scale != 0) { p.Scale = (byte)sqlType.Scale; } p.Value = value ?? DBNull.Value; }
internal DbSqlException(string message, SqlException innerException, DbEntity failingEntity, SqlQueryType sqlQueryType) : this(message, innerException) { FailingEntity = failingEntity; SqlQueryType = sqlQueryType; }
internal SqlQueryColumn(SqlColumn main, Dictionary <string, SqlColumn> builderColumns, SqlQueryType type, string query) : base(main.Table, main.Name, main.Alias) { this.builderColumns = builderColumns; Type = type; Query = query; }
public InvalidQueryTypeException(SqlQueryType type) : base(type.ToString()) { }
private string GetSQLQueryByType(SqlQueryType sqlQueryType) { // CHARACTER_MAXIMUM_LENGTH -1 For MAX Length And NULL is for undefined i.e int , float etc. so we have excluded max length and included undefined string _tableColumns = @"SELECT DISTINCT TBLA.COLUMN_ID, TBLB.COLUMN_NAME, TBLB.DATA_TYPE,TBLA.MAX_LENGTH, TBLA.PRECISION, CASE TBLB.NUMERIC_SCALE WHEN 0 THEN 'NO' ELSE 'YES' END AS NUMERIC_SCALE, TBLB.IS_NULLABLE, TBLB.COLUMN_DEFAULT --,ISNULL(CONSTRAINTS.IS_CONSTRAINT,0) AS IS_CONSTRAINT FROM SYS.COLUMNS TBLA INNER JOIN (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName) TBLB ON TBLA.COLUMN_ID=TBLB.ORDINAL_POSITION LEFT JOIN ( SELECT 1 as IS_CONSTRAINT, TABLE_VIEW, CONSTRAINT_TYPE AS [CONSTRAINT], CONSTRAINT_NAME, Column_Name FROM ( SELECT table_view ,object_type ,constraint_type ,constraint_name ,details ,Column_Name FROM ( SELECT '[' + schema_name(t.schema_id) + '].[' + t.[name] + ']' AS table_view ,CASE WHEN t.[type] = 'U' THEN 'Table' WHEN t.[type] = 'V' THEN 'View' END AS [object_type] ,CASE WHEN c.[type] = 'PK' THEN 'Primary key' WHEN c.[type] = 'UQ' THEN 'Unique constraint' WHEN i.[type] = 1 THEN 'Unique clustered index' WHEN i.type = 2 THEN 'Unique index' END AS constraint_type ,isnull(c.[name], i.[name]) AS constraint_name ,substring(column_names, 1, len(column_names) - 1) AS [details] ,substring(column_names, 1, len(column_names)-1) AS Column_Name FROM sys.objects t LEFT OUTER JOIN sys.indexes i ON t.object_id = i.object_id LEFT OUTER JOIN sys.key_constraints c ON i.object_id = c.parent_object_id AND i.index_id = c.unique_index_id CROSS APPLY ( SELECT col.[name] + ', ' FROM sys.index_columns ic INNER JOIN sys.columns col ON ic.object_id = col.object_id AND ic.column_id = col.column_id WHERE ic.object_id = t.object_id AND ic.index_id = i.index_id ORDER BY col.column_id FOR XML path('') ) D(column_names) WHERE is_unique = 1 AND t.is_ms_shipped <> 1 UNION ALL SELECT '[' + OBJECT_SCHEMA_NAME(f.parent_object_id) + '].[' + OBJECT_NAME(f.parent_object_id) + ']' AS foreign_table ,'Table' ,'Foreign key' ,f.name AS fk_constraint_name ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS PKRefdBaseColumn ,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS Column_Name FROM sys.foreign_keys AS f JOIN sys.foreign_key_columns AS fc ON (f.object_id = fc.constraint_object_id) UNION ALL SELECT '[' + schema_name(t.schema_id) + '].[' + t.[name] + ']' ,'Table' ,'Check constraint' ,con.[name] AS constraint_name ,con.[definition] ,COL_NAME(col.object_id, col.column_id) AS Column_Name FROM sys.check_constraints con LEFT OUTER JOIN sys.objects t ON con.parent_object_id = t.object_id LEFT OUTER JOIN sys.all_columns col ON con.parent_column_id = col.column_id AND con.parent_object_id = col.object_id UNION ALL SELECT '[' + schema_name(t.schema_id) + '].[' + t.[name] + ']' ,'Table' ,'Default constraint' ,con.[name] ,col.[name] + ' = ' + con.[definition] ,COL_NAME(col.object_id, col.column_id) AS Column_Name FROM sys.default_constraints con LEFT OUTER JOIN sys.objects t ON con.parent_object_id = t.object_id LEFT OUTER JOIN sys.all_columns col ON con.parent_column_id = col.column_id AND con.parent_object_id = col.object_id ) a ) tblA ) CONSTRAINTS ON COL_NAME(TBLA.object_id, TBLA.column_id) = CONSTRAINTS.COLUMN_NAME AND TBLA.OBJECT_ID = OBJECT_ID(CONSTRAINTS.table_view) WHERE TBLA.OBJECT_ID = OBJECT_ID(@TableNameWithSchema) AND TBLA.IS_IDENTITY = 0 AND TBLA.IS_COMPUTED = 0 AND ISNULL(CONSTRAINTS.IS_CONSTRAINT,0) = 0 AND ((CHARACTER_MAXIMUM_LENGTH BETWEEN 0 AND 500) OR CHARACTER_MAXIMUM_LENGTH IS NULL) AND RTRIM(LTRIM((LOWER(DATA_TYPE)))) NOT IN( " + sqlDbTypesNotAllowed + ")"; string _tableConstrains = @"SELECT TABLE_VIEW, OBJECT_TYPE, CONSTRAINT_TYPE AS [CONSTRAINT], DETAILS AS [COLUMNS], CONSTRAINT_NAME, Column_Name FROM (SELECT table_view ,object_type ,constraint_type ,constraint_name ,details ,Column_Name FROM ( SELECT '[' + schema_name(t.schema_id) + '].[' + t.[name] + ']' AS table_view ,CASE WHEN t.[type] = 'U' THEN 'Table' WHEN t.[type] = 'V' THEN 'View' END AS [object_type] ,CASE WHEN c.[type] = 'PK' THEN 'Primary key' WHEN c.[type] = 'UQ' THEN 'Unique constraint' WHEN i.[type] = 1 THEN 'Unique clustered index' WHEN i.type = 2 THEN 'Unique index' END AS constraint_type ,isnull(c.[name], i.[name]) AS constraint_name ,substring(column_names, 1, len(column_names) - 1) AS [details] ,substring(column_names, 1, len(column_names)-1) AS Column_Name FROM sys.objects t LEFT OUTER JOIN sys.indexes i ON t.object_id = i.object_id LEFT OUTER JOIN sys.key_constraints c ON i.object_id = c.parent_object_id AND i.index_id = c.unique_index_id CROSS APPLY ( SELECT col.[name] + ', ' FROM sys.index_columns ic INNER JOIN sys.columns col ON ic.object_id = col.object_id AND ic.column_id = col.column_id WHERE ic.object_id = t.object_id AND ic.index_id = i.index_id ORDER BY col.column_id FOR XML path('') ) D(column_names) WHERE is_unique = 1 AND t.is_ms_shipped <> 1 UNION ALL SELECT '[' + OBJECT_SCHEMA_NAME(f.parent_object_id) + '].[' + OBJECT_NAME(f.parent_object_id) + ']' AS foreign_table ,'Table' ,'Foreign key' ,f.name AS fk_constraint_name ,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS PKRefdBaseColumn ,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS Column_Name FROM sys.foreign_keys AS f JOIN sys.foreign_key_columns AS fc ON (f.object_id = fc.constraint_object_id) UNION ALL SELECT '[' + schema_name(t.schema_id) + '].[' + t.[name] + ']' ,'Table' ,'Check constraint' ,con.[name] AS constraint_name ,con.[definition] ,COL_NAME(col.object_id, col.column_id) AS Column_Name FROM sys.check_constraints con LEFT OUTER JOIN sys.objects t ON con.parent_object_id = t.object_id LEFT OUTER JOIN sys.all_columns col ON con.parent_column_id = col.column_id AND con.parent_object_id = col.object_id UNION ALL SELECT '[' + schema_name(t.schema_id) + '].[' + t.[name] + ']' ,'Table' ,'Default constraint' ,con.[name] ,col.[name] + ' = ' + con.[definition] ,COL_NAME(col.object_id, col.column_id) AS Column_Name FROM sys.default_constraints con LEFT OUTER JOIN sys.objects t ON con.parent_object_id = t.object_id LEFT OUTER JOIN sys.all_columns col ON con.parent_column_id = col.column_id AND con.parent_object_id = col.object_id ) a ) tblA WHERE OBJECT_ID(tblA.table_view) = OBJECT_ID(@TableNameWithSchema) ORDER BY table_view ,constraint_type ,constraint_name" ; string _tableReference = @"SELECT (OBJECT_SCHEMA_NAME(f.parent_object_id) + '.'+ OBJECT_NAME(f.parent_object_id)) as [TABLE] , COL_NAME(fc.parent_object_id, fc.parent_column_id) as [COLUMN] FROM sys.foreign_keys AS f INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id INNER JOIN sys.tables t ON t.OBJECT_ID = fc.referenced_object_id WHERE OBJECT_NAME(f.referenced_object_id) = @TableName"; string _tableDependentUpon = @"SELECT BASE_COLUMN, REF_TABLE, REF_COLUMN FROM ( SELECT BASE_TABLE = FK.TABLE_NAME ,BASE_COLUMN = CU.COLUMN_NAME ,REF_TABLE = PK.TABLE_SCHEMA + '.' + PK.TABLE_NAME ,REF_COLUMN = PT.COLUMN_NAME ,CONSTRAINT_NAME = C.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME INNER JOIN ( SELECT i1.TABLE_NAME ,i2.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' ) PT ON PT.TABLE_NAME = PK.TABLE_NAME WHERE FK.TABLE_NAME LIKE '%' + @TableName ) TBL ORDER BY 1 ASC" ; string _disableConstrainst = "ALTER TABLE {0} NOCHECK CONSTRAINT ALL;"; string _enableConstraint = "ALTER TABLE {0} CHECK CONSTRAINT ALL;"; string _fnGetMaskedValue = @"IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnGetMaskedValue]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')) BEGIN execute dbo.sp_executesql @statement = N' CREATE FUNCTION [dbo].[fnGetMaskedValue] (@InputText VARCHAR(max), @DataType VARCHAR(100), @NumericMaxLength BIGINT) RETURNS VARCHAR(max) AS BEGIN DECLARE @Shit_Pos AS TINYINT = 3 DECLARE @OutputText AS VARCHAR(max) = '''' DECLARE @pc AS VARCHAR(1) DECLARE @i AS SMALLINT = 1 DECLARE @n AS SMALLINT DECLARE @chekVal AS VARCHAR(400)='''' SET @n = len(@InputText) SET @InputText = upper(@InputText) SET @chekVal= upper(@InputText) IF(@DataType = ''TINYINT'') BEGIN SET @OutputText = CAST ( (CAST( @InputText as tinyint ) + 1) AS VARCHAR) --------- validate to Overflow datatype size --------- IF (@OutputText > @NumericMaxLength AND @NumericMaxLength > 0) BEGIN SET @OutputText = CAST (CAST(@i AS int) AS VARCHAR) END END ELSE IF(@DataType = ''SMALLINT'') BEGIN SET @OutputText = CAST ( (CAST( @InputText as int ) + 1) AS VARCHAR) --------- validate to Overflow datatype size --------- IF (@OutputText > @NumericMaxLength AND @NumericMaxLength > 0) BEGIN SET @OutputText = CAST (CAST(@i AS int) AS VARCHAR) END END ELSE BEGIN WHILE @i < = @n BEGIN SET @pc = SUBSTRING(@InputText, @i, 1) IF ascii(@pc) BETWEEN 65 AND 90 BEGIN IF ascii(@pc) + @Shit_Pos > 90 SET @pc = CHAR((ascii(@pc) + @Shit_Pos) - 90 + 64) ELSE SET @pc = CHAR((ascii(@pc) + @Shit_Pos)) END ELSE BEGIN IF ascii(@pc) BETWEEN 48 AND 57 BEGIN IF ascii(@pc) + @Shit_Pos > 57 SET @pc = CHAR((ascii(@pc) + @Shit_Pos) - 57 + 47) ELSE SET @pc = CHAR((ascii(@pc) + @Shit_Pos)) END END SET @OutputText = @OutputText + @pc SET @i = @i + 1 END END IF (@DataType<>''VARCHAR'' AND @DataType<>''CHAR'' AND @DataType<>''NVARCHAR'' AND @DataType<>''NCHAR'') BEGIN IF ((SELECT ISNUMERIC(REPLACE(REPLACE(@OutputText,''E'',''X''),''D'',''X''))) = 1) BEGIN IF (CAST(CASE WHEN Replace(Replace(ISNULL(@OutputText, ''''), ''.'', ''''), '','', '''') = '''' THEN ''0'' ELSE Replace(Replace(ISNULL(@OutputText, ''''), ''.'', ''''), '','', '''') END AS DECIMAL(38, 0)) = 0) BEGIN SET @OutputText = CAST(( CAST(CASE WHEN Replace(Replace(ISNULL(@chekVal, ''''), ''.'', ''''), '','', '''') = '''' THEN ''0'' ELSE Replace(Replace(ISNULL(@chekVal, ''''), ''.'', ''''), '','', '''') END AS DECIMAL(38, 0)) + 1 ) AS VARCHAR) END END END RETURN @OutputText END; ' END"; string _return = string.Empty; switch (sqlQueryType) { case SqlQueryType.GetColumns: _return = _tableColumns; break; case SqlQueryType.GetConstraints: _return = _tableConstrains; break; case SqlQueryType.GetTableReference: _return = _tableReference; break; case SqlQueryType.GetTableDependentUpon: _return = _tableDependentUpon; break; case SqlQueryType.EnableConstraints: _return = _enableConstraint; break; case SqlQueryType.DisableConstraints: _return = _disableConstrainst; break; case SqlQueryType.FnGetMaskedValue: _return = _fnGetMaskedValue; break; default: _return = string.Empty; break; } return(_return); }
public override string Format(QueryType type, bool suppressSize) { StringBuilder sb = new StringBuilder(); SqlQueryType sqlType = (SqlQueryType)type; SqlType sqlDbType = sqlType.SqlType; switch (sqlDbType) { case SqlType.BigInt: case SqlType.SmallInt: case SqlType.Int: case SqlType.TinyInt: sb.Append("INTEGER"); break; case SqlType.Bit: sb.Append("BOOLEAN"); break; case SqlType.SmallDateTime: sb.Append("DATETIME"); break; case SqlType.Char: case SqlType.NChar: sb.Append("CHAR"); if (type.Length > 0 && !suppressSize) { sb.Append("("); sb.Append(type.Length); sb.Append(")"); } break; case SqlType.Variant: case SqlType.Binary: case SqlType.Image: case SqlType.UniqueIdentifier: //There is a setting to make it string, look at later sb.Append("BLOB"); if (type.Length > 0 && !suppressSize) { sb.Append("("); sb.Append(type.Length); sb.Append(")"); } break; case SqlType.Xml: case SqlType.NText: case SqlType.NVarChar: case SqlType.Text: case SqlType.VarBinary: case SqlType.VarChar: sb.Append("TEXT"); if (type.Length > 0 && !suppressSize) { sb.Append("("); sb.Append(type.Length); sb.Append(")"); } break; case SqlType.Decimal: case SqlType.Money: case SqlType.SmallMoney: sb.Append("NUMERIC"); if (type.Precision != 0) { sb.Append("("); sb.Append(type.Precision); sb.Append(")"); } break; case SqlType.Float: case SqlType.Real: sb.Append("FLOAT"); if (type.Precision != 0) { sb.Append("("); sb.Append(type.Precision); sb.Append(")"); } break; case SqlType.Date: case SqlType.DateTime: case SqlType.Timestamp: default: sb.Append(sqlDbType); break; } return(sb.ToString()); }