private IEnumerable <EmployeeModel> ReadEmployeeWithDataTable(string predicate, params SqlParameter[] parameters)
{
DataTable table = new DataTable();
Sqlconn.Open();
var cmd = new SqlCommand("select * from Employee e " + predicate, Sqlconn);
foreach (var item in parameters)
{
cmd.Parameters.Add(item);
}
SqlDataAdapter Adapter = new SqlDataAdapter(cmd);
Adapter.Fill(table);
for (int i = 0; i < table.Rows.Count; i++)
{
yield return(new EmployeeModel
{
EmployeeId = table.Rows[i].Field <int>("EmployeeId"),
FirstName = table.Rows[i].Field <string>("FirstName"),
LastName = table.Rows[i].Field <string>("LastName")
});
}
Sqlconn.Close();
}
public IEnumerable <SecurityModel> GetDoorActivity(DateTime from, DateTime to, int doorId)
{
Sqlconn.Open();
SqlCommand cmd = new SqlCommand(@"select ah.AccessHistoryId,ah.AttemptDate,e.EmployeeId, e.FirstName,e.LastName,ah.Result from AccessHistory ah join Employee
e on ah.EmployeeId = e.EmployeeId where ah.DoorId=@doorId", Sqlconn);
cmd.Parameters.AddWithValue("@From", from);
cmd.Parameters.AddWithValue("@To", to);
cmd.Parameters.AddWithValue("@doorId", doorId);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
yield return(new SecurityModel
{
AccessHistoryId = reader.GetInt32(0),
AccessAttempt = reader.GetDateTime(1),
EmployeeId = reader.GetInt32(2),
FirstName = reader.GetString(3),
LastName = reader.GetString(4),
Result = reader.GetBoolean(5),
});
}
Sqlconn.Close();
}
public void DeleteEmployee(int Id)
{
Sqlconn.Open();
SqlCommand cmd = new SqlCommand("delete Employee Where EmployeeId=@Pk", Sqlconn);
cmd.Parameters.AddWithValue("@Pk", Id);
cmd.ExecuteNonQuery();
Sqlconn.Close();
}
public void AddCredential(SecurityModel model)
{
Sqlconn.Open();
SqlCommand cmd = new SqlCommand("insert SecurityDevice(SecurityDeviceType)values(@DeviceType);select scope_identity()", Sqlconn);
cmd.Parameters.AddWithValue("@DeviceType", model.Credential);
cmd.ExecuteNonQuery();
Sqlconn.Close();
}
public string GetCachedTree()
{
lock (Sqlconn)
{
var ser = new JavaScriptSerializer();
try
{
string ConfStr = ser.Serialize(conf);
string sqlquery = string.Format("Select SavedTreeJson from SavedTree where Configuration='{0}'", ConfStr);
Sqlconn.Open();
DataTable dtres = new DataTable();
using (SqlCommand comm = new SqlCommand(sqlquery, Sqlconn))
{
using (SqlDataAdapter da = new SqlDataAdapter(comm))
{
da.Fill(dtres);
}
}
if (dtres == null || dtres.Rows.Count == 0 || dtres.Rows[0][0] == null)
{
return(null);
}
string JsonTree = dtres.Rows[0][0].ToString();
//ISdmxObjects ret = GetSdmxOBJ(dtres.Rows[0][0].ToString());
try
{
string sqlupd = string.Format("Update SavedTree set LastRequest='{1}' where Configuration='{0}'", ConfStr, DateTime.Now.ToString("yyyyMMdd HHmm"));
using (SqlCommand commupd = new SqlCommand(sqlupd, Sqlconn))
commupd.ExecuteNonQuery();
}
catch (Exception)
{
return(null);
}
return(JsonTree);
}
catch (Exception)
{
return(null);
}
finally
{
Sqlconn.Close();
}
}
}
public void CreateEmployee(EmployeeModel model)
{
Sqlconn.Open();
// SqlCommand cmd2 = new SqlCommand(@"if exists(select * from Employee e where e.EmployeeId=@Pk)
//begin
//update Employee
//set FirstName = @FName,
//LastName = @LName where EmployeeId = @Pk
//end
//else
// begin
// insert Employee(FirstName, LastName)values(@FName, @LName)
//select scope_identity()
//end");
SqlCommand cmd = new SqlCommand("insert Employee(FirstName,LastName)values(@FName,@LName)", Sqlconn);
cmd.Parameters.AddWithValue("@Fname", model.FirstName);
cmd.Parameters.AddWithValue("@LName", model.LastName);
// cmd.Parameters.AddWithValue("@Pk", model.EmployeeId);
cmd.ExecuteNonQuery();
Sqlconn.Close();
}
private IEnumerable <EmployeeModel> ReadEmployee(string predicate, params SqlParameter[] parameters)
{
Sqlconn.Open();
var cmd = new SqlCommand("select * from Employee e " + predicate, Sqlconn);
foreach (var item in parameters)
{
cmd.Parameters.Add(item);
}
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
yield return(new EmployeeModel
{
EmployeeId = reader.GetInt32(0),
FirstName = reader.GetString(1),
LastName = reader.GetString(2)
});
}
Sqlconn.Close();
}
public void ThreadSaveCachedTree(object TObject)
{
string JsonTree = (string)TObject;
lock (Sqlconn)
{
var ser = new JavaScriptSerializer();
try
{
Sqlconn.Open();
string ConfStr = ser.Serialize(conf);
//string TreeName = string.Format("tree_{0}.json", Guid.NewGuid());
//SaveSdmxOBJ(SdmxOBJ, TreeName);
string sqlcount = string.Format("Select count(*) from SavedTree where Configuration='{0}'", ConfStr.Replace("'", "''"));
int conta = 0;
using (SqlCommand commconta = new SqlCommand(sqlcount, Sqlconn))
conta = Convert.ToInt32(commconta.ExecuteScalar());
if (conta == 0)
{
string sqlupd = string.Format("INSERT INTO SavedTree (Configuration, SavedTreeJson, LastUpdate, LastRequest) VALUES ('{0}', '{1}', '{2}', '{2}')"
, ConfStr.Replace("'", "''"), JsonTree.Replace("'", "''"), DateTime.Now.ToString("yyyyMMdd HHmm"));
using (SqlCommand commupd = new SqlCommand(sqlupd, Sqlconn))
commupd.ExecuteNonQuery();
}
}
catch (Exception)
{
return;
}
finally
{
Sqlconn.Close();
}
}
}
