Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

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

0
rwheeler23
Asked:
rwheeler23
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now