/// <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); } } }