/// <summary> /// Overloaded. Adds a VistaDBParameter to the VistaDBParameterCollection. /// </summary> /// <param name="value">The VistaDBParameter to add to the collection. </param> /// <returns>VistaDBParameter</returns> public VistaDBParameter Add(VistaDBParameter value) { if (value.ParameterName != null) { Add((object)value); return((VistaDBParameter)items[items.Count - 1]); } else { throw new ArgumentException("Parameter must be named"); } }
private void RefreshSchema() { string commandText, tableName; int pos, i, k, textLen; string text, primaryKey, s, s2; VistaDBType fieldType; VistaDBParameter parameter; VistaDBDataReader reader; bool needClose; string columnName; if (dataAdapter.SelectCommand == null) { return; } //'Getting table name commandText = dataAdapter.SelectCommand.CommandText.ToUpper(); tableName = ""; pos = commandText.IndexOf("FROM"); if (pos > 0) { pos += 5; textLen = commandText.Length; for (i = pos; i < textLen; i++) { if (commandText[i] != ' ') { for (k = i; k < textLen; k++) { if (commandText[k] == ' ' || commandText[k] == ';') { break; } } tableName = commandText.Substring(i, k - i); break; } } if (tableName == "") { return; } } else { return; } //Create data reader if (dataAdapter.SelectCommand.Connection.State != ConnectionState.Open) { needClose = true; dataAdapter.SelectCommand.Connection.Open(); } else { needClose = false; } try { reader = dataAdapter.SelectCommand.ExecuteReader(CommandBehavior.KeyInfo); try { //Creating where expression primaryKey = ""; for (i = 0; i < reader.Columns.Count; i++) { if (reader.Columns[i].Unique) { if (primaryKey != "") { primaryKey += " and "; } columnName = reader.Columns[i].Name; if (reader.Columns[i].ReservedWord) { columnName = "[" + columnName + "]"; } primaryKey += columnName + " = @" + reader.Columns[i].Name; } } if (primaryKey == "") { throw new VistaDBException(VistaDBErrorCodes.TableDoesNotHaveUniqueColumn); } //Creation of VistaDBCommand's /////////////////////////////////////////////////////// ///////////////////////Update Command////////////////// /////////////////////////////////////////////////////// text = "update " + tableName + " set "; //Create set expression s = ""; for (i = 0; i < reader.Columns.Count; i++) { fieldType = reader.Columns[i].VistaDBType; if (!reader.Columns[i].Unique && !reader.Columns[i].Identity) { columnName = reader.Columns[i].Name; if (reader.Columns[i].ReservedWord) { columnName = "[" + columnName + "]"; } text = text + s + columnName + " = @" + reader.Columns[i].Name; s = ","; } } //Create where expression text = text + " where " + primaryKey; updateCommand = new VistaDBCommand(text); updateCommand.Connection = dataAdapter.SelectCommand.Connection; updateCommand.CommandTimeout = dataAdapter.SelectCommand.CommandTimeout; updateCommand.Transaction = dataAdapter.SelectCommand.Transaction; //Create parameters for (i = 0; i < reader.Columns.Count; i++) { fieldType = reader.Columns[i].VistaDBType; parameter = new VistaDBParameter("@" + reader.Columns[i].Name, fieldType, reader.Columns[i].Name); parameter.SourceVersion = DataRowVersion.Current; updateCommand.Parameters.Add(parameter); } ///////////////////////////////////////////////////////// //////////////////////Insert(Command)//////////////////// ///////////////////////////////////////////////////////// text = "insert into " + tableName + "("; //Create into and values expression s = ""; s2 = " VALUES ("; for (i = 0; i < reader.Columns.Count; i++) { fieldType = reader.Columns[i].VistaDBType; if (!reader.Columns[i].Identity) { columnName = reader.Columns[i].Name; if (reader.Columns[i].ReservedWord) { columnName = "[" + columnName + "]"; } text = text + s + columnName; s2 = s2 + s + " @" + reader.Columns[i].Name; s = ","; } } text = text + ")" + s2 + ")"; insertCommand = new VistaDBCommand(text, (VistaDBConnection)(dataAdapter.SelectCommand.Connection)); insertCommand.Connection = dataAdapter.SelectCommand.Connection; insertCommand.CommandTimeout = dataAdapter.SelectCommand.CommandTimeout; insertCommand.Transaction = dataAdapter.SelectCommand.Transaction; //'CREATE(PARAMETERS) for (i = 0; i < reader.Columns.Count; i++) { fieldType = reader.Columns[i].VistaDBType; if (!reader.Columns[i].Identity) { parameter = new VistaDBParameter("@" + reader.Columns[i].Name, fieldType, reader.Columns[i].Name); parameter.SourceVersion = DataRowVersion.Current; insertCommand.Parameters.Add(parameter); } } // Delete Command text = "delete from " + tableName + " where " + primaryKey; deleteCommand = new VistaDBCommand(text); // Create parameters for (i = 0; i < reader.Columns.Count; i++) { if (reader.Columns[i].Unique) { fieldType = reader.Columns[i].VistaDBType; parameter = new VistaDBParameter("@" + reader.Columns[i].Name, fieldType, reader.Columns[i].Name); parameter.SourceVersion = DataRowVersion.Original; deleteCommand.Parameters.Add(parameter); } } deleteCommand.Connection = dataAdapter.SelectCommand.Connection; deleteCommand.CommandTimeout = dataAdapter.SelectCommand.CommandTimeout; deleteCommand.Transaction = dataAdapter.SelectCommand.Transaction; } finally { reader.Close(); } } finally { if (needClose) { dataAdapter.SelectCommand.Connection.Close(); } } }
/// <summary> /// Overloaded. Adds a VistaDBParameter to the VistaDBParameterCollection. /// </summary> /// <param name="value">The VistaDBParameter to add to the collection. </param> /// <returns>VistaDBParameter</returns> public VistaDBParameter Add(VistaDBParameter value) { if(value.ParameterName != null) { Add((object)value); return (VistaDBParameter)items[items.Count - 1]; } else throw new ArgumentException("Parameter must be named"); }
private void RefreshSchema() { string commandText, tableName; int pos, i, k, textLen; string text, primaryKey, s, s2; VistaDBType fieldType; VistaDBParameter parameter; VistaDBDataReader reader; bool needClose; string columnName; if (dataAdapter.SelectCommand == null) return; //'Getting table name commandText = dataAdapter.SelectCommand.CommandText.ToUpper(); tableName = ""; pos = commandText.IndexOf("FROM"); if (pos > 0) { pos += 5; textLen = commandText.Length; for(i = pos; i < textLen; i++) { if(commandText[i] != ' ') { for(k = i; k < textLen; k++) { if(commandText[k] == ' ' || commandText[k] == ';') break; } tableName = commandText.Substring(i, k - i); break; } } if(tableName == "") return; } else return; //Create data reader if( dataAdapter.SelectCommand.Connection.State != ConnectionState.Open ) { needClose = true; dataAdapter.SelectCommand.Connection.Open(); } else needClose = false; try { reader = dataAdapter.SelectCommand.ExecuteReader(CommandBehavior.KeyInfo); try { //Creating where expression primaryKey = ""; for(i = 0; i < reader.Columns.Count; i++) { if( reader.Columns[i].Unique ) { if(primaryKey != "") primaryKey += " and "; columnName = reader.Columns[i].Name; if(reader.Columns[i].ReservedWord) columnName = "[" + columnName + "]"; primaryKey += columnName + " = @" + reader.Columns[i].Name; } } if(primaryKey == "") { throw new VistaDBException(VistaDBErrorCodes.TableDoesNotHaveUniqueColumn); } //Creation of VistaDBCommand's /////////////////////////////////////////////////////// ///////////////////////Update Command////////////////// /////////////////////////////////////////////////////// text = "update " + tableName + " set "; //Create set expression s = ""; for(i = 0; i < reader.Columns.Count; i++) { fieldType = reader.Columns[i].VistaDBType; if(!reader.Columns[i].Unique && !reader.Columns[i].Identity) { columnName = reader.Columns[i].Name; if(reader.Columns[i].ReservedWord) columnName = "[" + columnName + "]"; text = text + s + columnName + " = @" + reader.Columns[i].Name; s = ","; } } //Create where expression text = text + " where " + primaryKey; updateCommand = new VistaDBCommand(text); updateCommand.Connection = dataAdapter.SelectCommand.Connection; updateCommand.CommandTimeout = dataAdapter.SelectCommand.CommandTimeout; updateCommand.Transaction = dataAdapter.SelectCommand.Transaction; //Create parameters for(i = 0; i < reader.Columns.Count; i++) { fieldType = reader.Columns[i].VistaDBType; parameter = new VistaDBParameter("@" + reader.Columns[i].Name, fieldType, reader.Columns[i].Name); parameter.SourceVersion = DataRowVersion.Current; updateCommand.Parameters.Add(parameter); } ///////////////////////////////////////////////////////// //////////////////////Insert(Command)//////////////////// ///////////////////////////////////////////////////////// text = "insert into " + tableName + "("; //Create into and values expression s = ""; s2 = " VALUES ("; for(i = 0; i < reader.Columns.Count; i++) { fieldType = reader.Columns[i].VistaDBType; if (!reader.Columns[i].Identity) { columnName = reader.Columns[i].Name; if(reader.Columns[i].ReservedWord) columnName = "[" + columnName + "]"; text = text + s + columnName; s2 = s2 + s + " @" + reader.Columns[i].Name; s = ","; } } text = text + ")" + s2 + ")"; insertCommand = new VistaDBCommand(text, (VistaDBConnection)(dataAdapter.SelectCommand.Connection)); insertCommand.Connection = dataAdapter.SelectCommand.Connection; insertCommand.CommandTimeout = dataAdapter.SelectCommand.CommandTimeout; insertCommand.Transaction = dataAdapter.SelectCommand.Transaction; //'CREATE(PARAMETERS) for(i = 0; i < reader.Columns.Count; i++) { fieldType = reader.Columns[i].VistaDBType; if(!reader.Columns[i].Identity) { parameter = new VistaDBParameter("@" + reader.Columns[i].Name, fieldType, reader.Columns[i].Name); parameter.SourceVersion = DataRowVersion.Current; insertCommand.Parameters.Add(parameter); } } // Delete Command text = "delete from " + tableName + " where " + primaryKey; deleteCommand = new VistaDBCommand(text); // Create parameters for(i = 0; i < reader.Columns.Count; i++) { if( reader.Columns[i].Unique ) { fieldType = reader.Columns[i].VistaDBType; parameter = new VistaDBParameter("@" + reader.Columns[i].Name, fieldType, reader.Columns[i].Name); parameter.SourceVersion = DataRowVersion.Original; deleteCommand.Parameters.Add(parameter); } } deleteCommand.Connection = dataAdapter.SelectCommand.Connection; deleteCommand.CommandTimeout = dataAdapter.SelectCommand.CommandTimeout; deleteCommand.Transaction = dataAdapter.SelectCommand.Transaction; } finally { reader.Close(); } } finally { if( needClose ) dataAdapter.SelectCommand.Connection.Close(); } }