-3

I'm currently trying to create a query through Visual Studio C# that will select all records for a CustomerID when someone using the form enters a customer ID in a textbox, and then display those records in textboxes on the form. How can I go about achieving this? I know very very little about SQL or how to make these two languages work together. I have already created the database and created a data source in visual studio with it. I believe my query should look something like 

SELECT        CustomerID, Name, Address, City, State, ZipCode, Phone, Email
FROM            Customers
WHERE        (CustomerID = @Param1)

However, I don't really know what @Param1 should actually be called since it's coming from a textbox. How should this query read? Am I even on the right track?

Second question is once I get this data selected, how do I make each piece of selected data populate a textbox on my form?

Sorry for the newbie questions, this is my first time really doing anything with SQL.

Thanks for any help.

Improve this question
0

3 Answers 3

Reset to default
3

You should probably read up on ADO.NET and how it works. You'll need to create a SqlConnection and a SqlCommand to execute your SQL from C#.

using( SqlConnection conn = new SqlConnection( connectionString ) )
{
    conn.Open();
    using( SqlCommand command = new SqlCommand( "your select statement", conn ) )
    {
        command.AddWithValue( "@Param1", YourTextBox.Text );
        var reader = command.ExecuteReader();

        reader.Read();
        txtFoo.Text = reader["FooColumn"];
        txtBar.Text = reader["BarColumn"];
    }
}

The above is rough code, not complete and functional. We're happy to help once you've done some research and reading but not write your entire work/school assignment for you.

Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Not a fan of the AddWithValue() method (it can lead to performance issues), but the rest of this is pretty good.
Please expand on the performance issues. All it does is wrap the creation of a new SqlParameter and add it to the collection. Decompiling the SqlParameterCollection the actual code in AddWithValue is literally return this.Add(new SqlParameter(parameterName, value));
If you want to add an answer to the second part of his question I'll just delete my answer?
Here is one example (others exist): AddWithValue() forces ADO.Net to guess your parameter type. If you give it a string, it will guess nvarchar. If you compare that with a varchar column in the database, the db will choose to widen the varchar field to an nvarchar for every row in the table, because the alternative nvarchar to varchar is a narrowing conversion (potential to lose data, return wrong result). Not only does this force a per-row conversion for every row in the table, but breaks the use of any index on the column. This can be a night and day performance difference.
So it's really not a problem with AddWithValue per se, but rather with the SqlParameter(String, Object) constructor. I don't do much raw ADO.NET anymore but I do remember wishing that a SqlParameter(String, SqlDbType, Object) constructor existed because if you want specify the SqlDbType and the value you have to use that PITA 13-arg constructor, or create the SqlParameter and then set the properties individually, neither of which ever seemed all that elegant.
|
2

See this thread for your first question. Here's a quick example (I used a converter, sorry for any inconsistencies).

    string eId = EmployeeIDTxt.Text;
    string query = String.Empty;
    query = "SELECT EMPL_SEQ_ID, EMPL_ID, EMPL_LAST_NM, EMPL_FIRST_NM, EMPL_PREFRD_NM"
    query &= "  FROM EMPL"
    query &= "  WHERE EMPL_SEQ_ID = @ID; "

DataSet ds = new DataSet();
using (SqlConnection conn = new SqlConnection(MyConn)) {  'myconn should be your connection string
    using (SqlDataAdapter da = new SqlDataAdapter()) {  'SqlDataAdapter should be different if you're using OleDb or Oracle
        da.SelectCommand = new SqlCommand(query, conn); 'See previous comment for SqlCommand
        da.SelectCommand.Parameters.Add(new SqlParameter("@ID", eId)); 'Gives your parameter the value you feed it
        da.Fill(ds); 'fill the dataset with the results from the query
    }
}

You can also refer to each item specifically by loading the data from the SQL statement into a DataTable or DataSet. It would look like this:

DataSet ds = New DataSet();

EmployeeIDTxt.Text = ds.Tables(0).Rows(0).Item(1).ToString;
LNameTxt.Text = ds.Tables(0).Rows(0).Item(2).ToString;
FNameTxt.Text = ds.Tables(0).Rows(0).Item(3).ToString;

By using the index of the table in the DataSet or item in the DataTable, you can insert those values into textboxes.

In the above example, the first item (in the index of ds.Tables(0).Rows(0).Item()) is EmployeeID which is inserted into the textbox EmployeeIDTxt.

Also note if you're using Oracle, the semicolon at the end of the SQL statement should not exist, and the Parameter should be preceeded with a colon. See below for a quick example:

myCommand.Parameters.Add(New OracleParameter(":EmployeeID", empId))
Improve this answer

Comments

1

Seeing as you already have a data source, you can use LINQ to interact with it. For basic information about LINQ and how to use it, see this link.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.