public static StatementParameterCollection ConvertStatement(Dictionary <string, CustomerParam> paras, out bool
isNonQuery)
{
isNonQuery = false;
if (paras != null && paras.Count > 0)
{
StatementParameterCollection dic = new StatementParameterCollection();
foreach (KeyValuePair <string, CustomerParam> item in paras)
{
if (item.Value.ParameterDirection.Equals(ParameterDirection.ReturnValue))
{
dic.AddReturnParameter(item.Key, LinqEnumHelper.IntToEnum <DbType>(item.Value.DbType));
}
else if (item.Value.ParameterDirection.Equals(ParameterDirection.Output))
{
if (!isNonQuery)
{
isNonQuery = true;
}
dic.AddOutParameter(item.Key, LinqEnumHelper.IntToEnum <DbType>(item.Value.DbType));
}
else
{
dic.AddInParameter(item.Key, LinqEnumHelper.IntToEnum <DbType>(item.Value.DbType), item.Value.Value);
}
dic[item.Key].TableName = item.Value.TableName;
dic[item.Key].ColumnName = item.Value.ColumnName;
}
return(dic);
}
else
{
return(new StatementParameterCollection());
}
}
/// <summary>
/// 更新时间戳
/// </summary>
public void UpdateDataChangeLastTime(long id)
{
var parameters = new StatementParameterCollection();
parameters.AddInParameter("@ProductId", DbType.Int64, id);
parameters.AddInParameter("@DataChange_LastTime", DbType.DateTime, DBNull.Value);
try
{
DB.ExecSp("spA_Product_u", parameters);
}
catch (Exception ex)
{
throw new DalException("调用 ProductDal 时，访问 UpdateDataChangeLastTime 时出错", ex);
}
}
public SysUser GetUser(string username, string password)
{
try
{
string sql = @"select top 1 * from SysUser(nolock)
where UserName=@username and Password=@password";
StatementParameterCollection parameters = new StatementParameterCollection();
parameters.AddInParameter("@username", System.Data.DbType.AnsiString, username);
parameters.AddInParameter("@password", System.Data.DbType.AnsiString, password);
return(baseDao.SelectFirst <SysUser>(sql, parameters));
}
catch (Exception ex)
{
throw new DalException("调用ActivityDirectRulesDao时，访问GetAll时出错", ex);
}
}
/// <summary>
/// 更新Product 实体
/// </summary>
/// <param name="entity">ProductModel对象</param>
public void UpdateProduct(ProductEntity entity)
{
var parameters = new StatementParameterCollection();
parameters.AddInParameter("@ProductId", DbType.Int64, entity.ProductId);
parameters.AddInParameter("@ProductName", DbType.String, entity.ProductName);
parameters.AddInParameter("@CreateTime", DbType.DateTime, entity.CreateTime);
parameters.AddInParameter("@UpdateTime", DbType.DateTime, entity.UpdateTime);
try
{
DB.ExecSp("spA_Product_u", parameters);
}
catch (Exception ex)
{
throw new DalException("调用 ProductDal 时，访问 UpdateProduct 时出错", ex);
}
}
/// <summary>
/// 更新User 实体
/// </summary>
/// <param name="entity">UserModel对象</param>
public void UpdateUser(UserEntity entity)
{
var parameters = new StatementParameterCollection();
parameters.AddInParameter("@UserId", DbType.Int64, entity.UserId);
parameters.AddInParameter("@UserName", DbType.String, entity.UserName);
parameters.AddInParameter("@CreateTime", DbType.DateTime, entity.CreateTime);
parameters.AddInParameter("@UpdateTime", DbType.DateTime, entity.UpdateTime);
try
{
DB.ExecSp("spA_User_u", parameters);
}
catch (Exception ex)
{
throw new DalException("调用 UserDal 时，访问 UpdateUser 时出错", ex);
}
}
/// <summary>
/// 更新User 实体
/// </summary>
/// <param name="entity">UserModel对象</param>
public void UpdateUser(UserEntity entity)
{
var parameters = new StatementParameterCollection();
parameters.AddInParameter("@UserId", DbType.Int64, entity.UserId);
parameters.AddInParameter("@UserName", DbType.String, entity.UserName);
parameters.AddInParameter("@CreateTime", DbType.DateTime, entity.CreateTime);
parameters.AddInParameter("@UpdateTime", DbType.DateTime, entity.UpdateTime);
try
{
DB.ExecSp("spA_User_u", parameters);
}
catch (Exception ex)
{
throw new DalException("调用 UserDal 时，访问 UpdateUser 时出错", ex);
}
}
/// <summary>
/// 向数据库中插入 User 实体
/// </summary>
/// <param name="entity">UserModel对象</param>
/// <returns>自增主键</returns>
public long InsertUser(UserEntity entity)
{
var parameters = new StatementParameterCollection();
parameters.AddOutParameter("@UserId", DbType.Int64, 8);
parameters.AddInParameter("@UserName", DbType.String, entity.UserName);
parameters.AddInParameter("@CreateTime", DbType.DateTime, entity.CreateTime);
parameters.AddInParameter("@UpdateTime", DbType.DateTime, entity.UpdateTime);
try
{
DB.ExecSp("spA_User_i", parameters);
return Convert.ToInt64(parameters["@UserId"].Value);
}
catch (Exception ex)
{
throw new DalException("调用 UserDal 时，访问 InsertUser 时出错", ex);
}
}
/// <summary>
/// 更新Product 实体
/// </summary>
/// <param name="entity">ProductModel对象</param>
public void UpdateProduct(ProductEntity entity)
{
var parameters = new StatementParameterCollection();
parameters.AddInParameter("@ProductId", DbType.Int64, entity.ProductId);
parameters.AddInParameter("@ProductName", DbType.String, entity.ProductName);
parameters.AddInParameter("@CreateTime", DbType.DateTime, entity.CreateTime);
parameters.AddInParameter("@UpdateTime", DbType.DateTime, entity.UpdateTime);
try
{
DB.ExecSp("spA_Product_u", parameters);
}
catch (Exception ex)
{
throw new DalException("调用 ProductDal 时，访问 UpdateProduct 时出错", ex);
}
}
/// <summary>
/// 向数据库中插入 Product 实体
/// </summary>
/// <param name="entity">ProductModel对象</param>
/// <returns>自增主键</returns>
public long InsertProduct(ProductEntity entity)
{
var parameters = new StatementParameterCollection();
parameters.AddOutParameter("@ProductId", DbType.Int64, 8);
parameters.AddInParameter("@ProductName", DbType.String, entity.ProductName);
parameters.AddInParameter("@CreateTime", DbType.DateTime, entity.CreateTime);
parameters.AddInParameter("@UpdateTime", DbType.DateTime, entity.UpdateTime);
try
{
DB.ExecSp("spA_Product_i", parameters);
return(Convert.ToInt64(parameters["@ProductId"].Value));
}
catch (Exception ex)
{
throw new DalException("调用 ProductDal 时，访问 InsertProduct 时出错", ex);
}
}
/// <summary>
/// 删除SysAgentInfo
/// </summary>
/// <param name="sysAgentInfo">SysAgentInfo实体对象</param>
/// <returns>状态代码</returns>
public int Delete(SysAgentInfo sysAgentInfo)
{
try
{
string sql = @"UPdate [SysUser] set [isdelete]=1 where Id=@uid;
Update [SysAgentInfo] set [isdelete]=1 where Id=@id";
StatementParameterCollection parameters = new StatementParameterCollection();
parameters.AddInParameter("@uid", DbType.Int64, sysAgentInfo.UserId);
parameters.AddInParameter("@id", DbType.Int64, sysAgentInfo.Id);
Object result = baseDao.ExecNonQuery(sql, parameters);
int iReturn = Convert.ToInt32(result);
return(iReturn);
}
catch (Exception ex)
{
throw new DalException("调用SysAgentInfo时，访问Delete时出错", ex);
}
}
public SysOrderInfo GetOrderByNumber(string ordernumber)
{
try
{
string sql = @"Select TOP 1 * from SysOrderInfo with (nolock) where Isdelete=0 and OrderNo=@OrderNo ";
StatementParameterCollection parameters = new StatementParameterCollection();
parameters.AddInParameter("@OrderNo", DbType.AnsiString, ordernumber);
return(baseDao.SelectFirst <SysOrderInfo>(sql, parameters));
}
catch (Exception ex)
{
throw new DalException("调用ActivityDirectRules时，访问Update时出错", ex);
}
}
/// <summary>
/// 根据自增主键删除数据库中 Order 实体
/// </summary>
/// <param name="primaryKey">自增主键</param>
public void DeleteOrderByPk(long primaryKey)
{
var parameters = new StatementParameterCollection();
parameters.AddInParameter("@OrderId", DbType.Int64, primaryKey);
try
{
DB.ExecSp("spA_Order_d", parameters);
}
catch (Exception ex)
{
throw new DalException("调用 OrderDal 时，访问 DeleteOrderByPk 时出错", ex);
}
}
/// <summary>
/// 根据自增主键删除数据库中 Product 实体
/// </summary>
/// <param name="primaryKey">自增主键</param>
public void DeleteProductByPk(long primaryKey)
{
var parameters = new StatementParameterCollection();
parameters.AddInParameter("@ProductId", DbType.Int64, primaryKey);
try
{
DB.ExecSp("spA_Product_d", parameters);
}
catch (Exception ex)
{
throw new DalException("调用 ProductDal 时，访问 DeleteProductByPk 时出错", ex);
}
}
public IList <SysLogisticsInfo> GetLogisticsInfoList(string single)
{
try
{
string sql = @"select * from [SysLogisticsInfo] (nolock)
where [IsDelete]=0 and [LogisticsSingle]=@single order by [UpdateDate] desc";
StatementParameterCollection parameters = new StatementParameterCollection();
parameters.AddInParameter("@single", DbType.AnsiString, single);
return(baseDao.SelectList <SysLogisticsInfo>(sql, parameters));
}
catch (Exception)
{
throw;
}
}
public SysAddresserInfo GetByOrderId(long id)
{
try
{
string sql = @"select top 1 * from [SysAddresserInfo](nolock)
where [IsDelete]=0 and [OrderId]=@orderId";
StatementParameterCollection parameters = new StatementParameterCollection();
parameters.AddInParameter("@orderId", DbType.Int64, id);
return(baseDao.SelectFirst <SysAddresserInfo>(sql, parameters));
}
catch (Exception ex)
{
throw new DalException("调用ActivityDirectRulesDao时，访问FindByPk时出错", ex);
}
}
public static StatementParameterCollection ConvertStatement(Dictionary <string, CustomerParam> paras)
{
if (paras != null && paras.Count > 0)
{
StatementParameterCollection dic = new StatementParameterCollection();
foreach (KeyValuePair <string, CustomerParam> item in paras)
{
dic.AddInParameter(item.Key, LinqEnumHelper.IntToEnum <DbType>(item.Value.DbType), item.Value.Value);
}
return(dic);
}
else
{
return(new StatementParameterCollection());
}
}
/// <summary>
/// 将 List{1,2,3},转换成 @p0,@p1,@p2 目前只支持int long string, 且string默认是AnsiString，请参考 ansiString参数
/// </summary>
/// <param name="ie">集合</param>
/// <param name="prex">前缀，默认p</param>
/// <param name="ps">参数集合</param>
/// <param name="ansiString">是否是AnsiString(对应于Varchar)，默认是true，如果是NVarchar，传入False</param>
/// <returns></returns>
public static String NormalizeInParam(IEnumerable ie, StatementParameterCollection ps, String prex = "p", Boolean ansiString = true)
{
if (ie == null)
{
throw new DalException("The list is null, you should pass in something for DAL to process!");
}
var enumerator = ie.GetEnumerator();
StringBuilder sb = new StringBuilder();
Int32 index = 0;
while (enumerator.MoveNext())
{
String pName = String.Format("@{0}{1}", prex, index++);
sb.AppendFormat(pName + ",");
DbType dbType;
Type type = enumerator.Current.GetType();
if (type == typeof(Int32))
{
dbType = DbType.Int32;
}
else if (type == typeof(Int64))
{
dbType = DbType.Int64;
}
else if (type == typeof(String))
{
dbType = ansiString ? DbType.AnsiString : DbType.String;
}
else
{
throw new NotSupportedException(String.Format("Type {0} not supported, only support int , long ,string!", type));
}
ps.AddInParameter(pName, dbType, enumerator.Current);
}
if (sb.Length < 1)
{
throw new DalException("The length of the list is zero, you should pass in something for DAL to process!");
}
return(sb.ToString().TrimEnd(','));
}
/// <summary>
/// 向数据库中插入 Order 实体
/// </summary>
/// <param name="entity">OrderModel对象</param>
/// <returns>自增主键</returns>
public long InsertOrder(OrderEntity entity)
{
var parameters = new StatementParameterCollection();
parameters.AddOutParameter("@OrderId", DbType.Int64, 8);
parameters.AddInParameter("@UserId", DbType.Int64, entity.UserId);
parameters.AddInParameter("@Name", DbType.String, entity.Name);
parameters.AddInParameter("@Price", DbType.Decimal, entity.Price);
parameters.AddInParameter("@ProductId", DbType.Int64, entity.ProductId);
parameters.AddInParameter("@CreateTime", DbType.DateTime, entity.CreateTime);
parameters.AddInParameter("@UpdateTime", DbType.DateTime, entity.UpdateTime);
try
{
DB.ExecSp("spA_Order_i", parameters);
return(Convert.ToInt64(parameters["@OrderId"].Value));
}
catch (Exception ex)
{
throw new DalException("调用 OrderDal 时，访问 InsertOrder 时出错", ex);
}
}
/// <summary>
/// 更新时间戳
/// </summary>
public void UpdateDataChangeLastTime(long id)
{
var parameters = new StatementParameterCollection();
parameters.AddInParameter("@OrderId", DbType.Int64, id);
parameters.AddInParameter("@DataChange_LastTime", DbType.DateTime, DBNull.Value);
try
{
DB.ExecSp("spA_Order_u", parameters);
}
catch (Exception ex)
{
throw new DalException("调用 OrderDal 时，访问 UpdateDataChangeLastTime 时出错", ex);
}
}
public string AddOrderInfo(string username, SysOrderInfo orderinfo, SysAddresserInfo addresserinfo,
SysReceiverInfo receiverinfo, ref int status)
{
var param = new StatementParameterCollection();
param.AddInParameter("@OrderNo", DbType.AnsiString, orderinfo.OrderNo);
param.AddInParameter("@ShipperName", DbType.String, orderinfo.ShipperName);
param.AddInParameter("@ShipperPhone", DbType.AnsiString, orderinfo.ShipperPhone);
param.AddInParameter("@UserName", DbType.AnsiString, username);
param.AddInParameter("@PickupNumber", DbType.Int32, orderinfo.PickupNumber);
param.AddInParameter("@Status", DbType.Int32, orderinfo.Status);
param.AddInParameter("@LogisticsSingle", DbType.AnsiString, addresserinfo.LogisticsSingle);
param.AddInParameter("@RussiaCityId", DbType.Int64, addresserinfo.RussiaCityId);
param.AddInParameter("@RussiaAddress", DbType.String, addresserinfo.RussiaAddress);
param.AddInParameter("@CargoNumber", DbType.Int32, addresserinfo.CargoNumber);
param.AddInParameter("@PickupDate", DbType.DateTime, addresserinfo.PickupDate);
param.AddInParameter("@PickupWay", DbType.Int32, addresserinfo.PickupWay);
param.AddInParameter("@GoodsType", DbType.Int32, addresserinfo.GoodsType);
param.AddInParameter("@TransportationWay", DbType.AnsiString, addresserinfo.TransportationWay);
param.AddInParameter("@ProtectPrice", DbType.Decimal, addresserinfo.ProtectPrice);
param.AddInParameter("@PolicyFee", DbType.Decimal, addresserinfo.PolicyFee);
param.AddInParameter("@GoodsWeight", DbType.Decimal, addresserinfo.GoodsWeight);
param.AddInParameter("@BoxLong", DbType.Decimal, addresserinfo.BoxLong);
param.AddInParameter("@BoxWidth", DbType.Decimal, addresserinfo.BoxWidth);
param.AddInParameter("@BoxHeight", DbType.Decimal, addresserinfo.BoxHeight);
param.AddInParameter("@ParcelSingle", DbType.AnsiString, receiverinfo.ParcelSingle);
param.AddInParameter("@ChinaCityId", DbType.Int64, receiverinfo.ChinaCityId);
param.AddInParameter("@ChinaAddress", DbType.String, receiverinfo.ChinaAddress);
param.AddInParameter("@ReceiverName", DbType.String, receiverinfo.ReceiverName);
param.AddInParameter("@ReceiverPhone", DbType.AnsiString, receiverinfo.ReceiverPhone);
param.AddInParameter("@PackagingWay", DbType.Int32, receiverinfo.PackagingWay);
param.AddInParameter("@ExpressWay", DbType.Int32, receiverinfo.ExpressWay);
param.AddInParameter("@GoodsDesc", DbType.String, receiverinfo.GoodsDesc);
param.AddInParameter("@ParcelWeight", DbType.Decimal, receiverinfo.ParcelWeight);
param.AddInParameter("@ChinaCourierNumber", DbType.AnsiString, receiverinfo.ChinaCourierNumber);
param.AddInParameter("@Desc", DbType.String, receiverinfo.Desc);
param.AddOutParameter("@result", DbType.String, 100);
param.AddParameter("@message", DbType.Int32, 0, 32, ParameterDirection.ReturnValue);
var s = baseDao.VisitDataReaderBySp <object>("CreateOrder_Proc", param, x =>
{
return(x);
});
status = Convert.ToInt32(param["@message"].Value);
return(param[31].Value.ToString());
}