/// <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();
}
}
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 override ProductInfo GetProductSimpleInfo(int productId)
{
ProductInfo result = null;
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.Append(" SELECT dp.*, p.TaobaoProductId, p.Unit, p.ImageUrl1, p.ImageUrl2, p.ImageUrl3, p.ImageUrl4, p.ImageUrl5,p.LowestSalePrice, p.PenetrationStatus");
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;");
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, HiContext.Current.SiteSettings.UserId.Value);
using (System.Data.IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
if (dataReader.Read())
{
result = DataMapper.PopulateProduct(dataReader);
}
}
return(result);
}
public static string GetUpdateUrl()
{
DbAccessLayer.MySqlDbAccess db = new DbAccessLayer.MySqlDbAccess(ConnectionString(), DbAccessLayer.ConnectionStringSourceType.MySetting);
System.Data.IDataReader reader = db.GetDataReaderFromProcedure("stpDad_settingsSelUpdateUrl");
string url = string.Empty;
if (db.HasError)
{
return(string.Empty);
}
if (reader.Read())
{
url = DbAccessLayer.MySqlDataHelper.GetString(reader, "settingValue");
}
reader.Close();
return(url);
}
public static IList <T> ToList <T>(System.Data.IDataReader reader) where T : class
{
//实例化一个List<>泛型集合
IList <T> m_List = new List <T>();
while (reader.Read())
{
//由于是是未知的类型,所以必须通过Activator.CreateInstance<T>()方法来依据T的类型动态创建数据实体对象
T RowInstance = Activator.CreateInstance <T>();
//通过反射取得对象所有的Property
foreach (PropertyInfo Property in typeof(T).GetProperties())
{
//将DataReader读取出来的数据填充到对象实体的属性里
Property.SetValue(RowInstance, Convert.ChangeType(reader[Property.Name].ToString(), Property.PropertyType), null);
}
//将数据实体对象add到泛型集合中
m_List.Add(RowInstance);
}
return(m_List);
}
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 distro_MemberGrades WHERE CreateUserId=@CreateUserId Order by Point Desc ");
this.database.AddInParameter(sqlStringCommand, "CreateUserId", System.Data.DbType.Int32, HiContext.Current.SiteSettings.UserId.Value);
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);
}
public List <RolePermissionInfo> GetPermissionsByRoleId(int roleId)
{
List <RolePermissionInfo> list = new List <RolePermissionInfo>();
string query = "SELECT [PermissionId],[RoleId] FROM [aspnet_RolePermissions] where RoleId=@RoleId";
System.Data.Common.DbCommand sqlStringCommand = this.db.GetSqlStringCommand(query);
this.db.AddInParameter(sqlStringCommand, "@RoleId", System.Data.DbType.Int32, roleId);
using (System.Data.IDataReader dataReader = this.db.ExecuteReader(sqlStringCommand))
{
while (dataReader.Read())
{
list.Add(new RolePermissionInfo
{
PermissionId = dataReader["PermissionId"].ToString(),
RoleId = roleId
});
}
}
return(list);
}
//END HITT 8329 #############################################
//BEGIN CR - 23 -- Canned Search
public static KVPairCollection GetCannedSearchIdText(string procname, string param)
{
Database db = DatabaseFactory.CreateDatabase();
DbCommand cw = db.GetStoredProcCommand(procname);
db.AddInParameter(cw, "recordid", DbType.String, param);
using (System.Data.IDataReader dr = db.ExecuteReader(cw))
{
KVPairCollection coll = new KVPairCollection();
while (dr.Read())
{
KVPair k = new KVPair(
dr.GetInt32(0).ToString(),
(dr[1] == DBNull.Value) ? "" : dr[1].ToString()
);
coll.Add(k);
}
return(coll);
}
}
public ShoppingCartInfo GetShoppingCart(MemberInfo member, int Templateid)
{
ShoppingCartInfo shoppingCartInfo = new ShoppingCartInfo();
System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT * FROM Hishop_ShoppingCarts WHERE UserId = @UserId and Templateid=@Templateid");
this.database.AddInParameter(sqlStringCommand, "UserId", System.Data.DbType.Int32, member.UserId);
this.database.AddInParameter(sqlStringCommand, "Templateid", System.Data.DbType.Int32, Templateid);
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);
}
internal TList ExecuteMany <TList>(Action <TEntity, IDataReader> customMapperReader, Action <TEntity, dynamic> customMapperDynamic) where TList : IList <TEntity>
{
TList result = (TList)this.dbCommandData_0.Context.Data.EntityFactory.Create(typeof(TList));
System.Data.IDataReader innerReader = this.dbCommandData_0.Reader.InnerReader;
if (this.iqueryTypeHandler_0.IterateDataReader)
{
while (innerReader.Read())
{
TEntity item = (TEntity)this.iqueryTypeHandler_0.HandleType(customMapperReader, customMapperDynamic);
((ICollection <TEntity>)result).Add(item);
}
}
else
{
TEntity item2 = (TEntity)this.iqueryTypeHandler_0.HandleType(customMapperReader, customMapperDynamic);
((ICollection <TEntity>)result).Add(item2);
}
return(result);
}
public override DbQueryResult GetMyBalanceDetails(BalanceDetailQuery query)
{
DbQueryResult result;
if (null == query)
{
result = new DbQueryResult();
}
else
{
DbQueryResult dbQueryResult = new DbQueryResult();
StringBuilder stringBuilder = new StringBuilder();
string text = StoreData.BuildBalanceDetailsQuery(query);
stringBuilder.AppendFormat("select top {0} B.JournalNumber,B.UserId,B.UserName, B.TradeDate,B.TradeType,B.Income,B.Expenses,B.Balance,B.Remark", query.PageSize);
stringBuilder.Append(" from Hishop_DistributorBalanceDetails B where 0=0 ");
if (query.PageIndex == 1)
{
stringBuilder.AppendFormat("{0} ORDER BY JournalNumber DESC", text);
}
else
{
stringBuilder.AppendFormat(" and JournalNumber < (select min(JournalNumber) from (select top {0} JournalNumber from Hishop_DistributorBalanceDetails where 0=0 {1} ORDER BY JournalNumber DESC ) as tbltemp) {1} ORDER BY JournalNumber DESC", (query.PageIndex - 1) * query.PageSize, text);
}
if (query.IsCount)
{
stringBuilder.AppendFormat(";select count(JournalNumber) as Total from Hishop_DistributorBalanceDetails where 0=0 {0}", text);
}
System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand(stringBuilder.ToString());
using (System.Data.IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
dbQueryResult.Data = DataHelper.ConverDataReaderToDataTable(dataReader);
if (query.IsCount && dataReader.NextResult())
{
dataReader.Read();
dbQueryResult.TotalRecords = dataReader.GetInt32(0);
}
}
result = dbQueryResult;
}
return(result);
}
public void CreateArticleXml()
{
XmlDocument xmlDocument = new XmlDocument();
XmlDeclaration newChild = xmlDocument.CreateXmlDeclaration("1.0", "utf-8", null);
xmlDocument.AppendChild(newChild);
XmlElement xmlElement = xmlDocument.CreateElement("", "urlset", "http://www.sitemaps.org/schemas/sitemap/0.9");
xmlDocument.AppendChild(xmlElement);
System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand("select ArticleId from dbo.Hishop_Articles where IsRelease='1'");
using (System.Data.IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
while (dataReader.Read())
{
XmlElement xmlElement2 = xmlDocument.CreateElement("url", xmlElement.NamespaceURI);
XmlElement xmlElement3 = xmlDocument.CreateElement("loc", xmlElement2.NamespaceURI);
XmlText newChild2 = xmlDocument.CreateTextNode(this.prourl + Globals.GetSiteUrls().UrlData.FormatUrl("ArticleDetails", new object[]
{
Convert.ToInt32(dataReader["ArticleId"])
}));
xmlElement3.AppendChild(newChild2);
XmlElement xmlElement4 = xmlDocument.CreateElement("lastmod", xmlElement2.NamespaceURI);
XmlText newChild3 = xmlDocument.CreateTextNode(DateTime.Now.ToString("yyyy-MM-dd"));
xmlElement4.AppendChild(newChild3);
XmlElement xmlElement5 = xmlDocument.CreateElement("changefreq", xmlElement2.NamespaceURI);
xmlElement5.InnerText = "daily";
XmlElement xmlElement6 = xmlDocument.CreateElement("priority", xmlElement2.NamespaceURI);
xmlElement6.InnerText = "1.0";
xmlElement2.AppendChild(xmlElement3);
xmlElement2.AppendChild(xmlElement4);
xmlElement2.AppendChild(xmlElement5);
xmlElement2.AppendChild(xmlElement6);
xmlElement.AppendChild(xmlElement2);
}
}
if (File.Exists(this.webroot + "/sitemap3.xml"))
{
File.Delete(this.webroot + "/sitemap3.xml");
}
xmlDocument.Save(this.webroot + "/sitemap3.xml");
}
public override DbQueryResult GetBalanceDrawRequests(BalanceDrawRequestQuery query)
{
DbQueryResult result;
if (null == query)
{
result = new DbQueryResult();
}
else
{
DbQueryResult dbQueryResult = new DbQueryResult();
StringBuilder stringBuilder = new StringBuilder();
string text = MemberData.BuildBalanceDrawRequestQuery(query);
stringBuilder.AppendFormat("select top {0} *", query.PageSize);
stringBuilder.Append(" from Hishop_BalanceDrawRequest B where 0=0 ");
if (query.PageIndex == 1)
{
stringBuilder.AppendFormat("{0} ORDER BY RequestTime DESC", text);
}
else
{
stringBuilder.AppendFormat(" and RequestTime < (select min(RequestTime) from (select top {0} RequestTime from Hishop_BalanceDrawRequest where 0=0 {1} ORDER BY RequestTime DESC ) as tbltemp) {1} ORDER BY RequestTime DESC", (query.PageIndex - 1) * query.PageSize, text);
}
if (query.IsCount)
{
stringBuilder.AppendFormat(";select count(*) as Total from Hishop_BalanceDrawRequest where 0=0 {0}", text);
}
System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand(stringBuilder.ToString());
using (System.Data.IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
dbQueryResult.Data = DataHelper.ConverDataReaderToDataTable(dataReader);
if (query.IsCount && dataReader.NextResult())
{
dataReader.Read();
dbQueryResult.TotalRecords = dataReader.GetInt32(0);
}
}
result = dbQueryResult;
}
return(result);
}
//List
public List <SalesManModel> GetAllSalesMan()
{
List <SalesManModel> lstSaleMan = new List <SalesManModel>();
SalesManModel objModel;
string Query = "SELECT DISTINCT SalesMan_Id,SM_Name,SM_Alias 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);
}
public string getCodice(string systemid)
{
string result = string.Empty;
DocsPaUtils.Query q = DocsPaUtils.InitQuery.getInstance().getQuery("S_CODICE_CORRETTO_BY_ID");
q.setParam("system_id", systemid);
logger.Debug("getCodice " + q.getSQL());
string commandText = q.getSQL();
using (DocsPaDB.DBProvider dbProvider = new DocsPaDB.DBProvider())
{
using (System.Data.IDataReader reader = dbProvider.ExecuteReader(commandText))
{
if (reader.Read())
{
result = reader["var_cod_rubrica"].ToString();
}
}
}
return(result);
}
public System.Data.DataTable LoadVote(int voteId, out string voteName, out int checkNum, out int voteNum)
{
System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand("SELECT VoteName, MaxCheck, (SELECT SUM(ItemCount) FROM Hishop_VoteItems WHERE VoteId = @VoteId) AS VoteNum FROM Hishop_Votes WHERE VoteId = @VoteId; SELECT * FROM Hishop_VoteItems WHERE VoteId = @VoteId");
this.database.AddInParameter(sqlStringCommand, "VoteId", System.Data.DbType.Int64, voteId);
voteName = string.Empty;
checkNum = 1;
voteNum = 0;
System.Data.DataTable result;
using (System.Data.IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
if (dataReader.Read())
{
voteName = (string)dataReader["VoteName"];
checkNum = (int)dataReader["MaxCheck"];
voteNum = (int)dataReader["VoteNum"];
}
dataReader.NextResult();
result = DataHelper.ConverDataReaderToDataTable(dataReader);
}
return(result);
}
public List <TdsModel> GetListofTDS()
{
List <TdsModel> lstTdsCategory = new List <TdsModel>();
TdsModel objTds;
string Query = "SELECT * FROM `tdscategory`";
System.Data.IDataReader dr = _dbHelper.ExecuteDataReader(Query, _dbHelper.GetConnObject());
while (dr.Read())
{
objTds = new TdsModel();
objTds.Tds_Id = Convert.ToInt32(dr["Tds_Id"]);
objTds.TdsCategoryName = dr["TdsCategoryName"].ToString();
objTds.Selectcode = dr["SelectCode"].ToString();
lstTdsCategory.Add(objTds);
}
return(lstTdsCategory);
}
/// <summary>
///
/// </summary>
/// <param name="codice"></param>
/// <returns></returns>
public bool codicePerlaPubblicazione(string codice)
{
bool result = false;
DocsPaUtils.Query q = DocsPaUtils.InitQuery.getInstance().getQuery("S_VERIFICA_CODICE_CORRETTO");
q.setParam("var_cod_rubrica", codice);
logger.Debug("codicePerlaPubblicazione " + q.getSQL());
string commandText = q.getSQL();
using (DocsPaDB.DBProvider dbProvider = new DocsPaDB.DBProvider())
{
using (System.Data.IDataReader reader = dbProvider.ExecuteReader(commandText))
{
if (reader.Read())
{
result = true;
}
}
}
return(result);
}
internal List <TotalSoldModel> GetTotalSoldFromDb()
{
totalSold = new List <TotalSoldModel>();
AssuredConnected();
using (System.Data.IDbCommand command = connection.CreateCommand())
{
command.CommandText = storedProc_GetInventoryTotalsByType;
command.CommandType = System.Data.CommandType.StoredProcedure;
using (System.Data.IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
totalSold.Add(MapTotalSoldFromDb(reader));
}
}
}
return(totalSold);
}
public override List <Subscription> ConvertResultsList(DbCommandWrapper cw, System.Data.IDataReader reader)
{
//Once the query is executed the results must converted to a
//array of Subscription objects
//Check if reader is null. No results returned
if (reader == null)
{
return(null);
}
List <Subscription> subscriptions = new List <Subscription>();
while (reader.Read())
{
//Init new subscription object for each subscription record
Subscription subscriptionDto = new Subscription();
//Populate subscription object from record
subscriptionDto.SubscriptionId = (Int32)reader["SubscriptionId"];
subscriptionDto.VolumeIssueId = Convert.ToInt32(reader["VolumeIssueId"]);
subscriptionDto.ArticleId = Convert.ToInt32(reader["ArticleId"]);
subscriptionDto.VolumeName = (string)reader["VolumeName"];
subscriptionDto.IssueName = (string)reader["IssueName"];
subscriptionDto.ArticleName = ((string)reader["ArticleName"]).Substring(0, 15);
subscriptionDto.EffectiveDate = (DateTime)reader["EffectiveDate"];
subscriptionDto.ExpirationDate = (DateTime)reader["ExpirationDate"];
subscriptionDto.IsActive = (bool)reader["Active"];
//Add to list array
subscriptions.Add(subscriptionDto);
}
if (subscriptions.Count == 0)
{
return(null);
}
return(subscriptions);
}
public override DbQueryResult GetBalanceDetails(BalanceDetailQuery query)
{
DbQueryResult result;
if (null == query)
{
result = null;
}
else
{
DbQueryResult dbQueryResult = new DbQueryResult();
StringBuilder stringBuilder = new StringBuilder();
string text = ProfileData.BuildBalanceDetailsQuery(query);
stringBuilder.AppendFormat("SELECT TOP {0} * FROM Hishop_BalanceDetails B WHERE 0=0", query.PageSize);
if (query.PageIndex == 1)
{
stringBuilder.AppendFormat(" {0} ORDER BY JournalNumber DESC;", text);
}
else
{
stringBuilder.AppendFormat(" AND JournalNumber < (SELECT MIN(JournalNumber) FROM (SELECT TOP {0} JournalNumber FROM Hishop_BalanceDetails WHERE 0=0 {1} ORDER BY JournalNumber DESC ) AS T) {1} ORDER BY JournalNumber DESC;", (query.PageIndex - 1) * query.PageSize, text);
}
if (query.IsCount)
{
stringBuilder.AppendFormat(" SELECT COUNT(JournalNumber) AS Total from Hishop_BalanceDetails WHERE 0=0 {0}", text);
}
System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand(stringBuilder.ToString());
using (System.Data.IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
dbQueryResult.Data = DataHelper.ConverDataReaderToDataTable(dataReader);
if (query.IsCount && dataReader.NextResult())
{
dataReader.Read();
dbQueryResult.TotalRecords = dataReader.GetInt32(0);
}
}
result = dbQueryResult;
}
return(result);
}
public override DbQueryResult GetBalanceDrawRequests(BalanceDrawRequestQuery query)
{
DbQueryResult result;
if (null == query)
{
result = null;
}
else
{
DbQueryResult dbQueryResult = new DbQueryResult();
StringBuilder stringBuilder = new StringBuilder();
string text = UnderlingData.BuildBalanceDrawRequestQuery(query);
stringBuilder.AppendFormat("SELECT Top {0} * FROM distro_BalanceDrawRequest B WHERE 0=0", query.PageSize);
if (query.PageIndex == 1)
{
stringBuilder.AppendFormat("{0} ORDER BY RequestTime DESC;", text);
}
else
{
stringBuilder.AppendFormat(" AND RequestTime < (SELECT MIN(RequestTime) FROM (SELECT TOP {0} RequestTime FROM distro_BalanceDrawRequest WHERE 0=0 {1} ORDER BY RequestTime DESC ) as T) {1} ORDER BY RequestTime DESC;", (query.PageIndex - 1) * query.PageSize, text);
}
if (query.IsCount)
{
stringBuilder.AppendFormat("SELECT COUNT(*) AS Total from distro_BalanceDrawRequest WHERE 0=0 {0}", text);
}
System.Data.Common.DbCommand sqlStringCommand = this.database.GetSqlStringCommand(stringBuilder.ToString());
using (System.Data.IDataReader dataReader = this.database.ExecuteReader(sqlStringCommand))
{
dbQueryResult.Data = DataHelper.ConverDataReaderToDataTable(dataReader);
if (query.IsCount && dataReader.NextResult())
{
dataReader.Read();
dbQueryResult.TotalRecords = dataReader.GetInt32(0);
}
}
result = dbQueryResult;
}
return(result);
}
public List <Moneda> getMonedas()
{
if (_ListaMonedas != null && _ListaMonedas.Count > 0)
{
return(_ListaMonedas);
}
else
{
DbCommand Command = new SqlCommand("SELECT top 2 CODMONEDA AS ID,DESCRIPCION AS NOMBRE, INICIALES,MORA FROM MONEDAS where codmoneda between 1 and 2", (SqlConnection)Connection);
System.Data.IDataReader rd = null;
List <Moneda> FormasPago = new List <Moneda>();
Moneda Entity = null;
try
{
if (_Connection.State == System.Data.ConnectionState.Closed)
{
_Connection.Open();
}
rd = ExecuteReader(Command);
while (rd.Read())
{
Entity = (Moneda)getMonedaFromReader(rd);
if (Entity.Descripcion.Length > 0)
{
FormasPago.Add(Entity);
}
}
rd.Close();
return(FormasPago);
}
catch (Exception Ex)
{
throw Ex;
}
finally
{
CerrarConexion(_Connection);
}
}
}
internal Moneda getMonedaByID(int Moneda)
{
foreach (Moneda M in _ListaMonedas)
{
if (M.Codmoneda == Moneda)
{
return((Moneda)M);
}
}
List <IDataParameter> P = new List <IDataParameter>();
P.Add(new SqlParameter("@ID", Moneda));
DbCommand Command = new SqlCommand("SELECT TOP 1 CODMONEDA AS ID,DESCRIPCION AS NOMBRE, INICIALES,MORA FROM MONEDAS where codmoneda = @ID", (SqlConnection)Connection);
System.Data.IDataReader rd = null;
Moneda Entity = null;
try
{
if (_Connection.State == System.Data.ConnectionState.Closed)
{
_Connection.Open();
}
rd = ExecuteReader(Command, P);
if (rd.Read())
{
Entity = (Moneda)getMonedaFromReader(rd);
}
rd.Close();
return(Entity);
}
catch (Exception Ex)
{
throw Ex;
}
finally
{
CerrarConexion(_Connection);
}
}
public List <BatchModel> GetAllBatchesFromDb()
{
batches = new List <BatchModel>();
AssuredConnected();
using (System.Data.IDbCommand command = connection.CreateCommand())
{
string text = $"select * from dbo.OnHandInventory";
command.CommandText = text;
command.CommandType = CommandType.Text;
using (System.Data.IDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
batches.Add(MapBatchesFromDb(reader));
}
}
}
return(batches);
}
public void ListDoldur(ref ListView list, string sql)
{
CheckConnection();
System.Data.IDataReader rd = TSelect(sql);
list.Items.Clear();
int i;
while (rd != null && rd.Read())
{
ListViewItem row = new ListViewItem();
if (rd.IsDBNull(0))
{
row.Text = "";
}
else
{
row.Text = rd.GetValue(0).ToString();
}
for (i = 1; i < rd.FieldCount; i++)
{
if (rd.IsDBNull(i))
{
row.SubItems.Add("");
}
else
{
row.SubItems.Add(rd.GetValue(i).ToString());
}
}
list.Items.Add(row);
}
if (rd != null)
{
rd.Close();
}
}
/// <summary>
/// Get an account from the database via a known ID
/// </summary>
/// <param name="id"></param>
/// <returns>null if not found</returns>
public ServerUser GetUserByName(String userName)
{
ServerUser user = null;
try {
DbCommand command = dbConnection.CreateCommand();
command.CommandText = "SELECT * FROM users WHERE name='" + userName + "';";
DbReader reader = command.ExecuteReader();
while (reader.Read())
{
user = new ServerUser(reader["id"].ToString(), reader["name"].ToString(), reader["password"].ToString());
}
reader.Close();
}
catch (Exception e) {
Console.WriteLine("There was an error fetching the account " + e.Message);
}
return(user);
}
public static DbQueryResult PagingByRownumber(int pageIndex, int pageSize, string sortBy, SortAction sortOrder, bool isCount, string table, string pk, string filter, string selectFields, int partitionSize)
{
DbQueryResult result;
if (string.IsNullOrEmpty(table))
{
result = null;
}
else if (string.IsNullOrEmpty(sortBy) && string.IsNullOrEmpty(pk))
{
result = null;
}
else
{
if (string.IsNullOrEmpty(selectFields))
{
selectFields = "*";
}
string query = DataHelper.BuildRownumberQuery(sortBy, sortOrder, isCount, table, pk, 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(query);
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);
}
private string RicercaFinale(string codice, ref string id_parent)
{
id_parent = null;
DocsPaUtils.Query q = DocsPaUtils.InitQuery.getInstance().getQuery("S_CODICE_CORRETTO");
q.setParam("var_cod_rubrica", codice);
logger.Debug("getCodiceCorretto " + q.getSQL());
string commandText = q.getSQL();
using (DocsPaDB.DBProvider dbProvider = new DocsPaDB.DBProvider())
{
using (System.Data.IDataReader reader = dbProvider.ExecuteReader(commandText))
{
if (reader.Read())
{
id_parent = reader["id_parent"].ToString();
//return reader["var_cod_rubrica"].ToString();
return(reader["system_id"].ToString());
}
}
}
return(string.Empty);
}
/// <summary>
/// Reperimento delle amministrazioni disponibili
/// </summary>
/// <returns></returns>
public static InfoAmministrazione[] GetAmministrazioni()
{
List <InfoAmministrazione> list = new List <InfoAmministrazione>();
string query = "SELECT TO_CHAR(SYSTEM_ID) AS ID," +
"VAR_CODICE_AMM AS CODICE," +
"VAR_DESC_AMM AS DESCR," +
"VAR_LIBRERIA AS LIBRERIA," +
"VAR_FORMATO_SEGNATURA AS SEGN," +
"VAR_FORMATO_FASCICOLATURA AS FASC," +
"VAR_DOMINIO AS DOMINIO," +
"VAR_SMTP AS SMTP," +
"TO_CHAR(NUM_PORTA_SMTP) AS PORTASMTP," +
"VAR_USER_SMTP AS USERSMTP," +
"VAR_PWD_SMTP AS PWDSMTP," +
"TO_CHAR(ID_RAGIONE_TO) AS RAGTO," +
"TO_CHAR(ID_RAGIONE_CC) AS RAGCC," +
"TO_CHAR(NUM_GG_PERM_TODOLIST) AS GG_TDL," +
"CHA_ATTIVA_GG_PERM_TODOLIST AS A_GG_TDL," +
"CHA_SMTP_SSL AS SMTP_SSL,CHA_SMTP_STA AS SMTP_STA," +
"FROM_EMAIL_ADDRESS AS FROM_EMAIL, ID_RAGIONE_COMPETENZA AS RAGCOMP," +
"TO_CHAR(ID_RAGIONE_CONOSCENZA) AS RAGCONO " +
"FROM DPA_AMMINISTRA";
using (DocsPaDB.DBProvider dbProvider = new DocsPaDB.DBProvider())
{
using (System.Data.IDataReader reader = dbProvider.ExecuteReader(query))
{
while (reader.Read())
{
logger.Error("numero colonne trovate: " + reader.FieldCount.ToString());
list.Add(GetInfoAmministrazione(reader));
}
}
}
return(list.ToArray());
}
