protected override string CreateSelectTopStatement(string tableName, string where, string[] columns, string orderBy, string groupBy, int topCount) { StringBuilder sqlBuilder = new StringBuilder("SELECT TOP "); sqlBuilder.Append(topCount); sqlBuilder.Append(' '); for (int i = 0; i < columns.Length; ++i) { SqlQueryUtils.AppendColumnName(sqlBuilder, columns[i], parameterLeftToken, parameterRightToken); if (i < columns.Length - 1) { sqlBuilder.Append(','); } } sqlBuilder.Append(" FROM "); sqlBuilder.Append(tableName); sqlBuilder.Append(' '); sqlBuilder.Append(where); sqlBuilder.Append(" ORDER BY " + orderBy); sqlBuilder.Append(" GROUP BY " + groupBy); return(sqlBuilder.ToString()); }
public new SubQuery Alias(string aliasName) { this.sql.Append(' '); SqlQueryUtils.AppendColumnName(this.sql, aliasName); return(this); }
public SelectSqlSection(Database db, QueryTable table, params ExpressionClip[] columns) { this.db = db; this.tableName = table.TableName; string aliasName = table.TableAlias; this.whereClip = new WhereClip(new FromClip(this.tableName, string.IsNullOrEmpty(aliasName) ? this.tableName : aliasName)); if (table is IExpression) { SqlQueryUtils.AddParameters(this.whereClip.Parameters, table as IExpression); } if (columns != null && columns.Length > 0) { this.columnNames = new string[columns.Length]; for (int i = 0; i < columns.Length; ++i) { this.columnNames[i] = columns[i].ToString(); //add parameters in column to whereClip if (columns[i].Parameters.Count > 0) { SqlQueryUtils.AddParameters(this.whereClip.Parameters, columns[i]); } } } }
protected virtual DbCommand CreateSelectTopCommand(WhereClip where, string[] columns, int topCount) { DbCommand cmd = fac.CreateCommand(); cmd.CommandType = CommandType.Text; StringBuilder sb = new StringBuilder("SELECT TOP "); sb.Append(topCount); sb.Append(' '); for (int i = 0; i < columns.Length; ++i) { SqlQueryUtils.AppendColumnName(sb, columns[i]); if (i < columns.Length - 1) { sb.Append(','); } } sb.Append(" FROM "); sb.Append(where.ToString()); AddExpressionParameters(where, cmd); cmd.CommandText = SqlQueryUtils.ReplaceDatabaseTokens(sb.ToString(), leftToken, rightToken, paramPrefixToken, wildcharToken, wildsinglecharToken); PrepareCommand(cmd); return(cmd); }
public SelectSqlSection RightJoin(QueryTable joinTable, string joinTableAliasName, WhereClip joinOnWhere) { this.whereClip.From.RightJoin(joinTable.TableName, joinTableAliasName, joinOnWhere); if (joinOnWhere.Parameters.Count > 0) { SqlQueryUtils.AddParameters(whereClip.Parameters, joinOnWhere); } return(this); }
private string BuildParameterPrefix(string sqlCommandText) { if (string.IsNullOrEmpty(sqlCommandText) || string.IsNullOrWhiteSpace(sqlCommandText)) { return(null); } return(SqlQueryUtils.ReplaceDatabaseTokens(sqlCommandText, this.database.DBProvider.ParameterLeftToken, this.database.DBProvider.ParameterRightToken, this.database.DBProvider.ParameterPrefix, this.database.DBProvider.WildCharToken, this.database.DBProvider.WildSingleCharToken)); }
protected override void PrepareCommand(DbCommand cmd) { base.PrepareCommand(cmd); foreach (DbParameter p in cmd.Parameters) { if (p.Direction == ParameterDirection.Output || p.Direction == ParameterDirection.ReturnValue) { continue; } object value = p.Value; if (value == DBNull.Value) { continue; } Type type = value.GetType(); MySqlParameter mySqlParam = (MySqlParameter)p; if (mySqlParam.DbType != DbType.Guid && type == typeof(Guid)) { mySqlParam.MySqlDbType = MySqlDbType.VarChar; mySqlParam.Size = 36; continue; } if ((p.DbType == DbType.Time || p.DbType == DbType.DateTime) && type == typeof(TimeSpan)) { mySqlParam.MySqlDbType = MySqlDbType.Double; mySqlParam.Value = ((TimeSpan)value).TotalDays; continue; } switch (p.DbType) { case DbType.Binary: if (((byte[])value).Length > 2000) { mySqlParam.MySqlDbType = MySqlDbType.LongBlob; } break; case DbType.Time: mySqlParam.MySqlDbType = MySqlDbType.DateTime; break; case DbType.DateTime: mySqlParam.MySqlDbType = MySqlDbType.DateTime; break; case DbType.AnsiString: if (value.ToString().Length > 65535) { mySqlParam.MySqlDbType = MySqlDbType.LongText; } break; case DbType.String: if (value.ToString().Length > 65535) { mySqlParam.MySqlDbType = MySqlDbType.LongText; } break; case DbType.Object: mySqlParam.MySqlDbType = MySqlDbType.LongText; p.Value = SerializationManager.Instance.Serialize(value); break; } } //replace mysql specific function names in cmd.CommandText cmd.CommandText = cmd.CommandText .Replace("LEN(", "LENGTH(") .Replace("GETDATE()", "NOW()") .Replace("GETUTCDATE()", "UTC_TIMESTAMP()") .Replace("DATEPART(Year,", "YEAR(") .Replace("DATEPART(Month,", "MONTH(") .Replace("DATEPART(Day,", "DAY("); //replace CHARINDEX with INSTR and reverse seqeunce of param items in CHARINDEX() int startIndexOfCharIndex = cmd.CommandText.IndexOf("CHARINDEX("); while (startIndexOfCharIndex > 0) { int endIndexOfCharIndex = SqlQueryUtils.GetEndIndexOfMethod(cmd.CommandText, startIndexOfCharIndex + "CHARINDEX(".Length); string[] itemsInCharIndex = SqlQueryUtils.SplitTwoParamsOfMethodBody( cmd.CommandText.Substring(startIndexOfCharIndex + "CHARINDEX(".Length, endIndexOfCharIndex - startIndexOfCharIndex - "CHARINDEX(".Length)); cmd.CommandText = cmd.CommandText.Substring(0, startIndexOfCharIndex) + "INSTR(" + itemsInCharIndex[1] + "," + itemsInCharIndex[0] + ")" + (cmd.CommandText.Length - 1 > endIndexOfCharIndex ? cmd.CommandText.Substring(endIndexOfCharIndex + 1) : string.Empty); startIndexOfCharIndex = cmd.CommandText.IndexOf("CHARINDEX(", endIndexOfCharIndex); } }
protected override string CreateSelectRangeStatementForUnsortedRows(string tableName, string where, string[] columns, string orderBy, string groupBy, int topCount, int skipCount, string identyColumn) { //SELECT TOP 10 * //FROM TestTable //WHERE (ID NOT IN // (SELECT TOP 20 id // FROM TestTable // ORDER BY id)) //ORDER BY ID StringBuilder outerSqlBuilder = new StringBuilder("SELECT "); if (topCount < int.MaxValue) { outerSqlBuilder.Append("TOP "); outerSqlBuilder.Append(topCount); outerSqlBuilder.Append(' '); } for (int i = 0; i < columns.Length; ++i) { SqlQueryUtils.AppendColumnName(outerSqlBuilder, columns[i], parameterLeftToken, parameterRightToken); if (i < columns.Length - 1) { outerSqlBuilder.Append(','); } } outerSqlBuilder.Append(" FROM "); outerSqlBuilder.Append(tableName); outerSqlBuilder.Append(" WHERE "); StringBuilder innerWhereClipBuilder = new StringBuilder(); innerWhereClipBuilder.Append(tableName); if (!string.IsNullOrEmpty(where) && !string.IsNullOrWhiteSpace(where)) { innerWhereClipBuilder.Append(" WHERE " + where); } StringBuilder orderByGroupByBuilder = new StringBuilder(); if (!string.IsNullOrEmpty(orderBy) && !string.IsNullOrWhiteSpace(orderBy)) { orderByGroupByBuilder.Append(" ORDER BY " + orderBy); } if (!string.IsNullOrEmpty(groupBy) && !string.IsNullOrWhiteSpace(groupBy)) { orderByGroupByBuilder.Append(" GROUP BY " + groupBy); } innerWhereClipBuilder.Append(orderByGroupByBuilder.ToString()); #region Construct & extend CloneWhere StringBuilder innerSqlBuilder = new StringBuilder(); innerSqlBuilder.Append(identyColumn); innerSqlBuilder.Append(" NOT IN (SELECT TOP "); innerSqlBuilder.Append(skipCount); innerSqlBuilder.Append(' '); innerSqlBuilder.Append(identyColumn); innerSqlBuilder.Append(" FROM "); innerSqlBuilder.Append(innerWhereClipBuilder.ToString()); innerSqlBuilder.Append(")"); string outerWhereSql = string.Empty; if (where.Length == 0) { outerWhereSql = innerSqlBuilder.ToString(); } else { outerWhereSql = "(" + where + ") AND " + innerSqlBuilder.ToString(); } #endregion outerSqlBuilder.Append(outerWhereSql); outerSqlBuilder.Append(orderByGroupByBuilder.ToString()); return(SqlQueryUtils.ReplaceDatabaseTokens(outerSqlBuilder.ToString(), this.parameterLeftToken, this.parameterRightToken, this.parameterPrefix, this.wildCharToken, this.wildSingleCharToken)); }
protected override System.Data.Common.DbCommand CreateSelectRangeCommandForUnsortedRows(WhereClip where, string[] columns, int topCount, int skipCount, string identyColumn) { //page split algorithm using ROW_NUMBER() in SqlServer 2005 DbCommand cmd = fac.CreateCommand(); cmd.CommandType = CommandType.Text; StringBuilder sb = new StringBuilder("WITH [__T] AS (SELECT "); if (topCount < int.MaxValue && (int.MaxValue - topCount > skipCount)) { sb.Append("TOP "); sb.Append(topCount + skipCount); sb.Append(' '); } for (int i = 0; i < columns.Length; ++i) { SqlQueryUtils.AppendColumnName(sb, columns[i]); if (i < columns.Length - 1) { sb.Append(','); } } sb.Append(",ROW_NUMBER() OVER (ORDER BY "); if (string.IsNullOrEmpty(where.OrderBy)) { sb.Append(identyColumn); } else { sb.Append(where.OrderBy); } sb.Append(") AS [__Pos]"); sb.Append(" FROM "); if (string.IsNullOrEmpty(where.OrderBy)) { sb.Append(where.ToString()); } else { lock (where) { string tempOrderBy = where.OrderBy; where.OrderBy = null; sb.Append(where.ToString()); where.OrderBy = tempOrderBy; } } sb.Append(") SELECT *"); //for (int i = 0; i < columns.Length; ++i) //{ // sb.Append("[__T].[__C"); // sb.Append(i); // sb.Append(']'); // if (i < columns.Length - 1) // { // sb.Append(','); // } //} sb.Append(" FROM [__T] WHERE [__T].[__Pos]>"); sb.Append(skipCount); if (topCount < int.MaxValue && (int.MaxValue - topCount > skipCount)) { sb.Append(" AND [__T].[__Pos]<="); sb.Append(topCount + skipCount); sb.Append(' '); } AddExpressionParameters(where, cmd); cmd.CommandText = SqlQueryUtils.ReplaceDatabaseTokens(sb.ToString(), leftToken, rightToken, paramPrefixToken, wildcharToken, wildsinglecharToken); PrepareCommand(cmd); return(cmd); }
protected virtual DbCommand CreateSelectRangeCommandForUnsortedRows(WhereClip where, string[] columns, int topCount, int skipCount, string identyColumn) { //SELECT TOP 10 * //FROM TestTable //WHERE (ID NOT IN // (SELECT TOP 20 id // FROM TestTable // ORDER BY id)) //ORDER BY ID DbCommand cmd = fac.CreateCommand(); cmd.CommandType = CommandType.Text; StringBuilder sb = new StringBuilder("SELECT "); if (topCount < int.MaxValue) { sb.Append("TOP "); sb.Append(topCount); sb.Append(' '); } for (int i = 0; i < columns.Length; ++i) { SqlQueryUtils.AppendColumnName(sb, columns[i]); if (i < columns.Length - 1) { sb.Append(','); } } sb.Append(" FROM "); WhereClip cloneWhere = (WhereClip) where.Clone(); #region Construct & extend CloneWhere StringBuilder sbInside = new StringBuilder(); sbInside.Append(identyColumn); sbInside.Append(" NOT IN (SELECT TOP "); sbInside.Append(skipCount); sbInside.Append(' '); sbInside.Append(identyColumn); sbInside.Append(" FROM "); sbInside.Append(where.ToString()); sbInside.Append(")"); if (cloneWhere.Sql.Length == 0) { cloneWhere.Sql = sbInside.ToString(); } else { cloneWhere.Sql = "(" + cloneWhere.Sql.ToString() + ") AND " + sbInside.ToString(); } #endregion sb.Append(cloneWhere.ToString()); AddExpressionParameters(where, cmd); AddExpressionParameters(cloneWhere, cmd); cmd.CommandText = SqlQueryUtils.ReplaceDatabaseTokens(sb.ToString(), leftToken, rightToken, paramPrefixToken, wildcharToken, wildsinglecharToken); PrepareCommand(cmd); return(cmd); }
protected override void PrepareCommand(DbCommand cmd) { base.PrepareCommand(cmd); foreach (DbParameter p in cmd.Parameters) { if (p.Direction == ParameterDirection.Output || p.Direction == ParameterDirection.ReturnValue) { continue; } object value = p.Value; if (value == DBNull.Value) { continue; } Type type = value.GetType(); OleDbParameter oleDbParam = (OleDbParameter)p; if (oleDbParam.DbType != DbType.Guid && type == typeof(Guid)) { oleDbParam.OleDbType = OleDbType.Char; oleDbParam.Size = 36; continue; } if ((p.DbType == DbType.Time || p.DbType == DbType.DateTime) && type == typeof(TimeSpan)) { oleDbParam.OleDbType = OleDbType.Double; oleDbParam.Value = ((TimeSpan)value).TotalDays; continue; } switch (p.DbType) { case DbType.Binary: if (((byte[])value).Length > 2000) { oleDbParam.OleDbType = OleDbType.LongVarBinary; } break; case DbType.Time: oleDbParam.OleDbType = OleDbType.LongVarWChar; p.Value = value.ToString(); break; case DbType.DateTime: oleDbParam.OleDbType = OleDbType.LongVarWChar; p.Value = value.ToString(); break; case DbType.AnsiString: if (value.ToString().Length > 4000) { oleDbParam.OleDbType = OleDbType.LongVarChar; } break; case DbType.String: if (value.ToString().Length > 2000) { oleDbParam.OleDbType = OleDbType.LongVarWChar; } break; case DbType.Object: oleDbParam.OleDbType = OleDbType.LongVarWChar; p.Value = SerializationManager.Instance.Serialize(value); break; } } //replace msaccess specific function names in cmd.CommandText cmd.CommandText = cmd.CommandText .Replace("] [", "] AS [") .Replace("UPPER(", "UCASE(") .Replace("LOWER(", "LCASE(") .Replace("SUBSTRING(", "MID(") .Replace("GETDATE()", "DATE() + TIME()") .Replace("GETUTCDATE()", "DATE() + TIME()") .Replace("DATEPART(Year", "DATEPART('yyyy'") .Replace("DATEPART(Month", "DATEPART('m'") .Replace("DATEPART(Day", "DATEPART('d'") .Replace("TO_NUMBER(", "CDBL(") .Replace("CAST('", "CDATE('").Replace("' AS datetime", "')"); //replace CHARINDEX with INSTR and reverse seqeunce of param items in CHARINDEX() int startIndexOfCharIndex = cmd.CommandText.IndexOf("CHARINDEX("); while (startIndexOfCharIndex > 0) { int endIndexOfCharIndex = SqlQueryUtils.GetEndIndexOfMethod(cmd.CommandText, startIndexOfCharIndex + "CHARINDEX(".Length); string[] itemsInCharIndex = SqlQueryUtils.SplitTwoParamsOfMethodBody( cmd.CommandText.Substring(startIndexOfCharIndex + "CHARINDEX(".Length, endIndexOfCharIndex - startIndexOfCharIndex - "CHARINDEX(".Length)); cmd.CommandText = cmd.CommandText.Substring(0, startIndexOfCharIndex) + "INSTR(" + itemsInCharIndex[1] + "," + itemsInCharIndex[0] + ")" + (cmd.CommandText.Length - 1 > endIndexOfCharIndex ? cmd.CommandText.Substring(endIndexOfCharIndex + 1) : string.Empty); startIndexOfCharIndex = cmd.CommandText.IndexOf("CHARINDEX(", endIndexOfCharIndex); } }
protected override void PrepareCommand(DbCommand cmd) { base.PrepareCommand(cmd); foreach (DbParameter p in cmd.Parameters) { if (p.Direction == ParameterDirection.Output || p.Direction == ParameterDirection.ReturnValue) { if (p.DbType == DbType.Object) { OracleParameter op = (OracleParameter)p; op.OracleType = OracleType.Cursor; } continue; } object value = p.Value; if (value == DBNull.Value) { continue; } Type type = value.GetType(); OracleParameter oracleParam = (OracleParameter)p; if (oracleParam.DbType != DbType.Guid && type == typeof(Guid)) { oracleParam.OracleType = OracleType.Char; oracleParam.Size = 36; continue; } if ((p.DbType == DbType.Time || p.DbType == DbType.DateTime) && type == typeof(TimeSpan)) { oracleParam.OracleType = OracleType.Double; oracleParam.Value = ((TimeSpan)value).TotalDays; continue; } switch (p.DbType) { case DbType.Binary: if (((byte[])value).Length > 2000) { oracleParam.OracleType = OracleType.Blob; } break; case DbType.Time: oracleParam.OracleType = OracleType.DateTime; break; case DbType.DateTime: oracleParam.OracleType = OracleType.DateTime; break; case DbType.AnsiString: if (value.ToString().Length > 4000) { oracleParam.OracleType = OracleType.Clob; } break; case DbType.String: if (value.ToString().Length > 2000) { oracleParam.OracleType = OracleType.NClob; } break; case DbType.Object: oracleParam.OracleType = OracleType.NClob; p.Value = SerializationManager.Instance.Serialize(value); break; } } //replace oracle specific function names in cmd.CommandText cmd.CommandText = cmd.CommandText .Replace("LEN(", "LENGTH(") .Replace("SUBSTRING(", "SUBSTR(") .Replace("GETDATE()", "TO_CHAR(CURRENT_DATE,'DD-MON-YYYY HH:MI:SS')") .Replace("GETUTCDATE()", "TO_CHAR(SYSDATE,'DD-MON-YYYY HH:MI:SS')"); //replace CHARINDEX with INSTR and reverse seqeunce of param items in CHARINDEX() int startIndexOfCharIndex = cmd.CommandText.IndexOf("CHARINDEX("); while (startIndexOfCharIndex > 0) { int endIndexOfCharIndex = SqlQueryUtils.GetEndIndexOfMethod(cmd.CommandText, startIndexOfCharIndex + "CHARINDEX(".Length); string[] itemsInCharIndex = SqlQueryUtils.SplitTwoParamsOfMethodBody( cmd.CommandText.Substring(startIndexOfCharIndex + "CHARINDEX(".Length, endIndexOfCharIndex - startIndexOfCharIndex - "CHARINDEX(".Length)); cmd.CommandText = cmd.CommandText.Substring(0, startIndexOfCharIndex) + "INSTR(" + itemsInCharIndex[1] + "," + itemsInCharIndex[0] + ")" + (cmd.CommandText.Length - 1 > endIndexOfCharIndex ? cmd.CommandText.Substring(endIndexOfCharIndex + 1) : string.Empty); startIndexOfCharIndex = cmd.CommandText.IndexOf("CHARINDEX(", endIndexOfCharIndex); } //replace DATEPART with TO_CHAR(CURRENT_DATE,'XXXX') startIndexOfCharIndex = cmd.CommandText.IndexOf("DATEPART("); if (startIndexOfCharIndex > 0) { cmd.CommandText = cmd.CommandText .Replace("DATEPART(Year", "TO_CHAR('YYYY'") .Replace("DATEPART(Month", "TO_CHAR('MM'") .Replace("DATEPART(Day", "TO_CHAR('DD'"); startIndexOfCharIndex = cmd.CommandText.IndexOf("TO_CHAR("); while (startIndexOfCharIndex > 0) { int endIndexOfCharIndex = SqlQueryUtils.GetEndIndexOfMethod(cmd.CommandText, startIndexOfCharIndex + "TO_CHAR(".Length); string[] itemsInCharIndex = SqlQueryUtils.SplitTwoParamsOfMethodBody( cmd.CommandText.Substring(startIndexOfCharIndex + "TO_CHAR(".Length, endIndexOfCharIndex - startIndexOfCharIndex - "TO_CHAR(".Length)); cmd.CommandText = cmd.CommandText.Substring(0, startIndexOfCharIndex) + "TO_CHAR(" + itemsInCharIndex[1] + "," + itemsInCharIndex[0] + ")" + (cmd.CommandText.Length - 1 > endIndexOfCharIndex ? cmd.CommandText.Substring(endIndexOfCharIndex + 1) : string.Empty); startIndexOfCharIndex = cmd.CommandText.IndexOf("TO_CHAR(", endIndexOfCharIndex); } } }
public override DbCommand CreateSelectRangeCommand(WhereClip where, string[] columns, int topCount, int skipCount, string identyColumn, bool identyColumnIsNumber) { //Check.Require(((object)where) != null && where.From != null, "expr and expr.From could not be null!"); //Check.Require(columns != null && columns.Length > 0, "columns could not be null or empty!"); //Check.Require(topCount > 0, "topCount must > 0!"); if (string.IsNullOrEmpty(where.OrderBy) && identyColumn != null) { where.SetOrderBy(new KeyValuePair <string, bool>[] { new KeyValuePair <string, bool>(identyColumn, false) }); } if (topCount == int.MaxValue && skipCount == 0) { return(CreateSelectCommand(where, columns)); } else { //Check.Require(!string.IsNullOrEmpty(identyColumn), "identyColumn could not be null or empty!"); identyColumn = ColumnFormatter.ValidColumnName(identyColumn); //page split algorithm using ROW_NUMBER() in Oracle9+ DbCommand cmd = fac.CreateCommand(); cmd.CommandType = CommandType.Text; StringBuilder sb = new StringBuilder(); sb.Append("SELECT *"); //for (int i = 0; i < columns.Length; ++i) //{ // sb.Append("[__T].[__C"); // sb.Append(i); // sb.Append(']'); // if (i < columns.Length - 1) // { // sb.Append(','); // } //} sb.Append(" FROM ("); sb.Append("SELECT "); for (int i = 0; i < columns.Length; ++i) { SqlQueryUtils.AppendColumnName(sb, columns[i]); if (i < columns.Length - 1) { sb.Append(','); } } sb.Append(",ROW_NUMBER() OVER (ORDER BY "); if (string.IsNullOrEmpty(where.OrderBy)) { sb.Append(identyColumn); } else { sb.Append(where.OrderBy); } sb.Append(") AS [__Pos]"); sb.Append(" FROM "); if (string.IsNullOrEmpty(where.OrderBy)) { sb.Append(where.ToString()); } else { lock (where) { string tempOrderBy = where.OrderBy; where.OrderBy = null; sb.Append(where.ToString()); where.OrderBy = tempOrderBy; } } sb.Append(") [__T] WHERE [__T].[__Pos]>"); sb.Append(skipCount); if (topCount < int.MaxValue && (int.MaxValue - topCount > skipCount)) { sb.Append(" AND [__T].[__Pos]<="); sb.Append(topCount + skipCount); sb.Append(' '); } AddExpressionParameters(where, cmd); cmd.CommandText = SqlQueryUtils.ReplaceDatabaseTokens(sb.ToString(), leftToken, rightToken, paramPrefixToken, wildcharToken, wildsinglecharToken); PrepareCommand(cmd); return(cmd); } }
protected override void PrepareCommand(DbCommand cmd) { base.PrepareCommand(cmd); foreach (DbParameter p in cmd.Parameters) { if (cmd.CommandType == CommandType.StoredProcedure) { p.ParameterName = string.Empty; } if (p.Direction == ParameterDirection.Output || p.Direction == ParameterDirection.ReturnValue) { continue; } object value = p.Value; if (value == DBNull.Value) { continue; } Type type = value.GetType(); NpgsqlParameter npgParam = (NpgsqlParameter)p; if (npgParam.DbType != DbType.Guid && type == typeof(Guid)) { npgParam.DbType = DbType.String; npgParam.Size = 36; continue; } if ((p.DbType == DbType.Time || p.DbType == DbType.DateTime) && type == typeof(TimeSpan)) { npgParam.DbType = DbType.String; npgParam.Value = "'" + ((TimeSpan)value).TotalDays + " days'"; continue; } switch (p.DbType) { case DbType.String: npgParam.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Text; p.Value = value.ToString(); break; case DbType.Object: npgParam.DbType = DbType.String; p.Value = SerializationManager.Instance.Serialize(value); break; } } //replace postgresql specific function names in cmd.CommandText cmd.CommandText = cmd.CommandText .Replace("SUBSTRING(", "substr(") .Replace("LEN(", "length(") .Replace("GETDATE()", "current_timestamp") .Replace("GETUTCDATE()", "LOCALTIMESTAMP") .Replace("DATEPART(Year", "date_part('year'") .Replace("DATEPART(Month", "date_part('month'") .Replace("DATEPART(Day", "date_part('day'"); //replace CHARINDEX with strpos and reverse seqeunce of param items in CHARINDEX() int startIndexOfCharIndex = cmd.CommandText.IndexOf("CHARINDEX("); while (startIndexOfCharIndex > 0) { int endIndexOfCharIndex = SqlQueryUtils.GetEndIndexOfMethod(cmd.CommandText, startIndexOfCharIndex + "CHARINDEX(".Length); string[] itemsInCharIndex = SqlQueryUtils.SplitTwoParamsOfMethodBody( cmd.CommandText.Substring(startIndexOfCharIndex + "CHARINDEX(".Length, endIndexOfCharIndex - startIndexOfCharIndex - "CHARINDEX(".Length)); cmd.CommandText = cmd.CommandText.Substring(0, startIndexOfCharIndex) + "strpos(" + itemsInCharIndex[1] + "," + itemsInCharIndex[0] + ")" + (cmd.CommandText.Length - 1 > endIndexOfCharIndex ? cmd.CommandText.Substring(endIndexOfCharIndex + 1) : string.Empty); startIndexOfCharIndex = cmd.CommandText.IndexOf("CHARINDEX(", endIndexOfCharIndex); } }
public QueryColumn(string name, DbType type) { SqlQueryUtils.AppendColumnName(this.sql, name); this.DbType = type; }