Retrieve Data from Stored Proc

[mikasa]

Ok, does anyone know how to retrieve Data that is generated from a Stored proc into an ADO.NET DataSet or DataTable? This used to be very easy in VB6... :wtf: I just need a sample to lead me on the right track.

[Colin Angus Mackay]

In the stored procedure you just SELECT the relevant data. In the .NET application open a SqlConnection, create a SqlCommand to call the Stored Procedure and then call SqlCommand.ExecuteQuery() to get a DataReader to extract it* Does this help? *You can also uses crazy things like DataAdapters and DataSets, but y'know I don't really like them

---

"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell The Second EuroCPian Event will be in Brussels on the 4th of September Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!

[mikasa]

Sorry, I should have been more specific. Unfortunately, I do need to get this data into a DataTable or DataSet. Anyone have an idea? I know the DataAdapter uses a DataReader internally, but I don't know how I could implement this...I'm sure it would be super difficult.

[rudy net]

In the "Server Explorer" window select the connection to your database that contains the stored procedure and drag it to your form. This will create a SqlConnection object and a SqlCommand object, for example conn and cmd. Then you can fill your DataSet with the following commands: conn.Open(); SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd); DataSet dataset = new DataSet("datasetName"); dataAdapter.Fill(dataset); conn.Close();

[mikasa]

Yeah, that's not going to work either, tried that before... The problem is that the Stored Proc is Dynamically creating Columns and Data depending on what Parameters you pass to it. Even in the Designer, I can't get it to Generate a DataSet because ADO.NET what hard-defined structures of Data when working with the Designer. However, I can run the Data Preview and it works fine, but that is because in the "Preview" mode, the data is being retrieved through a DataReader, not a DataTable. Why can't the DataCommand Object just return a DataTable like it did in VB6!? :sigh:

[Michael Potter]

I have no trouble executing SPs like you descibe. Here is a sample of code I just wrote today:

public static DataSet GetEventList(
     int activeState, 
     int eventId,
     int eventDateStatus,
     DateTime eventDateStart,
     DateTime eventDateEnd,
     int createDateStatus,
     DateTime createDateStart,
     DateTime createDateEnd,
     int ntdStatus,
     int eventTypeId,
     string firstName,
     string lastName)
{
     DataSet ds = null;
     SqlCommand cmd = null;
     SqlDataAdapter da = null;

     SqlConnection conn = GetConnection();
     if (conn != null)
     {
          try
          {
               cmd = new SqlCommand("EventListGet",conn);
               cmd.CommandType = CommandType.StoredProcedure;

               SqlParameter p = cmd.Parameters.Add("@ActiveState",SqlDbType.Int);
               p.Value = activeState;
                    
               p = cmd.Parameters.Add("@EventId",SqlDbType.Int);
               p.Value = eventId;

               p = cmd.Parameters.Add("@EventDateStatus",SqlDbType.Int);
               p.Value = eventDateStatus;
                    
               p = cmd.Parameters.Add("@EventDateStart",SqlDbType.DateTime);
               p.Value = eventDateStart;
                    
               p = cmd.Parameters.Add("@EventDateEnd",SqlDbType.DateTime);
               p.Value = eventDateEnd;

               p = cmd.Parameters.Add("@CreateDateStatus",SqlDbType.Int);
               p.Value = createDateStatus;
					
               p = cmd.Parameters.Add("@CreateDateStart",SqlDbType.DateTime);
               p.Value = createDateStart;
					
               p = cmd.Parameters.Add("@CreateDateEnd",SqlDbType.DateTime);
               p.Value = createDateEnd;

               p = cmd.Parameters.Add("@NTDState",SqlDbType.Int);
               p.Value = ntdStatus;

               p = cmd.Parameters.Add("@EventTypeId",SqlDbType.Int);
               p.Value = eventTypeId;

               p = cmd.Parameters.Add("@FirstName",SqlDbType.VarChar,50);
               p.Value = firstName;

               p = cmd.Parameters.Add("@LastName",SqlDbType.VarChar,50);
               p.Value = lastName;
					
               da = new SqlDataAdapter(cmd);
               ds = new DataSet();
               da.Fill(ds);
          }
          catch (Exception ex)
          {
               MessageBox.Show(ex.Message,"Load Event List Failed");
               if (ds != null)
               {
                    ds.Dispo

[mikasa]

Ok, this is very similar to what I tried. Do you think it matters if the Stored Proc is creating and using a Temporary Table (ex. ##TempTable)? I get errors on my Temp Table in the Designer. It doesn't make any sense because I can run this in the Query Analyzer without fail every time!

[Michael Potter]

Are you sure the table is being DROP'ed properly at the end of your SP? What is the error you are getting?