public void AddCommandParameter(EPField p, EnumParameterStyle style) { DbParameter pam = cmd.CreateParameter(); pam.ParameterName = p.GetParameterNameForCommand(style); pam.DbType = ValueConvertor.OleDbTypeToDbType(p.OleDbType); pam.Size = p.DataSize; pam.Value = p.Value; cmd.Parameters.Add(pam); }
public void AddDateParam(string name, System.DateTime dt) { DbParameter pam = cmd.CreateParameter(); pam.ParameterName = name; pam.Size = 8; pam.DbType = ValueConvertor.OleDbTypeToDbType(System.Data.OleDb.OleDbType.DBTimeStamp); pam.Value = dt; cmd.Parameters.Add(pam); }
public void MakeCommand(string table, FieldList rowID, Connection cn) { if (fields.Count > 0) { int i; rowIDFields = rowID; //================================ cmdUpdates = new DbCommand[fields.Count]; for (int k = 0; k < fields.Count; k++) { cmdUpdates[k] = cn.CreateCommand(); StringBuilder sSQL = new StringBuilder(); sSQL.Append(QueryParser.SQL_Update()); sSQL.Append(DatabaseEditUtil.SepBegin(cn.NameDelimiterStyle)); sSQL.Append(table); sSQL.Append(DatabaseEditUtil.SepEnd(cn.NameDelimiterStyle)); sSQL.Append(QueryParser.SQL_Set()); sSQL.Append(DatabaseEditUtil.SepBegin(cn.NameDelimiterStyle)); sSQL.Append(fields[k].Name); sSQL.Append(DatabaseEditUtil.SepEnd(cn.NameDelimiterStyle)); sSQL.Append("=?"); sSQL.Append(QueryParser.SQL_Where()); sSQL.Append(DatabaseEditUtil.SepBegin(cn.NameDelimiterStyle)); sSQL.Append(rowIDFields[0].Name); sSQL.Append(DatabaseEditUtil.SepEnd(cn.NameDelimiterStyle)); sSQL.Append("=? "); for (i = 1; i < rowIDFields.Count; i++) { sSQL.Append(QueryParser.SQL_And()); sSQL.Append(DatabaseEditUtil.SepBegin(cn.NameDelimiterStyle)); sSQL.Append(rowIDFields[i].Name); sSQL.Append(DatabaseEditUtil.SepEnd(cn.NameDelimiterStyle)); sSQL.Append("=?"); } cmdUpdates[k].CommandText = sSQL.ToString(); DbParameter pam = cmdUpdates[k].CreateParameter(); pam.ParameterName = fields[k].Name; pam.DbType = ValueConvertor.OleDbTypeToDbType(fields[k].OleDbType); pam.Size = fields[k].DataSize; pam.SourceColumn = fields[k].Name; cmdUpdates[k].Parameters.Add(pam); for (i = 0; i < rowIDFields.Count; i++) { pam = cmdUpdates[k].CreateParameter(); pam.ParameterName = rowIDFields[i].Name; pam.DbType = ValueConvertor.OleDbTypeToDbType(rowIDFields[i].OleDbType); pam.Size = rowIDFields[i].DataSize; pam.SourceColumn = rowIDFields[i].Name; cmdUpdates[k].Parameters.Add(pam); } } } }
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 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); }