Skip to content

Instantly share code, notes, and snippets.

@mota57

mota57/Helper.cs

Last active September 25, 2019 12:58
Show Gist options
  • Save mota57/553b3781e9116c5f0e3fee9cfeda4185 to your computer and use it in GitHub Desktop.
Save mota57/553b3781e9116c5f0e3fee9cfeda4185 to your computer and use it in GitHub Desktop.
Download ZIP
SqlkataExtensions
Raw
Helper.cs
public static class ConvertHelper
{
//https://stackoverflow.com/questions/425389/c-sharp-equivalent-of-sql-server-datatypes
private static readonly Dictionary<string, string> DotNetMappingSqlServer = new Dictionary<string, string> {
{ "Byte[]", "VARBINARY" } ,
{ "Byte", "BINARY" } ,
{ "Boolean", "BIT" } ,
{ "Char", "nvarchar(1)" } ,
{ "DateTime", "DATETIME" } ,
{ "Decimal", "DECIMAL" } ,
{ "Double", "FLOAT" } ,
{"Int16", "smallint"},
{"Int32", "Int"},
{"Int64", "bigint"},
{ "Single", "REAL" } ,
{ "object", "SQL_VARIANT" } ,
{ "Guid", "UNIQUEIDENTIFIER" } ,
{ "String", "NVARCHAR(MAX)" } ,
};
/// Mapping of .NET Framework Data Types to Oracle Native Data Types https://docs.microsoft.com/en-us/dotnet/api/system.data.oracleclient.oracletype?redirectedfrom=MSDN&view=netframework-4.8 and https://docs.oracle.com/html/B10961_01/features.htm#1024984 and https://docs.oracle.com/cd/B19306_01/win.102/b14306/appendixa.htm
private static readonly Dictionary<string, string> DotNetMappingOracle = new Dictionary<string, string> {
{"Byte","Byte"},
{"Byte[]", "Raw"},
{"Char", "Varchar2"},
{"Char[]", "Varchar2"},
{"DateTime", "TimeStamp"},
{"Decimal", "Decimal"},
{"Double", "Double"},
{"Float", "Single"},
{"Int16", "Int16"},
{"Int32", "Int32"},
{"Int64", "Int64"},
{"Single", "Single"},
{"String", "Varchar2" },
{"TimeSpan", "IntervalDS"},
};
//https://docs.telerik.com/data-access/developers-guide/database-specifics/sqlite/database-specifics-sqlite-type-mapping
private static readonly Dictionary<string, string> DotNetMappingSqlite = new Dictionary<string, string>() {
{"Boolean" ,"BIT"},
{"Char" ,"CHAR(1)"},
{"SByte" ,"SMALLINT"},
{"Byte" ,"SMALLINT"},
{"Int16" ,"SMALLINT"},
{"UInt16" ,"INTEGER"},
{"Int32" ,"INTEGER"},
{"UInt32" ,"BIGINT"},
{"Int64" ,"BIGINT"},
{"UInt64" ,"UNSIGNED BIG INT"},
{"Single" ,"REAL"},
{"Double" ,"DOUBLE"},
{"String" ,"VARCHAR(255)"},
{"DateTime" ,"TIMESTAMP"},
{"Decimal" ,"NUMERIC(20,10)"},
{"Guid" ,"GUID"},
{"Byte[]" ,"BLOB"},
};
//https://docs.telerik.com/data-access/developers-guide/database-specifics/firebird/database-specifics-firebird-type-mapping
private static readonly Dictionary<string, string> DotNetMappingFireBird = new Dictionary<string, string>{
{"Boolean", "SMALLINT"},
{"Char", "CHAR(1)"},
{"SByte", "SMALLINT"},
{"Byte", "SMALLINT"},
{"Int16", "SMALLINT"},
{"UInt16", "INTEGER"},
{"Int32", "INTEGER"},
{"UInt32", "BIGINT"},
{"Int64", "BIGINT"},
{"UInt64", "BIGINT"},
{"Single", "FLOAT"},
{"Double", "DOUBLE PRECISION"},
{"String", "VARCHAR(190)"},
{"DateTime", "TIMESTAMP"},
{"Decimal", "NUMERIC(18,8)"},
{"Guid", "CHAR(16)"},
{"Byte[]", "BLOB"},
};
//https://docs.telerik.com/data-access/developers-guide/database-specifics/postgresql/data-access-tasks-postgresql-type-mapping
private static readonly Dictionary<string, string> DotNetMappingPostGreSQL = new Dictionary<string, string>
{
{"Boolean", "BOOL"},
{"Char", "BPCHAR(1)"},
{"SByte", "INT2"},
{"Byte", "INT2"},
{"Int16", "INT2"},
{"UInt16", "INT4"},
{"Int32", "INT4"},
{"UInt32", "INT8"},
{"Int64", "INT8"},
{"UInt64", "NUMERIC(20)"},
{"Single", "FLOAT4"},
{"Double", "FLOAT8"},
{"String", "VARCHAR(255)"},
{"DateTime", "TIMESTAMP"},
{"Decimal", "NUMERIC(20,10)"},
{"Guid", "UUID"},
{"Byte[]", "BYTEA"},
};
/// <summary>
/// Returns the
/// </summary>
/// <param name="engineCode"></param>
/// <param name=""></param>
/// <returns></returns>
public static string ConverToTypeSqlDataType(Type valueType, string engineCode)
{
var valueTypeName = valueType.Name;
if (EngineCodes.SqlServer == engineCode)
{
if (DotNetMappingSqlServer.TryGetValue(valueTypeName, out string value))
{
return value;
}
}
else if (EngineCodes.Sqlite == engineCode)
{
if (DotNetMappingSqlite.TryGetValue(valueTypeName, out string value))
{
return value;
}
}
else if (EngineCodes.Oracle == engineCode)
{
if(DotNetMappingOracle.TryGetValue(valueTypeName, out string value))
{
return value;
}
}
else if(EngineCodes.Firebird == engineCode)
{
if(DotNetMappingFireBird.TryGetValue(valueTypeName, out string value))
{
return value;
}
}
else if (EngineCodes.PostgreSql == engineCode)
{
if (DotNetMappingPostGreSQL.TryGetValue(valueTypeName, out string value))
{
return value;
}
}
throw new NotSupportedException($"DataType {valueTypeName} not supported for the engine {engineCode}");
}
}
Raw
SqliteCompiler
public static string CompileWithVariableSqlLite(WithVarClause withVar)
{
return $"(SELECT Value FROM Variables where Name = '{withVar.Name}')";
}
public static string CompileSqlLiteDeclaration(SqlResult ctx, List<string> declarations)
{
StringBuilder builder = new StringBuilder();
var body = string.Join(", ", declarations);
builder.Append($"CREATE TEMP TABLE Variables(Name TEXT PRIMARY KEY, Value TEXT);");
foreach (var withVar in ctx.Query.GetComponents<WithVarClause>("withVar", EngineCodes.Sqlite))
{
builder.Append($"INSERT OR REPLACE INTO Variables (Name, Value) VALUES('{withVar.Name}', '{withVar.Value}');");
}
return builder.ToString();
}
Raw
SqlkataExtensions
protected virtual SqlResult CompileSelectQuery(Query query)
{
var ctx = new SqlResult
{
Query = query.Clone(),
};
var results = new[] {
this.CompileDeclarations(ctx),
this.CompileColumns(ctx),
this.CompileFrom(ctx),
this.CompileJoins(ctx),
this.CompileWheres(ctx),
this.CompileGroups(ctx),
this.CompileHaving(ctx),
this.CompileOrders(ctx),
this.CompileLimit(ctx),
this.CompileUnion(ctx),
}
.Where(x => x != null)
.Where(x => !string.IsNullOrEmpty(x))
.ToList();
string sql = string.Join(" ", results);
ctx.RawSql = sql;
return ctx;
}
/// <summary>
/// </summary>
/// <param name="ctx"></param>
/// <returns></returns>
public virtual string CompileDeclarations(SqlResult ctx)
{
var declarations = ctx.Query
.GetComponents<WithVarClause>("withVar", EngineCode)
.Select(x => CompileDeclaration(ctx, x))
.ToList();
if(declarations.Count() > 0)
{
if (EngineCodes.SqlServer == EngineCode)
{
var bodyDeclaractionsSqlServer = string.Join(", ", declarations);
return $"DECLARE {bodyDeclaractionsSqlServer} ;";
} else if(EngineCodes.Sqlite == EngineCode)
{
SqliteCompiler.CompileSqlLiteDeclaration(ctx, declarations);
} else if(EngineCodes.MySql == EngineCode)
{
var bodyDeclarationMySql = string.Join(", ", declarations);
return $"SET {bodyDeclarationMySql} ;";
} else if(EngineCodes.Oracle == EngineCode)
{
var bodyOracleDeclarations = string.Join("; ", declarations);
return $"DECLARE {bodyOracleDeclarations} ";
} else if (EngineCodes.Firebird == EngineCode)
{
}
}
return string.Empty;
}
/// <summary>
/// execute when CompileSelectQuery is called
/// </summary>
/// <param name="ctx"></param>
/// <param name="clause"></param>
/// <returns></returns>
public virtual string CompileDeclaration(SqlResult ctx, WithVarClause clause)
{
if(EngineCodes.Sqlite == EngineCode)
{
return $"{clause.Name} {ConvertHelper.ConverToTypeSqlDataType(clause.Value.GetType(), EngineCode)}";
}
if(EngineCodes.MySql == EngineCode)
{
return $"{clause.Name} = ?";
} else
{
ctx.Bindings.Add(clause.Name);
return $"{clause.Name} {ConvertHelper.ConverToTypeSqlDataType(clause.Value.GetType(), EngineCode)} = ?";
}
}
private string CompileParameterValueForPrepareResult(SqlResult ctx, int i, Query query)
{
var parameterPrefix = parameterPlaceholderPrefix + i;
var value = ctx.NamedBindings[parameterPrefix].ToString();
if (query != null && query.HasComponent("withVar") && EngineCodes.Sqlite == ctx.EngineCode)
{
var withVar = query.GetComponents<WithVarClause>("withVar", EngineCode)
.FirstOrDefault(_ => _.Name == value);
if(withVar != null)
{
return SqliteCompiler.CompileWithVariableSqlLite(withVar);
}
}
return parameterPrefix;
}
Raw
WithVarClause.cs
public class WithVarClause : AbstractClause
{
/// <summary>
/// Hold the name of a sql variable
/// </summary>
public string Name { get; set; }
/// <summary>
/// Hold the value of a sql variable
/// </summary>
public object Value { get; set; }
public override AbstractClause Clone()
{
return new WithVarClause()
{
Component = Component,
Engine = Engine,
Name = Name,
Value = Value
};
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment