C# Return Multiple Values from SQL Query

Hi Experts,
I have the following SQL query where I need to assign the returned values to some variables.

SqlCommand cmdSendRequest = new SqlCommand("SELECT rtrim(PHONE1),"
            + "rtrim(ADDRESS1),rtrim(CITY),rtrim(STATE),rtrim(ZIP) FROM RM00101"
            + " WHERE CUSTNMBR ='" + txtCustNum.Text + "'", con);

Here are the variables I need to assign the values to.

hPhone = ["PHONE1"].ToString();
addrs1 = ["ADDRESS1"].ToString();
city = ["CITY"].ToString();
state = ["STATE"].ToString();
zip = ["ZIP"].ToString();

Of course, the code above where I am assigning a value to the variable isn't working.  I tried using cmdSendRequest.ExecuteScalar(); , but that is for returning only a single value.  I also tried using DataReader, but couldn't get that to work either.  My application kept crashing as soon as it tried to assign a value to the first variable.  I tried using a Try/Catch to see why it was crashing, but no error was writted to the console.

Please, what is my best route to get the query result assigned to my variables?
Thanks,
Dan
isda-donAsked:
Who is Participating?
 
lazyberezovskyConnect With a Mentor Commented:
using DataReader:

SqlDataReader reader = cmdSendResult.ExecuteReader();

if (reader.Read()) // or while
{
hPhone = reader["PHONE1"].ToString();
addrs1 = reader["ADDRESS1"].ToString();
city = reader["CITY"].ToString();
state =reader ["STATE"].ToString();
zip = reader["ZIP"].ToString();
}

Of course you need to deal with null by your own.
0
 
Shaun KlineConnect With a Mentor Lead Software EngineerCommented:
Assuming you are using SQL Server as your database, your query will not bring back columns with the names Phone1, Address1, City, State and Zip because you are using the RTRIM function on them. You should provide aliases for these columns:
SELECT rtrim(PHONE1) As Phone1, rtrim(ADDRESS1) As Address1 ,rtrim(CITY) As City ,rtrim(STATE) As State,rtrim(ZIP) As Zip FROM RM00101

For returning a dataset from a database, a datareader is general the best approach with the least overhead (as compared to using a data adapter with a data set). When using the data reader, use:
if(datareader.Read()) // This moved the data reader's cursor to the next available row of data, and assumes you only want to only handle a single row of data.
{
hPhone = datareader.["PHONE1"].ToString();
addrs1 = datareader.["ADDRESS1"].ToString();
city = datareader.["CITY"].ToString();
state = datareader.["STATE"].ToString();
zip = datareader.["ZIP"].ToString();
}

This code is generic by should give you the general idea.
0
 
isda-donAuthor Commented:
Both experts provided solutions on assigning values to the variables.  Assigning aliases for the column names completed the solution.  I feel it's only fair to split the points.  Thanks Experts.
0
All Courses

From novice to tech pro — start learning today.