Data Layer Without Linq

A good n-tiered approach is to create a [ProjectName].Data class library.

Create a base class.

public class SqlDataCaller

{

private static SqlConnection getConnection()

{

return new SqlConnection(

ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);

}

protected static DataSet ExecuteSPDataSet(String spName, SqlParameter[] parameters)

{

DataSet ds = new DataSet();

using (SqlConnection connection = getConnection())

{

try

{

connection.Open();

}

catch (SqlException ex)

{

throw ex;

}

SqlDataAdapter da = new SqlDataAdapter(spName, connection);

da.SelectCommand.CommandType = CommandType.StoredProcedure;

if (parameters != null)

da.SelectCommand.Parameters.AddRange(parameters);

try

{

da.Fill(ds);

}

catch (SqlException ex)

{

throw ex;

}

connection.Close();

}

return ds;

}

}

Then in their own namespaces, create classes to pass a DataTable to the business objects.

namespace [Project Name].Data.People

{

using System;

using System.Data;

using System.Data.SqlClient;

using System.Collections.Generic;

using System.Text;

using [Project Name].Data.Base;

///

/// Class responsible for all data access regarding people

///

public class PeopleDAC : SqlDataCaller

{

public const String ColName_PersonId = "PersonId";

public const String ColName_Name = "Name";

public const String ColName_Region = "RegionName";

public const String ColName_DateTime = "TimeDateStamp";

public static DataTable GetPerson(Int32 personId)

{

String spName = "example_GetPerson";

SqlParameter[] parameters = new SqlParameter[1];

parameters[0] = new SqlParameter("@PersonId", SqlDbType.Int, 4);

parameters[0].Value = count;

DataSet ds = ExecuteSPDataSet(spName, parameters);

return ds.Tables[0];

}

}

}