/// <summary>
///
/// </summary>
static public void UpdateWaterUser(int id, string newName, string newRemark, int waterUserTypeID, bool hasParent,
int parentWaterUserID, int waterUserLevelID)
{
string s = @"update tblWaterUser set
waterUserName = @name,
Remark = @remark,
waterUserTypeID = @waterUserTypeID,
parentWaterUserid = @parentWaterUserID,
waterUserLevelID = @waterUserLevelID
where WaterUserID = @id";
SqlCommand cmd = new SqlCommand(s);
DBI.AddSqlParameter(cmd, "id", id);
DBI.AddSqlParameter(cmd, "name", newName);
DBI.AddSqlParameter(cmd, "remark", newRemark);
DBI.AddSqlParameter(cmd, "waterUserTypeID", waterUserTypeID);
// TODO:
//
if (hasParent)
{
DBI.AddSqlParameter(cmd, "parentWaterUserID", parentWaterUserID);
}
else
{
DBI.AddSqlParameter(cmd, "parentWaterUserID", DBNull.Value);
}
DBI.AddSqlParameter(cmd, "waterUserLevelID", waterUserLevelID);
//DBI.AddSqlParameter(cmd, "", );
DBI.GetDBI().ExecuteScalar(cmd);
}
/// <summary>
///
/// </summary>
/// <param name="name"></param>
static public void InsertWaterUser(string name, string remark, int waterUserTypeID,
bool hasParentWaterUser, int parentWaterUserID, int waterUserLevelID)
{
string s = @"insert into tblWaterUser(waterUserName, Remark, waterUserTypeID, parentWaterUserID, waterUserLevelID)
values(@name, @remark, @waterUserTypeID, @parentWaterUserID, @waterUserLevelID)";
SqlCommand cmd = new SqlCommand(s);
DBI.AddSqlParameter(cmd, "name", name);
DBI.AddSqlParameter(cmd, "remark", remark);
DBI.AddSqlParameter(cmd, "waterUserTypeID", waterUserTypeID);
// TODO:
//
if (hasParentWaterUser)
{
DBI.AddSqlParameter(cmd, "parentWaterUserID", parentWaterUserID);
}
else
{
DBI.AddSqlParameter(cmd, "parentWaterUserID", DBNull.Value);
}
DBI.AddSqlParameter(cmd, "waterUserLevelID", waterUserLevelID);
DBI.GetDBI().ExecuteScalar(cmd);
int waterUserID = GetWaterUserID(name);
}
/// <summary>
///
/// </summary>
/// <param name="userName"></param>
/// <param name="pwd"></param>
/// <returns></returns>
static public bool CanLogin(string userName, string password, out int userID, out int waterUserID)
{
userID = 0;
waterUserID = 0;
string s = "select * from tb_user where name = @n and password = @p";
SqlCommand cmd = new SqlCommand(s);
DBI.AddSqlParameter(cmd, "n", userName);
DBI.AddSqlParameter(cmd, "p", password);
bool isLogin = false;
DataTable tbl = DBI.GetDBI().ExecuteDataTable(cmd);
if (tbl.Rows.Count > 0)
{
DataRow row = tbl.Rows[0];
userID = Convert.ToInt32(row["userID"]);
waterUserID = Convert.ToInt32(row["waterUserID"]);
isLogin = true;
}
return(isLogin);
}
/// <summary>
///
/// </summary>
/// <param name="newWaterUserName"></param>
/// <param name="p"></param>
public static bool ExistWaterUserName(string newWaterUserName, int ignoreID)
{
string s = "select count(*) from tblWaterUser " +
"where waterUsername = @waterUserName and waterUserID <> @ignoreID and deleted = 0";
SqlCommand cmd = new SqlCommand(s);
DBI.AddSqlParameter(cmd, "waterUserName", newWaterUserName);
DBI.AddSqlParameter(cmd, "ignoreID", ignoreID);
object obj = DBI.GetDBI().ExecuteScalar(cmd);
int count = Convert.ToInt32(obj);
return(count > 0);
}
/// <summary>
///
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
static public int GetUserID(string name)
{
string s = "select userID from tb_user where name = @name";
SqlCommand cmd = new SqlCommand(s);
DBI.AddSqlParameter(cmd, "name", name);
object obj = DBI.GetDBI().ExecuteScalar(cmd);
if (obj != null && obj != DBNull.Value)
{
return(Convert.ToInt32(obj));
}
else
{
return(0);
}
}
/// <summary>
///
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
static public int GetWaterUserID(string name)
{
string s = "select wateruserID from tblWaterUser where wateruserName = @n";
SqlCommand cmd = new SqlCommand(s);
DBI.AddSqlParameter(cmd, "n", name);
object obj = DBI.GetDBI().ExecuteScalar(cmd);
if (obj == null || obj == DBNull.Value)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
/// <summary>
///
/// </summary>
/// <param name="userID"></param>
/// <param name="name"></param>
/// <param name="pwd"></param>
/// <param name="waterUserID"></param>
static public void UpdateUser(int userID, string name, string pwd, int waterUserID,
int role, bool allowEdit, string editPassword)
{
string s = string.Format(
"update tb_User set [Name] = @name, [Password] = @pwd, " +
"[WaterUserID] = @waterUserID, Role = @role, AllowEdit = @allowEdit, " +
"editPassword = @editPassword " +
"where userid = " + userID);
SqlCommand cmd = new SqlCommand(s);
DBI.AddSqlParameter(cmd, "name", name);
DBI.AddSqlParameter(cmd, "pwd", pwd);
DBI.AddSqlParameter(cmd, "waterUserID", waterUserID);
DBI.AddSqlParameter(cmd, "role", role);
DBI.AddSqlParameter(cmd, "allowEdit", allowEdit);
DBI.AddSqlParameter(cmd, "editPassword", editPassword);
DBI.GetDBI().ExecuteScalar(cmd);
}
/// <summary>
/// add user return id
/// </summary>
/// <param name="name"></param>
/// <param name="pwd"></param>
/// <param name="WaterUserID"></param>
/// <returns>0 - fail, otherwise - success</returns>
static public int AddUser(string name, string pwd, int WaterUserID, int role, bool allowEdit, string editPassword)
{
//string s = string.Format("INSERT INTO tb_User( [Name], [Password], [role_id], [WaterUserID]) " +
// " VALUES(@name, @pwd, 1, @waterUserID)");
string s = string.Format("INSERT INTO tb_User( [Name], [Password], [WaterUserID], role, allowEdit, editPassword) " +
" VALUES(@name, @pwd, @waterUserID, @role, @allowEdit, @editPassword)");
SqlCommand cmd = new SqlCommand(s);
DBI.AddSqlParameter(cmd, "name", name);
DBI.AddSqlParameter(cmd, "pwd", pwd);
DBI.AddSqlParameter(cmd, "waterUserID", WaterUserID);
DBI.AddSqlParameter(cmd, "role", role);
DBI.AddSqlParameter(cmd, "allowEdit", allowEdit);
DBI.AddSqlParameter(cmd, "editPassword", editPassword);
DBI.GetDBI().ExecuteScalar(cmd);
return(GetUserID(name));
}