public static DateTime GetDateTime(DbProviderFactory provider)
{
DateTime now = DateTime.Now;
ZDBMS db = GetDBMS(provider);
switch (db)
{
case ZDBMS.Firebird:
return(now);
case ZDBMS.MySQL:
return(new DateTime(now.Year, now.Month, now.Day, now.Hour, now.Minute, now.Second));
case ZDBMS.Oracle:
return(now);
case ZDBMS.PostgreSQL:
return(now);
case ZDBMS.SQLite:
return(now);
case ZDBMS.SQLServer:
return(now);
default:
return(now);
}
}
/// <summary>
/// Get SQL limited by N records
/// </summary>
/// <param name="command"></param>
/// <param name="provider"></param>
/// <param name="records"></param>
public static void SqlRecords(DbCommand command, DbProviderFactory provider, int?records)
{
if (records == null || records <= 0)
{
records = RecordsBySearch;
}
if (records != Int32.MaxValue)
{
ZDBMS db = GetDBMS(provider);
switch (db)
{
case ZDBMS.Firebird:
if (!command.CommandText.Contains(" FIRST "))
{
command.CommandText = command.CommandText.StringReplaceFirst("SELECT ", "SELECT FIRST " + records.ToString() + " ");
}
break;
case ZDBMS.MySQL:
if (!command.CommandText.Contains(" LIMIT "))
{
command.CommandText = command.CommandText + " LIMIT " + records.ToString();
}
break;
case ZDBMS.Oracle:
if (!command.CommandText.Contains(" ROWNUM <"))
{
command.CommandText = "SELECT * FROM (" + command.CommandText + ") WHERE ROWNUM <= " + records.ToString();
}
break;
case ZDBMS.PostgreSQL:
if (!command.CommandText.Contains(" LIMIT "))
{
command.CommandText = command.CommandText + " LIMIT " + records.ToString();
}
break;
case ZDBMS.SQLite:
if (!command.CommandText.Contains(" LIMIT "))
{
command.CommandText = "SELECT * FROM (" + command.CommandText + ") ORDER BY ROWID ASC LIMIT " + records.ToString();
}
break;
case ZDBMS.SQLServer:
if (!command.CommandText.Contains(" TOP "))
{
command.CommandText = command.CommandText.StringReplaceFirst("SELECT ", "SELECT TOP " + records.ToString() + " ");
}
break;
}
}
}
public void SetIsolationLevel(IsolationLevel isolationLevel)
{
ZDBMS dbms = AdoNetHelper.GetDBMS(Session.Connection.ConnectionString);
string sql = AdoNetHelper.SqlIsolationLevel(dbms, isolationLevel);
if (!String.IsNullOrEmpty(sql))
{
SQLCommand(sql);
}
}
/// <summary>
/// Execute Reader.
/// </summary>
/// <param name="dbCommand">Command</param>
/// <param name="isolationLevel">Isolation level</param>
/// <returns>Data Reader</returns>
public static DbDataReader ExecuteReader(this DbCommand dbCommand,
IsolationLevel isolationLevel)
{
if (ConfigurationHelper.AppSettings <bool>("EasyLOB.AdoNet.IsolationLevel"))
{
ZDBMS dbms = AdoNetHelper.GetDBMS(dbCommand.Connection);
string sql = AdoNetHelper.SqlIsolationLevel(dbms, isolationLevel);
dbCommand.CommandText = (String.IsNullOrEmpty(sql) ? "" : sql + Environment.NewLine)
+ dbCommand.CommandText;
}
return(dbCommand.ExecuteReader());
}
/// <summary>
/// Get Id generated by DBMS SQL
/// </summary>
/// <param name="dbms"></param>
/// <returns></returns>
public static string GetIdSql(ZDBMS dbms)
{
switch (dbms)
{
case ZDBMS.MySQL:
return(";SELECT LAST_INSERT_ID();");
case ZDBMS.PostgreSQL:
return(";LASTVAL();");
case ZDBMS.SQLServer:
return(";SELECT SCOPE_IDENTITY();");
default:
return("");
}
}
/// <summary>
/// Does DBMS generate Identity Ids ?
/// </summary>
/// <param name="database">Database</param>
/// <returns>Generates ?</returns>
public static bool GeneratesIdentity(ZDBMS database)
{
bool result;
switch (database)
{
case ZDBMS.MySQL:
case ZDBMS.SQLite:
case ZDBMS.SQLServer:
result = true;
break;
default:
result = false;
break;
}
return(result);
}
/// <summary>
/// Get Sequence SQL
/// </summary>
/// <param name="dbms"></param>
/// <param name="entity"></param>
/// <returns></returns>
public static string GetSequenceSql(ZDBMS dbms, string entity)
{
switch (dbms)
{
case ZDBMS.Firebird:
// Firebird Generators: CREATE GENERATOR Generator
return("SELECT GEN_ID(" + SequencePrefix + entity + ", 1) FROM RDB$DATABASE");
case ZDBMS.Oracle:
// Oracle Sequences: CREATE SEQUENCE Sequence
return("SELECT " + SequencePrefix + entity + ".NEXTVAL FROM DUAL");
case ZDBMS.PostgreSQL:
// PostgreSQL Sequences: CREATE SEQUENCE Sequence
return("SELECT NEXTVAL('" + SequencePrefix + entity + "')");
default:
return("");
}
}
/// <summary>
/// Get isolation level SQL.
/// </summary>
/// <param name="dbms">DBMS</param>
/// <param name="isolationLevel">Isolation level</param>
/// <returns></returns>
public static string SqlIsolationLevel(ZDBMS dbms, IsolationLevel isolationLevel)
{
string result = "";
switch (dbms)
{
case ZDBMS.SQLServer:
// SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
// https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-2017
if (isolationLevel == IsolationLevel.ReadUncommitted)
{
result = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED";
}
break;
}
return(result);
}
/// <summary>
/// Get SQL parameter token
/// </summary>
/// <param name="provider"></param>
/// <returns></returns>
public static string SqlParameterToken(DbProviderFactory provider)
{
string parameter;
ZDBMS db = GetDBMS(provider);
switch (db)
{
case ZDBMS.Firebird:
parameter = "@";
break;
case ZDBMS.MySQL:
parameter = "@";
break;
case ZDBMS.Oracle:
parameter = ":";
break;
case ZDBMS.PostgreSQL:
parameter = ":";
break;
case ZDBMS.SQLite:
parameter = "@";
break;
case ZDBMS.SQLServer:
parameter = "@";
break;
default:
parameter = "";
break;
}
return(parameter);
}
/// <summary>
/// Does DBMS have Server-Side Joins ?
/// </summary>
/// <param name="database">Database</param>
/// <returns>Has ?</returns>
public static bool HasServerSideJoins(ZDBMS database)
{
bool result;
switch (database)
{
case ZDBMS.Firebird:
case ZDBMS.MySQL:
case ZDBMS.Oracle:
case ZDBMS.PostgreSQL:
case ZDBMS.RavenDB:
case ZDBMS.SQLite:
case ZDBMS.SQLServer:
result = false;
break;
default:
result = false;
break;
}
return(result);
}
/// <summary>
/// Convert "#" parameter token to database specific parameter token
/// </summary>
/// <param name="command">Database command</param>
/// <param name="provider">Database provider</param>
public static void SqlParameters(DbCommand command, DbProviderFactory provider)
{
ZDBMS db = GetDBMS(provider);
switch (db)
{
case ZDBMS.Firebird:
command.CommandText = command.CommandText.Replace("#", "@");
command.CommandText = command.CommandText.Replace("@Value IS NULL", "CAST(@Value AS VARCHAR(10)) IS NULL");
foreach (DbParameter parameter in command.Parameters)
{
parameter.ParameterName = parameter.ParameterName.Replace("#", "@");
}
break;
case ZDBMS.MySQL:
command.CommandText = command.CommandText.Replace("#", "@");
foreach (DbParameter parameter in command.Parameters)
{
parameter.ParameterName = parameter.ParameterName.Replace("#", "@");
}
break;
case ZDBMS.Oracle:
command.CommandText = command.CommandText.Replace("#", ":");
foreach (DbParameter parameter in command.Parameters)
{
parameter.ParameterName = parameter.ParameterName.Replace("#", ":");
}
if (provider.GetType().FullName == "Oracle.DataAccess.Client.OracleClientFactory")
{
//((OracleCommand)command).BindByName = true;
command.GetType().GetProperty("BindByName").SetValue(command, true, null);
}
break;
case ZDBMS.PostgreSQL:
command.CommandText = command.CommandText.Replace("#", ":");
foreach (DbParameter parameter in command.Parameters)
{
parameter.ParameterName = parameter.ParameterName.Replace("#", ":");
}
break;
case ZDBMS.SQLite:
command.CommandText = command.CommandText.Replace("#", "@");
foreach (DbParameter parameter in command.Parameters)
{
parameter.ParameterName = parameter.ParameterName.Replace("#", "@");
}
break;
case ZDBMS.SQLServer:
command.CommandText = command.CommandText.Replace("#", "@");
foreach (DbParameter parameter in command.Parameters)
{
parameter.ParameterName = parameter.ParameterName.Replace("#", "@");
}
break;
}
}
public SyncfusionGrid(Type type, ZDBMS dbms)
{
DataNamespace = type.Namespace;
HasServerSideJoins = PersistenceHelper.HasServerSideJoins(dbms);
}
