/[AnywhereTS-MSSQL]/trunk/AnywhereTS.DBSupport/DBConnector.cs
ViewVC logotype

Diff of /trunk/AnywhereTS.DBSupport/DBConnector.cs

Parent Directory Parent Directory | Revision Log Revision Log | View Patch Patch

revision 86 by william, Fri Jul 13 21:10:26 2012 UTC revision 87 by william, Fri Jul 13 22:37:40 2012 UTC
# Line 4  using System.Text; Line 4  using System.Text;
4  using System.Data.SqlClient;  using System.Data.SqlClient;
5  using System.Data.Common;  using System.Data.Common;
6  using System.Data;  using System.Data;
7    using System.Text.RegularExpressions;
8    
9  namespace AnywhereTS.DBSupport  namespace AnywhereTS.DBSupport
10  {  {
# Line 21  namespace AnywhereTS.DBSupport Line 22  namespace AnywhereTS.DBSupport
22          DbDataReader ExecuteQuery(string command, List<DBParameter> Params, out Exception ErrorInfo);          DbDataReader ExecuteQuery(string command, List<DBParameter> Params, out Exception ErrorInfo);
23          void ExecuteNonQuery(string command, List<DBParameter> Params, out Exception ErrorInfo);          void ExecuteNonQuery(string command, List<DBParameter> Params, out Exception ErrorInfo);
24          List<string> ExecuteColumnNamesReader(string command, List<DBParameter> Params, out Exception ErrorInfo);          List<string> ExecuteColumnNamesReader(string command, List<DBParameter> Params, out Exception ErrorInfo);
25    
26            DBCommand CreateCommandInstance(string command, List<DBParameter> Params, out Exception ErrorInfo);
27            public bool RunScript(string strFile, MsSqlConnector con, out Exception ErrorInfo);
28      }      }
29      public abstract class DBConnector<DBParameter, DBCommand, DBConnection, DBDataAdapter> :      public abstract class DBConnector<DBParameter, DBCommand, DBConnection, DBDataAdapter> :
30          IDBConnector<DBParameter, DBCommand, DBConnection, DBDataAdapter>          IDBConnector<DBParameter, DBCommand, DBConnection, DBDataAdapter>
# Line 32  namespace AnywhereTS.DBSupport Line 36  namespace AnywhereTS.DBSupport
36    
37          public DBConnector(string DBServerAddress, string DBServerInstance, string DBDatabase)          public DBConnector(string DBServerAddress, string DBServerInstance, string DBDatabase)
38          {          {
39                this.DBServerAddress = DBServerAddress;
40                this.DBServerInstance = DBServerInstance;
41                this.DBDatabase = DBDatabase;
42          }          }
43    
44          protected DBConnection connection;          protected DBConnection connection;
45          protected static string DBServerAddress = "";          protected string DBServerAddress = "";
46          protected static string DBServerInstance = "";          protected string DBServerInstance = "";
47          protected static string DBDatabase = "";          protected string DBDatabase = "";
48          private string SafeSqlLiteral(string inputSQL) { return inputSQL.Replace("'", "''"); }          private string SafeSqlLiteral(string inputSQL) { return inputSQL.Replace("'", "''"); }
49          #region IDBConnector members          #region IDBConnector members
50            public DBCommand CreateCommandInstance(string command, List<DBParameter> Params, out Exception ErrorInfo)
51            {
52                ErrorInfo = null;
53                try
54                {
55                    command = this.SafeSqlLiteral(command);
56                    DBCommand sqlComm = new DBCommand();
57                    sqlComm.CommandText = command;
58                    sqlComm.Connection = connection;
59                    foreach (DBParameter p in Params) { sqlComm.Parameters.Add(p); }
60                    return sqlComm;
61                }
62                catch (SqlException ex)
63                {
64                    using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", ex.Number.ToString(), ex.Message, System.Environment.NewLine, ex.ToString())))
65                    {
66                        Logging.DatabaseLog.Error(string.Format("Failed to create command instance using command: {0}", command));
67                    }
68                    ErrorInfo = ex; throw ErrorInfo;
69                }
70                catch (Exception ex)
71                {
72                    using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
73                    {
74                        Logging.DatabaseLog.Error(string.Format("Failed to create command instance using command: {0}", command));
75                    }
76                    ErrorInfo = ex; throw ErrorInfo;
77                }
78            }
79          private bool _ConnectionIsOpen;          private bool _ConnectionIsOpen;
80          public virtual bool ConnectionIsOpen { get { return _ConnectionIsOpen; } protected set { _ConnectionIsOpen = value; } }          public virtual bool ConnectionIsOpen { get { return _ConnectionIsOpen; } protected set { _ConnectionIsOpen = value; } }
81          public virtual void CreateConnection(out Exception ErrorInfo)          public virtual void CreateConnection(out Exception ErrorInfo)
# Line 52  namespace AnywhereTS.DBSupport Line 88  namespace AnywhereTS.DBSupport
88                  connection = new DBConnection();                  connection = new DBConnection();
89                  connection.ConnectionString = connetionString;                  connection.ConnectionString = connetionString;
90              }              }
91              //catch (SqlException ex) { Console.WriteLine(ex.ToString()); ErrorInfo = ex;  throw ErrorInfo;}              catch (SqlException ex)
92              catch (Exception ex) { Console.WriteLine(ex.ToString()); ErrorInfo = ex; throw ErrorInfo; }              {
93                    using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", ex.Number.ToString(), ex.Message, System.Environment.NewLine, ex.ToString())))
94                    {
95                        Logging.DatabaseLog.Error(string.Format("Failed to create connection to {0} Database", DBDatabase));
96                    }
97                    ErrorInfo = ex; throw ErrorInfo;
98                }
99                catch (Exception ex)
100                {
101                    using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
102                    {
103                        Logging.DatabaseLog.Error(string.Format("Failed to create connection to {0} Database", DBDatabase));
104                    }
105                    ErrorInfo = ex; throw ErrorInfo;
106                }
107          }          }
108          public virtual void OpenConnection(out Exception ErrorInfo)          public virtual void OpenConnection(out Exception ErrorInfo)
109          {          {
# Line 65  namespace AnywhereTS.DBSupport Line 115  namespace AnywhereTS.DBSupport
115                  this.ConnectionIsOpen = true;                  this.ConnectionIsOpen = true;
116              }              }
117              //catch (SqlException ex) { Console.WriteLine(ex.ToString()); ErrorInfo = ex;  throw ErrorInfo;}              //catch (SqlException ex) { Console.WriteLine(ex.ToString()); ErrorInfo = ex;  throw ErrorInfo;}
118              catch (Exception ex) { Console.WriteLine(ex.ToString()); ErrorInfo = ex; throw ErrorInfo; }              catch (SqlException ex)
119                {
120                    using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", ex.Number.ToString(), ex.Message, System.Environment.NewLine, ex.ToString())))
121                    {
122                        Logging.DatabaseLog.Error(string.Format("Failed to open connection to {0} Database", DBDatabase));
123                    }
124                    ErrorInfo = ex; throw ErrorInfo;
125                }
126                catch (Exception ex)
127                {
128                    using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
129                    {
130                        Logging.DatabaseLog.Error(string.Format("Failed to open connection to {0} Database", DBDatabase));
131                    }
132                    ErrorInfo = ex; throw ErrorInfo;
133                }
134          }          }
135          public virtual void CloseConnection(out Exception ErrorInfo)          public virtual void CloseConnection(out Exception ErrorInfo)
136          {          {
# Line 75  namespace AnywhereTS.DBSupport Line 140  namespace AnywhereTS.DBSupport
140                  if (this.ConnectionIsOpen)                  if (this.ConnectionIsOpen)
141                      connection.Close();                      connection.Close();
142              }              }
143              //catch (SqlException ex) { Console.WriteLine(ex.ToString()); ErrorInfo = ex;  throw ErrorInfo;}              catch (SqlException ex)
144              catch (Exception ex) { Console.WriteLine(ex.ToString()); ErrorInfo = ex; throw ErrorInfo; }              {
145                    using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", ex.Number.ToString(), ex.Message, System.Environment.NewLine, ex.ToString())))
146                    {
147                        Logging.DatabaseLog.Error(string.Format("Failed to close connection to {0} Database", DBDatabase));
148                    }
149                    ErrorInfo = ex; throw ErrorInfo;
150                }
151                catch (Exception ex)
152                {
153                    using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
154                    {
155                        Logging.DatabaseLog.Error(string.Format("Failed to close connection to {0} Database", DBDatabase));
156                    }
157                    ErrorInfo = ex; throw ErrorInfo;
158                }
159          }          }
160    
161          public virtual DbDataReader ExecuteQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)          public virtual DbDataReader ExecuteQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
# Line 93  namespace AnywhereTS.DBSupport Line 172  namespace AnywhereTS.DBSupport
172                  DbDataReader r = sqlComm.ExecuteReader();                  DbDataReader r = sqlComm.ExecuteReader();
173                  return r;                  return r;
174              }              }
175              catch (Exception ex) { ErrorInfo = new Exception("Cannot execute query.", ex); }              catch (SqlException ex)
176              return null;              {
177                    using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", ex.Number.ToString(), ex.Message, System.Environment.NewLine, ex.ToString())))
178                    {
179                        Logging.DatabaseLog.Error(string.Format("Failed to execute query: {0}", command));
180                    }
181                    ErrorInfo = ex; throw ErrorInfo;
182                }
183                catch (Exception ex)
184                {
185                    using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
186                    {
187                        Logging.DatabaseLog.Error(string.Format("Failed to execute querey: {0}", command));
188                    }
189                    ErrorInfo = ex; throw ErrorInfo;
190                }
191          }          }
192          public virtual void ExecuteNonQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)          public virtual void ExecuteNonQuery(string command, List<DBParameter> Params, out Exception ErrorInfo)
193          {          {
# Line 109  namespace AnywhereTS.DBSupport Line 202  namespace AnywhereTS.DBSupport
202                  foreach (DBParameter p in Params) { sqlComm.Parameters.Add(p); }                  foreach (DBParameter p in Params) { sqlComm.Parameters.Add(p); }
203                  sqlComm.ExecuteNonQuery();                  sqlComm.ExecuteNonQuery();
204              }              }
205              catch (Exception ex) { ErrorInfo = new Exception("Cannot execute non-query.", ex); }              catch (SqlException ex)
206                {
207                    using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", ex.Number.ToString(), ex.Message, System.Environment.NewLine, ex.ToString())))
208                    {
209                        Logging.DatabaseLog.Error(string.Format("Failed to execute non querey: {0}", command));
210                    }
211                    ErrorInfo = ex; throw ErrorInfo;
212                }
213                catch (Exception ex)
214                {
215                    using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
216                    {
217                        Logging.DatabaseLog.Error(string.Format("Failed to execute non querey: {0}", command));
218                    }
219                    ErrorInfo = ex; throw ErrorInfo;
220                }    
221          }          }
222          public virtual List<string> ExecuteColumnNamesReader(string command, List<DBParameter> Params, out Exception ErrorInfo)          public virtual List<string> ExecuteColumnNamesReader(string command, List<DBParameter> Params, out Exception ErrorInfo)
223          {          {
# Line 135  namespace AnywhereTS.DBSupport Line 243  namespace AnywhereTS.DBSupport
243                  }                  }
244                  return ColumnNames;                  return ColumnNames;
245              }              }
246              catch (Exception ex) { ErrorInfo = new Exception("Cannot get column names from reader.", ex); }              catch (SqlException ex)
247              return new List<string>();              {
248                    using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", ex.Number.ToString(), ex.Message, System.Environment.NewLine, ex.ToString())))
249                    {
250                        Logging.DatabaseLog.Error(string.Format("Failed to get colum names from reader: {0}", command));
251                    }
252                    ErrorInfo = ex; throw ErrorInfo;
253                }
254                catch (Exception ex)
255                {
256                    using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
257                    {
258                        Logging.DatabaseLog.Error(string.Format("Failed to get colum names from reader: {0}", command));
259                    }
260                    ErrorInfo = ex; throw ErrorInfo;
261                }            
262            }
263            public string[] ParseScriptToCommands(string strScript)
264            {
265                string[] commands;
266                commands = Regex.Split(strScript, "GO\r\n", RegexOptions.IgnoreCase);
267                return commands;
268            }
269            public virtual bool RunScript(string strFile, out Exception ErrorInfo)
270            {
271                ErrorInfo = null;
272                try
273                {
274                    string[] strCommands;
275                    strCommands = ParseScriptToCommands(strFile);
276                    if (this.ConnectionIsOpen)
277                    {
278                        foreach (string strCmd in strCommands)
279                        {
280                            if (strCmd.Length > 0)
281                            {
282                                // Substitute database directory with the decided one.
283                                DBCommand command = this.CreateCommandInstance(strCmd, new List<DBParameter>(), out ErrorInfo);
284                                command.ExecuteNonQuery();
285                            }
286                        }
287                        return true;
288                    }
289                    else
290                    {
291                        Logging.ATSAdminLog.Fatal(string.Format("Failed to run script: [database connection is not open] {0}{1}", System.Environment.NewLine, strFile));
292                        return false;
293                    }
294                }
295                catch (SqlException ex)
296                {
297                    using (log4net.NDC.Push(string.Format("SqlException: ID={0} MESSAGE={1}{2}Diagnostics:{2}{3}", ex.Number.ToString(), ex.Message, System.Environment.NewLine, ex.ToString())))
298                    {
299                        Logging.DatabaseLog.Error(string.Format("Failed to run script: {0}{1}", System.Environment.NewLine, strFile));
300                    }
301                    ErrorInfo = ex;
302                }
303                catch (Exception ex)
304                {
305                    using (log4net.NDC.Push(string.Format("{0}: MESSAGE={1}{2}Diagnostics:{2}{3}", ex.GetType().Name, ex.Message, System.Environment.NewLine, ex.ToString())))
306                    {
307                        Logging.DatabaseLog.Error(string.Format("Failed to run script: {0}{1}", System.Environment.NewLine, strFile));
308                    }
309                    ErrorInfo = ex;
310                }
311                return false;
312          }          }
   
313          #endregion          #endregion
314    
315          #region IDisposable Members          #region IDisposable Members

Legend:
Removed from v.86  
changed lines
  Added in v.87

  ViewVC Help
Powered by ViewVC 1.1.22