public static Record findRecordByRecordID(int recordID)
{
SqlConnection conn = null;
SqlCommand cmd = null;
try
{
conn = new SqlConnection(conStr);
conn.Open();
cmd = conn.CreateCommand();
cmd.CommandText = "select * from record where record_ID=@recordID";
cmd.Parameters.AddWithValue("@recordID", recordID);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Record rc = new Record();
rc.recordID = reader.GetInt32(reader.GetOrdinal("record_ID"));
rc.carID = reader.GetString(reader.GetOrdinal("car_ID"));
rc.time = reader.GetDateTime(reader.GetOrdinal("time"));
rc.worker = reader.GetString(reader.GetOrdinal("worker"));
rc.faultID = reader.GetInt32(reader.GetOrdinal("fault_ID"));
rc.solutionID = reader.GetInt32(reader.GetOrdinal("solution_ID"));
return rc;
}
return null;
}
finally
{
conn.Close();
}
}
public static List<Record> findRecordByTime(DateTime start, DateTime end)
{
SqlConnection conn = null;
SqlCommand cmd = null;
try
{
conn = new SqlConnection(conStr);
conn.Open();
cmd = conn.CreateCommand();
cmd.CommandText = "select * from record where time between @start and @end";
cmd.Parameters.AddWithValue("@start", start);
cmd.Parameters.AddWithValue("@end", end);
SqlDataReader reader = cmd.ExecuteReader();
List<Record> recordList = new List<Record>();
while (reader.Read())
{
Record rc = new Record();
rc.recordID = reader.GetInt32(reader.GetOrdinal("record_ID"));
rc.carID = reader.GetString(reader.GetOrdinal("car_ID"));
rc.time = reader.GetDateTime(reader.GetOrdinal("time"));
rc.worker = reader.GetString(reader.GetOrdinal("worker"));
rc.faultID = reader.GetInt32(reader.GetOrdinal("fault_ID"));
rc.solutionID = reader.GetInt32(reader.GetOrdinal("solution_ID"));
recordList.Add(rc);
}
return recordList;
}
finally
{
conn.Close();
}
}
public static int deleteRecord(Record record)
{
SqlConnection conn = null;
SqlCommand cmd = null;
try
{
conn = new SqlConnection(conStr);
conn.Open();
cmd = conn.CreateCommand();
cmd.CommandText = "delete from record where record_ID=@recordID";
cmd.Parameters.AddWithValue("@recordID", record.recordID);
return cmd.ExecuteNonQuery();
}
finally
{
conn.Close();
}
}
public static int addRecord(Record rc)
{
SqlConnection conn = null;
SqlCommand cmd = null;
try
{
conn = new SqlConnection(conStr);
conn.Open();
cmd = conn.CreateCommand();
if (findRecordByRecordID(rc.recordID) != null)
return -1;
cmd.CommandText = "insert into record (car_ID,time,worker,fault_ID,solution_ID) values(@carID,@time,@worker,@faultID,@solutionID)";
//cmd.Parameters.AddWithValue("@recordID", rc.recordID);
cmd.Parameters.AddWithValue("@carID", rc.carID);
cmd.Parameters.AddWithValue("@time", rc.time);
cmd.Parameters.AddWithValue("@worker", rc.worker);
cmd.Parameters.AddWithValue("@faultID", rc.faultID);
cmd.Parameters.AddWithValue("@solutionID", rc.solutionID);
return cmd.ExecuteNonQuery();
}
finally
{
conn.Close();
}
}
public static int updateRecord(Record record)
{
SqlConnection conn = null;
SqlCommand cmd = null;
try
{
conn = new SqlConnection(conStr);
conn.Open();
cmd = conn.CreateCommand();
cmd.CommandText = "update record set car_ID=@carID,time=@time,worker=@worker,fault_ID=@faultID,solution_ID=@solutionID where record_ID=@recordID";
cmd.Parameters.AddWithValue("@recordID", record.recordID);
cmd.Parameters.AddWithValue("@carID", record.carID);
cmd.Parameters.AddWithValue("@time", record.time);
cmd.Parameters.AddWithValue("@worker", record.worker);
cmd.Parameters.AddWithValue("@faultID", record.faultID);
cmd.Parameters.AddWithValue("@solutionID", record.solutionID);
return cmd.ExecuteNonQuery();
}
finally
{
conn.Close();
}
}
public static int updateMessage(Message m)
{
Operator op = findAllByRecordID(m.recordID);
m.solutionID = op.solution.solutionID;
m.faultID = op.fault.faultID;
Record r = new Record();
r.recordID = m.recordID;
r.carID = m.carID;
r.time = m.time;
r.worker = m.worker;
r.faultID = m.faultID;
r.solutionID = m.solutionID;
updateRecord(r);
Fault f = new Fault();
f.faultID = m.faultID;
f.fault = m.fault;
f.faultKey = m.faultKey;
f.faultImgs = op.fault.faultImgs;
updateFault(f);
Solution s = new Solution();
s.solutionID = m.solutionID;
s.solution = m.solution;
s.runSolution = m.runSolution;
s.criImgs = op.solution.criImgs;
int i = 0;
i = updateSolution(s);
if (i != 0)
{
return 0;
}
else
{
return 1;
}
}
public static List<Record> getAllRecord()
{
SqlConnection conn = null;
SqlCommand cmd = null;
try
{
conn = new SqlConnection(conStr);
conn.Open();
cmd = conn.CreateCommand();
cmd.CommandText = "select * from record";
SqlDataReader reader = cmd.ExecuteReader();
List<Record> recordList = new List<Record>();
while (reader.Read())
{
Record rc = new Record();
rc.recordID = reader.GetInt32(reader.GetOrdinal("record_ID"));
rc.carID = reader.GetString(reader.GetOrdinal("car_ID"));
rc.time = reader.GetDateTime(reader.GetOrdinal("time"));
rc.worker = reader.GetString(reader.GetOrdinal("worker"));
rc.faultID = reader.GetInt32(reader.GetOrdinal("fault_ID"));
rc.solutionID = reader.GetInt32(reader.GetOrdinal("solution_ID"));
recordList.Add(rc);
}
return recordList;
}
finally
{
conn.Close();
}
}