1 /// <summary>
2 /// Standard interface for data access using stored procedures
3 /// </summary>
4 public interface IDataAccess
5 {
6 string ConnectionString { get; set; }
7 SqlConnection CreateConnection();
8 SqlConnection CreateConnectionWithColumnEncryption();
9
10 /// <summary>
11 /// Executes a command that does not return a query
12 /// </summary>
13 /// <param name="commandText">command text to execute</param>
14 /// <param name="conn"></param>
15 /// <param name="parameters">Optional DbParameter collection to use in executing</param>
16 /// <returns>Number of rows that have been effected by the stored procedure execution</returns>
17 int ExecuteNonQuery(string commandText, SqlConnection conn, params SqlParameter[] parameters);
18
19 /// <summary>
20 /// Executes a command and returns a data reader
21 /// </summary>
22 /// <param name="commandText">command text to execute</param>
23 /// <param name="conn"></param>
24 /// <param name="parameters">DbParameter collection to use in executing</param>
25 /// <returns>SqlDataReader allowing access to results from command</returns>
26 IDataReader ExecuteReader(string commandText, SqlConnection conn, params SqlParameter[] parameters);
27
28 /// <summary>
29 ///
30 /// </summary>
31 /// <param name="commandText"></param>
32 /// <param name="conn"></param>
33 /// <param name="parameters"></param>
34 /// <returns></returns>
35 object ExecuteScalar(string commandText, SqlConnection conn, params SqlParameter[] parameters);
36
37 /// <summary>
38 /// ExecuteDataSet
39 /// </summary>
40 /// <param name="commandText"></param>
41 /// <param name="conn"></param>
42 /// <param name="parameters"></param>
43 /// <returns></returns>
44 DataSet ExecuteDataSet(string commandText, SqlConnection conn, params SqlParameter[] parameters);
45
46 }
1 public abstract class BaseDataAccess
2 {
3 protected SqlParameter CreateParameter(string name, SqlDbType sqlDbType, object value,
4 ParameterDirection parameterDirection)
5 {
6 var prm = new SqlParameter(name, sqlDbType)
7 {
8 Direction = parameterDirection
9 };
10 if (value == null)
11 {
12 prm.IsNullable = true;
13 prm.Value = DBNull.Value;
14 }
15 else
16 prm.Value = value;
17
18 return prm;
19 }
20
21 protected SqlParameter CreateParameter(string name, SqlDbType sqlDbType, object value,
22 ParameterDirection parameterDirection, int size)
23 {
24 var prm = CreateParameter(name, sqlDbType, value, parameterDirection);
25 prm.Size = size;
26 return prm;
27 }
28
29 protected SqlParameter CreateParameter(string name, SqlDbType sqlDbType, object value)
30 {
31 return CreateParameter(name, sqlDbType, value, ParameterDirection.Input);
32 }
33
34 protected SqlParameter CreateParameter(string name, SqlDbType sqlDbType, object value, int size)
35 {
36 return CreateParameter(name, sqlDbType, value, ParameterDirection.Input, size);
37 }
38
39 protected string GetCurrentUser()
40 {
41 return System.Security.Principal.WindowsIdentity.GetCurrent().Name;
42 }
43 }
1 public class DataAccess : BaseDataAccess, IDataAccess
2 {
3 private readonly int _commandTimeOut;
4
5 public DataAccess(string connectionString, int commandTimeOut = 60)
6 {
7 if (string.IsNullOrEmpty(connectionString)) throw new ArgumentNullException(nameof(connectionString));
8
9 ConnectionString = connectionString;
10 _commandTimeOut = commandTimeOut;
11
12 }
13
14 public string ConnectionString { get; set; }
15
16 public SqlConnection CreateConnection()
17 {
18 return new SqlConnection(ConnectionString);
19 }
20
21 public SqlConnection CreateConnectionWithColumnEncryption()
22 {
23 return new SqlConnection(ConnectionString + "; Column Encryption Setting=enabled");
24 }
25
26
27 public int ExecuteNonQuery(string commandText, SqlConnection conn, params SqlParameter[] parameters)
28 {
29 using (var cmd = new SqlCommand(commandText, conn))
30 {
31 conn.Open();
32 cmd.CommandTimeout = _commandTimeOut;
33 cmd.CommandType = CommandType.StoredProcedure;
34 if (parameters != null && parameters.Length > 0)
35 cmd.Parameters.AddRange(parameters);
36 var retValue = cmd.ExecuteNonQuery();
37 conn.Close();
38 return retValue;
39 }
40 }
41
42 public IDataReader ExecuteReader(string commandText, SqlConnection conn, params SqlParameter[] parameters)
43 {
44 using (var cmd = new SqlCommand(commandText, conn))
45 {
46 conn.Open();
47 cmd.CommandTimeout = _commandTimeOut;
48 cmd.CommandType = CommandType.Text;
49 if (parameters != null && parameters.Length > 0)
50 cmd.Parameters.AddRange(parameters);
51 var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
52 return reader;
53
54 }
55 }
56
57 public object ExecuteScalar(string commandText, SqlConnection conn, params SqlParameter[] parameters)
58 {
59 using (var cmd = new SqlCommand(commandText, conn))
60 {
61 conn.Open();
62 cmd.CommandTimeout = _commandTimeOut;
63 cmd.CommandType = CommandType.StoredProcedure;
64 if (parameters != null && parameters.Length > 0)
65 cmd.Parameters.AddRange(parameters);
66 var retValue = cmd.ExecuteScalar();
67 conn.Close();
68 return retValue;
69 }
70 }
71
72 public DataSet ExecuteDataSet(string commandText, SqlConnection conn, params SqlParameter[] parameters)
73 {
74 using (var cmd = new SqlCommand(commandText, conn))
75 {
76 DataSet ds = new DataSet();
77 conn.Open();
78 SqlDataAdapter da = new SqlDataAdapter();
79 cmd.CommandTimeout = _commandTimeOut;
80 cmd.CommandType = CommandType.StoredProcedure;
81 da.SelectCommand = cmd;
82 if (parameters != null && parameters.Length > 0)
83 cmd.Parameters.AddRange(parameters);
84 da.Fill(ds);
85 conn.Close();
86 da.Dispose();
87 return ds;
88 }
89 }
90 }
1 private List<Brand> GetBrandFromDB()
2 {
3 List<Brand> brands = new List<Brand>();
4 using (var reader = _dataAccess.ExecuteReader("[GetAllBrand]", _dataAccess.CreateConnection()))
5 {
6 while (reader.Read())
7 {
8 var brand = new Brand
9 {
10 BrandCode = reader.IsDBNull(reader.GetOrdinal("BrandCode"))? null :reader.GetString(reader.GetOrdinal("BrandCode")).Trim(),
11 BrandName = reader.GetString(reader.GetOrdinal("BrandName ")).Trim(),
12 BrandValue = reader.GetString(reader.GetOrdinal("BrandValue")).Trim()
13 };
14 brandMaps.Add(brand);
15 }
16
17 }
18 return brands;
19 }
1 private List<Brand> InsertBrand(Brand brand)
2 {
3 lock (this)
4 {
5 objectBrandKey = _dataAccess.ExecuteScalar("dbo.InsertBrand", _dataAccess.CreateConnection(),
6 CreateParameter("@pRequestKey", SqlDbType.UniqueIdentifier, Guid.Parse(brand.RequestKey)),
7
8 CreateParameter("@pBrandKey", SqlDbType.SmallInt, brand.BrandKey),
9 CreateParameter("@pBrandName", SqlDbType.VarChar, brand.BrandName),
10 CreateParameter("@pBrandValue", SqlDbType.VarChar, brand.BrandValue));
11
12
13 return Convert.ToInt64(objectBrandKey);
14 }
15 }
