Click here to Skip to main content
15,899,475 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hello all,
I have been working on some CRUD methods in C# and I was wondering if I am doing this the best practice?
So far I got create, read, and delete working but I am still working on update so I am also wondering which approach I should take with this.
Here is the code that I have developed.


C#
public static void CreateProduct(Product product)
        {
            SqlConnection connection = new SqlConnection(connectionstring);
            string createStatement = "INSERT INTO [dbo].[Products](ProductName, ProductPrice, ProductType) " +
                "VALUES(@ProductName, @ProductPrice, @ProductType)";

            SqlCommand createCommand = new SqlCommand(createStatement, connection);
            createCommand.Parameters.AddWithValue("@ProductName", product.ProductName);
            createCommand.Parameters.AddWithValue("@ProductPrice", product.ProductPrice);
            createCommand.Parameters.AddWithValue("@ProductType", product.ProductType);
            try
            {
                connection.Open();
                createCommand.ExecuteNonQuery();
            }
            catch (SqlException sqlexception) { MessageBox.Show(sqlexception.Message.ToString()); }
            catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); }
            finally
            {
                connection.Close();
            }
        }

    /// <summary>
    /// READ A PRODUCT FROM THE MUSIC STORE DATABASE VERSION 0.0, MY PRACTICE
    /// </summary>  
    /// <param name="product"></param>
        public static Product ReadProduct(int productId)
        {
            SqlConnection connection = new SqlConnection(connectionstring);
            string readStatement = "SELECT ProductID, ProductName, ProductPrice, ProductType " +
                "FROM [dbo].[Products] " +
                "WHERE ProductID = @productID";//ID ???
            SqlCommand readCommand = new SqlCommand(readStatement, connection);
            readCommand.Parameters.AddWithValue("@productID", productId);
            try
            {
                connection.Open();
                SqlDataReader dataReader = readCommand.ExecuteReader(CommandBehavior.SingleRow);
                if (dataReader.Read())
                {
                    Product p = new Product();
                    p.ProductID = Convert.ToInt32(dataReader["ProductID"]);
                    p.ProductName = dataReader["ProductName"].ToString();
                    p.ProductPrice = Convert.ToDouble(dataReader["ProductPrice"].ToString());
                    p.ProductType = dataReader["ProductType"].ToString();
                    return p;
                }
                else
                {
                        return null;
                }                    
            }
            catch (SqlException sqlexception) { MessageBox.Show(sqlexception.Message.ToString()); return null; }
            catch (Exception ex) { MessageBox.Show(ex.Message.ToString()); return null; }
            finally
            {
                connection.Close();
            }
        }
        /// <summary>
        /// READ PRODUCT LIST FROM THE MUSIC STORE DATABASE VERSION 0.0, MY PRACTICE
        /// </summary>  
        /// <param name="product"></param>
        public static List<Product> ReadProductList()
        {
            List<Product> products = new List<Product>();
            SqlConnection connect = new SqlConnection(connectionstring);
            string readProductList = "SELECT * FROM [dbo].[Products]";
            SqlCommand readListCommand = new SqlCommand(readProductList, connect);
            try
            {
                connect.Open();
                SqlDataReader productReader = readListCommand.ExecuteReader();
                while(productReader.Read() && productReader != null)
                {
                    Product product = new Product();
                    product.ProductName = productReader["ProductName"].ToString();
                    product.ProductID = Convert.ToInt32(productReader["ProductID"]);
                    product.ProductPrice = Convert.ToDouble(productReader["ProductPrice"]);
                    product.ProductType = productReader["ProductType"].ToString();
                    products.Add(product);
                }
                productReader.Close();
            }
            catch (SqlException ex) { MessageBox.Show(ex.Message.ToString()); }
            catch (Exception ee) { MessageBox.Show(ee.Message.ToString()); }
            finally
            {
                connect.Close();
            }
            return products;
        }

        //delete query method
        public static bool DeleteProduct(Product product)
        {
            SqlConnection connect = new SqlConnection(connectionstring);
            string deleteStatement = "DELETE FROM [dbo].[Products] " +
                "WHERE ProductID = @ProductID AND ProductName = @ProductName " +
                "AND ProductPrice = @ProductPrice AND ProductType = @ProductType";
            SqlCommand deletecmd = new SqlCommand(deleteStatement, connect);
            deletecmd.Parameters.AddWithValue("@ProductID", product.ProductID);
            deletecmd.Parameters.AddWithValue("@ProductName", product.ProductName);
            deletecmd.Parameters.AddWithValue("@ProductPrice", product.ProductPrice);
            deletecmd.Parameters.AddWithValue("@ProductType", product.ProductType);
            try
            {
                connect.Open();
                int count = deletecmd.ExecuteNonQuery();
                if (count > 0) return true;
                else return false;
            }
            catch(SqlException sqlexception)
            {
                MessageBox.Show(sqlexception.Message.ToString());
                return false;
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message.ToString());
                return false;
            }
            finally
            {
                connect.Close();
            }
        }
        //update method
        public static bool UpdateProduct(Product oldProduct, Product newProduct)
        {
            SqlConnection connect = new SqlConnection(connectionstring);
            string updateStatement = "UPDATE [dbo].[Products] SET ProductName = @newProductName, " +
                "ProductPrice = @newProductPrice, ProductType = newProductType WHERE " +
                "ProductName = @oldProductName AND ProductPrice = oldProductPrice AND " +
                "ProductType = @oldProductType";
            SqlCommand updateCommand = new SqlCommand(updateStatement, connect);
            updateCommand.Parameters.AddWithValue("@newProductName", newProduct.ProductName);
            updateCommand.Parameters.AddWithValue("@newProductPrice", newProduct.ProductPrice);
            updateCommand.Parameters.AddWithValue("@newProductType", newProduct.ProductType);
            updateCommand.Parameters.AddWithValue("@oldProductName", oldProduct.ProductName);
            updateCommand.Parameters.AddWithValue("@oldProductPrice", oldProduct.ProductPrice);
            updateCommand.Parameters.AddWithValue("@oldProductType", oldProduct.ProductType);
            try
            {
                connect.Open();
                int count = updateCommand.ExecuteNonQuery();
                if (count > 0)
                    return true;
                else
                    return false;
            }
            catch (SqlException sqlex) { throw sqlex; }
            catch (Exception ex) { throw ex; }
            finally { connect.Close(); }
        }
