Database.cs
/*
Description: Vega - Fastest ORM with enterprise features
Author: Ritesh Sutaria
Date: 9-Dec-2017
Home Page: https://github.com/aadreja/vega
http://www.vegaorm.com
*/
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading;
namespace Vega
{
internal static clast DBCache
{
private static ReaderWriterLockSlim cacheLock = new ReaderWriterLockSlim();
private static Dictionary dbs = new Dictionary();
public static Database Get(IDbConnection con)
{
string key = con.GetType().Name + "," + con.ConnectionString;
Database db;
try
{
cacheLock.EnterReadLock();
if (dbs.TryGetValue(key, out db)) return db;
}
finally
{
cacheLock.ExitReadLock();
}
if (key.ToLowerInvariant().Contains("npgsqlconnection"))
db = new PgSqlDatabase();
else if (key.ToLowerInvariant().Contains("sqliteconnection"))
db = new SQLiteDatabase();
else
db = new MsSqlDatabase();
try
{
cacheLock.EnterWriteLock();
return dbs[key] = db;
}
finally
{
cacheLock.ExitWriteLock();
}
}
}
internal abstract clast Database
{
protected Dictionary dbTypeString;
#region abstract methods
public abstract string DBObjectExistsQuery(string name, DBObjectTypeEnum objectType, string schema = null);
public abstract string IndexExistsQuery(string tableName, string indexName);
public abstract string CreateTableQuery(Type ensaty);
public abstract string CreateIndexQuery(string tableName, string indexName, string columns, bool isUnique);
public abstract DBVersionInfo FetchDBServerInfo(IDbConnection connection);
#endregion
#region virtual methods
public virtual string DropTableQuery(Type ensaty)
{
TableAttribute tableInfo = EnsatyCache.Get(ensaty);
return $"DROP TABLE {tableInfo.FullName}";
}
public virtual string VirtualForeignKeyCheckQuery(ForeignKey vfk)
{
StringBuilder query = new StringBuilder();
query.Append($"SELECT {vfk.ColumnName} FROM {vfk.FullTableName} WHERE {vfk.ColumnName}=@Id");
if (vfk.ContainsIsActive)
query.Append($" AND {Config.IsActiveColumnName}={BITTRUEVALUE}");
query.Append(" LIMIT 1 ");
return query.ToString();
}
#endregion
#region abstract properties
public abstract string DEFAULTSCHEMA { get; }
public abstract string CURRENTDATETIMESQL { get; }
public abstract string BITTRUEVALUE { get; }
public abstract string BITFALSEVALUE { get; }
public abstract string LASTINSERTEDROWIDSQL { get; }
public abstract Dictionary DbTypeString { get; }
#endregion
#region properties
DBVersionInfo dbVersion;
internal DBVersionInfo GetDBVersion(IDbConnection connection)
{
if (dbVersion == null)
dbVersion = FetchDBServerInfo(connection);
return dbVersion;
}
#endregion
#region Create CRUD commands
internal virtual void CreateAddCommand(IDbCommand cmd, object ensaty, IAuditTrail audit = null, string columnNames = null, bool doNotAppendCommonFields = false, bool overrideCreatedUpdatedOn = false)
{
TableAttribute tableInfo = EnsatyCache.Get(ensaty.GetType());
if (tableInfo.NeedsHistory && tableInfo.IsCreatedByEmpty(ensaty))
throw new MissingFieldException("CreatedBy is required when Audit Trail is enabled");
List columns = new List();
if (!string.IsNullOrEmpty(columnNames)) columns.AddRange(columnNames.Split(','));
else columns.AddRange(tableInfo.DefaultInsertColumns);//Get columns from Ensaty attributes loaded in TableInfo
bool isPrimaryKeyEmpty = false;
foreach(ColumnAttribute pkCol in tableInfo.Columns.Where(p=>p.Value.IsPrimaryKey).Select(p=>p.Value))
{
if (pkCol.PrimaryKeyInfo.IsIdensaty && tableInfo.IsKeyIdEmpty(ensaty, pkCol))
{
isPrimaryKeyEmpty = true;
//if idensaty remove keyfield if added in field list
columns.Remove(pkCol.Name);
}
else if (pkCol.Property.PropertyType == typeof(Guid) && tableInfo.IsKeyIdEmpty(ensaty, pkCol))
{
isPrimaryKeyEmpty = true;
//if not idensaty and key not generated, generate before save
tableInfo.SetKeyId(ensaty, pkCol, Guid.NewGuid());
}
}
#region append common columns
if (!doNotAppendCommonFields)
{
if (!tableInfo.NoIsActive)
{
if (!columns.Contains(Config.ISACTIVE_COLUMN.Name))
columns.Add(Config.ISACTIVE_COLUMN.Name);
bool isActive = tableInfo.GetIsActive(ensaty) ?? true;
//when IsActive is not set then true for insert
cmd.AddInParameter("@" + Config.ISACTIVE_COLUMN.Name, Config.ISACTIVE_COLUMN.ColumnDbType, isActive);
if (tableInfo.NeedsHistory)
audit.AppendDetail(Config.ISACTIVE_COLUMN.Name, isActive, DbType.Boolean, null);
tableInfo.SetIsActive(ensaty, isActive); //Set IsActive value
}
if (!tableInfo.NoVersionNo)
{
int versionNo = tableInfo.GetVersionNo(ensaty) ?? 1; //set defualt versionno 1 for Insert
if (versionNo == 0) versionNo = 1; //set defualt versionno 1 for Insert even if its zero or null
if (!columns.Contains(Config.VERSIONNO_COLUMN.Name))
columns.Add(Config.VERSIONNO_COLUMN.Name);
cmd.AddInParameter("@" + Config.VERSIONNO_COLUMN.Name, Config.VERSIONNO_COLUMN.ColumnDbType, versionNo);
tableInfo.SetVersionNo(ensaty, versionNo); //Set VersionNo value
}
if (!tableInfo.NoCreatedBy)
{
if (!columns.Contains(Config.CREATEDBY_COLUMN.Name))
columns.Add(Config.CREATEDBY_COLUMN.Name);
cmd.AddInParameter("@" + Config.CREATEDBY_COLUMN.Name, Config.CREATEDBY_COLUMN.ColumnDbType, tableInfo.GetCreatedBy(ensaty));
}
if (!tableInfo.NoCreatedOn & !columns.Contains(Config.CREATEDON_COLUMN.Name))
{
columns.Add(Config.CREATEDON_COLUMN.Name);
}
if (!tableInfo.NoUpdatedBy)
{
if (!columns.Contains(Config.UPDATEDBY_COLUMN.Name))
columns.Add(Config.UPDATEDBY_COLUMN.Name);
cmd.AddInParameter("@" + Config.UPDATEDBY_COLUMN.Name, Config.UPDATEDBY_COLUMN.ColumnDbType, tableInfo.GetCreatedBy(ensaty));
}
if (!tableInfo.NoUpdatedOn & !columns.Contains(Config.UPDATEDON_COLUMN.Name))
{
columns.Add(Config.UPDATEDON_COLUMN.Name);
}
}
#endregion
//append @ before each fields to add as parameter
List parameters = columns.Select(c => "@" + c).ToList();
int pIndex = parameters.FindIndex(c => c == "@" + Config.CREATEDON_COLUMN.Name);
if (pIndex >= 0)
{
var createdOn = Helper.GetDateTimeOrDatabaseDateTimeSQL(tableInfo.GetCreatedOn(ensaty), this, overrideCreatedUpdatedOn);
if (createdOn is string)
{
parameters[pIndex] = (string)createdOn;
}
else
{
cmd.AddInParameter(parameters[pIndex], Config.CREATEDON_COLUMN.ColumnDbType, createdOn);
}
//parameters[pIndex] = (string)Helper.GetDateTimeOrDatabaseDateTimeSQL(tableInfo.GetCreatedOn(ensaty), this, overrideCreatedUpdatedOn);
}
pIndex = parameters.FindIndex(c => c == "@" + Config.UPDATEDON_COLUMN.Name);
if (pIndex >= 0)
{
var updatedOn = Helper.GetDateTimeOrDatabaseDateTimeSQL(tableInfo.GetUpdatedOn(ensaty), this, overrideCreatedUpdatedOn);
if (updatedOn is string)
{
parameters[pIndex] = (string)updatedOn;
}
else
{
cmd.AddInParameter(parameters[pIndex], Config.CREATEDON_COLUMN.ColumnDbType, updatedOn);
}
//parameters[pIndex] = (string)Helper.GetDateTimeOrDatabaseDateTimeSQL(tableInfo.GetUpdatedOn(ensaty), this, overrideCreatedUpdatedOn);
}
StringBuilder cmdText = new StringBuilder();
cmdText.Append($"INSERT INTO {tableInfo.FullName} ({string.Join(",", columns)}) VALUES({string.Join(",", parameters)});");
if (tableInfo.IsKeyIdensaty() && isPrimaryKeyEmpty)
{
//add query to get inserted id
cmdText.Append(LASTINSERTEDROWIDSQL);
}
//remove common columns and parameters already added above
columns.RemoveAll(c => c == Config.CREATEDON_COLUMN.Name || c == Config.CREATEDBY_COLUMN.Name
|| c == Config.UPDATEDON_COLUMN.Name || c == Config.UPDATEDBY_COLUMN.Name
|| c == Config.VERSIONNO_COLUMN.Name || c == Config.ISACTIVE_COLUMN.Name);
cmd.CommandType = CommandType.Text;
cmd.CommandText = cmdText.ToString();
for (int i = 0; i < columns.Count(); i++)
{
tableInfo.Columns.TryGetValue(columns[i], out ColumnAttribute columnInfo); //find column attribute
DbType dbType = DbType.Object;
object columnValue = null;
if (columnInfo != null && columnInfo.GetMethod != null)
{
dbType = columnInfo.ColumnDbType;
columnValue = columnInfo.GetAction(ensaty);
if (tableInfo.NeedsHistory) audit.AppendDetail(columns[i], columnValue, dbType, null);
}
cmd.AddInParameter("@" + columns[i], dbType, columnValue);
}
}
internal virtual bool CreateUpdateCommand(IDbCommand cmd, object ensaty, object oldEnsaty, IAuditTrail audit = null, string columnNames = null, bool doNotAppendCommonFields = false, bool overrideCreatedUpdatedOn = false)
{
bool isUpdateNeeded = false;
TableAttribute tableInfo = EnsatyCache.Get(ensaty.GetType());
if (!tableInfo.NoUpdatedBy && tableInfo.IsUpdatedByEmpty(ensaty))
throw new MissingFieldException("Updated By is required");
List columns = new List();
if (!string.IsNullOrEmpty(columnNames)) columns.AddRange(columnNames.Split(','));
else columns.AddRange(tableInfo.DefaultUpdateColumns);//Get columns from Ensaty attributes loaded in TableInfo
StringBuilder cmdText = new StringBuilder();
cmdText.Append($"UPDATE {tableInfo.FullName} SET ");
//add default columns if doesn't exists
if (!doNotAppendCommonFields)
{
if (!tableInfo.NoVersionNo && !columns.Contains(Config.VERSIONNO_COLUMN.Name))
columns.Add(Config.VERSIONNO_COLUMN.Name);
if (!tableInfo.NoUpdatedBy && !columns.Contains(Config.UPDATEDBY_COLUMN.Name))
columns.Add(Config.UPDATEDBY_COLUMN.Name);
if (!tableInfo.NoUpdatedOn && !columns.Contains(Config.UPDATEDON_COLUMN.Name))
columns.Add(Config.UPDATEDON_COLUMN.Name);
}
//remove primarykey, createdon and createdby columns if exists
columns.RemoveAll(c => tableInfo.PkColumnList.Select(p=>p.Name).Contains(c));
columns.RemoveAll(c => c == Config.CREATEDON_COLUMN.Name ||
c == Config.CREATEDBY_COLUMN.Name);
for (int i = 0; i < columns.Count(); i++)
{
if (columns[i].Equals(Config.VERSIONNO_COLUMN.Name, StringComparison.OrdinalIgnoreCase))
{
cmdText.Append($"{columns[i]} = {columns[i]}+1");
cmdText.Append(",");
}
else if (columns[i].Equals(Config.UPDATEDBY_COLUMN.Name, StringComparison.OrdinalIgnoreCase))
{
cmdText.Append($"{columns[i]} = @{columns[i]}");
cmdText.Append(",");
cmd.AddInParameter("@" + columns[i], Config.UPDATEDBY_COLUMN.ColumnDbType, tableInfo.GetUpdatedBy(ensaty));
}
else if (columns[i].Equals(Config.UPDATEDON_COLUMN.Name, StringComparison.OrdinalIgnoreCase))
{
var updatedOn = Helper.GetDateTimeOrDatabaseDateTimeSQL(tableInfo.GetUpdatedOn(ensaty), this, overrideCreatedUpdatedOn);
if (updatedOn is string)
{
cmdText.Append($"{columns[i]} = {CURRENTDATETIMESQL}");
}
else
{
cmdText.Append($"{columns[i]} = @{columns[i]}");
cmd.AddInParameter("@" + columns[i], Config.UPDATEDON_COLUMN.ColumnDbType, updatedOn);
}
cmdText.Append(",");
}
else
{
bool includeInUpdate = true;
tableInfo.Columns.TryGetValue(columns[i], out ColumnAttribute columnInfo); //find column attribute
DbType dbType = DbType.Object;
object columnValue = null;
if (columnInfo != null && columnInfo.GetMethod != null)
{
dbType = columnInfo.ColumnDbType;
columnValue = columnInfo.GetAction(ensaty);
includeInUpdate = oldEnsaty == null; //include in update when oldEnsaty not available
//compare with old object to check whether update is needed or not
object oldColumnValue = null;
if (oldEnsaty != null)
{
oldColumnValue = columnInfo.GetAction(oldEnsaty);
if (oldColumnValue != null && columnValue != null)
{
if (!oldColumnValue.Equals(columnValue)) //add to history only if property is modified
{
includeInUpdate = true;
}
}
else if (oldColumnValue == null && columnValue != null)
{
includeInUpdate = true;
}
else if (oldColumnValue != null)
{
includeInUpdate = true;
}
}
if (tableInfo.NeedsHistory && includeInUpdate) audit.AppendDetail(columns[i], columnValue, dbType, oldColumnValue);
}
if (includeInUpdate)
{
isUpdateNeeded = true;
cmdText.Append($"{columns[i]} = @{columns[i]}");
cmdText.Append(",");
cmd.AddInParameter("@" + columns[i], dbType, columnValue);
}
}
}
cmdText.RemoveLastComma(); //Remove last comma if exists
cmdText.Append(" WHERE ");
if (tableInfo.PkColumnList.Count > 1)
{
int index = 0;
foreach (ColumnAttribute pkCol in tableInfo.PkColumnList)
{
cmdText.Append($" {(index > 0 ? " AND " : "")} {pkCol.Name}=@{pkCol.Name}");
cmd.AddInParameter("@" + pkCol.Name, pkCol.ColumnDbType, tableInfo.GetKeyId(ensaty, pkCol));
index++;
}
}
else
{
cmdText.Append($" {tableInfo.PkColumn.Name}=@{tableInfo.PkColumn.Name}");
cmd.AddInParameter("@" + tableInfo.PkColumn.Name, tableInfo.PkColumn.ColumnDbType, tableInfo.GetKeyId(ensaty));
}
if (Config.DbConcurrencyCheck && !tableInfo.NoVersionNo)
{
cmdText.Append($" AND {Config.VERSIONNO_COLUMN.Name}=@{Config.VERSIONNO_COLUMN.Name}");
cmd.AddInParameter("@" + Config.VERSIONNO_COLUMN.Name, Config.VERSIONNO_COLUMN.ColumnDbType, tableInfo.GetVersionNo(ensaty));
}
cmd.CommandType = CommandType.Text;
cmd.CommandText = cmdText.ToString();
return isUpdateNeeded;
}
internal virtual void CreateSelectCommandForReadOne(IDbCommand cmd, object id, string columns)
{
TableAttribute tableInfo = EnsatyCache.Get(typeof(T));
cmd.CommandType = CommandType.Text;
if (tableInfo.PkColumnList.Count > 1)
{
if (!(id is T))
{
throw new InvalidOperationException("Ensaty has multiple primary keys. Past ensaty setting Primary Key attributes.");
}
StringBuilder cmdText = new StringBuilder($"SELECT {columns} FROM {tableInfo.FullName} WHERE ");
int index = 0;
foreach (ColumnAttribute pkCol in tableInfo.PkColumnList)
{
cmdText.Append($" {(index > 0 ? " AND " : "")} {pkCol.Name}=@{pkCol.Name}");
cmd.AddInParameter("@" + pkCol.Name, pkCol.ColumnDbType, tableInfo.GetKeyId(id, pkCol));
index++;
}
cmd.CommandText = cmdText.ToString();
}
else
{
cmd.CommandText = $"SELECT {columns} FROM {tableInfo.FullName} WHERE {tableInfo.PkColumn.Name}=@{tableInfo.PkColumn.Name}";
cmd.AddInParameter(tableInfo.PkColumn.Name, tableInfo.PkColumn.ColumnDbType, id);
}
}
internal virtual StringBuilder CreateSelectCommand(IDbCommand cmd, string query, object parameters = null)
{
return CreateSelectCommand(cmd, query, null, parameters);
}
internal virtual StringBuilder CreateSelectCommand(IDbCommand cmd, string query, string criteria = null, object parameters = null)
{
bool hasWhere = query.ToLowerInvariant().Contains("where");
StringBuilder cmdText = new StringBuilder(query);
if (!string.IsNullOrEmpty(criteria))
{
//add WHERE statement if not exists in query or criteria
if (!hasWhere && !criteria.ToLowerInvariant().Contains("where"))
cmdText.Append(" WHERE ");
cmdText.Append(criteria);
}
ParameterCache.AddParameters(parameters, cmd);
return cmdText;
}
internal virtual void CreateTextCommand(IDbCommand cmd, string query, string criteria = null, object parameters = null)
{
bool hasWhere = query.ToLowerInvariant().Contains("where");
if (!string.IsNullOrEmpty(criteria))
{
//add WHERE statement if not exists in query or criteria
if (!hasWhere && !criteria.ToLowerInvariant().Contains("where"))
query = query + " WHERE ";
}
query = query + " " + criteria;
cmd.CommandText = query;
cmd.CommandType = CommandType.Text;
ParameterCache.AddParameters(parameters, cmd);
}
internal virtual void CreateReadAllPagedCommand(IDbCommand cmd, string query, string orderBy, int pageNo, int pageSize, object parameters = null)
{
StringBuilder cmdText = new StringBuilder();
if (this is MsSqlDatabase)
{
if (GetDBVersion(cmd.Connection).Version.Major >= 11) //SQL server 2012 and above supports offset
cmdText.Append($@"{query}
ORDER BY {orderBy}
OFFSET {((pageNo - 1) * pageSize)} ROWS
FETCH NEXT {pageSize} ROWS ONLY");
else
cmdText.Append($@"SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY {orderBy}) AS rownumber,
* FROM ({query}) as sq
) AS q
WHERE (rownumber between {((pageNo - 1) * pageSize) + 1} AND {pageNo * pageSize})");
}
else
{
cmdText.Append($@"{query}
ORDER BY {orderBy}
LIMIT {pageSize}
OFFSET {((pageNo - 1) * pageSize)}");
}
cmd.CommandType = CommandType.Text;
cmd.CommandText = cmdText.ToString();
ParameterCache.AddParameters(parameters, cmd); //ParameterCache.GetFromCache(parameters, cmd).Invoke(parameters, cmd);
}
internal virtual void CreateReadAllPagedNoOffsetCommand(IDbCommand cmd, string query, string orderBy, int pageSize, PageNavigationEnum navigation, object[] lastOrderByColumnValues = null, object lastKeyId = null, object parameters = null)
{
string[] orderByColumns = orderBy.Split(',');
string[] orderByDirection = new string[orderByColumns.Length];
for (int i = 0; i < orderByColumns.Length; i++)
{
if (orderByColumns[i].ToLowerInvariant().Contains("desc"))
{
orderByDirection[i] = "DESC";
orderByColumns[i] = orderByColumns[i].ToLowerInvariant().Replace("desc", "").Trim();
}
else
{
orderByDirection[i] = "ASC";
orderByColumns[i] = orderByColumns[i].ToLowerInvariant().Replace("asc", "").Trim();
}
}
if (orderByColumns.Length == 0)
throw new MissingMemberException("Orderby column(s) is missing");
if ((navigation == PageNavigationEnum.Next || navigation == PageNavigationEnum.Previous) && lastOrderByColumnValues.Length != orderByColumns.Length)
throw new MissingMemberException("For Next and Previous Navigation Length of Last Values must be equal to orderby columns length");
if ((navigation == PageNavigationEnum.Next || navigation == PageNavigationEnum.Previous) && lastKeyId == null)
throw new MissingMemberException("For Next and Previous Navigation Last KeyId is required");
TableAttribute tableInfo = EnsatyCache.Get(typeof(T));
bool hasWhere = query.ToLowerInvariant().Contains("where");
StringBuilder pagedCriteria = new StringBuilder();
StringBuilder pagedOrderBy = new StringBuilder();
if (!hasWhere)
pagedCriteria.Append(" WHERE 1=1");
for (int i = 0; i < orderByColumns.Length; i++)
{
string applyEquals = (i ")}) OR {orderByColumns[i]} >{applyEquals} @p_{orderByColumns[i]})");
else
pagedCriteria.Append($" AND (({orderByColumns[i]} = @p_{orderByColumns[i]} {GetPrimaryColumnCriteriaForPagedQuery(tableInfo, " 0) pagedOrderBy.Append(",");
if (navigation == PageNavigationEnum.Last || navigation == PageNavigationEnum.Previous)
{
//reverse sort as we are going backward
pagedOrderBy.Append($"{orderByColumns[i]} {(orderByDirection[i] == "ASC" ? "DESC" : "ASC")}");
}
else
{
pagedOrderBy.Append($"{orderByColumns[i]} {orderByDirection[i]}");
}
}
//add keyfield parameter for Next and Previous navigation
if (navigation == PageNavigationEnum.Next || navigation == PageNavigationEnum.Previous)
{
//add LastKeyId Parameter
if (tableInfo.PkColumnList.Count > 1)
{
if (!(lastKeyId is T))
{
throw new InvalidOperationException("Ensaty has multiple primary keys. Past ensaty setting Primary Key attributes.");
}
int index = 0;
foreach (ColumnAttribute pkCol in tableInfo.PkColumnList)
{
cmd.AddInParameter("@p_" + pkCol.Name, pkCol.ColumnDbType, tableInfo.GetKeyId(lastKeyId, pkCol));
index++;
}
}
else
{
cmd.AddInParameter("@p_" + tableInfo.PkColumn.Name, tableInfo.PkColumn.ColumnDbType, lastKeyId);
}
}
//add keyfield in orderby clause. Direction will be taken from 1st orderby column
if (navigation == PageNavigationEnum.Last || navigation == PageNavigationEnum.Previous)
{
//reverse sort as we are going backward
if (tableInfo.PkColumnList.Count > 1)
{
foreach (ColumnAttribute pkCol in tableInfo.PkColumnList)
{
pagedOrderBy.Append($",{pkCol.Name} {(orderByDirection[0] == "ASC" ? "DESC" : "ASC")}");
}
}
else
{
pagedOrderBy.Append($",{tableInfo.PkColumn.Name} {(orderByDirection[0] == "ASC" ? "DESC" : "ASC")}");
}
}
else
{
if (tableInfo.PkColumnList.Count > 1)
{
foreach (ColumnAttribute pkCol in tableInfo.PkColumnList)
{
pagedOrderBy.Append($",{pkCol.Name} {orderByDirection[0]}");
}
}
else
{
pagedOrderBy.Append($",{tableInfo.PkColumn.Name} {orderByDirection[0]}");
}
}
cmd.CommandType = CommandType.Text;
if (this is MsSqlDatabase)
cmd.CommandText = $"SELECT * FROM (SELECT TOP {pageSize} * FROM ({query} {pagedCriteria.ToString()}) AS r1 ORDER BY {pagedOrderBy}) AS r2 ORDER BY {orderBy}";
else
cmd.CommandText = $"SELECT * FROM ({query} {pagedCriteria.ToString()} ORDER BY {pagedOrderBy} LIMIT {pageSize}) AS r ORDER BY {orderBy}";
ParameterCache.AddParameters(parameters, cmd); //ParameterCache.GetFromCache(parameters, cmd).Invoke(parameters, cmd);
}
#endregion
#region other methods
internal string GetPrimaryColumnCriteriaForPagedQuery(TableAttribute tableInfo, string op)
{
StringBuilder result = new StringBuilder();
foreach(ColumnAttribute pkCol in tableInfo.PkColumnList)
{
result.Append($" AND {pkCol.Name} {op} @p_{pkCol.Name} ");
}
return result.ToString();
}
internal void AppendStatusCriteria(StringBuilder cmdText, RecordStatusEnum status = RecordStatusEnum.All)
{
if (status == RecordStatusEnum.All) return; //nothing to do
//add missing where clause
if (!cmdText.ToString().ToLowerInvariant().Contains("where"))
cmdText.Append(" WHERE ");
if (status == RecordStatusEnum.Active)
cmdText.Append($" {Config.ISACTIVE_COLUMN.Name}={BITTRUEVALUE}");
else if (status == RecordStatusEnum.InActive)
cmdText.Append($" {Config.ISACTIVE_COLUMN.Name}={BITFALSEVALUE}");
}
internal virtual string GetDBTypeWithSize(DbType type, int size, int scale=0)
{
if(type == DbType.String || type == DbType.StringFixedLength)
{
if (size > 0)
return DbTypeString[DbType.StringFixedLength] + "(" + size + ")";
else
return DbTypeString[DbType.String];
}
else if (type == DbType.AnsiString || type == DbType.AnsiStringFixedLength)
{
if (size > 0)
return DbTypeString[DbType.AnsiStringFixedLength] + "(" + size + ")";
else
return DbTypeString[DbType.AnsiString];
}
else if (type == DbType.Decimal)
{
if (size > 0 && scale > 0)
return DbTypeString[DbType.Decimal] + $"({size},{scale})";
else if (size > 0)
return DbTypeString[DbType.Decimal] + $"({size})";
else
return DbTypeString[DbType.Decimal];
}
else
return DbTypeString[type];
}
internal bool IsNullableType(Type type)
{
if (!type.IsGenericType)
{
return false;
}
if (type.GetGenericTypeDefinition() == typeof(Nullable))
{
return true;
}
else
{
return false;
}
}
#endregion
}
///
/// Database Version Information
///
public clast DBVersionInfo
{
///
/// Database Product Name like Microsoft SQL Server 2012
///
public string ProductName { get; set; }
///
/// Database Edition details like Standard, Enterprise, Express
///
public string Edition { get; set; }
///
/// Database Version Info
///
public Version Version { get; set; }
///
/// Gets or set whether database is 64bit or 32bit
///
public bool Is64Bit { get; set; }
///
/// SQL Server Only. To check whether OFFSET keyword is supported by sql version
///
internal bool IsOffsetSupported
{
get
{
//SQL Server 2012 and above supports offset keyword
return ProductName.ToLowerInvariant().Contains("sql server") && Version.Major >= 11;
}
}
}
}
