How to return SqlDataReader in C # WCF?

Question

In my C # WCF service, I have a SqlDataReader that contains data rows that I would like to return to the client.

How to return everything to SqlDataReader ?

I have now

 if (sqlReader != null) { if (sqlReader.HasRows) { while (sqlReader.Read()) { return sqlReader[0].ToString(); } sqlConn.Close(); } else { return null; } } 

This returns only the first result. The return type of the class is the string at the moment. I was thinking of something like an array in an array, but I'm not sure how?

EDIT:

Thanks for answers. I am interested in returning all the SQL data that the service creates. Offline first column ([0]) - this was for testing only.

But I'm not sure how to return everything from the service back to the client.

• How to return it?

For example, in Powershell, I would create a collection and add objects to this collection if I had to transfer it between clients.

I am looking for something similar in C # and WCF.

Thanks so far :)

EDIT # 2:

Got!:)

A new class has been created (for example):

 public class ObjectNotification { public string AlertDescription; public string Servername; } 

In my svc.cs file above:

 List<ObjectNotification> objlist = new List<ObjectNotification>(); 

and

  if (sqlReader.HasRows) { while (sqlReader.Read()) { ObjectNotification obj = new ObjectNotification(); obj.AlertDescription = sqlReader["AlertDescription"].ToString(); obj.Servername = sqlReader["ComputerName"].ToString(); objlist.Add(obj); } } return objlist; 

This gave me exactly what I wanted :)

Best wishes

Answer 1

 // for instance List<string> list = new List<string>(); if (sqlReader != null) { if (sqlReader.HasRows) { while (sqlReader.Read()) { //return sqlReader[0].ToString(); list.Add(sqlReader[0].ToString()); } sqlConn.Close(); } else { return null; } } return list; // ta-da 

Answer 2

You need to define a DataContract. If you are passing a list of rows or an array of strings, your service consumers need to know which index for which column, etc., this approach will be difficult when adding or removing a column to the service in the future. What you can do is create a DataContract. which has all the properties necessary to send and create a contract for execution. Now service consumers can update the help desk in the future if the field changes, they will receive a compiler error. which is easy to identify.

 public List<MyDataContract> GetData() { List<MyDataContract> list = new List<MyDataContract>(); //your code if (sqlReader != null) { if (sqlReader.HasRows) { while (sqlReader.Read()) { list.Add(new MyDataContract() { Id = (int)sqlReader["Id"].ToString(), Name= sqlReader = sqlReader["Name"].ToString() }); } sqlConn.Close(); } } //finally return list of data return list; } 

Contract data example

 [DataContract] public class MyDataContract { [DataMember] public int Id{ get; set; } [DataMember] public string Name{ get; set; } } 

And operating contract

  [OperationContract] List<MyDataContract> GetData(); 

---

in my opinion we need a more general reusable code ...

If you have only .net service users, you can return DaTaSet or DataTable from the service method. You need to have a SqlDataAdapter instead of sqlReader and populate a DataTable or Dataset and return it. You can put any number of columns without changing the service method definition. You can even send the return type as a string using DataSet.GetXml()

Convert DataTable to Json

 string json = JsonConvert.SerializeObject(table, Formatting.Indented); 

Answer 3

SqlReader is designed to minimize memory performance and, therefore, will request the result only one by one. If you want to get all the results, you can use the while loop around your code to extract the lines as long as they are available. By calling return, you break the while loop and return only the first line. If you call a return income, then IEnumerable<string> will be returned by your method instead of string .

Answer 4

Perhaps you can create an array or List and populate the list with your sqlReader. The list and array can be serialized and passed using WCF and are also compatible.

Inside while loop

 listObject.Add(sqlReader[0].ToString()); 

Answer 5

You need to really read all the datareader lines, loading them into a datatable, the datareader is a lazyload object and cannot be sent over the network, since it is never completely populated at the beginning:

 DataTable dt=new DataTable(); dt.Load(dr); 

Then, since you are using wcf, you may need to load this data type into an object that matches your contract interface. Take a look at the snippet below, which converts an arbitrary data type into suitable object properties with good accounting, using reflection.

Using snippet

 List<myType> r = (List<myType>) dt.ToList<myType>(); 

Excerpt

  using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Reflection; namespace xxxx.Sql.Extensions { public static class DataTableExtensions { /// <summary> /// Gets a list of objects based on a generic datatable /// </summary> /// <typeparam name="T">List of objects</typeparam> /// <param name="table">Existing datatable</param> /// <returns></returns> public static IList<T> ToList<T>(this DataTable table) where T : new() { IList<PropertyInfo> properties = typeof(T).GetProperties().ToList(); IList<T> result = new List<T>(); foreach (var row in table.Rows) { var item = CreateItemFromRow<T>((DataRow)row, properties); result.Add(item); } return result; } private static T CreateItemFromRow<T>(DataRow row, IList<PropertyInfo> properties) where T : new() { T item = new T(); foreach (var property in properties) { if (row.Table.Columns.Contains(property.Name)) { var prop = row[property.Name] == System.DBNull.Value ? null : row[property.Name]; property.SetValue(item, prop, null); } } return item; } /// <summary> /// Creat a generic string list on the first field of a dataTable /// </summary> /// <param name="table"></param> /// <returns></returns> public static List<string> ToStringList(this DataTable table) { List<string> result = new List<string>(); foreach (DataRow dr in table.Rows) result.Add(dr[0].ToString()); return result; } } } 

Answer 6

An ancient thread, but I will add two of my own:

  string sqltext = @"select a.columnown, a.columntwo, a.columnthreee from blahblah as a"; List<List<string>> toReturn = new List<List<string>>(); using (SqlConnection con = new SqlConnection("YourConnectionString")) { con.Open(); SqlCommand cmd = con.CreateCommand(); cmd.CommandText = sqlTest; using (SqlDataReader sqlReader = cmd.ExecuteReader()) { if (sqlReader != null) { if (sqlReader.HasRows) { while (sqlReader.Read()) { List<string> innerList = new List<string>(); for (int i = 0; i < sqlReader.FieldCount; i++) { innerList.Add(sqlReader[i].ToString()); } toReturn.Add(innerList); } con.Close(); } } } } 

then you just simply return List<List<string>>