public partial class ProductList : Window
    {
        private Product product;
        private List<Product> products;

        public ProductList()
        {
            InitializeComponent();
            products = new List<Product>();
            PopulateListBox();
            //populate the List<Product>
            //fill the ListBox
        }

        //get the product from the database
        private void GetProduct(int productId)
        {
            try
            {
                product = ProductDB.ReadProduct(productId);
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.Message.ToString());
            }
        }

        //populate the listbox with all of the products in the database
        private void PopulateListBox()
        {
            listboxProducts.Items.Clear();
            products = ProductDB.ReadProductList();

            //iterate through the product list
            foreach(Product prod in products)
            {
                listboxProducts.Items.Add(prod.ToString() + "\n");
            }
        }

        private void btnAddProduct_Click(object sender, RoutedEventArgs e)
        {
            Console.WriteLine(product);
            Window newProduct = new ProductAdd();
            newProduct.ShowDialog();
            this.Close();
        }

        private void btnDeleteProduct_Click(object sender, RoutedEventArgs e)
        {
            MessageBoxResult result = MessageBox.Show("Are you sure you want to Delete ",
                                 "Confirm Delete", MessageBoxButton.YesNo, MessageBoxImage.Question);
            if(result == MessageBoxResult.Yes)
            {
                try
                {
                    if(listboxProducts.SelectedIndex != -1)
                    {
                        if(!ProductDB.DeleteProduct(products[listboxProducts.SelectedIndex]))
                        {
                            this.GetProduct(product.ProductID);
                        }
                        PopulateListBox();
                    }
                    else
                    {
                        MessageBox.Show("Get Real! You need to make a selection b4 you can delete", "Error");
                    }
                }
                catch(FormatException format) { MessageBox.Show(format.Message.ToString()); }
                catch(Exception ex) { MessageBox.Show(ex.Message.ToString()); }
public partial class ProductAdd : Window
    {
        private Product product;
        private List<Product> products;
        private bool modify = false;

        public ProductAdd()
        {
            InitializeComponent();
            products = new List<Product>();

        }

        private void btnAddProduct_Click(object sender, RoutedEventArgs e)
        {
            product = new Product(txtProductName.Text, Convert.ToDouble(txtProductPrice.Text), txtProductType.Text);
            MessageBox.Show(product.ToString());
            ProductDB.CreateProduct(product);

            Window backToProductList = new ProductList();
            backToProductList.Show();
            this.Close();
        }
Posted
Comments
Sinisa Hajnal 17-Feb-15 6:32am    
What is the problem? You're doing fine - I would remove all database calls into separate project (DLL ) and just call something like ProductManager.GetProduct(productId), but in general you're doing fine. What bothers you with update functionality? Note that this is not a good question for this forum since you're asking for personal oppinions instead of a solution for particular problem.
VishwaKL 19-Feb-15 2:11am    
for insert/Update/Delete/Read => CURD you can find SQL Helper Class which will be having all methods just you need to pass the parameter that will do the further operation. That will save your time for re-writing the same code. SQL Helper Class is applicable for any object you just download and check that
John C Rayan 1-Mar-15 10:37am    
Did you look at Entity Framework?

1 solution

Hi,

What ever operations you are performing are correct but you need to have seperation of concerns you are calling the database in the codebehind which is wrong.

You need to follow MVVM.

V- View where all your view or xaml are there.

VM -View Model is Where are your business logic is present and you will do all your bindings. From VM you need to call the database say your Dataaccess code.

M- Model is where you have your data.

Please check out MVVM pattern from John Gossman's blog and MSDN.
Try to read and implement the SOLID principles to get your architecture right.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900