static public VistaDBCommand BuildStoredProcInsertCommand(tgDataRequest request) { VistaDBCommand cmd = new VistaDBCommand(); if (request.CommandTimeout != null) { cmd.CommandTimeout = request.CommandTimeout.Value; } cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = Delimiters.StoredProcNameOpen + request.ProviderMetadata.spInsert + Delimiters.StoredProcNameClose; PopulateStoredProcParameters(cmd, request); foreach (tgColumnMetadata col in request.Columns) { if (col.HasDefault && col.Default.ToLower() == "GUID()") { VistaDBParameter p = cmd.Parameters[Delimiters.Param + col.PropertyName]; p.Direction = ParameterDirection.InputOutput; } else if (col.IsComputed || col.IsAutoIncrement) { VistaDBParameter p = cmd.Parameters[Delimiters.Param + col.PropertyName]; p.Direction = ParameterDirection.Output; } } return(cmd); }
static public VistaDBCommand BuildDynamicDeleteCommand(tgDataRequest request, List <string> modifiedColumns) { Dictionary <string, VistaDBParameter> types = Cache.GetParameters(request); VistaDBCommand cmd = new VistaDBCommand(); if (request.CommandTimeout != null) { cmd.CommandTimeout = request.CommandTimeout.Value; } string sql = "DELETE FROM " + CreateFullName(request) + " "; string comma = String.Empty; comma = String.Empty; sql += " WHERE "; foreach (tgColumnMetadata col in request.Columns) { if (col.IsInPrimaryKey || col.IsTiraggoConcurrency || col.IsConcurrency) { VistaDBParameter p = types[col.Name]; cmd.Parameters.Add(CloneParameter(p)); sql += comma; sql += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName; comma = " AND "; } } cmd.CommandText = sql; cmd.CommandType = CommandType.Text; return(cmd); }
override internal IDbCommand _LoadFromRawSql(string rawSql, params object[] parameters) { int i = 0; string token = ""; string sIndex = ""; string param = ""; VistaDBCommand cmd = new VistaDBCommand(); foreach (object o in parameters) { sIndex = i.ToString(); token = '{' + sIndex + '}'; param = "@p" + sIndex; rawSql = rawSql.Replace(token, param); VistaDBParameter p = new VistaDBParameter(param, o); cmd.Parameters.Add(p); i++; } cmd.CommandText = rawSql; return(cmd); }
static public VistaDBCommand BuildStoredProcUpdateCommand(tgDataRequest request) { VistaDBCommand cmd = new VistaDBCommand(); if (request.CommandTimeout != null) { cmd.CommandTimeout = request.CommandTimeout.Value; } cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = Delimiters.StoredProcNameOpen + request.ProviderMetadata.spUpdate + Delimiters.StoredProcNameClose; PopulateStoredProcParameters(cmd, request); foreach (tgColumnMetadata col in request.Columns) { if (col.IsComputed) { VistaDBParameter p = cmd.Parameters[Delimiters.Param + col.PropertyName]; p.Direction = ParameterDirection.InputOutput; } } return(cmd); }
internal override IDataParameter CreateIDataParameter(string name, object value) { VistaDBParameter p = new VistaDBParameter(); p.ParameterName = name; p.Value = value; return p; }
override internal IDataParameter CreateIDataParameter(string name, object value) { VistaDBParameter p = new VistaDBParameter(); p.ParameterName = name; p.Value = value; return(p); }
private void tabControl1_Selected(object sender, TabControlEventArgs e) { // Move the input focus to the query builder. // This will fire Leave event in the text box and update the query builder // with modified query text. queryBuilder1.Focus(); Application.DoEvents(); // Try to execute the query using current database connection: if (e.TabPage == tabPageData) { dataGridView1.DataSource = null; if (queryBuilder1.MetadataProvider != null && queryBuilder1.MetadataProvider.Connected) { VistaDBCommand command = (VistaDBCommand)queryBuilder1.MetadataProvider.Connection.CreateCommand(); command.CommandText = queryBuilder1.SQL; // handle the query parameters if (queryBuilder1.Parameters.Count > 0) { for (int i = 0; i < queryBuilder1.Parameters.Count; i++) { if (!command.Parameters.Contains(queryBuilder1.Parameters[i].FullName)) { VistaDBParameter parameter = new VistaDBParameter(); parameter.ParameterName = queryBuilder1.Parameters[i].FullName; parameter.DbType = queryBuilder1.Parameters[i].DataType; command.Parameters.Add(parameter); } } using (QueryParametersForm qpf = new QueryParametersForm(command)) { qpf.ShowDialog(); } } VistaDBDataAdapter adapter = new VistaDBDataAdapter(command); DataSet dataset = new DataSet(); try { adapter.Fill(dataset, "QueryResult"); dataGridView1.DataSource = dataset.Tables["QueryResult"]; } catch (Exception ex) { MessageBox.Show(ex.Message, "SQL query error"); } } } }
public override DbDataAdapter GetAdapter(string selectCommand, DbConnection connection, CommandParameterCollection parameters) { VistaDBDataAdapter adapter = new VistaDBDataAdapter(selectCommand, connection as VistaDBConnection); foreach (CommandParameter p in parameters) { VistaDBParameter parameter = adapter.SelectCommand.Parameters.Add(p.Name, (VistaDBType)p.DataType, p.Size); object value = p.Value; parameter.Value = value is Variant ? ((Variant)value).Value : value; } return(adapter); }
static public Dictionary<string, VistaDBParameter> GetParameters(Guid dataID, tgProviderSpecificMetadata providerMetadata, tgColumnMetadataCollection columns) { lock (parameterCache) { if (!parameterCache.ContainsKey(dataID)) { // The Parameters for this Table haven't been cached yet, this is a one time operation Dictionary<string, VistaDBParameter> types = new Dictionary<string, VistaDBParameter>(); VistaDBParameter param1; foreach (tgColumnMetadata col in columns) { tgTypeMap typeMap = providerMetadata.GetTypeMap(col.PropertyName); if (typeMap != null) { string nativeType = typeMap.NativeType; VistaDBType dbType = Cache.NativeTypeToDbType(nativeType); param1 = new VistaDBParameter(Delimiters.Param + col.PropertyName, dbType, 0, col.Name); param1.SourceColumn = col.Name; switch (dbType) { case VistaDBType.BigInt: case VistaDBType.Int: case VistaDBType.SmallInt: case VistaDBType.Decimal: case VistaDBType.Float: case VistaDBType.Money: case VistaDBType.SmallMoney: param1.Size = (int)col.CharacterMaxLength; break; } types[col.Name] = param1; } } parameterCache[dataID] = types; } } return parameterCache[dataID]; }
public static Dictionary<string, VistaDBParameter> GetParameters(Guid dataID, tgProviderSpecificMetadata providerMetadata, tgColumnMetadataCollection columns) { lock (parameterCache) { if (!parameterCache.ContainsKey(dataID)) { // The Parameters for this Table haven't been cached yet, this is a one time operation Dictionary<string, VistaDBParameter> types = new Dictionary<string, VistaDBParameter>(); VistaDBParameter param1; foreach (tgColumnMetadata col in columns) { tgTypeMap typeMap = providerMetadata.GetTypeMap(col.PropertyName); if (typeMap != null) { string nativeType = typeMap.NativeType; VistaDBType dbType = Cache.NativeTypeToDbType(nativeType); param1 = new VistaDBParameter(Delimiters.Param + col.PropertyName, dbType, 0, col.Name); param1.SourceColumn = col.Name; switch (dbType) { case VistaDBType.BigInt: case VistaDBType.Int: case VistaDBType.SmallInt: case VistaDBType.Decimal: case VistaDBType.Float: case VistaDBType.Money: case VistaDBType.SmallMoney: param1.Size = (int)col.CharacterMaxLength; break; } types[col.Name] = param1; } } parameterCache[dataID] = types; } } return parameterCache[dataID]; }
static public void GatherReturnParameters(VistaDBCommand cmd, tgDataRequest request, tgDataResponse response) { if (cmd.Parameters.Count > 0) { if (request.Parameters != null && request.Parameters.Count > 0) { response.Parameters = new tgParameters(); foreach (tgParameter esParam in request.Parameters) { if (esParam.Direction != tgParameterDirection.Input) { response.Parameters.Add(esParam); VistaDBParameter p = cmd.Parameters[Delimiters.Param + esParam.Name]; esParam.Value = p.Value; } } } } }
static private VistaDBParameter CloneParameter(VistaDBParameter p) { ICloneable param = p as ICloneable; return(param.Clone() as VistaDBParameter); }
internal override IDbCommand _LoadFromRawSql(string rawSql, params object[] parameters) { int i = 0; string token = ""; string sIndex = ""; string param = ""; VistaDBCommand cmd = new VistaDBCommand(); foreach(object o in parameters) { sIndex = i.ToString(); token = '{' + sIndex + '}'; param = "@p" + sIndex; rawSql = rawSql.Replace(token, param); VistaDBParameter p = new VistaDBParameter(param, o); cmd.Parameters.Add(p); i++; } cmd.CommandText = rawSql; return cmd; }
/// <summary> /// Create and prepare a VistaDBCommand, and call ExecuteReader with the appropriate CommandBehavior. /// </summary> /// <remarks> /// If we created and opened the connection, we want the connection to be closed when the DataReader is closed. /// /// If the caller provided the connection, we want to leave it to them to manage. /// </remarks> /// <param name="connection">A valid VistaDBConnection, on which to execute this command</param> /// <param name="transaction">A valid VistaDBTransaction, or 'null'</param> /// <param name="commandType">The CommandType (TableDirect, Text)</param> /// <param name="commandText">The T-SQL command</param> /// <param name="commandParameters">An array of VistaDBParameters to be associated with the command or 'null' if no parameters are required</param> /// <param name="connectionOwnership">Indicates whether the connection parameter was provided by the caller, or created by VistaDBHelper</param> /// <returns>VistaDBDataReader containing the results of the command</returns> private static VistaDBDataReader ExecuteReader(VistaDBConnection connection, VistaDBTransaction transaction, CommandType commandType, string commandText, VistaDBParameter[] commandParameters, VistaDBConnectionOwnership connectionOwnership) { if( connection == null ) throw new ArgumentNullException( "connection" ); bool mustCloseConnection = false; // Create a command and prepare it for execution VistaDBCommand cmd = new VistaDBCommand(); try { PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection ); // Create a reader VistaDBDataReader dataReader; // Call ExecuteReader with the appropriate CommandBehavior if (connectionOwnership == VistaDBConnectionOwnership.External) { dataReader = cmd.ExecuteReader(); } else { dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection); } // Detach the VistaDBParameters from the command object, so they can be used again. // HACK: There is a problem here, the output parameter values are fletched // when the reader is closed, so if the parameters are detached from the command // then the VistaDBReader can´t set its values. // When this happen, the parameters can´t be used again in other command. bool canClear = true; foreach(VistaDBParameter commandParameter in cmd.Parameters) { if (commandParameter.Direction != ParameterDirection.Input) canClear = false; } if (canClear) { cmd.Parameters.Clear(); } return dataReader; } catch { if( mustCloseConnection ) connection.Close(); throw; } }
static public VistaDBCommand BuildDynamicInsertCommand(tgDataRequest request, List <string> modifiedColumns) { string sql = String.Empty; string defaults = String.Empty; string into = String.Empty; string values = String.Empty; string comma = String.Empty; string defaultComma = String.Empty; string where = String.Empty; string whereComma = String.Empty; PropertyCollection props = new PropertyCollection(); VistaDBParameter p = null; Dictionary <string, VistaDBParameter> types = Cache.GetParameters(request); VistaDBCommand cmd = new VistaDBCommand(); if (request.CommandTimeout != null) { cmd.CommandTimeout = request.CommandTimeout.Value; } tgColumnMetadataCollection cols = request.Columns; foreach (tgColumnMetadata col in cols) { bool isModified = modifiedColumns == null ? false : modifiedColumns.Contains(col.Name); if (isModified && (!col.IsAutoIncrement && !col.IsConcurrency && !col.IsTiraggoConcurrency)) { p = types[col.Name]; cmd.Parameters.Add(CloneParameter(p)); into += comma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose; values += comma + p.ParameterName; comma = ", "; } else if (col.IsAutoIncrement) { props["AutoInc"] = col.Name; props["Source"] = request.ProviderMetadata.Source; p = CloneParameter(types[col.Name]); p.Direction = ParameterDirection.Output; cmd.Parameters.Add(p); } else if (col.IsConcurrency) { props["Timestamp"] = col.Name; props["Source"] = request.ProviderMetadata.Source; p = CloneParameter(types[col.Name]); p.Direction = ParameterDirection.Output; cmd.Parameters.Add(p); } else if (col.IsTiraggoConcurrency) { into += comma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose; values += comma + "1"; comma = ", "; p = CloneParameter(types[col.Name]); p.Direction = ParameterDirection.Output; p.Value = 1; // Seems to work, We'll take it ... cmd.Parameters.Add(p); } else if (col.IsComputed) { // Do nothing but leave this here } else if (cols.IsSpecialColumn(col)) { // Do nothing but leave this here } else if (col.HasDefault) { defaults += defaultComma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose; defaultComma = ","; string def = col.Default.ToLower(); if (def.Contains("guid") || def.Contains("newid")) { p = CloneParameter(types[col.Name]); p.Direction = ParameterDirection.Output; p.SourceVersion = DataRowVersion.Current; cmd.Parameters.Add(p); sql += " IF " + Delimiters.Param + col.Name + " IS NULL SET " + Delimiters.Param + col.Name + " = NEWID();"; into += comma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose; values += comma + p.ParameterName; comma = ", "; } } if (col.IsInPrimaryKey) { where += whereComma + col.Name; whereComma = ","; } } #region Special Columns if (cols.DateAdded != null && cols.DateAdded.IsServerSide) { into += comma + Delimiters.ColumnOpen + cols.DateAdded.ColumnName + Delimiters.ColumnClose; values += comma + request.ProviderMetadata["DateAdded.ServerSideText"]; comma = ", "; defaults += defaultComma + Delimiters.ColumnOpen + cols.DateAdded.ColumnName + Delimiters.ColumnClose; defaultComma = ","; } if (cols.DateModified != null && cols.DateModified.IsServerSide) { into += comma + Delimiters.ColumnOpen + cols.DateModified.ColumnName + Delimiters.ColumnClose; values += comma + request.ProviderMetadata["DateModified.ServerSideText"]; comma = ", "; defaults += defaultComma + Delimiters.ColumnOpen + cols.DateModified.ColumnName + Delimiters.ColumnClose; defaultComma = ","; } if (cols.AddedBy != null && cols.AddedBy.IsServerSide) { into += comma + Delimiters.ColumnOpen + cols.AddedBy.ColumnName + Delimiters.ColumnClose; values += comma + request.ProviderMetadata["AddedBy.ServerSideText"]; comma = ", "; defaults += defaultComma + Delimiters.ColumnOpen + cols.AddedBy.ColumnName + Delimiters.ColumnClose; defaultComma = ","; } if (cols.ModifiedBy != null && cols.ModifiedBy.IsServerSide) { into += comma + Delimiters.ColumnOpen + cols.ModifiedBy.ColumnName + Delimiters.ColumnClose; values += comma + request.ProviderMetadata["ModifiedBy.ServerSideText"]; comma = ", "; defaults += defaultComma + Delimiters.ColumnOpen + cols.ModifiedBy.ColumnName + Delimiters.ColumnClose; defaultComma = ","; } #endregion if (defaults.Length > 0) { comma = String.Empty; props["Defaults"] = defaults; props["Where"] = where; } sql += " INSERT INTO " + CreateFullName(request); if (into.Length != 0) { sql += "(" + into + ") VALUES (" + values + ")"; } else { sql += "DEFAULT VALUES"; } request.Properties = props; cmd.CommandText = sql; cmd.CommandType = CommandType.Text; return(cmd); }
static public VistaDBParameter CloneParameter(VistaDBParameter p) { ICloneable param = p as ICloneable; return param.Clone() as VistaDBParameter; }
/// <summary> /// Call the Stored Proc version to get the database version /// </summary> public static void CallGetDatabaseVersionProcedureSQL() { Console.WriteLine("Attempting to execute CLR Procedure GetVersionProcedure"); using (VistaDBConnection connection = new VistaDBConnection(SampleRunner.ConnectionString)) { connection.Open(); try { // Setup a command against the database like any other command, but then you have to change the command type // to tell it you are calling a stored proc directly using (VistaDBCommand command = new VistaDBCommand()) { // Use our connection from above command.Connection = connection; // Put the name of the stored proc, you don't need to EXEC. This command will be called directly // Be sure to include all the parameters command.CommandText = "GetVersionProcedure(@versionout);"; command.CommandType = System.Data.CommandType.StoredProcedure; // Normally this is just text that is being executed // Build up the parameter to the clr proc VistaDBParameter outparam = new VistaDBParameter(); // This name has to match the entry in the commandtext outparam.ParameterName = "@versionout"; // Telling it that this is an OUTPUT parameter // This is how you should always get values back from a stored proc. The return value in a stored proc is really only // meant to tell you the number of rows affected, not values. outparam.Direction = System.Data.ParameterDirection.Output; // Add it to the command command.Parameters.Add(outparam); // We are not expecting any return values, and the output parameters will still be filled out // using ExecuteNonQuery. This saves object setup and teardown of a reader when we don't need it. command.ExecuteNonQuery(); // Make sure the outparam is not null if (outparam.Value != null) { // Print it to the console Console.WriteLine(Convert.ToString(outparam.Value)); } } } catch (Exception e) { Console.WriteLine("Failed to execute CLR Function GetVersionProcedure, Reason: " + e.Message); } } }
protected static string GetComparisonStatement(StandardProviderParameters std, tgDynamicQuerySerializable query, List<tgComparison> items, string prefix) { string sql = String.Empty; string comma = String.Empty; IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal; //======================================= // WHERE //======================================= if (items != null) { sql += prefix; string compareTo = String.Empty; foreach (tgComparison comparisonItem in items) { tgComparison.tgComparisonData comparisonData = (tgComparison.tgComparisonData)comparisonItem; tgDynamicQuerySerializable subQuery = null; bool requiresParam = true; bool needsStringParameter = false; if (comparisonData.IsParenthesis) { if (comparisonData.Parenthesis == tgParenthesis.Open) sql += "("; else sql += ")"; continue; } if (comparisonData.IsConjunction) { switch (comparisonData.Conjunction) { case tgConjunction.And: sql += " AND "; break; case tgConjunction.Or: sql += " OR "; break; case tgConjunction.AndNot: sql += " AND NOT "; break; case tgConjunction.OrNot: sql += " OR NOT "; break; } continue; } Dictionary<string, VistaDBParameter> types = null; if (comparisonData.Column.Query != null) { IDynamicQuerySerializableInternal iLocalQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal; types = Cache.GetParameters(iLocalQuery.DataID, (tgProviderSpecificMetadata)iLocalQuery.ProviderMetadata, (tgColumnMetadataCollection)iLocalQuery.Columns); } if (comparisonData.IsLiteral) { sql += comparisonData.Column.Name.Substring(1, comparisonData.Column.Name.Length - 2); continue; } if (comparisonData.ComparisonColumn.Name == null) { subQuery = comparisonData.Value as tgDynamicQuerySerializable; if (subQuery == null) { if (comparisonData.Column.Name != null) { IDynamicQuerySerializableInternal iColQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal; tgColumnMetadataCollection columns = (tgColumnMetadataCollection)iColQuery.Columns; compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++std.pindex).ToString(); } else { compareTo = Delimiters.Param + "Expr" + (++std.pindex).ToString(); } } else { // It's a sub query compareTo = GetSubquerySearchCondition(subQuery) + " (" + BuildQuery(std, subQuery) + ") "; requiresParam = false; } } else { compareTo = GetColumnName(comparisonData.ComparisonColumn); requiresParam = false; } switch (comparisonData.Operand) { case tgComparisonOperand.Exists: sql += " EXISTS" + compareTo; break; case tgComparisonOperand.NotExists: sql += " NOT EXISTS" + compareTo; break; //----------------------------------------------------------- // Comparison operators, left side vs right side //----------------------------------------------------------- case tgComparisonOperand.Equal: if (comparisonData.ItemFirst) sql += ApplyWhereSubOperations(std, query, comparisonData) + " = " + compareTo; else sql += compareTo + " = " + ApplyWhereSubOperations(std, query, comparisonData); break; case tgComparisonOperand.NotEqual: if (comparisonData.ItemFirst) sql += ApplyWhereSubOperations(std, query, comparisonData) + " <> " + compareTo; else sql += compareTo + " <> " + ApplyWhereSubOperations(std, query, comparisonData); break; case tgComparisonOperand.GreaterThan: if (comparisonData.ItemFirst) sql += ApplyWhereSubOperations(std, query, comparisonData) + " > " + compareTo; else sql += compareTo + " > " + ApplyWhereSubOperations(std, query, comparisonData); break; case tgComparisonOperand.LessThan: if (comparisonData.ItemFirst) sql += ApplyWhereSubOperations(std, query, comparisonData) + " < " + compareTo; else sql += compareTo + " < " + ApplyWhereSubOperations(std, query, comparisonData); break; case tgComparisonOperand.LessThanOrEqual: if (comparisonData.ItemFirst) sql += ApplyWhereSubOperations(std, query, comparisonData) + " <= " + compareTo; else sql += compareTo + " <= " + ApplyWhereSubOperations(std, query, comparisonData); break; case tgComparisonOperand.GreaterThanOrEqual: if (comparisonData.ItemFirst) sql += ApplyWhereSubOperations(std, query, comparisonData) + " >= " + compareTo; else sql += compareTo + " >= " + ApplyWhereSubOperations(std, query, comparisonData); break; case tgComparisonOperand.Like: string esc = comparisonData.LikeEscape.ToString(); if (String.IsNullOrEmpty(esc) || esc == "\0") { sql += ApplyWhereSubOperations(std, query, comparisonData) + " LIKE " + compareTo; needsStringParameter = true; } else { sql += ApplyWhereSubOperations(std, query, comparisonData) + " LIKE " + compareTo; sql += " ESCAPE '" + esc + "'"; needsStringParameter = true; } break; case tgComparisonOperand.NotLike: esc = comparisonData.LikeEscape.ToString(); if (String.IsNullOrEmpty(esc) || esc == "\0") { sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT LIKE " + compareTo; needsStringParameter = true; } else { sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT LIKE " + compareTo; sql += " ESCAPE '" + esc + "'"; needsStringParameter = true; } break; case tgComparisonOperand.Contains: sql += " CONTAINS(" + GetColumnName(comparisonData.Column) + ", " + compareTo + ")"; needsStringParameter = true; break; case tgComparisonOperand.IsNull: sql += ApplyWhereSubOperations(std, query, comparisonData) + " IS NULL"; requiresParam = false; break; case tgComparisonOperand.IsNotNull: sql += ApplyWhereSubOperations(std, query, comparisonData) + " IS NOT NULL"; requiresParam = false; break; case tgComparisonOperand.In: case tgComparisonOperand.NotIn: { if (subQuery != null) { // They used a subquery for In or Not sql += ApplyWhereSubOperations(std, query, comparisonData); sql += (comparisonData.Operand == tgComparisonOperand.In) ? " IN" : " NOT IN"; sql += compareTo; } else { comma = String.Empty; if (comparisonData.Operand == tgComparisonOperand.In) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " IN ("; } else { sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT IN ("; } foreach (object oin in comparisonData.Values) { string str = oin as string; if (str != null) { // STRING sql += comma + Delimiters.StringOpen + str + Delimiters.StringClose; comma = ","; } else if (null != oin as System.Collections.IEnumerable) { // LIST OR COLLECTION OF SOME SORT System.Collections.IEnumerable enumer = oin as System.Collections.IEnumerable; if (enumer != null) { System.Collections.IEnumerator iter = enumer.GetEnumerator(); while (iter.MoveNext()) { object o = iter.Current; string soin = o as string; if (soin != null) sql += comma + Delimiters.StringOpen + soin + Delimiters.StringClose; else sql += comma + Convert.ToString(o); comma = ","; } } } else { // NON STRING OR LIST sql += comma + Convert.ToString(oin); comma = ","; } } sql += ")"; requiresParam = false; } } break; case tgComparisonOperand.Between: VistaDBCommand sqlCommand = std.cmd as VistaDBCommand; sql += ApplyWhereSubOperations(std, query, comparisonData) + " BETWEEN "; sql += compareTo; if (comparisonData.ComparisonColumn.Name == null) { sqlCommand.Parameters.Add(compareTo, comparisonData.BetweenBegin); } if (comparisonData.ComparisonColumn2.Name == null) { IDynamicQuerySerializableInternal iColQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal; tgColumnMetadataCollection columns = (tgColumnMetadataCollection)iColQuery.Columns; compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++std.pindex).ToString(); sql += " AND " + compareTo; sqlCommand.Parameters.AddWithValue(compareTo, comparisonData.BetweenEnd); } else { sql += " AND " + Delimiters.ColumnOpen + comparisonData.ComparisonColumn2 + Delimiters.ColumnClose; } requiresParam = false; break; } if (requiresParam) { VistaDBParameter p; if (comparisonData.Column.Name != null) { p = types[comparisonData.Column.Name]; p = Cache.CloneParameter(p); p.ParameterName = compareTo; p.Value = comparisonData.Value; if (needsStringParameter) { p.DbType = DbType.String; } } else { p = new VistaDBParameter(compareTo, comparisonData.Value); } std.cmd.Parameters.Add(p); } } } return sql; }
static public VistaDBCommand BuildDynamicUpdateCommand(tgDataRequest request, List <string> modifiedColumns) { string where = String.Empty; string scomma = String.Empty; string wcomma = String.Empty; string defaults = String.Empty; string defaultsWhere = String.Empty; string defaultsComma = String.Empty; string defaultsWhereComma = String.Empty; string sql = "UPDATE " + CreateFullName(request) + " SET "; PropertyCollection props = new PropertyCollection(); VistaDBParameter p = null; Dictionary <string, VistaDBParameter> types = Cache.GetParameters(request); VistaDBCommand cmd = new VistaDBCommand(); if (request.CommandTimeout != null) { cmd.CommandTimeout = request.CommandTimeout.Value; } tgColumnMetadataCollection cols = request.Columns; foreach (tgColumnMetadata col in cols) { bool isModified = modifiedColumns == null ? false : modifiedColumns.Contains(col.Name); if (isModified && (!col.IsAutoIncrement && !col.IsConcurrency && !col.IsTiraggoConcurrency)) { p = CloneParameter(types[col.Name]); cmd.Parameters.Add(p); sql += scomma; sql += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName; scomma = ", "; } else if (col.IsAutoIncrement) { // Nothing to do but leave this here } else if (col.IsConcurrency) { props["Timestamp"] = col.Name; props["Source"] = request.ProviderMetadata.Source; p = CloneParameter(types[col.Name]); p.SourceVersion = DataRowVersion.Original; p.Direction = ParameterDirection.InputOutput; cmd.Parameters.Add(p); where += wcomma; where += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName; wcomma = " AND "; } else if (col.IsTiraggoConcurrency) { props["EntitySpacesConcurrency"] = col.Name; p = CloneParameter(types[col.Name]); p.SourceVersion = DataRowVersion.Original; p.Direction = ParameterDirection.InputOutput; cmd.Parameters.Add(p); sql += scomma; sql += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " + 1"; scomma = ", "; where += wcomma; where += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName; wcomma = " AND "; } else if (col.IsComputed) { // Do nothing but leave this here } else if (cols.IsSpecialColumn(col)) { // Do nothing but leave this here } else if (col.HasDefault) { // defaults += defaultsComma + Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose; // defaultsComma = ","; } if (col.IsInPrimaryKey) { p = CloneParameter(types[col.Name]); p.SourceVersion = DataRowVersion.Original; cmd.Parameters.Add(p); where += wcomma; where += Delimiters.ColumnOpen + col.Name + Delimiters.ColumnClose + " = " + p.ParameterName; wcomma = " AND "; defaultsWhere += defaultsWhereComma + col.Name; defaultsWhereComma = ","; } } #region Special Columns if (cols.DateModified != null && cols.DateModified.IsServerSide) { sql += scomma; sql += Delimiters.ColumnOpen + cols.DateModified.ColumnName + Delimiters.ColumnClose + " = " + request.ProviderMetadata["DateModified.ServerSideText"]; scomma = ", "; defaults += defaultsComma + Delimiters.ColumnOpen + cols.DateModified.ColumnName + Delimiters.ColumnClose; defaultsComma = ","; } if (cols.ModifiedBy != null && cols.ModifiedBy.IsServerSide) { sql += scomma; sql += Delimiters.ColumnOpen + cols.ModifiedBy.ColumnName + Delimiters.ColumnClose + " = " + request.ProviderMetadata["ModifiedBy.ServerSideText"]; scomma = ", "; defaults += defaultsComma + Delimiters.ColumnOpen + cols.ModifiedBy.ColumnName + Delimiters.ColumnClose; defaultsComma = ","; } #endregion if (defaults.Length > 0) { props["Defaults"] = defaults; props["Where"] = defaultsWhere; } sql += " WHERE " + where + ";"; request.Properties = props; cmd.CommandText = sql; cmd.CommandType = CommandType.Text; return(cmd); }
protected static string GetComparisonStatement(StandardProviderParameters std, esDynamicQuerySerializable query, List <esComparison> items, string prefix) { string sql = String.Empty; string comma = String.Empty; IDynamicQuerySerializableInternal iQuery = query as IDynamicQuerySerializableInternal; //======================================= // WHERE //======================================= if (items != null) { sql += prefix; string compareTo = String.Empty; foreach (esComparison comparisonItem in items) { esComparison.esComparisonData comparisonData = (esComparison.esComparisonData)comparisonItem; esDynamicQuerySerializable subQuery = null; bool requiresParam = true; bool needsStringParameter = false; if (comparisonData.IsParenthesis) { if (comparisonData.Parenthesis == esParenthesis.Open) { sql += "("; } else { sql += ")"; } continue; } if (comparisonData.IsConjunction) { switch (comparisonData.Conjunction) { case esConjunction.And: sql += " AND "; break; case esConjunction.Or: sql += " OR "; break; case esConjunction.AndNot: sql += " AND NOT "; break; case esConjunction.OrNot: sql += " OR NOT "; break; } continue; } Dictionary <string, VistaDBParameter> types = null; if (comparisonData.Column.Query != null) { IDynamicQuerySerializableInternal iLocalQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal; types = Cache.GetParameters(iLocalQuery.DataID, (esProviderSpecificMetadata)iLocalQuery.ProviderMetadata, (esColumnMetadataCollection)iLocalQuery.Columns); } if (comparisonData.IsLiteral) { sql += comparisonData.Column.Name.Substring(1, comparisonData.Column.Name.Length - 2); continue; } if (comparisonData.ComparisonColumn.Name == null) { subQuery = comparisonData.Value as esDynamicQuerySerializable; if (subQuery == null) { if (comparisonData.Column.Name != null) { IDynamicQuerySerializableInternal iColQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal; esColumnMetadataCollection columns = (esColumnMetadataCollection)iColQuery.Columns; compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++std.pindex).ToString(); } else { compareTo = Delimiters.Param + "Expr" + (++std.pindex).ToString(); } } else { // It's a sub query compareTo = GetSubquerySearchCondition(subQuery) + " (" + BuildQuery(std, subQuery) + ") "; requiresParam = false; } } else { compareTo = GetColumnName(comparisonData.ComparisonColumn); requiresParam = false; } switch (comparisonData.Operand) { case esComparisonOperand.Exists: sql += " EXISTS" + compareTo; break; case esComparisonOperand.NotExists: sql += " NOT EXISTS" + compareTo; break; //----------------------------------------------------------- // Comparison operators, left side vs right side //----------------------------------------------------------- case esComparisonOperand.Equal: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " = " + compareTo; } else { sql += compareTo + " = " + ApplyWhereSubOperations(std, query, comparisonData); } break; case esComparisonOperand.NotEqual: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " <> " + compareTo; } else { sql += compareTo + " <> " + ApplyWhereSubOperations(std, query, comparisonData); } break; case esComparisonOperand.GreaterThan: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " > " + compareTo; } else { sql += compareTo + " > " + ApplyWhereSubOperations(std, query, comparisonData); } break; case esComparisonOperand.LessThan: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " < " + compareTo; } else { sql += compareTo + " < " + ApplyWhereSubOperations(std, query, comparisonData); } break; case esComparisonOperand.LessThanOrEqual: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " <= " + compareTo; } else { sql += compareTo + " <= " + ApplyWhereSubOperations(std, query, comparisonData); } break; case esComparisonOperand.GreaterThanOrEqual: if (comparisonData.ItemFirst) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " >= " + compareTo; } else { sql += compareTo + " >= " + ApplyWhereSubOperations(std, query, comparisonData); } break; case esComparisonOperand.Like: string esc = comparisonData.LikeEscape.ToString(); if (String.IsNullOrEmpty(esc) || esc == "\0") { sql += ApplyWhereSubOperations(std, query, comparisonData) + " LIKE " + compareTo; needsStringParameter = true; } else { sql += ApplyWhereSubOperations(std, query, comparisonData) + " LIKE " + compareTo; sql += " ESCAPE '" + esc + "'"; needsStringParameter = true; } break; case esComparisonOperand.NotLike: esc = comparisonData.LikeEscape.ToString(); if (String.IsNullOrEmpty(esc) || esc == "\0") { sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT LIKE " + compareTo; needsStringParameter = true; } else { sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT LIKE " + compareTo; sql += " ESCAPE '" + esc + "'"; needsStringParameter = true; } break; case esComparisonOperand.Contains: sql += " CONTAINS(" + GetColumnName(comparisonData.Column) + ", " + compareTo + ")"; needsStringParameter = true; break; case esComparisonOperand.IsNull: sql += ApplyWhereSubOperations(std, query, comparisonData) + " IS NULL"; requiresParam = false; break; case esComparisonOperand.IsNotNull: sql += ApplyWhereSubOperations(std, query, comparisonData) + " IS NOT NULL"; requiresParam = false; break; case esComparisonOperand.In: case esComparisonOperand.NotIn: { if (subQuery != null) { // They used a subquery for In or Not sql += ApplyWhereSubOperations(std, query, comparisonData); sql += (comparisonData.Operand == esComparisonOperand.In) ? " IN" : " NOT IN"; sql += compareTo; } else { comma = String.Empty; if (comparisonData.Operand == esComparisonOperand.In) { sql += ApplyWhereSubOperations(std, query, comparisonData) + " IN ("; } else { sql += ApplyWhereSubOperations(std, query, comparisonData) + " NOT IN ("; } foreach (object oin in comparisonData.Values) { string str = oin as string; if (str != null) { // STRING sql += comma + Delimiters.StringOpen + str + Delimiters.StringClose; comma = ","; } else if (null != oin as System.Collections.IEnumerable) { // LIST OR COLLECTION OF SOME SORT System.Collections.IEnumerable enumer = oin as System.Collections.IEnumerable; if (enumer != null) { System.Collections.IEnumerator iter = enumer.GetEnumerator(); while (iter.MoveNext()) { object o = iter.Current; string soin = o as string; if (soin != null) { sql += comma + Delimiters.StringOpen + soin + Delimiters.StringClose; } else { sql += comma + Convert.ToString(o); } comma = ","; } } } else { // NON STRING OR LIST sql += comma + Convert.ToString(oin); comma = ","; } } sql += ")"; requiresParam = false; } } break; case esComparisonOperand.Between: VistaDBCommand sqlCommand = std.cmd as VistaDBCommand; sql += ApplyWhereSubOperations(std, query, comparisonData) + " BETWEEN "; sql += compareTo; if (comparisonData.ComparisonColumn.Name == null) { sqlCommand.Parameters.Add(compareTo, comparisonData.BetweenBegin); } if (comparisonData.ComparisonColumn2.Name == null) { IDynamicQuerySerializableInternal iColQuery = comparisonData.Column.Query as IDynamicQuerySerializableInternal; esColumnMetadataCollection columns = (esColumnMetadataCollection)iColQuery.Columns; compareTo = Delimiters.Param + columns[comparisonData.Column.Name].PropertyName + (++std.pindex).ToString(); sql += " AND " + compareTo; sqlCommand.Parameters.AddWithValue(compareTo, comparisonData.BetweenEnd); } else { sql += " AND " + Delimiters.ColumnOpen + comparisonData.ComparisonColumn2 + Delimiters.ColumnClose; } requiresParam = false; break; } if (requiresParam) { VistaDBParameter p; if (comparisonData.Column.Name != null) { p = types[comparisonData.Column.Name]; p = Cache.CloneParameter(p); p.ParameterName = compareTo; p.Value = comparisonData.Value; if (needsStringParameter) { p.DbType = DbType.String; } } else { p = new VistaDBParameter(compareTo, comparisonData.Value); } std.cmd.Parameters.Add(p); } } } return(sql); }
protected static void OnRowUpdated(object sender, VistaDBRowUpdatedEventArgs e) { try { PropertyCollection props = e.Row.Table.ExtendedProperties; if (props.ContainsKey("props")) { props = (PropertyCollection)props["props"]; } if (e.Status == UpdateStatus.Continue && (e.StatementType == StatementType.Insert || e.StatementType == StatementType.Update)) { esDataRequest request = props["esDataRequest"] as esDataRequest; esEntitySavePacket packet = (esEntitySavePacket)props["esEntityData"]; string source = props["Source"] as string; if (e.StatementType == StatementType.Insert) { if (props.Contains("AutoInc")) { string autoInc = props["AutoInc"] as string; VistaDBCommand cmd = new VistaDBCommand(); cmd.Connection = e.Command.Connection; cmd.Transaction = e.Command.Transaction; cmd.CommandText = "SELECT LastIdentity([" + autoInc + "]) FROM [" + source + "]"; object o = null; o = cmd.ExecuteScalar(); if (o != null) { e.Row[autoInc] = o; e.Command.Parameters["@" + autoInc].Value = o; } } if (props.Contains("EntitySpacesConcurrency")) { string esConcurrencyColumn = props["EntitySpacesConcurrency"] as string; packet.CurrentValues[esConcurrencyColumn] = 1; } } if (props.Contains("Timestamp")) { string column = props["Timestamp"] as string; VistaDBCommand cmd = new VistaDBCommand(); cmd.Connection = e.Command.Connection; cmd.Transaction = e.Command.Transaction; cmd.CommandText = "SELECT LastTimestamp('" + source + "');"; object o = null; o = cmd.ExecuteScalar(); if (o != null) { e.Command.Parameters["@" + column].Value = o; } } //------------------------------------------------------------------------------------------------- // Fetch any defaults, SQLite doesn't support output parameters so we gotta do this the hard way //------------------------------------------------------------------------------------------------- if (props.Contains("Defaults")) { // Build the Where parameter and parameters VistaDBCommand cmd = new VistaDBCommand(); cmd.Connection = e.Command.Connection; cmd.Transaction = e.Command.Transaction; string select = (string)props["Defaults"]; string[] whereParameters = ((string)props["Where"]).Split(','); string comma = String.Empty; string where = String.Empty; int i = 1; foreach (string parameter in whereParameters) { VistaDBParameter p = new VistaDBParameter("@p" + i++.ToString(), e.Row[parameter]); cmd.Parameters.Add(p); where += comma + "[" + parameter + "]=" + p.ParameterName; comma = " AND "; } // Okay, now we can execute the sql and get any values that have defaults that were // null at the time of the insert and/or our timestamp cmd.CommandText = "SELECT " + select + " FROM [" + request.ProviderMetadata.Source + "] WHERE " + where + ";"; VistaDBDataReader rdr = null; try { rdr = cmd.ExecuteReader(CommandBehavior.SingleResult); if (rdr.Read()) { select = select.Replace("[", String.Empty).Replace("]", String.Empty); string[] selectCols = select.Split(','); for (int k = 0; k < selectCols.Length; k++) { packet.CurrentValues[selectCols[k]] = rdr.GetValue(k); } } } finally { // Make sure we close the reader no matter what if (rdr != null) { rdr.Close(); } } } if (e.StatementType == StatementType.Update) { string colName = props["EntitySpacesConcurrency"] as string; object o = e.Row[colName]; VistaDBParameter p = e.Command.Parameters["@" + colName]; object v = null; switch (Type.GetTypeCode(o.GetType())) { case TypeCode.Int16: v = ((System.Int16)o) + 1; break; case TypeCode.Int32: v = ((System.Int32)o) + 1; break; case TypeCode.Int64: v = ((System.Int64)o) + 1; break; case TypeCode.UInt16: v = ((System.UInt16)o) + 1; break; case TypeCode.UInt32: v = ((System.UInt32)o) + 1; break; case TypeCode.UInt64: v = ((System.UInt64)o) + 1; break; } p.Value = v; } } } catch { } }
/// <summary> /// This method is used to attach array of VistaDBParameters to a VistaDBCommand. /// /// This method will assign a value of DbNull to any parameter with a direction of /// InputOutput and a value of null. /// /// This behavior will prevent default values from being used, but /// this will be the less common case than an intended pure output parameter (derived as InputOutput) /// where the user provided no input value. /// </summary> /// <param name="command">The command to which the parameters will be added</param> /// <param name="commandParameters">An array of VistaDBParameters to be added to command</param> private static void AttachParameters(VistaDBCommand command, VistaDBParameter[] commandParameters) { if( command == null ) throw new ArgumentNullException( "command" ); if( commandParameters != null ) { foreach (VistaDBParameter p in commandParameters) { if( p != null ) { // Check for derived output value with no value assigned if ( ( p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input ) && (p.Value == null)) { p.Value = DBNull.Value; } command.Parameters.Add(p); } } } }
public static VistaDBParameter CloneParameter(VistaDBParameter p) { ICloneable param = p as ICloneable; return param.Clone() as VistaDBParameter; }
static private VistaDBParameter CloneParameter(VistaDBParameter p) { ICloneable param = p as ICloneable; return param.Clone() as VistaDBParameter; }
protected static void OnRowUpdated(object sender, VistaDBRowUpdatedEventArgs e) { try { PropertyCollection props = e.Row.Table.ExtendedProperties; if (props.ContainsKey("props")) { props = (PropertyCollection)props["props"]; } if (e.Status == UpdateStatus.Continue && (e.StatementType == StatementType.Insert || e.StatementType == StatementType.Update)) { tgDataRequest request = props["tgDataRequest"] as tgDataRequest; tgEntitySavePacket packet = (tgEntitySavePacket)props["esEntityData"]; string source = props["Source"] as string; if (e.StatementType == StatementType.Insert) { if (props.Contains("AutoInc")) { string autoInc = props["AutoInc"] as string; VistaDBCommand cmd = new VistaDBCommand(); cmd.Connection = e.Command.Connection; cmd.Transaction = e.Command.Transaction; cmd.CommandText = "SELECT LastIdentity([" + autoInc + "]) FROM [" + source + "]"; object o = null; #region Profiling if (sTraceHandler != null) { using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "OnRowUpdated", System.Environment.StackTrace)) { try { o = cmd.ExecuteScalar(); } catch (Exception ex) { esTrace.Exception = ex.Message; throw; } } } else #endregion Profiling { o = cmd.ExecuteScalar(); } if (o != null) { e.Row[autoInc] = o; e.Command.Parameters["@" + autoInc].Value = o; } } if (props.Contains("EntitySpacesConcurrency")) { string esConcurrencyColumn = props["EntitySpacesConcurrency"] as string; packet.CurrentValues[esConcurrencyColumn] = 1; } } if (props.Contains("Timestamp")) { string column = props["Timestamp"] as string; VistaDBCommand cmd = new VistaDBCommand(); cmd.Connection = e.Command.Connection; cmd.Transaction = e.Command.Transaction; cmd.CommandText = "SELECT LastTimestamp('" + source + "');"; object o = null; #region Profiling if (sTraceHandler != null) { using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "OnRowUpdated", System.Environment.StackTrace)) { try { o = cmd.ExecuteScalar(); } catch (Exception ex) { esTrace.Exception = ex.Message; throw; } } } else #endregion Profiling { o = cmd.ExecuteScalar(); } if (o != null) { e.Command.Parameters["@" + column].Value = o; } } //------------------------------------------------------------------------------------------------- // Fetch any defaults, SQLite doesn't support output parameters so we gotta do this the hard way //------------------------------------------------------------------------------------------------- if (props.Contains("Defaults")) { // Build the Where parameter and parameters VistaDBCommand cmd = new VistaDBCommand(); cmd.Connection = e.Command.Connection; cmd.Transaction = e.Command.Transaction; string select = (string)props["Defaults"]; string[] whereParameters = ((string)props["Where"]).Split(','); string comma = String.Empty; string where = String.Empty; int i = 1; foreach (string parameter in whereParameters) { VistaDBParameter p = new VistaDBParameter("@p" + i++.ToString(), e.Row[parameter]); cmd.Parameters.Add(p); where += comma + "[" + parameter + "]=" + p.ParameterName; comma = " AND "; } // Okay, now we can execute the sql and get any values that have defaults that were // null at the time of the insert and/or our timestamp cmd.CommandText = "SELECT " + select + " FROM [" + request.ProviderMetadata.Source + "] WHERE " + where + ";"; VistaDBDataReader rdr = null; try { #region Profiling if (sTraceHandler != null) { using (esTraceArguments esTrace = new esTraceArguments(request, cmd, "OnRowUpdated", System.Environment.StackTrace)) { try { rdr = cmd.ExecuteReader(CommandBehavior.SingleResult); } catch (Exception ex) { esTrace.Exception = ex.Message; throw; } } } else #endregion Profiling { rdr = cmd.ExecuteReader(CommandBehavior.SingleResult); } if (rdr.Read()) { select = select.Replace("[", String.Empty).Replace("]", String.Empty); string[] selectCols = select.Split(','); for (int k = 0; k < selectCols.Length; k++) { packet.CurrentValues[selectCols[k]] = rdr.GetValue(k); } } } finally { // Make sure we close the reader no matter what if (rdr != null) rdr.Close(); } } if (e.StatementType == StatementType.Update) { string colName = props["EntitySpacesConcurrency"] as string; object o = e.Row[colName]; VistaDBParameter p = e.Command.Parameters["@" + colName]; object v = null; switch (Type.GetTypeCode(o.GetType())) { case TypeCode.Int16: v = ((System.Int16)o) + 1; break; case TypeCode.Int32: v = ((System.Int32)o) + 1; break; case TypeCode.Int64: v = ((System.Int64)o) + 1; break; case TypeCode.UInt16: v = ((System.UInt16)o) + 1; break; case TypeCode.UInt32: v = ((System.UInt32)o) + 1; break; case TypeCode.UInt64: v = ((System.UInt64)o) + 1; break; } p.Value = v; } } } catch { } }
/// <summary> /// This method opens (if necessary) and assigns a connection, transaction, command type and parameters /// to the provided command /// </summary> /// <param name="command">The VistaDBCommand to be prepared</param> /// <param name="connection">A valid VistaDBConnection, on which to execute this command</param> /// <param name="transaction">A valid VistaDBTransaction, or 'null'</param> /// <param name="commandType">The CommandType (TableDirect, Text)</param> /// <param name="commandText">The T-SQL command</param> /// <param name="commandParameters">An array of VistaDBParameters to be associated with the command or 'null' if no parameters are required</param> /// <param name="mustCloseConnection"><c>true</c> if the connection was opened by the method, otherwose is false.</param> private static void PrepareCommand(VistaDBCommand command, VistaDBConnection connection, VistaDBTransaction transaction, CommandType commandType, string commandText, VistaDBParameter[] commandParameters, out bool mustCloseConnection ) { if( command == null ) throw new ArgumentNullException( "command" ); if(commandType == CommandType.StoredProcedure ) throw new ArgumentException("Stored Procedures are not supported."); // If the provided connection is not open, we will open it if (connection.State != ConnectionState.Open) { mustCloseConnection = true; connection.Open(); } else { mustCloseConnection = false; } // Associate the connection with the command command.Connection = connection; // Set the command text (SQL statement) command.CommandText = commandText; // If we were provided a transaction, assign it if (transaction != null) { if( transaction.Connection == null ) throw new ArgumentException( "The transaction was rollbacked or commited, please provide an open transaction.", "transaction" ); command.Transaction = transaction; } // Set the command type command.CommandType = commandType; // Attach the command parameters if they are provided if (commandParameters != null) { AttachParameters(command, commandParameters); } return; }