C# - Sqldatareader And Serialization

Can an SqlDataReader be passed to a session or sent to a client? For instance, if I retrieved some rows from a database, and want to send this data to another client machine. Can

Solution 1:

No, only data (no methods or functionality) can be serialized, so the data reader would be useless since you could not call methods to advance the reader, etc. The pattern would be to read all the records into a list of objects from the reader, close it, and then serialize those objects back to the client.

Solution 2:

No, any resource-related object cannot be "passed" to a client.

This wouldn't really make sense. You should "materialize" the data reader and pass the results.

I.e. you can create an SqlDataAdapter from your reader, fill a DataTable and pass the DataTable.

Solution 3:

No, you have to map the reader to a POCO and that will be serialized. Even if you can technically serialize the SqlDatReader object, it's a VERY BAD Idea. Don't do it. It's trivial to use a micro-orm to map a query to a DTO. Don't complicate your work.

Solution 4:

Old Question, but technology provide new solutions.

You can use Protocol Buffers DataReader Extensions for .NET to Serialize SqlDataReader and can be passed to a session or sent to a client

Example:

string connstring1 = "Data Source=server1;Initial Catalog=northwind;user=xxx;password=xxx";

        //Serializing an IDataReader into a ProtoBuf:

        Stream buffer = new MemoryStream();
        using (var c1 = new SqlConnection(connstring1))
        {
           c1.Open();
            // Serialize SQL results to a bufferusing (var command = new SqlCommand("SELECT * FROM products", c1))
            using (var reader = command.ExecuteReader())
                DataSerializer.Serialize(buffer, reader);

            // Read them back
            buffer.Seek(0, SeekOrigin.Begin);
            using (var reader = DataSerializer.Deserialize(buffer))
            {
                while (reader.Read())
                {
                    Console.WriteLine(reader["ProductName"]);
                }
            }
        }

    }

you should install nuget package:

   Install-Package protobuf-net-data