Simple Data Access Class
Language(s):C#, SQL Server
Category(s):Data Access
This is the companion web page for my video on creating a simple data access class: https://www.youtube.com/watch?v=zelFDKfJA18

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;

        }

    }

}

This article has been viewed 4318 times.
The examples on this page are presented "as is". They may be used in code as long as credit is given to the original author. Contents of this page may not be reproduced or published in any other manner what so ever without written permission from Idioma Software Inc.