public IEnumerable <long> GetQualified(long qualifier) { List <long> qualifiedRelations = new List <long>(); using (VistaDBConnection conn = new VistaDBConnection(this.db)) { VistaDBCommand cmd; // get qualified root relations cmd = new VistaDBCommand("select id from RootRelations where qualifierId=@qualifierId", conn); cmd.Parameters.AddWithValue("@qualifierId", qualifier); using (VistaDBDataReader rd = cmd.ExecuteReader()) { while (rd.Read()) { qualifiedRelations.Add(rd.GetInt64(0)); } } // get qualified inner relations cmd.CommandText = "select id from InnerRelations where qualifierId=@qualifierId"; using (VistaDBDataReader rd = cmd.ExecuteReader()) { while (rd.Read()) { qualifiedRelations.Add(rd.GetInt64(0)); } } } return(qualifiedRelations); }
public IEnumerable <long> GetChildren(long parent, ParentModes mode, long qualifier) { List <long> children = new List <long>(); using (VistaDBConnection conn = new VistaDBConnection(this.db)) { VistaDBCommand cmd; cmd = new VistaDBCommand("select id from InnerRelations where nParentId=@parentId", conn); cmd.Parameters.AddWithValue("@parentId", parent); if (qualifier != 0) { cmd.CommandText += " and qualifierId=@qualifierId"; cmd.Parameters.AddWithValue("@qualifierId", qualifier); } if (mode == ParentModes.normative || mode == ParentModes.both) { using (VistaDBDataReader rd = cmd.ExecuteReader()) { while (rd.Read()) { children.Add(rd.GetInt64(0)); } } } if (mode == ParentModes.associative || mode == ParentModes.both) { cmd.CommandText = cmd.CommandText.Replace("nParentId", "aParentId"); using (VistaDBDataReader rd = cmd.ExecuteReader()) { while (rd.Read()) { children.Add(rd.GetInt64(0)); } } } } return(children); }
public void ParseVistaDB(string vistaTableName, string qualifiedTableName, DataTable dataTable) { Program objProgram = new Program(); //Create a connection to VistaDB string connectionString = @"Data Source=C:\superpay\Superpay4.vdb6;Open Mode=SingleProcessReadWrite"; //Create string connection to Oracle string conString = "User Id=dwrrhh; password=milenaok;" + //How to connect to an Oracle DB without SQL*Net configuration file //also known as tnsnames.ora. "Data Source=cocgsa016.cupagroup.com:1521/CUPIRE; Pooling=false;"; try { //Read data from VistaDb and create variable with Values using (VistaDBConnection dbConn = new VistaDBConnection(connectionString)) { dbConn.Open(); VistaDBCommand command = dbConn.CreateCommand(); command.CommandText = "SELECT * FROM " + vistaTableName; using (VistaDBDataReader dr = command.ExecuteReader()) { int count = 0; while (dr.Read()) { count++; DataRow dataRow = dataTable.NewRow(); for (int i = 0; dataTable.Columns.Count > i; i++) { string colName = dataTable.Columns[i].ColumnName.ToString(); int colLength = dr[colName].ToString().Length; if (colLength == 0) { dataRow[colName] = 0.0; } else { string s1 = dr[colName].ToString(); string s2 = s1.Replace(",", "."); dataRow[colName] = s2; } //Console.WriteLine("Columna Nº: " + count + "-- Nombre COL: " + colName + " -- Valor: " + dataRow[colName]); } dataTable.Rows.Add(dataRow); } // Send parse data to Oracle objProgram.WriteToOracle(qualifiedTableName, conString, dataTable); } // Just a little pause for viewing //Console.ReadLine(); } } catch (Exception e) { Console.WriteLine("Error encountered during parsed data from VistaDB table name " + vistaTableName + " con error " + e.Message); throw; } finally { Console.WriteLine("Parse data from Table " + vistaTableName + " of VistaDb was successfully"); } }
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> /// Returns the specified error from the database, or null /// if it does not exist. /// </summary> public override ErrorLogEntry GetError(string id) { if (id == null) { throw new ArgumentNullException("id"); } if (id.Length == 0) { throw new ArgumentException(null, "id"); } int errorId; try { errorId = int.Parse(id, CultureInfo.InvariantCulture); } catch (FormatException e) { throw new ArgumentException(e.Message, "id", e); } catch (OverflowException e) { throw new ArgumentException(e.Message, "id", e); } string errorXml; using (VistaDBConnection connection = new VistaDBConnection(this.ConnectionString)) using (VistaDBCommand command = connection.CreateCommand()) { command.CommandText = @"SELECT AllXml FROM ELMAH_Error WHERE ErrorId = @ErrorId"; command.CommandType = CommandType.Text; VistaDBParameterCollection parameters = command.Parameters; parameters.Add("@ErrorId", VistaDBType.Int).Value = errorId; connection.Open(); // NB this has been deliberately done like this as command.ExecuteScalar // is not exhibiting the expected behaviour in VistaDB at the moment using (VistaDBDataReader dr = command.ExecuteReader()) { if (dr.Read()) { errorXml = dr[0] as string; } else { errorXml = null; } } } if (errorXml == null) { return(null); } Error error = ErrorXml.DecodeString(errorXml); return(new ErrorLogEntry(this, id, error)); }