This is the companion web page for my video on creating a
simple data access class:
https://www.youtube.com/watch?v=zelFDKfJA18
The code below demonstrates how to write a simple data
access class to consume the stored procedure developed to access a table of
names by First Name, Last Name, Middle Initial, Social Security Number and/or
Date of Birth.
Check out the series on Youtube:
See how to create a randomly generated table of real looking
names for testing or demonstration purposes, an SQL Server Stored Procedure to lookup
the names and a data access class written in C# to consume the sproc:
Creating Meaningful Test Data: http://youtu.be/4pK_D3oX72Y
Creating Meaningful Test Data – Addendum: http://youtu.be/Sy4IhYehXeo
A Stored Procedure to Look Up Names: http://youtu.be/zI2PCGyqMIE
Simple Data Access Class: http://youtu.be/zelFDKfJA18
namespace DataAccess.Models
{
public class Name
{
public int Id { get; set; }
public string
FirstName { get; set;
}
public string
MiddleInitial { get; set;
}
public string
LastName { get; set;
}
public string SSN { get; set; }
public DateTime?
DOB { get; set;
}
}
}
namespace DataAccess.Models
{
public class NameResults
{
public string Status
{ get; set; }
public List<Name> Names { get;
set; }
}
}
namespace DataAccess
{
public class Repository
{
private const string GET_NAMES = "GetNames";
private string
_connectionString;
public string
ConnectionString
{
get
{
return _connectionString;
}
}
private SqlConnection
GetOpenConnection()
{
var connection = new SqlConnection(_connectionString);
connection.Open();
return connection;
}
public Repository(string
connectionString)
{
_connectionString = connectionString;
}
public List<Name> GetNames(string
LastName = null, string
FirstName = null,
string MiddleInitial = null,
string SSN = null,
DateTime? DOB = null)
{
var names = new List<Name>();
using (var connection
= GetOpenConnection())
{
using (var
command = new SqlCommand(GET_NAMES,
connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@LastName",
SqlDbType.NVarChar, 50));
command.Parameters["@LastName"].Value
= LastName;
command.Parameters.Add(new SqlParameter("@FirstName",
SqlDbType.NVarChar, 50));
command.Parameters["@FirstName"].Value
= FirstName;
command.Parameters.Add(new SqlParameter("@MiddleInitial",
SqlDbType.NVarChar, 1));
command.Parameters["@MiddleInitial"].Value
= MiddleInitial;
command.Parameters.Add(new SqlParameter("@SSN",
SqlDbType.VarChar,
11));
command.Parameters["@SSN"].Value
= SSN;
command.Parameters.Add(new SqlParameter("@DOB",
SqlDbType.DateTime));
command.Parameters["@DOB"].Value
= DOB;
using (var
reader = command.ExecuteReader())
{
while (reader.Read())
{
names.Add (new Name
{
Id = (int)reader["Id"],
FirstName =
reader["FirstName"].ToString(),
MiddleInitial =
reader["MiddleInitial"].ToString(),
LastName =
reader["LastName"].ToString(),
SSN = reader["SSN"].ToString(),
DOB = (DateTime)reader["DOB"]
});
}
}
}
}
return names;
}
}
}