Retrieve Data from SQL Server based on TextBox value in C#

Question

I have a database with 4 columns HouseNo, Date, Time and IndoorTemp. In my C# Windows application I have 4 textboxes, one each for the above columns.

The user enters the HouseNo, Date and Time values in the Text Box. The inputs here should be a part of my SQL Where Clause with the output as the Indoor Temperature. This should then be displayed in the last text box.

I have used this connection:

    // Create a String to hold the database connection string.
    string sdwConnectionString = @"Data Source=IE1ADTBD5ZL1S\;Initial Catalog=ThermostatData2;Integrated Security=True";

    // Create a connection
    SqlConnection sdwDBConnection = new SqlConnection(sdwConnectionString);

I tried a code to do this but it failed. My code is,

cmd.IndoorTemp = "select * from ThermData where HouseNo = '" + HouseNovalue.Text + "' and Date = '" + Datevalue.Text + "' and Time = '" + Timevalue.Text + "' ";

How do i achieve the desired result.

I am new to C# and do not know much. All the above code was also with the help of a few blogs.

Do please help me out on this one. Also suggest a good book where i could learn to program C# with SQL Server. Thanks a lot in advance.

Do not build the query by string concatenation! Use SqlParameters instead — CodeZombie
– CodeZombie, Commented May 28, 2013 at 12:19

Mike Perrenoud · Accepted Answer · 2013-05-28 12:21:26Z

Try this:

using (SqlConnection c = new SqlConnection("your connection string"))
{
    c.Open();

    using (SqlCommand cmd = new SqlCommand("SELECT IndoorTemp FROM ThermData WHERE HouseNo = @HouseNo AND Date = @Date AND Time = @Time")
    {
        cmd.Parameters.AddWithValue("@HouseNo", HouseNovalue.Text);
        cmd.Parameters.AddWithValue("@Date", Datevalue.Text);
        cmd.Parameters.AddWithValue("@Time", Timevalue.Text);

        var indoorTemp = cmd.ExecuteScalar();
        Indoortemp.Text = indoorTemp;
    }
}

In this example you're building a new SqlConnection, but you're leveraging the using statement to ensure it's properly disposed. You are doing the same thing for the SqlCommand. Then, you are leveraging parameterized statements to ensure you're safe from SQL Injection.

And don't shy away from this approach even if it's just a personal project that nobody else uses because you practice how you play. If you don't use parameterized SQL when you practice you certainly won't when you play.

Finally, you're leveraging ExecuteScalar which returns the first column from the first row of the result set -and that's what you want.

gzaxx · Accepted Answer · 2013-05-28 12:19:18Z

0

First of all use parametrized query:

var cmd = new SqlCommand("select * from ThermData where HouseNo = @houseno and Date = @date and Time = @time", sdwDBConnection);
cmd.Parameters.AddWithValue("@houseno", HouseNovalue.Text);
cmd.Parameters.AddWithValue("@date", Datevalue.Text);
cmd.Parameters.AddWithValue("@time", Timevalue.Text);

to get results you can do:

sdwDBConnection.Open();
var reader = cmd.ExecuteReader();

if (reader.Read()) //if there is at least one result
{
    //data from reader
    IndoorTemp.Text = reader["IndoorTemp"];  //access value returned for column IndoorTemp
}

answered May 28, 2013 at 12:19

gzaxx

17.6k2 gold badges40 silver badges55 bronze badges

Collectives™ on Stack Overflow

Retrieve Data from SQL Server based on TextBox value in 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