// сохранить изменения строки 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"); }