How to retrieve data from a SQL Server database in C#?

Question

I have a database table with 3 columns firstname, Lastname and age. In my C# Windows application I have 3 textboxes called textbox1... I made my connectivity to my SQL Server using this code:

SqlConnection con = new SqlConnection("Data Source = .;
                                       Initial Catalog = domain;
                                       Integrated Security = True");
con.Open();
SqlCommand cmd = new SqlCommand("Select * from tablename", con);

I'd like to get values from my database; if I give a value in textbox1 it has to match the values in the database and retrieve other details to the corresponding textboxes.

I tried this method but it's not working:

cmd.CommandText = "select * from tablename where firstname = '" + textBox1.Text + "' ";

How can I do it to retrieve all the other values to the textboxes?

question from:https://stackoverflow.com/questions/14171794/how-to-retrieve-data-from-a-sql-server-database-in-c

Answer 1

 public Person SomeMethod(string fName)
        {
            var con = ConfigurationManager.ConnectionStrings["Yourconnection"].ToString();

            Person matchingPerson = new Person();
            using (SqlConnection myConnection = new SqlConnection(con))
            {
                string oString = "Select * from Employees where FirstName=@fName";
                SqlCommand oCmd = new SqlCommand(oString, myConnection);
                oCmd.Parameters.AddWithValue("@Fname", fName);           
                myConnection.Open();
                using (SqlDataReader oReader = oCmd.ExecuteReader())
                {
                    while (oReader.Read())
                    {    
                        matchingPerson.firstName = oReader["FirstName"].ToString();
                        matchingPerson.lastName = oReader["LastName"].ToString();                       
                    }

                    myConnection.Close();
                }               
            }
            return matchingPerson;
        }

Few things to note here: I used a parametrized query, which makes your code safer. The way you are making the select statement with the "where x = "+ Textbox.Text +"" part opens you up to SQL injection.

I've changed this to:

  "Select * from Employees where FirstName=@fName"
  oCmd.Parameters.AddWithValue("@fname", fName);  

So what this block of code is going to do is:

Execute an SQL statement against your database, to see if any there are any firstnames matching the one you provided. If that is the case, that person will be stored in a Person object (see below in my answer for the class). If there is no match, the properties of the Person object will be null.

Obviously I don't exactly know what you are trying to do, so there's a few things to pay attention to: When there are more then 1 persons with a matching name, only the last one will be saved and returned to you. If you want to be able to store this data, you can add them to a List<Person> .

Person class to make it cleaner:

 public class Person
    {
            public string firstName { get; set; }
            public string lastName { get; set; }
    }

Now to call the method:

Person x = SomeMethod("John");

You can then fill your textboxes with values coming from the Person object like so:

txtLastName.Text = x.LastName;