public void Delete(int id)
{
using (sqlConnection = DBConnectionFactory.GetConnection())
{
string baseQuery = "DELETE FROM [NORTHWNDSDP-162].[dbo].[Customers] WHERE CustomerId = '{0}'";
string realQuery = String.Format(baseQuery, id);
sqlConnection.Open();
using (SqlCommand sqlCommand = new SqlCommand(realQuery, sqlConnection))
{
sqlCommand.ExecuteNonQuery();
}
sqlConnection.Close();
}
}
public string Update(CustomersDto t)
{
using (sqlConnection = DBConnectionFactory.GetConnection())
{
string baseQuery = "UPDATE [NORTHWNDSDP-162].[dbo].[Customers] SET CustomerId = '{0}',CompanyName = '{1}',ContactName = '{2}',ContactTitle = {3},Address = {4},City = '{5}',Region = '{6}',PostalCode = {7},Country = {8},Phone = {9},Fax = {10}";
string realQuery = String.Format(baseQuery, t.CustomerId, t.CompanyName,
t.ContactName, t.ContactTitle, t.Address, t.City,
t.Region, t.PostalCode, t.Country, t.Phone, t.Fax);
sqlConnection.Open();
using (SqlCommand sqlCommand = new SqlCommand(realQuery, sqlConnection))
{
sqlConnection.Close();
return(sqlCommand.ExecuteNonQuery().ToString());
}
}
}
public ICollection <CustomersDto> Read()
{
List <CustomersDto> customerDTOsToReturn = new List <CustomersDto>();
using (sqlConnection = DBConnectionFactory.GetConnection())
{
sqlConnection.Open();
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
string realSelectQuery = @"SELECT * FROM [NORTHWNDSDP-162].[dbo].[Customers]";
sqlCommand.CommandText = realSelectQuery;
sqlCommand.CommandType = CommandType.Text;
SqlDataReader reader = sqlCommand.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
customerDTOsToReturn.Add(new CustomersDto()
{
CustomerId = reader["CustomerId"].ToString(),
CompanyName = reader["CompanyName"].ToString(),
ContactName = reader["ContactName"].ToString(),
ContactTitle = reader["ContactTitle"].ToString(),
Address = reader["Address"].ToString(),
City = reader["City"].ToString(),
Region = reader["Region"].ToString(),
PostalCode = reader["PostalCode"].ToString(),
Country = reader["Country"].ToString(),
Phone = reader["Phone"].ToString(),
Fax = reader["Fax"].ToString()
});
}
}
}
sqlConnection.Close();
}
return(customerDTOsToReturn);
}
public CustomersDto Read(int id)
{
using (sqlConnection = DBConnectionFactory.GetConnection())
{
sqlConnection.Open();
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
string baseSelectQuery = @"SELECT * FROM [NORTHWNDSDP-162].[dbo].[Customers]" +
"WHERE [CustomerId = {0}]";
string realSelectQuery = String.Format(baseSelectQuery, id.ToString());
sqlCommand.CommandText = realSelectQuery;
sqlCommand.CommandType = CommandType.Text;
SqlDataReader reader = sqlCommand.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
customerDTOToReturn = new CustomersDto()
{
CustomerId = reader["CustomerId"].ToString(),
CompanyName = reader["CompanyName"].ToString(),
ContactName = reader["ContactName"].ToString(),
ContactTitle = reader["ContactTitle"].ToString(),
Address = reader["Address"].ToString(),
City = reader["City"].ToString(),
Region = reader["Region"].ToString(),
PostalCode = reader["PostalCode"].ToString(),
Country = reader["Country"].ToString(),
Phone = reader["Phone"].ToString(),
Fax = reader["Fax"].ToString()
};
}
}
sqlConnection.Close();
}
return(customerDTOToReturn);
}
public string Create(CustomersDto t)
{
string baseSqlQuery = @"INSERT INTO [NORTHWNDSDP-162].[dbo].[Customers],
([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address],
[City], [Region], [PostalCode], [Country], [Phone], [Fax])" +
"VALUES (@customerID, @companyName, @contactName, @contactTitle, @address, @city, @region, @postalCode, @country, @phone, @fax)" +
"SELECT Id AS @ReturnedId FROM [NORTHWNDSDP-162].[dbo].[Customers] WHERE CustomerID = @customerID";
SqlParameter idParameter = new SqlParameter()
{
ParameterName = "@customerID",
Value = t.CustomerId,
SqlDbType = SqlDbType.NChar,
Direction = ParameterDirection.Input
};
SqlParameter companyNameParameter = new SqlParameter()
{
ParameterName = "@companyName",
Value = t.CompanyName,
SqlDbType = SqlDbType.NVarChar,
Direction = ParameterDirection.Input
};
SqlParameter contactNameParameter = new SqlParameter()
{
ParameterName = "@contactName",
Value = t.ContactName,
SqlDbType = SqlDbType.NVarChar,
Direction = ParameterDirection.Input
};
SqlParameter contactTitleParameter = new SqlParameter()
{
ParameterName = "@contactTitle",
Value = t.ContactTitle,
SqlDbType = SqlDbType.NVarChar,
Direction = ParameterDirection.Input
};
SqlParameter addressParameter = new SqlParameter()
{
ParameterName = "@address",
Value = t.Address,
SqlDbType = SqlDbType.NVarChar,
Direction = ParameterDirection.Input
};
SqlParameter cityParameter = new SqlParameter()
{
ParameterName = "@city",
Value = t.City,
SqlDbType = SqlDbType.NVarChar,
Direction = ParameterDirection.Input
};
SqlParameter regionParameter = new SqlParameter()
{
ParameterName = "@region",
Value = t.Region,
SqlDbType = SqlDbType.NVarChar,
Direction = ParameterDirection.Input
};
SqlParameter postalCodeParameter = new SqlParameter()
{
ParameterName = "@postalCode",
Value = t.PostalCode,
SqlDbType = SqlDbType.NVarChar,
Direction = ParameterDirection.Input
};
SqlParameter countryParameter = new SqlParameter()
{
ParameterName = "@country",
Value = t.Country,
SqlDbType = SqlDbType.NVarChar,
Direction = ParameterDirection.Input
};
SqlParameter phoneParameter = new SqlParameter()
{
ParameterName = "@phone",
Value = t.Phone,
SqlDbType = SqlDbType.NVarChar,
Direction = ParameterDirection.Input
};
SqlParameter faxParameter = new SqlParameter()
{
ParameterName = "@Fax",
Value = t.Fax,
SqlDbType = SqlDbType.NVarChar,
Direction = ParameterDirection.Input
};
SqlParameter returnedIdParameter = new SqlParameter()
{
ParameterName = "@ReturnedId",
SqlDbType = SqlDbType.NVarChar,
Direction = ParameterDirection.Output,
Size = 100
};
using (sqlConnection = DBConnectionFactory.GetConnection())
{
sqlConnection.Open();
using (SqlCommand sqlCommand = new SqlCommand(baseSqlQuery, sqlConnection))
{
sqlCommand.CommandText = baseSqlQuery;
sqlCommand.CommandType = CommandType.Text;
sqlCommand.Parameters.Add(idParameter);
sqlCommand.Parameters.Add(companyNameParameter);
sqlCommand.Parameters.Add(contactNameParameter);
sqlCommand.Parameters.Add(contactTitleParameter);
sqlCommand.Parameters.Add(addressParameter);
sqlCommand.Parameters.Add(cityParameter);
sqlCommand.Parameters.Add(regionParameter);
sqlCommand.Parameters.Add(postalCodeParameter);
sqlCommand.Parameters.Add(countryParameter);
sqlCommand.Parameters.Add(phoneParameter);
sqlCommand.Parameters.Add(faxParameter);
SqlDataReader reader = sqlCommand.ExecuteReader();
reader.Read();
string value = reader["@returnedId"].ToString();
}
sqlConnection.Close();
return(t.CustomerId.ToString());
}
}