Написать программу на C# Form

ListRequest-- Удаление временной таблицы и создание окончательной структуры представленияDROP TABLE IF EXISTS "ListRequest";CREATE VIEW dbo.ListRequestASSELECT R1.ApplicantId, R1.DateRequest, dbo.AppStatus.AppStatusName, R1.Summa, R1.NameCredit, R1.PercentCredit, R1.MinTime, R1.MaxTime, R1.CreditTimeFROM dbo.AppStatus INNER JOIN (SELECT dbo.Requests.ApplicantId, dbo.Requests.DateRequest, dbo.Requests.AppStatusId, dbo.Requests.Summa, dbo.Credits.NameCredit, dbo.Credits.PercentCredit, dbo.Credits.MinTime, dbo.Credits.MaxTime, dbo.Requests.CreditTime FROM dbo.Credits INNER JOINdbo.Requests ON dbo.Requests.CreditId = dbo.Credits.Id) AS R1 ON R1.AppStatusId = dbo.AppStatus.Id;-- Дамп структуры для представление dbBankCredit.RequestForScoring-- Удаление временной таблицы и создание окончательной структуры представленияDROP TABLE IF EXISTS "RequestForScoring";Create view RequestForScoring asSelect Applicants.Id,DateRequest,AppStatusName,Summa,NameCredit,PercentCredit,MinTime,MaxTime,CreditTime,FIOId,Gender,Birthday,DATEDIFF(YEAR, Birthday, GETDATE()) as Age,PassportId,Phone,EducationId,PlaceWork,LastWorkExperience,AllWorkExperience,SNILS,INN,Address,FactAddress,Salary,MaritalStatusId,Children from ListRequestinner join Applicantson ListRequest.ApplicantId=Applicants.Id;/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;Приложение 2using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Data.SqlClient;using System.Drawing;using System.IO;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;namespace BankCreditApp{publicpartialclassAutorizationFrm : Form {// строка соединения с базой данных// Создаем и открываем соединение с MS SQL Server ...publicstaticstring ConnStr = "";publicstaticstring id = "";publicstatic SqlConnection conn;publicAutorizationFrm() {InitializeComponent(); label2.Visible = false; label3.Visible = false; txtBoxLogin.Visible = false; txtBoxPassword.Visible = false; }privatevoid btnExit_Click(object sender, EventArgs e) {this.Close(); }privatevoid btnStart_Click(object sender, EventArgs e) {if (cmbRole.SelectedItem == "Гость") {this.Hide();//MessageBox.Show("Здесь будет вызов формы на подачу заявки");RequestAdd reqBD = newRequestAdd();reqBD.ShowDialog();this.Show();//this.Close(); }else {//MessageBox.Show("Здесь будет проверка пары логин/пароль");if (cmbRole.SelectedItem == "Начальник отдела") {this.Hide();//MessageBox.Show("Здесь будет вызов формы на подачу заявки");ScoringFrm scrFrm = newScoringFrm();scrFrm.ShowDialog();this.Show();//this.Close(); } } }privatevoid AutorizationFrm_Load(object sender, EventArgs e) {using (StreamReader sr = new StreamReader("BDConnect.ini")) {while (sr.Peek() >= 0) { ConnStr = @sr.ReadLine();} }// Созданиеподключенияconn = new SqlConnection(ConnStr);try {// Открываем подключение conn.Open();//Закрываем подключение conn.Close(); }catch (SqlException ex){MessageBox.Show(ex.Message); } }privatevoid cmbRole_SelectedIndexChanged(object sender, EventArgs e) {if (cmbRole.SelectedItem == "Гость") { label2.Visible = false; label3.Visible = false; txtBoxLogin.Visible = false; txtBoxPassword.Visible = false; }else { label2.Visible = true; label3.Visible = true; txtBoxLogin.Visible = true; txtBoxPassword.Visible = true; } } }}using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Data.SqlClient;using System.Drawing;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;namespace BankCreditApp{publicpartialclassRequestAdd : Form { SqlDataAdapter da; SqlCommand cmd; DataSet ds;publicRequestAdd() {InitializeComponent();//подключаемсякбазеданныхAutorizationFrm.conn.Open();//заполняем комбо с образованиемSqlCommand cmd1 = newSqlCommand("Select * From Education", AutorizationFrm.conn); DataTable tbl1 = newDataTable(); SqlDataAdapter da = newSqlDataAdapter(cmd1);da.Fill(tbl1);this.cmbEducation.DataSource = tbl1;this.cmbEducation.DisplayMember = "NameEducation";// столбецдляотображенияthis.cmbEducation.ValueMember = "Id";//столбецс idthis.cmbEducation.SelectedIndex = -1;AutorizationFrm.conn.Close();//заполняем комбо с семейным положением AutorizationFrm.conn.Open();SqlCommand cmd2 = newSqlCommand("Select * From MartialStatus", AutorizationFrm.conn); DataTable tbl2 = newDataTable(); SqlDataAdapter da1 = newSqlDataAdapter(cmd2);da1.Fill(tbl2);this.cmbMartialStatus.DataSource = tbl2;this.cmbMartialStatus.DisplayMember = "MartialStatus";// столбецдляотображенияthis.cmbMartialStatus.ValueMember = "Id";//столбецс idthis.cmbMartialStatus.SelectedIndex = -1;AutorizationFrm.conn.Close();//заполняем комбо с кредитамиAutorizationFrm.conn.Open(); SqlCommand cmd3 = newSqlCommand("Select Id,NameCredit,PercentCredit,MinTime,MaxTime From Credits", AutorizationFrm.conn); DataTable tbl3 = newDataTable(); SqlDataAdapter da2 = newSqlDataAdapter(cmd3);da2.Fill(tbl3);this.cmbTypeCredit.DataSource = tbl3;this.cmbTypeCredit.DisplayMember = "NameCredit";// столбецдляотображенияthis.cmbTypeCredit.ValueMember = "Id";//столбецс idthis.cmbTypeCredit.SelectedIndex = -1;//this.dataGridView1.DataSource = tbl3;//закрываем подключение к базе данных AutorizationFrm.conn.Close();}privatevoid btnExit_Click(object sender, EventArgs e) {this.Close(); }privatevoid cmbTypeCredit_SelectedIndexChanged(object sender, EventArgs e) {string IdCredit= "0";if (cmbTypeCredit.SelectedIndex != -1&& cmbTypeCredit.SelectedValue.ToString()!="System.Data.DataRowView") { IdCredit = cmbTypeCredit.SelectedValue.ToString();//подключаемсякбазеданных//AutorizationFrm.conn.Open();//заполняемтаблицускредитами SqlCommand cmd = newSqlCommand("Select PercentCredit,MinTime,MaxTime From Credits WHERE Id=" + IdCredit, AutorizationFrm.conn); DataTable tbl = newDataTable(); SqlDataAdapter da = newSqlDataAdapter(cmd);da.Fill(tbl);this.dataGridView1.DataSource = tbl;//закрываем подключение к базе данных AutorizationFrm.conn.Close(); } }privatevoid btnAddRequest_Click(object sender, EventArgs e) {int maxIdFIO;int maxIdPassport;int maxIdAppl;if (txtBoxPhone.Text.Length == 11 && txtBoxPlaceWork.Text.Length > 0 && txtBoxLastWorkExp.Text.Length > 0 && txtBoxAllWorkExp.Text.Length > 0 && txtBoxAddress.Text.Length > 0 && txtBoxFactAddress.Text.Length > 0 && txtBoxSalary.Text.Length > 0 && txtBoxChildren.Text.Length > 0 && txtBoxCreditSum.Text.Length > 0 && txtBoxCreditTime.Text.Length > 0 && cmbEducation.SelectedIndex > -1 && cmbGender.SelectedIndex > -1 && cmbMartialStatus.SelectedIndex > -1 && cmbTypeCredit.SelectedIndex > -1) {if (txtName.Text.Length != 0 && txtPatronymic.Text.Length != 0 && txtSurname.Text.Length != 0&& txtBoxSeriosPassport.Text.Length == 4 && txtBoxNumberPassport.Text.Length == 6 && txtBoxPlaceIssue.Text.Length != 0 && txtBoxCodeUnit.Text.Length == 7) {AutorizationFrm.conn.Open();string Name1 = txtName.Text;string Name2 = txtPatronymic.Text;string Surname = txtSurname.Text;string sqlExpression = "INSERT INTO FIO (FirstName, Patronymic, Surname) VALUES (@Name1, @Name2, @Surname)"; SqlCommand command = newSqlCommand(sqlExpression, AutorizationFrm.conn);// создаемпараметрдляимени SqlParameter name1Param = newSqlParameter("@Name1", Name1);// добавляемпараметрккомандеcommand.Parameters.Add(name1Param); SqlParameter name2Param = newSqlParameter("@Name2", Name2);// добавляемпараметрккомандеcommand.Parameters.Add(name2Param); SqlParameter SurnameParam = newSqlParameter("@Surname", Surname);// добавляемпараметрккомандеcommand.Parameters.Add(SurnameParam);int num = command.ExecuteNonQuery(); SqlCommand cmd1 = newSqlCommand("Select * From FIO", AutorizationFrm.conn); DataTable tbl1 = newDataTable(); SqlDataAdapter da = newSqlDataAdapter(cmd1);da.Fill(tbl1);var maxId = tbl1.AsEnumerable().Max(row => row["Id"]);//MessageBox.Show($"Добавлено объектов: {number}");//MessageBox.Show($"Максимальный Id: {maxId}"); maxIdFIO = Convert.ToInt32(maxId);//паспортstring series = txtBoxSeriosPassport.Text;string number = txtBoxNumberPassport.Text;string placeIssue = txtBoxPlaceIssue.Text;string dateIssue = dTPDateIssue.Value.ToString("yyyy-MM-dd");string codeUnit = txtBoxCodeUnit.Text;string sqlExpression1 = "INSERT INTO Passports (Series, Number, PlaceIssue, DateIssue, CodeUnit) VALUES (@series, @number, @placeIssue, @dateIssue, @codeUnit)"; SqlCommand command1 = newSqlCommand(sqlExpression1, AutorizationFrm.conn);// создаемпараметрдляимени SqlParameter seriesParam = newSqlParameter("@series", series);// добавляемпараметрккомандеcommand1.Parameters.Add(seriesParam); SqlParameter numberParam = newSqlParameter("@number", number);// добавляемпараметрккомандеcommand1.Parameters.Add(numberParam); SqlParameter placeIssueParam = newSqlParameter("@placeIssue", placeIssue);// добавляемпараметрккомандеcommand1.Parameters.Add(placeIssueParam); SqlParameter dateIssueParam = newSqlParameter("@dateIssue", dateIssue);// добавляемпараметрккомандеcommand1.Parameters.Add(dateIssueParam); SqlParameter codeUnitParam = newSqlParameter("@codeUnit", codeUnit);// добавляемпараметрккомандеcommand1.Parameters.Add(codeUnitParam);num = command1.ExecuteNonQuery(); SqlCommand cmd2 = newSqlCommand("Select * From Passports", AutorizationFrm.conn); DataTable tbl2 = newDataTable(); SqlDataAdapter da1 = newSqlDataAdapter(cmd2);da1.Fill(tbl2);maxId = tbl2.AsEnumerable().Max(row => row["Id"]);//MessageBox.Show($"Добавлено объектов: {number}");//MessageBox.Show($"Максимальный Id: {maxId}"); maxIdPassport = Convert.ToInt32(maxId);//заявительstring phone = txtBoxPhone.Text;string placeWork = txtBoxPlaceWork.Text;float lastWorkExp = (float)Convert.ToDouble(txtBoxLastWorkExp.Text);float allWorkExp = (float)Convert.ToDouble(txtBoxAllWorkExp.Text);string address = txtBoxAddress.Text;string factAddress = txtBoxFactAddress.Text;float salary = (float)Convert.ToDouble(txtBoxSalary.Text);int children = Convert.ToInt32(txtBoxChildren.Text);float sumCredit = (float)Convert.ToDouble(txtBoxCreditSum.Text);int creditTime = Convert.ToInt32(txtBoxCreditTime.Text); DateTime birthday = dTPBirthday.Value;int educationId = Convert.ToInt32(cmbEducation.SelectedValue.ToString());int maritalStatusId = Convert.ToInt32(cmbMartialStatus.SelectedValue.ToString());int typeCreditId = Convert.ToInt32(cmbTypeCredit.SelectedValue.ToString());string gender = cmbGender.SelectedItem.ToString();string snils = txtBoxSNILS.Text;string inn = txtBoxINN.Text; DateTime dateRequest = DateTime.Today;//"FIOId", "Gender", "Birthday", "Phone", "EducationId", "PlaceWork", "LastWorkExperience", "AllWorkExperience", "PassportId", "SNILS", "INN", "Address", "FactAddress", "Salary", "MaritalStatusId", "Children"string sqlExpression2 = "INSERT INTO Applicants (FIOId, Gender, Birthday, Phone, EducationId, PlaceWork, LastWorkExperience, AllWorkExperience, PassportId,SNILS,INN,Address,FactAddress,Salary,MaritalStatusId,Children)"; sqlExpression2 += "VALUES(@FIOId, @gender, @birthday, @phone, @educationId, @placeWork,@lastWorkExp,@allWorkExp,@passportId,@snils,@inn,@address,@factAddress,@salary,@maritalStatusId,@children)"; SqlCommand command2 = newSqlCommand(sqlExpression2, AutorizationFrm.conn); SqlParameter fioIdParam = newSqlParameter("@FIOId", maxIdFIO);// добавляемпараметрккомандеcommand2.Parameters.Add(fioIdParam); SqlParameter genderParam = newSqlParameter("@gender", gender);// добавляемпараметрккомандеcommand2.Parameters.Add(genderParam); SqlParameter birthdayParam = newSqlParameter("@birthday", birthday);// добавляемпараметрккомандеcommand2.Parameters.Add(birthdayParam);// создаемпараметрдляимени SqlParameter phoneParam = newSqlParameter("@phone", phone);// добавляемпараметрккомандеcommand2.Parameters.Add(phoneParam); SqlParameter educationIdParam = newSqlParameter("@educationId", educationId);// добавляемпараметрккомандеcommand2.Parameters.Add(educationIdParam); SqlParameter placeWorkParam = newSqlParameter("@placeWork", placeWork);// добавляемпараметрккомандеcommand2.Parameters.Add(placeWorkParam); SqlParameter lastWorkExpParam = newSqlParameter("@lastWorkExp", lastWorkExp);// добавляемпараметрккомандеcommand2.Parameters.Add(lastWorkExpParam); SqlParameter allWorkExpParam = newSqlParameter("@allWorkExp", allWorkExp);command2.Parameters.Add(allWorkExpParam); SqlParameter passportIdParam = newSqlParameter("@passportId", maxIdPassport);// добавляемпараметрккомандеcommand2.Parameters.Add(passportIdParam); SqlParameter snilsParam = newSqlParameter("@snils", snils);// добавляемпараметрккомандеcommand2.Parameters.Add(snilsParam); SqlParameter innParam = newSqlParameter("@inn", inn);// добавляемпараметрккомандеcommand2.Parameters.Add(innParam); SqlParameter addressParam = newSqlParameter("@address", address);// добавляемпараметрккомандеcommand2.Parameters.Add(addressParam); SqlParameter factAddressParam = newSqlParameter("@factAddress", factAddress);// добавляемпараметрккомандеcommand2.Parameters.Add(factAddressParam);//@salary,@maritalStatusId,@children SqlParameter salaryParam = newSqlParameter("@salary", salary);// добавляемпараметрккомандеcommand2.Parameters.Add(salaryParam); SqlParameter maritalStatusIdParam = newSqlParameter("@maritalStatusId", maritalStatusId);// добавляемпараметрккомандеcommand2.Parameters.Add(maritalStatusIdParam); SqlParameter childrenParam = newSqlParameter("@children", children);// добавляемпараметрккомандеcommand2.Parameters.Add(childrenParam);num = command2.ExecuteNonQuery(); SqlCommand cmd3 = newSqlCommand("Select * From Applicants", AutorizationFrm.conn); DataTable tbl3 = newDataTable(); SqlDataAdapter da2 = newSqlDataAdapter(cmd3);da2.Fill(tbl3);maxId = tbl3.AsEnumerable().Max(row => row["Id"]);//MessageBox.Show($"Добавлено объектов: {number}");//MessageBox.Show($"Максимальный Id: {maxId}");maxIdAppl= Convert.ToInt32(maxId); SqlCommand cmd4 = newSqlCommand("Select * From Requests", AutorizationFrm.conn); DataTable tbl4 = newDataTable(); SqlDataAdapter da3 = newSqlDataAdapter(cmd4);da3.Fill(tbl4);var maxIdReq = tbl4.AsEnumerable().Max(row => row["Id"]);// "Requests"("Id", "ApplicantId", "DateRequest", "AppStatusId", "Summa", "CreditId", "CreditTime") VALUESstring sqlExpression3 = "INSERT INTO Requests (Id, ApplicantId, DateRequest, AppStatusId, Summa, CreditId, CreditTime)"; sqlExpression3 += "VALUES(@id, @applicantId, @dateRequest, @appStatusId, @summa, @creditId, @creditTime)"; SqlCommand command3 = newSqlCommand(sqlExpression3, AutorizationFrm.conn); SqlParameter IdParam = newSqlParameter("@id", Convert.ToInt32(maxIdReq)+1);// добавляемпараметрккомандеcommand3.Parameters.Add(IdParam); SqlParameter applicantIdParam = newSqlParameter("@applicantId", maxIdAppl);// добавляемпараметрккомандеcommand3.Parameters.Add(applicantIdParam); SqlParameter dateRequestParam = newSqlParameter("@dateRequest", dateRequest);// добавляемпараметрккомандеcommand3.Parameters.Add(dateRequestParam); SqlParameter appStatusIdParam = newSqlParameter("@appStatusId", 3);// добавляемпараметрккомандеcommand3.Parameters.Add(appStatusIdParam);// создаемпараметрдляимени SqlParameter summaParam = newSqlParameter("@summa", sumCredit);// добавляемпараметрккомандеcommand3.Parameters.Add(summaParam); SqlParameter creditIdParam = newSqlParameter("@creditId", typeCreditId);// добавляемпараметрккомандеcommand3.Parameters.Add(creditIdParam); SqlParameter creditTimeParam = newSqlParameter("@creditTime", creditTime);// добавляемпараметрккомандеcommand3.Parameters.Add(creditTimeParam);num = command3.ExecuteNonQuery(); cmd4 = newSqlCommand("Select * From Requests", AutorizationFrm.conn); tbl4 = newDataTable(); da3 = newSqlDataAdapter(cmd4);da3.Fill(tbl4);maxIdReq = tbl4.AsEnumerable().Max(row => row["Id"]);MessageBox.Show($"Запомните номер Вашей заявки: {maxIdReq}");AutorizationFrm.conn.Close();this.Close();}else { MessageBox.Show("Заполните ФИО и паспортные данные полностью!");return; } }else { MessageBox.Show("Заполните данные полностью!");return; } }privatevoid cmbEducation_SelectedIndexChanged(object sender, EventArgs e) {//if (cmbEducation.SelectedIndex != -1 && cmbEducation.SelectedValue.ToString() != "System.Data.DataRowView")// MessageBox.Show(cmbEducation.SelectedValue.ToString()); }privatevoid btnGetStatusRequest_Click(object sender, EventArgs e) { SqlCommand cmd1 = newSqlCommand("Select AppStatusId From Requests Where Id = "+ txtBoxRequestId.Text, AutorizationFrm.conn); DataTable tbl1 = newDataTable(); SqlDataAdapter da = newSqlDataAdapter(cmd1);da.Fill(tbl1);var statId = tbl1.AsEnumerable().Max(row => row["AppStatusId"]);if (Convert.ToInt32(statId) == 3) {MessageBox.Show("Вашазаявкаврассмотрении"); }if (Convert.ToInt32(statId) == 2) {MessageBox.Show("Вашазаявканеодобрена"); }if (Convert.ToInt32(statId) == 1) {MessageBox.Show("Вашазаявкаодобрена"); }//MessageBox.Show($"Добавленообъектов: {number}");//MessageBox.Show($"Максимальный Id: {maxId}"); } }}using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Threading.Tasks;using System.Windows.Forms;using System.Data.SqlClient;namespace BankCreditApp{publicpartialclassScoringFrm : Form {publicScoringFrm() {InitializeComponent(); SqlCommand cmd1 = newSqlCommand("Select * From RequestForScoring Where AppStatusId=3", AutorizationFrm.conn); DataTable tbl1 = newDataTable(); SqlDataAdapter da = newSqlDataAdapter(cmd1);da.Fill(tbl1);this.dGVRequests.DataSource = tbl1; SqlCommand cmd3 = newSqlCommand("Select * From Credits", AutorizationFrm.conn); DataTable tbl3 = newDataTable(); SqlDataAdapter da2 = newSqlDataAdapter(cmd3);da2.Fill(tbl3);this.dGVCredits.DataSource = tbl3;AutorizationFrm.conn.Close(); }privatevoid btnExit_Click(object sender, EventArgs e) {this.Close(); }privatevoid btnGetBall_Click(object sender, EventArgs e) {int idReq;idReq = Convert.ToInt32(txtBoxIdReq.Text);if (idReq > 0) { string sqlExpression = "UpdateScoringBallOnId";AutorizationFrm.conn.Open(); SqlCommand cmnd = newSqlCommand(sqlExpression, AutorizationFrm.conn);// указываем, что команда представляет хранимую процедуруcmnd.CommandType = CommandType.StoredProcedure;// добавляем параметр к команде cmnd.Parameters.Add("@idAppl", idReq).Value= idReq;var reader = cmnd.ExecuteNonQuery(); SqlCommand cmd5 = newSqlCommand("Select * From Applicants Where id="+ txtBoxIdReq.Text, AutorizationFrm.conn); DataTable tbl5 = newDataTable(); SqlDataAdapter da4 = newSqlDataAdapter(cmd5);da4.Fill(tbl5);var ball = tbl5.AsEnumerable().Max(row => row["Ball"]);MessageBox.Show($"Скоринговыйбалл: {ball}");AutorizationFrm.conn.Close(); } } }}