// сохранить изменения строки
public void Save(AutoschoolDataSet dataSet, AbstractConnection conn, AbstractTransaction tr)
{
dataAdapter = new SqlDataAdapter();
// на обновление
dataAdapter.UpdateCommand = new SqlCommand("UPDATE PracticeLessons SET ID = @ID, Student = @Student, " +
"AppointedDate = @AppointedDate, AppointedTime = @AppointedTime, FactDate = @FactDate, FactTime = @FactTime " +
"WHERE ID = @OldID", conn.getConnection(), tr.getTransaction());
dataAdapter.UpdateCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int, 255, "ID");
dataAdapter.UpdateCommand.Parameters.Add("@Student", System.Data.SqlDbType.Int, 255, "Student");
dataAdapter.UpdateCommand.Parameters.Add("@AppointedDate", System.Data.SqlDbType.Date, 255, "AppointedDate");
dataAdapter.UpdateCommand.Parameters.Add("@AppointedTime", System.Data.SqlDbType.Time, 255, "AppointedTime");
dataAdapter.UpdateCommand.Parameters.Add("@FactDate", System.Data.SqlDbType.Date, 255, "FactDate");
dataAdapter.UpdateCommand.Parameters.Add("@FactTime", System.Data.SqlDbType.Time, 255, "FactTime");
dataAdapter.UpdateCommand.Parameters.Add("@OldID", System.Data.SqlDbType.Int, 255, "ID").SourceVersion = System.Data.DataRowVersion.Original;
// на вставку
dataAdapter.InsertCommand = new SqlCommand("INSERT INTO PracticeLessons (ID, Student, AppointedDate, AppointedTime, " +
"FactDate, FactTime) VALUES (@ID, @Student, @AppointedDate, @AppointedTime, " +
"@FactDate, @FactTime)", conn.getConnection(), tr.getTransaction());
dataAdapter.InsertCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int, 255, "ID");
dataAdapter.InsertCommand.Parameters.Add("@Student", System.Data.SqlDbType.Int, 255, "Student");
dataAdapter.InsertCommand.Parameters.Add("@AppointedDate", System.Data.SqlDbType.Date, 255, "AppointedDate");
dataAdapter.InsertCommand.Parameters.Add("@AppointedTime", System.Data.SqlDbType.Time, 255, "AppointedTime");
dataAdapter.InsertCommand.Parameters.Add("@FactDate", System.Data.SqlDbType.Date, 255, "FactDate");
dataAdapter.InsertCommand.Parameters.Add("@FactTime", System.Data.SqlDbType.Time, 255, "FactTime");
// на удаление
dataAdapter.DeleteCommand = new SqlCommand("DELETE PracticeLessons WHERE ID = @ID", conn.getConnection(), tr.getTransaction());
dataAdapter.DeleteCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int, 255, "ID").SourceVersion = System.Data.DataRowVersion.Original;
dataAdapter.Update(dataSet, "PracticeLessons");
}
// сохранить изменения строки
public void Save(AutoschoolDataSet dataSet, AbstractConnection conn, AbstractTransaction tr)
{
dataAdapter = new SqlDataAdapter();
// на обновление
dataAdapter.UpdateCommand = new SqlCommand("UPDATE ReplacementsCarriers SET ID = @ID, CarrierUse = @CarrierUse, " +
"CarrierReplacement = @CarrierReplacement, DateBeginReplacement = @DateBeginReplacement, " +
"DateEndReplacement = @DateEndReplacement " +
"WHERE ID = @OldID", conn.getConnection(), tr.getTransaction());
dataAdapter.UpdateCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int, 255, "ID");
dataAdapter.UpdateCommand.Parameters.Add("@CarrierUse", System.Data.SqlDbType.Int, 255, "CarrierUse");
dataAdapter.UpdateCommand.Parameters.Add("@CarrierReplacement", System.Data.SqlDbType.Int, 255, "CarrierReplacement");
dataAdapter.UpdateCommand.Parameters.Add("@DateBeginReplacement", System.Data.SqlDbType.Date, 255, "DateBeginReplacement");
dataAdapter.UpdateCommand.Parameters.Add("@DateEndReplacement", System.Data.SqlDbType.Date, 255, "DateEndReplacement");
dataAdapter.UpdateCommand.Parameters.Add("@OldID", System.Data.SqlDbType.Int, 255, "ID").SourceVersion = System.Data.DataRowVersion.Original;
// на вставку
dataAdapter.InsertCommand = new SqlCommand("INSERT INTO ReplacementsCarriers (ID, CarrierUse, CarrierReplacement, " +
"DateBeginReplacement, DateEndReplacement) VALUES (@ID, @CarrierUse, @CarrierReplacement, @DateBeginReplacement, @DateEndReplacement)",
conn.getConnection(), tr.getTransaction());
dataAdapter.InsertCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int, 255, "ID");
dataAdapter.InsertCommand.Parameters.Add("@CarrierUse", System.Data.SqlDbType.Int, 255, "CarrierUse");
dataAdapter.InsertCommand.Parameters.Add("@CarrierReplacement", System.Data.SqlDbType.Int, 255, "CarrierReplacement");
dataAdapter.InsertCommand.Parameters.Add("@DateBeginReplacement", System.Data.SqlDbType.Date, 255, "DateBeginReplacement");
dataAdapter.InsertCommand.Parameters.Add("@DateEndReplacement", System.Data.SqlDbType.Date, 255, "DateEndReplacement");
// на удаление
dataAdapter.DeleteCommand = new SqlCommand("DELETE ReplacementsCarriers WHERE ID = @ID", conn.getConnection(), tr.getTransaction());
dataAdapter.DeleteCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int, 255, "ID").SourceVersion = System.Data.DataRowVersion.Original;
dataAdapter.Update(dataSet, "ReplacementsCarriers");
}
public void ReadByID(AutoschoolDataSet dataSet, AbstractConnection conn, AbstractTransaction tr, int ID)
{
dataAdapter = new SqlDataAdapter();
dataAdapter.SelectCommand = new SqlCommand("SELECT * FROM Instructors WHERE ID = @ID", conn.getConnection(), tr.getTransaction());
dataAdapter.SelectCommand.Parameters.AddWithValue("@ID", ID);
dataAdapter.Fill(dataSet, "Instructors");
}
// сохранить изменения строки
public void Save(AutoschoolDataSet dataSet, AbstractConnection conn, AbstractTransaction tr)
{
dataAdapter = new SqlDataAdapter();
// на обновление
dataAdapter.UpdateCommand = new SqlCommand("UPDATE CarriersRepairs SET ID = @ID, Carrier = @Carrier, Master = @Master, " +
"Work = @Work, BeginDate = @BeginDate, EndDate = @EndDate " +
"WHERE ID = @OldID", conn.getConnection(), tr.getTransaction());
dataAdapter.UpdateCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int, 255, "ID");
dataAdapter.UpdateCommand.Parameters.Add("@Carrier", System.Data.SqlDbType.Int, 255, "Carrier");
dataAdapter.UpdateCommand.Parameters.Add("@Master", System.Data.SqlDbType.Int, 255, "Master");
dataAdapter.UpdateCommand.Parameters.Add("@Work", System.Data.SqlDbType.Text, 255, "Work");
dataAdapter.UpdateCommand.Parameters.Add("@BeginDate", System.Data.SqlDbType.Date, 255, "BeginDate");
dataAdapter.UpdateCommand.Parameters.Add("@EndDate", System.Data.SqlDbType.Date, 255, "EndDate");
dataAdapter.UpdateCommand.Parameters.Add("@OldID", System.Data.SqlDbType.Int, 255, "ID").SourceVersion = System.Data.DataRowVersion.Original;
// на вставку
dataAdapter.InsertCommand = new SqlCommand("INSERT INTO CarriersRepairs (ID, Carrier, Master, Work, " +
"BeginDate, EndDate) VALUES (@ID, @Carrier, @Master, @Work, @BeginDate, @EndDate)",
conn.getConnection(), tr.getTransaction());
dataAdapter.InsertCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int, 255, "ID");
dataAdapter.InsertCommand.Parameters.Add("@Carrier", System.Data.SqlDbType.Int, 255, "Carrier");
dataAdapter.InsertCommand.Parameters.Add("@Master", System.Data.SqlDbType.Int, 255, "Master");
dataAdapter.InsertCommand.Parameters.Add("@Work", System.Data.SqlDbType.Text, 255, "Work");
dataAdapter.InsertCommand.Parameters.Add("@BeginDate", System.Data.SqlDbType.Date, 255, "BeginDate");
dataAdapter.InsertCommand.Parameters.Add("@EndDate", System.Data.SqlDbType.Date, 255, "EndDate");
// на удаление
dataAdapter.DeleteCommand = new SqlCommand("DELETE CarriersRepairs WHERE ID = @ID", conn.getConnection(), tr.getTransaction());
dataAdapter.DeleteCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int, 255, "ID").SourceVersion = System.Data.DataRowVersion.Original;
dataAdapter.Update(dataSet, "CarriersRepairs");
}
// сохранить изменения строки
public void Save(AutoschoolDataSet dataSet, AbstractConnection conn, AbstractTransaction tr)
{
dataAdapter = new SqlDataAdapter();
// на обновление
dataAdapter.UpdateCommand = new SqlCommand("UPDATE Instructors SET ID = @ID, Surname = @Surname, " +
"FirstName = @FirstName, PatronymicName = @PatronymicName, Photo = @Photo, WorkStatus = @WorkStatus " +
"WHERE ID = @OldID", conn.getConnection(), tr.getTransaction());
dataAdapter.UpdateCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int, 255, "ID");
dataAdapter.UpdateCommand.Parameters.Add("@Surname", System.Data.SqlDbType.Text, 255, "Surname");
dataAdapter.UpdateCommand.Parameters.Add("@FirstName", System.Data.SqlDbType.Text, 255, "FirstName");
dataAdapter.UpdateCommand.Parameters.Add("@PatronymicName", System.Data.SqlDbType.Text, 255, "PatronymicName");
dataAdapter.UpdateCommand.Parameters.Add("@Photo", System.Data.SqlDbType.Image, 2147483647, "Photo");
dataAdapter.UpdateCommand.Parameters.Add("@WorkStatus", System.Data.SqlDbType.Int, 255, "WorkStatus");
dataAdapter.UpdateCommand.Parameters.Add("@OldID", System.Data.SqlDbType.Int, 255, "ID").SourceVersion = System.Data.DataRowVersion.Original;
// на вставку
dataAdapter.InsertCommand = new SqlCommand("INSERT INTO Instructors (ID, Surname, FirstName, PatronymicName, " +
"Photo, WorkStatus) VALUES (@ID, @Surname, @FirstName, @PatronymicName, @Photo, @WorkStatus)",
conn.getConnection(), tr.getTransaction());
dataAdapter.InsertCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int, 255, "ID");
dataAdapter.InsertCommand.Parameters.Add("@Surname", System.Data.SqlDbType.Text, 255, "Surname");
dataAdapter.InsertCommand.Parameters.Add("@FirstName", System.Data.SqlDbType.Text, 255, "FirstName");
dataAdapter.InsertCommand.Parameters.Add("@PatronymicName", System.Data.SqlDbType.Text, 255, "PatronymicName");
dataAdapter.InsertCommand.Parameters.Add("@Photo", System.Data.SqlDbType.Image, 2147483647, "Photo");
dataAdapter.InsertCommand.Parameters.Add("@WorkStatus", System.Data.SqlDbType.Int, 255, "WorkStatus");
// на удаление
dataAdapter.DeleteCommand = new SqlCommand("DELETE Instructors WHERE ID = @ID", conn.getConnection(), tr.getTransaction());
dataAdapter.DeleteCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int, 255, "ID").SourceVersion = System.Data.DataRowVersion.Original;
dataAdapter.Update(dataSet, "Instructors");
}
// сохранить изменения строки
public void Save(AutoschoolDataSet dataSet, AbstractConnection conn, AbstractTransaction tr)
{
dataAdapter = new SqlDataAdapter();
// на обновление
dataAdapter.UpdateCommand = new SqlCommand("UPDATE TheoryLessons SET ID = @ID, Date = @Date, " +
"Time = @Time, Auditorium = @Auditorium, [Group] = @Group " +
"WHERE ID = @OldID", conn.getConnection(), tr.getTransaction());
dataAdapter.UpdateCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int, 255, "ID");
dataAdapter.UpdateCommand.Parameters.Add("@Date", System.Data.SqlDbType.Date, 255, "Date");
dataAdapter.UpdateCommand.Parameters.Add("@Time", System.Data.SqlDbType.Time, 255, "Time");
dataAdapter.UpdateCommand.Parameters.Add("@Auditorium", System.Data.SqlDbType.Int, 255, "Auditorium");
dataAdapter.UpdateCommand.Parameters.Add("@Group", System.Data.SqlDbType.Int, 255, "Group");
dataAdapter.UpdateCommand.Parameters.Add("@OldID", System.Data.SqlDbType.Int, 255, "ID").SourceVersion = System.Data.DataRowVersion.Original;
// на вставку
dataAdapter.InsertCommand = new SqlCommand("INSERT INTO TheoryLessons (ID, Date, Time, Auditorium, " +
"[Group]) VALUES (@ID, @Date, @Time, @Auditorium, @Group)", conn.getConnection(), tr.getTransaction());
dataAdapter.InsertCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int, 255, "ID");
dataAdapter.InsertCommand.Parameters.Add("@Date", System.Data.SqlDbType.Date, 255, "Date");
dataAdapter.InsertCommand.Parameters.Add("@Time", System.Data.SqlDbType.Time, 255, "Time");
dataAdapter.InsertCommand.Parameters.Add("@Auditorium", System.Data.SqlDbType.Int, 255, "Auditorium");
dataAdapter.InsertCommand.Parameters.Add("@Group", System.Data.SqlDbType.Int, 255, "Group");
// на удаление
dataAdapter.DeleteCommand = new SqlCommand("DELETE TheoryLessons WHERE ID = @ID", conn.getConnection(), tr.getTransaction());
dataAdapter.DeleteCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int, 255, "ID").SourceVersion = System.Data.DataRowVersion.Original;
dataAdapter.Update(dataSet, "TheoryLessons");
}
public AbstractTransaction BeginTransaction()
{
SqlTransaction tr = conn.BeginTransaction();
AbstractTransaction result = new AbstractTransaction(tr);
return(result);
}
// сохранить изменения строки
public void Save(AutoschoolDataSet dataSet, AbstractConnection conn, AbstractTransaction tr)
{
dataAdapter = new SqlDataAdapter();
// на обновление
dataAdapter.UpdateCommand = new SqlCommand("UPDATE InstructorsCategories SET ID = @ID, Instructor = @Instructor, Category = @Category " +
"WHERE ID = @OldID", conn.getConnection(), tr.getTransaction());
dataAdapter.UpdateCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int, 255, "ID");
dataAdapter.UpdateCommand.Parameters.Add("@Instructor", System.Data.SqlDbType.Int, 255, "Instructor");
dataAdapter.UpdateCommand.Parameters.Add("@Category", System.Data.SqlDbType.Int, 255, "Category");
dataAdapter.UpdateCommand.Parameters.Add("@OldID", System.Data.SqlDbType.Int, 255, "ID").SourceVersion = System.Data.DataRowVersion.Original;
// на вставку
dataAdapter.InsertCommand = new SqlCommand("INSERT INTO InstructorsCategories (ID, Instructor, Category) VALUES (@ID, @Instructor, @Category)",
conn.getConnection(), tr.getTransaction());
dataAdapter.InsertCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int, 255, "ID");
dataAdapter.InsertCommand.Parameters.Add("@Instructor", System.Data.SqlDbType.Int, 255, "Instructor");
dataAdapter.InsertCommand.Parameters.Add("@Category", System.Data.SqlDbType.Int, 255, "Category");
// на удаление
dataAdapter.DeleteCommand = new SqlCommand("DELETE InstructorsCategories WHERE ID = @ID", conn.getConnection(), tr.getTransaction());
dataAdapter.DeleteCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int, 255, "ID").SourceVersion = System.Data.DataRowVersion.Original;
dataAdapter.Update(dataSet, "InstructorsCategories");
}
// сохранить изменения строки
public void Save(AutoschoolDataSet dataSet, AbstractConnection conn, AbstractTransaction tr)
{
dataAdapter = new SqlDataAdapter();
// на обновление
dataAdapter.UpdateCommand = new SqlCommand("UPDATE Groups SET ID = @ID, Name = @Name, StartLearning = @StartLearning, " +
"EndLearning = @EndLearning, Category = @Category, Teacher = @Teacher " +
"WHERE ID = @OldID", conn.getConnection(), tr.getTransaction());
dataAdapter.UpdateCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int, 255, "ID");
dataAdapter.UpdateCommand.Parameters.Add("@Name", System.Data.SqlDbType.Text, 255, "Name");
dataAdapter.UpdateCommand.Parameters.Add("@StartLearning", System.Data.SqlDbType.Date, 255, "StartLearning");
dataAdapter.UpdateCommand.Parameters.Add("@EndLearning", System.Data.SqlDbType.Date, 255, "EndLearning");
dataAdapter.UpdateCommand.Parameters.Add("@Category", System.Data.SqlDbType.Int, 255, "Category");
dataAdapter.UpdateCommand.Parameters.Add("@Teacher", System.Data.SqlDbType.Int, 255, "Teacher");
dataAdapter.UpdateCommand.Parameters.Add("@OldID", System.Data.SqlDbType.Int, 255, "ID").SourceVersion = System.Data.DataRowVersion.Original;
// на вставку
dataAdapter.InsertCommand = new SqlCommand("INSERT INTO Groups (ID, Name, StartLearning, EndLearning, " +
"Category, Teacher) VALUES (@ID, @Name, @StartLearning, @EndLearning, @Category, @Teacher)",
conn.getConnection(), tr.getTransaction());
dataAdapter.InsertCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int, 255, "ID");
dataAdapter.InsertCommand.Parameters.Add("@Name", System.Data.SqlDbType.Text, 255, "Name");
dataAdapter.InsertCommand.Parameters.Add("@StartLearning", System.Data.SqlDbType.Date, 255, "StartLearning");
dataAdapter.InsertCommand.Parameters.Add("@EndLearning", System.Data.SqlDbType.Date, 255, "EndLearning");
dataAdapter.InsertCommand.Parameters.Add("@Category", System.Data.SqlDbType.Int, 255, "Category");
dataAdapter.InsertCommand.Parameters.Add("@Teacher", System.Data.SqlDbType.Int, 255, "Teacher");
// на удаление
dataAdapter.DeleteCommand = new SqlCommand("DELETE Groups WHERE ID = @ID", conn.getConnection(), tr.getTransaction());
dataAdapter.DeleteCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int, 255, "ID").SourceVersion = System.Data.DataRowVersion.Original;
dataAdapter.Update(dataSet, "Groups");
}
// сохранить изменения строки
public void Save(AutoschoolDataSet dataSet, AbstractConnection conn, AbstractTransaction tr)
{
dataAdapter = new SqlDataAdapter();
// на обновление
dataAdapter.UpdateCommand = new SqlCommand("UPDATE Students SET ID = @ID, Surname = @Surname, " +
"FirstName = @FirstName, PatronymicName = @PatronymicName, PhoneNumber = @PhoneNumber, " +
"Retraining = @Retraining, [Group] = @Group, CarrierUse = @CarrierUse, Photo = @Photo " +
"WHERE ID = @OldID", conn.getConnection(), tr.getTransaction());
dataAdapter.UpdateCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int, 255, "ID");
dataAdapter.UpdateCommand.Parameters.Add("@Surname", System.Data.SqlDbType.Text, 255, "Surname");
dataAdapter.UpdateCommand.Parameters.Add("@FirstName", System.Data.SqlDbType.Text, 255, "FirstName");
dataAdapter.UpdateCommand.Parameters.Add("@PatronymicName", System.Data.SqlDbType.Text, 255, "PatronymicName");
dataAdapter.UpdateCommand.Parameters.Add("@PhoneNumber", System.Data.SqlDbType.Text, 255, "PhoneNumber");
dataAdapter.UpdateCommand.Parameters.Add("@Retraining", System.Data.SqlDbType.Bit, 255, "Retraining");
dataAdapter.UpdateCommand.Parameters.Add("@Group", System.Data.SqlDbType.Int, 255, "Group");
dataAdapter.UpdateCommand.Parameters.Add("@CarrierUse", System.Data.SqlDbType.Int, 255, "CarrierUse");
dataAdapter.UpdateCommand.Parameters.Add("@Photo", System.Data.SqlDbType.Image, 2147483647, "Photo");
dataAdapter.UpdateCommand.Parameters.Add("@OldID", System.Data.SqlDbType.Int, 255, "ID").SourceVersion = System.Data.DataRowVersion.Original;
// на вставку
dataAdapter.InsertCommand = new SqlCommand("INSERT INTO Students (ID, Surname, FirstName, PatronymicName, " +
"PhoneNumber, Retraining, [Group], CarrierUse, Photo) VALUES (@ID, @Surname, @FirstName, @PatronymicName, " +
"@PhoneNumber, @Retraining, @Group, @CarrierUse, @Photo)", conn.getConnection(), tr.getTransaction());
dataAdapter.InsertCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int, 255, "ID");
dataAdapter.InsertCommand.Parameters.Add("@Surname", System.Data.SqlDbType.Text, 255, "Surname");
dataAdapter.InsertCommand.Parameters.Add("@FirstName", System.Data.SqlDbType.Text, 255, "FirstName");
dataAdapter.InsertCommand.Parameters.Add("@PatronymicName", System.Data.SqlDbType.Text, 255, "PatronymicName");
dataAdapter.InsertCommand.Parameters.Add("@PhoneNumber", System.Data.SqlDbType.Text, 255, "PhoneNumber");
dataAdapter.InsertCommand.Parameters.Add("@Retraining", System.Data.SqlDbType.Bit, 255, "Retraining");
dataAdapter.InsertCommand.Parameters.Add("@Group", System.Data.SqlDbType.Int, 255, "Group");
dataAdapter.InsertCommand.Parameters.Add("@CarrierUse", System.Data.SqlDbType.Int, 255, "CarrierUse");
dataAdapter.InsertCommand.Parameters.Add("@Photo", System.Data.SqlDbType.Image, 2147483647, "Photo");
// на удаление
dataAdapter.DeleteCommand = new SqlCommand("DELETE Students WHERE ID = @ID", conn.getConnection(), tr.getTransaction());
dataAdapter.DeleteCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int, 255, "ID").SourceVersion = System.Data.DataRowVersion.Original;
dataAdapter.Update(dataSet, "Students");
}
// сохранить изменения строки
public void Save(AutoschoolDataSet dataSet, AbstractConnection conn, AbstractTransaction tr)
{
dataAdapter = new SqlDataAdapter();
// на обновление
dataAdapter.UpdateCommand = new SqlCommand("UPDATE CarriersStatuses SET ID = @ID, Name = @Name " +
"WHERE ID = @OldID", conn.getConnection(), tr.getTransaction());
dataAdapter.UpdateCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int, 255, "ID");
dataAdapter.UpdateCommand.Parameters.Add("@Name", System.Data.SqlDbType.Text, 255, "Name");
dataAdapter.UpdateCommand.Parameters.Add("@OldID", System.Data.SqlDbType.Int, 255, "ID").SourceVersion = System.Data.DataRowVersion.Original;
// на вставку
dataAdapter.InsertCommand = new SqlCommand("INSERT INTO CarriersStatuses (ID, Name) VALUES (@ID, @Name)",
conn.getConnection(), tr.getTransaction());
dataAdapter.InsertCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int, 255, "ID");
dataAdapter.InsertCommand.Parameters.Add("@Name", System.Data.SqlDbType.Text, 255, "Name");
// на удаление
dataAdapter.DeleteCommand = new SqlCommand("DELETE CarriersStatuses WHERE ID = @ID", conn.getConnection(), tr.getTransaction());
dataAdapter.DeleteCommand.Parameters.Add("@ID", System.Data.SqlDbType.Int, 255, "ID").SourceVersion = System.Data.DataRowVersion.Original;
dataAdapter.Update(dataSet, "CarriersStatuses");
}
public void ReadByCarrierReplacementID_AND_BeginEndDates(AutoschoolDataSet dataSet, AbstractConnection conn, AbstractTransaction tr, int CarrierReplacementID, DateTime BeginDate, DateTime EndDate)
{
dataAdapter = new SqlDataAdapter();
dataAdapter.SelectCommand = new SqlCommand("SELECT * FROM ReplacementsCarriers WHERE CarrierReplacement = @CarrierReplacementID AND DateBeginReplacement >= @BeginDate AND DateBeginReplacement <= @EndDate", conn.getConnection(), tr.getTransaction());
dataAdapter.SelectCommand.Parameters.AddWithValue("@CarrierReplacementID", CarrierReplacementID);
dataAdapter.SelectCommand.Parameters.AddWithValue("@BeginDate", BeginDate);
dataAdapter.SelectCommand.Parameters.AddWithValue("@EndDate", EndDate);
dataAdapter.Fill(dataSet, "ReplacementsCarriers");
}
public void ReadReplacementsCarriersByLessonDateANDCarrierUseID(AutoschoolDataSet dataSet, AbstractConnection conn, AbstractTransaction tr, DateTime LessonDate, int CarrierUseID)
{
dataAdapter = new SqlDataAdapter();
string query = "SELECT * FROM ReplacementsCarriers " +
"WHERE CarrierUse=@CarrierUseID AND DateBeginReplacement <= @LessonDate AND DateEndReplacement >=@LessonDate";
dataAdapter.SelectCommand = new SqlCommand(query, conn.getConnection(), tr.getTransaction());
dataAdapter.SelectCommand.Parameters.AddWithValue("@LessonDate", LessonDate.ToShortDateString());
dataAdapter.SelectCommand.Parameters.AddWithValue("@CarrierUseID", CarrierUseID);
dataAdapter.Fill(dataSet, "ReplacementsCarriers");
}
public void ReadPracticeLessonsByCarrierID_AND_BeginEndDates(AutoschoolDataSet dataSet, AbstractConnection conn, AbstractTransaction tr, int CarrierID, DateTime BeginDate, DateTime EndDate)
{
dataAdapter = new SqlDataAdapter();
string query = /*"SELECT pl.ID, pl.Student, pl.AppointedDate, pl.AppointedTime, pl.FactDate, pl.FactTime " +
* "FROM PracticeLessons pl " +
* "INNER JOIN Students St ON pl.Student=St.ID " +
* "INNER JOIN CarriersUses CU ON St.CarrierUse=CU.ID " +
* "WHERE CU.Carrier = @CarrierID AND " +
* "@BeginDate <= IIF(pl.FactDate <> '01.01.0001', pl.FactDate, pl.AppointedDate) " +
* "@EndDate >= IIF(pl.FactDate <> '01.01.0001', pl.FactDate, pl.AppointedDate)";*/
/*"IF (pl.FactDate <> '01.01.0001') " +
* "THEN pl.FactDate >= @BeginDate AND pl.FactDate <= @EndDate " +
* "ELSE pl.AppointedDate >= @BeginDate AND pl.AppointedDate <= @EndDate";*/
@"SELECT pl.ID, pl.Student, pl.AppointedDate, pl.AppointedTime, pl.FactDate, pl.FactTime
FROM PracticeLessons pl
INNER JOIN Students St ON pl.Student=St.ID
INNER JOIN CarriersUses CU ON St.CarrierUse=CU.ID
WHERE CU.Carrier = @CarrierID AND
@BeginDate <= CASE
WHEN pl.FactDate <> '0001-01-01'
THEN pl.FactDate
ELSE pl.AppointedDate
END
AND
@EndDate >= CASE
WHEN pl.FactDate <> '0001-01-01'
THEN pl.FactDate
ELSE pl.AppointedDate
END";
dataAdapter.SelectCommand = new SqlCommand(query, conn.getConnection(), tr.getTransaction());
dataAdapter.SelectCommand.Parameters.AddWithValue("@CarrierID", CarrierID);
dataAdapter.SelectCommand.Parameters.AddWithValue("@BeginDate", BeginDate.ToShortDateString());
dataAdapter.SelectCommand.Parameters.AddWithValue("@EndDate", EndDate.ToShortDateString());
dataAdapter.Fill(dataSet, "PracticeLessons");
}
public void ReadCarriersByStatusName(AutoschoolDataSet dataSet, AbstractConnection conn, AbstractTransaction tr, string StatusName)
{
dataAdapter = new SqlDataAdapter();
string query = "SELECT Cr.ID, Cr.Brand, Cr.Model, Cr.StateNumber, Cr.Color, Cr.Transmission, Cr.Category, Cr.Status " +
"FROM Carriers Cr " +
"INNER JOIN CarriersStatuses CS ON Cr.Status=CS.ID " +
"WHERE CS.Name = @StatusName";
dataAdapter.SelectCommand = new SqlCommand(query, conn.getConnection(), tr.getTransaction());
dataAdapter.SelectCommand.Parameters.AddWithValue("@StatusName", StatusName);
dataAdapter.Fill(dataSet, "Carriers");
}
public void ReadCarriersByInstructorID(AutoschoolDataSet dataSet, AbstractConnection conn, AbstractTransaction tr, int InstructorID)
{
dataAdapter = new SqlDataAdapter();
string query = "SELECT Cr.ID, Cr.Brand, Cr.Model, Cr.StateNumber, Cr.Color, Cr.Transmission, Cr.Category, Cr.Status " +
"FROM Carriers Cr " +
"INNER JOIN CarriersUses CU ON Cr.ID=CU.Carrier " +
"WHERE CU.Instructor = @InstructorID";
dataAdapter.SelectCommand = new SqlCommand(query, conn.getConnection(), tr.getTransaction());
dataAdapter.SelectCommand.Parameters.AddWithValue("@InstructorID", InstructorID);
dataAdapter.Fill(dataSet, "Carriers");
}
public void ReadTheoryLessonsByTeacherID(AutoschoolDataSet dataSet, AbstractConnection conn, AbstractTransaction tr, int TeacherID)
{
dataAdapter = new SqlDataAdapter();
string query = "SELECT tl.ID, tl.Date, tl.Time, tl.Auditorium, tl.[Group] " +
"FROM TheoryLessons TL " +
"INNER JOIN Groups Gr ON tl.[Group]=Gr.ID " +
"WHERE Gr.Teacher = @TeacherID";
dataAdapter.SelectCommand = new SqlCommand(query, conn.getConnection(), tr.getTransaction());
dataAdapter.SelectCommand.Parameters.AddWithValue("@TeacherID", TeacherID);
dataAdapter.Fill(dataSet, "TheoryLessons");
}
public void ReadPracticeLessonsByCarrierID(AutoschoolDataSet dataSet, AbstractConnection conn, AbstractTransaction tr, int CarrierID)
{
dataAdapter = new SqlDataAdapter();
string query = "SELECT pl.ID, pl.Student, pl.AppointedDate, pl.AppointedTime, pl.FactDate, pl.FactTime " +
"FROM PracticeLessons pl " +
"INNER JOIN Students St ON pl.Student=St.ID " +
"INNER JOIN CarriersUses CU ON St.CarrierUse=CU.ID " +
"WHERE CU.Carrier = @CarrierID";
dataAdapter.SelectCommand = new SqlCommand(query, conn.getConnection(), tr.getTransaction());
dataAdapter.SelectCommand.Parameters.AddWithValue("@CarrierID", CarrierID);
dataAdapter.Fill(dataSet, "PracticeLessons");
}
public void ReadReplacementsCarriersByCarrierID_AND_Date(AutoschoolDataSet dataSet, AbstractConnection conn, AbstractTransaction tr, int CarrierID, DateTime Date)
{
dataAdapter = new SqlDataAdapter();
string query = "SELECT RC.ID, RC.CarrierUse, RC.CarrierReplacement, RC.DateBeginReplacement, RC.DateEndReplacement " +
"FROM ReplacementsCarriers RC " +
"INNER JOIN CarriersUses CU ON RC.CarrierUse=CU.ID " +
"WHERE CU.Carrier = @CarrierID AND @Date >= RC.DateBeginReplacement AND @Date <= RC.DateEndReplacement";
dataAdapter.SelectCommand = new SqlCommand(query, conn.getConnection(), tr.getTransaction());
dataAdapter.SelectCommand.Parameters.AddWithValue("@CarrierID", CarrierID);
dataAdapter.SelectCommand.Parameters.AddWithValue("@Date", Date);
dataAdapter.Fill(dataSet, "ReplacementsCarriers");
}
public void ReadPracticeLessonsByCarrierUseID_AND_DatesBeginEnd(AutoschoolDataSet dataSet, AbstractConnection conn, AbstractTransaction tr, int CarrierUseID, DateTime BeginDate, DateTime EndDate)
{
dataAdapter = new SqlDataAdapter();
string query = "SELECT pl.ID, pl.Student, pl.AppointedDate, pl.AppointedTime, pl.FactDate, pl.FactTime " +
"FROM PracticeLessons pl " +
"INNER JOIN Students St ON pl.Student=St.ID " +
"WHERE St.CarrierUse = @CarrierUseID AND " +
@"@BeginDate <= CASE
WHEN pl.FactDate <> '0001-01-01'
THEN pl.FactDate
ELSE pl.AppointedDate
END
AND
@EndDate >= CASE
WHEN pl.FactDate <> '0001-01-01'
THEN pl.FactDate
ELSE pl.AppointedDate
END";
dataAdapter.SelectCommand = new SqlCommand(query, conn.getConnection(), tr.getTransaction());
dataAdapter.SelectCommand.Parameters.AddWithValue("@CarrierUseID", CarrierUseID);
dataAdapter.SelectCommand.Parameters.AddWithValue("@BeginDate", BeginDate.ToShortDateString());
dataAdapter.SelectCommand.Parameters.AddWithValue("@EndDate", EndDate.ToShortDateString());
dataAdapter.Fill(dataSet, "PracticeLessons");
}
public void ReadByInstructorIdANDCategoryId(AutoschoolDataSet dataSet, AbstractConnection conn, AbstractTransaction tr, int InstructorID, int CategoryID)
{
dataAdapter = new SqlDataAdapter();
dataAdapter.SelectCommand = new SqlCommand("SELECT * FROM InstructorsCategories WHERE Instructor = @InstructorID AND Category = @CategoryID", conn.getConnection(), tr.getTransaction());
dataAdapter.SelectCommand.Parameters.AddWithValue("@InstructorID", InstructorID);
dataAdapter.SelectCommand.Parameters.AddWithValue("@CategoryID", CategoryID);
dataAdapter.Fill(dataSet, "InstructorsCategories");
}
public void ReadCarriersByServiceMasterID(DataSet dataSet, AbstractConnection conn, AbstractTransaction tr, int ServiceMasterID)
{
dataAdapter = new SqlDataAdapter();
string query = "SELECT Cr.ID, Cr.Brand, Cr.Model, Cr.StateNumber, Cr.Color, Tr.Transmission, Cat.Name AS [Category], CarSt.Name AS [Status], CarR.Work, CarR.BeginDate, CarR.EndDate " +
"FROM Carriers Cr " +
"INNER JOIN CarriersRepairs CarR ON Cr.ID=CarR.Carrier " +
"INNER JOIN Transmissions Tr ON Cr.Transmission=Tr.ID " +
"INNER JOIN CarriersStatuses CarSt ON Cr.Status=CarSt.ID " +
"INNER JOIN Categories Cat ON Cr.Category=Cat.ID " +
"WHERE CarR.Master = @ServiceMasterID";
dataAdapter.SelectCommand = new SqlCommand(query, conn.getConnection(), tr.getTransaction());
dataAdapter.SelectCommand.Parameters.AddWithValue("@ServiceMasterID", ServiceMasterID);
dataAdapter.Fill(dataSet, "RepairsOfServiceMaster");
}
public void ReadStudentsOfGroup(AutoschoolDataSet dataSet, AbstractConnection conn, AbstractTransaction tr, int ID)
{
dataAdapter = new SqlDataAdapter();
dataAdapter.SelectCommand = new SqlCommand("SELECT * FROM Students WHERE [Group] = @ID", conn.getConnection(), tr.getTransaction());
dataAdapter.SelectCommand.Parameters.AddWithValue("@ID", ID);
dataAdapter.Fill(dataSet, "Students");
}
public void ReadBusyCarriers(AutoschoolDataSet dataSet, AbstractConnection conn, AbstractTransaction tr, DateTime Date, TimeSpan Time, int LessonTime)
{
dataAdapter = new SqlDataAdapter();
/*string query = "SELECT Cr.ID, Cr.Brand, Cr.Model, Cr.StateNumber, Cr.Color, Cr.Transmission, Cr.Category, Cr.Status " +
* "FROM Carriers Cr " +
* "INNER JOIN CarriersUses CU ON Cr.ID=CU.Carrier " +
* "INNER JOIN Students St ON CU.ID=St.CarrierUse " +
* "INNER JOIN PracticeLessons PL ON St.ID=PL.Student " +
* "WHERE PL.AppointedDate = @Date AND PL.AppointedTime <= @Time AND DATEADD(MINUTE, @LessonTime, PL.AppointedTime) > @Time";*/
string query = @"SELECT Cr.ID, Cr.Brand, Cr.Model, Cr.StateNumber, Cr.Color, Cr.Transmission, Cr.Category, Cr.Status
FROM Carriers Cr
INNER JOIN CarriersUses CU ON Cr.ID=CU.Carrier
INNER JOIN Students St ON CU.ID=St.CarrierUse
INNER JOIN PracticeLessons PL ON St.ID=PL.Student
WHERE
@Date = CASE
WHEN PL.FactDate <> '0001-01-01'
THEN PL.FactDate
ELSE PL.AppointedDate
END
AND
@Time >= CASE
WHEN PL.FactDate <> '0001-01-01'
THEN PL.FactTime
ELSE PL.AppointedTime
END
AND
@Time < CASE
WHEN PL.FactDate <> '0001-01-01'
THEN DATEADD(MINUTE, @LessonTime, PL.FactTime)
ELSE DATEADD(MINUTE, @LessonTime, PL.AppointedTime)
END";
dataAdapter.SelectCommand = new SqlCommand(query, conn.getConnection(), tr.getTransaction());
dataAdapter.SelectCommand.Parameters.AddWithValue("@Date", Date);
dataAdapter.SelectCommand.Parameters.AddWithValue("@Time", Time);
dataAdapter.SelectCommand.Parameters.AddWithValue("@LessonTime", LessonTime);
dataAdapter.Fill(dataSet, "Carriers");
}
public void ReadByAuditoriumID(AutoschoolDataSet dataSet, AbstractConnection conn, AbstractTransaction tr, int AuditoriumID)
{
dataAdapter = new SqlDataAdapter();
dataAdapter.SelectCommand = new SqlCommand("SELECT * FROM TheoryLessons WHERE Auditorium = @AuditoriumID", conn.getConnection(), tr.getTransaction());
dataAdapter.SelectCommand.Parameters.AddWithValue("@AuditoriumID", AuditoriumID);
dataAdapter.Fill(dataSet, "TheoryLessons");
}
// прочитать таблицу
public void Read(AutoschoolDataSet dataSet, AbstractConnection conn, AbstractTransaction tr)
{
dataAdapter = new SqlDataAdapter();
dataAdapter.SelectCommand = new SqlCommand("SELECT * FROM Instructors", conn.getConnection(), tr.getTransaction());
dataAdapter.Fill(dataSet, "Instructors");
}
public void ReadCarriersUsesWithRepairingCarriers(DataSet dataSet, AbstractConnection conn, AbstractTransaction tr, DateTime BeginDate, DateTime EndDate)
{
dataAdapter = new SqlDataAdapter();
string query = "SELECT Cr.ID AS [CarrierID], Cr.Brand AS [Brand], Cr.Model AS [Model], Cr.StateNumber AS [StateNumber], " +
"CarR.BeginDate AS [BeginDate], CarR.EndDate AS [EndDate], " +
"CU.Instructor AS [InstructorID], (Instr.Surname + ' ' + Instr.FirstName + ' ' + Instr.PatronymicName) AS [InstructorName], " +
"CU.ID AS [CarrierUseID]" +
"FROM CarriersUses CU " +
"INNER JOIN Carriers Cr ON CU.Carrier=Cr.ID " +
"INNER JOIN CarriersRepairs CarR ON Cr.ID=CarR.Carrier " +
"INNER JOIN Instructors Instr ON CU.Instructor=Instr.ID " +
"WHERE (@BeginDate <= CarR.BeginDate AND CarR.BeginDate <= @EndDate) OR (@BeginDate <= CarR.EndDate AND CarR.EndDate <= @EndDate)";
dataAdapter.SelectCommand = new SqlCommand(query, conn.getConnection(), tr.getTransaction());
dataAdapter.SelectCommand.Parameters.AddWithValue("@BeginDate", BeginDate);
dataAdapter.SelectCommand.Parameters.AddWithValue("@EndDate", EndDate);
dataAdapter.Fill(dataSet, "CarrierUsesWithRepairingCarriers");
}
public void ReadCategoriesOfInstructor(AutoschoolDataSet dataSet, AbstractConnection conn, AbstractTransaction tr, int InstructorID)
{
dataAdapter = new SqlDataAdapter();
string query = "SELECT Cat.ID, Cat.Name " +
"FROM Categories Cat " +
"INNER JOIN InstructorsCategories InstrCat ON Cat.ID=InstrCat.Category " +
"WHERE InstrCat.Instructor = @InstructorID";
dataAdapter.SelectCommand = new SqlCommand(query, conn.getConnection(), tr.getTransaction());
dataAdapter.SelectCommand.Parameters.AddWithValue("@InstructorID", InstructorID);
dataAdapter.Fill(dataSet, "Categories");
}
public void ReadByCarrierReplacementID(AutoschoolDataSet dataSet, AbstractConnection conn, AbstractTransaction tr, int CarrierReplacementID)
{
dataAdapter = new SqlDataAdapter();
dataAdapter.SelectCommand = new SqlCommand("SELECT * FROM ReplacementsCarriers WHERE CarrierReplacement = @CarrierReplacementID", conn.getConnection(), tr.getTransaction());
dataAdapter.SelectCommand.Parameters.AddWithValue("@CarrierReplacementID", CarrierReplacementID);
dataAdapter.Fill(dataSet, "ReplacementsCarriers");
}
public void ReadRepairingCarriers(AutoschoolDataSet dataSet, AbstractConnection conn, AbstractTransaction tr, DateTime Date)
{
dataAdapter = new SqlDataAdapter();
string query = "SELECT DISTINCT Cr.ID, Cr.Brand, Cr.Model, Cr.StateNumber, Cr.Color, Cr.Transmission, Cr.Category, Cr.Status " +
"FROM Carriers Cr " +
"INNER JOIN CarriersRepairs CarR ON Cr.ID=CarR.Carrier " +
"WHERE CarR.BeginDate <= @Date AND CarR.EndDate >= @Date";
dataAdapter.SelectCommand = new SqlCommand(query, conn.getConnection(), tr.getTransaction());
dataAdapter.SelectCommand.Parameters.AddWithValue("@Date", Date);
dataAdapter.Fill(dataSet, "Carriers");
}
