Разработка базы данных для предметной области «Столовая» и приложения для ведения базы данных

Add("@cpProductID", SqlDbType.Int);command.Parameters["@cpProductID"].Value = productID;command.Parameters.Add("@cpQuantity", SqlDbType.Float);command.Parameters["@cpQuantity"].Value = quantity;command.ExecuteNonQuery(); }publicvoidUpdateProductItem(intrecordID, float quantity) {SqlCommand command = newSqlCommand("UPDATE [DishCompositions] SET [Quantity] = @cpQuantity WHERE ID = @cpRecID)", conn);command.Parameters.Add("@cpRecID", SqlDbType.Int);command.Parameters["@cpRecID"].Value = recordID; command.Parameters.Add("@cpQuantity", SqlDbType.Float);command.Parameters["@cpQuantity"].Value = quantity;command.ExecuteNonQuery(); }publicvoidDeleteProductItem(intrecordID) {SqlCommand command = newSqlCommand("DELETE FROM [DishCompositions] WHERE ID = @cpRecID", conn);command.Parameters.Add("@cpRecID", SqlDbType.Int);command.Parameters["@cpRecID"].Value = recordID; command.ExecuteNonQuery(); }publicDataTableGetDishComposition(intdishID) {DataSetdataSet = newDataSet();newSqlDataAdapter(String.Format("select dc.ID, cd.DishName as DishName, p.ProductName, dc.Quantity from DishCompositions dc left join CanteenDishes cd on dc.ID=cd.ID left join Products p on dc.ProductID=p.ID where dc.DishID='{0}' order by DishName", dishID), conn).Fill(dataSet);returndataSet.Tables[0]; }publicDataTableGetWeekMenu() {DataSetdataSet = newDataSet();newSqlDataAdapter("select wm.ID, wm.DishID, cd.DishName, cd.Description from WeekMenuwm left join CanteenDishes cd on wm.DishID=cd.ID order by dishname", conn).Fill(dataSet);returndataSet.Tables[0]; }publicvoidAddDishToWeekMenu(intdishID) {SqlCommand command = newSqlCommand("INSERT INTO [WeekMenu] ([DishID]) VALUES (@cpDishID)", conn);command.Parameters.Add("@cpDishID", SqlDbType.Int);command.Parameters["@cpDishID"].Value = dishID; command.ExecuteNonQuery(); }publicvoidRemoveFromWeekMenu(intrecID) {SqlCommand command = newSqlCommand("DELETE FROM [WeekMenu] WHERE ID = @cpRecID)", conn);command.Parameters.Add("@cpRecID", SqlDbType.Int);command.Parameters["@cpRecID"].Value = recID;command.ExecuteNonQuery(); } }}OrdersController.csusing System;usingSystem.Collections.Generic;usingSystem.Data;usingSystem.Data.SqlClient;usingSystem.Linq;usingSystem.Text;usingSystem.Threading.Tasks;namespaceCanteenManagement{internalclassOrdersController {SqlConnection conn = null;publicOrdersController(SqlConnection conn) {this.conn = conn; }publicDataTableFill() {DataSetdataSet = newDataSet();newSqlDataAdapter("select o.ID, o.DishID, cd.DishName, o.OrderDate, o.CompleteDate, u.ID as UserID, (u.Name + ' ' +u.Surname) as FullName from orders o left join CanteenDishes cd on o.DishID=cd.ID left join Users u on o.UserID=u.ID order by OrderDate", conn).Fill(dataSet);returndataSet.Tables[0]; }publicDataRowGetItemById(int id) {DataSetdataSet = newDataSet();newSqlDataAdapter("select o.ID, o.DishID, cd.DishName, o.OrderDate from orders o left join CanteenDishes cd on o.DishID=cd.ID left join Users u on o.UserID=u.ID where o.id='{0}'", conn).Fill(dataSet);returndataSet.Tables[0].Rows[0]; }publicvoidInsert(intdishID, intuserID) {SqlCommand command = newSqlCommand("INSERT INTO [Orders] ([DishID],[OrderDate],[UserID]) VALUES (@cpDishID, CURRENT_TIMESTAMP, @cpUserID)", conn);command.Parameters.Add("@cpDishID", SqlDbType.Int);command.Parameters["@cpDishID"].Value = dishID;command.Parameters.Add("@cpUserID", SqlDbType.Int);command.Parameters["@cpUserID"].Value = userID;command.ExecuteNonQuery(); }publicvoidDelete(int id) {SqlCommand command = newSqlCommand("DELETE FROM [Orders] WHERE ID =@cpID", conn);command.Parameters.Add("@cpID", SqlDbType.Int);command.Parameters["@cpID"].Value = id;command.ExecuteNonQuery(); }publicvoidCompleteOrder(intorderID) {SqlCommand command = newSqlCommand("UPDATE [Orders] set CompleteDate=CURRENT_TIMESTAMP WHERE ID =@cpID", conn);command.Parameters.Add("@cpID", SqlDbType.Int);command.Parameters["@cpID"].Value = orderID;command.ExecuteNonQuery(); } }}ProductsController.csusing System;usingSystem.Collections.Generic;usingSystem.Data.SqlClient;usingSystem.Data;usingSystem.Linq;usingSystem.Text;usingSystem.Threading.Tasks;namespaceCanteenManagement{internalclassProductsController {SqlConnection conn = null;publicProductsController(SqlConnection conn) {this.conn = conn; }publicDataTableFill() {DataSetdataSet = newDataSet();newSqlDataAdapter("select * from products order by productname", conn).Fill(dataSet);returndataSet.Tables[0]; }publicDataRowGetItemById(int id) {DataSetdataSet = newDataSet();newSqlDataAdapter("select * from products where id='"+id+"'", conn).Fill(dataSet);returndataSet.Tables[0].Rows[0]; }publicvoidInsert(string name) {SqlCommand command = newSqlCommand("INSERT INTO [Products] ([ProductName]) VALUES (@cpName)", conn);command.Parameters.Add("@cpName", SqlDbType.VarChar); command.Parameters["@cpName"].Value = name; command.ExecuteNonQuery(); }publicvoidDelete(int id) {SqlCommand command = newSqlCommand("DELETE FROM Products WHERE ID =@cpID", conn);command.Parameters.Add("@cpID", SqlDbType.Int);command.Parameters["@cpID"].Value = id;command.ExecuteNonQuery(); }publicvoidUpdate(int id, string name, float quantity) {SqlCommand command = newSqlCommand("UPDATE [Products] SET [ProductName] = @cpName ,[CurrentQuantity] = @cpQuantity WHERE id=@cpID", conn);command.Parameters.Add("@cpName", SqlDbType.VarChar);command.Parameters["@cpName"].Value = name;command.Parameters.Add("@cpQuantity", SqlDbType.Float);command.Parameters["@cpQuantity"].Value = quantity;command.Parameters.Add("@cpID", SqlDbType.Int);command.Parameters["@cpID"].Value = id;command.ExecuteNonQuery(); }publicvoidConsumption(int id, float quantity, string comment) {SqlCommand command = newSqlCommand("UPDATE [Products] SET [CurrentQuantity] = [CurrentQuantity] - @cpQuantity, Comment = @cpComment WHERE id=@cpID", conn);command.Parameters.Add("@cpComment", SqlDbType.VarChar);command.Parameters["@cpComment"].Value = comment;command.Parameters.Add("@cpQuantity", SqlDbType.Float);command.Parameters["@cpQuantity"].Value = quantity;command.Parameters.Add("@cpID", SqlDbType.Int);command.Parameters["@cpID"].Value = id;command.ExecuteNonQuery(); }publicvoidPurchase(int id, float quantity, float price, string comment) {SqlCommand command = newSqlCommand("UPDATE [Products] SET [CurrentQuantity] = [CurrentQuantity] + @cpQuantity WHERE id=@cpID", conn);command.Parameters.Add("@cpID", SqlDbType.Int);command.Parameters["@cpID"].Value = id;command.Parameters.Add("@cpQuantity", SqlDbType.Float);command.Parameters["@cpQuantity"].Value = quantity;command.ExecuteNonQuery(); command = newSqlCommand("INSERT INTO [PurchaseProducts] ([ProductID],[Quantity],[Price],[PurchaseDate],[Comment]) VALUES (@cpProductID, @cpQuantity, @cpPrice, CURRENT_TIMESTAMP, @cpComment)", conn);command.Parameters.Add("@cpProductID", SqlDbType.Int);command.Parameters["@cpProductID"].Value = id;command.Parameters.Add("@cpQuantity", SqlDbType.Float);command.Parameters["@cpQuantity"].Value = quantity;command.Parameters.Add("@cpPrice", SqlDbType.Float);command.Parameters["@cpPrice"].Value = price;command.Parameters.Add("@cpComment", SqlDbType.VarChar);command.Parameters["@cpComment"].Value = comment;command.ExecuteNonQuery(); } }}UsersController.csusing System;usingSystem.Collections.Generic;usingSystem.Data;usingSystem.Data.SqlClient;usingSystem.Linq;usingSystem.Text;usingSystem.Threading.Tasks;namespaceCanteenManagement{internalclassUsersController {SqlConnection conn = null;publicUsersController(SqlConnection conn) {this.conn = conn; }publicDataTableFill() {DataSetdataSet = newDataSet();newSqlDataAdapter("select *, ([Surname] + ' ' +[Name]) as FullName, stuff(" +"(" +"SELECT ',' + [Role] FROM UserRoles WHERE UserRoles.UserID = u.ID FOR XML PATH('')" +"), 1, 1, '') as Roles from users u left join userrolesur on u.ID = ur.UserID order by surname, name, email", conn).Fill(dataSet);returndataSet.Tables[0]; }publicvoidInsert(string login, string password, string name, string surname, string address, string phone, string email) {SqlCommand command = newSqlCommand("INSERT INTO [dbo].[Users] ([Name],[Surname],[Address],[Phone],[Email],[Login],[Password]) VALUES (@cpName, @cpSurname, @cpAddress, @cpPhone, @cpEmail, @cpLogin, @cpPassword)", conn);command.Parameters.Add("@cpName", SqlDbType.VarChar);command.Parameters.Add("@cpSurname", SqlDbType.VarChar);command.Parameters.Add("@cpAddress", SqlDbType.VarChar);command.Parameters.Add("@cpPhone", SqlDbType.VarChar);command.Parameters.Add("@cpEmail", SqlDbType.VarChar);command.Parameters.Add("@cpLogin", SqlDbType.VarChar);command.Parameters.Add("@cpPassword", SqlDbType.VarChar);command.Parameters["@cpName"].Value = name;command.Parameters["@cpSurname"].Value = surname;command.Parameters["@cpAddress"].Value = address;command.Parameters["@cpPhone"].Value = phone;command.Parameters["@cpEmail"].Value = email;command.Parameters["@cpLogin"].Value = login;command.Parameters["@cpPassword"].Value = password;command.ExecuteNonQuery(); }publicvoidDelete(int id) {SqlCommand command = newSqlCommand("DELETE FROM Users WHERE ID =@cpID", conn);command.Parameters.Add("@cpID", SqlDbType.Int);command.Parameters["@cpID"].Value = id;command.ExecuteNonQuery(); }public List GetUserRoles(int id) {DataSetdataSet = newDataSet();newSqlDataAdapter(String.Format("select Role from userroles where userid='{0}'", id), conn).Fill(dataSet); List list = new List();for (inti = 0; i < dataSet.Tables[0].Rows.Count; i++) {list.Add(int.Parse(dataSet.Tables[0].Rows[i].ItemArray[0].ToString())); }return list; }publicvoidAddUserRole(int id, int code){thrownewNotImplementedException(); } }}