Run a stored procedure by returning multiple tables without using SqlDataAdapter.Fill ()?

Question

I know that the usually recommended way to populate a DataSet instance from a stored procedure is to use SqlDataAdapter.Fill (DataSet). Every answer I could find says that he uses it the way it appears out of the box. I will give it ok, it works fine.

However, it is interrupted when you need to process data between the output of a stored procedure and the input of a DataSet. In particular, when using the custom CLR type in the output, it breaks into an InvalidOperationException: DataReader.GetFieldType(n) returned null. , where n is the index of the column column (in one of the tables, there seems to be no obvious way to indicate in which table the index of the link column is indicated), which uses the CLR type in its output. This is understandable because the reader has no idea what data is in the column in question.

I can break the CLR deserialization logic and use it in both places , but . I don't seem to have time to call any such deserialization method before Fill () breaks.

I know about SqlCommand.ExecuteReader() , but it might seem that only one table has exited this.

The CLR type in question is intended to replace one of the main system fields (which is currently stored as free-form text) with something more stringent. The issue of moving through the database and adding a method call to each returned instance of such a field was discussed as an opportunity, but it would definitely be a non-trivial amount of work. Thus, it was believed that the conversion could be performed in DAL instead (it is only required that such columns can be identified programmatically, which is doable), which makes the storage transparent to the client and the client using data that is still transparent to the database.

Thus: How to access all tables in the output of a stored procedure without using SqlDataAdapter.Fill ()? Alternatively , how can I connect to the SqlDataAdapter.Fill () execution for manual processing between SP execution and populating the DataSet?

Answer 1

You can select multiple tables with DataReader . You can use reader.NextResult to check if there are more result sets and push the data reader to it:

 using (var con = new SqlConnection(Properties.Settings.Default.ConnectionString)) { using (var cmd = new SqlCommand("StoredProcedureName", con)) { cmd.CommandType = CommandType.StoredProcedure; int rowCount = 0; con.Open(); using (IDataReader rdr = cmd.ExecuteReader()) { while (rdr.Read()) { Console.WriteLine("Object 1 in Row {0}: '{1}'", ++rowCount, rdr[0]); } if (rdr.NextResult()) { rowCount = 0; while (rdr.Read()) { Console.WriteLine("Object 1 in Row {0}: '{1}'", ++rowCount, rdr[0]); } } } } } 

Answer 2

It seems like it was actually easier than I thought. While Tim Schmelter's answer made me experiment, the real trick was to simply add an assembly of type CLR to the DAL project . Then everything fell into place with SqlDataAdapter.Fill(DataSet) ; all of a sudden he knew what to do with binary data received from SQL Server, apparently (not verified, but plausible) calling the ToString() . I have not yet verified what happens when the DAL code creates a DataSet that is used elsewhere, but between the two, I think it will be possible to find a working solution.