Best way to handle sql-connections in webservices?

[Guest]

Hello, what is the proper way to handle sql-connections in webservices?

1. Creating a SqlConnection object and opening connection in each web
method? Example:

[WebMethod]
public Clients GetClients()
{

using(SqlConnection conn = new SqlConnection(connstring))
{
conn.Open();
using(SqlCommand
.......
}
}

2. Or maybe define SqlConnection once as public variable and in every
web method only opening this connection?
Example:

public class Service1 : System.Web.Services.WebService
{
public SqlConnection conn1 = new SqlConnection();
....
....
[WebMethod]
public Clients GetClients()
{
conn.Open();
using(SqlConnection = new ...
...
}

Which is a good example?

 

[Mr. Arnold]

Hello, what is the proper way to handle sql-connections in webservices?

1. Creating a SqlConnection object and opening connection in each web
method? Example:

[WebMethod]
public Clients GetClients()
{

using(SqlConnection conn = new SqlConnection(connstring))
{
conn.Open();
using(SqlCommand
.......
}
}

2. Or maybe define SqlConnection once as public variable and in every web
method only opening this connection?
Example:

public class Service1 : System.Web.Services.WebService
{
public SqlConnection conn1 = new SqlConnection();
...
...
[WebMethod]
public Clients GetClients()
{
conn.Open();
using(SqlConnection = new ...
...
}

Which is a good example?

Neither one of them are good examples, as a DAL (Data Access Layer) method
should be responsible for its data access of opening the connection,
accessing the data, closing the connection and returning the data to the Web
method and not the Web method doing it.

[WebMethod]
public Clients GetClients()
{
DAL dal = new DAL();

return dal.GetClients();
}


__________ Information from ESET NOD32 Antivirus, version of virus signature database 4530 (20091021) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

 

[CSharpner]

I second Mr Arnold's advice.

Hello, what is the proper way to handle sql-connections in webservices?

1. Creating a SqlConnection object and opening connection in each web
method? Example:

[WebMethod]
public Clients GetClients()
{

  using(SqlConnection conn = new SqlConnection(connstring))
  {
conn.Open();
using(SqlCommand
  .......
  }
}

2. Or maybe define SqlConnection once as public variable and in every web
method only opening this connection?
Example:

public class Service1 : System.Web.Services.WebService
{
  public SqlConnection conn1 = new SqlConnection();
...
...
[WebMethod]
public Clients GetClients()
{
  conn.Open();
  using(SqlConnection = new ...
  ...
}

Which is a good example?

Neither one of them are  good examples, as a DAL (Data Access Layer) method
should be responsible for its data access of opening the connection,
accessing the data, closing the connection and returning the data to the Web
method and not the Web method doing it.

 [WebMethod]
 public Clients GetClients()
 {
       DAL dal = new DAL();

       return dal.GetClients();
 }

__________ Information from ESET NOD32 Antivirus, version of virus signature database 4530 (20091021) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com