public int InsertMeasureWay(MeasureWayEntity me)
{
StringBuilder sb = new StringBuilder("insert into MeasureWay(Mwa_Name,Mwa_Remark,Mwa_IfUse) values(");
sb.AppendFormat(" '{0}',", me.MwaName);
sb.AppendFormat(" '{0}',", me.MwaRemark);
sb.AppendFormat(" '{0}')", me.MwaIfUse == true ? 1 : 0);
return(SqlDataHelper.ExecuteNonQuery(SqlDataHelper.GetConnection(), CommandType.Text, sb.ToString()));
}
public DataSet SelectOrganizationByCmstName(string cmstName)
{
string sqlstr = "select * from dbo.CmstOrganization where Cmst_Name='{0}'";
sqlstr = string.Format(sqlstr, cmstName);
DataSet ds = SqlDataHelper.ExecuteDataSet(SqlDataHelper.GetConnection(), CommandType.Text, sqlstr);
return(ds);
}
public int UpdateMeasureWay(MeasureWayEntity me)
{
StringBuilder sb = new StringBuilder("update dbo.MeasureWay set");
sb.AppendFormat(" Mwa_Remark='{0}', ", me.MwaRemark);
sb.AppendFormat(" Mwa_IfUse={0} ", me.MwaIfUse == true ? 1 : 0);
sb.AppendFormat(" where Mwa_Id={0}", me.MwaId);
return(SqlDataHelper.ExecuteNonQuery(SqlDataHelper.GetConnection(), CommandType.Text, sb.ToString()));
}
public OperationEntity SelectOperationEntity(int operationID)
{
string sqlstr = "select * from dbo.Operation where Ope_ID={0}";
sqlstr = string.Format(sqlstr, operationID);
DataSet ds = SqlDataHelper.ExecuteDataSet(SqlDataHelper.GetConnection(), CommandType.Text, sqlstr);
return(DsToOperationEntity(ds));
}
public MenuEntity SelectMenuEntity(int menuID)
{
string sqlstr = "select * from dbo.Menu where Meu_ID={0}";
sqlstr = string.Format(sqlstr, menuID);
DataSet ds = SqlDataHelper.ExecuteDataSet(SqlDataHelper.GetConnection(), CommandType.Text, sqlstr);
return(DsToMenuEntity(ds));
}
public OrganizationEntity SelectOrganizationByID(int cmstID)
{
string sqlstr = "select * from dbo.CmstOrganization where Cmst_ID={0}";
sqlstr = string.Format(sqlstr, cmstID);
DataSet ds = SqlDataHelper.ExecuteDataSet(SqlDataHelper.GetConnection(), CommandType.Text, sqlstr);
return(DsToOrganizationEntity(ds));
}
public int CheckStocktakingBillDetail(long stocktakingDetailId, int status, int result, int checker, string remark, SqlTransaction st)
{
string sqlstr = $" update StocktakingBillDetail set Sbd_Status = {status}, Sbd_Result = {result}, Sbd_Ope_Checker = {checker}, Sbd_CheckTime = getdate(), Sbd_Remark = @Remark where Sbd_Id = {stocktakingDetailId} ";
SqlParameter spRemark = new SqlParameter("@Remark", SqlDbType.NVarChar, 255)
{
Value = remark
};
return(SqlDataHelper.ExecuteNonQuery(st, CommandType.Text, sqlstr, spRemark));
}
/// <summary>
/// 获取五道项目对应的仓库
/// </summary>
/// <returns></returns>
public List <int> getWarehouseIds()
{
var sql = "select * from XM_WareHouses where isEnable = 0 and ParentID = 0 and ProjectId = 1";//五道数据库中是1
//var sql = "select Id from XM_WareHouses where isEnable = 0 and ParentID = 0 and ProjectId = 21";
var dt = SqlDataHelper.GetDatatableBySql(sql);
var ErpWarehouse = CurUtilManager.ToEntity <ErpWarehouseId>(dt);
var ErpWarehouseIdList = ErpWarehouse.Select(m => m.Id).ToList();
return(ErpWarehouseIdList);
}
public bool CheckedOperatorAccountRepeate(string account, int operatorId)
{
DataSet ds = SqlDataHelper.ExecuteDataSet(SqlDataHelper.GetConnection(), "Operator_View_Check", account, operatorId);
if (DataValidate.CheckDataSetNotEmpty(ds))
{
return(true);
}
return(false);
}
public StockModel SelectStocktakingBill(int cmstId, long Id, SqlTransaction st)
{
string sqlstr = $" select * from StocktakingView where StackingBillID = {Id} and CmstID = {cmstId} ";
DataSet ds = SqlDataHelper.ExecuteDataSet(st, CommandType.Text, sqlstr);
if (DataValidate.CheckDataSetNotEmpty(ds))
{
return(DrToStocktakingBill(ds.Tables[0].Rows[0]));
}
return(null);
}
public SupplierSystemSqlData(ISupplierSystem m)
{
this.m = m;
SqlDataHelper.TableName = "table_m_temp";
SqlDataHelper.DataSource = ".";
SqlDataHelper.DatabaseName = "TKDB";
SqlDataHelper.IntegratedSecurity = true;
conn = SqlDataHelper.GetSqlConnection();
comm = new SqlCommand();
comm.Connection = conn;
}
public DataSet SelectAllMenu()
{
string sqlstr = "select * from dbo.Menu";
DataSet ds = SqlDataHelper.ExecuteDataSet(SqlDataHelper.GetConnection(), CommandType.Text, sqlstr);
if (DataValidate.CheckDataSetNotEmpty(ds))
{
return(ds);
}
return(null);
}
//---------------------------------------------------------------------
#endregion
#region --------------GetShortShoppingCart--------------
//---------------------------------------------------------------------
//GetShortShoppingCart
//---------------------------------------------------------------------
public List <ShoppingCartItem> GetShortShoppingCart(int langId, int userId)
{
string sp = "[dbo].[ShoppingCart_GetShortShoppingCart]";
CustomDbParameterList customParameters = new CustomDbParameterList();
customParameters.Add("@langId", langId);
customParameters.Add("@UserID", userId);
List <ShoppingCartItem> shoppingCartItems = SqlDataHelper.RetrieveEntityList <ShoppingCartItem>(sp, customParameters);
return(shoppingCartItems);
}
//---------------------------------------------------------------------
#endregion
#region --------------UpdateCart--------------
//---------------------------------------------------------------------
//UpdateCart
//---------------------------------------------------------------------
public bool UpdateCart(int userId, List <ShoppingCartItem> items)
{
bool savestats = false;
string sp1_RemoveAllItems = "[dbo].[ShoppingCart_RemoveAllItems]";
string sp2_AddItemWithOutReturnCount = "[dbo].[ShoppingCart_AddItemWithOutReturnCount]";
using (SqlConnection myConnection = SqlDataHelper.GetSqlConnection())
{
SqlTransaction transaction = null;
try
{
myConnection.Open();
transaction = myConnection.BeginTransaction();
using (SqlCommand myCommand = new SqlCommand(sp1_RemoveAllItems, myConnection, transaction))
{
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.Add("@UserID", SqlDbType.Int, 4).Value = userId;
myCommand.ExecuteNonQuery();
}
using (SqlCommand myCommand = new SqlCommand(sp2_AddItemWithOutReturnCount, myConnection, transaction))
{
myCommand.CommandType = CommandType.StoredProcedure;
myCommand.Parameters.Add("@UserID", SqlDbType.Int, 4).Value = userId;
myCommand.Parameters.Add("@ProductID", SqlDbType.Int, 4);
myCommand.Parameters.Add("@Quantity", SqlDbType.Int, 4);
foreach (var item in items)
{
myCommand.Parameters["@ProductID"].Value = item.ProductID;
myCommand.Parameters["@Quantity"].Value = item.Quantity;
myCommand.ExecuteNonQuery();
}
}
transaction.Commit();
myConnection.Close();
savestats = true;
}
catch (Exception ex)
{
// Attempt to roll back the transaction.
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
// This catch block will handle any errors that may have occurred
// on the server that would cause the rollback to fail, such as
// a closed connection.
}
}
}
return(savestats);
}
/// <summary>
/// 获取库存信息
/// </summary>
/// <param name="pageSize">每页获取条数</param>
/// <param name="pageNum">获取页码</param>
/// <returns></returns>
public List <ErpInventoryList> GetXLMInventory(int pageSize, int pageNum, ref int totalCount)
{
try
{
DateTime date = DateTime.Parse(DateTime.Now.ToShortDateString());
var ids = getWarehouseIds();
var sqlwehereids = string.Join(",", ids);
#region
var sql = string.Format(@"select top {0} * from(
(select c.PlatformMerchantCode,b.ProductName,a.BarCode,b.ProductUnit as Unit,a.Inventory
from XM_XLMInventory a join XM_Product b on a.ManufacturersCode = b.ManufacturersCode
left join XM_ProductDetails c on c.ProductId = b.Id
where a.CreateDate > '{1}' and b.IsEnable = 0 and c.IsEnable = 0
) union (
select c.PlatformMerchantCode,b.ProductName,null as BarCode, b.ProductUnit as Unit,a.CanOrderCount as Inventory
from XM_InventoryInfo a join XM_Product b on a.PlatformMerchantCode = b.ManufacturersCode
left join XM_ProductDetails c on c.ProductId = b.Id
where b.IsEnable = 0 and c.IsEnable = 0 and a.WfId in ({3}) and c.PlatformMerchantCode not in(
select c.PlatformMerchantCode
from XM_XLMInventory a join XM_Product b on a.ManufacturersCode = b.ManufacturersCode
left join XM_ProductDetails c on c.ProductId = b.Id
where b.IsEnable = 0 and c.IsEnable = 0
)
)) as tb where tb.PlatformMerchantCode not in
(
select top {2} * from (
(select c.PlatformMerchantCode
from XM_XLMInventory a join XM_Product b on a.ManufacturersCode = b.ManufacturersCode
left join XM_ProductDetails c on c.ProductId = b.Id
where a.CreateDate > '{1}' and b.IsEnable = 0 and c.IsEnable = 0
) union (
select c.PlatformMerchantCode
from XM_InventoryInfo a join XM_Product b on a.PlatformMerchantCode = b.ManufacturersCode
left join XM_ProductDetails c on c.ProductId = b.Id
where b.IsEnable = 0 and c.IsEnable = 0 and a.WfId in ({3}) and c.PlatformMerchantCode not in(
select c.PlatformMerchantCode
from XM_XLMInventory a join XM_Product b on a.ManufacturersCode = b.ManufacturersCode
left join XM_ProductDetails c on c.ProductId = b.Id
where b.IsEnable = 0 and c.IsEnable = 0))
) as tb1 order by tb1.PlatformMerchantCode
) order by tb.PlatformMerchantCode ", pageSize, date, pageSize * (pageNum - 1), sqlwehereids);//查询库存管理中的物品库存信息
var dt = SqlDataHelper.GetDatatableBySql(sql);
var ErpInventoryList = CurUtilManager.ToEntity <ErpInventoryList>(dt);
#endregion
totalCount = ErpInventoryList.Distinct().Count();
return(ErpInventoryList.ToList());
}
catch (Exception ex)
{
return(null);
}
}
public int InsertMenu(MenuEntity me)
{
string sqlstr = "insert into dbo.Menu (Csy_ID,Meu_Menu,Meu_FatherMenuID,Meu_Rank,Meu_Url)values({0},'{1}',{2},{3},'{4}')";
sqlstr = string.Format(sqlstr, me.CsyID, me.MenuName, me.FatherID, me.Rank, me.Url);
int ret = SqlDataHelper.ExecuteNonQuery(SqlDataHelper.GetConnection(), CommandType.Text, sqlstr);
if (ret > 0)
{
return(1);
}
return(-1);
}
public int InsertOperation(OperationEntity oe)
{
string sqlstr = "insert into dbo.Operation(Ope_Meu_ID,Csy_ID,Ope_Name,Ope_Url)values({0},{1},'{2}','{3}')";
sqlstr = string.Format(sqlstr, oe.MenuID, oe.CsyID, oe.OperateName, oe.OperateUrl);
int ret = SqlDataHelper.ExecuteNonQuery(SqlDataHelper.GetConnection(), CommandType.Text, sqlstr);
if (ret > 0)
{
return(1);
}
return(-1);
}
public bool InsertRoleOperate(int roleId, int operationId, int cmstid)
{
int ret = SqlDataHelper.ExecuteNonQuery(SqlDataHelper.GetConnection(), "RoleOperate_Insert", roleId, operationId, cmstid);
if (ret > 0)
{
return(true);
}
else
{
return(false);
}
}
//---------------------------------------------------------------------
#endregion
//[dbo].[Message_AddUserMessage]
#region --------------GetMessagesByOrderIdForUser--------------
//---------------------------------------------------------------------
//GetMessagesByOrderIdForUser
//---------------------------------------------------------------------
public List <MessageDto> GetMessagesByOrderIdForUser(int orderId)
{
string sp = "[dbo].[Message_GetMessagesByOrderIdForUser]";
CustomDbParameterList customParameters = new CustomDbParameterList();
// Set the parameters
//---------------------------------------------------------------------
customParameters.Add("@OrderId", orderId);
List <MessageDto> list =
SqlDataHelper.RetrieveEntityList <MessageDto>(sp, customParameters);
//---------------------------------------------------------------------
return(list);
}
static void Main(string[] args)
{
myConnection.Open();
SqlDataReader myReader = null;
var query = "SELECT * " + "FROM TABLE_BUG";
SqlCommand myCommand = new SqlCommand(query, myConnection);
myReader = myCommand.ExecuteReader();
helper = new SqlDataHelper(myReader);
var result = helper.Fetch(20);
helper.GetReaderProperty("Fetched");
Console.ReadLine();
}
/// <summary>
/// Saves the error log.
/// </summary>
/// <param name="userId">The user id.</param>
/// <param name="message">The message.</param>
/// <param name="stackTrace">The stack trace.</param>
/// <createdby>Amit Kumar Chandra</createdby>
/// <createddate>11/06/2012</createddate>
public static void SaveErrorLog(int userId, string message, string stackTrace)
{
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.Open();
helper.CommandText = "[webadmin].[uspSaveError]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@UserId", userId);
helper.Parameters.Add("@ErrorMessage", message);
helper.Parameters.Add("@StackTrace", stackTrace);
helper.ExecuteNonQuery();
helper.Close();
}
}
/// <summary>
/// 寻找商品表中的商品信息
/// </summary>
public List <XMProductNew> GetXMProductListByPlatFormMerchantCode(string PlatFormMerchantCode, int PlatformTypeId)
{
var sql = string.Format(@"select a.Id,a.ProductId,a.PlatformTypeId,a.PlatformMerchantCode,a.ProductTypeId,a.PlatformInventory,
a.strUrl,a.Images,a.Costprice,a.Saleprice,a.TCostprice,a.TDateTimeStart,a.TDateTimeEnd,a.IsMainPush,
b.BrandTypeId, b.ProductName,b.ManufacturersCode,b.Specifications,b.ManufacturersInventory,b.WarningQuantity,b.ProductColors,
b.ProductUnit,b.IsPremiums,b.IsEnable,b.CreateID,b.CreateDate,b.UpdateID,b.UpdateDate,a.TemporaryManufacturersCode
from XM_ProductDetails a join XM_Product b on a.ProductId = b.Id
where a.IsEnable = 0 and a.PlatformMerchantCode = '{0}' and a.PlatformTypeId = {1}", PlatFormMerchantCode, PlatformTypeId);
//先去找对应平台没有则找通用平台
var dt = SqlDataHelper.GetDatatableBySql(sql);
var resultArray = CurUtilManager.ToEntity <XMProductNew>(dt);
return(resultArray.ToList());
}
//---------------------------------------------------------------------
//AddItemAndReturnList
//---------------------------------------------------------------------
public List <ShoppingCartItem> AddItemAndReturnList(int langId, int userId, int productID, int quantity)
{
string sp = "[dbo].[ShoppingCart_AddItemAndReturnList]";
CustomDbParameterList customParameters = new CustomDbParameterList();
// Set the parameters
customParameters.Add("@langId", langId);
customParameters.Add("@UserID", userId);
customParameters.Add("@ProductID", productID);
customParameters.Add("@Quantity", quantity);
List <ShoppingCartItem> shoppingCartItems =
SqlDataHelper.RetrieveEntityList <ShoppingCartItem>(sp, customParameters);
return(shoppingCartItems);
}
public int SelectStocktakingStatus(long stocktakingId, int status, SqlTransaction st)
{
string sqlstr = $" select Count(Sb_Id) from StocktakingBill where Sb_Id = {stocktakingId} and Sb_Status = {status} ";
DataSet ds = SqlDataHelper.ExecuteDataSet(st, CommandType.Text, sqlstr);
if (DataValidate.CheckDataSetNotEmpty(ds))
{
int ret = Convert.ToInt32(ds.Tables[0].Rows[0][0]);
if (ret > 0)
{
return(ret);
}
}
return(-1);
}
public int SelectStocktakingBillDetaisCount(long stocktakingId, int status, SqlTransaction st)
{
StringBuilder sqlsb = new StringBuilder($" select count(Sbd_Id) from StocktakingBillDetail where Sbd_sb_id = {stocktakingId} ");
if (status > 0)
{
sqlsb.Append($" and Sbd_Status = {status} ");
}
DataSet ds = SqlDataHelper.ExecuteDataSet(st, CommandType.Text, sqlsb.ToString());
if (DataValidate.CheckDataSetNotEmpty(ds))
{
return(Convert.ToInt32(ds.Tables[0].Rows[0][0]));
}
return(-1);
}
public static void TestFetch_BIG_DATE_TABLE()
{
using (myConnection)
{
myConnection.Open();
SqlDataReader myReader = null;
var query = "SELECT * " +
"FROM T_DATE";
SqlCommand myCommand = new SqlCommand(query, myConnection);
myReader = myCommand.ExecuteReader();
helper = new SqlDataHelper(myReader);
var result = helper.Fetch(20);
Assert.Equal(result, 100000);
}
}
public int InsertOrganization(OrganizationEntity oe)
{
StringBuilder sb = new StringBuilder("insert into dbo.CmstOrganization(Cmst_ID,Cmst_Name,Cmst_IfUse)values(");
sb.AppendFormat(" {0},", Convert.ToInt32(oe.CmstID));
sb.AppendFormat(" '{0}',", oe.CmstName);
sb.AppendFormat(" {0})", oe.CmstIfUse == true ? 1 : 0);
//DataSet ds = SqlDataHelper.ExecuteDataSet(SqlDataHelper.GetConnection(), CommandType.Text, sb.ToString());
int ret = SqlDataHelper.ExecuteNonQuery(SqlDataHelper.GetConnection(), CommandType.Text, sb.ToString());
if (ret > 0)
{
return(1);
}
return(-1);
}
public static DataTable GetUserSpecificMenuList(int UserID)
{
DataSet ds = new DataSet();
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.CommandText = "[webadmin].[uspGetUserSpecificMenuList]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@UserId", UserID);
helper.Open();
ds = helper.ExecuteDataSet();
helper.Close();
}
return(ds.Tables[0]);
}
public DepotsPropertyEntity SelectDepotsPropertyByName(string name)
{
string sqlstr = "select * from dbo.DepotsProperty where Dpr_name='{0}'";
sqlstr = String.Format(sqlstr, name);
DataSet ds = SqlDataHelper.ExecuteDataSet(SqlDataHelper.GetConnection(), CommandType.Text, sqlstr);
if (ds.Tables[0].Rows.Count > 0)
{
return(DsToDepotsPropertyEntity(ds));
}
else
{
return(null);
}
}
public static DataSet GetUserById(int userId)
{
DataSet ds = new DataSet();
using (SqlDataHelper helper = new SqlDataHelper(ConnectionString))
{
helper.CommandText = "[webadmin].[uspGetUserById]";
helper.CommandType = CommandType.StoredProcedure;
helper.Parameters.Add("@UserId", userId);
helper.Open();
ds = helper.ExecuteDataSet();
helper.Close();
}
return(ds);
}
public RateTypeEntity SelectRateTypeByName(string name)
{
string sqlstr = "select * from dbo.RateType where Rty_Name='{0}'";
sqlstr = String.Format(sqlstr, name);
DataSet ds = SqlDataHelper.ExecuteDataSet(SqlDataHelper.GetConnection(), CommandType.Text, sqlstr);
if (ds.Tables[0].Rows.Count > 0)
{
return(DsToRateTypeEntity(ds));
}
else
{
return(null);
}
}
public static void TestGetProperty()
{
try
{
myConnection.Open();
var helper = new SqlDataHelper();
var state = helper.GetConnectionProperty(myConnection, "State");
myConnection.Close();
foreach (var prop in myConnection.GetType().GetProperties())
Console.WriteLine(helper.GetConnectionProperty(myConnection, prop.Name));
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
finally
{
myConnection.Close();
}
Console.ReadLine();
}
public static void TestFetch()
{
using (myConnection)
{
myConnection.Open();
SqlDataReader myReader = null;
var query = "SELECT name,object_id,create_date \n" +
"FROM sys.tables";
//var query = "SELECT mpg,cyl,wt \n" +
// "FROM T_MTCARS ";
SqlCommand myCommand = new SqlCommand(query, myConnection);
myReader = myCommand.ExecuteReader();
helper = new SqlDataHelper(myReader);
var result = helper.Fetch(20);
Assert.Equal(helper.ResultSet.Keys.Count, 3);
string[] cols = new string[] { "name", "object_id", "create_date" };
foreach (string key in helper.ResultSet.Keys)
Assert.Contains(key, cols);
}
Assert.Equal(helper.ResultSet["name"].Length, helper.Fetched);
Assert.Equal(helper.ResultSet.Keys.Count, helper.Cnames.Length);
}
private SqlDataHelper SQLHelper(string connectionStringName)
{
m_SQLHelper = new SqlDataHelper(connectionStringName);
return m_SQLHelper;
}
static void Main(string[] args)
{
myConnection.Open();
SqlDataReader myReader = null;
var helper = new SqlDataHelper();
var state = helper.GetConnectionProperty(myConnection, "State");
var query = "SELECT * " + "FROM TABLE_BUG";
SqlCommand myCommand = new SqlCommand(query, myConnection);
myReader = myCommand.ExecuteReader();
helper = new SqlDataHelper(myReader);
var result = helper.Fetch(20);
helper.GetReaderProperty("Fetched");
Console.ReadLine();
}
public static void TestGetReaderProperty()
{
try
{
myConnection.Open();
SqlCommand cmd = new SqlCommand("select * from sys.tables");
cmd.Connection = myConnection;
var reader = cmd.ExecuteReader();
var helper = new SqlDataHelper(reader);
foreach (var prop in myConnection.GetType().GetProperties())
Console.WriteLine(helper.GetReaderProperty( prop.Name));
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
finally
{
myConnection.Close();
}
Console.ReadLine();
}
