WCF and SQL error

Question

I am building a simple WCF service that has to return some data from an SQL table. When i run the project i get the following error:

Failed to invoke the service. Possible causes: The service is offline or inaccessible; the client-side configuration does not match the proxy; the existing proxy is invalid. Refer to the stack trace for more detail. You can try to recover by starting a new proxy, restoring to default configuration, or refreshing the service

If i comment all the SQL part and send some static data everything is working fine. This is the function that is giving me headache :

public Client getClient(int idClient)
{
    Client c = new Client();
    SqlConnection sql = new SqlConnection(@"Data Source=GRIGORE\SQLEXPRESS;Initial Catalog=testWCF;Integrated Security=True");
    sql.Open();

    SqlCommand cmd = new SqlCommand("Select * from Clienti where id = " + idClient);
    SqlDataReader dr = cmd.ExecuteReader();
    if (dr.Read())
    {
        c.idClient = int.Parse(dr["id"].ToString());
        c.numeClient = dr["nume"].ToString();
    }

    dr.Close();
    sql.Close();

    return c;
}

Ideas ?

Answer 1

You're not setting the Connection property of your SqlCommand instance. You need to do this:

    SqlCommand cmd = new SqlCommand("Select * from Clienti where id = " + idClient);
    cmd.Connection = sql;  // added Connection property initialization
    SqlDataReader dr = cmd.ExecuteReader();

Or you can just inject this into your constructor:

    SqlCommand cmd = new SqlCommand("...your query text", sql);

Answer 2

Actually, your code should be more like this:

public Client getClient(int idClient)
{
    var c = new Client();
    using (var sql = new SqlConnection(@"Data Source=GRIGORE\SQLEXPRESS;Initial Catalog=testWCF;Integrated Security=True"))
    {
        sql.Open();

        using (var cmd = new SqlCommand("Select * from Clienti where id = " + idClient, sql))
        {
            using (SqlDataReader dr = cmd.ExecuteReader())
            {
                if (dr.Read())
                {
                    c.idClient = int.Parse(dr["id"].ToString());
                    c.numeClient = dr["nume"].ToString();
                }
            }
        }
    }

    return c;
}

By placing the connection, command and reader in using blocks, you ensure that they are disposed of, even if an exception is thrown. You also don't need the explicit Close calls in this case, as disposing of the objects takes care of that for you.