How to return SqlDataReader in C# WCF?

Question

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

How to return everything in the SqlDataReader?

Right now I have

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

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

EDIT:

Thanks for the many replies. I am interested in returning the entire SQL data that the service 'creates'. Not online the first column ([0]) - this was only for testing.

But I am not sure on how to get everything from the service back to the client.

• How to return it?

For eg. in Powershell I would create a collection and add objects to that collection, if I had to pass it between clients.

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

Many thanks so far :)

EDIT #2:

Got it! :)

Created a new class (e.g.):

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

In my svc.cs file in top:

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;

That gave me exactly what I wanted :)

Best regards

Answer 1

You need to define DataContract, If you pass list of strings or array of strings your service consumers need to know which index is for which column etc.. that approach will be hard when you adding or deleting column to the service in future. What you can do is create DataContract which having all the properties you need to send and create the operation contract accordingly. Now service consumers can update the service reference in future in case of changing field they will get compiler error. that 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;
}

Sample Data Contract

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

    [DataMember]
    public string Name{ get; set; }
}

And Operation contract

     [OperationContract]
     List<MyDataContract> GetData();

---

in my opinion we need more generic reusable code...

If you have only the .net Service consumers you can return DaTaSet or DataTable from the service method. You need to have SqlDataAdapter instead of sqlReader and Fill the DataTable Or Dataset and Return it. You can parace any number of columns no change in Service method definition. You can even send return type as string by using DataSet.GetXml()

Converting DataTable To Json

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

Answer 2

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