ADO.Net DataAccess 常用方法ExecuteNonQuery ExecuteReader ExecuteDataSet

 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         }