We help IT Professionals succeed at work.

VS 2008 C# Get Values back from SQL

rwheeler23
rwheeler23 asked
on
297 Views
Last Modified: 2012-08-13
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

Comment
Watch Question

Commented:
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

Paul JacksonSoftware Engineer
CERTIFIED EXPERT
Top Expert 2011

Commented:
Carl TawnSenior Systems and Integration Developer
CERTIFIED EXPERT

Commented:
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
rwheeler23President

Author

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.
rwheeler23President

Author

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

rwheeler23President

Author

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.
Senior Systems and Integration Developer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
rwheeler23President

Author

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

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.