internal Organization GetOrganizationByID(int id)
{
string searchExpression = "SELECT * from dbo.Organization WHERE ID = @id";
using (SqlConnection connection = new SqlConnection(_ConnectionString))
{
SqlCommand searchCommand = new SqlCommand(searchExpression, connection);
searchCommand.Parameters.Add(new SqlParameter("@id", id));
connection.Open();
using (SqlDataReader reader = searchCommand.ExecuteReader())
{
if (reader.HasRows)
{
ContactsDataConverter converter = new ContactsDataConverter();
Organization organization = new Organization();
while (reader.Read())
{
organization = (converter.OrganizationFromDataReader(reader));
}
connection.Close();
return(organization);
}
else
{
connection.Close();
return(null);
}
};
}
}
public string InsertContact(string Name, string Surname, string MiddleName,
string Gender, string BirthDate, string Phone, string TaxNumber,
string Position, string JobID)
{
Logger.Log.Info($"InsertContact got params: name = {Name}, surname = {Surname}, " +
$"middlename = {MiddleName},gender = { Gender}, birthday = {BirthDate}, phone = {Phone}," +
$" taxnumber = {TaxNumber}, position = {Position}, jobid = {JobID}");
ContactsDataConverter converter = new ContactsDataConverter();
Contact newContact = converter.CreateInstance(Name, Surname, MiddleName,
Gender, BirthDate, Phone, TaxNumber, Position, JobID);
string db = ConfigurationManager.AppSettings["DBConnectionString"];
ContactsDBOperator dBOperator = new ContactsDBOperator(db);
try
{
int id = dBOperator.InsertContact(newContact);
string result = JsonConvert.SerializeObject(id);
return(result);
}
catch (DBConnectionException)
{
throw new FaultException("Ошибка подключения к базе данных");
}
catch (SQLCommandException)
{
throw new FaultException("Ошибка выполнения запроса к базе данных");
}
}
private List <Contact> GetList(SqlCommand command)
{
using (SqlConnection connection = new SqlConnection(_ConnectionString))
{
command.Connection = connection;
connection.Open();
try
{
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
ContactsDataConverter converter = new ContactsDataConverter();
List <Contact> result = new List <Contact>();
while (reader.Read())
{
Contact contact = (converter.ContactFromDataReader(reader));
result.Add(contact);
Logger.Log.Info($" New result row added, {contact.ToString()}");
}
connection.Close();
return(result);
}
else
{
Logger.Log.Info($" No results");
connection.Close();
return(null);
}
};
}
catch (SqlException ex)
{
Logger.Log.Info($"SQLCommand failed, {ex.ToString()}");
throw new SQLCommandException(ex);
}
}
}
internal Contact GetById(string id)
{
string searchExpression = "SELECT * from dbo.Contact WHERE ID = @id";
using (SqlConnection connection = new SqlConnection(_ConnectionString))
{
SqlCommand searchCommand = new SqlCommand(searchExpression, connection);
searchCommand.Parameters.Add(new SqlParameter("@id", id));
connection.Open();
try
{
using (SqlDataReader reader = searchCommand.ExecuteReader())
{
if (reader.HasRows)
{
ContactsDataConverter converter = new ContactsDataConverter();
Contact contact = new Contact();
while (reader.Read())
{
contact = (converter.ContactFromDataReader(reader));
}
connection.Close();
return(contact);
}
else
{
connection.Close();
return(null);
}
};
}
catch (SqlException ex)
{
Logger.Log.Info($"SQLCommand failed, {ex.ToString()}");
throw new SQLCommandException(ex);
}
}
}
internal List <Organization> GetOrgList()
{
string searchExpression = "SELECT * from dbo.Organization";
using (SqlConnection connection = new SqlConnection(_ConnectionString))
{
SqlCommand searchCommand = new SqlCommand(searchExpression, connection);
OpenConnection(connection);
try
{
using (SqlDataReader reader = searchCommand.ExecuteReader())
{
if (reader.HasRows)
{
ContactsDataConverter converter = new ContactsDataConverter();
List <Organization> result = new List <Organization>();
while (reader.Read())
{
result.Add(converter.OrganizationFromDataReader(reader));
}
connection.Close();
return(result);
}
else
{
connection.Close();
return(null);
}
};
}
catch (SqlException ex)
{
Logger.Log.Info($"SQLCommand failed, {ex.ToString()}");
throw new SQLCommandException(ex);
}
}
}
public void UpdateContact(string ID, string Name, string Surname, string MiddleName,
string Gender, string BirthDate, string Phone, string TaxNumber,
string Position, string JobID)
{
Logger.Log.Info($"UpdateContact got params: id = {ID}, name = {Name}, surname = {Surname}, " +
$"middlename = {MiddleName},gender = { Gender}, birthday = {BirthDate}, phone = {Phone}," +
$" taxnumber = {TaxNumber}, position = {Position}, jobid = {JobID}");
string db = ConfigurationManager.AppSettings["DBConnectionString"];
ContactsDBOperator dBOperator = new ContactsDBOperator(db);
if (dBOperator.RowExists(ID))
{
ContactsDataConverter converter = new ContactsDataConverter();
Contact newContact = converter.CreateInstance(Name, Surname, MiddleName,
Gender, BirthDate, Phone, TaxNumber, Position, JobID, ID);
try
{
dBOperator.UpdateContact(newContact);
}
catch (DBConnectionException)
{
throw new FaultException("Ошибка подключения к базе данных");
}
catch (SQLCommandException)
{
throw new FaultException("Ошибка выполнения запроса к базе данных");
}
}
else
{
Logger.Log.Info($"Contact doesn't exist: id = {ID}");
throw new FaultException <string>(ID, "Контакт не существует");
}
}