GitBucket
Newer
using IOM_cs.irisDb.model;
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SQLite;
using System.Windows.Forms;
namespace IOM_cs.irisDb.service.sqliteImpl
{
public class UserIrisService_SqliteImpl:IUserIrisService
{
#region person
/// <summary>
/// 获取数据库中的注册人员总数
/// </summary>
/// <returns></returns>
public int GetCountOfPerson()
{
int count = 0;
try
{
SQLiteDataReader aReader = null;
string sQry = "SELECT COUNT(*) FROM sys_person where DELFLAG=0";
SQLiteCommand cmd = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
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 IsIDCardNoPresent(string sIDCardNo)
{
//加密
//sIDCardNo = CryptoHelper.Encrypt(sIDCardNo);
int mun = 0;
try
{
string sQry = "SELECT COUNT(*) FROM sys_person WHERE ID_CARD_NO=@IDCardNo and DELFLAG=0";
SQLiteCommand cmd = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
cmd.Parameters.Add
(
"@IDCardNo",
DbType.AnsiString,
100
).Value = sIDCardNo.Trim();
SQLiteDataReader aReader = cmd.ExecuteReader(CommandBehavior.Default);
if (aReader.Read())
{
if (!aReader.IsDBNull(0)) mun = aReader.GetInt16(0);
}
aReader.Close();
cmd.Dispose();
}
catch (SQLiteException e)
{
if (_SqliteHelper.sqliteConnection.State == ConnectionState.Closed)
{
MessageBox.Show("数据库连接断开，请重启软件！");
}
MessageBox.Show("IsIDCardNoPresentException:" + e.Message);
}
return mun;
}
/// <summary>
/// sys_person,sys_person_ext新增人员
/// </summary>
public int AddPerson(Person person, PersonExt personExt)
{
int re = -1;
SQLiteTransaction tx = null;
try
{
//sIDCardNo = CryptoHelper.Encrypt(sIDCardNo);//加密
tx = _SqliteHelper.sqliteConnection.BeginTransaction();
string sQry =
"INSERT INTO sys_person (ID,DELFLAG,CREATETIME,NAME,SEX,ID_CARD_NO,REMARKS,NATION,BIRTHDAY,CARD_TYPE)" +
"values (@var1,@var2,@var3,@var4,@var5,@var6, @var7, @var8, @var9, @var10)";
SQLiteCommand aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
aCommand.Transaction = tx; //绑定事务
aCommand.Parameters.Add(new SQLiteParameter("@var1", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var2", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var3", DbType.DateTime));
aCommand.Parameters.Add(new SQLiteParameter("@var4", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var5", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var6", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var7", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var8", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var9", DbType.Date));
aCommand.Parameters.Add(new SQLiteParameter("@var10", DbType.String));
aCommand.Parameters[0].Value = person.id;
aCommand.Parameters[1].Value = "0";
aCommand.Parameters[2].Value = DateTime.Now;
aCommand.Parameters[3].Value = person.name;
aCommand.Parameters[4].Value = person.sex;
aCommand.Parameters[5].Value = person.idCardNo;
aCommand.Parameters[6].Value = person.remarks;
aCommand.Parameters[7].Value = person.nation;
aCommand.Parameters[8].Value = person.birthday;
aCommand.Parameters[9].Value = person.cardType;
aCommand.ExecuteNonQuery();
aCommand.Dispose();
sQry =
"INSERT INTO sys_person_ext(ID, TARGET_ID, COLUMN_1, COLUMN_2, COLUMN_3, COLUMN_4, COLUMN_5, COLUMN_6, COLUMN_7)" +
"values (@var1,@var2,@var3,@var4,@var5,@var6, @var7, @var8, @var9)";
aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
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.String));
aCommand.Parameters.Add(new SQLiteParameter("@var6", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var7", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var8", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var9", DbType.String));
aCommand.Parameters[0].Value = personExt.id;
aCommand.Parameters[1].Value = personExt.targetId;
aCommand.Parameters[2].Value = personExt.column1;
aCommand.Parameters[3].Value = personExt.column2;
aCommand.Parameters[4].Value = personExt.column3;
aCommand.Parameters[5].Value = personExt.column4;
aCommand.Parameters[6].Value = personExt.column5;
aCommand.Parameters[7].Value = personExt.column6;
aCommand.Parameters[8].Value = personExt.column7;
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("AddPerson EXCEPTION: " + ex.Message);
}
finally
{
tx.Dispose();
}
return re;
}
/// <summary>
/// 删除人员，sys_person、iris_data、iris_data_coll物理删除
/// </summary>
public int DeletePerson(Int64 personId)
{
int iRetval = -1;
try
{
string sQry = "delete FROM sys_person WHERE ID=@Id";
SQLiteCommand aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
aCommand.Parameters.AddWithValue("@Id", personId);
iRetval = aCommand.ExecuteNonQuery();
aCommand.Dispose();
sQry = "DELETE FROM iris_data WHERE S_ID=@Id";
aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
aCommand.Parameters.AddWithValue("@Id", personId);
iRetval = aCommand.ExecuteNonQuery();
aCommand.Dispose();
sQry = "DELETE FROM iris_data_coll WHERE S_ID=@Id";
aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
aCommand.Parameters.AddWithValue("@Id", personId);
iRetval = aCommand.ExecuteNonQuery();
aCommand.Dispose();
iRetval = 0;
}
catch (Exception ex)
{
if (_SqliteHelper.sqliteConnection.State == ConnectionState.Closed)
{
MessageBox.Show("数据库连接断开，请重启软件！");
return -1;
}
MessageBox.Show("EXCEPTION: " + ex.Message);
iRetval = -1;
}
return iRetval;
}
/// <summary>
/// 获取人员详情,返回Person, IrisDataColl, IrisData三个对象
/// </summary>
public int GetDetail(Int64 personId, ref Person person, ref PersonExt personExt, ref IrisDataColl irisDataColl, ref IrisData irisData)
{
int iRetval = 0;
try
{
// sys_person表
string sQry = "SELECT * FROM sys_person WHERE ID=@Id";
SQLiteCommand aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
aCommand.Parameters.AddWithValue("@Id", personId);
SQLiteDataAdapter adapter = new SQLiteDataAdapter();
adapter.SelectCommand = aCommand;
DataTable dt = new DataTable();
adapter.Fill(dt);
if (dt != null && dt.Rows.Count != 0)
{
person = ConvertToEntity<Person>(dt, dt.Rows[0]);
}
aCommand.Dispose();
adapter.Dispose();
// sys_person表
sQry = "SELECT * FROM sys_person_ext WHERE TARGET_ID=@Id";
aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
aCommand.Parameters.AddWithValue("@Id", personId);
adapter = new SQLiteDataAdapter();
adapter.SelectCommand = aCommand;
dt = null;
dt = new DataTable();
adapter.Fill(dt);
if (dt != null && dt.Rows.Count != 0)
{
personExt = ConvertToEntity<PersonExt>(dt, dt.Rows[0]);
}
aCommand.Dispose();
adapter.Dispose();
// iris_data_coll表
sQry = "SELECT * FROM iris_data_coll WHERE S_ID=@Id";
aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
aCommand.Parameters.AddWithValue("@Id", personId);
adapter = new SQLiteDataAdapter();
adapter.SelectCommand = aCommand;
dt = null;
dt = new DataTable();
adapter.Fill(dt);
if (dt != null && dt.Rows.Count != 0)
{
irisDataColl = ConvertToEntity<IrisDataColl>(dt, dt.Rows[0]);
}
aCommand.Dispose();
adapter.Dispose();
// iris_data表
sQry = "SELECT IR_ID,S_ID,IRIS_L,IRIS_R,IMAGE_R1,IMAGE_L1,IMAGE_R2,IMAGE_L2,PATH_VIDEO FROM iris_data WHERE S_ID=@Id";
aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
aCommand.Parameters.AddWithValue("@Id", personId);
adapter = new SQLiteDataAdapter();
adapter.SelectCommand = aCommand;
dt = null;
dt = new DataTable();
adapter.Fill(dt);
aCommand.Dispose();
adapter.Dispose();
sQry = "SELECT IMAGE_L1_DATA,IMAGE_R1_DATA FROM iris_data WHERE S_ID=@Id";
aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
aCommand.Parameters.AddWithValue("@Id", personId);
SQLiteDataReader aReader = aCommand.ExecuteReader(CommandBehavior.Default);
string IMAGE_L1_DATA = "";
string IMAGE_R1_DATA = "";
if (aReader.Read())
{
IMAGE_L1_DATA = aReader.GetValue(0).ToString();
IMAGE_R1_DATA = aReader.GetValue(1).ToString();
}
aReader.Close();
//拼接datatable
DataColumn myColum = new DataColumn();
myColum.ColumnName = "IMAGE_L1_DATA";
dt.Columns.Add(myColum);
dt.Rows[0]["IMAGE_L1_DATA"] = IMAGE_L1_DATA;
myColum = new DataColumn();
myColum.ColumnName = "IMAGE_R1_DATA";
dt.Columns.Add(myColum);
dt.Rows[0]["IMAGE_R1_DATA"] = IMAGE_R1_DATA;
if (dt != null && dt.Rows.Count != 0)
{
irisData = ConvertToEntity<IrisData>(dt, dt.Rows[0]);
}
aCommand.Dispose();
}
catch (SqlException exSqlDb)
{
iRetval = exSqlDb.ErrorCode;
if (_SqliteHelper.sqliteConnection.State == ConnectionState.Closed)
{
MessageBox.Show("数据库连接断开，请重启软件！");
return -1;
}
MessageBox.Show("GetDetail EXCEPTION: " + exSqlDb.Message);
}
catch (Exception ex)
{
MessageBox.Show("GetDetail EXCEPTION: " + ex.Message);
}
return iRetval;
}
/// <summary>
/// 保存编辑，更新sys_person、sys_person_ext、iris_data_coll表
/// </summary>
public int Update(Person person, PersonExt personExt, IrisDataColl irisDataColl)
{
int iRetval = -1;
try
{
// sys_person表
string sQry = "UPDATE sys_person set NAME=@var1,SEX=@var2,ID_CARD_NO=@var3, REMARKS = @var4,NATION=@var5, BIRTHDAY=@var6,CARD_TYPE=@var7 WHERE ID=@Id";
SQLiteCommand aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
aCommand.Parameters.Add(new SQLiteParameter("@Id", 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.String));
aCommand.Parameters.Add(new SQLiteParameter("@var6", DbType.Date));
aCommand.Parameters.Add(new SQLiteParameter("@var7", DbType.String));
aCommand.Parameters[0].Value = person.id;
aCommand.Parameters[1].Value = person.name;
aCommand.Parameters[2].Value = person.sex;
aCommand.Parameters[3].Value = person.idCardNo;
aCommand.Parameters[4].Value = person.remarks;
aCommand.Parameters[5].Value = person.nation;
aCommand.Parameters[6].Value = person.birthday;
aCommand.Parameters[7].Value = person.cardType;
aCommand.ExecuteNonQuery();
aCommand.Dispose();
// sys_person_ext表
sQry = "UPDATE sys_person_ext set COLUMN_1=@var1,COLUMN_2=@var2,COLUMN_3=@var3, COLUMN_4=@var4,COLUMN_5=@var5,COLUMN_6=@var6 WHERE TARGET_ID=@Id";
aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
aCommand.Parameters.Add(new SQLiteParameter("@Id", 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.String));
aCommand.Parameters.Add(new SQLiteParameter("@var6", DbType.Date));
aCommand.Parameters[0].Value = person.id;
aCommand.Parameters[1].Value = personExt.column1;
aCommand.Parameters[2].Value = personExt.column2;
aCommand.Parameters[3].Value = personExt.column3;
aCommand.Parameters[4].Value = personExt.column4;
aCommand.Parameters[5].Value = personExt.column5;
aCommand.Parameters[6].Value = personExt.column6;
aCommand.ExecuteNonQuery();
aCommand.Dispose();
// iris_data_coll表
sQry = "UPDATE iris_data_coll set TEL1=@var1,TEL2=@var2,REASON_COLL_DESC=@var3, FLG_FOCUS=@var4,FOCUS_END_DATE=@var5,FOCUS_REMARKS=@var6,COLL_PLACE=@var7,FLG_WO=@var8,FLG_FORCE_PERSON =@var9 WHERE S_ID=@Id";
aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
aCommand.Parameters.Add(new SQLiteParameter("@Id", 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.Date));
aCommand.Parameters.Add(new SQLiteParameter("@var6", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var7", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var8", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var9", DbType.String));
aCommand.Parameters[0].Value = person.id;
aCommand.Parameters[1].Value = irisDataColl.tel1;
aCommand.Parameters[2].Value = irisDataColl.tel2;
aCommand.Parameters[3].Value = irisDataColl.reasonCollDesc;
aCommand.Parameters[4].Value = irisDataColl.flgFocus;
aCommand.Parameters[5].Value = irisDataColl.focusEndDate;
aCommand.Parameters[6].Value = irisDataColl.focusRemarks;
aCommand.Parameters[7].Value = irisDataColl.collPlace;
aCommand.Parameters[8].Value = irisDataColl.flgWo;
aCommand.Parameters[9].Value = irisDataColl.flgForcePerson;
aCommand.ExecuteNonQuery();
aCommand.Dispose();
iRetval = 0;
}
catch (SqlException exSqlDb)
{
iRetval = exSqlDb.ErrorCode;
iRetval = -1;
if (_SqliteHelper.sqliteConnection.State == ConnectionState.Closed)
{
MessageBox.Show("数据库连接断开，请重启软件！");
return -1;
}
MessageBox.Show("Update EXCEPTION: " + exSqlDb.Message);
}
catch (Exception ex)
{
iRetval = -1;
MessageBox.Show("Update EXCEPTION: " + ex.Message);
}
return iRetval;
}
#endregion
#region iris_data_coll
/// <summary>
/// iris_data_coll新增采集信息
/// </summary>
public int AddIrisDataColl(IrisDataColl irisDataColl)
{
int re = -1;
SQLiteTransaction tx = null;
try
{
tx = _SqliteHelper.sqliteConnection.BeginTransaction();
string sQry =
"INSERT INTO iris_data_coll(IR_ID,S_ID,TEL1,TEL2,FLG_CER,COLL_NAME,COLL_ID,Q_L,Q_R,REASON_COLL_DESC,COLL_TYPE,COLL_REASON,DEF_REASON,FLG_FORCE,FLG_SYNC,FLG_UPLOAD,FLG_FOCUS,FOCUS_END_DATE,FOCUS_REMARKS,COLL_PLACE,FLG_WO,COLL_WAY,IS_CER,COLL_TIME,ST_NAME, ST_TYPE,DEV_TYPE,DEV_CODE,DEV_FIRM,COLL_NUM,COLL_SPEND,FLG_FORCE_PERSON)" +
"values(@var1,@var2,@var3,@var4,@var5,@var6, @var7, @var8, @var9, @var10, @var11, @var12, @var13, @var14, @var15, @var16, @var17, @var18, @var19, @var20, @var21, @var22, @var23, @var24, @var25, @var26, @var27, @var28, @var29, @var30, @var31, @var32)";
SQLiteCommand aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
//绑定事务
aCommand.Transaction = tx;
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.String));
aCommand.Parameters.Add(new SQLiteParameter("@var6", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var7", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var8", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var9", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var10", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var11", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var12", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var13", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var14", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var15", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var16", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var17", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var18", DbType.DateTime));
aCommand.Parameters.Add(new SQLiteParameter("@var19", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var20", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var21", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var22", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var23", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var24", DbType.DateTime));
aCommand.Parameters.Add(new SQLiteParameter("@var25", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var26", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var27", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var28", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var29", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var30", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var31", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var32", DbType.String));
aCommand.Parameters[0].Value = irisDataColl.irId;
aCommand.Parameters[1].Value = irisDataColl.sId;
aCommand.Parameters[2].Value = irisDataColl.tel1;
aCommand.Parameters[3].Value = irisDataColl.tel2;
aCommand.Parameters[4].Value = irisDataColl.flgCer;
aCommand.Parameters[5].Value = irisDataColl.collName;
aCommand.Parameters[6].Value = irisDataColl.collId;
aCommand.Parameters[7].Value = irisDataColl.qL;
aCommand.Parameters[8].Value = irisDataColl.qR;
aCommand.Parameters[9].Value = irisDataColl.reasonCollDesc;
aCommand.Parameters[10].Value = irisDataColl.collType;
aCommand.Parameters[11].Value = irisDataColl.collReason;
aCommand.Parameters[12].Value = irisDataColl.defReason;
aCommand.Parameters[13].Value = irisDataColl.flgForce;
aCommand.Parameters[14].Value = irisDataColl.flgSync;
aCommand.Parameters[15].Value = irisDataColl.flgUpload;
aCommand.Parameters[16].Value = irisDataColl.flgFocus;
aCommand.Parameters[17].Value = irisDataColl.focusEndDate;
aCommand.Parameters[18].Value = irisDataColl.focusRemarks;
aCommand.Parameters[19].Value = irisDataColl.collPlace;
aCommand.Parameters[20].Value = irisDataColl.flgWo;
aCommand.Parameters[21].Value = irisDataColl.collWay;
aCommand.Parameters[22].Value = irisDataColl.isCer;
aCommand.Parameters[23].Value = irisDataColl.collTime;
aCommand.Parameters[24].Value = irisDataColl.stName;
aCommand.Parameters[25].Value = irisDataColl.stType;
aCommand.Parameters[26].Value = irisDataColl.devType;
aCommand.Parameters[27].Value = irisDataColl.devCode;
aCommand.Parameters[28].Value = irisDataColl.devFirm;
aCommand.Parameters[29].Value = irisDataColl.collNum;
aCommand.Parameters[30].Value = irisDataColl.collSpend;
aCommand.Parameters[31].Value = irisDataColl.flgForcePerson;
aCommand.ExecuteNonQuery();
tx.Commit();
aCommand.Dispose();
re = 0;
}
catch (Exception ex)
{
re = -1; tx.Rollback();
if (_MySqlHelper.mySqlConnection.State == ConnectionState.Closed)
{
MessageBox.Show("数据库连接断开，请重启软件！");
return -1;
}
MessageBox.Show("AddIrisDataColl EXCEPTION: " + ex.Message);
}
finally
{
tx.Dispose();
}
return re;
}
#endregion
#region iris_data
/// <summary>
/// iris_data新增虹膜数据
/// </summary>
public int AddIrisData(IrisData irisData)
{
int re = -1;
SQLiteTransaction tx = null;
try
{
tx = _SqliteHelper.sqliteConnection.BeginTransaction();
string sQry =
"INSERT INTO iris_data(IR_ID,S_ID,IRIS_L,IRIS_R,PATH_VIDEO,IMAGE_L1,IMAGE_L2,IMAGE_R1,IMAGE_R2,IMAGE_L1_DATA,IMAGE_R1_DATA)" +
"values(@var1,@var2,@var3,@var4,@var5,@var6, @var7, @var8, @var9, @var10, @var11)";
SQLiteCommand aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
//绑定事务
aCommand.Transaction = tx;
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.String));
aCommand.Parameters.Add(new SQLiteParameter("@var6", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var7", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var8", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var9", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var10", DbType.String));
aCommand.Parameters.Add(new SQLiteParameter("@var11", DbType.String));
aCommand.Parameters[0].Value = irisData.irId;
aCommand.Parameters[1].Value = irisData.sId;
aCommand.Parameters[2].Value = irisData.irisL;
aCommand.Parameters[3].Value = irisData.irisR;
aCommand.Parameters[4].Value = irisData.pathVideo;
aCommand.Parameters[5].Value = irisData.imageL1;
aCommand.Parameters[6].Value = irisData.imageL2;
aCommand.Parameters[7].Value = irisData.imageR1;
aCommand.Parameters[8].Value = irisData.imageR2;
aCommand.Parameters[9].Value = irisData.imageL1Data;
aCommand.Parameters[10].Value = irisData.imageR1Data;
aCommand.ExecuteNonQuery();
tx.Commit();
aCommand.Dispose();
re = 0;
}
catch (Exception 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;
}
#endregion
/// <summary>
/// DataTable转换为实体对象
/// </summary>
private T ConvertToEntity<T>(DataTable dt, DataRow row) where T : new()
{
T t = new T();
try
{
System.Data.DataColumnCollection columns = dt.Columns;
int iColumnCount = columns.Count;
int i;
int j;
Type elementType;
elementType = t.GetType();
System.Reflection.PropertyInfo[] publicProperties = elementType.GetProperties();
//if (!(publicProperties.Length > iColumnCount))
//{
for (i = 0; i < iColumnCount; i++)
{
for (j = 0; j < publicProperties.Length; j++)
{
if (columns[i].ColumnName.ToLower().Replace("_", "") == publicProperties[j].Name.ToLower().Replace("_", ""))
{
if (publicProperties[j].PropertyType == typeof(int))
{
int num = 0;
try
{
num = Convert.ToInt32(row[i]);
}
catch
{
}
publicProperties[j].SetValue(t, num, null);
}
else
if (publicProperties[j].PropertyType == typeof(string) && row[i] == System.DBNull.Value)
{
publicProperties[j].SetValue(t, "", null);
}
else
{
object value = row[i] == System.DBNull.Value ? null : row[i];
publicProperties[j].SetValue(t, value, null);
}
}
}
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
return t;
}
/// <summary>
/// 生成采集编号
/// </summary>
public string getCollNum()
{
string collNum = "";
string preStr = "HM" + ConfigHelper.GetAppConfig("DeptCode").ToString().Trim();
SQLiteDataReader aReader = null;
try
{
// sys_person表
string sQry = "select max(COLL_NUM) from iris_data_coll";
SQLiteCommand aCommand = new SQLiteCommand(sQry, _SqliteHelper.sqliteConnection);
aCommand.ExecuteNonQuery();
aReader = aCommand.ExecuteReader(CommandBehavior.Default);
if (aReader.Read() && !aReader.IsDBNull(0))
{
string date = aReader.GetString(0).Substring(14, 8);
if (date == DateTime.Now.ToString("yyyyMMdd"))
{
//string index = (Convert.ToInt64(aReader.GetString(0).Substring(22, 6)) + 1).ToString();
//if (index.Length != 6)
//{ index =}
collNum = preStr + date + (Convert.ToInt64(aReader.GetString(0).Substring(22, 6)) + 1).ToString("000000");
}
else
{
collNum = preStr + DateTime.Now.ToString("yyyyMMdd") + "000001";
}
}
else
{
collNum = preStr + DateTime.Now.ToString("yyyyMMdd") + "000001";
}
}
catch (Exception e)
{
if (_MySqlHelper.mySqlConnection.State == ConnectionState.Closed)
{
MessageBox.Show("数据库连接断开，请重启软件！");
}
MessageBox.Show("getCollNum catch error:" + e.Message);
}
finally
{
if (aReader != null)
{
aReader.Close();
aReader.Dispose();
}
}
return collNum;
}
}
}