CRUD更新 - 正确的方向？ - 建议 [英] CRUD Update - Right Direction? - Suggestions

问题描述

大家好，

我一直在研究C＃中的一些CRUD方法，我想知道我这样做是不是最好的做法？

到目前为止我创造了，读取和删除工作，但我仍然在进行更新，所以我也想知道我应该采用哪种方法。

这是我开发的代码。

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.

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

推荐答案

什么操作你正在执行是正确的，但你需要分离你在代码隐藏中调用数据库的问题。



您需要关注MVVM。



V-查看所有视图或xaml所在的位置。



VM -View Model是您的业务逻辑在哪里，您将完成所有绑定。从VM你需要调用数据库说你的Dataaccess代码。



M-模型是你拥有数据的地方。



请查看来自John Gossman博客和MSDN的MVVM模式。

尝试阅读并实施SOLID原则以使您的架构正确。

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.

这篇关于CRUD更新 - 正确的方向？ - 建议的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！