VS 2008 C# Get Values back from SQL

As I continue to learn VS 2008 C# I would like to learn how to reverse the process of sending parameters down to SQL. The sample code shows sending down parameters now I want return values from SQL.  From what I can gather, it is a good practice to have all your C# code calling sp's. So I want to create an sp that does the select as opposed to embedding it inside my code. Can anyone supply links to sample code that would illustrate how to return a set of values from a SQL sp back into the C# code?
SqlCommand cmd = new SqlCommand();

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@COMPANYID", CompanyID));
                cmd.Parameters.Add(new SqlParameter("@DCMTNMBR", VoucherNumber));
                cmd.Parameters.Add(new SqlParameter("@MASTERTYPE", MasterType));

                cmd.CommandText = "rbsUpdateJobLinkerDistSeq";

                cmd.Connection = DataConnection;

                cmd.ExecuteNonQuery();

Open in new window

LVL 1
rwheeler23Asked:
Who is Participating?
 
Carl TawnSystems and Integration DeveloperCommented:
You don't need to use both a DataReader and DataAdapter, you use on or the other. In your scenario it sounds like the DataAdapter approach is more what you are after. So your code for the initial read from the database should be:
DataDataSet = new System.Data.DataSet();
DataDataSet.CaseSensitive = false;

// define the SQL Command
DataCommand = new System.Data.SqlClient.SqlCommand();
DataCommand.Connection = DataConnection;

// create a DataAdapter
DataDataAdapter = new System.Data.SqlClient.SqlDataAdapter();
DataDataAdapter.SelectCommand = DataCommand;
_commandBuilder = new SqlCommandBuilder(DataDataAdapter);

// populate the DataSet from the DataAdapter
DataDataAdapter.Fill(DataDataSet);

Open in new window

0
 
almanderCommented:
ExecuteNonQuery does not return any results

This will put your data into a SqlDataReader
SqlDataReader reader;
SqlCommand cmd = new SqlCommand();

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new SqlParameter("@COMPANYID", CompanyID));
                cmd.Parameters.Add(new SqlParameter("@DCMTNMBR", VoucherNumber));
                cmd.Parameters.Add(new 
SqlParameter("@MASTERTYPE", MasterType));

                cmd.CommandText = "rbsUpdateJobLinkerDistSeq";

                cmd.Connection = DataConnection;

reader = cmd.ExecuteReader();
// Data is accessible through the DataReader object here.

Open in new window

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Carl TawnSystems and Integration DeveloperCommented:
This is using inline SQL rather than an SP, but the principle is the same:

     www.myvirtualplayground.co.uk/blog/Fundamentals_Reading_From_a_Database_Part_1
0
 
rwheeler23Author Commented:
The code I provided was an example of what I was doing to pass parameters down. From your example it appears I simply change it to ExecuteReader and then values flow up from the database? I will check out all of your suggestions. Thank you.
0
 
rwheeler23Author Commented:
Can you please assist with this code. I have taken your code and I tried to merge it with mine. I need help determining the placement of getting the data with the datareader and then putting it in the dataset so I view it on the screen.
SqlDataReader reader;
                SqlCommand cmd = new SqlCommand();

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.CommandText = "rbsSelectVendorBatchFailedImport";

                cmd.Connection = DataConnection;

                reader = cmd.ExecuteReader();
                // Data is accessible through the DataReader object here. 

                DataDataSet = new System.Data.DataSet();
                DataDataSet.CaseSensitive = false;

                DataCommand = new System.Data.SqlClient.SqlCommand();
                DataCommand.Connection = DataConnection;

                DataDataAdapter = new System.Data.SqlClient.SqlDataAdapter();
                DataDataAdapter.SelectCommand = DataCommand;
                _commandBuilder = new SqlCommandBuilder(DataDataAdapter);

                DataDataAdapter.Fill(DataDataSet);

Open in new window

0
 
rwheeler23Author Commented:
For the record, what I am trying to accomplish is present to the user a list of records that failed to import into a table. On each row will be a flag field whereby if the click on this check box, it will update a field on the row so the next time the import routine comes along it will not attempt to import it. So I need to read a dataset, insert the data into a datagridview and then write the entire dataset back out when done.
0
 
rwheeler23Author Commented:
I found that this code completed the link between getting the data back from the SP and filing the dataset.
DataDataSet = new System.Data.DataSet();
                DataDataSet.CaseSensitive = false;
                DataCommand = new System.Data.SqlClient.SqlCommand();
                DataCommand.Connection = DataConnection;
                DataCommand.CommandType = CommandType.StoredProcedure;
                DataCommand.CommandText = "rbsSelectVendorBatchFailedImport";
                DataDataAdapter = new System.Data.SqlClient.SqlDataAdapter();
                DataDataAdapter.SelectCommand = DataCommand;
                _commandBuilder = new SqlCommandBuilder(DataDataAdapter);

                DataDataAdapter.Fill(DataDataSet);

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.