public SupplierDTO Read(string id)
{
SupplierDTO supplierToReturn = null;
using (sqlConnection = DataBaseConnectionFactory.Connection())
{
int num;
sqlConnection.Open();
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
string readQuery = string.Format(@"SELECT * FROM Suppliers
WHERE [RegionID] = {0}", id);
sqlCommand.CommandText = readQuery;
sqlCommand.CommandType = CommandType.Text;
SqlDataReader reader = sqlCommand.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
Int32.TryParse(reader["SupplierID"].ToString(), out num);
supplierToReturn = new SupplierDTO()
{
SupplierId = num,
CompanyName = reader["CompanyName"].ToString(),
ContactName = reader["ContactName"].ToString()
};
}
}
sqlConnection.Close();
}
return(supplierToReturn);
}
public CustomerDTO Read(string id)
{
CustomerDTO customerToReturn = null;
using (sqlConnection = DataBaseConnectionFactory.Connection())
{
sqlConnection.Open();
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
string readQuery = string.Format(@"SELECT * FROM Customers
WHERE [RegionID] = {0}", id);
sqlCommand.CommandText = readQuery;
sqlCommand.CommandType = CommandType.Text;
SqlDataReader reader = sqlCommand.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
customerToReturn = new CustomerDTO()
{
CustomerId = reader["CustomerID"].ToString(),
CompanyName = reader["CompanyName"].ToString(),
ContactName = reader["ContactName"].ToString()
};
}
}
sqlConnection.Close();
}
return(customerToReturn);
}
public string Create(CustomerDTO t)
{
using (sqlConnection = DataBaseConnectionFactory.Connection())
{
string createQuery = string.Format(@"use [NORTHWNDSDP-162] insert into Customers
Values('{0}', '{1}', '{2}', null, null, null, null, null, null, null, null)", t.CustomerId, t.ContactName, t.CompanyName);
sqlConnection.Open();
try
{
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
sqlCommand.CommandText = createQuery;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Console.WriteLine("Возможно вы ввели повторяющееся Id");
}
sqlConnection.Close();
return(t.CustomerId);
}
}
public void Delete(string Id)
{
using (sqlConnection = DataBaseConnectionFactory.Connection())
{
string deleteQuery = string.Format(@"delete from Customers
where CustomerID='{0}';", Id);
sqlConnection.Open();
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
sqlCommand.CommandText = deleteQuery;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.ExecuteNonQuery();
}
sqlConnection.Close();
}
}
public void Update(SupplierDTO t)
{
using (sqlConnection = DataBaseConnectionFactory.Connection())
{
string updateQuery = string.Format(@"UPDATE Customers
SET CompanyName='{0}', ContactName='{1}'
WHERE CustomerID='{2}';", t.CompanyName, t.ContactName, t.SupplierId);
sqlConnection.Open();
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
sqlCommand.CommandText = updateQuery;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.ExecuteNonQuery();
}
sqlConnection.Close();
}
}
public void Create(SupplierDTO t)
{
using (sqlConnection = DataBaseConnectionFactory.Connection())
{
string createQuery = string.Format(@"insert into Suppliers
(CompanyName,ContactName )
Values('{0}', '{1}')", t.CompanyName, t.ContactName);
sqlConnection.Open();
try
{
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
sqlCommand.CommandText = createQuery;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.ExecuteNonQuery();
}
}
catch (Exception ex)
{
Console.WriteLine("Введены повторяющиеся значения");
}
sqlConnection.Close();
}
}