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