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); }