/// <summary>
/// 更新用户登陆及基本信息
/// </summary>
/// <returns>The update.</returns>
/// <param name="model">Model.</param>
/// <param name="info">Info.</param>
public int Update(Model.SysUser model, Model.SysUserInfo info)
{
List <string> li = new List <string>();
StringBuilder strSql = new StringBuilder();
strSql.Append(" Update SysUser set LoginName='" + model.LoginName + "',RoleID=" + model.RoleID + " where UserID= " + model.UserID + "");
strSql.Append(" Update SysUserInfo set Gender=" + (info.Gender == true ? "1" : "0") + ",Name='" + info.Name + "',Telephone='" + info.Telephone + "',Department='" + info.Department + "' where UserID= " + info.UserID);
li.Add(strSql.ToString());
return(DbHelperSQL.ExecuteSqlTran(li));
}
/// <summary>
/// 更新用户登陆及基本信息
/// </summary>
/// <returns>The update.</returns>
public int Update(Model.VM_SysUser model)
{
Model.SysUser m = new Model.SysUser();
m.UserID = model.UserID;
m.LoginName = model.LoginName;
m.RoleID = model.RoleID;
Model.SysUserInfo ui = new Model.SysUserInfo();
ui.Gender = model.Gender;
ui.Department = model.Department;
ui.Name = model.Name;
ui.Telephone = model.Telephone;
ui.UserID = model.UserID;
return(dal.Update(m, ui));
}
/// <summary>
/// 新增系统用户
/// </summary>
public int Insert(Model.VM_SysUser model)
{
Model.SysUser m = new Model.SysUser();
m.LoginName = model.LoginName;
m.LoginPassword = "******";
m.ParentID = model.ParentID;
m.RoleID = model.RoleID;
m.State = true;
Model.SysUserInfo ui = new Model.SysUserInfo();
ui.Gender = model.Gender;
ui.Department = model.Department;
ui.Name = model.Name;
ui.RegisterDate = DateTime.Now;
ui.Telephone = model.Telephone;
return(dal.Insert(m, ui));
}
public void TestTran()
{
var insertModel = new Model.SysUserInfo()
{
uAddTime = DateTime.Now,
uDepId = 222,
uEmail = "@@#[email protected]",
uGender = false,
uIsDel = true,
uLoginName = "z1z16q",
uPwd = "jsdjf1iu",
uRemark = "cces12i1ff6fdx21"
};
//需要加tran
var res12 = _baseDAL.Insert(insertModel, true);
var res22 = _sysUserInfoDAL.Insert(insertModel, false);
var res33 = _sysUserInfoDAL.Delete(new Model.SysUserInfo()
{
uId = (int)res12
});
var roleModel = new Model.SysRole()
{
rDepId = 1229,
rName = "421d44",
rAddTime = DateTime.Now
};
var res44 = _sysRoleDAL.Insert(roleModel, true);
var res45 = _sysRoleDAL.Delete(new Model.SysRole()
{
rId = res44
});
Console.WriteLine(res12);
Console.WriteLine(res22);
Console.WriteLine(res33);
Console.WriteLine(res44);
Console.WriteLine(res45);
var i = 0;
insertModel.uDepId = 1 / i; //模拟出错
}
/// <summary>
/// 新增系统用户
/// </summary>
/// <returns>The add.</returns>
/// <param name="Model">Model.</param>
public int Insert(Model.SysUser Model, Model.SysUserInfo Info)
{
List <string> li = new List <string>();
StringBuilder strSql = new StringBuilder();
strSql.Append(" Declare @ID int; ");
strSql.Append(" set @ID=0; ");
strSql.Append(" if not exists(select LoginName from SysUser where LoginName ='" + Model.LoginName + "' ) begin ");
strSql.Append(" insert into SysUser(");
strSql.Append(" LoginName,LoginPassword,ParentID,RoleID,State)");
strSql.Append(" values (");
strSql.Append(" '" + Model.LoginName + "','" + DBUtility.DESEncrypt.Encrypt(Model.LoginPassword) + "'," + Model.ParentID + "," + Model.RoleID + "," + (Model.State == true?"1":"0") + ")");
strSql.Append(" ; set @ID=@@IDENTITY; ");
strSql.Append(" END;");
strSql.Append(" if @ID>0 begin ");
strSql.Append(" insert into SysUserInfo(");
strSql.Append(" UserID,Gender,Name,Telephone,Department,RegisterDate)");
strSql.Append(" values( @ID," + (Info.Gender == true ? "1" : "0") + ",'" + Info.Name + "','" + Info.Telephone + "','" + Info.Department + "','" + Info.RegisterDate + "');");
strSql.Append(" END;");
li.Add(strSql.ToString());
return(DbHelperSQL.ExecuteSqlTran(li));
}
/// <summary>
/// 展示调用
/// </summary>
public void Example()
{
#region Insert
var insertModel = new Model.SysUserInfo()
{
uAddTime = DateTime.Now,
uDepId = 2,
uEmail = "*****@*****.**",
uGender = false,
uId = 5,
uIsDel = true,
uLoginName = "zzq",
uPwd = "jsdjfiu",
uRemark = "ceshi1"
};
var res1 = _sysUserInfoBLL.Insert(insertModel, true);
insertModel.uGender = true;
insertModel.uEmail = "222@@@@qq.com";
insertModel.uLoginName = "lisi";
var res2 = _sysUserInfoBLL.Insert(insertModel, false);
Console.WriteLine(res1);
Console.WriteLine(res2);
var user_list = new List <SysUserInfo>
{
new SysUserInfo()
{
uId = 11, uAddTime = DateTime.Now, uDepId = 1, uLoginName = "3", uPwd = "1"
},
new SysUserInfo()
{
uId = 22, uAddTime = DateTime.Now, uDepId = 1, uLoginName = "1", uPwd = "2"
},
new SysUserInfo()
{
uId = 33, uAddTime = DateTime.Now, uDepId = 1, uLoginName = "2", uPwd = "2"
}
};
_baseDAL.BulkCopyToInsert(user_list);
#endregion
#region Update
var updateModel = new Model.SysUserInfo()
{
uAddTime = DateTime.Now.AddYears(2),
uDepId = 21,
uEmail = "*****@*****.**",
uGender = false,
uId = 102,
uIsDel = true,
uLoginName = "zzq23",
uPwd = "123456",
uRemark = "nihao"
};
var res3 = _sysUserInfoBLL.Update(updateModel);
Console.WriteLine(res3);
var dataParams = new DataParameters();
dataParams.Add <SysUserInfo>(p => p.uId, 3);
dataParams.Add <SysUserInfo>(p => p.uLoginName, "cwjl");
dataParams.Add <SysUserInfo>(p => p.uEmail, "*****@*****.**");
dataParams.AddUpdate <SysUserInfo>(p => p.uEmail, "*****@*****.**");
var r1 = _sysUserInfoDAL.UpdatePart <SysUserInfo>(dataParams);
Console.WriteLine(r1);
#endregion
#region Delete
var isOk1 = _sysUserInfoBLL.Delete(new Model.SysUserInfo()
{
uId = 4
});
Console.WriteLine(isOk1);
#endregion
#region Statistics
var res7 = _sysUserInfoDAL.Count <SysUserInfo>(new { udepid = 2 });
Console.WriteLine(res7);
#endregion
#region Query
//匿名类查询
var list_0 = _baseDAL.QueryList <SysUserInfo>(new { uId = 2 });
//dataParamseters多功能的使用
dataParams.Clear();
dataParams.Add <SysUserInfo>(p => p.uLoginName, "zzq");
dataParams.Add <SysUserInfo>(p => p.uRemark, "良", ConditionOperator.Like);
var list_1 = _baseDAL.QueryList <SysUserInfo>(dataParams);
dataParams.Clear();
dataParams.Add <SysUserInfo>(p => p.uLoginName, "zzq");
dataParams.Add <SysUserInfo>(p => p.uLoginName, "zzq", ConditionOperator.NotEqual); //可以多个同字段条件
dataParams.Add <SysUserInfo>(p => p.uEmail, "123");
dataParams.Add <SysUserInfo>(p => p.uRemark, "良", ConditionOperator.Like);
dataParams.AddBetween <SysUserInfo>(p => p.uId, 1, 2000);
var list_2 = _baseDAL.QueryList <SysUserInfo>(dataParams, selectFields: "uloginname");
var dict = new Dictionary <string, object>
{
{ "uloginname", "1zzq" },
{ "uEmail", "*****@*****.**" },
{ "uRemark", "nv100" }
};
var list_1_1 = _baseDAL.QueryList <SysUserInfo>(dict, selectFields: "uloginname,uRemark");
//连表使用
var mainSql = "select u.*,dep.depName from sysUserInfo as u join sysDepartment as dep on dep.depId=u.uDepId";
dataParams.Clear();
dataParams.Add <SysUserInfo>(p => p.uId, 2, tablePrefix: "u");
dataParams.Add <SysUserInfo>(p => p.uRemark, "hi", ConditionOperator.Like, tablePrefix: "u");
dataParams.Add <SysDepartment>(p => p.depId, 6, tablePrefix: "dep");
var list_2_1 = _baseDAL.QueryJoinList <UserInfoWithDep>(mainSql, dataParams, "u.uId asc");
//分页
dataParams.Clear();
dataParams.Add <SysUserInfo>(p => p.uId, 5, ConditionOperator.GreaterThan, tablePrefix: "u");
//dataParams.Add<SysUserInfo>(p => p.uRemark, "hi", ConditionOperator.Like, tablePrefix: "u");
//dataParams.Add<SysDepartment>(p => p.depId, 2, tablePrefix: "dep");
var pcp_n = new PageParameters()
{
PageIndex = 1,
PageSize = 10,
Select = "u.*,dep.depName",
Order = "u.uId asc",
Table = @"sysUserInfo as u join sysDepartment as dep on dep.depId=u.uDepId",
WhereParam = dataParams
};
var pageData = _baseDAL.PageList <UserInfoWithDep>(pcp_n);
#endregion
#region 存储过程
dataParams.Clear();
dataParams.Add("@channelId", 2, DbType.Int32);
dataParams.Add("outstr", "未知错误", DbType.String, direction: ParameterDirection.Output);
_baseDAL.ExecProcedure("sp_test", dataParams);
var outstr = dataParams.GetParamVal <string>("outstr");
Console.WriteLine(outstr);
#endregion
#region 事务
_baseDAL.ExecTransaction(() =>
{
var insertModel2 = new Model.SysUserInfo()
{
uAddTime = DateTime.Now,
uDepId = 2,
uEmail = "*****@*****.**",
uGender = false,
uIsDel = true,
uLoginName = "zz1q",
uPwd = "jsdjf1iu",
uRemark = "ces12i1fffdx21"
};
//需要加tran
var res12 = _sysUserInfoDAL.Insert(insertModel2, true);
var res22 = _sysUserInfoDAL.Insert(insertModel2, true);
var res33 = _sysUserInfoDAL.Delete(new Model.SysUserInfo()
{
uId = (int)res12
});
var roleModel = new Model.SysRole()
{
rDepId = 1,
rName = "444",
rAddTime = DateTime.Now
};
var res44 = _sysRoleDAL.Insert(roleModel, true);
//var res45 = SysRoleDAL.Delete(new Model.SysRole() { rId = res44 });
Console.WriteLine(res12);
Console.WriteLine(res22);
Console.WriteLine(res33);
//var i = 0;
//insertModel2.uDepId = 1 / i; //模拟出错
});
#endregion
#region 事务后在添加
insertModel = new Model.SysUserInfo()
{
uAddTime = DateTime.Now,
uDepId = 2,
uEmail = "*****@*****.**",
uGender = false,
uIsDel = true,
uLoginName = "zzq",
uPwd = "jsdjfiu",
uRemark = "ceshi1"
};
var res21 = _sysUserInfoDAL.Insert(insertModel, true);
#endregion
#region 表别名
var s = _sysRoleBLL.QueryModel <Model.SysRole>(new { });
//SysUserInfoDAL.QueryModel("ffff=11"); //模拟出错,MiniProfiler过滤器中会记录Errored=true
Console.WriteLine(s.rName);
#endregion
}
