0

I need to connect a WCF webservice to a SQL Server database. I need to only use SQL to access the data, no LINQ or Entity Framework.

I tried to follow this tutorial: http://www.c-sharpcorner.com/UploadFile/rohatash/inserting-data-into-database-using-wcf-service/ . The SqlConnection class seems to be what I need to use, but when I see this service method:

public string InsertUserDetails(UserDetails userInfo)
    {
        // ...
        SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=Rajesh;User ID=sa;Password=wintellect");
        con.Open();
        SqlCommand cmd = new SqlCommand("insert into RegistrationTable(UserName,Password,Country,Email) values(@UserName,@Password,@Country,@Email)", con);
        cmd.Parameters.AddWithValue("@UserName", userInfo.UserName);
        cmd.Parameters.AddWithValue("@Password", userInfo.Password);
        cmd.Parameters.AddWithValue("@Country", userInfo.Country);
        cmd.Parameters.AddWithValue("@Email", userInfo.Email);
        int result = cmd.ExecuteNonQuery();
        // ...
    }

I'm worried because apparently, a database connection will be created on each request.

Here's a sample of the code structure I would like to have:

public class Service : IService
{       
    public Company GetCompany(int key)
    {
        // Get existing database connection
        // SELECT * from companies where c_key=key
        // Return Company instance
    }
}

What structure should I use to access the data using SQL and not create a database connection on each request? Can I just make the SqlConnection instance static? I don't understand how WCF handle this, how many Service instances will be created etc.. Thank you for your help!

Improve this question
8
  • You can create a static: See answer below Commented Feb 24, 2015 at 19:43
  • What's wrong with connecting every time? Pooling takes care of the overhead. Commented Feb 24, 2015 at 19:44
  • I think ideally the db connection should be kept opened for as long as the service is alive because if x clients connects to the web service at the same time, x database connections will be created. Am I right? What is pooling? @DavidP I would like to do that I just want to make sure that this approach is right! Commented Feb 24, 2015 at 19:55
  • 2
    Yes, if 10 clients connect to the service at the same time, you may end up with 10 database connections -- and this is typically what you want, because you want to service 10 clients at the same time. You can configure WCF to limit the maximum number of clients to what your server can handle -- a typical SQL server can handle hundreds to thousands of connections without a problem; it depends on what those connections are doing. Simple inserts are no problem. If clients connect one by one, you will end up with only one physical connection to the database (the SqlConnection is just a wrapper). Commented Feb 24, 2015 at 21:59
  • 1
    Actually, you may well be creating a completely new instance of the service for every client session (the default instancing mode for WCF services). The only thing I can see wrong with the code snippet you posted is the lack of using statements for the SqlCommand and SqlConnection. Commented Feb 24, 2015 at 22:09

3 Answers 3

Reset to default
3

It is generally best practice to keep a connection open only as long as required.

You can improve performance by managing your resources efficiently. ADO.NET Data Provider objects implement IDisposable which allows you to use using statements to ensure that these objects and their unmanaged resources such as database connections get released properly and predictably.

As soon as you call Dispose() (which in turn calls Close()) on an SqlConnection object - it will release the connection back into the connection pool.

Modern RDBMS's are built to handle thousands upon thousands of connections concurrently. Your part as a developer is to make sure that you keep connections open only as long as needed. So open your connection, run your SQL, get your results, close your connection.

Follow something similar to this: 

using (var conn = new SqlConnection(ConnectionString))
{
     conn.Open();
     using (SqlCommand cmd = conn.CreateCommand())
     {
          cmd.CommandText = "SELECT * FROM SomeTable";
          using (SqlDataReader reader = cmd.ExecuteReader())
          {
               while (reader.Read())
               {
                   // DO SOME WORK
               }
          }
     }
}
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks a lot for the detailed explanation! That makes things very clear.
plus one for the code snip, I answered from my phone and that was just too tedious
0

Your connection code and command code should use a using statement to ensure things get cleaned up properly. When you create the initial connection, pooling will kick in. When the connection disposes it'll be released to the pool to be consumed again. You can control the size of the pool with the connection string.

Improve this answer

Comments

-1

Just make sure you open and close it each time.

public class Service : IService
{       
    private static SQLConnection con;
    public Company GetCompany(int key)
    {
        // Get existing database connection
        // SELECT * from companies where c_key=key
        // Return Company instance
    }
}
Improve this answer

2 Comments

This is bad idea. By making the instance static, the WCF service can process no more than one request at a time. Worse, you need to actually keep track of closing the connection; make an error somewhere and the next call to .Open() (which is blameless) will blow up. Connections are pooled; an SqlConnection does not represent a physical connection and by far the sanest way to deal with them is to limit their scope as much as possible.
If I'm not mistaken, your code example doesn't actually illustrate opening and closing like you suggest in your answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.