////////////////////////////////////////////////////////////////////////////////////////////////// /// <summary> /// Добавляеит нового клиента в базу /// </summary> /// <param name="cl"></param> public void AddClient(ClientOLD cl) { try { string sql = ""; using (var con = new SqlConnection(strCon.ConnectionString)) { con.Open(); if (cl.Status == "COMPANY") { sql = $"INSERT dbo.Company(" + "CompName," + "CreateDay," + "INN,Finance," + "BankRating," + "ClStatus," + "Adress," + "ActivLevel," + "FirstTrans)" + $"VALUES(N'{cl.FName}'," + $"'{cl.BrDay.ToString("yyyy-MM-dd")}'," + $"N'{cl.NumDoc}'," + $"{cl.Finance.ToInt32()}," + $"{cl.Rating.ToInt32()}," + $"N'{cl.Status}'," + $"N'{cl.Adress}'," + $"{0}," + $"N'{DateTime.Now.ToShortTimeString()}')"; } else { sql = $"INSERT dbo.Client(FName, LName, BrthDay, Passport, Finance, BankRating, ClStatus, Adress, WorkPlace, ActivLevel, FrstTrans)" + $"VALUES(N'{cl.FName}'," + $"N'{cl.LName}', " + $"'{cl.BrDay.ToString("yyyy-MM-dd")}', " + $"N'{cl.NumDoc}', " + $"{cl.Finance}, " + $"{cl.Rating.ToInt32()}, " + $"N'{cl.Status}'," + $"N'{cl.Adress}'," + $"N'{cl.WorkPlace}', " + $"0," + $"'{DateTime.Now.ToShortTimeString()}')"; } var command = new SqlCommand(sql, con); command.ExecuteNonQuery(); MessageBox.Show("Add"); } } catch (Exception e) { MessageBox.Show(e.Message); } }
/// <summary> /// Получаем клиента из базы данных /// </summary> /// <param name="status">Статус клиента</param> /// <param name="num">номер документа</param> /// <returns></returns> public ClientOLD GetClientFromDB(string status, string num) { ClientOLD cl = new ClientOLD(); List <string> li = new List <string>(); string sql = ""; try { using (var con = new SqlConnection(strCon.ConnectionString)) { con.Open(); if (status == "COMPANY") { sql = $"SELECT * FROM dbo.Company WHERE INN = N'{num}'"; var com = new SqlCommand(sql, con); var res = com.ExecuteReader(); while (res.Read()) { cl = new ClientOLD(res[cd.Inn].ToString(), ///string numDoc res[cd.CompStatus].ToString(), ///string status res[cd.CompName].ToString(), /// string fName res[cd.Compfinance].ToString(), /// string finance res[cd.CompAdress].ToString(), /// string adress res[cd.CompRating].ToString(), /// string rating Convert.ToDateTime(res[cd.CompCreateDate])); /// DateTime brDay } } else { sql = $"SELECT * FROM dbo.Client WHERE Passport = N'{num}'"; var com = new SqlCommand(sql, con); var res = com.ExecuteReader(); while (res.Read()) { cl = new ClientOLD(res[cd.Passport].ToString(), /// string numDoc, res[cd.Status].ToString(), ///string status, res[cd.Name].ToString(), ///string fName, res[cd.Lname].ToString(), ///string lName, res[cd.Finance].ToString(), ///string finance, res[cd.Adress].ToString(), ///string adress, res[cd.Rating].ToString(), ///string rating, res[cd.WorkPlace].ToString(), ///string workPlace, Convert.ToDateTime(res[cd.BrhDay])); ///DateTime brDay } } } } catch (Exception e) { MessageBox.Show(e.Message); } return(cl); }
/// <summary> /// Изменяет данные клиента /// </summary> /// <param name="cl">Клиент с изменнёными данными</param> /// <param name="oldnum">номер документа до обновления</param> public void ChangeClient(ClientOLD cl, string oldnum) { try { string sql = ""; using (var con = new SqlConnection(strCon.ConnectionString)) { con.Open(); cl.Finance.Replace(',', '.'); if (cl.Status == "COMPANY") { sql = $"UPDATE Company SET " + $"CompName = N'{cl.FName}'," + $" CreateDay = '{cl.BrDay.ToString("yyyy-MM-dd")}'," + $" INN = '{cl.NumDoc}', " + $" Finance = '{cl.Finance.Replace(',', '.')}', " + $" BankRating = {cl.Rating}, " + $"ClStatus = '{cl.Status}', " + $"Adress = N'{cl.Adress}' " + $" WHERE INN = '{oldnum}'"; } else { sql = $"UPDATE dbo.Client SET FName = N'{cl.FName}'," + $" LName = N'{cl.LName}'," + $" BrthDay = '{cl.BrDay.ToString("yyyy-MM-dd")}'," + $" Passport = '{cl.NumDoc}', " + $" Finance = '{cl.Finance.Replace(',', '.')}', " + $" BankRating = {cl.Rating}, " + $"ClStatus = '{cl.Status}', " + $"Adress = N'{cl.Adress}', " + $"WorkPlace = N'{cl.WorkPlace}'" + $" WHERE Passport = '{oldnum}'"; } var command = new SqlCommand(sql, con); command.ExecuteNonQuery(); MessageBox.Show("Change"); } } catch (Exception e) { MessageBox.Show(e.Message); } }