本文介绍了CRUD更新 - 正确的方向？ - 建议的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
大家好，
我一直在研究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();
}推荐答案
这篇关于CRUD更新 - 正确的方向？ - 建议的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！