public void Log(string message, params object[] values) { if (LoggingEnabled) { if (!string.IsNullOrEmpty(this.LogFilePath)) { string msg; try { if (values != null && values.Length > 0) { msg = string.Format(message, values); } else { msg = message; } } catch (Exception e) { msg = ExceptionLimnorDatabase.FormExceptionText(e) + " " + message; } if (!string.IsNullOrEmpty(msg)) { StreamWriter sw = null; try { sw = new StreamWriter(LogFilePath, true, Encoding.ASCII); sw.Write(DateTime.Now.ToString("u")); sw.Write(" - "); sw.WriteLine(msg); } catch (Exception er) { FormLog.ShowMessage(ExceptionLimnorDatabase.FormExceptionText(er)); } finally { if (sw != null) { sw.Close(); } } } } } }
public virtual string Execute() { string sMsg = string.Empty; SetError(sMsg); _affectedRows = 0; if (_sql != null && connect != null) { DbCommand cmd = connect.ConnectionObject.CreateCommand(); if (_transaction != null) { cmd.Transaction = _transaction; } bool bClosed = !connect.ConnectionObject.Opened; if (bClosed) { connect.ConnectionObject.Open(); } if (connect.ConnectionObject.Opened) { try { int i; EnumParameterStyle pstyle = connect.ParameterStyle; FieldList pmMap = new FieldList(); string s; int nCount; s = _sql.GetSQLStatement(pmMap, pstyle); if (pstyle == EnumParameterStyle.QuestionMark) { nCount = pmMap.Count; } else { nCount = _sql.ParamCount; } connect.Log("Command:{0}, params:{1}", s, nCount); cmd.CommandText = s; cmd.CommandType = CommandType.Text; for (i = 0; i < nCount; i++) { DbParameter pam = cmd.CreateParameter(); EPField f; if (pstyle == EnumParameterStyle.QuestionMark) { f = pmMap[i]; pam.ParameterName = "@P" + i.ToString(); } else { f = _sql.Parameters[i]; pam.ParameterName = ParameterList.GetParameterName(pstyle, _sql.Parameters[i].Name); } pam.DbType = ValueConvertor.OleDbTypeToDbType(f.OleDbType); pam.Size = f.DataSize; OleDbParameter op = pam as OleDbParameter; if (op != null && f.OleDbType == OleDbType.DBTimeStamp) { if (f.Value != null && f.Value != DBNull.Value) { DateTime dt = (DateTime)(f.Value); dt = new DateTime(dt.Year, dt.Month, dt.Day, dt.Hour, dt.Minute, dt.Second); f.SetValue(dt); } } connect.Log("param{0}:{1},{2},{3}. Value type:{4}. Value:{5}", i, pam.ParameterName, pam.DbType, pam.Size, f.OleDbType, f.Value); if (EPField.IsDatetime(f.OleDbType)) { if (f.IsNullOrEmpty) { pam.Value = System.DBNull.Value; } else { object dt0 = ValueConvertor.ConvertByOleDbType(f.Value, f.OleDbType); try { DateTime dt = (DateTime)dt0; if (dt.Ticks == 0) { pam.Value = System.DBNull.Value; } else { pam.Value = dt; } } catch { pam.Value = dt0; } } } else { pam.Value = ValueConvertor.ConvertByOleDbType(f.Value, f.OleDbType); } cmd.Parameters.Add(pam); } _affectedRows = cmd.ExecuteNonQuery(); connect.Log("Affected rows:{0}", _affectedRows); if (bClosed) { closeConnections(); } if (this.ExecuteFinish != null) { ExecuteFinish(this, EventArgs.Empty); } if (_webPage != null && !string.IsNullOrEmpty(_name)) { _webPage.SetServerComponentName(_name); } } catch (Exception er) { if (_transaction != null) { _transaction.Rollback(); _transaction.Dispose(); _transaction = null; connect.Log("rollback with error {0}", er.Message); throw; } else { sMsg = ExceptionLimnorDatabase.FormExceptionText(er); } } finally { if (bClosed) { if (connect.ConnectionObject.State != ConnectionState.Closed) { connect.ConnectionObject.Close(); } } } } else { sMsg = "Database connection not set"; } } else { sMsg = "SQL statement not set"; } if (!string.IsNullOrEmpty(sMsg)) { SetError(sMsg); if (connect != null) { connect.Log("Error executing EasyUpdator.Execute. {0}", sMsg); } } return(sMsg); }
public virtual string ExecuteCommand(string command) { string sMsg = string.Empty; SetError(sMsg); _affectedRows = 0; connect.Log("Executing command:{0}", command); if (!string.IsNullOrEmpty(command)) { DbCommand cmd = connect.ConnectionObject.CreateCommand(); if (_transaction != null) { cmd.Transaction = _transaction; } bool bClosed = !connect.ConnectionObject.Opened; if (bClosed) { connect.ConnectionObject.Open(); } if (connect.ConnectionObject.Opened) { try { cmd.CommandText = command; cmd.CommandType = CommandType.Text; _affectedRows = cmd.ExecuteNonQuery(); connect.Log("Affected rows:{0}", _affectedRows); if (bClosed) { closeConnections(); } if (this.ExecuteFinish != null) { ExecuteFinish(this, EventArgs.Empty); } if (_webPage != null && !string.IsNullOrEmpty(_name)) { _webPage.SetServerComponentName(_name); } } catch (Exception er) { if (_transaction != null) { _transaction.Rollback(); _transaction.Dispose(); _transaction = null; connect.Log("rollback with error {0}", er.Message); throw; } else { sMsg = ExceptionLimnorDatabase.FormExceptionText(er); } } finally { if (bClosed) { if (connect.ConnectionObject.State != ConnectionState.Closed) { connect.ConnectionObject.Close(); } } } } } return(sMsg); }
public bool ExportToCsvFile(string filename, DataTable tblSrc, bool append, EnumCharEncode encode) { _error = null; System.IO.StreamWriter sw = null; if (tblSrc == null) { _error = "Source data is null"; } else if (tblSrc.Columns.Count == 0) { _error = "Source data is empty"; } else { try { bool bWriteHeader = HasHeader; if (bWriteHeader && append) { if (System.IO.File.Exists(filename)) { System.IO.FileInfo fi = new System.IO.FileInfo(filename); if (fi.Length > 0) { bWriteHeader = false; } } } System.Text.StringBuilder sb; // sw = new System.IO.StreamWriter(filename, append, EncodeUtility.GetEncoding(encode), 2048); if (bWriteHeader && !append) { sb = new System.Text.StringBuilder(); sb.Append(DataTransferConvert.Encode(_delimiter, tblSrc.Columns[0].ColumnName)); for (int i = 1; i < tblSrc.Columns.Count; i++) { sb.Append(DataTransferConvert.Delimiter(_delimiter)); sb.Append(DataTransferConvert.Encode(_delimiter, tblSrc.Columns[i].ColumnName)); } sw.WriteLine(sb.ToString()); } for (int r = 0; r < tblSrc.Rows.Count; r++) { sb = new System.Text.StringBuilder(); object v = tblSrc.Rows[r][0]; if (v == DBNull.Value || v == null) { v = VPLUtil.GetDefaultValue(tblSrc.Columns[0].DataType); } sb.Append(DataTransferConvert.Encode(_delimiter, StringUtility.ToString(v))); for (int i = 1; i < tblSrc.Columns.Count; i++) { sb.Append(DataTransferConvert.Delimiter(_delimiter)); v = tblSrc.Rows[r][i]; if (v == DBNull.Value || v == null) { v = VPLUtil.GetDefaultValue(tblSrc.Columns[i].DataType); } sb.Append(DataTransferConvert.Encode(_delimiter, StringUtility.ToString(v))); } sw.WriteLine(sb.ToString()); } } catch (Exception er) { _error = ExceptionLimnorDatabase.FormExceptionText(er, "Error saving data to file {0}", filename); } finally { if (sw != null) { sw.Close(); } } } return(string.IsNullOrEmpty(_error)); }
public override string Execute() { string sMsg = string.Empty; SetError(sMsg); ResetAffectedRows(); SQLNoneQuery sql = base.ExecutionCommand; ConnectionItem connect = Connection; DbTransaction transaction = Transaction; if (sql != null && connect != null) { DbCommand cmd = connect.ConnectionObject.CreateCommand(); if (transaction != null) { cmd.Transaction = transaction; } bool bClosed = !connect.ConnectionObject.Opened; if (bClosed) { connect.ConnectionObject.Open(); } if (connect.ConnectionObject.Opened) { try { int i; EnumParameterStyle pstyle = connect.ParameterStyle; cmd.CommandText = sql.SQL; if (sql.CommandType == enmNonQueryType.StoredProcedure) { cmd.CommandType = CommandType.StoredProcedure; } else { cmd.CommandType = CommandType.Text; } int nCount = ParameterCount; for (i = 0; i < nCount; i++) { DbParameter pam = cmd.CreateParameter(); if (pstyle == EnumParameterStyle.LeadingQuestionMark) { if (sql.Param_Name[i].StartsWith("@", StringComparison.OrdinalIgnoreCase)) { pam.ParameterName = string.Format(CultureInfo.InvariantCulture, "?{0}", sql.Param_Name[i].Substring(1)); } else if (sql.Param_Name[i].StartsWith("?", StringComparison.OrdinalIgnoreCase)) { pam.ParameterName = sql.Param_Name[i]; } else { pam.ParameterName = string.Format(CultureInfo.InvariantCulture, "?{0}", sql.Param_Name[i]); } } else { if (sql.Param_Name[i].StartsWith("@", StringComparison.OrdinalIgnoreCase)) { pam.ParameterName = sql.Param_Name[i]; } else { pam.ParameterName = string.Format(CultureInfo.InvariantCulture, "@{0}", sql.Param_Name[i]); } } pam.DbType = ValueConvertor.OleDbTypeToDbType(sql.Param_OleDbType[i]); pam.Direction = this.Param_Directions[i]; pam.Size = EPField.FieldDataSize(sql.Param_OleDbType[i], this.Param_DataSize[i]); pam.Value = ValueConvertor.ConvertByOleDbType(sql.Parameters[i].Value, sql.Param_OleDbType[i]); cmd.Parameters.Add(pam); } cmd.Prepare(); DbDataReader dr = cmd.ExecuteReader(); _dataset = new DataSet("Results"); int n = 1; while (true) { DataTable tbl = new DataTable(string.Format("Table{0}", n)); for (i = 0; i < dr.FieldCount; i++) { DataColumn dc = new DataColumn(dr.GetName(i), dr.GetFieldType(i)); tbl.Columns.Add(dc); } _dataset.Tables.Add(tbl); while (dr.Read()) { object[] vs = new object[dr.FieldCount]; for (int k = 0; k < dr.FieldCount; k++) { vs[k] = dr.GetValue(k); } tbl.Rows.Add(vs); } n++; if (!dr.NextResult()) { break; } } dr.Close(); if (bClosed) { closeConnections(); } for (i = 0; i < nCount; i++) { ParameterDirection pt = cmd.Parameters[i].Direction; if (pt != ParameterDirection.Input) { sql.Parameters[i].Value = cmd.Parameters[i].Value; } } FireExecuteFinish(); } catch (Exception er) { if (transaction != null) { transaction.Rollback(); transaction.Dispose(); ResetTransaction(); throw; } else { sMsg = ExceptionLimnorDatabase.FormExceptionText(er); } } finally { if (bClosed) { if (connect.ConnectionObject.State != ConnectionState.Closed) { connect.ConnectionObject.Close(); } } } } else { sMsg = "Database connection not set"; } } else { sMsg = "SQL statement not set"; } if (!string.IsNullOrEmpty(sMsg)) { SetError(sMsg); } return(sMsg); }
public bool ExecuteActionsInTransaction(IsolationLevel isolationLevel) { if (ExecuteInOneTransaction != null && _commands != null && _commands.Length > 0) { string sMsg = string.Empty; SetError(sMsg); Connection connect = _commands[0].DatabaseConnection.ConnectionObject; if (connect != null) { if (connect.IsConnectionReady) { lock (_transactionObject) { try { connect.Open(); _transaction = connect.BeginTransaction(isolationLevel); for (int i = 0; i < _commands.Length; i++) { _commands[i].SetTransaction(_transaction, _commands[0].DatabaseConnection); } ExecuteInOneTransaction(this, new EventArgsDbTransaction(isolationLevel)); if (_transaction != null) { _transaction.Commit(); } } catch (Exception exp) { sMsg = ExceptionLimnorDatabase.FormExceptionText(exp); if (_transaction != null) { if (_transaction.Connection != null && _transaction.Connection.State == ConnectionState.Open) { try { _transaction.Rollback(); } catch { } } } } finally { connect.Close(); if (_transaction != null) { _transaction.Dispose(); _transaction = null; } } } } } else { sMsg = "Database connection not ready"; } if (!string.IsNullOrEmpty(sMsg)) { SetError(sMsg); return(false); } return(true); } else { StringBuilder sb = new StringBuilder(); if (ExecuteInOneTransaction == null) { sb.Append("There are not actions assigned to ExecuteInOneTransaction"); } if (_commands == null || _commands.Length == 0) { sb.Append("There are not execution commands"); } SetError(sb.ToString()); return(false); } }