internal DaoErrMsg UpdatePersonnel(List <PersonnelContent> Personnel)
{
DaoErrMsg err = new DaoErrMsg();
if (Personnel.Count <= 0)
{
return(err);
}
//先清除人員資資料;//
string strSchema = string.Format("DELETE FROM tbPersonnel;");
m_SQL.ExecuteNonQuery(strSchema);
//更新資料;//
int Count = 0;
StringBuilder sbSchema = new StringBuilder();
sbSchema.Append(@"insert into tbPersonnel( [Key], [JobNumber], [ChineseName], [EnglishName], [CardNumber], [Alias], [Voice], [TEL], [Phone]) values ");
for (int i = 0; i < Personnel.Count; i++)
{
sbSchema.AppendFormat(@"('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}'), "
, Personnel[i].Key
, Personnel[i].JobNumber
, Personnel[i].ChineseName
, Personnel[i].EnglicshName
, Personnel[i].CardNumber
, Personnel[i].Alias
, Personnel[i].Voice
, Personnel[i].TEL
, Personnel[i].Phone);
Count++;
if (Count >= 50)
{
sbSchema.RemoveLast(",");
err = m_SQL.ExecuteNonQuery(sbSchema.ToString());
if (err.isError == true)
{
return(err);
}
sbSchema.Length = 0;
sbSchema.Capacity = 0;
Count = 0;
sbSchema.Append(@"insert into tbPersonnel( [Key], [JobNumber], [ChineseName], [EnglishName], [CardNumber], [Alias], [Voice], [TEL], [Phone]) values ");
}
}
if (Count != 0)
{
sbSchema.RemoveLast(",");
err = m_SQL.ExecuteNonQuery(sbSchema.ToString());
}
return(err);
}
/// <summary>
/// 照SQL語法取得Table資料
/// </summary>
/// <param name="Schema"></param>
/// <returns></returns>
private DataTable GetDataTable(DaoDbCommon DbCom, string Schema, params object[] Values)
{
DataTable Dt;
DaoErrMsg Err = DbCom.GetDataTable(Schema, out Dt, Values);
if (Err.isError)
{
return(null);
}
return(Dt);
}
internal DaoErrMsg UpdateAttendance(List <Attendance> attendance)
{
DaoErrMsg err = new DaoErrMsg();
if (attendance.Count <= 0)
{
return(err);
}
//先清除人員資資料;//
string strSchema = string.Format("DELETE FROM tbAttendance;");
m_SQL.ExecuteNonQuery(strSchema);
//更新資料;//
int Count = 0;
StringBuilder sbSchema = new StringBuilder();
sbSchema.Append(@"insert into tbAttendance( [CardNum], [Date], [Time]) values ");
for (int i = 0; i < attendance.Count; i++)
{
sbSchema.AppendFormat(@"('{0}','{1}','{2}'), "
, attendance[i].CardNo
, attendance[i].Time.ToString("yyyyMMdd")
, attendance[i].Time.ToString("HHmm"));
Count++;
if (Count >= 50)
{
sbSchema.RemoveLast(",");
err = m_SQL.ExecuteNonQuery(sbSchema.ToString());
if (err.isError == true)
{
return(err);
}
sbSchema.Length = 0;
sbSchema.Capacity = 0;
Count = 0;
sbSchema.Append(@"insert into tbAttendance( [CardNum], [Date], [Time]) values ");
}
}
if (Count != 0)
{
sbSchema.RemoveLast(",");
err = m_SQL.ExecuteNonQuery(sbSchema.ToString());
}
return(err);
}
/// <summary>
/// 連接使用的資料庫
/// </summary>
internal DaoErrMsg OpenDatabase()
{
DaoErrMsg Msg = new DaoErrMsg();
try
{
Msg = ConnectSQLite();
if (Msg.isError == true)
{
throw new NotImplementedException(string.Format("Connect SQL Server database error. Message:{0}", Msg.ErrorMsg));
}
}
catch (Exception ex)
{
Msg.isError = true;
Msg.ErrorMsg = string.Format("Connect SQL Server database throw exception. Message:{0}", ex.Message);
return(Msg);
}
return(Msg);
}
private DaoErrMsg ConnectSQLite()
{
DaoErrMsg Err = new DaoErrMsg();
if (m_SQL != null)
{
//表示已開啟過;//
return(Err);
}
//連接Sqlite;//
string SQLiteConn = @"Data source = ./MYP.db; Password = Myp53750804; DateTimeKind = Utc";
m_SQL = new DaoDbCommon(SQLiteConn, new SQLiteConnection());
Err = m_SQL.Connect();
if (Err.isError)
{
System.Diagnostics.Debug.WriteLine(Err.ErrorMsg);
m_SQL = null;
return(Err);
}
return(Err);
}
