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()); } }