public static YellowstonePathology.Business.ClientOrder.Model.ClientOrder BuildClientOrder(SqlDataReader dr) { YellowstonePathology.Business.ClientOrder.Model.ClientOrder clientOrder = null; Nullable<int> panelSetId = null; while (dr.Read()) { if (dr["PanelSetId"] != DBNull.Value) { panelSetId = Convert.ToInt32(dr["PanelSetId"].ToString()); } } clientOrder = YellowstonePathology.Business.ClientOrder.Model.ClientOrderFactory.GetClientOrder(panelSetId); dr.NextResult(); while (dr.Read()) { YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter propertyWriter = new Persistence.SqlDataReaderPropertyWriter(clientOrder, dr); propertyWriter.WriteProperties(); } dr.NextResult(); while (dr.Read()) { YellowstonePathology.Business.Client.Model.ClientLocation clientLocation = new YellowstonePathology.Business.Client.Model.ClientLocation(); YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter propertyWriter = new YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter(clientLocation, dr); propertyWriter.WriteProperties(); clientOrder.ClientLocation = clientLocation; } return clientOrder; }
protected void Page_Load(object sender, EventArgs e) { conn = new SqlConnection(ConfigurationManager.AppSettings["strConLocal"]); comm = new SqlCommand("InterventionPlan_Select", conn); comm.CommandType = CommandType.StoredProcedure; comm.Parameters.AddWithValue("@PortTypeID", _PortTypeID.ToString()); comm.Parameters.AddWithValue("@PortfolioID", "0"); comm.Parameters.AddWithValue("@InterventionPlanID", _PlanID.ToString()); conn.Open(); drData = comm.ExecuteReader(); rptDelivery.DataSource = drData; rptDelivery.DataBind(); drData.NextResult(); rptStrategy.DataSource = drData; rptStrategy.DataBind(); drData.NextResult(); rptPerson.DataSource = drData; rptPerson.DataBind(); drData.NextResult(); rptUpdate.DataSource = drData; rptUpdate.DataBind(); drData.NextResult(); }
public static Dictionary <string, List <Product> > GetSimilarProducts(string categoryId, string colorId, long productId, long userId, string db) { Dictionary <string, List <Product> > similarProducts = new Dictionary <string, List <Product> >(); string query = "EXEC [stp_SS_GetSimilarProducts] @categoryId=N'" + categoryId + "', @colorId=N'" + colorId + "', @productId=" + productId + ",@userId=" + userId; SqlConnection myConnection = new SqlConnection(db); try { myConnection.Open(); using (SqlDataAdapter adp = new SqlDataAdapter(query, myConnection)) { SqlCommand cmd = adp.SelectCommand; cmd.CommandTimeout = 300000; System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader(); List <Product> exactSimilarproducts = new List <Product>(); while (dr.Read()) { exactSimilarproducts.Add(GetProductFromSqlDataReader(dr)); } similarProducts.Add("exact", exactSimilarproducts); //Same Brand dr.NextResult(); List <Product> sameBrandProducts = new List <Product>(); while (dr.Read()) { sameBrandProducts.Add(GetProductFromSqlDataReader(dr)); } similarProducts.Add("brand", sameBrandProducts); //Same Color dr.NextResult(); List <Product> sameColorProducts = new List <Product>(); while (dr.Read()) { sameColorProducts.Add(GetProductFromSqlDataReader(dr)); } similarProducts.Add("color", sameColorProducts); } } finally { myConnection.Close(); } return(similarProducts); }
public int isEmailExisted(string eMail) { try { SqlInit(); cmd.CommandText = "SELECT * FROM users where email='" + eMail; dr = cmd.ExecuteReader(); if (dr.NextResult()) { if (dr.GetString(2) == eMail) { SqlExit(); //name existed return 1; } } } catch (SqlException e) { //error SqlExit(); return 666; } SqlExit(); //name can use return 0; }
public int isNameExisted(string uName) { try { SqlInit(); cmd.CommandText = "SELECT * FROM users where email='" + uName; dr = cmd.ExecuteReader(); if(dr.NextResult()) { if(dr.GetString(1)==uName) { SqlExit(); //email existed return 1; } } } catch (SqlException e) { //error SqlExit(); return 666; } SqlExit(); //email can use return 0; }
protected override Exception ProcessSqlResult(SqlDataReader reader) { Exception nextResultSet = StoreUtilities.GetNextResultSet(base.InstancePersistenceCommand.Name, reader); if (nextResultSet == null) { reader.NextResult(); List<IDictionary<XName, object>> parameters = new List<IDictionary<XName, object>>(); if (reader.Read()) { do { IDictionary<XName, object> item = new Dictionary<XName, object>(); item.Add(WorkflowServiceNamespace.SiteName, reader.GetString(0)); item.Add(WorkflowServiceNamespace.RelativeApplicationPath, reader.GetString(1)); item.Add(WorkflowServiceNamespace.RelativeServicePath, reader.GetString(2)); parameters.Add(item); } while (reader.Read()); } else { base.Store.UpdateEventStatus(false, InstancePersistenceEvent<HasActivatableWorkflowEvent>.Value); base.StoreLock.InstanceDetectionTask.ResetTimer(false); } base.InstancePersistenceContext.QueriedInstanceStore(new ActivatableWorkflowsQueryResult(parameters)); } return nextResultSet; }
private void SendAllRecordsToGraphite(string path, SqlDataReader myReader) { do { this.SendRecordToGraphite(path, myReader); } while (myReader.NextResult()); }
protected void Page_Load(object sender, EventArgs e) { conn = new SqlConnection(ConfigurationManager.AppSettings["strConLocal"]); comm = new SqlCommand("InterventionPlan_Select", conn); comm.CommandType = CommandType.StoredProcedure; comm.Parameters.AddWithValue("@PortTypeID", _PortTypeID.ToString()); comm.Parameters.AddWithValue("@PortfolioID", "0"); comm.Parameters.AddWithValue("@InterventionPlanID", _PlanID.ToString()); conn.Open(); drData = comm.ExecuteReader(); rptDelivery.DataSource = drData; rptDelivery.DataBind(); drData.NextResult(); rptStrategy.DataSource = drData; rptStrategy.DataBind(); drData.NextResult(); rptPerson.DataSource = drData; rptPerson.DataBind(); drData.NextResult(); rptUpdate.DataSource = drData; rptUpdate.DataBind(); drData.NextResult(); drData.Read(); if (drData["InterventionContAreaText"].ToString() != "") strContentArea = drData["InterventionContAreaName_EN"].ToString() + ": " + drData["InterventionContAreaText"].ToString(); else strContentArea = drData["InterventionContAreaName_EN"].ToString(); if (Convert.ToBoolean(drData["IsDiscontinued"])) strStatus = "Discontinued"; else if (drData["AEDate"].ToString() != "") strStatus = "Completed"; else strStatus = "Active"; if (drData["InterventionInstrText"].ToString() != "") strInstrument = drData["InterventionInstrName_EN"].ToString() + ": " + drData["InterventionInstrText"].ToString(); else strInstrument = drData["InterventionInstrName_EN"].ToString(); }
protected void Page_Load(object sender, EventArgs e) { conn = new SqlConnection(ConfigurationManager.AppSettings["strConLocal"]); comm = new SqlCommand("InterventionPlanSchool_Select", conn); comm.CommandType = CommandType.StoredProcedure; comm.Parameters.AddWithValue("@InterventionPlanID", _PlanID.ToString()); conn.Open(); drData = comm.ExecuteReader(); drData.NextResult(); rptCurricular.DataSource = drData; rptCurricular.DataBind(); drData.NextResult(); rptBehavioral.DataSource = drData; rptBehavioral.DataBind(); drData.NextResult(); }
public static Exception CheckRemainingResultSetForErrors(XName commandName, SqlDataReader reader) { Exception nextResultSet = null; do { nextResultSet = GetNextResultSet(commandName, reader); } while ((nextResultSet == null) && reader.NextResult()); return nextResultSet; }
public static Exception CheckRemainingResultSetForErrors(XName commandName, SqlDataReader reader) { Exception returnException = null; do { returnException = StoreUtilities.GetNextResultSet(commandName, reader); } while (returnException == null && reader.NextResult()); return returnException; }
private static bool AdvanceToNextRow(SqlDataReader reader) { do { if (reader.Read()) { return true; } } while (reader.NextResult()); return false; }
public static Collection<Post> CreatePostsFromReader(SqlDataReader reader) { // First result set is the postcategories. Dictionary<int, Category> postcats = new Dictionary<int, Category>(); if (reader.HasRows) { while (reader.Read()) { // Oops - key can't be PostId as key has to be unique. Need to use a different collection.. // .. for now, make a hash of the post and category IDs. int categoryId = Convert.ToInt32(reader["categoryId"]); int postHash = (Convert.ToInt32(reader["PostId"]) * 10000) + categoryId; postcats.Add(postHash, new Category(categoryId, Convert.ToString(reader["Name"]), Convert.ToString(reader["Slug"]))); } } // Second resultset is the post(s) reader.NextResult(); Collection<Post> postlist = new Collection<Post>(); if (reader.HasRows) { while (reader.Read()) { Post p = new Post(); p.Id = Convert.ToInt32(reader["Id"]); p.Title = Convert.ToString(reader["Title"]); p.Postdate = Convert.ToDateTime(reader["PostDate"]); p.Body = Convert.ToString(reader["Body"]); p.Slug = Convert.ToString(reader["Slug"]); p.CommentCount = Convert.ToInt32(reader["CommentCount"]); p.Published = Convert.ToBoolean(reader["Published"]); p.Categories = new Collection<Category>(); // .. then the categories. IEnumerable<Category> cats = from entry in postcats where ((entry.Key / 10000) == p.Id) select entry.Value; foreach (Category cat in cats) { p.Categories.Add(cat); } postlist.Add(p); } } return postlist; }
public static void BuildClientOrderCollection(SqlDataReader dr, YellowstonePathology.Business.ClientOrder.Model.ClientOrderCollection clientOrderCollection) { int clientOrderIdCount = 0; while (dr.Read()) { if (dr["ClientOrderIdCount"] != DBNull.Value) { clientOrderIdCount = Convert.ToInt32(dr["ClientOrderIdCount"].ToString()); } } for (int i = 0; i < clientOrderIdCount; i++) { dr.NextResult(); YellowstonePathology.Business.ClientOrder.Model.ClientOrder clientOrder = BuildClientOrder(dr); dr.NextResult(); BuildClientOrderDetailCollection(clientOrder.ClientOrderDetailCollection, dr); if (clientOrder.ClientOrderId != null) { clientOrderCollection.Add(clientOrder); } } }
/// <summary> /// [static] PAB.Data.Utils.DataReaderConverters.ConvertDataReaderToDataSet: /// Converts one or more resultsets returned in a SqlDataReader to a DataSet /// </summary> /// <param name="reader">SqlDataReader</param> /// <returns>System.Data.DataSet</returns> public static DataSet ConvertDataReaderToDataSet(SqlDataReader reader) { DataSet dataSet = new DataSet(); do { // Create new data table DataTable schemaTable = reader.GetSchemaTable(); DataTable dataTable = new DataTable(); if ( schemaTable != null ) { for ( int i = 0; i < schemaTable.Rows.Count; i++ ) { DataRow dataRow = schemaTable.Rows[ i ]; // Create a column name that is unique in the data table string columnName = ( string )dataRow[ "ColumnName" ]; // Add the column definition to the data table DataColumn column = new DataColumn( columnName, ( Type )dataRow[ "DataType" ] ); dataTable.Columns.Add( column ); } dataSet.Tables.Add( dataTable ); while ( reader.Read() ) { DataRow dataRow = dataTable.NewRow(); for ( int i = 0; i < reader.FieldCount; i++ ) dataRow[ i ] = reader.GetValue( i ); dataTable.Rows.Add( dataRow ); } } else { // No records returned DataColumn column = new DataColumn("RowsAffected"); dataTable.Columns.Add(column); dataSet.Tables.Add( dataTable ); DataRow dataRow = dataTable.NewRow(); dataRow[0] = reader.RecordsAffected; dataTable.Rows.Add( dataRow ); } } while ( reader.NextResult() ); return dataSet; }
protected override Exception ProcessSqlResult(SqlDataReader reader) { Exception nextResultSet = StoreUtilities.GetNextResultSet(base.InstancePersistenceCommand.Name, reader); if (nextResultSet == null) { reader.NextResult(); if (reader.Read()) { base.Store.UpdateEventStatus(true, InstancePersistenceEvent<HasActivatableWorkflowEvent>.Value); return nextResultSet; } base.Store.UpdateEventStatus(false, InstancePersistenceEvent<HasActivatableWorkflowEvent>.Value); base.StoreLock.InstanceDetectionTask.ResetTimer(false); } return nextResultSet; }
public static List <Look> GetTaggedLooks(string db, long uId, long tagId, int offset = 1, int limit = 20, bool isPopular = false) { List <Look> looks = new List <Look>(); string query; if (isPopular) { query = "EXEC [stp_SS_GetTaggedPopularLooks] @tagId=" + tagId + ",@userId=" + uId + ",@offset=" + offset + ",@limit=" + limit; } else { query = "EXEC [stp_SS_GetTaggedLooks] @tagId=" + tagId + ",@userId=" + uId + ",@offset=" + offset + ",@limit=" + limit; } SqlConnection myConnection = new SqlConnection(db); try { myConnection.Open(); using (SqlDataAdapter adp = new SqlDataAdapter(query, myConnection)) { SqlCommand cmd = adp.SelectCommand; cmd.CommandTimeout = 300000; System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader(); int countLooks = 0; while (dr.Read()) { countLooks = int.Parse(dr["total"].ToString()); } dr.NextResult(); looks = Look.GetLooksFromSqlReader(dr); } } finally { myConnection.Close(); } if (looks.Count == 0) { looks = null; } return(looks); }
protected override Exception ProcessSqlResult(SqlDataReader reader) { Exception exception = StoreUtilities.GetNextResultSet(base.InstancePersistenceCommand.Name, reader); if (exception == null) { bool signalEvent = false; reader.NextResult(); signalEvent = reader.Read(); //The result set contains activatable workflows if (signalEvent) { base.Store.UpdateEventStatus(true, HasActivatableWorkflowEvent.Value); } else { base.Store.UpdateEventStatus(false, HasActivatableWorkflowEvent.Value); base.StoreLock.InstanceDetectionTask.ResetTimer(false); } } return exception; }
protected void Page_Load(object sender, EventArgs e) { StringBuilder sbSQL = new StringBuilder(); sbSQL.Append("select FirstName + ' ' + LastName StudentName, g.GradeDescr" + CareerCruisingWeb.CCLib.Common.Strings.SuffixCode() + " Grade, UserName, InstitutionName, rrs.RegionName"); sbSQL.Append(" from PortfolioUserInfo p"); sbSQL.Append(" left join GradeNumberLookup g on p.GradeNumber = g.GradeNumber"); sbSQL.Append(" left join UserinfoRegionID ur on p.SchoolID = ur.SchoolID"); sbSQL.Append(" left join RRSRegions rrs on ur.RegionID = rrs.RegionID"); sbSQL.Append(" where rrs.StateSystem = '" + _state + "' and PortfolioID = " + _pid); dtStuInfo = CareerCruisingWeb.CCLib.Common.DataAccess.GetDataTable(sbSQL.ToString()); sbSQL = null; cn = new SqlConnection(ConfigurationManager.AppSettings["strConLocal"]); cmd = new SqlCommand("InterventionPlan_Select", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@PortTypeID", _porttypeid); cmd.Parameters.AddWithValue("@PortfolioID", "0"); cmd.Parameters.AddWithValue("@InterventionPlanID", _planid); cn.Open(); drData = cmd.ExecuteReader(); rptDelivery.DataSource = drData; rptDelivery.DataBind(); drData.NextResult(); rptStrategy.DataSource = drData; rptStrategy.DataBind(); drData.NextResult(); rptPerson.DataSource = drData; rptPerson.DataBind(); drData.NextResult(); rptUpdate.DataSource = drData; rptUpdate.DataBind(); drData.NextResult(); }
private static bool AdvanceToNextRow(SqlDataReader reader) { Debug.Assert(reader != null && !reader.IsClosed); // this method skips empty result sets do { if (reader.Read()) { return true; } } while (reader.NextResult()); // no more rows return false; }
protected void Page_Load(object sender, EventArgs e) { #region Check Login CheckRegionAdminSession(); #endregion Check Login #region Initialize Values and Form Execution strPID = CCLib.Common.Strings.GetQueryString("PortfolioID"); strAction = CCLib.Common.Strings.GetQueryString("action"); strSortColumn = CCLib.Common.Strings.GetQueryString("col"); if (strSortColumn == "") strSortColumn = "3"; strSortDirection = CCLib.Common.Strings.GetQueryString("sort"); switch (strSortDirection) { case "": strSortDirection = "desc"; break; case "asc": strSortDirection = "desc"; break; case "desc": strSortDirection = "asc"; break; } HeaderInfo.PortfolioID = strPID; HeaderInfo.ToolTypeID = "4"; switch (strAction) { case "list": strParams = new string[5]; strParams[0] = PortTypeID.ToString(); strParams[1] = strPID; strParams[2] = "0"; strParams[3] = strSortColumn; strParams[4] = strSortDirection; dtData = CCLib.SqlHelper.GetDataTable(ConfigurationManager.AppSettings["strConLocal"], "InterventionPlan_Select", strParams); if (dtData == null || dtData.Rows.Count <= 0) bHasRecord = false; rptInterventionPlanList.DataSource = dtData; rptInterventionPlanList.DataBind(); break; case "view": strPlanID = CCLib.Common.Strings.GetQueryString("PlanID"); conn = new SqlConnection(ConfigurationManager.AppSettings["strConLocal"]); comm = new SqlCommand("InterventionPlan_Select", conn); comm.CommandType = CommandType.StoredProcedure; comm.Parameters.AddWithValue("@PortTypeID", PortTypeID.ToString()); comm.Parameters.AddWithValue("@PortfolioID", "0"); comm.Parameters.AddWithValue("@InterventionPlanID", strPlanID); conn.Open(); drData = comm.ExecuteReader(); rptDelivery.DataSource = drData; rptDelivery.DataBind(); drData.NextResult(); rptStrategy.DataSource = drData; rptStrategy.DataBind(); drData.NextResult(); rptPerson.DataSource = drData; rptPerson.DataBind(); drData.NextResult(); rptUpdate.DataSource = drData; rptUpdate.DataBind(); drData.NextResult(); break; } #endregion Initialize Values and Form Execution #region Properties For The Region Base Class TitleBar = "Intervention Plan"; SubTitleBar = "Intervention Plans"; StrictNoCache = false; #endregion Properties For The Region Base Class }
private YellowstonePathology.YpiConnect.Contract.Identity.WebServiceAccount BuildWebServiceAccount(SqlDataReader dr) { YellowstonePathology.YpiConnect.Contract.Identity.WebServiceAccount webServiceAccount = null; while (dr.Read()) { webServiceAccount = new Contract.Identity.WebServiceAccount(); YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter propertyWriter = new YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter(webServiceAccount, dr); propertyWriter.WriteProperties(); } if (webServiceAccount != null) { dr.NextResult(); while (dr.Read()) { YellowstonePathology.YpiConnect.Contract.Identity.WebServiceAccountClient webServiceAccountClient = new Contract.Identity.WebServiceAccountClient(); YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter propertyWriter = new YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter(webServiceAccountClient, dr); propertyWriter.WriteProperties(); webServiceAccount.WebServiceAccountClientCollection.Add(webServiceAccountClient); } dr.NextResult(); while (dr.Read()) { webServiceAccount.Client = new Business.Client.Model.Client(); int s = (int)dr["ClientId"]; //TODO this is here becuse Client.Zip should be a nullable int but is not. if (s == 0) { webServiceAccount.Client.ClientName = "Not Provided"; } else { webServiceAccount.Client = new Business.Client.Model.Client(); YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter propertyWriter = new YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter(webServiceAccount.Client, dr); propertyWriter.WriteProperties(); } } } if (webServiceAccount == null) { webServiceAccount = new Contract.Identity.WebServiceAccount(); webServiceAccount.UserName = "******"; webServiceAccount.Password = "******"; webServiceAccount.IsKnown = false; } return webServiceAccount; }
public static Dictionary <string, List <Product> > GetPopularProductsByFiltersv3(long userId, string db, int brandId = 0, string tags = null, string categoryId = null, string colorId = null, int offset = 1, int limit = 20, int items = 5, string filter = null) { Dictionary <string, List <Product> > popularProducts = new Dictionary <string, List <Product> >(); string query = "EXEC [stp_SS_GetPopularProductsByFilters] @uId =" + userId + ",@offset=" + offset + ",@limit=" + limit; if (categoryId != null) { query += ",@categoryId=N'" + categoryId + "'"; } if (colorId != null) { query += ",@colorId=N'" + colorId + "'"; } if (tags != null) { query += ",@tags=N'" + tags + "'"; } if (brandId != 0) { query += ",@brandId=" + brandId; } if (items != 0) { query += ",@items=" + items; } if (filter != null) { query += ",@filter=" + filter; } SqlConnection myConnection = new SqlConnection(db); try { myConnection.Open(); using (SqlDataAdapter adp = new SqlDataAdapter(query, myConnection)) { SqlCommand cmd = adp.SelectCommand; cmd.CommandTimeout = 300000; System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader(); do { string type = string.Empty; List <Product> closetItems = new List <Product>(); while (dr.Read()) { type = dr["type"].ToString(); closetItems.Add(Product.GetProductFromSqlDataReader(dr)); } if (!string.IsNullOrEmpty(type)) { if (!popularProducts.ContainsKey(type)) { popularProducts.Add(type, closetItems); } else { popularProducts[type] = closetItems; } } } while (dr.NextResult()); } } finally { myConnection.Close(); } return(popularProducts); }
public static Dictionary <string, object> GetTagMetaInfo(long userId, long tagId, int noOfLooks, int noOfItems, int noOfStylists, string db) { Dictionary <string, object> metaInfo = new Dictionary <string, object>(); string query = "EXEC [stp_SS_GetTagMetaInfo] @tagId=" + tagId + ",@userId=" + userId + ",@noOfLooks=" + noOfLooks + ",@noOfItems=" + noOfItems + ",@noOfStylists=" + noOfStylists; SqlConnection myConnection = new SqlConnection(db); try { myConnection.Open(); using (SqlDataAdapter adp = new SqlDataAdapter(query, myConnection)) { SqlCommand cmd = adp.SelectCommand; cmd.CommandTimeout = 300000; System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader(); //Get popular looks List <Look> popularLooks = new List <Look>(); int counter = 0; int countLooks = 0; while (dr.Read()) { countLooks = int.Parse(dr["total"].ToString()); } dr.NextResult(); do { Look look = Look.GetLookFromSqlReader(dr); popularLooks.Add(look); counter++; if (counter >= noOfLooks || counter >= countLooks) { break; } } while (dr.NextResult()); metaInfo.Add("Popular Looks", popularLooks); /*Get fresh looks * List<Look> recentLooks = new List<Look>(); * dr.NextResult(); * counter = 0; countLooks = 0; * while (dr.Read()) * { * countLooks = int.Parse(dr["total"].ToString()); * } * dr.NextResult(); * do * { * Look look = Look.GetLookFromSqlReader(dr); * recentLooks.Add(look); * counter++; * if (counter >= noOfLooks || counter >= countLooks) * break; * } while (dr.NextResult()); * metaInfo.Add("Recent Looks", recentLooks);*/ //Get top stylists List <UserProfile> stylists = new List <UserProfile>(); dr.NextResult(); while (dr.Read()) { UserProfile user = UserProfile.GetUserFromSqlReader(dr); stylists.Add(user); } metaInfo.Add("Top Stylists", stylists); /*Get top products * List<Product> topItems = new List<Product>(); * dr.NextResult(); * * while (dr.Read()) * { * topItems.Add(Product.GetProductFromSqlDataReader(dr)); * } * metaInfo.Add("Top Items", topItems);*/ dr.NextResult(); Tag tag = new Tag(); while (dr.Read()) { tag = Tag.GetTagFromSqlReader(dr); } metaInfo.Add("Tag", tag); } } finally { myConnection.Close(); } return(metaInfo); }
public void LoadCategoryTree(string db) { handbagCats = new Dictionary <Category, List <Category> >(); clothingCats = new Dictionary <Category, List <Category> >(); shoeCats = new Dictionary <Category, List <Category> >(); beautyCats = new Dictionary <Category, List <Category> >(); string query = "EXEC [stp_SS_LoadCategoryTree]"; SqlConnection myConnection = new SqlConnection(db); try { myConnection.Open(); using (SqlDataAdapter adp = new SqlDataAdapter(query, myConnection)) { SqlCommand cmd = adp.SelectCommand; cmd.CommandTimeout = 300000; System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { Category cat = Category.GetCategoryFromSqlDataReader(dr); List <Category> childrenCats = new List <Category>(); handbagCats.Add(cat, childrenCats); } dr.NextResult(); while (dr.Read()) { Category cat = Category.GetCategoryFromSqlDataReader(dr); List <Category> childrenCats = new List <Category>(); shoeCats.Add(cat, childrenCats); } dr.NextResult(); while (dr.Read()) { Category cat = Category.GetCategoryFromSqlDataReader(dr); List <Category> childrenCats = new List <Category>(); clothingCats.Add(cat, childrenCats); } dr.NextResult(); while (dr.Read()) { Category cat = Category.GetCategoryFromSqlDataReader(dr); List <Category> childrenCats = new List <Category>(); beautyCats.Add(cat, childrenCats); } dr.NextResult(); while (dr.Read()) { Category cat = Category.GetCategoryFromSqlDataReader(dr); var parentCat = from c in clothingCats.Keys where c.id == cat.parentId select c; foreach (var pCat in parentCat) { clothingCats[pCat].Add(cat); } } dr.NextResult(); while (dr.Read()) { Category cat = Category.GetCategoryFromSqlDataReader(dr); var parentCat = from c in beautyCats.Keys where c.id == cat.parentId select c; foreach (var pCat in parentCat) { beautyCats[pCat].Add(cat); } } } } finally { myConnection.Close(); } }
/// <summary> /// Realiza un recorrimiento de tablas y retorna todos sus resultados en formato JSON /// </summary> /// <param name="Lector">Referencia hacia el lector (SqlDataReader)</param> /// <returns>Tablas en formato JSON</returns> private string RecorerTablas(SqlDataReader Lector) { StringBuilder Tablas = new StringBuilder(); short i = 0; do { Tablas.AppendFormat("{1}{0}", RecorerTabla(ref Lector), (i>0)?",":""); i++; } while (Lector.NextResult()); Tablas.Insert(0, (i > 1) ? "[" : ""); Tablas.Append((i > 1) ? "]" : ""); return Tablas.ToString(); }
public static void BuildClientOrderDetailCollection(YellowstonePathology.Business.ClientOrder.Model.ClientOrderDetailCollection clientOrderDetailCollection, SqlDataReader dr) { int clientOrderDetailCount = 0; while (dr.Read()) { clientOrderDetailCount = Convert.ToInt32(dr["ClientOrderDetailCount"].ToString()); } if (clientOrderDetailCount > 0) { for (int i = 0; i < clientOrderDetailCount; i++) { dr.NextResult(); string orderTypeCode = null; while (dr.Read()) { orderTypeCode = dr["OrderTypeCode"].ToString(); } dr.NextResult(); YellowstonePathology.Business.ClientOrder.Model.ClientOrderDetail clientOrderDetail = YellowstonePathology.Business.ClientOrder.Model.ClientOrderDetailFactory.GetClientOrderDetail(orderTypeCode, Persistence.PersistenceModeEnum.UpdateChangedProperties); while (dr.Read()) { YellowstonePathology.Business.Persistence.SqlDataReaderPropertyWriter propertyWriter = new Persistence.SqlDataReaderPropertyWriter(clientOrderDetail, dr); propertyWriter.WriteProperties(); clientOrderDetailCollection.Add(clientOrderDetail); } } } else { dr.NextResult(); dr.NextResult(); } }
public static Dictionary <Category, List <Category> > GetMetaCategories(string db) { Dictionary <Category, List <Category> > metaCats = new Dictionary <Category, List <Category> >(); string query = "EXEC [stp_SS_LoadMetaCategoryTree]"; SqlConnection myConnection = new SqlConnection(db); try { myConnection.Open(); using (SqlDataAdapter adp = new SqlDataAdapter(query, myConnection)) { SqlCommand cmd = adp.SelectCommand; cmd.CommandTimeout = 300000; System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { Category cat = Category.GetCategoryFromSqlDataReader(dr); if (metaCats.Keys.Contains(cat)) { throw new Exception(); } else { List <Category> childrenCats = new List <Category>(); //childrenCats.Add(cat); metaCats.Add(cat, childrenCats); } } dr.NextResult(); //get child cats while (dr.Read()) { Category cat = Category.GetCategoryFromSqlDataReader(dr); var parentCat = from c in metaCats.Keys where c.id == cat.parentId select c; foreach (var pCat in parentCat) { metaCats[pCat].Add(cat); } } //trim single child nodes foreach (Category c in metaCats.Keys) { if (metaCats[c].Count == 1) { metaCats[c].RemoveAt(0); } } } } finally { myConnection.Close(); } return(metaCats); }
// Send results from SqlDataReader public void Send( SqlDataReader reader ) { ADP.CheckArgumentNull(reader, "reader"); SetPipeBusy( ); try { EnsureNormalSendValid( "Send" ); do { SmiExtendedMetaData[] columnMetaData = reader.GetInternalSmiMetaData(); if (null != columnMetaData && 0 != columnMetaData.Length) { // SQLBUDT #340528 -- don't send empty results. using ( SmiRecordBuffer recordBuffer = _smiContext.CreateRecordBuffer(columnMetaData, _eventSink) ) { _eventSink.ProcessMessagesAndThrow(); // Handle any errors that are reported. _smiContext.SendResultsStartToPipe( recordBuffer, _eventSink ); _eventSink.ProcessMessagesAndThrow(); // Handle any errors that are reported. try { while( reader.Read( ) ) { if (SmiContextFactory.Instance.NegotiatedSmiVersion >= SmiContextFactory.KatmaiVersion) { ValueUtilsSmi.FillCompatibleSettersFromReader(_eventSink, recordBuffer, new List<SmiExtendedMetaData>(columnMetaData), reader); } else { ValueUtilsSmi.FillCompatibleITypedSettersFromReader(_eventSink, recordBuffer, columnMetaData, reader); } _smiContext.SendResultsRowToPipe( recordBuffer, _eventSink ); _eventSink.ProcessMessagesAndThrow(); // Handle any errors that are reported. } } finally { _smiContext.SendResultsEndToPipe( recordBuffer, _eventSink ); _eventSink.ProcessMessagesAndThrow(); // Handle any errors that are reported. } } } } while ( reader.NextResult( ) ); } catch { _eventSink.CleanMessages(); throw; } finally { ClearPipeBusy( ); Debug.Assert(_eventSink.HasMessages == false, "There should be no messages left in _eventsink at the end of the Send reader!"); } }
private void Convert_Facet_Tables_To_Facet_Lists(SqlDataReader reader, List<short> Facet_Types) { // Go to the next table if (!reader.NextResult()) return; // Incrementor going through tables (and skipping aggregation table maybe) if (reader.FieldCount > 2) { // Read all the aggregation fields while (reader.Read()) { Aggregation_Facets.Add(new Search_Facet_Aggregation(reader.GetString(1), reader.GetInt32(2), reader.GetString(0))); } // Go to the next table if (!reader.NextResult()) return; } // Build the first facet list if ((reader.FieldCount == 2) && (Facet_Types.Count > 0)) { // Assign the first facet type First_Facets_MetadataTypeID = Facet_Types[0]; // Read all the individual facet values while (reader.Read()) { First_Facets.Add(new Search_Facet(reader.GetString(0), reader.GetInt32(1))); } // Go to the next table if (!reader.NextResult()) return; } // Build the second facet list if ((reader.FieldCount == 2) && (Facet_Types.Count > 1)) { // Assign the second facet type Second_Facets_MetadataTypeID = Facet_Types[1]; // Read all the individual facet values while (reader.Read()) { Second_Facets.Add(new Search_Facet(reader.GetString(0), reader.GetInt32(1))); } // Go to the next table if (!reader.NextResult()) return; } // Build the third facet list if ((reader.FieldCount == 2) && (Facet_Types.Count > 2)) { // Assign the third facet type Third_Facets_MetadataTypeID = Facet_Types[2]; // Read all the individual facet values while (reader.Read()) { Third_Facets.Add(new Search_Facet(reader.GetString(0), reader.GetInt32(1))); } // Go to the next table if (!reader.NextResult()) return; } // Build the fourth facet list if ((reader.FieldCount == 2) && (Facet_Types.Count > 3)) { // Assign the fourth facet type Fourth_Facets_MetadataTypeID = Facet_Types[3]; // Read all the individual facet values while (reader.Read()) { Fourth_Facets.Add(new Search_Facet(reader.GetString(0), reader.GetInt32(1))); } // Go to the next table if (!reader.NextResult()) return; } // Build the fifth facet list if ((reader.FieldCount == 2) && (Facet_Types.Count > 4)) { // Assign the fifth facet type Fifth_Facets_MetadataTypeID = Facet_Types[4]; // Read all the individual facet values while (reader.Read()) { Fifth_Facets.Add(new Search_Facet(reader.GetString(0), reader.GetInt32(1))); } // Go to the next table if (!reader.NextResult()) return; } // Build the sixth facet list if ((reader.FieldCount == 2) && (Facet_Types.Count > 5)) { // Assign the sixth facet type Sixth_Facets_MetadataTypeID = Facet_Types[5]; // Read all the individual facet values while (reader.Read()) { Sixth_Facets.Add(new Search_Facet(reader.GetString(0), reader.GetInt32(1))); } // Go to the next table if (!reader.NextResult()) return; } // Build the seventh facet list if ((reader.FieldCount == 2) && (Facet_Types.Count > 6)) { // Assign the seventh facet type Seventh_Facets_MetadataTypeID = Facet_Types[6]; // Read all the individual facet values while (reader.Read()) { Seventh_Facets.Add(new Search_Facet(reader.GetString(0), reader.GetInt32(1))); } // Go to the next table if (!reader.NextResult()) return; } // Build the eighth facet list if ((reader.FieldCount == 2) && (Facet_Types.Count > 7)) { // Assign the eighth facet type Eighth_Facets_MetadataTypeID = Facet_Types[7]; // Read all the individual facet values while (reader.Read()) { Eighth_Facets.Add(new Search_Facet(reader.GetString(0), reader.GetInt32(1))); } } }
private void LoadChangesFromReader(SqlDataReader reader, object parameter) { if (!reader.Read()) return; // // Reset the min to the most recent change event DateTime tmpDT = _changesMinDT; if (!reader.IsDBNull(0)) tmpDT = reader.GetDateTime(0); if (reader.NextResult()) { WorkflowMarkupSerializer serializer = new WorkflowMarkupSerializer(); DesignerSerializationManager serializationManager = new DesignerSerializationManager(); while (reader.Read()) { IList errors = null; using (StringReader sr = new StringReader(reader.GetString(0))) { using (serializationManager.CreateSession()) { using (XmlReader xmlReader = XmlReader.Create(sr)) { ActivityChangeAction aAction = serializer.Deserialize(serializationManager, xmlReader) as ActivityChangeAction; errors = serializationManager.Errors; if (null != aAction) aAction.ApplyTo(_def); else throw new WorkflowMarkupSerializationException(ExecutionStringManager.WorkflowMarkupDeserializationError); } } } } } if (tmpDT > _changesMinDT) { _changed = true; _changesMinDT = tmpDT; } }
private void LoadWorkflowChangeEventArgsFromReader(SqlDataReader reader, object parameter) { if (null == reader) throw new ArgumentNullException("reader"); if (null == parameter) throw new ArgumentNullException("parameter"); WorkflowTrackingRecord record = parameter as WorkflowTrackingRecord; if (null == record) throw new ArgumentException(ExecutionStringManager.InvalidWorkflowChangeEventArgsParameter, "parameter"); if (!reader.Read()) throw new ArgumentException(ExecutionStringManager.InvalidWorkflowChangeEventArgsReader); StringReader sr = new StringReader(reader.GetString(0)); //Deserialize the xoml and set the root activity Activity def = null; WorkflowMarkupSerializer serializer = new WorkflowMarkupSerializer(); DesignerSerializationManager serializationManager = new DesignerSerializationManager(); IList errors = null; try { using (serializationManager.CreateSession()) { using (XmlReader xmlReader = XmlReader.Create(sr)) { def = serializer.Deserialize(serializationManager, xmlReader) as Activity; errors = serializationManager.Errors; } } } finally { sr.Close(); } if ((null == def) || ((null != errors) && (errors.Count > 0))) throw new WorkflowMarkupSerializationException(ExecutionStringManager.WorkflowMarkupDeserializationError); if (!reader.NextResult()) throw new ArgumentException(ExecutionStringManager.InvalidWorkflowChangeEventArgsReader); // // There is a result set that we don't care about for this scenario, skip it if (!reader.NextResult()) throw new ArgumentException(ExecutionStringManager.InvalidWorkflowChangeEventArgsReader); List<WorkflowChangeAction> actions = new List<WorkflowChangeAction>(); DateTime currDT = DateTime.MinValue; int currEventOrder = -1; int currOrder = -1; while (reader.Read()) { DateTime dt = reader.GetDateTime(1); int eventOrder = reader.GetInt32(2); int order = reader.GetInt32(3); // // Build temp lists as we read the results but // only save the last set of change actions if (dt > currDT && eventOrder > currEventOrder) { currEventOrder = eventOrder; currOrder = order; currDT = dt; actions = new List<WorkflowChangeAction>(); } using (sr = new StringReader(reader.GetString(0))) { using (serializationManager.CreateSession()) { using (XmlReader xmlReader = XmlReader.Create(sr)) { ActivityChangeAction aAction = serializer.Deserialize(serializationManager, xmlReader) as ActivityChangeAction; errors = serializationManager.Errors; if (null == aAction) throw new WorkflowMarkupSerializationException(ExecutionStringManager.WorkflowMarkupDeserializationError); actions.Add(aAction); aAction.ApplyTo(def); } } } } record.EventArgs = new TrackingWorkflowChangedEventArgs(actions, def); }
private void LoadWorkflowEventsFromReader(SqlDataReader reader, object parameter) { if (null == reader) throw new ArgumentNullException("reader"); // // There should always be 2 recordsets in this reader! // DateTime tmpMin = SqlDateTime.MinValue.Value; Dictionary<long, WorkflowTrackingRecord> inst = new Dictionary<long, WorkflowTrackingRecord>(); while (reader.Read()) { TrackingWorkflowEvent evt = (TrackingWorkflowEvent)reader[0]; DateTime dt = reader.GetDateTime(1); int order = reader.GetInt32(2); object tmp = null; EventArgs args = null; if (!reader.IsDBNull(3)) { BinaryFormatter formatter = new BinaryFormatter(); tmp = formatter.Deserialize(new MemoryStream((Byte[])reader[3])); if (tmp is EventArgs) args = (EventArgs)tmp; } long eventId = reader.GetInt64(4); DateTime dbDt = reader.GetDateTime(5); inst.Add(eventId, new WorkflowTrackingRecord(evt, dt, order, args)); if (dbDt > tmpMin) tmpMin = dbDt; } if (!reader.NextResult()) throw new ArgumentException(ExecutionStringManager.InvalidWorkflowInstanceEventReader); // // Add any annotations while (reader.Read()) { long eventId = reader.GetInt64(0); string annotation = null; if (!reader.IsDBNull(1)) annotation = reader.GetString(1); WorkflowTrackingRecord rec = null; if (inst.TryGetValue(eventId, out rec)) { if (null != rec) rec.Annotations.Add(annotation); } } if (!reader.IsClosed) reader.Close(); // // Check if we have any WorkflowChange events in this list // If so pull back the change actions and reconstruct the args property foreach (KeyValuePair<long, WorkflowTrackingRecord> kvp in inst) { WorkflowTrackingRecord rec = kvp.Value; if (TrackingWorkflowEvent.Changed != rec.TrackingWorkflowEvent) continue; SqlCommand cmd = new SqlCommand(); cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "[dbo].[GetWorkflowChangeEventArgs]"; cmd.Parameters.Add(new SqlParameter("@WorkflowInstanceInternalId", _internalId)); cmd.Parameters.Add(new SqlParameter("@BeginDateTime", SqlDateTime.MinValue.Value)); cmd.Parameters.Add(new SqlParameter("@WorkflowInstanceEventId", kvp.Key)); ExecuteRetried(cmd, LoadWorkflowChangeEventArgsFromReader, rec); } _workflowEvents.AddRange(inst.Values); // // set the min for the next query to the most recent event from this query // Don't overwrite the previous min if nothing came back for this query if (tmpMin > SqlDateTime.MinValue.Value) _instMinDT = tmpMin; }
private void LoadUserEventsFromReader(SqlDataReader reader, object parameter) { if (null == reader) throw new ArgumentNullException("reader"); // // There should always be 4 recordsets in this reader! // BinaryFormatter formatter = new BinaryFormatter(); Dictionary<long, UserTrackingRecord> userEvents = new Dictionary<long, UserTrackingRecord>(); // // Build a dictionary of activity records so that we can match // annotation and artifact records from subsequent recordsets DateTime tmpMin = SqlDateTime.MinValue.Value; while (reader.Read()) { string qId = reader.GetString(0); DateTime dt = reader.GetDateTime(1); Guid context = reader.GetGuid(2), parentContext = reader.GetGuid(3); int order = reader.GetInt32(4); string key = null; if (!reader.IsDBNull(5)) key = reader.GetString(5); // // Get the user data from the serialized column if we can // Try the string column if serialized column is null // If both are null the user data was null originally object userData = null; if (!reader.IsDBNull(7)) userData = formatter.Deserialize(new MemoryStream((Byte[])reader[7])); else if (!reader.IsDBNull(6)) userData = reader.GetString(6); if (reader.IsDBNull(8) || reader.IsDBNull(9)) throw new InvalidOperationException(String.Format(System.Globalization.CultureInfo.CurrentCulture, ExecutionStringManager.SqlTrackingTypeNotFound, qId)); Type type = Type.GetType(reader.GetString(8) + ", " + reader.GetString(9), true, false); long eventId = reader.GetInt64(10); DateTime dbDt = reader.GetDateTime(11); userEvents.Add(eventId, new UserTrackingRecord(type, qId, context, parentContext, dt, order, key, userData)); if (dbDt > tmpMin) tmpMin = dbDt; } if (!reader.NextResult()) throw new ArgumentException(ExecutionStringManager.InvalidUserEventReader); // // If we have annotations on the event itself, add them while (reader.Read()) { long eventId = reader.GetInt64(0); string annotation = null; if (!reader.IsDBNull(1)) annotation = reader.GetString(1); UserTrackingRecord user = null; if (userEvents.TryGetValue(eventId, out user)) { if (null != user) user.Annotations.Add(annotation); } } if (!reader.NextResult()) throw new ArgumentException(ExecutionStringManager.InvalidUserEventReader); // // Build a dictionary of artifact records so that we can match // annotation records from subsequent recordsets Dictionary<long, TrackingDataItem> artifacts = new Dictionary<long, TrackingDataItem>(); while (reader.Read()) { long eventId = reader.GetInt64(0); long artId = reader.GetInt64(1); string name = reader.GetString(2), strData = null; object data = null; // // These may both be null if (!reader.IsDBNull(3)) strData = reader.GetString(3); if (!reader.IsDBNull(4)) data = formatter.Deserialize(new MemoryStream((Byte[])reader[4])); TrackingDataItem item = new TrackingDataItem(); item.FieldName = name; if (null != data) item.Data = data; else item.Data = strData; artifacts.Add(artId, item); // // Find the event to which this artifact belongs and add it to the record UserTrackingRecord user = null; if (userEvents.TryGetValue(eventId, out user)) { if (null != user) user.Body.Add(item); } } if (!reader.NextResult()) throw new ArgumentException(ExecutionStringManager.InvalidUserEventReader); // // If we have annotations add them to the appropriate artifact while (reader.Read()) { long artId = reader.GetInt64(0); string annotation = null; if (!reader.IsDBNull(1)) annotation = reader.GetString(1); // // Find the right artifact and give it the annotation TrackingDataItem item = null; if (artifacts.TryGetValue(artId, out item)) { if (null != item) item.Annotations.Add(annotation); } } _userEvents.AddRange(userEvents.Values); // // Set the min dt to query for next time to the most recent event we got for this query. // Don't overwrite the previous min if nothing came back for this query if (tmpMin > SqlDateTime.MinValue.Value) _userMinDT = tmpMin; return; }
private void BuildResults(SqlDataReader dr, int subqueryIndex = 0) { // paging switch (_pagingDirection) { case DatabaseQueryPagingDirection.Top: _currentPage = 1; break; case DatabaseQueryPagingDirection.Next: _currentPage++; break; case DatabaseQueryPagingDirection.Previous: _currentPage--; if (_currentPage < 1) _currentPage = 1; break; } int startread = (_currentPage * _pagingLimit) - _pagingLimit; int endread = startread + _pagingLimit; //build result node MemoryStream stream = new MemoryStream(); XmlTextWriter w = new XmlTextWriter(stream, Encoding.UTF8); XmlNodeList fieldNodes; // begin writing the xml result w.WriteStartElement("result"); try { //field values for results if(subqueryIndex > 0) fieldNodes = _querynode.SelectNodes(String.Format(@"subquery[{0}]/fields/field", subqueryIndex)); else fieldNodes = _querynode.SelectNodes(@"fields/field"); //add rows to result node long currentrow = 0; _resultPages = getNewResultPages(_querynode); int pagecount = 1; int inpagecount = 0; while (dr.Read()) { bool flginpage = false; if ((currentrow >= (startread) && currentrow < endread) || _pagingLimit == -1) flginpage = true; #region in page write results if (flginpage) { w.WriteStartElement("row"); // if we come accross Json fields, they are translated to Xml and added as child to row node // keep track of them and add them after all attributes have been processed List<string> innerXml = new List<string>(); //attributes (fields) if (fieldNodes.Count > 0) { foreach (XmlElement m in fieldNodes) { try { // validate json type if (m.GetAttribute("type") == "json") { try { var val = dr[m.GetAttribute("name")].ToString(); string json = null; if (string.IsNullOrEmpty(val)) { json = "{}"; } else { var jsoObj = Newtonsoft.Json.Linq.JObject.Parse(val); json = val; } w.WriteAttributeString(m.GetAttribute("name").ToString(), json); } catch (IndexOutOfRangeException handled) { throw handled; } catch (Exception unhandled) { w.WriteAttributeString(m.GetAttribute("name").ToString(), "{\"error\":\"" + unhandled.Message + "\"}"); } } else { string val = null; if(!(m.HasAttribute("encode") || m.HasAttribute("regx"))) val = dr[m.GetAttribute("name")].ToString().Trim(); if (m.HasAttribute("encode")) { val = System.Web.HttpUtility.UrlEncode(dr[m.GetAttribute("encode")].ToString().TrimEnd()).Replace("+", "%20"); ; } if (m.HasAttribute("regx") && m.HasAttribute("replace") && m.HasAttribute("field")) { val = dr[m.GetAttribute("field")].ToString().Trim(); val = Regex.Replace(val, m.GetAttribute("regex").ToString(), m.GetAttribute("replace").ToString()); } w.WriteAttributeString(m.GetAttribute("name").ToString(), String.IsNullOrEmpty(val) ? "" : val.ToString().Trim()); } if (m.GetAttribute("type") == "json") { string xml = null; try { var details = dr[m.GetAttribute("name")].ToString(); // try to parse it string jsonDetails = String.Format("{{\"{0}\":{1}}}", m.GetAttribute("name"), details); xml = JsonConvert.DeserializeXmlNode(jsonDetails).OuterXml; if (xml.StartsWith("<?")) xml = xml.Substring(xml.IndexOf("?>") + 2); } catch (JsonReaderException) { } catch (JsonSerializationException) { } finally { if(xml != null) innerXml.Add(xml); } } } catch (IndexOutOfRangeException handled) { w.WriteAttributeString(m.GetAttribute("name").ToString(), "#field not found#"); } } } else { for (int i = 0; i < dr.FieldCount; i++) { w.WriteAttributeString(dr.GetName(i), dr[i].ToString().Trim()); } } // add inner xml foreach (String s in innerXml) w.WriteRaw(s); w.WriteEndElement(); } #endregion _rowsaffected = (Int32)currentrow++; inpagecount++; if (inpagecount >= _pagingLimit) { _resultPages.Add(new ResultPaging(pagecount++, inpagecount)); inpagecount = 0; } } // get last result for _resultPage if (inpagecount > 0) _resultPages.Add(new ResultPaging(pagecount++, inpagecount)); _results = true; // reset a couple of paging items _currentPage = 1; _pagingDirection = DatabaseQueryPagingDirection.None; } catch (IndexOutOfRangeException ie) { string errmsg = string.Format("One or more invalid Field or Parameters for QueryName: {0}", _querynode.Attributes["name"].InnerText); ProjectExceptionArgs args = new ProjectExceptionArgs(errmsg, "Database.cs", "BuildResults", null, SeverityLevel.Fatal, LogLevel.Event); throw new ProjectException(args, ie); } catch (SqlException se) { string errmsg = string.Format("ExecuteReader Error For QueryName: {0}", _querynode.Attributes["name"].InnerText); ProjectExceptionArgs args = new ProjectExceptionArgs(errmsg, "Database.cs", "BuildResults", null, SeverityLevel.Fatal, LogLevel.Event); throw new ProjectException(args, se); } //end result node w.WriteEndElement(); w.Flush(); // include sub results (StoredProcedure returns more than one Result Set) while (dr.NextResult()) { subqueryIndex++; BuildResults(dr, subqueryIndex); } //add stream xml to return xml XmlDocument xmStreamObj = new XmlDocument(); stream.Seek(0, SeekOrigin.Begin); xmStreamObj.Load(stream); //import result xml to original xml obj XmlNode import = _xmresult.ImportNode(xmStreamObj.DocumentElement, true); XmlNode elm; if (subqueryIndex > 0 && _xmresult.SelectSingleNode("results/subquery") == null) elm = _xmresult.SelectSingleNode("results").AppendChild(_xmresult.CreateElement("subquery")); else elm = _xmresult.SelectSingleNode("results"); elm.AppendChild(import); }
private void FetchVendorDetails(VendorInfo data, SqlDataReader reader, SafeDataReader safe) { System.Diagnostics.StackFrame stackFrame = new System.Diagnostics.StackFrame(); System.Reflection.MethodBase methodBase = stackFrame.GetMethod(); log.Debug("Start: " + methodBase.Name); try { //Matched DI Records while (reader.Read()) { var ObjGetVendor = new Vendor(); ObjGetVendor.FetchMatchedVendorDetails(ObjGetVendor, safe); data.MatchedDIList.Add(ObjGetVendor); } //UnMatched DI Records reader.NextResult(); while (reader.Read()) { var ObjGetVendor = new Vendor(); ObjGetVendor.FetchUnMatchedVendorDetails(ObjGetVendor, safe); data.UnMatchedDIList.Add(ObjGetVendor); } //Over Billed reader.NextResult(); while (reader.Read()) { var ObjGetVendor = new Vendor(); ObjGetVendor.FetchMatchedVendorDetails(ObjGetVendor, safe); data.OverBilledList.Add(ObjGetVendor); } //Billed Discrepancy reader.NextResult(); while (reader.Read()) { var ObjGetVendor = new Vendor(); ObjGetVendor.FetchDiscrepancyVendorDetails(ObjGetVendor, safe, false); data.BilledDiscrepancyList.Add(ObjGetVendor); } //Unbilled Discrepancy reader.NextResult(); while (reader.Read()) { var ObjGetVendor = new Vendor(); ObjGetVendor.FetchDiscrepancyVendorDetails(ObjGetVendor, safe, true); data.UnbilledDiscrepancyList.Add(ObjGetVendor); } //Multple Discrepancy reader.NextResult(); while (reader.Read()) { var ObjGetVendor = new Vendor(); ObjGetVendor.FetchMultiDiscrepancyVendorDetails(ObjGetVendor, safe, false); data.MultpleDiscrepancy.Add(ObjGetVendor); } //Method is Use to Calculate Multiple Discripancy ProcessMultipleDiscrepancy(data.MultpleUpdatedDiscrepancy, data.MultpleDiscrepancy); //Error List reader.NextResult(); while (reader.Read()) { var ObjGetVendor = new Vendor(); ObjGetVendor.FetchErrorList(ObjGetVendor, safe); data.ErrorList.Add(ObjGetVendor); } //File Name reader.NextResult(); while (reader.Read()) { data.FileName = Convert.ToString(reader["FileName"]); } } catch (Exception ex) { log.Error("Error: " + ex); } finally { log.Debug("End: " + methodBase.Name); } }
public Dictionary <string, List <object> > GetResults(string db, string view, long reqUId, long uId) { Dictionary <string, List <object> > results = new Dictionary <string, List <object> >(); string query = "EXEC [stp_SS_GetUserExtended] @id=" + reqUId + ", @view='" + view + "', @uid=" + uId; SqlConnection myConnection = new SqlConnection(db); try { myConnection.Open(); using (SqlDataAdapter adp = new SqlDataAdapter(query, myConnection)) { SqlCommand cmd = adp.SelectCommand; cmd.CommandTimeout = 300000; System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { SetsLink.Text = dr["SetCount"].ToString() + " Looks"; } dr.NextResult(); while (dr.Read()) { LovesLink.Text = dr["LoveCount"].ToString() + " Loves"; } dr.NextResult(); while (dr.Read()) { FollowersLink.Text = dr["FollowerCount"].ToString() + " Followers"; } dr.NextResult(); while (dr.Read()) { FollowingLink.Text = dr["FollowingCount"].ToString() + " Following"; } dr.NextResult(); switch (view) { case "sets": while (dr.Read()) { string contestName = dr["Name"].ToString(); Look look = new Look(); look.id = long.Parse(dr["Id"].ToString()); look.products = new List <Product>(); look.isLoved = int.Parse(dr["love"].ToString()) == 1 ? true : false; if (results.ContainsKey(contestName)) { results[contestName].Add(look); } else { List <object> looks = new List <object>(); looks.Add(look); results.Add(contestName, looks); } } break; case "loves": while (dr.Read()) { string retailerName = dr["RetailerName"].ToString(); Product pdt = new Product(); pdt = Product.GetProductFromSqlDataReader(dr); if (results.ContainsKey(retailerName)) { results[retailerName].Add(pdt); } else { List <object> products = new List <object>(); products.Add(pdt); results.Add(retailerName, products); } } break; case "followers": case "following": while (dr.Read()) { string resultName = "Follows"; UserProfile user = new UserProfile(); user = UserProfile.GetUserFromSqlReader(dr); user.IsFollowing = int.Parse(dr["following"].ToString()); if (results.ContainsKey(resultName)) { results[resultName].Add(user); } else { List <object> users = new List <object>(); users.Add(user); results.Add(resultName, users); } } break; } } } finally { myConnection.Close(); } return(results); }