public SqlBuilderSettings(SqlQueryType queryType, bool withoutHeader, bool allowNullValue, bool appendDeleteQuery)
{
QueryType = queryType;
AllowNullValue = allowNullValue;
WriteWithoutHeader = withoutHeader;
AppendDeleteQuery = appendDeleteQuery;
}
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;
}
// 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;
}
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);
}
/// <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";
}
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());
}
