Chris said:
hi,
I have to create a small website in ASP.Net 2.0. I have question about
working with SQL connections. What are the best practises? How and where
to
create and store a connection? I see a few possibilities:
- create, open/close a connection on each web page
- create a connection on login page and store it on Session
- how to create a connection? ie. SqlConnection = new SqlConnection or
System.Data.Common.DbProviderFactory factory =
System.Data.Common.DbProviderFactories.GetFactory("System.Data.SqlClient");
System.Data.Common.DbConnection conn =
factory.CreateConnection();
thx in advance
You should have the connection string in web.config and have a class or
something which exposes it.
You don't want to hold a connection open.
( google connection pooling ).
Your code above is doing some fancy factory class creation stuff by the look
of it.
If you have a small project I wouldn't worry about that for now.
I'd keep it simple and have something like a class per table.
If you have no nulls then substitute datareader, if you do then google
nullabledatareader.
I've hacked this code up a bit to simplify it, so it started as (old) live
code then I probably broke it.
So the method will open up a connection, read a bunch of data, chuck it into
a typed list, close the connection and return the list.
The class is (mostly) generated by an app I wrote, I can provide a copy.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Collections.Generic;
using NullableReaders;
namespace Biz
{
public class People
{
public People()
{ }
private string _UserId;
private string _NetworkID;
private string _EmpNo;
private DateTime? _LeavingDate;
public string UserId
{
get
{
return _UserId;
}
set
{
_UserId = value;
}
}
public string NetworkID
{
get
{
return _NetworkID;
}
set
{
_NetworkID = value;
}
}
public string EmpNo
{
get
{
return _EmpNo;
}
set
{
_EmpNo = value;
}
public DateTime? LeavingDate
{
get
{
return _LeavingDate;
}
set
{
_LeavingDate = value;
}
}
public IList<People> GetPeople()
{
IList<People> People1 = new List<People>();
using (SqlConnection sqlConn = new SqlConnection(DB.connString))
{
sqlConn.Open();
SqlCommand sqlCmd = sqlConn.CreateCommand();
sqlCmd.CommandText = "select UserId, NetworkID, EmpNo, LeavingDate from
People";
using (NullableDataReader reader = new
NullableDataReader(sqlCmd.ExecuteReader()))
{
while (reader.Read())
{
People c = new People();
c.UserId = reader.GetString("UserId");
c.NetworkID = reader.GetString("NetworkID");
c.EmpNo = reader.GetString("EmpNo");
c.LeavingDate = reader.GetNullableDateTime("LeavingDate");
People1.Add(c);
}
return People1;
}
}
}
}
}
