1: using System;
2: using System.Configuration;
3: using System.Data.SqlClient;
4: using System.Linq;
5: using System.Collections.Generic;
6: using MartinOnDotNet.TypeConversion;
7:
8: namespace MartinOnDotNet.Data.SqlClient
9: {
10: /// <summary>
11: /// Handy methods for handling SQL
12: /// </summary>
13: public static class SqlClientExtensions
14: {
15: private static SqlConnection ToConnection(this string connectionStringName)
16: {
17: if (connectionStringName.IsNullOrWhiteSpace()) throw new ArgumentException("Connection String Name cannot be empty", "connectionStringName");
18: ConnectionStringSettings cns = ConfigurationManager.ConnectionStrings[connectionStringName];
19: if (cns == null) throw new ArgumentException("Connection String Name '{0}' cannot be found".ToFormattedString(connectionStringName), "connectionStringName");
20: return new SqlConnection(cns.ConnectionString);
21: }
22:
23: public static void Execute(this string commandText, string connectionStringName)
24: {
25: commandText.Execute(connectionStringName, null);
26: }
27:
28: public static void Execute(this string commandText, string connectionStringName, IDictionary<string, object> parameters)
29: {
30: PerformSqlAction<bool>(commandText, connectionStringName, parameters, (cmd) =>
31: {
32: cmd.ExecuteNonQuery();
33: return true;
34: });
35: }
36:
37: public static T ExecuteScalar<T>(this string commandText, string connectionStringName)
38: {
39: return commandText.ExecuteScalar<T>(connectionStringName, null);
40: }
41:
42: public static T ExecuteScalar<T>(this string commandText, string connectionStringName, IDictionary<string, object> parameters)
43: {
44: return PerformSqlAction<T>(commandText, connectionStringName, parameters, (cmd) =>
45: {
46: object value = cmd.ExecuteScalar();
47: if (value == DBNull.Value) return default(T);
48: return (T)value;
49: });
50: }
51:
52: public static void ProcessRecords(this string commandText, string connectionStringName, Func<SqlDataReader, bool> recordAction)
53: {
54: commandText.ProcessRecords(connectionStringName, recordAction);
55: }
56:
57: public static void ProcessRecords(this string commandText, string connectionStringName, IDictionary<string, object> parameters, Func<SqlDataReader, bool> recordAction)
58: {
59: PerformSqlAction<bool>(commandText, connectionStringName, parameters, (cmd) =>
60: {
61: using (SqlDataReader dr = cmd.ExecuteReader())
62: {
63: int i = 0;
64: try
65: {
66: while (dr.Read() && recordAction(dr)) i++;
67: }
68: catch (Exception ex)
69: {
70: ex.Data["Record Ordinal"] = i;
71: throw;
72: }
73: }
74: return true;
75: });
76: }
77:
78: private static void AddParametersToCommand(IDictionary<string, object> parameters, SqlCommand cmd)
79: {
80: if (parameters != null)
81: {
82: foreach (string key in parameters.Keys)
83: {
84: if (parameters[key] == null)
85: {
86: cmd.Parameters.AddWithValue(key, DBNull.Value);
87: }
88: else if (parameters[key].GetType().IsPrimitive)
89: {
90: cmd.Parameters.AddWithValue(key, parameters[key]);
91: }
92: else
93: {
94: cmd.Parameters.AddWithValue(key, "{0}".ToFormattedString(parameters[key]));
95: }
96:
97: }
98: }
99: }
100:
101: public static bool HasResults(this string commandText, string connectionStringName)
102: {
103: return commandText.HasResults(connectionStringName, null);
104: }
105:
106: public static bool HasResults(this string commandText, string connectionStringName, IDictionary<string, object> parameters)
107: {
108: return PerformSqlAction<bool>(commandText, connectionStringName, parameters, (cmd) =>
109: {
110: using (SqlDataReader dr = cmd.ExecuteReader())
111: {
112: return dr.Read();
113: }
114: });
115: }
116:
117: private static T PerformSqlAction<T>(string commandText, string connectionStringName, IDictionary<string, object> parameters, Func<SqlCommand, T> action)
118: {
119: if (commandText.IsNullOrWhiteSpace()) throw new ArgumentException("Command cannot be empty", "commandText");
120: if (action == null) throw new ArgumentNullException("action");
121: try
122: {
123: using (SqlConnection conn = connectionStringName.ToConnection())
124: using (SqlCommand cmd = conn.CreateCommand())
125: {
126: cmd.CommandText = commandText;
127: AddParametersToCommand(parameters, cmd);
128: conn.Open();
129: return action(cmd);
130: }
131: }
132: catch (Exception ex)
133: {
134: ex.Data["Connection String"] = connectionStringName;
135: ex.Data["Command"] = commandText;
136: if (parameters != null)
137: {
138: ex.Data["Parameters"] = parameters.Keys.Select(k => "{0} = '{1}'".ToFormattedString(k, parameters[k])).ToDelimitedString("\n");
139: }
140: throw;
141: }
142: }
143: }
144: }
