using IOM_cs.irisDb.model;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Windows.Forms;
namespace IOM_cs.irisDb.service.mySqlImpl
{
public class UserService_MysqlImpl : IUserService
{
/// <summary>
/// user新增用户
/// </summary>
public int AddUser(User user)
{
int re = -1;
MySqlTransaction tx = null;
try
{
tx = _MySqlHelper.mySqlConnection.BeginTransaction();
string sQry =
"INSERT INTO sys_user (id,user_id,name,police_id,user_number,password,user_type,creat_time,IsAdmin,IsSynch,SynchMessage,active)" +
"values (@var1,@var2,@var3,@var4,@var5,@var6, @var7, @var8, @var9, @var10, @var11, @var12)";
MySqlCommand aCommand = new MySqlCommand(sQry, _MySqlHelper.mySqlConnection);
aCommand.CommandTimeout = 0;
//绑定事务
aCommand.Transaction = tx;
aCommand.Parameters.AddWithValue("@var2", user.UserId);
aCommand.Parameters.AddWithValue("@var3", user.Name);
aCommand.Parameters.AddWithValue("@var4", user.PoliceId);
aCommand.Parameters.AddWithValue("@var5", user.UserNumber);
aCommand.Parameters.AddWithValue("@var6", user.Password);
aCommand.Parameters.AddWithValue("@var7", user.UserType);
aCommand.Parameters.AddWithValue("@var8", user.CreatTime);
aCommand.Parameters.AddWithValue("@var9", user.IsAdmin);
aCommand.Parameters.AddWithValue("@var10", user.IsSynch);
aCommand.Parameters.AddWithValue("@var11", user.SynchMessage);
aCommand.Parameters.AddWithValue("@var12", user.Active);
aCommand.ExecuteNonQuery();
tx.Commit();
aCommand.Dispose();
re = 0;
}
catch (MySqlException ex)
{
tx.Rollback();
re = -1;
if (_MySqlHelper.mySqlConnection.State == ConnectionState.Closed)
{
MessageBox.Show("数据库连接断开，请重启软件！");
return -1;
}
System.Windows.Forms.MessageBox.Show("EXCEPTION: " + ex.Message);
}
finally
{
tx.Dispose();
}
return re;
}
/// <summary>
/// 编辑
/// </summary>
/// <returns></returns>
public int EditUser(Int64 id, String userId, String name, String policeId, String userNumber)
{
int iRetval = 0;
try
{
// sys_person表
string sQry = "UPDATE sys_user set user_id='" + userId + "',name='" + name +
"',police_id='" + policeId + "', user_number = '" + userNumber + "' WHERE id=" + id;
MySqlCommand aCommand = new MySqlCommand(sQry, _MySqlHelper.mySqlConnection);
aCommand.CommandTimeout = 0;
aCommand.ExecuteNonQuery();
aCommand.Dispose();
}
catch (SqlException exSqlDb)
{
iRetval = exSqlDb.ErrorCode;
if (_MySqlHelper.mySqlConnection.State == ConnectionState.Closed)
{
MessageBox.Show("数据库连接断开，请重启软件！");
return -1;
}
MessageBox.Show("Update EXCEPTION: " + exSqlDb.Message);
}
catch (Exception ex)
{
MessageBox.Show("Update EXCEPTION: " + ex.Message);
iRetval = -1;
}
return iRetval;
}
public int editPassword(Int64 userId, String password)
{
int iRetval = 0;
try
{
// sys_person表
string sQry = "UPDATE sys_user set password='" + password +
"' WHERE id=" + userId;
MySqlCommand aCommand = new MySqlCommand(sQry, _MySqlHelper.mySqlConnection);
aCommand.CommandTimeout = 0;
iRetval = aCommand.ExecuteNonQuery();
aCommand.Dispose();
}
catch (SqlException exSqlDb)
{
iRetval = exSqlDb.ErrorCode;
MessageBox.Show("Update EXCEPTION: " + exSqlDb.Message);
}
catch (Exception ex)
{
MessageBox.Show("Update EXCEPTION: " + ex.Message);
iRetval = -1;
}
return iRetval;
}
/// <summary>
/// 获取同账号用户
/// </summary>
/// <returns></returns>
public int GetUserCountByUserId(String user_id)
{
int count = 0;
try
{
MySqlDataReader aReader = null;
string sQry = "SELECT COUNT(*) FROM sys_user where active=1 and user_id = '" + user_id + "'";
MySqlCommand cmd = new MySqlCommand(sQry, _MySqlHelper.mySqlConnection);
cmd.CommandTimeout = 0;
aReader = cmd.ExecuteReader(CommandBehavior.Default);
if (aReader.Read())
{
count = aReader.GetInt32(0);
}
aReader.Close();
}
catch (SqlException s)
{
MessageBox.Show(s.Message.ToString());
}
return count;
}
/// <summary>
/// 保存编辑
/// </summary>
public int reSetPassword(Int64 id)
{
int iRetval = 0;
try
{
// sys_person表
string sQry = "UPDATE sys_user set password = '123456' WHERE id=" + id;
MySqlCommand aCommand = new MySqlCommand(sQry, _MySqlHelper.mySqlConnection);
aCommand.CommandTimeout = 0;
aCommand.ExecuteNonQuery();
aCommand.Dispose();
}
catch (SqlException exSqlDb)
{
iRetval = exSqlDb.ErrorCode;
if (_MySqlHelper.mySqlConnection.State == ConnectionState.Closed)
{
MessageBox.Show("数据库连接断开，请重启软件！");
return -1;
}
MessageBox.Show("Update EXCEPTION: " + exSqlDb.Message);
}
catch (Exception ex)
{
MessageBox.Show("Update EXCEPTION: " + ex.Message);
iRetval = -1;
}
return iRetval;
}
/// <summary>
/// 删除
/// </summary>
public int Delete(Int64 id)
{
int iRetval = 0;
try
{
// sys_person表
string sQry = "UPDATE sys_user set active=0 WHERE id=" + id;
MySqlCommand aCommand = new MySqlCommand(sQry, _MySqlHelper.mySqlConnection);
aCommand.CommandTimeout = 0;
aCommand.ExecuteNonQuery();
aCommand.Dispose();
}
catch (SqlException exSqlDb)
{
iRetval = exSqlDb.ErrorCode;
if (_MySqlHelper.mySqlConnection.State == ConnectionState.Closed)
{
MessageBox.Show("数据库连接断开，请重启软件！");
return -1;
}
MessageBox.Show("Delete EXCEPTION: " + exSqlDb.Message);
}
catch (Exception ex)
{
iRetval = -1;
MessageBox.Show("Delete EXCEPTION: " + ex.Message);
}
return iRetval;
}
/// <summary>
/// 分批获取用户基本信息
/// </summary>
/// <param name="baseNum"></param>每次获取人数
/// <param name="i"></param>本次获取第 baseNum*(i-1) 到第 baseNum*i 条
/// <returns></returns>
public DataTable GetUsers(int baseNum, int i)
{
String strSql = "select id, user_id, name, police_id, user_number," +
" creat_time,IsSynch " +
" from sys_user where active=1 order by creat_time asc ";
strSql += "limit " + baseNum * (i - 1) + "," + baseNum;
MySqlCommand aCommand = new MySqlCommand(strSql, _MySqlHelper.mySqlConnection);
aCommand.CommandTimeout = 0;
MySqlDataAdapter adapter = new MySqlDataAdapter();
adapter.SelectCommand = aCommand;
DataTable dt = new DataTable();
try
{
adapter.Fill(dt);
for (int k = 0; k < dt.Rows.Count; k++)
{
if (dt.Rows[k][6].ToString() == "0")
dt.Rows[k][6] = "同步成功";
else
dt.Rows[k][6] = "未同步";
}
}
catch (SqlException exSqlDb)
{
if (_MySqlHelper.mySqlConnection.State == ConnectionState.Closed)
{
MessageBox.Show("数据库连接断开，请重启软件！");
return dt;
}
MessageBox.Show("GetUsersEXCEPTION: " + exSqlDb.Message);
}
catch (Exception ex)
{
if (_MySqlHelper.mySqlConnection.State == ConnectionState.Closed)
{
MessageBox.Show("数据库连接断开，请重启软件！");
return dt;
}
MessageBox.Show("GetUsersEXCEPTION: " + ex.Message);
}
return dt;
}
/// <summary>
/// 获取总数
/// </summary>
/// <returns></returns>
public int GetCount()
{
int count = 0;
try
{
MySqlDataReader aReader = null;
string sQry = "SELECT COUNT(*) FROM sys_user where active=1";
MySqlCommand cmd = new MySqlCommand(sQry, _MySqlHelper.mySqlConnection);
cmd.CommandTimeout = 0;
aReader = cmd.ExecuteReader(CommandBehavior.Default);
if (aReader.Read())
{
count = aReader.GetInt32(0);
}
aReader.Close();
}
catch (SqlException s)
{
if (_MySqlHelper.mySqlConnection.State == ConnectionState.Closed)
{
MessageBox.Show("数据库连接断开，请重启软件！");
return count;
}
MessageBox.Show(s.Message.ToString());
}
return count;
}
public User login(User user)
{
try
{
string sQry = "SELECT id,user_id,name,password,user_number FROM sys_user WHERE user_id=@user_id and active = 1";
MySqlCommand cmd = new MySqlCommand(sQry, _MySqlHelper.mySqlConnection);
cmd.Parameters.Add
(
"@user_id",
MySqlDbType.VarChar,
20
).Value = user.UserId.Trim();
MySqlDataReader aReader = cmd.ExecuteReader(CommandBehavior.Default);
if (aReader.Read())
{
if (!aReader.IsDBNull(3))
{
if (aReader.GetString(3) == user.Password)
{
if (!aReader.IsDBNull(0))
{
user.Id = aReader.GetInt64(0);
}
if (!aReader.IsDBNull(2))
{
user.Name = aReader.GetString(2);
}
if (!aReader.IsDBNull(4))
{
user.UserNumber = aReader.GetString(4);
}
}
else
user = null;
}
}
else
{
user = null;
}
aReader.Close();
cmd.Dispose();
}
catch (Exception e)
{
LogHelper.WriteLog(MethodBase.GetCurrentMethod().DeclaringType, "登录catchError：" + e.Message);
user = null;
}
return user;
}
}
}
