public ShoppingCartInfo GetShoppingCart(MemberInfo member)
{
ShoppingCartInfo shoppingCartInfo = new ShoppingCartInfo();
System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_ShoppingCarts WHERE UserId = @UserId");
this.database.AddInParameter(sqlStringCommand, "UserId", System.Data.DbType.Int32, member.UserId);
using (System.Data.IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
while (dataReader.Read())
{
ShoppingCartItemInfo cartItemInfo = this.GetCartItemInfo(member, (string)dataReader["SkuId"], (int)dataReader["Quantity"], 0, 0, 0);
if (cartItemInfo != null)
{
shoppingCartInfo.LineItems.Add(cartItemInfo);
}
}
}
return(shoppingCartInfo);
}
public bool SaveSalesVoucher(TransSalesModel objSales)
{
string Query = string.Empty;
bool isSaved = true;
try
{
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@VoucherNumber", objSales.VoucherNumber));
paramCollection.Add(new DBParameter("@Series", objSales.Series));
paramCollection.Add(new DBParameter("@SaleDate", objSales.SaleDate, System.Data.DbType.DateTime));
//paramCollection.Add(new DBParameter("@BillNo", objSales.BillNo));
//paramCollection.Add(new DBParameter("@DueDate", objSales.DueDate));
paramCollection.Add(new DBParameter("@SalesType", objSales.SalesType));
paramCollection.Add(new DBParameter("@Party", objSales.Party));
paramCollection.Add(new DBParameter("@MatCentre", objSales.MatCentre));
paramCollection.Add(new DBParameter("@Narration", objSales.Narration));
paramCollection.Add(new DBParameter("@ItemTotalAmount", objSales.TotalAmount));
paramCollection.Add(new DBParameter("@ItemTotalQty", objSales.TotalQty));
paramCollection.Add(new DBParameter("@BSTotalAmount", objSales.BSTotalAmount));
paramCollection.Add(new DBParameter("@CreatedBy", "Admin"));
paramCollection.Add(new DBParameter("@CreatedDate", DateTime.Now, DbType.DateTime));
paramCollection.Add(new DBParameter("@ModifiedBy", ""));
paramCollection.Add(new DBParameter("@ModifiedDate", DateTime.Now, DbType.DateTime));
System.Data.IDataReader dr =
_dbHelper.ExecuteDataReader("spInsertSalesVoucher", _dbHelper.GetConnObject(), paramCollection, System.Data.CommandType.StoredProcedure);
int id = 0;
dr.Read();
id = Convert.ToInt32(dr[0]);
SaveSalesVoucherItems(objSales.SalesItem_Voucher, id);
SaveSalesBillSundryVoucher(objSales.SalesBillSundry_Voucher, id);
}
catch (Exception ex)
{
isSaved = false;
// throw ex;
}
return(isSaved);
}
public IList <NewsMsgInfo> GetNewsReplyInfo(int replyid)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append("select ReplyId,MsgID,Title,ImageUrl,Url,Description,Content from vshop_Message ");
stringBuilder.Append(" where ReplyId=@ReplyId ");
System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand(stringBuilder.ToString());
this.database.AddInParameter(sqlStringCommand, "ReplyId", System.Data.DbType.Int32, replyid);
List <NewsMsgInfo> list = new List <NewsMsgInfo>();
using (System.Data.IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
while (dataReader.Read())
{
list.Add(this.ReaderBindNewsRelpy(dataReader));
}
}
return(list);
}
public System.Data.DataTable GetPrizes(int gameId)
{
System.Data.DataTable result;
try
{
System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand("select * from Hishop_Game_Prize where [GameId]= @GameId order by sort ");
this.database.AddInParameter(sqlStringCommand, "GameId", System.Data.DbType.Int32, gameId);
using (System.Data.IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
System.Data.DataTable dataTable = DataHelper.ConverDataReaderToDataTable(dataReader);
result = dataTable;
}
}
catch (Exception var_3_59)
{
result = null;
}
return(result);
}
//---------------------------------------------------------------------------------------------------------------------
public void LoadRegistrationInfo()
{
string sql = String.Format("SELECT reg_origin, reg_date FROM usrreg WHERE id_usr={0};", this.Id);
System.Data.IDbConnection dbConnection = context.GetDbConnection();
System.Data.IDataReader reader = context.GetQueryResult(sql, dbConnection);
if (reader.Read())
{
if (reader.GetValue(0) != DBNull.Value)
{
RegistrationOrigin = reader.GetString(0);
}
if (reader.GetValue(1) != DBNull.Value)
{
RegistrationDate = reader.GetDateTime(1);
}
}
context.CloseQueryResult(reader, dbConnection);
}
/// <summary>
/// Accesso ai dati e caricamento ragioni trasmissione
/// </summary>
/// <param name="commandText"></param>
/// <returns></returns>
private static OrgRagioneTrasmissione[] FetchRagioniTrasmissione(string commandText)
{
ArrayList retValue = new ArrayList();
logger.Debug(commandText);
using (DocsPaDB.DBProvider dbProvider = new DocsPaDB.DBProvider())
{
using (System.Data.IDataReader reader = dbProvider.ExecuteReader(commandText))
{
while (reader.Read())
{
retValue.Add(CreateRagioneTrasmissione(reader));
}
}
}
return((OrgRagioneTrasmissione[])retValue.ToArray(typeof(OrgRagioneTrasmissione)));
}
/// <summary>
/// Print a list of the accounts in the database
/// </summary>
public void ShowUsers()
{
Console.WriteLine("== users ==");
try {
DbCommand command = dbConnection.CreateCommand();
command.CommandText = "SELECT uid FROM oc_users";
DbReader reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader ["uid"]);
}
reader.Close();
} catch (Exception) {
//
}
}
public ActivityInfo GetAct(string name)
{
ActivityInfo activityInfo = null;
System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_Activities WHERE ActivitiesName= @Name");
this.database.AddInParameter(sqlStringCommand, "Name", System.Data.DbType.Int32, name);
using (System.Data.IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
activityInfo = ReaderConvert.ReaderToModel <ActivityInfo>(dataReader);
}
sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_Activities_Detail WHERE ActivitiesId = @ID");
this.database.AddInParameter(sqlStringCommand, "ID", System.Data.DbType.Int32, activityInfo.ActivitiesId);
using (System.Data.IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
IList <ActivityDetailInfo> details = ReaderConvert.ReaderToList <ActivityDetailInfo>(dataReader);
activityInfo.Details = details;
}
return(activityInfo);
}
public BrandCategoryInfo GetBrandCategory(int brandId)
{
BrandCategoryInfo brandCategoryInfo = new BrandCategoryInfo();
System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_BrandCategories WHERE BrandId = @BrandId;SELECT * FROM Hishop_ProductTypeBrands WHERE BrandId = @BrandId");
this.database.AddInParameter(sqlStringCommand, "BrandId", System.Data.DbType.Int32, brandId);
using (System.Data.IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
brandCategoryInfo = ReaderConvert.ReaderToModel <BrandCategoryInfo>(dataReader);
IList <int> list = new List <int>();
dataReader.NextResult();
while (dataReader.Read())
{
list.Add((int)dataReader["ProductTypeId"]);
}
brandCategoryInfo.ProductTypes = list;
}
return(brandCategoryInfo);
}
/// <summary>
/// executes sql against the datasource and will return a single result object
/// </summary>
/// <param name="pSQL"></param>
/// <returns></returns>
public object GetScalar(string pSQL)
{
object result = null;
try
{
System.Data.IDataReader ReaderResult = (System.Data.IDataReader) this.ExecuteReader(this.CreateQuery(pSQL));
while (ReaderResult.Read())
{
result = ReaderResult[0];
break;
}
}
catch (Exception e)
{
Logger.Log("Error MongoDBDatabase.IDataSource.GetDataTableReader:\n" + e.ToString());
}
return(result);
}
protected override SendMailModel LoadFromReader(System.Data.IDataReader iDataReader)
{
SendMailModel ret = new SendMailModel()
{
ID = ToNullableLong(iDataReader["ID"]),
CustomerID = ToNullableLong(iDataReader["CustomerID"]),
CustomerEmailAddress = ToNullableString(iDataReader["CustomerEmailAddress"]),
DepositNumber = ToNullableString(iDataReader["DepositNumber"]),
EmailType = ToNullableString(iDataReader["EmailType"]),
EmailText = ToNullableString(iDataReader["EmailText"]),
Subject = ToNullableString(iDataReader["Subject"]),
Status = ToNullableBool(iDataReader["mailstatus"]),
Error = ToNullableString(iDataReader["Error"]),
SendDate = ToNullableString(iDataReader["SendDate"]),
SendTime = ToNullableString(iDataReader["SendTime"])
};
return(ret);
}
private void ReaderToProperty(System.Data.IDataReader reader, object obj, ReadProperty rp, PropertyMapper pm, PropertyHandler handler)
{
try
{
object dbvalue = reader[rp.Index];
if (dbvalue != DBNull.Value)
{
if (pm.Cast != null)
{
dbvalue = pm.Cast.ToProperty(dbvalue, pm.Handler.Property.PropertyType, obj);
}
handler.Set(obj, Convert.ChangeType(dbvalue, pm.Handler.Property.PropertyType));
}
}
catch (Exception e_)
{
throw new PeanutException(string.Format(DataMsg.READER_TO_PROPERTY_ERROR, pm.ColumnName, pm.Handler.Property.Name), e_);
}
}
public void ReaderToObject(System.Data.IDataReader reader, object obj)
{
if (!mLoadColumnIndex)
{
SetColumnIndex(reader);
}
for (int i = 0; i < Properties.Count; i++)
{
ReadProperty rp = Properties[i];
if (!Proxy)
{
ReaderToProperty(reader, obj, rp, rp.Mapper, rp.Mapper.Handler);
}
else
{
ReaderToProperty(reader, obj, rp, rp.Mapper, ProxyProperties[i]);
}
}
}
public override T GetValue(System.Data.IDataReader dataReader)
{
var entity = Activator.CreateInstance <T>();
foreach (var mapping in this.mappings)
{
var value = dataReader[mapping.Key];
if (value == null || value is DBNull)
{
mapping.Value.SetValue(entity, null, null);
}
else
{
var underlyingType = Nullable.GetUnderlyingType(mapping.Value.PropertyType);
mapping.Value.SetValue(entity, Convert.ChangeType(value, underlyingType ?? mapping.Value.PropertyType), null);
}
}
return(entity);
}
public override bool ChangeMemberGrade(int userId, int gradId, int points)
{
System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT ISNULL(Points, 0) AS Point, GradeId FROM aspnet_MemberGrades Order by Point Desc ");
bool result;
using (System.Data.IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
while (dataReader.Read() && (int)dataReader["GradeId"] != gradId)
{
if ((int)dataReader["Point"] <= points)
{
result = this.UpdateUserRank(userId, (int)dataReader["GradeId"]);
return(result);
}
}
result = true;
}
return(result);
}
private void GetPasswordWithFormat(string tableName, string username, out bool success, out int passwordFormat, out string passwordSalt, out string passwordFromDb)
{
passwordFormat = 0;
passwordSalt = null;
passwordFromDb = null;
success = false;
System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT biz.TradePasswordFormat, biz.TradePasswordSalt, biz.TradePassword FROM " + tableName + " AS biz INNER JOIN aspnet_Users AS u ON biz.UserId = u.UserId WHERE u.LoweredUserName = LOWER(@Username)");
this.database.AddInParameter(sqlStringCommand, "Username", System.Data.DbType.String, username);
using (System.Data.IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
if (dataReader.Read())
{
passwordFormat = dataReader.GetInt32(0);
passwordSalt = dataReader.GetString(1);
passwordFromDb = dataReader.GetString(2);
success = true;
}
}
}
/// <summary>
/// Gets the page URL internal.
/// </summary>
/// <param name="PageId">The page id.</param>
/// <param name="WithUserId">if set to <c>true</c> [with user id].</param>
/// <returns></returns>
protected string GetPageUrlInternal(int PageId, bool WithUserId)
{
string path = String.Empty;
using (System.Data.IDataReader reader = Mediachase.Cms.FileTreeItem.GetItemById(PageId))
{
if (reader.Read())
{
if ((bool)reader["IsFolder"])
{
path += "/" + reader["Name"].ToString() + "/";
}
else
{
path += reader["Outline"].ToString();
}
}
reader.Close();
}
if (WithUserId)
{
//path += "?_mode=edit";
}
else
{
//trunc path
if (path.Length > 100)
{
string begin = string.Empty;
string end = string.Empty;
if (Regex.IsMatch(path, "/[\\w\\d]+\\u002Easpx"))
{
end = Regex.Match(path, "/[\\w\\d]+\\u002Easpx").Value;
}
if (Regex.IsMatch(path, "^/[\\w\\d]*"))
{
end = Regex.Match(path, "^/[\\w\\d]*").Value;
}
path = begin + "..." + end;
}
}
return(path.Trim());
}
public static EMS.Entities.MyProfileMDL GetProfileDetails(int UserId, int BusinessId)
{
EMS.Entities.MyProfileMDL user = new EMS.Entities.MyProfileMDL();
using (DBSqlCommand cmd = new DBSqlCommand())
{
try
{
cmd.AddParameters(UserId, CommonConstants.UserId, System.Data.SqlDbType.Int);
cmd.AddParameters(BusinessId, CommonConstants.BusinessID, System.Data.SqlDbType.Int);
System.Data.IDataReader ireader = cmd.ExecuteDataReader(SqlProcedures.Get_MyAccountDetails);
while (ireader.Read())
{
var userDet = new EMS.Entities.MyProfileMDL
{
UserFirstName = ireader.GetString(CommonColumns.FirstName),
UserLastName = ireader.GetString(CommonColumns.LastName),
Email = ireader.GetString(CommonColumns.UserEmail),
Fax = ireader.GetString(CommonColumns.BusinessFax),
AddressLine1 = ireader.GetString(CommonColumns.UserAddressLine1),
City = ireader.GetString(CommonColumns.City),
State = ireader.GetString(CommonColumns.State),
Country = ireader.GetString(CommonColumns.Country),
//RenewalDate = ireader.GetDateTime(CommonColumns.UserAccountExpiryDate),
Licences = ireader.GetInt16(CommonColumns.Licences),
BusinessName = ireader.GetString(CommonColumns.BusinessName),
LogMeOutId = ireader.GetInt32(CommonColumns.LogMeOutId),
EndDate = ireader.GetNullLocalDateTime(CommonColumns.UserAccountExpiryDate),
CityId = ireader.GetInt32(CommonColumns.CityId),
StateId = ireader.GetInt32(CommonColumns.StateId),
CountryId = ireader.GetInt16(CommonColumns.CountryId),
ImagePath = ireader.GetString(CommonColumns.ImagePath)
};
user = userDet;
}
return(user);
}
catch (Exception ex)
{
return(null);
}
}
}
public static Zipcode GetCSZ(int z)
{
Database db = DatabaseFactory.CreateDatabase();
DbCommand cw = db.GetStoredProcCommand("sp_NCIPL_GetCSZ");
db.AddInParameter(cw, "zip", DbType.String, z.ToString());
using (System.Data.IDataReader dr = db.ExecuteReader(cw))
{
Zipcode k = new Zipcode();
if (dr.Read())
{
//k.zip5=z;
k.City = dr["City"].ToString();
k.State = dr["State"].ToString();
k.Zip4 = dr["zip4"].ToString();
}
return(k); //May or may not have real values at this point...thats ok.
}
}
public void SetBinaryData(int id, string fieldName, byte[] data)
{
string query = "select " + fieldName +
" from PUBLISH_HISTORY " +
" where ID = " + id + " FOR UPDATE";
using (DBCommandWrapper wrapper = GetSqlStringCommandWrapper(query))
{
using (System.Data.IDataReader reader = this.ExecuteReader(wrapper))
{
if (reader.Read())
{
System.Data.OracleClient.OracleLob clob = ((OracleDataReaderWrapper)reader).InnerReader.GetOracleLob(0);
clob.Erase();
clob.Write(data, 0, data.Length);
}
}
}
}
public bool SavePaymentVoucher(PaymentVoucherModel objpaymod)
{
string Query = string.Empty;
//int payid = 0;
bool isSaved = true;
try
{
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@VoucherNumber", objpaymod.Voucher_Number));
paramCollection.Add(new DBParameter("@Series", objpaymod.Voucher_Series));
paramCollection.Add(new DBParameter("@PayDate", objpaymod.Pay_Date, System.Data.DbType.DateTime));
paramCollection.Add(new DBParameter("@Type", objpaymod.Type));
paramCollection.Add(new DBParameter("@PaymentModeId", objpaymod.PaymentModeId));
paramCollection.Add(new DBParameter("@PDCDate", objpaymod.PDCDate, System.Data.DbType.DateTime));
paramCollection.Add(new DBParameter("@LongNarration", objpaymod.LongNarration));
paramCollection.Add(new DBParameter("@TotalCreditAmount", objpaymod.TotalCreditAmt, DbType.Decimal));
paramCollection.Add(new DBParameter("@TotalDebitAmount", objpaymod.TotalDebitAmt, DbType.Decimal));
paramCollection.Add(new DBParameter("@CreatedBy", "Admin"));
paramCollection.Add(new DBParameter("@CreatedDate", DateTime.Now, DbType.DateTime));
paramCollection.Add(new DBParameter("@ModifiedBy", ""));
paramCollection.Add(new DBParameter("@ModifiedDate", DateTime.Now, DbType.DateTime));
System.Data.IDataReader dr =
_dbHelper.ExecuteDataReader("spInsertPaymentMaster", _dbHelper.GetConnObject(), paramCollection, System.Data.CommandType.StoredProcedure);
int id = 0;
dr.Read();
id = Convert.ToInt32(dr[0]);
SavePaymentAccounts(objpaymod.PaymentAccountModel, id);
objLPBL.LedgerPostingAddByList(objpaymod.PaymentLPDebit);
objLPBL.LedgerPostingAddByList(objpaymod.PaymentLPCredit);
}
catch (Exception ex)
{
isSaved = false;
throw ex;
}
return(isSaved);
}
/// <summary>
/// 通过dbreader填充数据到列表
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="reader"></param>
/// <returns></returns>
public static IList <T> FillList <T>(System.Data.IDataReader reader)
{
try
{
//先获取列名信息
List <string> fieldsList = new List <string>();
for (int i = 0; i < reader.FieldCount; i++)
{
fieldsList.Add(reader.GetName(i));
}
IList <T> lst = new List <T>();
while (reader.Read())
{
T RowInstance = Activator.CreateInstance <T>();
foreach (System.Reflection.PropertyInfo Property in typeof(T).GetProperties())
{
//try
//{
if (!fieldsList.Contains(Property.Name))
{
continue;
}
if (reader[Property.Name] != DBNull.Value)
{
Property.SetValue(RowInstance, reader[Property.Name], null);
}
//}
//catch
//{
// continue;
//}
}
lst.Add(RowInstance);
}
return(lst);
}
finally
{
reader.Close();
}
}
//List
public List <SalesManModel> GetAllSalesMan()
{
List <SalesManModel> lstSaleMan = new List <SalesManModel>();
SalesManModel objModel;
string Query = "SELECT * FROM SalesManMaster";
System.Data.IDataReader dr = _dbHelper.ExecuteDataReader(Query, _dbHelper.GetConnObject());
while (dr.Read())
{
objModel = new SalesManModel();
objModel.SalesMan_Id = Convert.ToInt32(dr["SalesMan_Id"]);
objModel.SM_Name = dr["SM_Name"].ToString();
objModel.SM_Alias = dr["SM_Alias"].ToString();
objModel.SM_PrintName = dr["SM_PrintName"].ToString();
objModel.EnableDefCommision = Convert.ToBoolean(dr["EnableDefCommision"]);
objModel.Commision_Mode = dr["Commision_Mode"].ToString();
objModel.DefCommision = Convert.ToDecimal(dr["DefCommision"]);
objModel.FreezeCommision = Convert.ToBoolean(dr["FreezeCommision"]);
objModel.Sales_DebitMode = dr["Sales_DebitMode"].ToString();
objModel.Sales_ACCredited = dr["Sales_ACCredited"].ToString();
objModel.Sales_AccDebited = dr["Sales_AccDebited"].ToString();
objModel.Purchase_DebitMode = dr["Purchase_DebitMode"].ToString();
objModel.Purchase_AccCredited = dr["Purchase_DebitMode"].ToString();
objModel.Purchase_AccDebited = dr["Purchase_AccDebited"].ToString();
objModel.Address = dr["Address"].ToString();
objModel.City = dr["City"].ToString();
objModel.State = dr["State"].ToString();
objModel.Country = dr["Country"].ToString();
objModel.State = dr["State"].ToString();
objModel.Mobile = dr["Mobile"].ToString();
lstSaleMan.Add(objModel);
}
return(lstSaleMan);
}
//Save Sales Voucher Items Details
public bool SaveSalesVoucherItems(List <Item_VoucherModel> lstSales, int ParentId)
{
string Query = string.Empty;
bool isSaved = true;
foreach (Item_VoucherModel item in lstSales)
{
item.ParentId = ParentId;
try
{
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@Trans_Sales_Id", item.ParentId));
paramCollection.Add(new DBParameter("@ItemId", item.ITM_Id));
paramCollection.Add(new DBParameter("@LedgerId", item.LedgerId));
paramCollection.Add(new DBParameter("@Qty", item.Qty, DbType.Decimal));
paramCollection.Add(new DBParameter("@Unit", item.Unit));
paramCollection.Add(new DBParameter("@Per", item.Per));
paramCollection.Add(new DBParameter("@Price", item.Price, DbType.Decimal));
paramCollection.Add(new DBParameter("@Batch", item.Batch));
paramCollection.Add(new DBParameter("@Free", item.Free, DbType.Decimal));
paramCollection.Add(new DBParameter("@BasicAmt", item.BasicAmt, DbType.Decimal));
paramCollection.Add(new DBParameter("@DiscountPercentage", item.DiscountPercentage, DbType.Decimal));
paramCollection.Add(new DBParameter("@DiscountAmount", item.DiscountAmount, DbType.Decimal));
paramCollection.Add(new DBParameter("@TaxAmount", item.TaxAmount, DbType.Decimal));
paramCollection.Add(new DBParameter("@Amount", item.Amount, DbType.Decimal));
paramCollection.Add(new DBParameter("@CreatedBy", "Admin"));
paramCollection.Add(new DBParameter("@CreatedDate", DateTime.Now, System.Data.DbType.DateTime));
paramCollection.Add(new DBParameter("@ModifiedBy", ""));
paramCollection.Add(new DBParameter("@ModifiedDate", DateTime.Now, System.Data.DbType.DateTime));
System.Data.IDataReader dr =
_dbHelper.ExecuteDataReader("spInsertSalesVoucherItemDetails", _dbHelper.GetConnObject(), paramCollection, System.Data.CommandType.StoredProcedure);
}
catch (Exception ex)
{
isSaved = false;
throw ex;
}
}
return(isSaved);
}
public static DbQueryResult PagingByRownumber(int pageIndex, int pageSize, string sortBy, SortAction sortOrder, bool isCount, string table, string string_0, string filter, string selectFields, int partitionSize)
{
DbQueryResult result;
if (string.IsNullOrEmpty(table))
{
result = null;
}
else
{
if (string.IsNullOrEmpty(sortBy) && string.IsNullOrEmpty(string_0))
{
result = null;
}
else
{
if (string.IsNullOrEmpty(selectFields))
{
selectFields = "*";
}
string text = DataHelper.BuildRownumberQuery(sortBy, sortOrder, isCount, table, string_0, filter, selectFields, partitionSize);
int num = (pageIndex - 1) * pageSize + 1;
int num2 = num + pageSize - 1;
DbQueryResult dbQueryResult = new DbQueryResult();
Database database = DatabaseFactory.CreateDatabase();
System.Data.Common.DbCommand sqlStringCommand = database.GetSqlStringCommand(text);
database.AddInParameter(sqlStringCommand, "StartNumber", System.Data.DbType.Int32, num);
database.AddInParameter(sqlStringCommand, "EndNumber", System.Data.DbType.Int32, num2);
using (System.Data.IDataReader dataReader = database.ExecuteReader(sqlStringCommand))
{
dbQueryResult.Data = DataHelper.ConverDataReaderToDataTable(dataReader);
if (isCount && partitionSize == 0 && dataReader.NextResult())
{
dataReader.Read();
dbQueryResult.TotalRecords = dataReader.GetInt32(0);
}
}
result = dbQueryResult;
}
}
return(result);
}
public bool GetQRCodeScanInfo(string AppID, bool IsClearAfterRead, out string SCannerUserOpenID, out string SCannerUserNickName, out string UserHead)
{
bool flag = false;
SCannerUserOpenID = "";
SCannerUserNickName = "";
UserHead = "";
string empty = string.Empty;
System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand(" select a.SCannerUserOpenID, b.UserName as NickName, b.UserHead from vshop_ScanOpenID a left join aspnet_Members b on a.SCannerUserOpenID= b.OpenId where 1=1 or a.AppID= @AppID order by AutoID desc ");
this.database.AddInParameter(sqlStringCommand, "AppID", System.Data.DbType.String, AppID);
using (System.Data.IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
if (dataReader.Read())
{
flag = true;
object obj = dataReader["SCannerUserOpenID"];
if (obj != null && obj != DBNull.Value)
{
SCannerUserOpenID = (string)obj;
flag = true;
}
obj = dataReader["NickName"];
if (obj != null && obj != DBNull.Value)
{
SCannerUserNickName = (string)obj;
}
obj = dataReader["UserHead"];
if (obj != null && obj != DBNull.Value)
{
UserHead = (string)obj;
}
}
}
if (flag && IsClearAfterRead)
{
System.Data.Common.DbCommand sqlStringCommand2 = this.database.GetSqlStringCommand(" delete from vshop_ScanOpenID where 1=1 or AppID= @AppID ");
this.database.AddInParameter(sqlStringCommand2, "AppID", System.Data.DbType.String, AppID);
this.database.ExecuteNonQuery(sqlStringCommand2);
}
return(flag);
}
public T GetValue <T>(System.Data.IDataReader theReader, string theColumnName)
{
//
// Read the value out of the reader by string (column name); returns object
object theValue = theReader[theColumnName];
// Cast to the generic type applied to this method (i.e. int?)
Type theValueType = typeof(T);
// Check for null value from the database
if (System.DBNull.Value != theValue)
{
// We have a null, do we have a nullable type for T?
if (!IsNullableType(theValueType))
{
if (theValueType == typeof(Guid))
{
if (theValue.GetType() == typeof(byte[]))
{
var a = (T)Convert.ChangeType(theValue, typeof(T));
}
{
throw new System.Exception("Invalid Guid");
}
}
else
{
return((T)Convert.ChangeType(theValue, theValueType));
}
}
else
{
// Yes, this is a nullable type so change the value's type from object to the underlying type of T
NullableConverter theNullableConverter = new NullableConverter(theValueType);
return((T)Convert.ChangeType(theValue, theNullableConverter.UnderlyingType));
}
}
// The value was null in the database, so return the default value for T; this will vary based on what T is (i.e. int has a default of 0)
return(default(T));
}
/// <summary>
/// Persists data to and from the specified excel file
/// </summary>
/// <param name="excelFilepath">The excel file that should be read into memory</param>
public ExcelPersister(string excelFilepath)
{
if (!System.IO.File.Exists(excelFilepath))
{
throw new System.IO.FileNotFoundException(excelFilepath);
}
// keep a pointer to the excel file
_ExcelFile = excelFilepath;
var table = new System.Data.DataTable("Sheet1");
try
{
using (System.Data.OleDb.OleDbConnection connection = new System.Data.OleDb.OleDbConnection(Util.GetExcelConnectionString(excelFilepath, true)))
{
using (System.Data.IDbCommand cmd = connection.CreateCommand())
{
cmd.CommandText = "SELECT * FROM [Sheet1$]";
connection.Open();
using (System.Data.IDataReader dr = cmd.ExecuteReader())
{
table.Load(dr);
}
}
}
}
catch (Exception ex)
{
if ((ex.Message.IndexOf("Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine", StringComparison.OrdinalIgnoreCase) >= 0))
{
logger.Error("Please install the Microsoft Access Database Engine 2016 Redistributable: https://www.microsoft.com/en-us/download/details.aspx?id=54920", ex);
throw new System.Exception("Please install the Microsoft Access Database Engine 2016 Redistributable: https://www.microsoft.com/en-us/download/details.aspx?id=54920");
}
logger.Error("Problem reading excel file: " + excelFilepath, ex);
throw;
}
// assign the data table before exiting the constructor
this.Data = table;
}
private List <Dictionary <string, object> > ExecutaQuery(System.Data.Common.DbCommand cmd)
{
List <Dictionary <string, object> > Linhas = new List <Dictionary <string, object> >();
try
{
if (cmd.CommandText.Contains("CALL "))
{
cmd.CommandType = CommandType.StoredProcedure;
}
else
{
cmd.CommandType = CommandType.Text;
}
cmd.CommandTimeout = 1800;
using (System.Data.IDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Dictionary <string, object> linha = new Dictionary <string, object>();
// Colunas
for (int i = 0; i < reader.FieldCount; i++)
{
linha.Add(reader.GetName(i), reader.GetValue(i) == DBNull.Value ? null : reader.GetValue(i));
}
// Adiciona Dictionary da Linha
Linhas.Add(linha);
}
}
}
catch (Exception ex)
{
string txtComando = Util.DevolveStringCommand(cmd);
throw new Exception(string.Format("Erro ao executar comando no banco de dados.\r\nTexto: {0}\r\nDetalhes: {1}", txtComando, ex.Message), ex);
}
return(Linhas);
}
public System.Data.DataTable GetSkuStocks(string productIds)
{
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.AppendFormat("SELECT p.ProductId,ProductName, SkuId, SKU, Stock, ThumbnailUrl40 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));
System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand(stringBuilder.ToString());
System.Data.DataTable dataTable = null;
System.Data.DataTable dataTable2 = null;
using (System.Data.IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
dataTable = DataHelper.ConverDataReaderToDataTable(dataReader);
dataReader.NextResult();
dataTable2 = DataHelper.ConverDataReaderToDataTable(dataReader);
}
dataTable.Columns.Add("SKUContent");
if (dataTable != null && dataTable.Rows.Count > 0 && dataTable2 != null && dataTable2.Rows.Count > 0)
{
foreach (System.Data.DataRow dataRow in dataTable.Rows)
{
string text = string.Empty;
foreach (System.Data.DataRow dataRow2 in dataTable2.Rows)
{
if ((string)dataRow["SkuId"] == (string)dataRow2["SkuId"])
{
object obj = text;
text = string.Concat(new object[]
{
obj,
dataRow2["AttributeName"],
"：",
dataRow2["ValueStr"],
"; "
});
}
}
dataRow["SKUContent"] = text;
}
}
return(dataTable);
}
public EpiDataReader(Rule_Context Context, System.Data.IDataReader Reader)
{
this.mContext = Context;
this.mReader = Reader;
}
public void ValidateDataReader()
{
try
{
if (m_oDataReader != null && !m_oDataReader.IsClosed)
{
m_oDataReader.Close();
m_oDataReader.Dispose();
m_oDataReader = null;
}
}
catch (Exception ex)
{
throw new DataAccessException("No se ha podido cerrar al DataReader.", ex);
}
}
private void Inicializa()
{
m_oConnection = null;
m_oCommand = null;
m_oTransaction = null;
m_sConnectionString = null;
m_nroTransaction = 0;
m_nCommandTimeout = 0;
m_nRetryConnect = 3;
m_bDisposed = false;
m_bConnected = false;
m_sProviderAssembly = null;
m_sProviderConnectionClass = null;
m_sProviderCommandBuilderClass = null;
m_eProvider = PROVIDER_TYPE.PROVIDER_NONE;
m_idConexion = 0;
m_oDataReader = null;
}
/// <summary>
/// Ejecuta una QueryString o un Store Procedure
/// </summary>
/// <param name="sSQL">Nombre del StoreProcedure o QueryString</param>
/// <param name="oType">Tipo de Comando a Ejecuatr</param>
/// <returns>IDataReader</returns>
public IDataReader ExecuteReader(string sSQL, CommandType oType)
{
try
{
if (m_oLog != null)
{
m_oLog.TraceLog("Realizando ExecuteReader...", m_idConexion);
}
ValidateDataReader();
ValidateConnection();
m_oCommand.CommandText = ProcessQryString(sSQL, oType);
m_oCommand.CommandType = oType;
ProcessParametersString();
if (m_oLog != null)
{
m_oLog.TraceLog("QueryString -> " + m_oCommand.CommandText, m_idConexion);
m_oLog.TraceLog("ParamsValues -> " + ParamsValues, m_idConexion);
}
m_oDataReader = m_oCommand.ExecuteReader();
return m_oDataReader;
}
catch (SqlException ex)
{
throw (new DataAccessException("Hubo un error en la Consulta. <br/> Consulta: " + sSQL + " <br/> Parametros: " + ParamsValues, ex));
}
catch (DataAccessException ex)
{
throw (ex);
}
catch (Exception ex)
{
throw (new DataAccessException("Hubo un error inesperado al tratar de realizar la consulta.", ex));
}
}
