Can't retrieve data from database using c#

Question

I have base named Flashcard with columns ID, English, Polish. I want to retrieve the name from one of them and save it to string. This is my code

        public string AskBaseForPolishName(string englishName)
    {
        string polishName;
        SqlConnect.Open();
        SqlCommand cmd = new SqlCommand($"select * from Flashcard where English = {englishName}", SqlConnect);
        SqlParameter param = new SqlParameter();
        SqlDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            polishName = reader["Polish"].ToString();
        }

        reader.Close();
        SqlConnect.Close();
        return polishName;
    }

and at line "return" Visual Studio is warning, that "use of unassigned variable polishName". I can't retrieve data from base. What's wrong and how to fix it?

You really should be using using statements, also look in to ExecuteScalar() instead of using ExecuteReader() — Scott Chamberlain
– Scott Chamberlain, Commented Apr 14, 2017 at 22:01

Joel Coehoorn · Accepted Answer · 2017-04-14 22:14:41Z

There are three big things wrong here.

First, a string inside an SQL statement needs to be enclosed in single quotes, like this:

SqlCommand cmd = new SqlCommand($"select * from Flashcard where English = '{englishName}'", SqlConnect);

This might seem to fix your problem all by itself, but things are still pretty bad. Think about what would happen if you have an English name that itself includes a single-quote character in it? The quote would throw the whole query out of whack. Worse, that issue can be used by hackers to do very bad things in your database.

So the second thing to do is to use query parameters:

SqlCommand cmd = new SqlCommand("select * from Flashcard where English = @EnglishName", SqlConnect);
cmd.Parameters.Add("@EnglishName", SqlDbType.NVarChar, 20).Value = englishName;

This will protect you from mis-placed or malicious single quotes, and notice as well that you no longer need to worry about enclosing the value. There a number of other benefits for doing it this way, as well. Query parameters are important. Use them.

Finally, think about what would happen if an exception is thrown by your query. Execution flow would bubble up out of your method, and the .Close() command would never happen. Do this enough, and it's possible to leave enough connections hanging open that you create a denial of service situation on the database... no one can use it! Protect against that with either a using block or try/finally blocks.

There are some other small things, too, but those are the three that are really important. Put it all together like this:

public string AskBaseForPolishName(string englishName)
{
    //it's best NOT to re-use the same connection object. Only reuse the same connection string
    using (var cn = new SqlConnection("connection string here"))
    using (var cmd = new SqlCommand("select Polish from Flashcard where English = @EnglishName;", cn))
    {
         cmd.Parameters.Add("@EnglishName", SqlDbType.NVarChar, 20).Value = englishName;
         cn.Open();
         return cmd.ExecuteScalar().ToString();
    }
}

Sdp · Accepted Answer · 2017-04-15 04:08:08Z

use this code snippet

    static void Read()
{
try
{
    string connectionString =
        "server=.;" +
        "initial catalog=employee;" +
        "user id=sa;" +
        "password=sa123";
    using (SqlConnection conn =
        new SqlConnection(connectionString))
    {
        conn.Open();
        using (SqlCommand cmd =
            new SqlCommand("SELECT * FROM EmployeeDetails", conn))
        {
            SqlDataReader reader = cmd.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Console.WriteLine("Id = ", reader["Id"]);
                    Console.WriteLine("Name = ", reader["Name"]);
                    Console.WriteLine("Address = ", reader["Address"]);
                }
            }

            reader.Close();
        }
    }
}
catch (SqlException ex)
{
    //Log exception
    //Display Error message
}
}

Collectives™ on Stack Overflow

Can't retrieve data from database using c#

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related