C# WPF read SQL method

Question

0.00/5 (No votes)

See more:

, +

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.

C#

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.

Posted 2-Dec-18 13:10pm

TheBigBearNow

Updated 2-Dec-18 23:55pm

Add a Solution

Comments

TheBigBearNow 2-Dec-18 20:08pm

public static Product ReadProductById(int prodId)
{
Product product = new Product();

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 product2 = 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"])
};
product = product2;
}
}
else { product = null; }
}
}
}
return product;
}

is what i am going to try.

PRAKASH9 3-Dec-18 0:27am

Yes you have to make declaration globally.

CHill60 3-Dec-18 5:55am

No, NOT globally. See my solution for a reference about Scope

2 solutions

Solution 1

I do it this way:

C#

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.
}

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

Posted 2-Dec-18 23:46pm

#realJSOP

Comments

CHill60 3-Dec-18 9:00am

Isn't this negating the use of using? MSDN states "it's generally better to instantiate the object in the using statement and limit its scope to the using block." (using Statement (C# Reference) | Microsoft Docs[^])
If you move the try-catch to within the using block would still enable you to query any exception and still benefit from the resource disposal.
It still doesn't address the real issue though - the OP doesn't understand scope

#realJSOP 3-Dec-18 9:14am

Not at all. The using still does cleanup for you - I'm just making the objects available in the event of an exception occurring so I can see what's in them. "Generally better" is just a guideline, kinda like the Pirate's Code.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Accepted Answer · 2018-12-02T23:56:00

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.

C#

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