C＃WPF读取SQL方法 [英] C# WPF read SQL method

问题描述

我正在通过Id创建一个read / get方法这是我第一次使用'using'语句创建一个方法，所以我想知道我的方法应该如何。我应该在哪里退回产品，我错过了任何提示/想法任何意见。

I am creating a read/get method by Id this is my first time creating a method with 'using' statements so i am wondering how my method should be. where should i return the product and am i missing anything or any tips/ideas any input is appreciated.

public static Product ReadProductById(int prodId)
        {
            using(SqlConnection connection = ConnectionString.GetSqlConnection())
            {
                string query = "SELET * " +
                               "FROM Products " +
                               "WHERE ProductId = @pid";
                using(SqlCommand cmdRead = new SqlCommand(query, connection))
                {
                    cmdRead.Parameters.AddWithValue("@pid", prodId);
                    using(SqlDataReader reader = cmdRead.ExecuteReader())
                    {
                        if (reader != null)
                        {
                            while (reader.Read())
                            {
                                Product product = new Product()
                                {
                                    ProductId = Convert.ToInt32(reader["ProductId"]),
                                    ProductName = reader["ProductName"].ToString(),
                                    ProductPrice = Convert.ToDecimal(reader["ProductPrice"]),
                                    ProductListedDate = Convert.ToDateTime(reader["ProductListedDate"]),
                                    ProductTax = Convert.ToDecimal(reader["ProductTax"]),
                                    UserId = Convert.ToInt32(reader["UserId"]),
                                    ProductQuantity = Convert.ToInt32(reader["ProductQuantity"]),
                                    ProductTotal = Convert.ToDecimal(reader["ProductTotal"]),
                                    ProductPurchasedDate = Convert.ToDateTime(reader["ProductPurchasedDate"]),
                                    ReceiptId = Convert.ToInt32(reader["ReceiptId"])
                                };
                                return product;
                            }
                        }
                        else { return null; }
                        //return product;
                    }
                }
                    
            }
        }

我的尝试：



如果我在方法的顶部声明了产品，那么将返回的地方移动到不同的地方？因为它现在正在移动它说它不在上下文中。

What I have tried:

moving the return to different places should i have the product declared on the top of the method? because where it is now moving it says its not in the context.

推荐答案

我是这样做的：

I do it this way:

SqlConnection conn = null;
SqlCommand cmd = null;
try
{
    using (conn = new SqlConnection("blah blah"))
    {
        conn.Open();
        using (cmd = new SqlCommand(...))
        {
            //... your code
        }
    }
}
catch (Exception ex)
{
    //... do something appropriate to handle the exception.
}

使用之前声明 conn 和 cmd 变量c>语句允许你在抛出异常的情况下检查它们的属性。

Declaring the conn and cmd variables BEFORE the using statements allows you to inspect their properties in the event of an exception being thrown.

你需要理解范围 - 有一个阅读此MSDN文章 Microsoft .NET中的变量和方法范围 [ ^ ]



查看评论中的代码，您宣布第二个产品没有充分理由的对象。 product 声明它的位置很好，因为它可以在任何你想引用的地方使用。我认为你在声明变量和创建新实例之间感到困惑



注意你的代码将无法正常工作，因为您在查询中拼错了SELECT。还有不必要的括号。



类似的东西应该没问题（未经测试！）。请注意我添加的评论。

You need to understand scope - have a read of this MSDN article Variable and Method Scope in Microsoft .NET[^]

Looking at the code from your comment, you are declaring a second Product object for no good reason. product where it is declared is fine as it is available everywhere you want to reference it. I think you are getting confused between declaring a variable and creating a new instance of it

Note that your code will not work because you have misspelled "SELECT" in your query. There are unneccesary braces added as well.

Something similar to this should be fine (untested!). Please take note of the comments I have added.

public static Product ReadProductById(int prodId)
{
   Product product = null;    // Actual instance created in the loop below

   using(SqlConnection connection = ConnectionString.GetSqlConnection())
   {
      string query = "SELECT * FROM Products WHERE ProductId = @pid";

      using(SqlCommand cmdRead = new SqlCommand(query, connection))
      {      
         cmdRead.Parameters.AddWithValue("@pid", prodId);
         using(SqlDataReader reader = cmdRead.ExecuteReader())
         {
            if (reader != null)
            {
               while (reader.Read())   // Do you really need this while here, or just a reader.Read()?
               {
                  product = new Product()   // Could have been done at the declaration
                  ProductId = Convert.ToInt32(reader["ProductId"]),   //Avoid using Convert.ToInt32 - use Int32.TryParse or Int32.Parse instead
                  ProductName = reader["ProductName"].ToString(),
                  ProductPrice = Convert.ToDecimal(reader["ProductPrice"]), //See comment above about Convert.To...
                  ProductListedDate = Convert.ToDateTime(reader["ProductListedDate"]), //See comment above about Convert.To...
                  ProductTax = Convert.ToDecimal(reader["ProductTax"]),
                  UserId = Convert.ToInt32(reader["UserId"]),
                  ProductQuantity = Convert.ToInt32(reader["ProductQuantity"]),
                  ProductTotal = Convert.ToDecimal(reader["ProductTotal"]),
                  ProductPurchasedDate = Convert.ToDateTime(reader["ProductPurchasedDate"]),
                  ReceiptId = Convert.ToInt32(reader["ReceiptId"])
               }
            }
         }
      } 
   }
   return product;
}

这篇关于C＃WPF读取SQL方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！