using IOM_cs.irisDb.model;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.Reflection;
using System.Windows.Forms;
namespace IOM_cs.irisDb.service.sqliteImpl
{
public class UserService_SqliteImpl : IUserService
{
/// <summary>
/// user新增用户
/// </summary>
public int AddUser(User user)
{
int re = -1;
SQLiteTransaction tx = null;
try
{
tx = _SqliteHelper.sqliteConnection.BeginTransaction();
string sQry =
"INSERT INTO sys_user (user_id,name,police_id,user_number,password,user_type,creat_time,IsAdmin,IsSynch,SynchMessage,active)" +
"values (@var0,@var1,@var2,@var3,@var4, @var5, @var6, @var7, @var8, @var9, @var10)";
SQLiteCommand aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
aCommand.CommandTimeout = 0;
//绑定事务
aCommand.Transaction = tx;
aCommand.Parameters.Add(new SQLiteParameter("@var0", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var1", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var2", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var3", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var4", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var5", DbType.Int32));
aCommand.Parameters.Add(new SQLiteParameter("@var6", DbType.DateTime));
aCommand.Parameters.Add(new SQLiteParameter("@var7", DbType.Int32));
aCommand.Parameters.Add(new SQLiteParameter("@var8", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var9", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var10", DbType.Int32));
aCommand.Parameters[0].Value = user.UserId;
aCommand.Parameters[1].Value = user.Name;
aCommand.Parameters[2].Value = user.PoliceId;
aCommand.Parameters[3].Value = user.UserNumber;
aCommand.Parameters[4].Value = user.Password;
aCommand.Parameters[5].Value = user.UserType;
aCommand.Parameters[6].Value = user.CreatTime;
aCommand.Parameters[7].Value = user.IsAdmin;
aCommand.Parameters[8].Value = user.IsSynch;
aCommand.Parameters[9].Value = user.SynchMessage;
aCommand.Parameters[10].Value = user.Active;
aCommand.ExecuteNonQuery();
tx.Commit();
aCommand.Dispose();
re = 0;
}
catch (SQLiteException ex)
{
tx.Rollback();
re = -1;
if (_SqliteHelper.sqliteConnection.State == ConnectionState.Closed)
{
MessageBox.Show("数据库连接断开，请重启软件！");
return -1;
}
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;
SQLiteCommand aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
aCommand.CommandTimeout = 0;
aCommand.ExecuteNonQuery();
aCommand.Dispose();
}
catch (SQLiteException exSqlDb)
{
iRetval = exSqlDb.ErrorCode;
if (_SqliteHelper.sqliteConnection.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;
SQLiteCommand aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
aCommand.CommandTimeout = 0;
iRetval = aCommand.ExecuteNonQuery();
aCommand.Dispose();
}
catch (SQLiteException 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
{
SQLiteDataReader aReader = null;
string sQry = "SELECT COUNT(*) FROM sys_user where active=1 and user_id = '" + user_id + "'";
SQLiteCommand cmd = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
cmd.CommandTimeout = 0;
aReader = cmd.ExecuteReader(CommandBehavior.Default);
if (aReader.Read())
{
count = aReader.GetInt32(0);
}
aReader.Close();
}
catch (SQLiteException 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;
SQLiteCommand aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
aCommand.CommandTimeout = 0;
aCommand.ExecuteNonQuery();
aCommand.Dispose();
}
catch (SQLiteException exSqlDb)
{
iRetval = exSqlDb.ErrorCode;
if (_SqliteHelper.sqliteConnection.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;
SQLiteCommand aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
aCommand.CommandTimeout = 0;
aCommand.ExecuteNonQuery();
aCommand.Dispose();
}
catch (SqlException exSqlDb)
{
iRetval = exSqlDb.ErrorCode;
if (_SqliteHelper.sqliteConnection.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;
SQLiteCommand aCommand = new SQLiteCommand(strSql, _SqliteHelper.sqliteConnection);
aCommand.CommandTimeout = 0;
SQLiteDataAdapter adapter = new SQLiteDataAdapter();
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 (SQLiteException exSqlDb)
{
if (_SqliteHelper.sqliteConnection.State == ConnectionState.Closed)
{
MessageBox.Show("数据库连接断开，请重启软件！");
return dt;
}
MessageBox.Show("GetUsersEXCEPTION: " + exSqlDb.Message);
}
catch (Exception ex)
{
if (_SqliteHelper.sqliteConnection.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
{
SQLiteDataReader aReader = null;
string sQry = "SELECT COUNT(*) FROM sys_user where active=1";
SQLiteCommand cmd = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
cmd.CommandTimeout = 0;
aReader = cmd.ExecuteReader(CommandBehavior.Default);
if (aReader.Read())
{
count = aReader.GetInt32(0);
}
aReader.Close();
}
catch (SQLiteException s)
{
if (_SqliteHelper.sqliteConnection.State == ConnectionState.Closed)
{
MessageBox.Show("数据库连接断开，请重启软件！");
return count;
}
MessageBox.Show(s.Message.ToString());
}
return count;
}
public User login(User user){
//User _user = new User();
try
{
string sQry = "SELECT id,user_id,name,password,user_number FROM sys_user WHERE user_id=@user_id and active = 1";
SQLiteCommand cdm = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
cdm.Parameters.Add
(
"@user_id",
DbType.AnsiString,
20
).Value = user.UserId.Trim();
SQLiteDataReader aReader = cdm.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();
cdm.Dispose();
}
catch (Exception e)
{
LogHelper.WriteLog(MethodBase.GetCurrentMethod().DeclaringType, "登录catchError：" + e.Message);
user=null;
}
return user;
}
}
}
