public override ProductBrowseInfo GetProductBrowseInfo(int productId, int?maxReviewNum, int?maxConsultationNum) { int value = HiContext.Current.SiteSettings.UserId.Value; if (HiContext.Current.User.UserRole == UserRole.Underling) { Hidistro.Membership.Context.Member member = HiContext.Current.User as Hidistro.Membership.Context.Member; int memberDiscount = MemberProvider.Instance().GetMemberDiscount(member.GradeId); int arg_59_0 = member.GradeId; } ProductBrowseInfo productBrowseInfo = new ProductBrowseInfo(); StringBuilder stringBuilder = new StringBuilder(); stringBuilder.Append("UPDATE distro_Products SET VistiCounts = VistiCounts + 1 WHERE ProductId = @ProductId AND DistributorUserId = @DistributorUserId;"); stringBuilder.Append(" SELECT dp.*, p.Unit, p.ImageUrl1, p.ImageUrl2, p.ImageUrl3, p.ImageUrl4, p.ImageUrl5, p.LowestSalePrice, p.PenetrationStatus, p.TaobaoProductId"); stringBuilder.Append(",CASE WHEN dp.BrandId IS NULL THEN NULL ELSE (SELECT bc.BrandName FROM Hishop_BrandCategories bc WHERE bc.BrandId=dp.BrandId) END AS BrandName"); stringBuilder.Append(" FROM distro_Products dp JOIN Hishop_Products p ON dp.ProductId = p.ProductId where dp.ProductId=@ProductId AND dp.DistributorUserId = @DistributorUserId;"); if (HiContext.Current.User.UserRole == UserRole.Underling) { Hidistro.Membership.Context.Member member = HiContext.Current.User as Hidistro.Membership.Context.Member; int memberDiscount = MemberProvider.Instance().GetMemberDiscount(member.GradeId); stringBuilder.Append("SELECT SkuId, ProductId, SKU,Weight, Stock, AlertStock, CostPrice, PurchasePrice,"); stringBuilder.AppendFormat(" CASE WHEN (SELECT COUNT(*) FROM distro_SKUMemberPrice WHERE SkuId = s.SkuId AND GradeId = {0} AND DistributoruserId = {1}) = 1", member.GradeId, value); stringBuilder.AppendFormat(" THEN (SELECT MemberSalePrice FROM distro_SKUMemberPrice WHERE SkuId = s.SkuId AND GradeId = {0} AND DistributoruserId = {1})", member.GradeId, value); stringBuilder.AppendFormat(" ELSE (SELECT SalePrice FROM vw_distro_SkuPrices WHERE SkuId = s.SkuId AND DistributoruserId = {0})*{1}/100 END AS SalePrice", value, memberDiscount); } else { stringBuilder.Append("SELECT SkuId, ProductId, SKU,Weight, Stock, AlertStock, CostPrice, PurchasePrice,"); stringBuilder.AppendFormat(" (SELECT SalePrice FROM vw_distro_SkuPrices WHERE SkuId = s.SkuId AND DistributoruserId = {0}) AS SalePrice", value); } stringBuilder.Append(" FROM Hishop_SKUs s WHERE ProductId = @ProductId"); if (maxReviewNum.HasValue) { stringBuilder.AppendFormat(" SELECT TOP {0} * FROM distro_ProductReviews where ProductId=@ProductId AND DistributorUserId=@DistributorUserId ORDER BY ReviewId DESC;", maxReviewNum); } if (maxConsultationNum.HasValue) { stringBuilder.AppendFormat(" SELECT TOP {0} * FROM distro_ProductConsultations where ProductId=@ProductId AND DistributorUserId=@DistributorUserId AND ReplyUserId IS NOT NULL ORDER BY ConsultationId DESC ;", maxConsultationNum); } stringBuilder.Append(" SELECT a.AttributeId, AttributeName, ValueStr FROM Hishop_ProductAttributes pa JOIN Hishop_Attributes a ON pa.AttributeId = a.AttributeId"); stringBuilder.Append(" JOIN Hishop_AttributeValues v ON a.AttributeId = v.AttributeId AND pa.ValueId = v.ValueId WHERE ProductId = @ProductId ORDER BY a.DisplaySequence DESC, v.DisplaySequence DESC"); stringBuilder.Append(" SELECT SkuId, a.AttributeId, AttributeName, UseAttributeImage, av.ValueId, ValueStr, ImageUrl FROM Hishop_SKUItems s join Hishop_Attributes a on s.AttributeId = a.AttributeId join Hishop_AttributeValues av on s.ValueId = av.ValueId WHERE SkuId IN (SELECT SkuId FROM Hishop_SKUs WHERE ProductId = @ProductId) ORDER BY a.DisplaySequence DESC,av.DisplaySequence DESC;"); stringBuilder.AppendFormat(" SELECT TOP 20 ProductId,ProductName,ThumbnailUrl60,ThumbnailUrl100,ThumbnailUrl160, ThumbnailUrl180,ThumbnailUrl220,ThumbnailUrl310,MarketPrice,SalePrice FROM vw_distro_BrowseProductList WHERE SaleStatus = {0}", 1); stringBuilder.AppendFormat(" AND DistributorUserId = {0} AND ProductId IN (SELECT RelatedProductId FROM distro_RelatedProducts WHERE ProductId = {1} AND DistributorUserId = {0})", value, productId); stringBuilder.AppendFormat(" UNION SELECT TOP 20 ProductId,ProductName,ThumbnailUrl60,ThumbnailUrl100,ThumbnailUrl160, ThumbnailUrl180,ThumbnailUrl220,ThumbnailUrl310,MarketPrice,SalePrice FROM vw_distro_BrowseProductList WHERE SaleStatus = {0}", 1); stringBuilder.AppendFormat(" AND DistributorUserId = {0} AND ProductId<>{1} AND CategoryId = (SELECT CategoryId FROM distro_Products WHERE ProductId={1} AND SaleStatus = {2} AND DistributorUserId = {0})", value, productId, 1); stringBuilder.AppendFormat(" AND ProductId NOT IN (SELECT RelatedProductId FROM distro_RelatedProducts WHERE ProductId = {0} AND DistributorUserId = {1})", productId, value); System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand(stringBuilder.ToString()); this.database.AddInParameter(sqlStringCommand, "ProductId", System.Data.DbType.Int32, productId); this.database.AddInParameter(sqlStringCommand, "DistributorUserId", System.Data.DbType.Int32, value); using (System.Data.IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand)) { if (dataReader.Read()) { productBrowseInfo.Product = DataMapper.PopulateProduct(dataReader); if (dataReader["BrandName"] != DBNull.Value) { productBrowseInfo.BrandName = (string)dataReader["BrandName"]; } } if (dataReader.NextResult() && productBrowseInfo.Product != null) { while (dataReader.Read()) { productBrowseInfo.Product.Skus.Add((string)dataReader["SkuId"], DataMapper.PopulateSKU(dataReader)); } } if (maxReviewNum.HasValue && dataReader.NextResult()) { productBrowseInfo.DBReviews = DataHelper.ConverDataReaderToDataTable(dataReader); } if (maxConsultationNum.HasValue && dataReader.NextResult()) { productBrowseInfo.DBConsultations = DataHelper.ConverDataReaderToDataTable(dataReader); } if (dataReader.NextResult() && productBrowseInfo.Product != null) { System.Data.DataTable dataTable = DataHelper.ConverDataReaderToDataTable(dataReader); if (dataTable != null && dataTable.Rows.Count > 0) { System.Data.DataTable dataTable2 = dataTable.Clone(); foreach (System.Data.DataRow dataRow in dataTable.Rows) { bool flag = false; if (dataTable2.Rows.Count > 0) { foreach (System.Data.DataRow dataRow2 in dataTable2.Rows) { if ((int)dataRow2["AttributeId"] == (int)dataRow["AttributeId"]) { flag = true; System.Data.DataRow dataRow3; (dataRow3 = dataRow2)["ValueStr"] = dataRow3["ValueStr"] + ", " + dataRow["ValueStr"]; } } } if (!flag) { System.Data.DataRow dataRow4 = dataTable2.NewRow(); dataRow4["AttributeId"] = dataRow["AttributeId"]; dataRow4["AttributeName"] = dataRow["AttributeName"]; dataRow4["ValueStr"] = dataRow["ValueStr"]; dataTable2.Rows.Add(dataRow4); } } productBrowseInfo.DbAttribute = dataTable2; } } if (dataReader.NextResult()) { productBrowseInfo.DbSKUs = DataHelper.ConverDataReaderToDataTable(dataReader); } if (dataReader.NextResult()) { productBrowseInfo.DbCorrelatives = DataHelper.ConverDataReaderToDataTable(dataReader); } } return(productBrowseInfo); }
public static void Main(String[] args) { // open connection and command SQLRelayConnection sqlrcon = new SQLRelayConnection("Data Source=sqlrelay:9000:/tmp/test.socket;User ID=test;Password=test;Retry Time=0;Tries=1;Debug=false"); sqlrcon.Open(); // execute scalar SQLRelayCommand sqlrcom = (SQLRelayCommand)sqlrcon.CreateCommand(); Console.WriteLine("EXECUTE SCALAR:"); sqlrcom.CommandText = "select 1 from dual"; Int64 value = ExecuteScalar(sqlrcom); checkSuccess(value, 1); Console.WriteLine("\n"); // drop the table Console.WriteLine("DROP TABLE:"); sqlrcom = new SQLRelayCommand("drop table testtable"); sqlrcom.Connection = sqlrcon; ExecuteNonQuery(sqlrcom); Console.WriteLine("\n"); // create the table Console.WriteLine("CREATE TABLE:"); sqlrcom = new SQLRelayCommand("create table testtable (testnumber number, testchar char(40), testvarchar varchar2(40), testdate date, testlong long, testclob clob, testblob blob)", sqlrcon); ExecuteNonQuery(sqlrcom); Console.WriteLine("\n"); // insert Console.WriteLine("INSERT:"); sqlrcom.CommandText = "insert into testtable values (1, 'testchar1', 'testvarchar1', '01-JAN-2001', 'testlong1', 'testclob1', empty_blob())"; sqlrcom.Prepare(); Int64 affectedrows = ExecuteNonQuery(sqlrcom); Console.WriteLine("\n"); // affected rows Console.WriteLine("AFFECTED ROWS:"); checkSuccess(affectedrows, 1); Console.WriteLine("\n"); // bind by position Console.WriteLine("BIND BY POSITION:"); sqlrcom.CommandText = "insert into testtable values (:var1, :var2, :var3, :var4, :var5, :var6, :var7)"; sqlrcom.Parameters.Add("1", 2); sqlrcom.Parameters.Add("2", "testchar2"); sqlrcom.Parameters.Add("3", "testvarchar2"); sqlrcom.Parameters.Add("4", new DateTime(2001, 1, 1, 0, 0, 0, 0)); sqlrcom.Parameters.Add("5", "testlong2"); SQLRelayParameter var6 = new SQLRelayParameter(); var6.ParameterName = "6"; var6.Value = "testclob2"; var6.SQLRelayType = SQLRelayType.Clob; sqlrcom.Parameters.Add(var6); SQLRelayParameter var7 = new SQLRelayParameter(); var7.ParameterName = "7"; var7.Value = System.Text.Encoding.Default.GetBytes("testblob2"); var7.SQLRelayType = SQLRelayType.Blob; sqlrcom.Parameters.Add(var7); checkSuccess(ExecuteNonQuery(sqlrcom), 1); sqlrcom.Parameters.Clear(); sqlrcom.Parameters.Add("1", 3); sqlrcom.Parameters.Add("2", "testchar3"); sqlrcom.Parameters.Add("3", "testvarchar3"); sqlrcom.Parameters.Add("4", new DateTime(2003, 1, 1, 0, 0, 0, 0)); sqlrcom.Parameters.Add("5", "testlong3"); var6.Value = "testclob3"; sqlrcom.Parameters.Add(var6); var7.Value = System.Text.Encoding.Default.GetBytes("testblob3"); var7.SQLRelayType = SQLRelayType.Object; var7.DbType = DbType.Binary; sqlrcom.Parameters.Add(var7); checkSuccess(ExecuteNonQuery(sqlrcom), 1); sqlrcom.Parameters.Clear(); Console.WriteLine("\n"); // bind by name sqlrcom.Parameters.Add("var1", 4); sqlrcom.Parameters.Add("var2", "testchar4"); sqlrcom.Parameters.Add("var3", "testvarchar4"); sqlrcom.Parameters.Add("var4", new DateTime(2004, 1, 1, 0, 0, 0, 0)); sqlrcom.Parameters.Add("var5", "testlong4"); var6.Value = "testclob4"; sqlrcom.Parameters.Add(var6); var7.Value = System.Text.Encoding.Default.GetBytes("testblob4"); sqlrcom.Parameters.Add(var7); checkSuccess(ExecuteNonQuery(sqlrcom), 1); sqlrcom.Parameters.Clear(); sqlrcom.Parameters.Add("var1", 5); sqlrcom.Parameters.Add("var2", "testchar5"); sqlrcom.Parameters.Add("var3", "testvarchar5"); sqlrcom.Parameters.Add("var4", new DateTime(2005, 1, 1, 0, 0, 0, 0)); sqlrcom.Parameters.Add("var5", "testlong5"); var6.Value = "testclob5"; sqlrcom.Parameters.Add(var6); var7.Value = System.Text.Encoding.Default.GetBytes("testblob5"); sqlrcom.Parameters.Add(var7); checkSuccess(ExecuteNonQuery(sqlrcom), 1); sqlrcom.Parameters.Clear(); Console.WriteLine("\n"); // null and empty binds Console.WriteLine("NULL BINDS:"); sqlrcom.Parameters.Add("1", null); sqlrcom.Parameters.Add("2", null); sqlrcom.Parameters.Add("3", null); sqlrcom.Parameters.Add("4", null); sqlrcom.Parameters.Add("5", null); sqlrcom.Parameters.Add("6", null); sqlrcom.Parameters.Add("7", null); checkSuccess(ExecuteNonQuery(sqlrcom), 1); sqlrcom.Parameters.Clear(); Console.WriteLine("\n"); // select Console.WriteLine("SELECT:"); sqlrcom.CommandText = "select * from testtable order by testnumber"; System.Data.IDataReader datareader = ExecuteReader(sqlrcom); checkSuccess(datareader != null, true); Console.WriteLine("\n"); // column count Console.WriteLine("COLUMN COUNT:"); checkSuccess(datareader.FieldCount, 7); Console.WriteLine("\n"); // column names Console.WriteLine("COLUMN NAMES:"); checkSuccess(datareader.GetName(0), "TESTNUMBER"); checkSuccess(datareader.GetName(1), "TESTCHAR"); checkSuccess(datareader.GetName(2), "TESTVARCHAR"); checkSuccess(datareader.GetName(3), "TESTDATE"); checkSuccess(datareader.GetName(4), "TESTLONG"); checkSuccess(datareader.GetName(5), "TESTCLOB"); checkSuccess(datareader.GetName(6), "TESTBLOB"); Console.WriteLine("\n"); // column types Console.WriteLine("COLUMN TYPES:"); checkSuccess(datareader.GetDataTypeName(0), "NUMBER"); checkSuccess(datareader.GetFieldType(0).ToString(), "System.Int64"); checkSuccess(datareader.GetDataTypeName(1), "CHAR"); checkSuccess(datareader.GetFieldType(1).ToString(), "System.String"); checkSuccess(datareader.GetDataTypeName(2), "VARCHAR2"); checkSuccess(datareader.GetFieldType(2).ToString(), "System.String"); checkSuccess(datareader.GetDataTypeName(3), "DATE"); checkSuccess(datareader.GetFieldType(3).ToString(), "System.DateTime"); checkSuccess(datareader.GetDataTypeName(4), "LONG"); checkSuccess(datareader.GetFieldType(4).ToString(), "System.Byte[]"); checkSuccess(datareader.GetDataTypeName(5), "CLOB"); checkSuccess(datareader.GetFieldType(5).ToString(), "System.String"); checkSuccess(datareader.GetDataTypeName(6), "BLOB"); checkSuccess(datareader.GetFieldType(6).ToString(), "System.Byte[]"); Console.WriteLine("\n"); // schema table Console.WriteLine("SCHEMA TABLE:"); DataTable schematable = datareader.GetSchemaTable(); checkSuccess(Convert.ToString(schematable.Rows[0]["ColumnName"]), "TESTNUMBER"); checkSuccess(Convert.ToInt64(schematable.Rows[0]["ColumnOrdinal"]), 0); checkSuccess(Convert.ToInt64(schematable.Rows[0]["ColumnSize"]), 22); checkSuccess(Convert.ToInt64(schematable.Rows[0]["NumericPrecision"]), 0); checkSuccess(Convert.ToInt64(schematable.Rows[0]["NumericScale"]), 129); checkSuccess(Convert.ToBoolean(schematable.Rows[0]["IsUnique"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[0]["IsKey"]), false); checkSuccess(Convert.ToString(schematable.Rows[0]["BaseServerName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[0]["BaseCatalogName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[0]["BaseColumnName"]), "TESTNUMBER"); checkSuccess(Convert.ToString(schematable.Rows[0]["BaseSchemaName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[0]["BaseTableName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[0]["DataType"]), "System.Int64"); checkSuccess(Convert.ToBoolean(schematable.Rows[0]["AllowDBNull"]), true); checkSuccess(Convert.ToString(schematable.Rows[0]["ProviderType"]), "NUMBER"); checkSuccess(Convert.ToBoolean(schematable.Rows[0]["IsAliased"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[0]["IsExpression"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[0]["IsIdentity"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[0]["IsAutoIncrement"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[0]["IsRowVersion"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[0]["IsHidden"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[0]["IsLong"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[0]["IsReadOnly"]), false); checkSuccess(Convert.ToString(schematable.Rows[0]["ProviderSpecificDataType"]), "NUMBER"); checkSuccess(Convert.ToString(schematable.Rows[0]["DataTypeName"]), "NUMBER"); checkSuccess(Convert.ToString(schematable.Rows[0]["XmlSchemaCollectionDatabase"]), ""); checkSuccess(Convert.ToString(schematable.Rows[0]["XmlSchemaCollectionOwningSchema"]), ""); checkSuccess(Convert.ToString(schematable.Rows[0]["XmlSchemaCollectionName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[1]["ColumnName"]), "TESTCHAR"); checkSuccess(Convert.ToInt64(schematable.Rows[1]["ColumnOrdinal"]), 1); checkSuccess(Convert.ToInt64(schematable.Rows[1]["ColumnSize"]), 40); checkSuccess(Convert.ToInt64(schematable.Rows[1]["NumericPrecision"]), 0); checkSuccess(Convert.ToInt64(schematable.Rows[1]["NumericScale"]), 0); checkSuccess(Convert.ToBoolean(schematable.Rows[1]["IsUnique"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[1]["IsKey"]), false); checkSuccess(Convert.ToString(schematable.Rows[1]["BaseServerName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[1]["BaseCatalogName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[1]["BaseColumnName"]), "TESTCHAR"); checkSuccess(Convert.ToString(schematable.Rows[1]["BaseSchemaName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[1]["BaseTableName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[1]["DataType"]), "System.String"); checkSuccess(Convert.ToBoolean(schematable.Rows[1]["AllowDBNull"]), true); checkSuccess(Convert.ToString(schematable.Rows[1]["ProviderType"]), "CHAR"); checkSuccess(Convert.ToBoolean(schematable.Rows[1]["IsAliased"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[1]["IsExpression"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[1]["IsIdentity"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[1]["IsAutoIncrement"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[1]["IsRowVersion"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[1]["IsHidden"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[1]["IsLong"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[1]["IsReadOnly"]), false); checkSuccess(Convert.ToString(schematable.Rows[1]["ProviderSpecificDataType"]), "CHAR"); checkSuccess(Convert.ToString(schematable.Rows[1]["DataTypeName"]), "CHAR"); checkSuccess(Convert.ToString(schematable.Rows[1]["XmlSchemaCollectionDatabase"]), ""); checkSuccess(Convert.ToString(schematable.Rows[1]["XmlSchemaCollectionOwningSchema"]), ""); checkSuccess(Convert.ToString(schematable.Rows[1]["XmlSchemaCollectionName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[2]["ColumnName"]), "TESTVARCHAR"); checkSuccess(Convert.ToInt64(schematable.Rows[2]["ColumnOrdinal"]), 2); checkSuccess(Convert.ToInt64(schematable.Rows[2]["ColumnSize"]), 40); checkSuccess(Convert.ToInt64(schematable.Rows[2]["NumericPrecision"]), 0); checkSuccess(Convert.ToInt64(schematable.Rows[2]["NumericScale"]), 0); checkSuccess(Convert.ToBoolean(schematable.Rows[2]["IsUnique"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[2]["IsKey"]), false); checkSuccess(Convert.ToString(schematable.Rows[2]["BaseServerName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[2]["BaseCatalogName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[2]["BaseColumnName"]), "TESTVARCHAR"); checkSuccess(Convert.ToString(schematable.Rows[2]["BaseSchemaName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[2]["BaseTableName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[2]["DataType"]), "System.String"); checkSuccess(Convert.ToBoolean(schematable.Rows[2]["AllowDBNull"]), true); checkSuccess(Convert.ToString(schematable.Rows[2]["ProviderType"]), "VARCHAR2"); checkSuccess(Convert.ToBoolean(schematable.Rows[2]["IsAliased"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[2]["IsExpression"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[2]["IsIdentity"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[2]["IsAutoIncrement"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[2]["IsRowVersion"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[2]["IsHidden"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[2]["IsLong"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[2]["IsReadOnly"]), false); checkSuccess(Convert.ToString(schematable.Rows[2]["ProviderSpecificDataType"]), "VARCHAR2"); checkSuccess(Convert.ToString(schematable.Rows[2]["DataTypeName"]), "VARCHAR2"); checkSuccess(Convert.ToString(schematable.Rows[2]["XmlSchemaCollectionDatabase"]), ""); checkSuccess(Convert.ToString(schematable.Rows[2]["XmlSchemaCollectionOwningSchema"]), ""); checkSuccess(Convert.ToString(schematable.Rows[2]["XmlSchemaCollectionName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[3]["ColumnName"]), "TESTDATE"); checkSuccess(Convert.ToInt64(schematable.Rows[3]["ColumnOrdinal"]), 3); checkSuccess(Convert.ToInt64(schematable.Rows[3]["ColumnSize"]), 7); checkSuccess(Convert.ToInt64(schematable.Rows[3]["NumericPrecision"]), 0); checkSuccess(Convert.ToInt64(schematable.Rows[3]["NumericScale"]), 0); checkSuccess(Convert.ToBoolean(schematable.Rows[3]["IsUnique"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[3]["IsKey"]), false); checkSuccess(Convert.ToString(schematable.Rows[3]["BaseServerName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[3]["BaseCatalogName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[3]["BaseColumnName"]), "TESTDATE"); checkSuccess(Convert.ToString(schematable.Rows[3]["BaseSchemaName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[3]["BaseTableName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[3]["DataType"]), "System.DateTime"); checkSuccess(Convert.ToBoolean(schematable.Rows[3]["AllowDBNull"]), true); checkSuccess(Convert.ToString(schematable.Rows[3]["ProviderType"]), "DATE"); checkSuccess(Convert.ToBoolean(schematable.Rows[3]["IsAliased"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[3]["IsExpression"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[3]["IsIdentity"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[3]["IsAutoIncrement"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[3]["IsRowVersion"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[3]["IsHidden"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[3]["IsLong"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[3]["IsReadOnly"]), false); checkSuccess(Convert.ToString(schematable.Rows[3]["ProviderSpecificDataType"]), "DATE"); checkSuccess(Convert.ToString(schematable.Rows[3]["DataTypeName"]), "DATE"); checkSuccess(Convert.ToString(schematable.Rows[3]["XmlSchemaCollectionDatabase"]), ""); checkSuccess(Convert.ToString(schematable.Rows[3]["XmlSchemaCollectionOwningSchema"]), ""); checkSuccess(Convert.ToString(schematable.Rows[3]["XmlSchemaCollectionName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[4]["ColumnName"]), "TESTLONG"); checkSuccess(Convert.ToInt64(schematable.Rows[4]["ColumnOrdinal"]), 4); checkSuccess(Convert.ToInt64(schematable.Rows[4]["ColumnSize"]), 0); checkSuccess(Convert.ToInt64(schematable.Rows[4]["NumericPrecision"]), 0); checkSuccess(Convert.ToInt64(schematable.Rows[4]["NumericScale"]), 0); checkSuccess(Convert.ToBoolean(schematable.Rows[4]["IsUnique"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[4]["IsKey"]), false); checkSuccess(Convert.ToString(schematable.Rows[4]["BaseServerName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[4]["BaseCatalogName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[4]["BaseColumnName"]), "TESTLONG"); checkSuccess(Convert.ToString(schematable.Rows[4]["BaseSchemaName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[4]["BaseTableName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[4]["DataType"]), "System.Byte[]"); checkSuccess(Convert.ToBoolean(schematable.Rows[4]["AllowDBNull"]), true); checkSuccess(Convert.ToString(schematable.Rows[4]["ProviderType"]), "LONG"); checkSuccess(Convert.ToBoolean(schematable.Rows[4]["IsAliased"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[4]["IsExpression"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[4]["IsIdentity"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[4]["IsAutoIncrement"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[4]["IsRowVersion"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[4]["IsHidden"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[4]["IsLong"]), true); checkSuccess(Convert.ToBoolean(schematable.Rows[4]["IsReadOnly"]), false); checkSuccess(Convert.ToString(schematable.Rows[4]["ProviderSpecificDataType"]), "LONG"); checkSuccess(Convert.ToString(schematable.Rows[4]["DataTypeName"]), "LONG"); checkSuccess(Convert.ToString(schematable.Rows[4]["XmlSchemaCollectionDatabase"]), ""); checkSuccess(Convert.ToString(schematable.Rows[4]["XmlSchemaCollectionOwningSchema"]), ""); checkSuccess(Convert.ToString(schematable.Rows[4]["XmlSchemaCollectionName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[5]["ColumnName"]), "TESTCLOB"); checkSuccess(Convert.ToInt64(schematable.Rows[5]["ColumnOrdinal"]), 5); checkSuccess(Convert.ToInt64(schematable.Rows[5]["ColumnSize"]), 0); checkSuccess(Convert.ToInt64(schematable.Rows[5]["NumericPrecision"]), 0); checkSuccess(Convert.ToInt64(schematable.Rows[5]["NumericScale"]), 0); checkSuccess(Convert.ToBoolean(schematable.Rows[5]["IsUnique"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[5]["IsKey"]), false); checkSuccess(Convert.ToString(schematable.Rows[5]["BaseServerName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[5]["BaseCatalogName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[5]["BaseColumnName"]), "TESTCLOB"); checkSuccess(Convert.ToString(schematable.Rows[5]["BaseSchemaName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[5]["BaseTableName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[5]["DataType"]), "System.String"); checkSuccess(Convert.ToBoolean(schematable.Rows[5]["AllowDBNull"]), true); checkSuccess(Convert.ToString(schematable.Rows[5]["ProviderType"]), "CLOB"); checkSuccess(Convert.ToBoolean(schematable.Rows[5]["IsAliased"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[5]["IsExpression"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[5]["IsIdentity"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[5]["IsAutoIncrement"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[5]["IsRowVersion"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[5]["IsHidden"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[5]["IsLong"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[5]["IsReadOnly"]), false); checkSuccess(Convert.ToString(schematable.Rows[5]["ProviderSpecificDataType"]), "CLOB"); checkSuccess(Convert.ToString(schematable.Rows[5]["DataTypeName"]), "CLOB"); checkSuccess(Convert.ToString(schematable.Rows[5]["XmlSchemaCollectionDatabase"]), ""); checkSuccess(Convert.ToString(schematable.Rows[5]["XmlSchemaCollectionOwningSchema"]), ""); checkSuccess(Convert.ToString(schematable.Rows[5]["XmlSchemaCollectionName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[6]["ColumnName"]), "TESTBLOB"); checkSuccess(Convert.ToInt64(schematable.Rows[6]["ColumnOrdinal"]), 6); checkSuccess(Convert.ToInt64(schematable.Rows[6]["ColumnSize"]), 0); checkSuccess(Convert.ToInt64(schematable.Rows[6]["NumericPrecision"]), 0); checkSuccess(Convert.ToInt64(schematable.Rows[6]["NumericScale"]), 0); checkSuccess(Convert.ToBoolean(schematable.Rows[6]["IsUnique"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[6]["IsKey"]), false); checkSuccess(Convert.ToString(schematable.Rows[6]["BaseServerName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[6]["BaseCatalogName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[6]["BaseColumnName"]), "TESTBLOB"); checkSuccess(Convert.ToString(schematable.Rows[6]["BaseSchemaName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[6]["BaseTableName"]), ""); checkSuccess(Convert.ToString(schematable.Rows[6]["DataType"]), "System.Byte[]"); checkSuccess(Convert.ToBoolean(schematable.Rows[6]["AllowDBNull"]), true); checkSuccess(Convert.ToString(schematable.Rows[6]["ProviderType"]), "BLOB"); checkSuccess(Convert.ToBoolean(schematable.Rows[6]["IsAliased"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[6]["IsExpression"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[6]["IsIdentity"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[6]["IsAutoIncrement"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[6]["IsRowVersion"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[6]["IsHidden"]), false); checkSuccess(Convert.ToBoolean(schematable.Rows[6]["IsLong"]), true); checkSuccess(Convert.ToBoolean(schematable.Rows[6]["IsReadOnly"]), false); checkSuccess(Convert.ToString(schematable.Rows[6]["ProviderSpecificDataType"]), "BLOB"); checkSuccess(Convert.ToString(schematable.Rows[6]["DataTypeName"]), "BLOB"); checkSuccess(Convert.ToString(schematable.Rows[6]["XmlSchemaCollectionDatabase"]), ""); checkSuccess(Convert.ToString(schematable.Rows[6]["XmlSchemaCollectionOwningSchema"]), ""); checkSuccess(Convert.ToString(schematable.Rows[6]["XmlSchemaCollectionName"]), ""); Console.WriteLine("\n"); // fields by index Console.WriteLine("FIELDS BY INDEX:"); checkSuccess(datareader.Read(), true); checkSuccess(datareader.GetInt16(0), 1); checkSuccess(datareader.GetInt32(0), 1); checkSuccess(datareader.GetInt64(0), 1); checkSuccess(Convert.ToInt64(datareader[0]), 1); checkSuccess(datareader.GetString(1), "testchar1 "); checkSuccess(Convert.ToString(datareader[1]), "testchar1 "); checkSuccess(datareader.GetString(2), "testvarchar1"); checkSuccess(Convert.ToString(datareader[2]), "testvarchar1"); checkSuccess(datareader.GetString(3), "01-JAN-01"); checkSuccess(Convert.ToString(datareader[3]), "1/1/2001 12:00:00 AM"); checkSuccess(datareader.GetString(4), "testlong1"); checkSuccess(System.Text.Encoding.Default.GetString((Byte[])datareader[4]), "testlong1"); checkSuccess(datareader.GetString(5), "testclob1"); checkSuccess(Convert.ToString(datareader[5]), "testclob1"); checkSuccess(datareader[6], null); Console.WriteLine("\n"); // fields by name Console.WriteLine("FIELDS BY NAME:"); checkSuccess(Convert.ToInt64(datareader["TESTNUMBER"]), 1); checkSuccess(datareader.GetInt16(datareader.GetOrdinal("TESTNUMBER")), 1); checkSuccess(datareader.GetInt32(datareader.GetOrdinal("TESTNUMBER")), 1); checkSuccess(datareader.GetInt64(datareader.GetOrdinal("TESTNUMBER")), 1); checkSuccess(Convert.ToString(datareader["TESTCHAR"]), "testchar1 "); checkSuccess(datareader.GetString(datareader.GetOrdinal("TESTCHAR")), "testchar1 "); checkSuccess(Convert.ToString(datareader["TESTVARCHAR"]), "testvarchar1"); checkSuccess(datareader.GetString(datareader.GetOrdinal("TESTVARCHAR")), "testvarchar1"); checkSuccess(Convert.ToString(datareader["TESTDATE"]), "1/1/2001 12:00:00 AM"); checkSuccess(datareader.GetString(datareader.GetOrdinal("TESTDATE")), "01-JAN-01"); checkSuccess(System.Text.Encoding.Default.GetString((Byte[])datareader["TESTLONG"]), "testlong1"); checkSuccess(datareader.GetString(datareader.GetOrdinal("TESTLONG")), "testlong1"); checkSuccess(Convert.ToString(datareader["TESTCLOB"]), "testclob1"); checkSuccess(datareader.GetString(datareader.GetOrdinal("TESTCLOB")), "testclob1"); checkSuccess(datareader["TESTBLOB"], null); checkSuccess(datareader.GetString(datareader.GetOrdinal("TESTBLOB")), ""); Console.WriteLine("\n"); // fields by array Console.WriteLine("FIELDS BY ARRAY:"); Object[] fields = new Object[datareader.FieldCount]; checkSuccess((Int64)datareader.GetValues(fields), datareader.FieldCount); checkSuccess(Convert.ToInt64(fields[0]), 1); checkSuccess(Convert.ToString(fields[1]), "testchar1 "); checkSuccess(Convert.ToString(fields[2]), "testvarchar1"); checkSuccess(Convert.ToString(fields[3]), "1/1/2001 12:00:00 AM"); checkSuccess(System.Text.Encoding.Default.GetString((Byte[])fields[4]), "testlong1"); checkSuccess(Convert.ToString(fields[5]), "testclob1"); checkSuccess(fields[6], null); Console.WriteLine("\n"); // more rows Console.WriteLine("MORE ROWS:"); checkSuccess(datareader.Read(), true); checkSuccess(datareader.GetInt64(0), 2); checkSuccess(System.Text.Encoding.Default.GetString((Byte[])datareader[6]), "testblob2"); checkSuccess(datareader.Read(), true); checkSuccess(datareader.GetInt64(0), 3); checkSuccess(datareader.Read(), true); checkSuccess(datareader.GetInt64(0), 4); checkSuccess(datareader.Read(), true); checkSuccess(datareader.GetInt64(0), 5); checkSuccess(datareader.Read(), true); checkSuccess(datareader.GetString(0), ""); checkSuccess(datareader.Read(), false); checkSuccess(datareader.GetString(0), null); Console.WriteLine("\n"); // commit and rollback Console.WriteLine("COMMIT AND ROLLBACK:"); SQLRelayConnection sqlrcon2 = new SQLRelayConnection("Data Source=sqlrelay:9000:/tmp/test.socket;User ID=test;Password=test;Retry Time=0;Tries=1;Debug=false"); sqlrcon2.Open(); SQLRelayCommand sqlrcom2 = new SQLRelayCommand("select count(*) from testtable", sqlrcon2); checkSuccess(Convert.ToInt64(sqlrcom2.ExecuteScalar()), 0); SQLRelayTransaction sqlrtran = sqlrcon.BeginTransaction(); sqlrtran.Commit(); checkSuccess(Convert.ToInt64(sqlrcom2.ExecuteScalar()), 6); sqlrtran = sqlrcon.BeginTransaction(); sqlrcom.CommandText = "insert into testtable values (6, 'testchar6', 'testvarchar6', '01-JAN-2006', 'testlong6', 'testclob6', empty_blob())"; checkSuccess(sqlrcom.ExecuteNonQuery(), 1); sqlrcom.CommandText = "select count(*) from testtable"; checkSuccess(Convert.ToInt64(sqlrcom.ExecuteScalar()), 7); sqlrtran.Rollback(); checkSuccess(Convert.ToInt64(sqlrcom2.ExecuteScalar()), 6); sqlrcon2.Close(); Console.WriteLine("\n"); // output bind by name Console.WriteLine("OUTPUT BINDS BY NAME:"); sqlrcom.CommandText = "begin :numvar:=1; :stringvar:='hello'; :floatvar:=2.5; :datevar:='03-FEB-2001'; end;"; SQLRelayParameter numvar = new SQLRelayParameter(); numvar.ParameterName = "numvar"; numvar.Direction = ParameterDirection.Output; numvar.DbType = DbType.Int64; sqlrcom.Parameters.Add(numvar); SQLRelayParameter stringvar = new SQLRelayParameter(); stringvar.ParameterName = "stringvar"; stringvar.Direction = ParameterDirection.Output; stringvar.DbType = DbType.String; stringvar.Size = 20; sqlrcom.Parameters.Add(stringvar); SQLRelayParameter floatvar = new SQLRelayParameter(); floatvar.ParameterName = "floatvar"; floatvar.Direction = ParameterDirection.Output; floatvar.DbType = DbType.Double; sqlrcom.Parameters.Add(floatvar); SQLRelayParameter datevar = new SQLRelayParameter(); datevar.ParameterName = "datevar"; datevar.Direction = ParameterDirection.Output; datevar.DbType = DbType.DateTime; sqlrcom.Parameters.Add(datevar); checkSuccess(ExecuteNonQuery(sqlrcom), 1); checkSuccess(Convert.ToInt64(numvar.Value), 1); checkSuccess(Convert.ToString(stringvar.Value), "hello"); checkSuccess(Convert.ToInt64(stringvar.Size), 5); checkSuccess(Convert.ToString(floatvar.Value), "2.5"); checkSuccess(Convert.ToInt64(Convert.ToDateTime(datevar.Value).Year), 2001); checkSuccess(Convert.ToInt64(Convert.ToDateTime(datevar.Value).Month), 2); checkSuccess(Convert.ToInt64(Convert.ToDateTime(datevar.Value).Day), 3); sqlrcom.Parameters.Clear(); Console.WriteLine("\n"); // output bind by position Console.WriteLine("OUTPUT BINDS BY POSITION:"); sqlrcom.CommandText = "begin :numvar:=1; :stringvar:='hello'; :floatvar:=2.5; :datevar:='03-FEB-2001'; end;"; numvar = new SQLRelayParameter(); numvar.ParameterName = "1"; numvar.Direction = ParameterDirection.Output; numvar.DbType = DbType.Int64; sqlrcom.Parameters.Add(numvar); stringvar = new SQLRelayParameter(); stringvar.ParameterName = "2"; stringvar.Direction = ParameterDirection.Output; stringvar.DbType = DbType.String; stringvar.Size = 20; sqlrcom.Parameters.Add(stringvar); floatvar = new SQLRelayParameter(); floatvar.ParameterName = "3"; floatvar.Direction = ParameterDirection.Output; floatvar.DbType = DbType.Double; sqlrcom.Parameters.Add(floatvar); datevar = new SQLRelayParameter(); datevar.ParameterName = "4"; datevar.Direction = ParameterDirection.Output; datevar.DbType = DbType.DateTime; sqlrcom.Parameters.Add(datevar); checkSuccess(ExecuteNonQuery(sqlrcom), 1); checkSuccess(Convert.ToInt64(numvar.Value), 1); checkSuccess(Convert.ToString(stringvar.Value), "hello"); checkSuccess(Convert.ToInt64(stringvar.Size), 5); checkSuccess(Convert.ToString(floatvar.Value), "2.5"); checkSuccess(Convert.ToInt64(Convert.ToDateTime(datevar.Value).Year), 2001); checkSuccess(Convert.ToInt64(Convert.ToDateTime(datevar.Value).Month), 2); checkSuccess(Convert.ToInt64(Convert.ToDateTime(datevar.Value).Day), 3); sqlrcom.Parameters.Clear(); Console.WriteLine("\n"); // cursor binds using NextResult Console.WriteLine("CURSOR BINDS USING NEXTRESULT:"); sqlrcom.CommandText = "create or replace package types is type cursorType is ref cursor; end;"; checkSuccess(ExecuteNonQuery(sqlrcom), 0); sqlrcom.CommandText = "create or replace function sp_testtable(value in number) return types.cursortype is l_cursor types.cursorType; begin open l_cursor for select * from testtable where testnumber>value; return l_cursor; end;"; checkSuccess(ExecuteNonQuery(sqlrcom), 0); sqlrcom.CommandText = "begin :curs1:=sp_testtable(2); :curs2:=sp_testtable(0); end;"; SQLRelayParameter curs1 = new SQLRelayParameter(); curs1.ParameterName = "curs1"; curs1.SQLRelayType = SQLRelayType.Cursor; curs1.Direction = ParameterDirection.Output; curs1.Value = null; sqlrcom.Parameters.Add(curs1); SQLRelayParameter curs2 = new SQLRelayParameter(); curs2.ParameterName = "curs2"; curs2.SQLRelayType = SQLRelayType.Cursor; curs2.Direction = ParameterDirection.Output; curs2.Value = null; sqlrcom.Parameters.Add(curs2); datareader = ExecuteReader(sqlrcom); checkSuccess(datareader != null, true); sqlrcom.Parameters.Clear(); datareader.Read(); checkSuccess(datareader.GetInt64(0), 3); datareader.Read(); checkSuccess(datareader.GetInt64(0), 4); datareader.Read(); checkSuccess(datareader.GetInt64(0), 5); datareader.Close(); checkSuccess(datareader.NextResult(), true); datareader.Read(); checkSuccess(datareader.GetInt64(0), 1); datareader.Read(); checkSuccess(datareader.GetInt64(0), 2); datareader.Read(); checkSuccess(datareader.GetInt64(0), 3); datareader.Close(); checkSuccess(datareader.NextResult(), false); sqlrcom.CommandText = "drop package types"; checkSuccess(ExecuteNonQuery(sqlrcom), 0); Console.WriteLine("\n"); // cursor binds Console.WriteLine("CURSOR BINDS:"); sqlrcom.CommandText = "create or replace package types is type cursorType is ref cursor; end;"; checkSuccess(ExecuteNonQuery(sqlrcom), 0); sqlrcom.CommandText = "create or replace function sp_testtable(value in number) return types.cursortype is l_cursor types.cursorType; begin open l_cursor for select * from testtable where testnumber>value; return l_cursor; end;"; checkSuccess(ExecuteNonQuery(sqlrcom), 0); sqlrcom.CommandText = "begin :curs1:=sp_testtable(2); :curs2:=sp_testtable(0); end;"; curs1 = new SQLRelayParameter(); curs1.ParameterName = "curs1"; curs1.SQLRelayType = SQLRelayType.Cursor; curs1.Direction = ParameterDirection.Output; curs1.Value = null; sqlrcom.Parameters.Add(curs1); curs2 = new SQLRelayParameter(); curs2.ParameterName = "curs2"; curs2.SQLRelayType = SQLRelayType.Cursor; curs2.Direction = ParameterDirection.Output; curs2.Value = null; sqlrcom.Parameters.Add(curs2); checkSuccess(ExecuteNonQuery(sqlrcom), 1); sqlrcom.Parameters.Clear(); SQLRelayDataReader curs1reader = (SQLRelayDataReader)curs1.Value; curs1reader.Read(); checkSuccess(curs1reader.GetInt64(0), 3); curs1reader.Read(); checkSuccess(curs1reader.GetInt64(0), 4); curs1reader.Read(); checkSuccess(curs1reader.GetInt64(0), 5); curs1reader.Close(); SQLRelayDataReader curs2reader = (SQLRelayDataReader)curs2.Value; curs2reader.Read(); checkSuccess(curs2reader.GetInt64(0), 1); curs2reader.Read(); checkSuccess(curs2reader.GetInt64(0), 2); curs2reader.Read(); checkSuccess(curs2reader.GetInt64(0), 3); curs2reader.Close(); sqlrcom.CommandText = "drop package types"; checkSuccess(ExecuteNonQuery(sqlrcom), 0); Console.WriteLine("\n"); // clob and blob output bind Console.WriteLine("CLOB AND BLOB OUTPUT BINDS:"); sqlrcom.CommandText = "drop table testtable1"; ExecuteNonQuery(sqlrcom); sqlrcom.CommandText = "create table testtable1 (testclob clob, testblob blob)"; checkSuccess(ExecuteNonQuery(sqlrcom), 0); sqlrcom.CommandText = "insert into testtable1 values ('hello', :var1)"; SQLRelayParameter var1 = new SQLRelayParameter(); var1.ParameterName = "var1"; var1.Value = System.Text.Encoding.Default.GetBytes("hello"); var1.SQLRelayType = SQLRelayType.Blob; sqlrcom.Parameters.Add(var1); checkSuccess(ExecuteNonQuery(sqlrcom), 1); sqlrcom.Parameters.Clear(); sqlrcom.CommandText = "begin select testclob into :clobvar from testtable1; select testblob into :blobvar from testtable1; end;"; SQLRelayParameter clobvar = new SQLRelayParameter(); clobvar.Direction = ParameterDirection.Output; clobvar.ParameterName = "clobvar"; clobvar.SQLRelayType = SQLRelayType.Clob; sqlrcom.Parameters.Add(clobvar); SQLRelayParameter blobvar = new SQLRelayParameter(); blobvar.ParameterName = "blobvar"; blobvar.SQLRelayType = SQLRelayType.Blob; blobvar.Direction = ParameterDirection.Output; sqlrcom.Parameters.Add(blobvar); checkSuccess(ExecuteNonQuery(sqlrcom), 1); sqlrcom.Parameters.Clear(); checkSuccess(Convert.ToString(clobvar.Value), "hello", 5); checkSuccess(clobvar.Size, 5); checkSuccess(System.Text.Encoding.Default.GetString((byte[])blobvar.Value), "hello"); checkSuccess(blobvar.Size, 5); sqlrcom.CommandText = "drop table testtable1"; checkSuccess(ExecuteNonQuery(sqlrcom), 0); Console.WriteLine("\n"); // null and empty clobs and blobs Console.WriteLine("NULL AND EMPTY CLOBS AND BLOBS:"); sqlrcom.CommandText = "create table testtable1 (testclob1 clob, testclob2 clob, testblob1 blob, testblob2 blob)"; checkSuccess(ExecuteNonQuery(sqlrcom), 0); sqlrcom.CommandText = "insert into testtable1 values (:testclob1, :testclob2, :testblob1, :testblob2)"; SQLRelayParameter testclob1 = new SQLRelayParameter(); testclob1.ParameterName = "testclob1"; testclob1.SQLRelayType = SQLRelayType.Clob; testclob1.Value = ""; sqlrcom.Parameters.Add(testclob1); SQLRelayParameter testclob2 = new SQLRelayParameter(); testclob2.ParameterName = "testclob2"; testclob2.SQLRelayType = SQLRelayType.Clob; testclob2.Value = null; sqlrcom.Parameters.Add(testclob2); SQLRelayParameter testblob1 = new SQLRelayParameter(); testblob1.ParameterName = "testblob1"; testblob1.SQLRelayType = SQLRelayType.Blob; testblob1.Value = System.Text.Encoding.Default.GetBytes(""); sqlrcom.Parameters.Add(testblob1); SQLRelayParameter testblob2 = new SQLRelayParameter(); testblob2.ParameterName = "testblob2"; testblob2.SQLRelayType = SQLRelayType.Blob; testblob2.Value = null; sqlrcom.Parameters.Add(testblob2); checkSuccess(ExecuteNonQuery(sqlrcom), 1); sqlrcom.Parameters.Clear(); sqlrcom.CommandText = "select * from testtable1"; datareader = ExecuteReader(sqlrcom); checkSuccess(datareader != null, true); checkSuccess(datareader.Read(), true); // FIXME: I'd expect these to come out as empty strings, not null's checkSuccess(datareader.GetString(0), ""); checkSuccess(datareader.GetString(1), ""); checkSuccess(datareader.GetString(2), ""); checkSuccess(datareader.GetString(3), ""); sqlrcom.CommandText = "drop table testtable1"; checkSuccess(ExecuteNonQuery(sqlrcom), 0); Console.WriteLine("\n"); // switching connection of command Console.WriteLine("SWITCHING CONNECTION OF COMMAND:"); sqlrcom.Connection = sqlrcon2; sqlrcom.CommandText = "select count(*) from testtable"; try { sqlrcom.ExecuteScalar(); checkSuccess(false, true); } catch { // this should fail because sqlrcon2 was closed earlier checkSuccess(true, true); } sqlrcom.Connection = sqlrcon; checkSuccess(ExecuteScalar(sqlrcom), 6); Console.WriteLine("\n"); // closed datareader Console.WriteLine("CLOSED DATAREADER:"); sqlrcom.CommandText = "select * from testtable"; datareader = sqlrcom.ExecuteReader(); checkSuccess(datareader != null, true); datareader.Read(); datareader.Close(); checkSuccess(datareader.IsClosed, true); try { datareader.Read(); checkSuccess(false, true); } catch { // this should fail because datareader was closed earlier checkSuccess(true, true); } Console.WriteLine("\n"); // has rows Console.WriteLine("HAS ROWS:"); sqlrcom.CommandText = "select * from testtable"; datareader = ExecuteReader(sqlrcom); checkSuccess(datareader != null, true); checkSuccess(((SQLRelayDataReader)datareader).HasRows, true); sqlrcom.CommandText = "delete from testtable"; checkSuccess(ExecuteNonQuery(sqlrcom), 6); sqlrcom.CommandText = "select * from testtable"; datareader = ExecuteReader(sqlrcom); checkSuccess(datareader != null, true); checkSuccess(((SQLRelayDataReader)datareader).HasRows, false); Console.WriteLine("\n"); // drop table Console.WriteLine("DROP TABLE:"); sqlrcom.CommandText = "drop table testtable"; checkSuccess(ExecuteNonQuery(sqlrcom), 0); Console.WriteLine("\n"); // invalid queries sqlrcon.Close(); }
public bool NextResult() { return(_innerReader.NextResult()); }
private PurchaseOrderInfo ConvertOrderToPurchaseOrder(OrderInfo order) { PurchaseOrderInfo result; if (order == null) { result = null; } else { StringBuilder stringBuilder = new StringBuilder(); string text = ""; foreach (LineItemInfo current in order.LineItems.Values) { stringBuilder.AppendFormat("'" + current.SkuId + "',", new object[0]); } if (stringBuilder.Length > 0) { stringBuilder = stringBuilder.Remove(stringBuilder.Length - 1, 1); text = string.Format("SELECT S.SkuId, S.CostPrice, p.ProductName FROM Hishop_Products P JOIN Hishop_SKUs S ON P.ProductId = S.ProductId WHERE S.SkuId IN({0});", stringBuilder); } if (order.Gifts.Count > 0) { StringBuilder stringBuilder2 = new StringBuilder(); foreach (OrderGiftInfo current2 in order.Gifts) { stringBuilder2.AppendFormat(current2.GiftId.ToString() + ",", new object[0]); } stringBuilder2.Remove(stringBuilder2.Length - 1, 1); text += string.Format(" SELECT GiftId, CostPrice FROM Hishop_Gifts WHERE GiftId IN({0});", stringBuilder2.ToString()); } System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand(text); Dictionary <string, PurchaseOrderItemInfo> dictionary = new Dictionary <string, PurchaseOrderItemInfo>(); Dictionary <int, decimal> dictionary2 = new Dictionary <int, decimal>(); using (System.Data.IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand)) { if (order.LineItems.Values.Count > 0) { while (dataReader.Read()) { PurchaseOrderItemInfo purchaseOrderItemInfo = new PurchaseOrderItemInfo(); if (dataReader["CostPrice"] != DBNull.Value) { purchaseOrderItemInfo.ItemCostPrice = (decimal)dataReader["CostPrice"]; } purchaseOrderItemInfo.ItemHomeSiteDescription = (string)dataReader["ProductName"]; dictionary.Add((string)dataReader["SkuId"], purchaseOrderItemInfo); } } if (order.Gifts.Count > 0) { if (order.LineItems.Count > 0) { dataReader.NextResult(); } while (dataReader.Read()) { dictionary2.Add((int)dataReader["GiftId"], (DBNull.Value == dataReader["CostPrice"]) ? 0m : Convert.ToDecimal(dataReader["CostPrice"])); } } } IUser user = Users.GetUser(HiContext.Current.SiteSettings.UserId.Value, false); if (user == null || user.UserRole != UserRole.Distributor) { result = null; } else { Distributor distributor = user as Distributor; PurchaseOrderInfo purchaseOrderInfo = new PurchaseOrderInfo(); purchaseOrderInfo.PurchaseOrderId = "PO" + order.OrderId; purchaseOrderInfo.OrderId = order.OrderId; purchaseOrderInfo.Remark = order.Remark; purchaseOrderInfo.PurchaseStatus = OrderStatus.WaitBuyerPay; purchaseOrderInfo.DistributorId = distributor.UserId; purchaseOrderInfo.Distributorname = distributor.Username; purchaseOrderInfo.DistributorEmail = distributor.Email; purchaseOrderInfo.DistributorRealName = distributor.RealName; purchaseOrderInfo.DistributorQQ = distributor.QQ; purchaseOrderInfo.DistributorWangwang = distributor.Wangwang; purchaseOrderInfo.DistributorMSN = distributor.MSN; purchaseOrderInfo.ShippingRegion = order.ShippingRegion; purchaseOrderInfo.Address = order.Address; purchaseOrderInfo.ZipCode = order.ZipCode; purchaseOrderInfo.ShipTo = order.ShipTo; purchaseOrderInfo.TelPhone = order.TelPhone; purchaseOrderInfo.CellPhone = order.CellPhone; purchaseOrderInfo.ShipToDate = order.ShipToDate; purchaseOrderInfo.ShippingModeId = order.ShippingModeId; purchaseOrderInfo.ModeName = order.ModeName; purchaseOrderInfo.RegionId = order.RegionId; purchaseOrderInfo.Freight = order.Freight; purchaseOrderInfo.AdjustedFreight = order.Freight; purchaseOrderInfo.ShipOrderNumber = order.ShipOrderNumber; purchaseOrderInfo.Weight = order.Weight; purchaseOrderInfo.RefundStatus = RefundStatus.None; purchaseOrderInfo.OrderTotal = order.GetTotal(); purchaseOrderInfo.ExpressCompanyName = order.ExpressCompanyName; purchaseOrderInfo.ExpressCompanyAbb = order.ExpressCompanyAbb; purchaseOrderInfo.Tax = order.Tax; purchaseOrderInfo.InvoiceTitle = order.InvoiceTitle; foreach (LineItemInfo current3 in order.LineItems.Values) { PurchaseOrderItemInfo purchaseOrderItemInfo2 = new PurchaseOrderItemInfo(); purchaseOrderItemInfo2.PurchaseOrderId = purchaseOrderInfo.PurchaseOrderId; purchaseOrderItemInfo2.SkuId = current3.SkuId; purchaseOrderItemInfo2.ProductId = current3.ProductId; purchaseOrderItemInfo2.SKU = current3.SKU; purchaseOrderItemInfo2.Quantity = current3.ShipmentQuantity; foreach (KeyValuePair <string, PurchaseOrderItemInfo> current4 in dictionary) { if (current4.Key == current3.SkuId) { purchaseOrderItemInfo2.ItemCostPrice = current4.Value.ItemCostPrice; purchaseOrderItemInfo2.ItemHomeSiteDescription = current4.Value.ItemHomeSiteDescription; } } purchaseOrderItemInfo2.ItemPurchasePrice = current3.ItemCostPrice; purchaseOrderItemInfo2.ItemListPrice = current3.ItemListPrice; purchaseOrderItemInfo2.ItemDescription = current3.ItemDescription; purchaseOrderItemInfo2.SKUContent = current3.SKUContent; purchaseOrderItemInfo2.ThumbnailsUrl = current3.ThumbnailsUrl; purchaseOrderItemInfo2.ItemWeight = current3.ItemWeight; if (string.IsNullOrEmpty(purchaseOrderItemInfo2.ItemHomeSiteDescription)) { purchaseOrderItemInfo2.ItemHomeSiteDescription = purchaseOrderItemInfo2.ItemDescription; } purchaseOrderInfo.PurchaseOrderItems.Add(purchaseOrderItemInfo2); } foreach (OrderGiftInfo current5 in order.Gifts) { PurchaseOrderGiftInfo purchaseOrderGiftInfo = new PurchaseOrderGiftInfo(); purchaseOrderGiftInfo.PurchaseOrderId = purchaseOrderInfo.PurchaseOrderId; foreach (KeyValuePair <int, decimal> current6 in dictionary2) { if (current6.Key == current5.GiftId) { purchaseOrderGiftInfo.CostPrice = current6.Value; } } purchaseOrderGiftInfo.PurchasePrice = current5.CostPrice; purchaseOrderGiftInfo.GiftId = current5.GiftId; purchaseOrderGiftInfo.GiftName = current5.GiftName; purchaseOrderGiftInfo.Quantity = current5.Quantity; purchaseOrderGiftInfo.ThumbnailsUrl = current5.ThumbnailsUrl; purchaseOrderInfo.PurchaseOrderGifts.Add(purchaseOrderGiftInfo); } result = purchaseOrderInfo; } } return(result); }
public System.Data.DataTable GetSkuMemberPrices(string productIds) { StringBuilder stringBuilder = new StringBuilder(); stringBuilder.AppendFormat("SELECT SkuId, ProductName, SKU, CostPrice, MarketPrice, SalePrice FROM Hishop_Products p JOIN Hishop_SKUs s ON p.ProductId = s.ProductId WHERE p.ProductId IN ({0})", DataHelper.CleanSearchString(productIds)); stringBuilder.Append(" SELECT SkuId, AttributeName, ValueStr FROM Hishop_SKUItems si JOIN Hishop_Attributes a ON si.AttributeId = a.AttributeId JOIN Hishop_AttributeValues av ON si.ValueId = av.ValueId"); stringBuilder.AppendFormat(" WHERE si.SkuId IN(SELECT SkuId FROM Hishop_SKUs WHERE ProductId IN ({0}))", DataHelper.CleanSearchString(productIds)); stringBuilder.AppendLine(" SELECT CAST(GradeId AS NVARCHAR) + '_' + [Name] AS MemberGradeName,Discount FROM aspnet_MemberGrades"); stringBuilder.AppendLine(" SELECT SkuId, (SELECT CAST(GradeId AS NVARCHAR) + '_' + [Name] FROM aspnet_MemberGrades WHERE GradeId = sm.GradeId) AS MemberGradeName,MemberSalePrice"); stringBuilder.AppendFormat(" FROM Hishop_SKUMemberPrice sm WHERE SkuId IN (SELECT SkuId FROM Hishop_SKUs WHERE ProductId IN ({0}))", DataHelper.CleanSearchString(productIds)); System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand(stringBuilder.ToString()); System.Data.DataTable dataTable = null; System.Data.DataTable dataTable2 = null; System.Data.DataTable dataTable3 = null; System.Data.DataTable dataTable4 = null; using (System.Data.IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand)) { dataTable = DataHelper.ConverDataReaderToDataTable(dataReader); if (dataTable != null && dataTable.Rows.Count > 0) { dataTable.Columns.Add("SKUContent"); dataReader.NextResult(); dataTable2 = DataHelper.ConverDataReaderToDataTable(dataReader); dataReader.NextResult(); dataTable4 = DataHelper.ConverDataReaderToDataTable(dataReader); if (dataTable4 != null && dataTable4.Rows.Count > 0) { foreach (System.Data.DataRow dataRow in dataTable4.Rows) { dataTable.Columns.Add((string)dataRow["MemberGradeName"]); } } dataReader.NextResult(); dataTable3 = DataHelper.ConverDataReaderToDataTable(dataReader); } } if (dataTable2 != null && dataTable2.Rows.Count > 0) { foreach (System.Data.DataRow dataRow2 in dataTable.Rows) { string text = string.Empty; foreach (System.Data.DataRow dataRow3 in dataTable2.Rows) { if ((string)dataRow2["SkuId"] == (string)dataRow3["SkuId"]) { object obj = text; text = string.Concat(new object[] { obj, dataRow3["AttributeName"], ":", dataRow3["ValueStr"], "; " }); } } dataRow2["SKUContent"] = text; } } if (dataTable3 != null && dataTable3.Rows.Count > 0) { foreach (System.Data.DataRow dataRow2 in dataTable.Rows) { foreach (System.Data.DataRow dataRow4 in dataTable3.Rows) { if ((string)dataRow2["SkuId"] == (string)dataRow4["SkuId"]) { dataRow2[(string)dataRow4["MemberGradeName"]] = dataRow4["MemberSalePrice"]; } } } } if (dataTable4 != null && dataTable4.Rows.Count > 0) { foreach (System.Data.DataRow dataRow2 in dataTable.Rows) { decimal d = decimal.Parse(dataRow2["SalePrice"].ToString()); foreach (System.Data.DataRow dataRow5 in dataTable4.Rows) { decimal d2 = decimal.Parse(dataRow5["Discount"].ToString()); string arg = (d * (d2 / 100m)).ToString("F2"); dataRow2[(string)dataRow5["MemberGradeName"]] = dataRow2[(string)dataRow5["MemberGradeName"]] + "|" + arg; } } } return(dataTable); }
private ShoppingCartItemInfo GetCartItemInfo(string skuId, int quantity) { ShoppingCartItemInfo shoppingCartItemInfo = null; System.Data.Common.DbCommand storedProcCommand = this.database.GetStoredProcCommand("ss_ShoppingCart_GetItemInfo"); this.database.AddInParameter(storedProcCommand, "Quantity", System.Data.DbType.Int32, quantity); this.database.AddInParameter(storedProcCommand, "UserId", System.Data.DbType.Int32, 0); this.database.AddInParameter(storedProcCommand, "SkuId", System.Data.DbType.String, skuId); this.database.AddInParameter(storedProcCommand, "GradeId", System.Data.DbType.Int32, 0); using (System.Data.IDataReader dataReader = this.database.ExecuteReader(storedProcCommand)) { if (dataReader.Read()) { shoppingCartItemInfo = new ShoppingCartItemInfo(); shoppingCartItemInfo.SkuId = skuId; shoppingCartItemInfo.ProductId = (int)dataReader["ProductId"]; shoppingCartItemInfo.Name = dataReader["ProductName"].ToString(); if (DBNull.Value != dataReader["Weight"]) { shoppingCartItemInfo.Weight = (int)dataReader["Weight"]; } shoppingCartItemInfo.MemberPrice = (shoppingCartItemInfo.AdjustedPrice = (decimal)dataReader["SalePrice"]); if (DBNull.Value != dataReader["ThumbnailUrl40"]) { shoppingCartItemInfo.ThumbnailUrl40 = dataReader["ThumbnailUrl40"].ToString(); } if (DBNull.Value != dataReader["ThumbnailUrl60"]) { shoppingCartItemInfo.ThumbnailUrl60 = dataReader["ThumbnailUrl60"].ToString(); } if (DBNull.Value != dataReader["ThumbnailUrl100"]) { shoppingCartItemInfo.ThumbnailUrl100 = dataReader["ThumbnailUrl100"].ToString(); } if (dataReader["SKU"] != DBNull.Value) { shoppingCartItemInfo.SKU = (string)dataReader["SKU"]; } ShoppingCartItemInfo arg_1CB_0 = shoppingCartItemInfo; shoppingCartItemInfo.ShippQuantity = quantity; arg_1CB_0.Quantity = quantity; string text = string.Empty; if (dataReader.NextResult()) { while (dataReader.Read()) { if (dataReader["AttributeName"] != DBNull.Value && !string.IsNullOrEmpty((string)dataReader["AttributeName"]) && dataReader["ValueStr"] != DBNull.Value && !string.IsNullOrEmpty((string)dataReader["ValueStr"])) { object obj = text; text = string.Concat(new object[] { obj, dataReader["AttributeName"], ":", dataReader["ValueStr"], "; " }); } } } shoppingCartItemInfo.SkuContent = text; } } return(shoppingCartItemInfo); }
public ShoppingCartItemInfo GetCartItemInfo(DateTime startSendDate, int quantityPerDay, int sendDays, MemberInfo member, string skuId, int quantity, int type = 0, int bargainDetialId = 0, int limitedTimeDiscountId = 0) { ShoppingCartItemInfo shoppingCartItemInfo = null; System.Data.Common.DbCommand storedProcCommand = this.database.GetStoredProcCommand("ss_ShoppingCart_GetItemInfo"); this.database.AddInParameter(storedProcCommand, "Quantity", System.Data.DbType.Int32, quantity); this.database.AddInParameter(storedProcCommand, "UserId", System.Data.DbType.Int32, (member != null) ? member.UserId : 0); this.database.AddInParameter(storedProcCommand, "SkuId", System.Data.DbType.String, skuId); this.database.AddInParameter(storedProcCommand, "GradeId", System.Data.DbType.Int32, (member != null) ? member.GradeId : 0); this.database.AddInParameter(storedProcCommand, "Type", System.Data.DbType.Int32, type); using (System.Data.IDataReader dataReader = this.database.ExecuteReader(storedProcCommand)) { if (dataReader.Read()) { shoppingCartItemInfo = new ShoppingCartItemInfo(); shoppingCartItemInfo.SkuId = skuId; ShoppingCartItemInfo arg_E7_0 = shoppingCartItemInfo; shoppingCartItemInfo.ShippQuantity = quantity; //牛奶配送所需属性 shoppingCartItemInfo.SendStartDate = startSendDate; shoppingCartItemInfo.SendEndDate = startSendDate.AddDays(sendDays - 1); shoppingCartItemInfo.QuantityPerDay = quantityPerDay; shoppingCartItemInfo.SendDays = sendDays; arg_E7_0.Quantity = quantity; shoppingCartItemInfo.MainCategoryPath = dataReader["MainCategoryPath"].ToString(); shoppingCartItemInfo.ProductId = (int)dataReader["ProductId"]; if (DBNull.Value != dataReader["CubicMeter"]) { shoppingCartItemInfo.CubicMeter = (decimal)dataReader["CubicMeter"]; } if (DBNull.Value != dataReader["FreightWeight"]) { shoppingCartItemInfo.FreightWeight = (decimal)dataReader["FreightWeight"]; } if (dataReader["SKU"] != DBNull.Value) { shoppingCartItemInfo.SKU = (string)dataReader["SKU"]; } shoppingCartItemInfo.Name = (string)dataReader["ProductName"]; if (DBNull.Value != dataReader["Weight"]) { shoppingCartItemInfo.Weight = (int)dataReader["Weight"]; } if (DBNull.Value != dataReader["FreightTemplateId"]) { shoppingCartItemInfo.FreightTemplateId = (int)dataReader["FreightTemplateId"]; } else { shoppingCartItemInfo.FreightTemplateId = 0; } if (DBNull.Value != dataReader["ThirdCommission"]) { shoppingCartItemInfo.ThirdCommission = (decimal)dataReader["ThirdCommission"]; } else { shoppingCartItemInfo.ThirdCommission = 0m; } if (DBNull.Value != dataReader["SecondCommission"]) { shoppingCartItemInfo.SecondCommission = (decimal)dataReader["SecondCommission"]; } else { shoppingCartItemInfo.SecondCommission = 0m; } if (DBNull.Value != dataReader["FirstCommission"]) { shoppingCartItemInfo.FirstCommission = (decimal)dataReader["FirstCommission"]; } else { shoppingCartItemInfo.FirstCommission = 0m; } if (DBNull.Value != dataReader["IsSetCommission"]) { shoppingCartItemInfo.IsSetCommission = (bool)dataReader["IsSetCommission"]; } else { shoppingCartItemInfo.IsSetCommission = false; } BargainDetialInfo bargainDetialInfo = null; if (bargainDetialId > 0) { bargainDetialInfo = new BargainDao().GetBargainDetialInfo(bargainDetialId); } if (bargainDetialId > 0 && bargainDetialInfo != null) { shoppingCartItemInfo.MemberPrice = (shoppingCartItemInfo.AdjustedPrice = bargainDetialInfo.Price); } else { shoppingCartItemInfo.MemberPrice = (shoppingCartItemInfo.AdjustedPrice = decimal.Round((decimal)dataReader["SalePrice"], 2)); } if (limitedTimeDiscountId > 0) { LimitedTimeDiscountProductInfo limitedTimeDiscountProductByLimitIdAndProductIdAndUserId = new LimitedTimeDiscountDao().GetLimitedTimeDiscountProductByLimitIdAndProductIdAndUserId(limitedTimeDiscountId, shoppingCartItemInfo.ProductId, (member != null) ? member.UserId : 0); if (limitedTimeDiscountProductByLimitIdAndProductIdAndUserId != null && limitedTimeDiscountProductByLimitIdAndProductIdAndUserId.BeginTime <= DateTime.Now && DateTime.Now < limitedTimeDiscountProductByLimitIdAndProductIdAndUserId.EndTime) { shoppingCartItemInfo.MemberPrice = (shoppingCartItemInfo.AdjustedPrice = decimal.Round(limitedTimeDiscountProductByLimitIdAndProductIdAndUserId.FinalPrice, 2)); shoppingCartItemInfo.LimitedTimeDiscountId = limitedTimeDiscountId; } else { shoppingCartItemInfo.LimitedTimeDiscountId = 0; } } else { shoppingCartItemInfo.LimitedTimeDiscountId = 0; } if (DBNull.Value != dataReader["ThumbnailUrl40"]) { shoppingCartItemInfo.ThumbnailUrl40 = dataReader["ThumbnailUrl40"].ToString(); } if (DBNull.Value != dataReader["ThumbnailUrl60"]) { shoppingCartItemInfo.ThumbnailUrl60 = dataReader["ThumbnailUrl60"].ToString(); } if (DBNull.Value != dataReader["ThumbnailUrl100"]) { shoppingCartItemInfo.ThumbnailUrl100 = dataReader["ThumbnailUrl100"].ToString(); } if (DBNull.Value != dataReader["IsfreeShipping"]) { shoppingCartItemInfo.IsfreeShipping = Convert.ToBoolean(dataReader["IsfreeShipping"]); } string text = string.Empty; if (dataReader.NextResult()) { while (dataReader.Read()) { if (dataReader["AttributeName"] != DBNull.Value && !string.IsNullOrEmpty((string)dataReader["AttributeName"]) && dataReader["ValueStr"] != DBNull.Value && !string.IsNullOrEmpty((string)dataReader["ValueStr"])) { object obj = text; text = string.Concat(new object[] { obj, dataReader["AttributeName"], ":", dataReader["ValueStr"], "; " }); } } } shoppingCartItemInfo.SkuContent = text; if (dataReader.NextResult()) { while (dataReader.Read()) { shoppingCartItemInfo.Type = 1; if (DBNull.Value != dataReader["ProductNumber"]) { shoppingCartItemInfo.ProductNumber = Convert.ToInt32(dataReader["ProductNumber"]); } if (DBNull.Value != dataReader["PointNumber"]) { shoppingCartItemInfo.PointNumber = Convert.ToInt32(dataReader["PointNumber"]); } if (DBNull.Value != dataReader["status"]) { shoppingCartItemInfo.Status = Convert.ToInt32(dataReader["status"]); } if (DBNull.Value != dataReader["exChangeId"]) { shoppingCartItemInfo.ExchangeId = Convert.ToInt32(dataReader["exChangeId"]); } } } else { shoppingCartItemInfo.Type = 0; } } } return(shoppingCartItemInfo); }