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 ?

Have you tried debugging that method?

jrummell
– jrummell

2012-02-29 14:11:49 +00:00
Commented Feb 29, 2012 at 14:11 — jrummell
– jrummell, Commented Feb 29, 2012 at 14:11

user596075 · Accepted Answer · 2012-02-29 14:13:07Z

6

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);

answered Feb 29, 2012 at 14:13

user596075

Sign up to request clarification or add additional context in comments.

1 Comment

sebastian.roibu Over a year ago

thanks a lot. that was my problem. i will accept you answer as soon as i can do that :-d

John Saunders · Accepted Answer · 2012-02-29 14:48:05Z

3

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.

answered Feb 29, 2012 at 14:48

John Saunders

162k26 gold badges252 silver badges403 bronze badges

2 Comments

sebastian.roibu Over a year ago

thanks for your answer. i was just trying to make it work. i will use your method when i want it to be secure and well done :-D

John Saunders Over a year ago

Actually, I recommend you get in the habit of using using blocks for every object you create that implements IDisposable (except for WCF proxy objects). In my experience, "demo" code winds up getting into production fairly often, so "demo" code should not have the more obvious and dangerous bugs in it.

Collectives™ on Stack Overflow

WCF and SQL error

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related