/// <summary>
/// Executes a query inside the database and returns a <see cref="QueryResult"/> object.
/// </summary>
/// <param name="query">The full query to execute.</param>
/// <returns>Detailed info about the executed query.</returns>
public async Task <QueryResult> ExecuteQuery(string query)
{
//Throws an exception if the object is already disposed
if (IsDisposed)
{
throw new InvalidOperationException("Connection is closed.");
}
//Creates the command object which holds the query
using var command = new OleDbCommand(query, _connection);
//Tries to execute the query and save its info
DbDataReader reader = null;
QueryResult res;
try
{
reader = await command.ExecuteReaderAsync();
res = new QueryResult(query, reader);
}
finally
{
//Disposes the used objects
reader?.Close();
command.Dispose();
}
return(res);
}
public List <Book> GetListBooksWithStudents()
{
List <Book> newList = new List <Book>();
DbCommand command = _factory.CreateCommand();
command.Connection = _connection;
command.CommandText =
"SELECT Books.Id, Books.Name, Books.Pages, Books.YearPress, Themes.Name as 'Theme', Categories.Name as 'Category', Authors.LastName, Authors.FirstName, Press.Name as 'Press', Books.Comment, Books.Quantity FROM Books JOIN Themes ON Id_Themes = Themes.Id JOIN Categories ON Id_Category = Categories.Id JOIN Authors ON Id_Author = Authors.Id JOIN Press ON Id_Press = Press.Id";
DbDataReader reader = null;
try
{
reader = command.ExecuteReader();
while (reader.Read())
{
newList.Add(new Book((int)reader["Id"], (String)reader["Name"], (String)reader["Press"],
(int)reader["YearPress"], (String)reader["Category"], (String)reader["Theme"],
(String)reader["LastName"], (String)reader["FirstName"], (int)reader["Quantity"]));
}
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
finally
{
reader?.Close();
}
LoadStudents(newList);
return(newList);
}
public List <Model.Order> GetByCode(string code)
{
DbDataReader reader = null;
try
{
var helper = Database.GetDbHelper();
reader = helper.ExecuteSPReader(_storedProcedure_GetByCode,
helper.CreateInputParam("@Code", code));
List <Model.Order> result = new List <Model.Order>();
while (reader.Read())
{
result.Add(CreateOrder(reader));
}
return(result);
}
catch (Exception ex)
{
Trace.WriteError("({0})", Trace.GetMethodName(), CLASSNAME, ex, code);
throw DbHelper.TranslateException(ex);
}
finally
{
reader?.Close();
}
}
/// <summary>
/// 异步执行一条查询语句。
/// </summary>
/// <typeparam name="T">参数类型</typeparam>
/// <param name="sql">SQL 语句</param>
/// <param name="parameters">参数</param>
/// <param name="asyncCallback">回调函数</param>
/// <param name="dbTransaction">事务</param>
/// <param name="commandTimeout">超时时间（秒）</param>
/// <param name="commandType">命令类型</param>
public void ExecuteReaderAsync <T>(string sql, Action <Exception, DbDataReader> asyncCallback, T parameters, DbTransaction dbTransaction = null, int commandTimeout = CommandTimeout, CommandType commandType = CommandType.Text)
{
StartTask(() =>
{
DbCommand dbCommand = null;
Exception exception = null;
DbDataReader dbDataReader = null;
try
{
dbCommand = CreateCommand(sql, parameters, dbTransaction, commandTimeout, commandType);
dbDataReader = dbCommand.ExecuteReader();
throw new Exception();
}
catch (Exception e)
{
exception = e;
}
finally
{
asyncCallback(exception, dbDataReader);
dbDataReader?.Close();
if (dbTransaction == null)
{
dbCommand?.Dispose();
dbCommand?.Connection.Close();
}
}
});
}
private void LoadStudents(List <Book> newList)
{
DbCommand command1 = _factory.CreateCommand();
command1.Connection = _connection;
foreach (var book in newList)
{
command1.CommandText =
$"SELECT Students.Id, Students.FirstName, Students.LastName FROM Students JOIN S_Cards ON Students.Id = S_Cards.Id_Student JOIN Books ON Books.Id = S_Cards.Id_Book WHERE Books.Id = {book.Id}";
DbDataReader reader2 = null;
try
{
reader2 = command1.ExecuteReader();
while (reader2.Read())
{
book.Students.Add(new Student((String)reader2["FirstName"],
(String)reader2["LastName"]));
}
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
finally
{
reader2?.Close();
}
}
}
/// <summary>
/// Get all HistoryAction records from the database
/// </summary>
public virtual List <Model.HistoryAction> GetAll()
{
DbDataReader reader = null;
try
{
var helper = Database.GetDbHelper();
reader = helper.ExecuteSPReader(_storedProcedure_GetAll);
List <Model.HistoryAction> result = new List <Model.HistoryAction>();
while (reader.Read())
{
result.Add(CreateHistoryAction(reader));
}
return(result);
}
catch (Exception ex)
{
Trace.WriteError("()", "GetAll", CLASSNAME, ex);
throw DbHelper.TranslateException(ex);
}
finally
{
reader?.Close();
}
}
/// <summary>
/// Get a HistoryAction by id from the database
/// </summary>
public virtual Model.HistoryAction GetById(Int64 historyActionId)
{
DbDataReader reader = null;
try
{
var helper = Database.GetDbHelper();
reader = helper.ExecuteSPReader(_storedProcedure_GetById,
helper.CreateInputParam("@HistoryActionId", historyActionId));
Model.HistoryAction result = null;
if (reader.Read())
{
result = CreateHistoryAction(reader);
}
return(result);
}
catch (Exception ex)
{
Trace.WriteError("{0}", "GetById", CLASSNAME, ex, historyActionId);
throw DbHelper.TranslateException(ex);
}
finally
{
reader?.Close();
}
}
public string[] GetTableList()
{
DbCommand dbCommand = (DbCommand)null;
DbDataReader dbDataReader = (DbDataReader)null;
List <string> stringList = new List <string>();
try
{
dbCommand = this.GetDBCommand(this.GetShowTablesQuery());
dbDataReader = dbCommand.ExecuteReader();
while (dbDataReader.Read())
{
if (!dbDataReader.IsDBNull(0))
{
stringList.Add(dbDataReader.GetString(0));
}
}
}
catch (DbException ex)
{
}
finally
{
dbDataReader?.Close();
dbCommand?.Dispose();
}
return(stringList.ToArray());
}
///<summary>ReadActionには1行読む毎にやる処理を書く 最後まで成功したらTrue</summary>
protected async Task <bool> ExecuteReader(MySqlCommand cmd, Action <DbDataReader> ReadAction, IsolationLevel IsolationLevel = IsolationLevel.ReadCommitted)
{
try
{
using (var conn = NewConnection())
{
await conn.OpenAsync().ConfigureAwait(false);
using (var tran = await conn.BeginTransactionAsync(IsolationLevel).ConfigureAwait(false))
{
cmd.Connection = conn;
cmd.Transaction = tran;
DbDataReader reader = null;
try
{
reader = await cmd.ExecuteReaderAsync().ConfigureAwait(false);
while (await reader.ReadAsync().ConfigureAwait(false))
{
ReadAction.Invoke(reader);
}
reader.Close();
await tran.CommitAsync().ConfigureAwait(false);
return(true);
}
catch (MySqlException e) { reader?.Close(); await tran.RollbackAsync().ConfigureAwait(false); }
}
}
}
catch (Exception e) { }
return(false);
}
void Close()
{
// this can occur when we're not profiling, but we've inherited from ProfiledDbCommand and are returning a
// an unwrapped reader from the base command
reader?.Close();
profiler?.ReaderFinish(this);
}
public static List <JournalEntry> GetByEventType(JournalTypeEnum eventtype, int commanderid, DateTime startutc, DateTime stoputc)
{
Dictionary <long, TravelLogUnit> tlus = TravelLogUnit.GetAll().ToDictionary(t => t.id);
DbCommand cmd = null;
DbDataReader reader = null;
List <JournalEntry> entries = new List <JournalEntry>();
try
{
cmd = UserDatabase.Instance.ExecuteWithDatabase(cn => cn.Connection.CreateCommand("SELECT * FROM JournalEntries WHERE EventTypeID = @eventtype and CommanderID=@commander and EventTime >=@start and EventTime<=@Stop ORDER BY EventTime ASC"));
reader = UserDatabase.Instance.ExecuteWithDatabase(cn =>
{
cmd.AddParameterWithValue("@eventtype", (int)eventtype);
cmd.AddParameterWithValue("@commander", (int)commanderid);
cmd.AddParameterWithValue("@start", startutc);
cmd.AddParameterWithValue("@stop", stoputc);
return(cmd.ExecuteReader());
});
List <JournalEntry> retlist = null;
do
{
retlist = UserDatabase.Instance.ExecuteWithDatabase(cn =>
{
List <JournalEntry> vsc = new List <JournalEntry>();
while (vsc.Count < 1000 && reader.Read())
{
JournalEntry je = CreateJournalEntry(reader);
je.beta = tlus.ContainsKey(je.TLUId) ? tlus[je.TLUId].Beta : false;
vsc.Add(je);
}
return(vsc);
});
entries.AddRange(retlist);
}while (retlist != null && retlist.Count != 0);
return(entries);
}
finally
{
if (reader != null || cmd != null)
{
UserDatabase.Instance.ExecuteWithDatabase(cn =>
{
reader?.Close();
cmd?.Dispose();
});
}
}
}
private TableDef GetTD_MySQL(string table)
{
string query = "SELECT COLUMN_NAME As field,ORDINAL_POSITION as ordinal, IS_NULLABLE, DATA_TYPE as DataType, CHARACTER_MAXIMUM_LENGTH as data_length,COLUMN_KEY as is_index,COLUMN_COMMENT as description,EXTRA\r\n\t\t\t\t\t\t\tFROM INFORMATION_SCHEMA.COLUMNS\r\n\t\t\t\t\t\t\twhere table_name = @name and table_schema = @schema group by COLUMN_NAME";
DbCommand cmd = (DbCommand)null;
DbDataReader reader = (DbDataReader)null;
TableDef tableDef = new TableDef();
tableDef.Name = table;
List <FieldDef> fieldDefList = new List <FieldDef>();
try
{
cmd = DBConnectionFactory.GetMySqlCommand(query, this._dbconn);
DBTools.AddDbParameter(ref cmd, "@name", (object)table);
DBTools.AddDbParameter(ref cmd, "@schema", (object)this._dbconn.Database);
reader = cmd.ExecuteReader();
while (reader.Read())
{
FieldDef fieldDef = new FieldDef();
fieldDef.Name = DBTools.GetDBStringValue(reader, "field");
fieldDef.FieldLength = DBTools.GetDBIntValue(reader, "data_length");
string dbStringValue = DBTools.GetDBStringValue(reader, "description");
fieldDef.Description = string.IsNullOrEmpty(dbStringValue) ? DBHandler.NormalizeFieldName("_" + fieldDef.Name) : dbStringValue;
try
{
fieldDef.DataType = DBTools.GetCSTypeFromDBType(DBTools.GetDBStringValue(reader, "DataType"), (double)fieldDef.FieldLength);
}
catch (Exception ex)
{
string message = ex.Message;
}
fieldDef.CanBeNull = DBTools.GetDBStringValue(reader, "is_nullable") == "YES";
fieldDef.IsPrimaryKey = DBTools.GetDBStringValue(reader, "is_index", (string)null) == "PRI";
fieldDefList.Add(fieldDef);
}
}
catch (DbException ex)
{
throw ex;
}
finally
{
reader?.Close();
cmd?.Dispose();
}
tableDef.Fields = fieldDefList.ToArray();
return(tableDef);
}
public DataTable ToDataTable()
{
DataTable result = new DataTable();
DbDataReader reader = null;
try
{
reader = this.GetReader();
result.Load(reader);
}
finally
{
reader?.Close();
this.Close();
}
return(result);
}
public List <T> ToList()
{
List <T> result = new List <T>();
DbDataReader reader = null;
try
{
reader = this.GetReader();
result = DataExtractor <T> .ToList(reader);
}
finally
{
reader?.Close();
this.Close();
}
return(result);
}
/**
* Load column meta data list.
*
* @param connection connection
* @param table table name
* @param databaseType database type
* @return column meta data list
* @throws SQLException SQL exception
*/
public static ICollection <ColumnMetaData> Load(DbConnection connection, string table, string databaseType)
{
if (!IsTableExist(connection, databaseType, table))
{
return(new List <ColumnMetaData>(0));
}
ICollection <ColumnMetaData> result = new LinkedList <ColumnMetaData>();
bool isCaseSensitive = false;
using (var dbCommand = connection.CreateCommand())
{
dbCommand.CommandText = GenerateEmptyResultSql(table, databaseType);
DbDataReader dbDataReader = null;
List <DbColumn> dbColumns = null;
try
{
dbDataReader = dbCommand.ExecuteReader(behavior: CommandBehavior.KeyInfo);
using (var schemaTable = dbDataReader.GetSchemaTable())
{
isCaseSensitive = schemaTable.CaseSensitive;
dbColumns = dbDataReader.GetColumnSchema().ToList();
foreach (var dbColumn in dbColumns)
{
if (dbColumn.ColumnOrdinal.HasValue)
{
result.Add(new ColumnMetaData(dbColumn.ColumnName, dbColumn.ColumnOrdinal.Value, dbColumn.DataTypeName, dbColumn.IsKey.GetValueOrDefault(), dbColumn.IsAutoIncrement.GetValueOrDefault(), isCaseSensitive));
}
}
}
}
finally
{
dbDataReader?.Close();
dbColumns?.Clear();
}
}
return(result.OrderBy(o => o.ColumnOrdinal).ToList());
}
public static List <T> ExecuteQuery <T>(this DbProvider dbProvider, String sql, DbParameter[] paras = null, DbConnection connection = null, DbTransaction transaction = null) where T : class
{
List <T> results = new List <T>();
if (String.IsNullOrEmpty(sql))
{
return(results);
}
Boolean requiredDispose = connection == null;
DbDataReader dataReader = null;
try
{
connection = connection ?? dbProvider.DbConnection();
DbCommand command = dbProvider.DbCommand();
ExceutePrepare(connection, command, sql, paras);
if (transaction != null)
{
command.Transaction = transaction;
}
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
dataReader = command.ExecuteReader();
command.Parameters.Clear();
results = DataExtractor <T> .ToList(dataReader);
}
finally
{
dataReader?.Close();
if (requiredDispose)
{
connection.Dispose();
}
}
return(results);
}
private TableDef GetTD_PgSQL(string table)
{
string query = "SELECT\tpg_attribute.attnum as ordinal,\tpg_attribute.attname AS Field,\tpg_type.typname AS DataType,\tpg_attribute.atttypmod AS data_length,\tpg_attribute.attnotnull AS is_nullable,\tpg_index.indkey is_index\r\n\t\t\t\t\t\t\tFROM\tpg_class\r\n\t\t\t\t\t\t\tINNER JOIN pg_attribute ON pg_attribute.attrelid = pg_class.oid\r\n\t\t\t\t\t\t\tLEFT JOIN pg_index ON pg_index.indrelid = pg_attribute.attrelid and pg_index.indnatts = pg_attribute.attnum\r\n\t\t\t\t\t\t\tINNER JOIN pg_type ON pg_type.oid = pg_attribute.atttypid\r\n\t\t\t\t\t\t\tWHERE\r\n\t\t\t\t\t\t\t\tpg_class.relname = @name\r\n\t\t\t\t\t\t\t\tand pg_attribute.attnum > 0\r\n\t\t\t\t\t\t\tORDER BY pg_attribute.attnum;";
DbCommand cmd = (DbCommand)null;
DbDataReader reader = (DbDataReader)null;
TableDef tableDef = new TableDef();
tableDef.Name = table;
List <FieldDef> fieldDefList = new List <FieldDef>();
try
{
cmd = DBConnectionFactory.GetPgSqlCommand(query, this._dbconn);
DBTools.AddDbParameter(ref cmd, "@name", (object)table);
reader = cmd.ExecuteReader();
while (reader.Read())
{
FieldDef fieldDef = new FieldDef();
fieldDef.Name = DBTools.GetDBStringValue(reader, "field");
fieldDef.FieldLength = DBTools.GetDBIntValue(reader, "data_length");
fieldDef.DataType = DBTools.GetCSTypeFromDBType(DBTools.GetDBStringValue(reader, "DataType"), (double)fieldDef.FieldLength);
fieldDef.CanBeNull = DBTools.GetDBBoolValue(reader, "is_nullable");
fieldDef.IsPrimaryKey = DBTools.GetDBStringValue(reader, "is_index", (string)null) != null;
fieldDefList.Add(fieldDef);
}
}
catch (DbException ex)
{
throw ex;
}
finally
{
reader?.Close();
cmd?.Dispose();
}
tableDef.Fields = fieldDefList.ToArray();
return(tableDef);
}
private void LoadDbToRamStorage()
{
DbDataReader dataReader = null;
try
{
connection.Open();
// Acticles price types
var command = GetDbCommandByQuery("select ID, CODE, NAME, IS_DEFAULT, IS_WHOLESALE from WAREHOUSE_CATEGORY");
ArticlePriceTypes = GetArticlePriceTypesFromReader(command.ExecuteReader()).ToList();
command.Transaction.Commit();
command.Dispose();
// Articles
command = GetDbCommandByQuery("select ID, CODE, NAME, QUANTITY, UNIT_NAME, BARCODE from WAREHOUSE");
Articles = GetArticlesFromReader(command.ExecuteReader()).ToList();
command.Transaction.Commit();
command.Dispose();
// Article prices
command = GetDbCommandByQuery("select ID, WAREHOUSE_ID, WAREHOUSE_CATEGORY_ID, PRICE_VALUE, ENTRY_DATE from WAREHOUSE_PRICE");
ArticlePrices = GetArticlePricesFromReader(command.ExecuteReader()).ToList();
command.Transaction.Commit();
command.Dispose();
}
catch (Exception ex)
{
throw new SystemException($"Ошибка получения данных о товарах из Зевса", ex);
}
finally
{
dataReader?.Close();
connection?.Close();
}
}
/// <inheritdoc cref="DbDataReader.Close()" />
public override void Close()
{
_reader?.Close();
}
public string GetColumnType(SqlHelper sqlHelper, string dataBaseName, string tableName, string columnName)
{
if (tableName != _lastTableName || _tableInfos == null)
{
_tableInfos = new List <dynamic>();
DbDataReader dr = sqlHelper.ExecuteReader("select COLUMN_NAME,DATA_TYPE from information_schema.columns where table_name='" + tableName + "'");
while (dr.Read())
{
string type = dr["DATA_TYPE"].ToString().ToLower();
if (type == "int")
{
type = "int";
}
else if (type == "bigint")
{
type = "long";
}
else if (type == "smallint")
{
type = "short";
}
else if (type == "decimal" || type == "money" || type == "numeric" || type == "smallmoney")
{
type = "decimal";
}
else if (type == "float")
{
type = "double";
}
else if (type == "real")
{
type = "float";
}
else if (type == "varchar" || type == "text" || type == "char" || type == "nchar" || type == "ntext" || type == "nvarchar")
{
type = "string";
}
else if (type == "datetime" || type == "date" || type == "datetime2" || type == "datetimeoffset" || type == "smalldatetime" || type == "time" || type == "timestamp")
{
type = "DateTime";
}
else if (type == "bit")
{
type = "bool";
}
else if (type == "tinyint")
{
type = "byte";
}
else if (type == "binary" || type == "image" || type == "varbinary")
{
type = "byte[]";
}
else
{
type = "string";
}
dynamic tableInfo = new ExpandoObject();
tableInfo.columnName = dr["COLUMN_NAME"].ToString();
tableInfo.type = type;
_tableInfos.Add(tableInfo);
}
dr.Close();
dr.Dispose();
}
_lastTableName = tableName;
foreach (dynamic item in _tableInfos)
{
if (item.columnName == columnName)
{
return(item.type);
}
}
return("string");
}
// Primary method to fill historylist
// Get All journals matching parameters.
// if callback set, then each JE is passed back thru callback and not accumulated. Callback is in a thread. Callback can stop the accumulation if it returns false
static public List <JournalEntry> GetAll(int commander = -999, DateTime?startdateutc = null, DateTime?enddateutc = null,
JournalTypeEnum[] ids = null, DateTime?allidsafterutc = null, Func <JournalEntry, Object, bool> callback = null, Object callbackobj = null,
int chunksize = 1000)
{
var tluslist = TravelLogUnit.GetAll();
Dictionary <long, TravelLogUnit> tlus = tluslist.ToDictionary(t => t.ID);
DbCommand cmd = null;
DbDataReader reader = null;
List <JournalEntry> entries = new List <JournalEntry>();
try
{
cmd = UserDatabase.Instance.ExecuteWithDatabase(cn => cn.Connection.CreateCommand("select Id,TravelLogId,CommanderId,EventData,Synced from JournalEntries"));
reader = UserDatabase.Instance.ExecuteWithDatabase(cn =>
{
string cnd = "";
if (commander != -999)
{
cnd = cnd.AppendPrePad("CommanderID = @commander", " and ");
cmd.AddParameterWithValue("@commander", commander);
}
if (startdateutc != null)
{
cnd = cnd.AppendPrePad("EventTime >= @after", " and ");
cmd.AddParameterWithValue("@after", startdateutc.Value);
}
if (enddateutc != null)
{
cnd = cnd.AppendPrePad("EventTime <= @before", " and ");
cmd.AddParameterWithValue("@before", enddateutc.Value);
}
if (ids != null)
{
int[] array = Array.ConvertAll(ids, x => (int)x);
if (allidsafterutc != null)
{
cmd.AddParameterWithValue("@idafter", allidsafterutc.Value);
cnd = cnd.AppendPrePad("(EventTypeId in (" + string.Join(",", array) + ") Or EventTime>=@idafter)", " and ");
}
else
{
cnd = cnd.AppendPrePad("EventTypeId in (" + string.Join(",", array) + ")", " and ");
}
}
if (cnd.HasChars())
{
cmd.CommandText += " where " + cnd;
}
cmd.CommandText += " Order By EventTime,Id ASC";
return(cmd.ExecuteReader());
});
List <JournalEntry> retlist = null;
#if TIMESCAN
Dictionary <string, List <long> > times = new Dictionary <string, List <long> >();
Stopwatch sw = new Stopwatch();
sw.Start();
#endif
do
{
// experiments state that reading the DL is not the time sink, its creating the journal entries
retlist = UserDatabase.Instance.ExecuteWithDatabase(cn => // split into smaller chunks to allow other things access..
{
List <JournalEntry> list = new List <JournalEntry>();
while (list.Count < chunksize && reader.Read())
{
#if TIMESCAN
long t = sw.ElapsedTicks;
#endif
JournalEntry je = JournalEntry.CreateJournalEntryFixedPos(reader);
list.Add(je);
#if TIMESCAN
long tw = sw.ElapsedTicks - t;
if (!times.TryGetValue(sys.EventTypeStr, out var x))
{
times[sys.EventTypeStr] = new List <long>();
}
times[sys.EventTypeStr].Add(tw);
#endif
}
return(list);
});
if (callback != null) // collated, now process them, if callback, feed them thru callback procedure
{
foreach (var e in retlist)
{
if (!callback.Invoke(e, callbackobj)) // if indicate stop
{
retlist = null;
break;
}
}
}
else
{
entries.AddRange(retlist);
}
}while (retlist != null && retlist.Count != 0);
#if TIMESCAN
List <Results> res = new List <Results>();
foreach (var kvp in times)
{
Results r = new Results();
r.name = kvp.Key;
r.avg = kvp.Value.Average();
r.min = kvp.Value.Min();
r.max = kvp.Value.Max();
r.total = kvp.Value.Sum();
r.avgtime = ((double)r.avg / Stopwatch.Frequency * 1000);
r.sumtime = ((double)r.total / Stopwatch.Frequency * 1000);
r.count = kvp.Value.Count;
res.Add(r);
}
//res.Sort(delegate (Results l, Results r) { return l.sumtime.CompareTo(r.sumtime); });
res.Sort(delegate(Results l, Results r) { return(l.avgtime.CompareTo(r.avgtime)); });
string rs = "";
foreach (var r in res)
{
rs = rs + Environment.NewLine + string.Format("Time {0} min {1} max {2} avg {3} ms count {4} totaltime {5} ms", r.name, r.min, r.max, r.avgtime.ToString("#.#########"), r.count, r.sumtime.ToString("#.#######"));
}
System.Diagnostics.Trace.WriteLine(rs);
//File.WriteAllText(@"c:\code\times.txt", rs);
#endif
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("Getall Exception " + ex);
}
finally
{
if (reader != null || cmd != null)
{
UserDatabase.Instance.ExecuteWithDatabase(cn =>
{
reader?.Close();
cmd?.Dispose();
});
}
}
return(entries);
}
public DataTable ReadData(DatasetConfig datasetConfig, string filter, long Start, int Count, DateTime?FromDate, DateTime?ToDate)
{
//try
//{
CheckAndEstablishReadConnection();
readCmd.Connection = readDBConn;
string where = "";
if (!string.IsNullOrEmpty(filter))
{
where = " WHERE ";
bool first = true;
foreach (var rows in datasetConfig.DatasetConfigRows)
{
if (!first)
{
where += "OR ";
}
where += rows.DatabaseField + " LIKE '%" + filter + "%' ";
first = false;
}
}
if (FromDate != null)
{
if (string.IsNullOrEmpty(where))
{
where = " WHERE ";
}
else
{
where += " AND ";
}
where += datasetConfig.DateTimeDatabaseField;
where += " >= '" + FromDate.Value.ToString("yyyyMMdd HH:mm:ss.mmm") + "'";
}
if (ToDate != null)
{
if (string.IsNullOrEmpty(where))
{
where = " WHERE ";
}
else
{
where += " AND ";
}
where += datasetConfig.DateTimeDatabaseField;
where += " <= '" + ToDate.Value.ToString("yyyyMMdd HH:mm:ss.mmm") + "'";
}
readCmd.CommandText = "WITH " + datasetConfig.Name + "_withRowNumber AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY id DESC) AS 'RowNumber' FROM " + datasetConfig.Name + where + " ) SELECT * FROM " + datasetConfig.Name + "_withRowNumber WHERE RowNumber BETWEEN " + Start.ToString() + " AND " + (Start + Count).ToString() + ";";
//readCmd.CommandText = "SELECT *, ROW_NUMBER() OVER(ORDER BY id DESC) AS [RowNum] FROM " + datasetConfig.Name + " WHERE RowNum BETWEEN " + Start.ToString() + " AND " + (Start + Count).ToString();
//readCmd.CommandText = "SELECT * FROM " + datasetConfig.Name + " ORDER BY id DESC LIMIT " + Count.ToString() + " OFFSET " + Start.ToString();
DbDataReader akReader = readCmd.ExecuteReader();
DataTable myTbl = new DataTable();
myTbl.Load(akReader);
akReader.Close();
return(myTbl);
}
public void ExecProc(Sbw.DbClinet.Proc proc)
{
using (DbClinet Clinet = new DbClinet())
{
DbDataReader dr = Clinet.ExecuteReader(proc);
Row r;
List <object> data;
Dictionary <string, object> ud = null;
while (dr.Read())
{
r = new Row();
data = new List <object>();
if (SwitchData != null)
{
ud = new Dictionary <string, object>();
r.id = dr[SwitchData.Id];
for (int i = 0; i < dr.FieldCount; i++)
{
ud.Add(dr.GetName(i), dr.GetValue(i));
}
for (int i = 0; i < SwitchData.Data.Length; i++)
{
Match m = Regex.Match(SwitchData.Data[i], "(.{0,})\\[(.{0,})\\]");
if (m.Success)
{
if ("".Equals(m.Groups[1].Value))
{
data.Add(SwitchData.SwitchData(m.Groups[2].Value, ud));
}
else
{
data.Add(SwitchData.SwitchData(m.Groups[2].Value, dr[m.Groups[1].Value]));
}
}
else
{
data.Add(dr[SwitchData.Data[i]]);
}
}
}
else
{
r.id = dr.GetValue(0);
for (int i = 1 - proc.type; i < dr.FieldCount; i++)
{
data.Add(dr.GetValue(i));
}
}
r.data = data;
r.userdata = ud;
rows.Add(r);
}
if (proc.type == 0 && dr.NextResult() && dr.Read())
{
this.total_count = dr.GetInt32(0);
}
if (dr.NextResult() && dr.Read())
{
List <string> l = new List <string>();
for (int i = 0; i < dr.FieldCount; i++)
{
l.Add(dr.GetName(i));
}
if (l.Contains("Header"))
{
this.Header = dr["Header"] as string;
}
if (l.Contains("ColAlign"))
{
this.ColAlign = dr["ColAlign"] as string;
}
if (l.Contains("ColTypes"))
{
this.ColTypes = dr["ColTypes"] as string;
}
if (l.Contains("ColSorting"))
{
this.ColSorting = dr["ColSorting"] as string;
}
if (l.Contains("Width"))
{
this.Width = dr["Width"] as string;
}
}
else if (SwitchData != null)
{
this.Header = SwitchData.Header;
this.ColAlign = SwitchData.ColAlign;
this.ColTypes = SwitchData.ColTypes;
this.ColSorting = SwitchData.ColSorting;
this.Width = SwitchData.Width;
}
dr.Close();
}
}
/// <summary>
/// Executes a query and reads and returns the first <see cref="Int32"/> value
/// </summary>
/// <param name="query"></param>
/// <param name="arg"></param>
/// <returns>True, if the first <see cref="Int32"/> value is above zero</returns>
public virtual bool QueryBool(string query, params object[] arg)
{
query = string.Format(query, arg);
var result = false;
LastException = null;
if (query.Length == 0) {
return false;
}
try {
Open();
Command = Connection.CreateCommand();
Command.CommandText = query;
Reader = Command.ExecuteReader();
while (Reader.Read()) {
result = (Reader.GetInt32(0) > 0);
}
Reader.Close();
} catch (Exception ex) {
System.Diagnostics.Debug.WriteLine(ex);
LastException = ex;
return false;
}
return result;
}
public virtual void VisitDatabase(Database database)
{
var visitSchema = ExportOptions == null || ExportOptions.ExportSchema;
var visitData = ExportOptions == null || ExportOptions.ExportData;
var visitFKs = ExportOptions == null || (ExportOptions.Flags & ExportFlags.ExportForeignKeys) != ExportFlags.ExportNothing;
var visitIdent = ExportOptions == null || (ExportOptions.Flags & ExportFlags.ExportIdentities) != ExportFlags.ExportNothing;
WriteComment("Database: {0}", database.Name);
WriteComment("Generated on: {0}", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
WriteComment("Author: {0}", Environment.UserName);
WriteLine();
if (visitSchema)
{
if (SupportsDbCreation)
{
WriteDbCreationDirective(database);
}
foreach (Table table in database.Tables)
{
if (table.Checked)
{
table.AcceptVisitor(this);
}
}
}
if (visitData)
{
foreach (Table table in database.Tables)
{
if (table.Checked)
{
var rowsInserted = false;
using (DbDataReader dr = SqlHelper.OpenTable(table, visitIdent, SupportsRowVersion))
{
while (dr.Read())
{
WriteInsertDirective(table, dr);
rowsInserted = true;
}
dr.Close();
}
if (rowsInserted)
{
WriteLine();
}
}
}
}
if (visitSchema && visitFKs && !RequireInlineConstraints)
{
foreach (Table table in database.Tables)
{
if (table.Checked)
{
var fkExported = false;
foreach (ForeignKey fk in table.ForeignKeys)
{
if (fk.RelatedTable.Checked)
{
fk.AcceptVisitor(this);
fkExported = true;
}
}
if (fkExported)
{
WriteLine();
}
}
}
}
}
static public List <JournalEntry> GetAll(int commander = -999, DateTime?afterutc = null, DateTime?beforeutc = null,
JournalTypeEnum[] ids = null, DateTime?allidsafterutc = null)
{
Dictionary <long, TravelLogUnit> tlus = TravelLogUnit.GetAll().ToDictionary(t => t.id);
DbCommand cmd = null;
DbDataReader reader = null;
List <JournalEntry> entries = new List <JournalEntry>();
try
{
cmd = UserDatabase.Instance.ExecuteWithDatabase(cn => cn.Connection.CreateCommand("select * from JournalEntries"));
reader = UserDatabase.Instance.ExecuteWithDatabase(cn =>
{
string cnd = "";
if (commander != -999)
{
cnd = cnd.AppendPrePad("CommanderID = @commander", " and ");
cmd.AddParameterWithValue("@commander", commander);
}
if (afterutc != null)
{
cnd = cnd.AppendPrePad("EventTime >= @after", " and ");
cmd.AddParameterWithValue("@after", afterutc.Value);
}
if (beforeutc != null)
{
cnd = cnd.AppendPrePad("EventTime <= @before", " and ");
cmd.AddParameterWithValue("@before", beforeutc.Value);
}
if (ids != null)
{
int[] array = Array.ConvertAll(ids, x => (int)x);
if (allidsafterutc != null)
{
cmd.AddParameterWithValue("@idafter", allidsafterutc.Value);
cnd = cnd.AppendPrePad("(EventTypeId in (" + string.Join(",", array) + ") Or EventTime>=@idafter)", " and ");
}
else
{
cnd = cnd.AppendPrePad("EventTypeId in (" + string.Join(",", array) + ")", " and ");
}
}
if (cnd.HasChars())
{
cmd.CommandText += " where " + cnd;
}
cmd.CommandText += " Order By EventTime ASC";
return(cmd.ExecuteReader());
});
List <JournalEntry> retlist = null;
do
{
retlist = UserDatabase.Instance.ExecuteWithDatabase(cn =>
{
List <JournalEntry> list = new List <JournalEntry>();
while (list.Count < 1000 && reader.Read())
{
JournalEntry sys = JournalEntry.CreateJournalEntry(reader);
sys.beta = tlus.ContainsKey(sys.TLUId) ? tlus[sys.TLUId].Beta : false;
list.Add(sys);
}
return(list);
});
entries.AddRange(retlist);
}while (retlist != null && retlist.Count != 0);
return(entries);
}
finally
{
if (reader != null || cmd != null)
{
UserDatabase.Instance.ExecuteWithDatabase(cn =>
{
reader?.Close();
cmd?.Dispose();
});
}
}
}
/// <summary>
/// 仅缓存查询用
/// </summary>
/// <param name="reader"></param>
/// <param name="mainType"></param>
/// <param name="mapping"></param>
/// <param name="runTime"></param>
/// <returns></returns>
internal static List <object> DataReaderToObjectList(DbDataReader reader, Type mainType, IEnumerable <Attribute.FieldMapping> mapping, out double runTime)
{
//rem mainType 不一定为T
//var sw = new Stopwatch();
//sw.Start();
runTime = 0;
var list = new List <object>();
var typeArry = TypeCache.GetTable(mainType).Fields;
var columns = new Dictionary <string, int>();
for (int i = 0; i < reader.FieldCount; i++)
{
columns.Add(reader.GetName(i).ToLower(), i);
}
var actions = new List <ActionItem <object> >();
object[] values = new object[reader.FieldCount];
foreach (var mp in mapping)
{
var fieldName = mp.QueryName.ToLower();
var info = typeArry.Find(b => b.MemberName == mp.MappingName);
if (info == null)
{
continue;
}
var action = new ActionItem <object>()
{
Set2 = info.SetValue, Name = mp.MappingName, ValueIndex = columns[fieldName]
};
columns.Remove(fieldName);
actions.Add(action);
}
var _actions = actions.ToArray();
int actionsCount = actions.Count;
while (reader.Read())
{
reader.GetValues(values);
var detailItem = System.Runtime.Serialization.FormatterServices.GetUninitializedObject(mainType);
foreach (var ac in _actions)
{
ac.SetValue2(detailItem, values);
}
#region 剩下的放索引
//按IModel算
if (columns.Count > 0)
{
var model = detailItem as IModel;
foreach (var item in columns)
{
var col = item.Key;
var n = col.LastIndexOf("__");
if (n == -1)
{
continue;
}
var mapingName = col.Substring(n + 2);
model.SetIndexData(mapingName, values[item.Value]);
}
}
#endregion
list.Add(detailItem);
}
reader.Close();
reader.Dispose();
//sw.Stop();
//runTime = sw.ElapsedMilliseconds;
//Console.WriteLine("CRL映射用时:" + runTime);
return(list);
}
/// <summary>
/// Создает таблицу значений и копирует в нее все записи набора.
/// </summary>
/// <returns>ТаблицаЗначений</returns>
//[ContextMethod("Выгрузить", "Unload")]
//public ValueTable Unload()
// Заполняет таблицу значений из reader
public void Unload()
{
_resultTable.Clear();
_resultTable.Load(_reader);
/*
* ValueTable resultTable = new ValueTable();
*
* for (int ColIdx = 0; ColIdx < _reader.FieldCount; ColIdx++)
* {
* resultTable.Columns.Add(_reader.GetName(ColIdx));
* }
*
* foreach (DbDataRecord record in _reader)
* {
* ValueTableRow row = resultTable.Add();
*
* for (int ColIdx = 0; ColIdx < _reader.FieldCount; ColIdx++)
* {
* if (record.IsDBNull(ColIdx))
* {
* row.Set(ColIdx, ValueFactory.Create());
* continue;
* }
*
* //Console.WriteLine("queryresult-col-type:" + record.GetFieldType(ColIdx).ToString() + "::" + record.GetDataTypeName(ColIdx));
*
* if (record.GetFieldType(ColIdx) == typeof(Int32))
* {
* row.Set(ColIdx, ValueFactory.Create((int)record.GetValue(ColIdx)));
* }
* if (record.GetFieldType(ColIdx) == typeof(Int64))
* {
* row.Set(ColIdx, ValueFactory.Create(record.GetInt64(ColIdx)));
* }
* if (record.GetFieldType(ColIdx) == typeof(Boolean))
* {
* row.Set(ColIdx, ValueFactory.Create(record.GetBoolean(ColIdx)));
* }
* if (record.GetFieldType(ColIdx) == typeof(UInt64))
* {
* row.Set(ColIdx, ValueFactory.Create(record.GetValue(ColIdx).ToString()));
* }
*
* if (record.GetFieldType(ColIdx).ToString() == "System.Double")
* {
* double val = record.GetDouble(ColIdx);
* row.Set(ColIdx, ValueFactory.Create(val.ToString()));
* }
* if (record.GetFieldType(ColIdx) == typeof(Single))
* {
* float val = record.GetFloat(ColIdx);
* row.Set(ColIdx, ValueFactory.Create(val.ToString()));
* }
* if (record.GetFieldType(ColIdx) == typeof(Decimal))
* {
* row.Set(ColIdx, ValueFactory.Create(record.GetDecimal(ColIdx)));
* }
* if (record.GetFieldType(ColIdx).ToString() == "System.String")
* {
* row.Set(ColIdx, ValueFactory.Create(record.GetString(ColIdx)));
* }
* if (record.GetFieldType(ColIdx).ToString() == "System.DateTime")
* {
* row.Set(ColIdx, ValueFactory.Create(record.GetDateTime(ColIdx)));
* }
* if (record.GetFieldType(ColIdx).ToString() == "System.Byte[]")
* {
* var data = (byte[])record[ColIdx];
* var newData = new BinaryDataContext(data);
* row.Set(ColIdx, ValueFactory.Create(newData));
* }
* }
* }*/
_reader.Close();
}
//将BOM表中的一个BOM树递归登记到列表bomTree中
private bool AddBomItem(long wuLBM, string qiH, string gongZLH)
{
long tmpWuLBM = 0L;
Bom bom = new Bom();
DbDataReader reader = null;
//查找代用件
sql = $"select daiWLBM from daiYJJHZhB z, daiYJJHCB c where c.zhuBBSh = z.biaoSh and c.yuanWLBM = z.wuLBM and c.yuanWLTH = z.tuH and c.yuanWLGG=z.GuiG and z.qiH='{qiH}' and z.gongZLH = '{gongZLH}' and z.wuLBM={wuLBM}";
cmd.CommandText = sql;
try
{
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
tmpWuLBM = Convert.ToInt64(reader["daiWLBM"].ToString());
}
else
{
tmpWuLBM = wuLBM;
}
}
catch (Exception e)
{
log.Error(string.Format($"查询代用件计划表失败，SQL=[{sql}]\nError{e.StackTrace}"));
return(false);
}
finally
{
reader?.Close();
}
//查找当前物料信息 Cmid = tmpWuLBM
sql = $"select * from BOM where CmId = {tmpWuLBM}";
cmd.CommandText = sql;
try
{
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
bom.materielIdentfication = Convert.ToInt64(reader["materielIdentfication"].ToString());
bom.tmpId = Convert.ToInt64(reader["tmpId"].ToString());
bom.CTmpId = Convert.ToInt64(reader["CTmpId"].ToString());
bom.CmId = Convert.ToInt64(reader["CmId"].ToString());
bom.CNum = Convert.ToDecimal(reader["CNum"].ToString());
bom.rlSeqNo = Convert.ToInt32(reader["rlSeqNo"].ToString());
bom.peiTNo = Convert.ToInt64(reader["peiTNo"].ToString());
}
else
{
log.Error(string.Format($"未在BOM表中查到相关记录，SQL=[{sql}]\n"));
return(false);
}
}
catch (Exception e)
{
log.Error(string.Format($"查询BOM失败，SQL=[{sql}]\nError{e.StackTrace}"));
return(false);
}
finally
{
reader.Close();
}
//登记物料树
bomTree.Add(bom);
//查找当前物料的子物料
List <long> mid_list = new List <long>();
sql = $"select Cmid from BOM where materielIdentfication = {wuLBM} ";
cmd.CommandText = sql;
try
{
reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
mid_list.Add(Convert.ToInt64(reader["CmId"].ToString()));
}
reader.Close();
foreach (var mid in mid_list)
{
if (!AddBomItem(mid, qiH, gongZLH))
{
return(false);
}
}
}
else
{
return(true);
}
}
catch (Exception e)
{
log.Error(string.Format($"查询BOM失败，SQL=[{sql}]\nError{e.StackTrace}"));
return(false);
}
finally
{
reader?.Close();
}
return(true);
}
public override void CreateOrModify_TablesAndFields(string dataTable, DatasetConfig datasetConfig)
{
this.datasetConfig = datasetConfig;
this.dataTable = dataTable;
this.fieldList = datasetConfig.DatasetConfigRows;
List <DatasetConfigRow> createFieldList;
//Look if Table exists, when not, create it!
try
{
string sql = "SELECT TOP 1 * FROM " + dataTable + ";";
myCmd.Connection = myDBConn;
myCmd.CommandText = sql;
myReader = myCmd.ExecuteReader();
}
catch (SqlException ex)
{
if (ex.Number == 208)
{
try
{
string sql = "CREATE TABLE " + dataTable + " (id int IDENTITY(1,1)PRIMARY KEY CLUSTERED); ";
myCmd.CommandText = sql;
myCmd.ExecuteNonQuery();
sql = "SELECT * FROM " + dataTable + ";";
myCmd.CommandText = sql;
myReader = myCmd.ExecuteReader();
}
catch (SqlException ex_ex)
{
throw ex_ex;
}
}
else
{
throw ex;
}
}
//Look for the Fields, create or alter them!
List <String> existDBFelderliste = new List <string>();
for (int n = 0; n < myReader.FieldCount; n++)
{
existDBFelderliste.Add(myReader.GetName(n));
}
myReader.Close();
//Wenn Date Time Feld gesetzt...
dateFieldName = datasetConfig.DateTimeDatabaseField;
createFieldList = new List <DatasetConfigRow>(fieldList);
if (!string.IsNullOrEmpty(datasetConfig.DateTimeDatabaseField))
{
createFieldList.Add(new DatasetConfigRow()
{
DatabaseField = dateFieldName, DatabaseFieldType = "datetime"
});
}
foreach (DatasetConfigRow myFeld in createFieldList)
{
foreach (string existMyFeld in existDBFelderliste)
{
if (myFeld.DatabaseField.ToLower() == existMyFeld.ToLower())
{
goto nextFeld;
}
}
//Feld existiert nicht -> erzeugen
string sql = "ALTER TABLE " + dataTable + " ADD " + myFeld.DatabaseField + " " + myFeld.DatabaseFieldType;
try
{
myCmd.Connection = myDBConn;
myCmd.CommandText = sql;
myCmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw ex;
}
nextFeld:
//Irgendeine anweisung, da sonst der Sprung nicht geht...
{
}
}
//Create Insert Command
string wertliste = "", felderliste = "";
foreach (DatasetConfigRow myFeld in createFieldList)
{
if (wertliste != "")
{
wertliste += ",";
felderliste += ",";
}
felderliste += myFeld.DatabaseField;
wertliste += "@" + myFeld.DatabaseField;
}
insertCommand = "INSERT INTO " + dataTable + "(" + felderliste + ") values(" + wertliste + ")";
}
public static void InnerJoin()
{
#if DEBUG
//System.Threading.Thread.Sleep(1000 * 8);
#endif
string tblname = "RDBMS_Table_ADONET_RegressionTest_" + Guid.NewGuid().ToString().Replace("-", "");
string tblname2 = "RDBMS_Table_ADONET_RegressionTest_" + Guid.NewGuid().ToString().Replace("-", "");
System.Data.Common.DbProviderFactory fact = DbProviderFactories.GetFactory("Qizmt_DataProvider");
DbConnection conn = fact.CreateConnection();
conn.ConnectionString = "Data Source = localhost";
conn.Open();
try
{
DbCommand cmd = conn.CreateCommand();
#if DEBUG
//System.Diagnostics.Debugger.Launch();
#endif
//Create table 1
{
cmd.CommandText = "CREATE TABLE " + tblname + " (num INT, str CHAR(60), X LONG, Y DOUBLE, Z DOUBLE, ID INT)";
int rows = cmd.ExecuteNonQuery();
}
//Insert data 1
{
cmd.CommandText = "INSERT INTO " + tblname + " VALUES(11380001,'a{BC54AFDD-34AE-445b-820A-BFCBC716FF77}ONE',312111,-11223344,3111, 1);";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO " + tblname + " VALUES(11380002,'B{745B9042-77B2-4633-B765-59A1B0DA7194}TWO',312111, 0 ,3111, 2);";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO " + tblname + " VALUES(11380003,'c{512441F2-0AFE-42d3-A4BA-9DD26A9E553A}THREE',312111, 11223344,3111, 3);";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO " + tblname + " VALUES(11380003,'d{6F380F7F-A70C-4b22-AA87-E480955B3579}FOUR',312111, 11223344,3111, 4);";
cmd.ExecuteNonQuery();
}
{
cmd.CommandText = "SELECT * FROM " + tblname;
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
}
reader.Close();
}
//Create table 2
{
cmd.CommandText = "CREATE TABLE " + tblname2 + " (foo INT, fooID INT)";
int rows = cmd.ExecuteNonQuery();
}
//Insert data 2
{
cmd.CommandText = "INSERT INTO " + tblname2 + " VALUES(2000, 2);";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO " + tblname2 + " VALUES(3000, 3);";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO " + tblname2 + " VALUES(1000, 1);";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO " + tblname2 + " VALUES(5000, 5);";
cmd.ExecuteNonQuery();
}
{
cmd.CommandText = "SELECT * FROM " + tblname;
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
}
reader.Close();
}
{
cmd.CommandText = "SELECT * FROM " + tblname2;
DbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
}
reader.Close();
}
{
cmd.CommandText = "SELECT * FROM " + tblname + " INNER JOIN " + tblname2 + " ON " + tblname + ".ID = " + tblname2 + ".fooID";
DbDataReader reader = cmd.ExecuteReader();
int rowcount = 0;
while (reader.Read())
{
rowcount++;
string field;
{
field = tblname + ".x";
if ((long)reader[field] != 312111)
{
throw new Exception("Unexpected value for " + field);
}
}
/*{
* field = "x";
* if ((long)reader[field] != 312111)
* {
* throw new Exception("Unexpected value for " + field);
* }
* }*/
{
field = tblname + ".id";
int id = (int)reader[field];
if (id < 0 || id > 3)
{
throw new Exception("ID out of range: " + id);
}
{
//field = "fooID";
field = tblname2 + ".fooID";
if ((int)reader[field] != id)
{
throw new Exception("Unexpected value for " + field);
}
}
{
field = tblname2 + ".foo";
if ((int)reader[field] != id * 1000)
{
throw new Exception("Unexpected value for " + field);
}
}
}
}
reader.Close();
if (rowcount != 3)
{
throw new Exception("Expected exactly 3 rows output");
}
}
}
finally
{
try
{
conn.Close();
}
catch (Exception e)
{
Console.Error.WriteLine(" *** Close error: {0}", e.Message);
}
}
}
