public int GetRecordByUserKey(string key, out STUser data, out string msg)
{
int ret = 0;
data = new STUser();
msg = null;
SqlConnection connect;
Log log = new Log(LogPath);
try
{
connect = new SqlConnection(ConnectionString);
connect.Open();
if (connect.State == ConnectionState.Open)
{
string query = "SELECT U.UserId,U.UserName,U.Email,U.Login,U.Password," +
"U.PasswordSalt,U.CreatedDate,U.LastModifiedDate,U.IsActivated,U.ActivatedDate,U.OwnerUserId," +
"U.Comments,Condition,U.Permission,U.PassValidDate,U.CntMissTry,U.LockTime,U.LastMissTime," +
"U.NewEmailKey,UI.UserName " +
"FROM dbo.Users U LEFT JOIN (SELECT UserId, UserName FROM dbo.Users) UI " +
"ON U.OwnerUserId=UI.UserId WHERE U.NewEmailKey=@1";
SqlCommand cmd = new SqlCommand(query, connect);
cmd.Parameters.Add(crp(SqlDbType.VarChar, "@1", key, false));
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
if (!read(reader, out data, out msg))
{
ret = -1;
}
}
}
reader.Dispose();
connect.Close();
}
else
{
return(1);
}
}
catch (Exception ex) { log.Write(LogType.Error, ex.Message); ret = -1; msg = ex.Message; }
return(ret);
}
public int Insert(STUser data, out string msg)
{
int ret = 0;
msg = null;
SqlConnection connect;
Log log = new Log(LogPath);
try
{
connect = new SqlConnection(ConnectionString);
connect.Open();
if (connect.State == ConnectionState.Open)
{
string guid = Guid.NewGuid().ToString();
string query = "INSERT INTO dbo.Users (UserId,UserName,Email,Login,CreatedDate,LastModifiedDate," +
"IsActivated,ActivatedDate,OwnerUserId,Comments,Condition,Permission,PassValidDate,NewEmailKey) " +
"VALUES (@1, @2, @3, @4, @5, @6, @7, @8, @9, @10, @11, @12, @13, @14)";
SqlCommand cmd = new SqlCommand(query, connect);
cmd.Parameters.Add(crp(SqlDbType.VarChar, "@1", guid, false));
cmd.Parameters.Add(crp(SqlDbType.VarChar, "@2", data.username, false));
cmd.Parameters.Add(crp(SqlDbType.VarChar, "@3", data.email, false));
cmd.Parameters.Add(crp(SqlDbType.VarChar, "@4", data.login, false));
cmd.Parameters.Add(crp(SqlDbType.DateTime, "@5", data.creationdate, false));
cmd.Parameters.Add(crp(SqlDbType.DateTime, "@6", data.modifieddate, false));
cmd.Parameters.Add(crp(SqlDbType.Bit, "@7", data.isactivated, true));
cmd.Parameters.Add(crp(SqlDbType.DateTime, "@8", data.activateddate, true));
cmd.Parameters.Add(crp(SqlDbType.VarChar, "@9", data.owneruserid, true));
cmd.Parameters.Add(crp(SqlDbType.VarChar, "@10", data.comments, true));
cmd.Parameters.Add(crp(SqlDbType.Int, "@11", data.condition, false));
cmd.Parameters.Add(crp(SqlDbType.Int, "@12", data.permission, false));
cmd.Parameters.Add(crp(SqlDbType.DateTime, "@13", data.passvaliddate, false));
cmd.Parameters.Add(crp(SqlDbType.VarChar, "@14", data.newemailkey, true));
SqlDataReader reader = cmd.ExecuteReader();
connect.Close();
}
else
{
return(1);
}
}
catch (Exception ex) { log.Write(LogType.Error, ex.Message); ret = -1; msg = ex.Message; }
return(ret);
}
public int Update(string userid, STUser data, out string msg)
{
int ret = 0;
msg = null;
SqlConnection connect;
Log log = new Log(LogPath);
try
{
connect = new SqlConnection(ConnectionString);
connect.Open();
if (connect.State == ConnectionState.Open)
{
string query = "UPDATE dbo.Users SET UserName=@1,Email=@2,Login=@3,LastModifiedDate=@4,Comments=@5," +
"Condition=@6,Permission=@7 WHERE UserId=@8";
SqlCommand cmd = new SqlCommand(query, connect);
cmd.Parameters.Add(crp(SqlDbType.VarChar, "@1", data.username, false));
cmd.Parameters.Add(crp(SqlDbType.VarChar, "@2", data.email, false));
cmd.Parameters.Add(crp(SqlDbType.VarChar, "@3", data.login, false));
cmd.Parameters.Add(crp(SqlDbType.DateTime, "@4", data.modifieddate, false));
cmd.Parameters.Add(crp(SqlDbType.VarChar, "@5", data.comments, true));
cmd.Parameters.Add(crp(SqlDbType.Int, "@6", data.condition, false));
cmd.Parameters.Add(crp(SqlDbType.Int, "@7", data.permission, false));
// cmd.Parameters.Add(crp(SqlDbType.DateTime, "@8", data.passvaliddate, false));
cmd.Parameters.Add(crp(SqlDbType.VarChar, "@8", userid, false));
SqlDataReader reader = cmd.ExecuteReader();
connect.Close();
}
else
{
return(1);
}
}
catch (Exception ex) { log.Write(LogType.Error, ex.Message); ret = -1; msg = ex.Message; }
return(ret);
}
private bool read(SqlDataReader reader, out STUser data, out string msg)
{
bool ret = true;
data = new STUser();
msg = null;
try
{
data.userid = reader.GetString(0);
data.username = reader.GetString(1);
data.email = reader.GetString(2);
data.login = reader.GetString(3);
if (!reader.IsDBNull(4))
{
data.password = reader.GetString(4);
}
else
{
data.password = null;
}
if (!reader.IsDBNull(5))
{
data.passwordsalt = reader.GetString(5);
}
else
{
data.passwordsalt = null;
}
data.creationdate = reader.GetDateTime(6);
data.modifieddate = reader.GetDateTime(7);
data.isactivated = reader.GetBoolean(8);
if (!reader.IsDBNull(9))
{
data.activateddate = reader.GetDateTime(9);
}
else
{
data.activateddate = null;
}
if (!reader.IsDBNull(10))
{
data.owneruserid = reader.GetString(10);
}
else
{
data.owneruserid = null;
}
if (!reader.IsDBNull(11))
{
data.comments = reader.GetString(11);
}
else
{
data.comments = null;
}
data.condition = reader.GetInt32(12);
data.permission = reader.GetInt32(13);
data.passvaliddate = reader.GetDateTime(14);
if (data.passvaliddate <= DateTime.Now)
{
data.oldpass = true;
}
else
{
data.oldpass = false;
}
if (!reader.IsDBNull(15))
{
data.cntmisstry = reader.GetInt32(15);
}
else
{
data.cntmisstry = 0;
}
if (!reader.IsDBNull(16))
{
data.locktime = reader.GetDateTime(16);
}
else
{
data.locktime = null;
}
if (data.locktime != null && data.locktime >= DateTime.Now)
{
data.islock = true;
}
else
{
data.islock = false;
}
if (!reader.IsDBNull(17))
{
data.lastmisstime = reader.GetDateTime(17);
}
else
{
data.lastmisstime = null;
}
if (!reader.IsDBNull(18))
{
data.newemailkey = reader.GetString(18);
}
else
{
data.newemailkey = null;
}
if (!reader.IsDBNull(19))
{
data.ownerusername = reader.GetString(19);
}
else
{
data.ownerusername = null;
}
}
catch (Exception ex) { msg = ex.Message; ret = false; }
return(ret);
}