public static DbTable LoadDb(DbDataReader reader) { if (!reader.Read()) { return null; } var tbl = new DbTable((string)reader["TABLE_NAME"]); var firstColumn = new DbColumn( (string)reader["COLUMN_NAME"], (string)reader["DATA_TYPE"] ); tbl.Columns = new List<DbColumn>() { firstColumn }; while (reader.Read()) { if (tbl.Name != (string)reader["TABLE_NAME"]) { break; } tbl.Columns.Add(new DbColumn( (string)reader["COLUMN_NAME"], (string)reader["DATA_TYPE"] )); } return tbl; }
public static string GetLookupValue(string sDisplayField, string sLinkField, string sTable, string sValue, TypeCode TType, bool isMultiline) { string sGetLookupValue = ""; string sqlSelect = "SELECT " + sDisplayField + " FROM " + sTable + " WHERE "; try { QueryCommand cmd = new QueryCommand(sqlSelect); if (isMultiline) { int i = 0; foreach (string s in sValue.Split(',')) { string paramName = "param" + i.ToString(); cmd.CommandSql += sLinkField + "=@" + paramName + " Or "; cmd.AddParameter(paramName, s); i += 1; } if (cmd.CommandSql.Length > 2) { cmd.CommandSql = cmd.CommandSql.Remove(cmd.CommandSql.Length - 3); } } else { cmd.CommandSql += sLinkField + "= @LinkField"; cmd.AddParameter("LinkField", sValue); } System.Data.Common.DbDataReader dbDr = (System.Data.Common.DbDataReader)DataService.GetReader(cmd); if (dbDr.HasRows) { if (isMultiline) { while (dbDr.Read()) { sGetLookupValue += dbDr[0].ToString() + ","; } sGetLookupValue = sGetLookupValue.Trim(",".ToCharArray()); } else if (dbDr.Read()) { sGetLookupValue = dbDr[0].ToString(); } } dbDr.Dispose(); } finally { } return(sGetLookupValue); }
public List <T> Fetch <T>(sql sql) { try { System.Data.Common.DbDataReader reader = dbHelperInstance.GetReader(sql.GetSql()); List <T> list = new List <T>(); Type type = typeof(T); if (type.IsPrimitive || type == typeof(string) || type == typeof(DateTime) || type.IsEnum) { while (reader.Read()) { if (type.IsEnum) { list.Add((T)Enum.ToObject(type, reader.GetValue(0))); } else { list.Add((T)Convert.ChangeType(reader.GetValue(0), type)); } } } else { while (reader.Read()) { T result = Activator.CreateInstance <T>(); PropertyInfo[] properties = type.GetProperties(); foreach (PropertyInfo property in properties) { string columName = attributesHelper.GetColumnName(property); if (property.PropertyType.IsEnum) { property.SetValue(result, Enum.ToObject(property.PropertyType, reader.GetValue(reader.GetOrdinal(columName))), null); } else { property.SetValue(result, Convert.ChangeType(reader.GetValue(reader.GetOrdinal(columName)), property.PropertyType), null); } } list.Add(result); } } return(list); } catch { throw; } finally { Close(); } }
public void Postprocess(DbDataReader reader, IList<Exception> exceptions) { reader.Read(); var values = reader.GetFieldValue<int[]>(0); applyDataFromSproc(values); }
public static void GetPrestataire() { String sql = "SELECT * FROM Prestataire"; using (MySqlCommand command = new MySqlCommand(sql, connection)) { try { command.Connection.Open(); } catch (MySqlException) { } using (System.Data.Common.DbDataReader dbReader = command.ExecuteReader()) { while (dbReader.Read()) { int Id = dbReader.GetInt32(0); String Nom = dbReader.GetString(1); String Url = dbReader.GetString(2); String Categorie = dbReader.GetString(3); String Contact = dbReader.GetString(4); String Adresse = dbReader.GetString(5); String Description = dbReader.GetString(6); Boolean Activation = dbReader.GetBoolean(7); String Prix = dbReader.GetString(8); // attente de la création de la class business Business.Prestataires.Add(new Prestataire(Id, Nom, Url, Categorie, Contact, Adresse, Description, Activation, Prix)); } } command.Connection.Close(); } }
public static DataTable ConvertDataReaderToDataTable(DbDataReader reader) { try { DataTable table = new DataTable(); int fieldCount = reader.FieldCount; for (int fieldIndex = 0; fieldIndex < fieldCount; ++fieldIndex) { table.Columns.Add(reader.GetName(fieldIndex), reader.GetFieldType(fieldIndex)); } table.BeginLoadData(); object[] rowValues = new object[fieldCount]; while (reader.Read()) { reader.GetValues(rowValues); table.LoadDataRow(rowValues, true); } reader.Close(); table.EndLoadData(); return table; } catch (Exception ex) { throw new Exception("DataReader转换为DataTable时出错!", ex); } }
public static void GetDevisPrestataire(Devis d) { String sql = "SELECT devisid, prestataireid, dp.prix, nom FROM DevisPrestataire dp inner join Prestataire p on dp.prestataireid = p.id WHERE devisId=@devisId "; using (MySqlCommand command = new MySqlCommand(sql, connection)) { command.Connection.Open(); command.Parameters.AddWithValue("@devisid", d.Id); using (System.Data.Common.DbDataReader dbReader = command.ExecuteReader()) { while (dbReader.Read()) { int devisid = dbReader.GetInt32(0); int prestataireid = dbReader.GetInt32(1); int prix = dbReader.GetInt32(2); string nom = dbReader.GetString(3); DevisPrestataire dp = new DevisPrestataire(devisid, prestataireid, prix) { Nom = nom }; d.DevisPrestataires.Add(dp); } } command.Connection.Close(); } }
public Table(DbDataReader reader) { Headers = Enumerable.Range(0, reader.FieldCount).Select(column => reader.GetName(column)).ToList(); Rows = new List<List<string>>(); while (reader.Read()) Rows.Add(Enumerable.Range(0, reader.FieldCount).Select(column => reader[column]).Select(value => GetDBValue(value)).ToList()); }
private void frm_load() { try { System.Data.Common.DbDataReader dbRS = null; string strSql = "SELECT * FROM CODE_DTL WHERE CODE_TYPE = 'PRIVILEGE' ORDER BY CODE_NO "; if (!clsDB.FunOpenDB()) { throw new Exception("資料庫開啟失敗!"); } if (clsDB.FunRsSql(strSql, ref dbRS)) { while (dbRS.Read()) { cboProgStytle.Items.Add(dbRS["CODE_NO"].ToString() + ":" + dbRS["CODE_NAME"].ToString()); } dbRS.Close(); // DbDataReader Close } clsDB.FunClsDB(); } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public static void Print(string sql, DbDataReader reader) { log.Info("Results of [" + sql + "]"); string results = "\n"; if (reader.HasRows) { for (int j = 0; j < reader.FieldCount; j++) { results += reader.GetName(j); if (j < reader.FieldCount - 1) { results += "|"; } } results += "\n"; while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { results += reader.GetValue(i); if (i < reader.FieldCount - 1) { results += "|"; } } results += "\n"; } results = results.Substring(0, results.Length - 1); log.Info(results); } }
public static void Write(XmlDocument target, DbDataReader source ) { /* Style: <root> <raw><name>name1</name><index>name2</index></raw> <raw><name>name1</name><index>name2</index></raw> <raw><name>name1</name><index>name2</index></raw> </root> */ XmlNode head = target.CreateNode(XmlNodeType.Element, "head", ""); XmlNode body = target.CreateNode(XmlNodeType.Element, "body", ""); for (int i = 0; i < source.FieldCount; ++i) { string vl = source.GetName(i); string local = (string)HttpContext.GetGlobalResourceObject("local", vl); if (local != null) vl = local; Util.AddNodedText(head, "column", vl, false); } while (source.Read()) { XmlNode raw = target.CreateNode(XmlNodeType.Element, "raw", ""); for (int i = 0; i < source.FieldCount; ++i) Util.AddNodedText(raw, "value", Util.GetString( source, i ), false); body.AppendChild(raw); } target.FirstChild.AppendChild(head); target.FirstChild.AppendChild(body); }
protected void Page_Load(object sender, EventArgs e) { if (Session["uname"] == null) { Response.Write("<script>alert('系统超时或非法登录,请重新登录!');window.location.href='default.aspx';</script>"); return; } if (Session["uid"]==null) { Response.Write("<script>alert('系统超时或非法登录,请重新登录!');window.location.href='default.aspx';</script>"); return; } if (Session["ucount"] == null) { Response.Write("<script>alert('系统超时或非法登录,请重新登录!');window.location.href='default.aspx';</script>"); return; } string uid = Session["uid"].ToString(); dbh = new DbHelper(); string sql; sql = string.Format("select count(*) from userinfo where parent_id='{0}' ", uid); DbCommand dbc = dbh.GetSqlStringCommond(sql); int count=int.Parse(dbh.ExecuteScalar(dbc).ToString()); int usercount = int.Parse(Session["ucount"].ToString()); ; if (count >= usercount) { Response.Write("<script>alert('用户最大数超出设定范围,请联系管理员!');window.location.href='userlist.aspx';</script>"); return; } if (Request["id"] != null) { id = Request["id"]; sql = string.Format("select * from userinfo where id='{0}' ", Request["id"]); DbCommand dbc3 = dbh.GetSqlStringCommond(sql); dbr = dbh.ExecuteReader(dbc3); dbr.Read(); tb_guid.Text = dbr["guid"].ToString(); tb_memo.Text = dbr["memo"].ToString(); tb_buydate.Text = dbr["buy_date"].ToString(); tb_stopdate.Text = dbr["stop_date"].ToString(); tb_username.Text = dbr["user_name"].ToString(); rb_stop.Checked = dbr["active"].ToString() == "0" ? true : false; rb_active.Checked = dbr["active"].ToString() == "0" ? false : true; } else { tb_guid.ReadOnly = false; tb_buydate.Text = DateTime.Now.ToString("yyyyMMdd"); tb_stopdate.Text = DateTime.Now.AddYears(1).ToString("yyyyMMdd"); rb_stop.Checked = true; } }
public async Task <ITEM> LoadItemAsync(int?ID_ITEM) { ITEM temp = new ITEM(); var con = _db.Database.GetDbConnection(); con.Open(); using (var command = con.CreateCommand()) { // this does a few things. First it returns the given item id, the item name, and the current rating aggregate across all ratings for it. // additionally it finds the next seqential ID, and the previous seqential ID. If none found then 0. // this is important for the 'next/previous' buttons. string q = @" SELECT top(1) b.ID ,b.TITLE ,isnull(C.RATING, 0) as RATING ,( SELECT isnull(min(a.id), 0) FROM [test].[dbo].[ITEM] a where a.id > b.ID) as [NEXT] ,( SELECT isnull(max(a.id), 0) FROM [test].[dbo].[ITEM] a where a.id < b.ID) as [PREVIOUS] FROM [test].[dbo].[ITEM] b left JOIN ( SELECT y.[ID] ,AVG(CAST(z.RATING AS FLOAT)) as RATING FROM [dbo].[ITEM] y JOIN [dbo].[ITEM_RATING] z ON y.ID = z.ID_ITEM GROUP BY y.ID ) c ON B.ID = c.ID "; if (ID_ITEM != null) { // IF there is a specific ID (say from someone clicking 'next' then load that specific item's info, next, previous, name, aggregate rating, etc. q += " WHERE b.ID = @ID_ITEM "; DbParameter tempParameter = command.CreateParameter(); tempParameter.ParameterName = "@ID_ITEM"; tempParameter.Value = ID_ITEM; command.Parameters.Add(tempParameter); } command.CommandText = q; System.Data.Common.DbDataReader reader = await command.ExecuteReaderAsync(); if (reader.HasRows) { reader.Read(); temp = new ITEM(); temp.ID = (int)reader["ID"]; temp.TITLE = (string)reader["TITLE"]; temp.RATING = Convert.ToDecimal(reader["RATING"]); temp.NEXT = (int)reader["NEXT"]; temp.PREVIOUS = (int)reader["PREVIOUS"]; } reader.Dispose(); } return(temp); }
public static ResultSet Map(DbDataReader dr) { int numberOfColumns = dr.FieldCount; string[] colNames = GetColumnNames(dr, numberOfColumns); var table = new ResultSet(colNames); while (dr.Read()) { MapRow(dr, numberOfColumns, table); } return table; }
/// <summary> /// ��ȡ���ݼ��ϡ� /// </summary> /// <param name="dataReader"></param> /// <returns></returns> public static List<Article> GetArticles(DbDataReader dataReader) { // ������ List<Article> articles = new List<Article>(); while (dataReader.Read()) { Article article = new Article(); article.ArticleId = Convert.ToInt32(dataReader["ArticleId"]); article.ArticleGuid = (Guid)dataReader["ArticleGuid"]; // ��ȡ Category ���� article.Category.CategoryId = Convert.ToInt32(dataReader["CategoryId"]); article.Category.CategoryGuid = (Guid)dataReader["CategoryGuid"]; article.Category.CategoryName = Convert.ToString(dataReader["CategoryName"]); article.Category.ParentGuid = (Guid)dataReader["ParentGuid"]; article.Category.Rank = Convert.ToInt32(dataReader["Rank"]); article.Category.RecordCount = (int)dataReader[CategoryField.RecordCount]; if (dataReader["MetaKeywords"] != DBNull.Value) article.MetaKeywords = Convert.ToString(dataReader["MetaKeywords"]); if (dataReader["MetaDesc"] != DBNull.Value) article.MetaDesc = Convert.ToString(dataReader["MetaDesc"]); article.Title = Convert.ToString(dataReader["Title"]); if (dataReader["TitleColor"] != DBNull.Value) article.TitleColor = Convert.ToString(dataReader["TitleColor"]); if (dataReader["SubTitle"] != DBNull.Value) article.SubTitle = Convert.ToString(dataReader["SubTitle"]); if (dataReader["Summary"] != DBNull.Value) article.Summary = Convert.ToString(dataReader["Summary"]); if (dataReader["ContentHtml"] != DBNull.Value) article.ContentHtml = Convert.ToString(dataReader["ContentHtml"]); if (dataReader["Editor"] != DBNull.Value) article.Editor = (Guid)dataReader["Editor"]; if (dataReader["Author"] != DBNull.Value) article.Author = Convert.ToString(dataReader["Author"]); if (dataReader["Original"] != DBNull.Value) article.Original = Convert.ToString(dataReader["Original"]); article.Rank = Convert.ToInt32(dataReader["Rank"]); article.Hits = Convert.ToInt32(dataReader["Hits"]); article.Comments = Convert.ToInt32(dataReader["Comments"]); article.Votes = Convert.ToInt32(dataReader["Votes"]); article.DateCreated = Convert.ToDateTime(dataReader["DateCreated"]); articles.Add(article); } dataReader.Close(); return articles; }
public void CreateChromosomeInDb() { App.DB.Exec("INSERT INTO chromosome (name, regionId, centromereStart, centromereEnd, length, facFile, originalChromosomeId) " + "VALUES('" + NewName + "', -1, 1, 1, 0, '" + NewFileName + "', " + Source.Id + ")"); System.Data.Common.DbDataReader res = App.DB.Query("SELECT id FROM chromosome WHERE name='" + NewName + "'"); res.Read(); NewChromosomeId = Convert.ToInt32(res["id"].ToString()); }
private static IEnumerable<object[]> Read(DbDataReader reader) { while (reader.Read()) { var values = new List<object>(); for (int i = 0; i < reader.FieldCount; i++) { values.Add(reader.GetValue(i)); } yield return values.ToArray(); } }
public int EjecutarComandoSqlReturnID() { //retornara el id que fue insertado serialmente int id = 0; System.Data.Common.DbDataReader Datos = comando.ExecuteReader(); while (Datos.Read()) { id = Convert.ToInt32(Datos.GetValue(0)); } return(id); }
//Get a list of all the ratings for a given ID. public async Task <List <ITEM_RATING> > LoadItemsRatingsAsync(int ID_ITEM) { List <ITEM_RATING> temp = new List <ITEM_RATING>(); var con = _db.Database.GetDbConnection(); if (con.State != System.Data.ConnectionState.Open) { con.Open(); } using (var command = con.CreateCommand()) { string q = @"SELECT [ID_ITEM] ,[ID_USER] ,b.NAME ,[RATING] ,[DATE_ENTERED] FROM dbo.ITEM_RATING a WITH(NOLOCK) join [USER] b WITH(NOLOCK) ON a.ID_USER = b.ID WHERE [ID_ITEM] = @ID_ITEM ORDER BY DATE_ENTERED DESC"; command.CommandText = q; DbParameter tempParameter = command.CreateParameter(); tempParameter.ParameterName = "@ID_ITEM"; tempParameter.Value = ID_ITEM; command.Parameters.Add(tempParameter); System.Data.Common.DbDataReader reader = await command.ExecuteReaderAsync(); temp = new List <ITEM_RATING>(); if (reader.HasRows) { while (reader.Read()) { var row = new ITEM_RATING { ID_ITEM = (int)reader["ID_ITEM"], ID_USER = (int)reader["ID_USER"], RATING = (int)(reader["RATING"]), DATE_ENTERED = Convert.ToDateTime(reader["DATE_ENTERED"]), USER_NAME = (string)(reader["NAME"]), }; temp.Add(row); } } reader.Dispose(); } return(temp); }
private static IEnumerable <T> ExecuteReaderSync <T>(IDataReader reader, Func <IDataReader, object> func, object parameters) { using (reader) { while (reader.Read()) { yield return((T)func(reader)); } while (reader.NextResult()) { } (parameters as IParameterCallbacks)?.OnCompleted(); } }
public static void ExportExcel(DbDataReader dataReader, string strTitle, string fileFullPath, bool isAutoFit) { Workbook workbook = new Workbook(); workbook.Worksheets.Add(); object[] header = new object[dataReader.FieldCount]; bool first = true; int rowIndex = 0; Style style = new Style(); while (dataReader.Read()) { for (int i = 0; i < dataReader.FieldCount; i++) { if (first) { style.Custom = "@"; style.Font.IsBold = true; workbook.Worksheets[0].Cells[rowIndex, i].SetStyle(style); workbook.Worksheets[0].Cells[rowIndex, i].Value = dataReader.GetName(i); } style.Font.IsBold = false; if (dataReader[i] is string) { style.Custom = "@"; } else if (dataReader[i] is DateTime) { style.Custom = "yyyy-MM-dd"; } else if (dataReader[i] is decimal) { style.Custom = "####.#####"; style.ShrinkToFit = true; } workbook.Worksheets[0].Cells[rowIndex + 1, i].SetStyle(style); workbook.Worksheets[0].Cells[rowIndex + 1, i].Value = dataReader[i]; } first = false; rowIndex++; } if (isAutoFit) { workbook.Worksheets[0].AutoFitColumns(); workbook.Worksheets[0].AutoFitRows(true); } if (fileFullPath.ToLower().EndsWith(".xlsx")) workbook.Save(fileFullPath, FileFormatType.Excel2007Xlsx); else workbook.Save(fileFullPath, FileFormatType.Default); }
/// <summary> /// /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sql"></param> /// <param name="args"></param> /// <param name="commandType"></param> /// <returns></returns> public async Task <IEnumerable <T> > QueryAsync <T>(string sql, object[] args, CommandType commandType = CommandType.Text) { if (EnableAutoSelect) { sql = AutoSelectHelper.AddSelectClause <T>(_provider, sql); } var resultList = new List <T>(); await OpenSharedConnectionAsync().ConfigureAwait(false); try { using (var cmd = CreateCommand(_sharedConnection, sql, args)) { SqlDataReader r = null; var pd = PocoData.ForType(typeof(T)); try { r = await((SqlCommand)cmd).ExecuteReaderAsync().ConfigureAwait(false); OnExecutedCommand(cmd); } catch (Exception x) { if (OnException(x)) { throw; } } var factory = pd.GetFactory(cmd.CommandText, _sharedConnection.ConnectionString, 0, r.FieldCount, r) as Func <IDataReader, T>; using (r) { while (true) { try { if (!r.Read()) { break; } T poco = factory(r); resultList.Add(poco); } catch (Exception x) { if (OnException(x)) { throw; } } } } } return(resultList); } finally { CloseSharedConnection(); } }
public void BulkCopy(DbDataReader dr, string tblName, int startCol) { using (SqlBulkCopy bulkCopy = new SqlBulkCopy(adoSqlCon)) { bulkCopy.DestinationTableName = tblName; while (startCol > 0) { dr.Read(); startCol--; } bulkCopy.BulkCopyTimeout = 3600; bulkCopy.WriteToServer(dr); } }
protected override void CorePublishImpl(TableConfiguration configuration, IUnitOfWork destinationUnitOfWork, DbDataReader sourceDataReader, out long rowsCopied) { IEnumerable<IResultset> resultsets; IEnumerable<DbParameter> dbParameters; long _rowsCopied = 0; if ((object)configuration == null) throw new ArgumentNullException(nameof(configuration)); if ((object)destinationUnitOfWork == null) throw new ArgumentNullException(nameof(destinationUnitOfWork)); if ((object)sourceDataReader == null) throw new ArgumentNullException(nameof(sourceDataReader)); if ((object)this.AdapterConfiguration.AdapterSpecificConfiguration.ExecuteCommand == null) throw new InvalidOperationException(string.Format("Configuration missing: '{0}'.", nameof(this.AdapterConfiguration.AdapterSpecificConfiguration.ExecuteCommand))); if (SolderLegacyInstanceAccessor.DataTypeFascadeLegacyInstance.IsNullOrWhiteSpace(this.AdapterConfiguration.AdapterSpecificConfiguration.ExecuteCommand.CommandText)) throw new InvalidOperationException(string.Format("Configuration missing: '{0}.{1}'.", nameof(this.AdapterConfiguration.AdapterSpecificConfiguration.ExecuteCommand), nameof(this.AdapterConfiguration.AdapterSpecificConfiguration.ExecuteCommand.CommandText))); while (sourceDataReader.Read()) { dbParameters = this.AdapterConfiguration.AdapterSpecificConfiguration.ExecuteCommand.GetDbDataParameters(destinationUnitOfWork); dbParameters = dbParameters.Select(p => { // prevent modified closure bug var _sourceDataReader = sourceDataReader; // lazy load p.Value = _sourceDataReader[p.SourceColumn]; return p; }); resultsets = destinationUnitOfWork.ExecuteResultsets(this.AdapterConfiguration.AdapterSpecificConfiguration.ExecuteCommand.CommandType ?? CommandType.Text, this.AdapterConfiguration.AdapterSpecificConfiguration.ExecuteCommand.CommandText, dbParameters); resultsets.ToArray(); _rowsCopied++; } rowsCopied = _rowsCopied; Console.WriteLine("DESTINATION (update): rowsCopied={0}", rowsCopied); }
public static IEnumerable <object[]> ExecuteQuery( this ExtendDataContext ctx, string query) { using (System.Data.Common.DbCommand cmd = ctx.Connection.CreateCommand()) { cmd.CommandText = query; ctx.Connection.Open(); using (System.Data.Common.DbDataReader rdr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)) { while (rdr.Read()) { object[] res = new object[rdr.FieldCount]; rdr.GetValues(res); yield return(res); } } } }
public static void Write( Stream output, DbDataReader source) { /* Style: value1row1 value2row1 value1row2 value2row2 */ TextWriter writer = new StreamWriter(output); while (source.Read()) { for (int i = 0; i < source.FieldCount; ++i) writer.Write(source.GetString(i)); writer.WriteLine(); } writer.Flush(); }
public DataResult(DbDataReader reader) { ColumnNames = Enumerable.Range(0, reader.FieldCount) .Select(x => reader.GetName(x)) .ToList(); Rows = new List<RowData>(); while (reader.Read()) { Dictionary<string, string> rowData = new Dictionary<string, string>(); for (var i = 0; i < reader.FieldCount; i++) { if (reader[i].GetType() == typeof(DateTime)) { // Use ISO time rowData[ColumnNames[i]] = ((DateTime)reader[i]).ToString("s"); } else { rowData[ColumnNames[i]] = reader[i].ToString(); } } Rows.Add(rowData); } }
public DataTable(DbDataReader reader) { this.fields = new List<string>(); this.data = new List<object[]>(); for (int i = 0; i < reader.FieldCount; i++) { fields.Add(reader.GetName(i)); } while (reader.Read()) { object[] values = new object[reader.FieldCount]; for (int i = 0; i < fields.Count; i++) { values[i] = reader.GetValue(reader.GetOrdinal(fields[i])); } data.Add(values); } }
private Dictionary<int, Medicine> Read(DbDataReader reader, IDatabase db) { Dictionary<int, Medicine> medicines = new Dictionary<int, Medicine>(); while (reader.Read()) { Medicine m = new Medicine(); m.Id = reader.GetInt32(0); m.Name = reader.GetString(1); m.Description = reader.GetString(2); m.PackageSize = reader.GetInt32(3); m.Price = Decimal.ToSingle(reader.GetDecimal(4)); m.Allergens = this.ReadAllergens(m, db); medicines.Add(m.Id, m); } return medicines; }
/// <summary> /// Process the search /// </summary> /// <param name="dbCtx">EF dbContext</param> /// <param name="tableName">the name of the table </param> /// <param name="primaryKeyColumnName">the primary key of the table</param> /// <param name="searchText">the FULLTEXT formated query</param> /// <param name="columnNames"></param> ///<param name="booleanMode">if true process as a boolean search, otherwise its a Natural Language searc</param> ///<param name="queryExpansion">If its Natural Language, also add the query expansion flag</param> ///<param name="orderByScore">order the results by score</param> /// <returns></returns> private static ArrayList ExecuteFullTextSearch(DbContext dbCtx, string tableName, string primaryKeyColumnName, string searchText, string[] columnNames, bool booleanMode, bool queryExpansion, bool orderByScore) { ArrayList returnValue = new ArrayList(); string columnNameText = string.Join(",", columnNames);; string commandMod = "IN NATURAL LANGUAGE MODE"; if (booleanMode) { commandMod = "IN BOOLEAN MODE"; } if (queryExpansion) { commandMod = "IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION"; } searchText = searchText.Trim(new char[] { '\'', ' ' }); string matchText = string.Format("MATCH ({0}) AGAINST ('{1}' {2})", columnNameText, searchText, commandMod); string sqlQuery = null; if (orderByScore) { sqlQuery = string.Format("SELECT {0}, {2} AS score FROM {1} WHERE {2} ORDER BY score DESC;", primaryKeyColumnName, tableName, matchText); } else { sqlQuery = string.Format("SELECT {0} FROM {1} WHERE {2};", primaryKeyColumnName, tableName, matchText); } using (var dataReader = dbCtx.Database.ExecuteSqlQuery(sqlQuery)) { System.Data.Common.DbDataReader dbDataReader = dataReader.DbDataReader; if (null != dbDataReader) { while (dbDataReader.Read()) { returnValue.Add(dbDataReader[0]); } } } return(returnValue); }
public static List<object> GetColumnFromDataReader(DbDataReader dr, string columnName) { List<object> column = new List<object>(); try { while (dr.Read()) { column.Add(dr[columnName]); } } finally { if (dr != null && !dr.IsClosed) { dr.Close(); // Data Reader is done (read only, forward only) dr.Dispose(); } } return column; }
/// <summary> /// This may be useful if you want to serialize a full table as json. Usually, a IDictionary object is serialized as JSON object (eg Newtonsoft.Json) /// It can be used with dynmaic as well (http://stackoverflow.com/questions/18290852/generate-dynamic-object-from-dictionary-with-c-reflection) /// </summary> /// <typeparam name="T">The type of the IDictionary, usually Dictionary<string,object></typeparam> /// <param name="table">The DbDataReader to convert to a Dictionary List</param> /// <param name="inputList">An existing list to append the items. Can be null to create a new list</param> /// <returns>A List containing the Objects</returns> public static List <T> AsDictionaryList <T>(this System.Data.Common.DbDataReader table, List <T>?inputList = null) where T : IDictionary <string, object?>, new() { if (inputList == null) { inputList = new List <T>(); } int columnCount = table.FieldCount; while (table.Read()) { T item = new T(); foreach (var col in Enumerable.Range(0, columnCount)) { item.Add(table.GetName(col), table.IsDBNull(col) ? null : table[col]); } inputList.Add(item); } return(inputList); }
public static List<Category> GetCategorys(DbDataReader dataReader) { List<Category> categorys = new List<Category>(); while (dataReader.Read()) { Category category = new Category(); category.CategoryId = Convert.ToInt32(dataReader[CategoryField.CategoryId]); category.CategoryGuid = (Guid)dataReader[CategoryField.CategoryGuid]; category.CategoryName = Convert.ToString(dataReader[CategoryField.CategoryName]); category.ParentGuid = (Guid)dataReader[CategoryField.ParentGuid]; category.ParentCategoryName = dataReader[CategoryField.ParentCategoryName].ToString(); category.Rank = Convert.ToInt32(dataReader[CategoryField.Rank]); category.ArticleType = (byte)dataReader[CategoryField.ArticleType]; if (dataReader[CategoryField.ThumbnailWidth] != DBNull.Value) category.ThumbnailWidth = (int)dataReader[CategoryField.ThumbnailWidth]; if (dataReader[CategoryField.ThumbnailHeight] != DBNull.Value) category.ThumbnailHeight = (int)dataReader[CategoryField.ThumbnailHeight]; category.RecordCount = (int)dataReader[CategoryField.RecordCount]; categorys.Add(category); } dataReader.Close(); return categorys; }
public String ViewTableTxt(String TableName) { ESData.GetInst.Open_Conn(); String txt = "TabeName:" + TableName + "\n"; System.Data.Common.DbDataReader dr = ESData.GetInst.Reader(String.Format("select * from {0};", TableName)); for (int i = 0; i < dr.FieldCount; i++) { txt += dr.GetName(i) + "\t "; } txt += "\n"; //if (dr.HasRows) while (dr.Read()) { for (int i = 0; i < dr.FieldCount; i++) { try{ txt += dr[i].ToString() + "\t "; } catch { MessageBox.Show(dr[0].ToString()); } } txt += "\n"; } dr.Close(); dr.Dispose(); return(txt); }
static void ReadData(DbDataReader reader) { while (reader.Read()) { Console.WriteLine("\nExpected - {0} , Got - {1}", "HR", reader.Get<string>("name")); break; } }
private List<IUserClaim> LoadClaimListFromReader(DbDataReader reader) { List<IUserClaim> userClaimList = new List<IUserClaim>(); using (reader) { while (reader.Read()) { UserClaim userClaim = new UserClaim(); userClaim.LoadFromReader(reader); userClaimList.Add(userClaim); } } return userClaimList; }
// // Render row values based on field settings // public void RenderRow() { // Initialize urls // Row Rendering event RegistrosVehiculos.Row_Rendering(); // // Common render codes for all row types // // IdRegistroVehiculo // IdTipoVehiculo // Placa // FechaIngreso // FechaSalida // Observaciones // // View Row // if (RegistrosVehiculos.RowType == EW_ROWTYPE_VIEW) { // View row // IdRegistroVehiculo RegistrosVehiculos.IdRegistroVehiculo.ViewValue = RegistrosVehiculos.IdRegistroVehiculo.CurrentValue; RegistrosVehiculos.IdRegistroVehiculo.ViewCustomAttributes = ""; // IdTipoVehiculo if (ew_NotEmpty(RegistrosVehiculos.IdTipoVehiculo.CurrentValue)) { sFilterWrk = "[IdTipoVehiculo] = " + ew_AdjustSql(RegistrosVehiculos.IdTipoVehiculo.CurrentValue) + ""; sSqlWrk = "SELECT [TipoVehiculo] FROM [dbo].[TiposVehiculos]"; sWhereWrk = ""; if (sFilterWrk != "") { if (sWhereWrk != "") sWhereWrk += " AND "; sWhereWrk += "(" + sFilterWrk + ")"; } if (sWhereWrk != "") sSqlWrk += " WHERE " + sWhereWrk; sSqlWrk += " ORDER BY [TipoVehiculo]"; drWrk = Conn.GetTempDataReader(sSqlWrk); if (drWrk.Read()) { RegistrosVehiculos.IdTipoVehiculo.ViewValue = drWrk["TipoVehiculo"]; } else { RegistrosVehiculos.IdTipoVehiculo.ViewValue = RegistrosVehiculos.IdTipoVehiculo.CurrentValue; } Conn.CloseTempDataReader(); } else { RegistrosVehiculos.IdTipoVehiculo.ViewValue = System.DBNull.Value; } RegistrosVehiculos.IdTipoVehiculo.ViewCustomAttributes = ""; // Placa RegistrosVehiculos.Placa.ViewValue = RegistrosVehiculos.Placa.CurrentValue; RegistrosVehiculos.Placa.ViewCustomAttributes = ""; // FechaIngreso RegistrosVehiculos.FechaIngreso.ViewValue = RegistrosVehiculos.FechaIngreso.CurrentValue; RegistrosVehiculos.FechaIngreso.ViewValue = ew_FormatDateTime(RegistrosVehiculos.FechaIngreso.ViewValue, 7); RegistrosVehiculos.FechaIngreso.ViewCustomAttributes = ""; // FechaSalida RegistrosVehiculos.FechaSalida.ViewValue = RegistrosVehiculos.FechaSalida.CurrentValue; RegistrosVehiculos.FechaSalida.ViewValue = ew_FormatDateTime(RegistrosVehiculos.FechaSalida.ViewValue, 7); RegistrosVehiculos.FechaSalida.ViewCustomAttributes = ""; // Observaciones RegistrosVehiculos.Observaciones.ViewValue = RegistrosVehiculos.Observaciones.CurrentValue; RegistrosVehiculos.Observaciones.ViewCustomAttributes = ""; // View refer script // IdTipoVehiculo RegistrosVehiculos.IdTipoVehiculo.LinkCustomAttributes = ""; RegistrosVehiculos.IdTipoVehiculo.HrefValue = ""; RegistrosVehiculos.IdTipoVehiculo.TooltipValue = ""; // Placa RegistrosVehiculos.Placa.LinkCustomAttributes = ""; RegistrosVehiculos.Placa.HrefValue = ""; RegistrosVehiculos.Placa.TooltipValue = ""; // Observaciones RegistrosVehiculos.Observaciones.LinkCustomAttributes = ""; RegistrosVehiculos.Observaciones.HrefValue = ""; RegistrosVehiculos.Observaciones.TooltipValue = ""; // // Add Row // } else if (RegistrosVehiculos.RowType == EW_ROWTYPE_ADD) { // Add row // IdTipoVehiculo RegistrosVehiculos.IdTipoVehiculo.EditCustomAttributes = ""; sFilterWrk = ""; sSqlWrk = "SELECT [IdTipoVehiculo], [TipoVehiculo] AS [DispFld], '' AS [Disp2Fld], '' AS [Disp3Fld], '' AS [Disp4Fld], '' AS [SelectFilterFld] FROM [dbo].[TiposVehiculos]"; sWhereWrk = ""; if (sFilterWrk != "") { if (sWhereWrk != "") sWhereWrk += " AND "; sWhereWrk += "(" + sFilterWrk + ")"; } if (sWhereWrk != "") sSqlWrk += " WHERE " + sWhereWrk; sSqlWrk += " ORDER BY [TipoVehiculo]"; alwrk = Conn.GetRows(sSqlWrk); alwrk.Insert(0, new OrderedDictionary()); ((OrderedDictionary)alwrk[0]).Add("0", ""); ((OrderedDictionary)alwrk[0]).Add("1", Language.Phrase("PleaseSelect")); RegistrosVehiculos.IdTipoVehiculo.EditValue = alwrk; // Placa RegistrosVehiculos.Placa.EditCustomAttributes = ""; RegistrosVehiculos.Placa.EditValue = ew_HtmlEncode(RegistrosVehiculos.Placa.CurrentValue); // Observaciones RegistrosVehiculos.Observaciones.EditCustomAttributes = ""; RegistrosVehiculos.Observaciones.EditValue = ew_HtmlEncode(RegistrosVehiculos.Observaciones.CurrentValue); // Edit refer script // IdTipoVehiculo RegistrosVehiculos.IdTipoVehiculo.HrefValue = ""; // Placa RegistrosVehiculos.Placa.HrefValue = ""; // Observaciones RegistrosVehiculos.Observaciones.HrefValue = ""; } if (RegistrosVehiculos.RowType == EW_ROWTYPE_ADD || RegistrosVehiculos.RowType == EW_ROWTYPE_EDIT || RegistrosVehiculos.RowType == EW_ROWTYPE_SEARCH) { // Add / Edit / Search row RegistrosVehiculos.SetupFieldTitles(); } // Row Rendered event if (RegistrosVehiculos.RowType != EW_ROWTYPE_AGGREGATEINIT) RegistrosVehiculos.Row_Rendered(); }
// Set a DbDataReader to a Structured+MultiValued setter (table type) // Assumes metaData correctly describes the reader's shape, and consumes only the current resultset private static void SetDbDataReader_Unchecked( SmiEventSink_Default sink, SmiTypedGetterSetter setters, int ordinal, SmiMetaData metaData, DbDataReader value ) { // Get the target gettersetter setters = setters.GetTypedGetterSetter(sink, ordinal); sink.ProcessMessagesAndThrow(); // Iterate over all rows in the current set of results while (value.Read()) { setters.NewElement(sink); sink.ProcessMessagesAndThrow(); FillCompatibleSettersFromReader(sink, setters, metaData.FieldMetaData, value); } setters.EndElements(sink); sink.ProcessMessagesAndThrow(); }
private List<ICurrency> LoadCurrencyListFromReader(DbDataReader reader) { List<ICurrency> currencyList = new List<ICurrency>(); using(reader) { while (reader.Read()) { Currency currency = new Currency(); LoadFromReader(reader, currency); currencyList.Add(currency); } } return currencyList; }
private List<ILanguage> LoadLanguageListFromReader(DbDataReader reader) { List<ILanguage> languageList = new List<ILanguage>(); using(reader) { while (reader.Read()) { Language language = new Language(); LoadFromReader(reader, language); languageList.Add(language); } } return languageList; }
private List<IGeoCountry> LoadCountryListFromReader(DbDataReader reader) { List<IGeoCountry> geoCountryList = new List<IGeoCountry>(); using(reader) { while (reader.Read()) { GeoCountry geoCountry = new GeoCountry(); LoadFromReader(reader, geoCountry); geoCountryList.Add(geoCountry); } } return geoCountryList; }
private List<IGeoZone> LoadGeoZoneListFromReader(DbDataReader reader) { List<IGeoZone> geoZoneList = new List<IGeoZone>(); using(reader) { while (reader.Read()) { GeoZone geoZone = new GeoZone(); LoadFromReader(reader, geoZone); geoZoneList.Add(geoZone); } } return geoZoneList; }
/// <summary> /// 由DbDataReader得到分页泛型数据列表 /// </summary> private List<ZSystem.Model.t_Operation> GetPageList(DbDataReader dr, int first, int count) { List<ZSystem.Model.t_Operation> lst = new List<ZSystem.Model.t_Operation>(); for (int i = 0; i < first; i++) { if (!dr.Read()) { return lst; } } int resultsFetched = 0; while (resultsFetched < count && dr.Read()) { lst.Add(GetModel(dr)); resultsFetched++; } return lst; }
private void CreateChromosome() { App.DB.OpenFast(); SQLiteCommand command = App.DB.CreateCommand(); SQLiteTransaction transaction = App.DB.BeginTransaction(); command.Transaction = transaction; String sql = "INSERT INTO exon (exonId, name, geneId, start, end, strand, isVirtual, chromosomeId) " + "VALUES(@exonId, @name, @geneId, @start, @end, @strand, 1, " + NewChromosomeId + ")"; command.CommandText = sql; int posOld = SourceRange.Start; int posNew = 1; int curGene = 0; byte[] buffer; FileStream fNew = File.Create(NewFilePath); do { App.Status("Writing Gene " + (curGene + 1) + " of " + Genes.Count); Gene geneOld = null; Gene geneNew = null; // WriteNonCodingSequence int nonCodingLength = 0; if (curGene < Genes.Count) { geneOld = Genes[curGene]; geneNew = Genes[PositionsShuffled[curGene]]; nonCodingLength = geneOld.Start - posOld; if (nonCodingLength < 0) { /* App.Log("Overlapping Gene: " + geneOld.Name + " - skipping"); * curGene++; * continue;*/ int diff = posOld - geneOld.Start; App.Log("Overlapping Gene: " + geneOld.Name + " - " + diff + "bp"); posOld = geneOld.Start; nonCodingLength = 0; } } else { nonCodingLength = SourceRange.End - posOld; } if (nonCodingLength > 0) { Source.ReadBuffer(posOld, nonCodingLength, out buffer); // App.Log("Reading Non Coding Sequence from pos " + posOld + ", length " + nonCodingLength); fNew.Write(buffer, 0, buffer.Length); } posOld += nonCodingLength; posNew += nonCodingLength; // App.Log("# " + curGene + " NC Length " + nonCodingLength); if (curGene >= Genes.Count) { break; } bool switchStrand = App.Random.NextBool(); // switchStrand = false; // WriteNewGene Source.ReadBuffer(geneNew.Start, geneNew.Length, out buffer); //App.Log("Reading Gene New from pos " + geneNew.Start + ", length " + geneNew.Length); Gene geneNewNull = new Gene(geneNew); geneNewNull.Range = new Range(posNew, posNew + geneNew.Length); if (switchStrand) { Sequencer.InvCompl(ref buffer); if (geneNewNull.Strand == 1) { geneNewNull.Strand = -1; } else { geneNewNull.Strand = 1; } // App.Log("Switched Strands"); } fNew.Write(buffer, 0, buffer.Length); // Exons System.Data.Common.DbDataReader res = App.DB.Query("SELECT * FROM exon WHERE geneId=" + geneNew.Id + " and chromosomeId = " + Source.Id + " ORDER BY start ASC"); while (res.Read()) { Exon exonOld = new Exon(res, Source); Exon exonNew = new Exon(exonOld); int bpFromGeneStart = exonNew.Start - geneNew.Start; int newStart = geneNewNull.Start + bpFromGeneStart; int newEnd = newStart + exonOld.Length; if (switchStrand) { newStart = geneNewNull.End - bpFromGeneStart - exonOld.Length; newEnd = geneNewNull.End - bpFromGeneStart; } exonNew.Range = new Range(newStart, newEnd); exonNew.Strand = geneNewNull.Strand; command.Parameters.AddWithValue("@exonId", exonNew.Id); command.Parameters.AddWithValue("@geneId", geneNewNull.Id); command.Parameters.AddWithValue("@start", newStart); command.Parameters.AddWithValue("@end", newEnd); command.Parameters.AddWithValue("@strand", exonNew.Strand); command.Parameters.AddWithValue("@name", exonNew.Name); command.ExecuteNonQuery(); } App.Log("# " + curGene + " " + geneNewNull.Name + "(ID " + geneNewNull.Id + ") -> " + geneOld.Name + " - Pos: " + geneNewNull.Start + " Strand: " + geneOld.Strand + (switchStrand ? " (reversed)" : "")); // App.Log("# " + curGene + " New Gene Length " + geneNew.Length); // App.Log("# " + curGene + " posNew " + posNew + " - posOld "+posOld); // App.Log("# " + curGene + " Old Gene Distance " + (Genes[curGene+1].Start-geneOld.Start)); // Next Gene long filePos = fNew.Position; posNew += geneNewNull.Length; posOld += geneOld.Length; App.Assert(filePos == (posNew - 1)); curGene++; App.LogScroll(); // if (curGene > 40) break; }while (posOld <= SourceRange.End); fNew.Close(); transaction.Commit(); command.Dispose(); App.DB.Exec("UPDATE chromosome SET length=" + posNew + " WHERE id=" + NewChromosomeId); App.DB.Close(); App.Status(""); }
static void ReadDataMulti(DbDataReader reader) { var ctr = 1; while (reader.HasRows) { while (reader.Read()) { Console.WriteLine(ctr == 1 ? string.Format("\nExpected - {0} , Got - {1}", "sfk shan", reader.Get<string>("Name")) //First result set - Employee : string.Format("\nExpected - {0} , Got - {1}", "HR", reader.Get<string>("name")));//Second Result set - Department break; } ctr++; reader.NextResult(); } //Note : for test purpose it is skipped to iterate the rest rows }
ProfileInfoCollection BuildProfileInfoCollection (DbDataReader reader, out int totalRecords) { ProfileInfoCollection pic = new ProfileInfoCollection (); while (reader.Read ()) { ProfileInfo pi = ReadProfileInfo (reader); if (pi != null) pic.Add (pi); } totalRecords = 0; if (reader.NextResult ()) { if (reader.Read ()) totalRecords = reader.GetInt32 (0); } return pic; }
public IEnumerable <Bunk> QueryBunkListView(DataTransferObject.Foundation.BunkQueryCondition condition, Core.Pagination pagination) { var result = new List <Bunk>(); using (var dbOperator = new DbOperator(Provider, ConnectionString)) { if (!string.IsNullOrWhiteSpace(condition.Airline)) { dbOperator.AddParameter("@iAirline", condition.Airline.Trim()); } if (!string.IsNullOrWhiteSpace(condition.Departure)) { dbOperator.AddParameter("@iDeparture", condition.Departure.Trim()); } if (!string.IsNullOrWhiteSpace(condition.Arrival)) { dbOperator.AddParameter("@iArrival", condition.Arrival.Trim()); } if (!string.IsNullOrWhiteSpace(condition.BunkCode)) { dbOperator.AddParameter("@iBunkCode", condition.BunkCode.Trim()); } if (condition.BunkType.HasValue) { dbOperator.AddParameter("@iBunkType", (int)condition.BunkType); } if (condition.VoyageType.HasValue) { dbOperator.AddParameter("@iVoyageType", (byte)condition.VoyageType); } if (condition.FlightBeginDate.HasValue) { dbOperator.AddParameter("@iFlightBeginDate", condition.FlightBeginDate.Value.Date); } if (condition.FlightEndDate.HasValue) { dbOperator.AddParameter("@iFlightEndDate", condition.FlightEndDate.Value.Date); } if (condition.Status.HasValue) { dbOperator.AddParameter("@iStatus", condition.Status); } if (pagination != null) { dbOperator.AddParameter("@iPageSize", pagination.PageSize); dbOperator.AddParameter("@iPageIndex", pagination.PageIndex); } System.Data.Common.DbParameter totalCount = dbOperator.AddParameter("@oTotalCount"); totalCount.DbType = System.Data.DbType.Int32; totalCount.Direction = System.Data.ParameterDirection.Output; using (System.Data.Common.DbDataReader reader = dbOperator.ExecuteReader("dbo.P_QueryBunks", System.Data.CommandType.StoredProcedure)) { Guid?previousId = null; Bunk bunk = null; while (reader.Read()) { var currentId = reader.GetGuid(1); if (bunk == null || previousId.Value != currentId) { bunk = null; } var bunkType = (BunkType)reader.GetInt32(0); switch (bunkType) { case BunkType.Economic: bunk = loadEconomicBunk(bunk, reader); break; case BunkType.FirstOrBusiness: bunk = loadFirstBusinessBunk(bunk, reader); break; case BunkType.Promotion: bunk = loadPromotionBunk(bunk, reader); break; case BunkType.Production: bunk = loadProductionBunk(bunk, reader); break; case BunkType.Transfer: bunk = loadTransferBunk(bunk, reader); break; case BunkType.Free: bunk = loadFreeBunk(bunk, reader); break; case BunkType.Team: bunk = loadTeamBunk(bunk, reader); break; } bunk.VoyageType = (VoyageTypeValue)reader.GetByte(19); bunk.TravelType = (TravelTypeValue)reader.GetByte(20); bunk.PassengerType = (PassengerTypeValue)reader.GetByte(21); bunk.RefundRegulation = reader.IsDBNull(8) ? string.Empty : reader.GetString(8); bunk.ChangeRegulation = reader.IsDBNull(16) ? string.Empty : reader.GetString(16); bunk.EndorseRegulation = reader.IsDBNull(17) ? string.Empty : reader.GetString(17); bunk.Remarks = reader.IsDBNull(18) ? string.Empty : reader.GetString(18); if (!previousId.HasValue || previousId.Value != currentId) { result.Add(bunk); previousId = currentId; } } } if (pagination.GetRowCount) { pagination.RowCount = (int)totalCount.Value; } } return(result); }