/// <summary>
///
/// select a row from table t_RBSR_AUFW_u_SAPauthField.
/// </summary>
/// <param name="ID"></param>
/// <returns>returnGetSAPauthField</returns>
public returnGetSAPauthField GetSAPauthField(int ID)
{
returnGetSAPauthField rv = new returnGetSAPauthField();
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "select \"c_id\",\"c_u_Name\",\"c_u_Description\",\"c_u_Status\" from \"t_RBSR_AUFW_u_SAPauthField\" where \"c_id\"= ?";
cmd.Parameters.Add("c_id", OdbcType.Int);
cmd.Parameters["c_id"].Value = (object)ID;
cmd.Connection = _dbConnection;
OdbcDataReader dr = cmd.ExecuteReader();
int drctr = 0;
while (dr.Read())
{
drctr++;
if (dr.IsDBNull(0))
{
throw new Exception("Value 'null' is not allowed for 'ID'");
}
else
{
rv.ID = dr.GetInt32(0);
}
if (dr.IsDBNull(1))
{
throw new Exception("Value 'null' is not allowed for 'Name'");
}
else
{
rv.Name = dr.GetString(1);
}
if (dr.IsDBNull(2))
{
rv.Description = null;
}
else
{
rv.Description = dr.GetString(2);
}
if (dr.IsDBNull(3))
{
throw new Exception("Value 'null' is not allowed for 'Status'");
}
else
{
rv.Status = dr.GetString(3);
}
}
dr.Close();
dr.Dispose();
if (drctr != 1)
{
throw new Exception("Operation selected no rows!");
}
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
///
/// select a row from table t_RBSR_AUFW_u_FuncApplNotes.
/// </summary>
/// <param name="ID"></param>
/// <returns>returnGetFuncApplNotes</returns>
public returnGetFuncApplNotes GetFuncApplNotes(int ID)
{
returnGetFuncApplNotes rv = new returnGetFuncApplNotes();
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "select \"c_id\",\"c_u_REFapplication\",\"c_u_Comment\",\"c_r_BusRole\" from \"t_RBSR_AUFW_u_FuncApplNotes\" where \"c_id\"= ?";
cmd.Parameters.Add("c_id", OdbcType.Int);
cmd.Parameters["c_id"].Value = (object)ID;
cmd.Connection = _dbConnection;
OdbcDataReader dr = cmd.ExecuteReader();
int drctr = 0;
while (dr.Read())
{
drctr++;
if (dr.IsDBNull(0))
{
throw new Exception("Value 'null' is not allowed for 'ID'");
}
else
{
rv.ID = dr.GetInt32(0);
}
if (dr.IsDBNull(1))
{
throw new Exception("Value 'null' is not allowed for 'REFapplication'");
}
else
{
rv.REFapplication = dr.GetInt32(1);
}
if (dr.IsDBNull(2))
{
throw new Exception("Value 'null' is not allowed for 'Comment'");
}
else
{
rv.Comment = dr.GetString(2);
}
if (dr.IsDBNull(3))
{
throw new Exception("Value 'null' is not allowed for 'BusRoleID'");
}
else
{
rv.BusRoleID = dr.GetInt32(3);
}
}
dr.Close();
dr.Dispose();
if (drctr != 1)
{
throw new Exception("Operation selected no rows!");
}
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
///
/// select a set of rows from table t_RBSR_AUFW_u_SAPsecurityOrg.
/// </summary>
/// <param name="maxRowsToReturn">Max number of rows to return. If null or 0 all rows are returned.</param>
/// <returns>returnListSAPsecurityOrg[]</returns>
public returnListSAPsecurityOrg[] ListSAPsecurityOrg(int?maxRowsToReturn)
{
returnListSAPsecurityOrg[] rv = null;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
if (maxRowsToReturn.HasValue && maxRowsToReturn.Value > 0)
{
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText = "SELECT TOP " + maxRowsToReturn.Value + " \"c_id\", \"c_u_Name\", \"c_r_SAPsecurityOrgAxis\" FROM \"t_RBSR_AUFW_u_SAPsecurityOrg\"";
}
else
{
cmd.CommandText = "SELECT \"c_id\", \"c_u_Name\", \"c_r_SAPsecurityOrgAxis\" FROM \"t_RBSR_AUFW_u_SAPsecurityOrg\"" + " LIMIT " + maxRowsToReturn.Value;
}
}
else
{
cmd.CommandText = "SELECT \"c_id\", \"c_u_Name\", \"c_r_SAPsecurityOrgAxis\" FROM \"t_RBSR_AUFW_u_SAPsecurityOrg\"";
}
OdbcDataReader dr = cmd.ExecuteReader();
List <returnListSAPsecurityOrg> rvl = new List <returnListSAPsecurityOrg>();
while (dr.Read())
{
returnListSAPsecurityOrg cr = new returnListSAPsecurityOrg();
if (dr.IsDBNull(0))
{
throw new Exception("Value 'null' is not allowed for 'ID'");
}
else
{
cr.ID = dr.GetInt32(0);
}
if (dr.IsDBNull(1))
{
throw new Exception("Value 'null' is not allowed for 'Name'");
}
else
{
cr.Name = dr.GetString(1);
}
if (dr.IsDBNull(2))
{
throw new Exception("Value 'null' is not allowed for 'SAPsecurityOrgAxisID'");
}
else
{
cr.SAPsecurityOrgAxisID = dr.GetInt32(2);
}
rvl.Add(cr);
}
dr.Close();
dr.Dispose();
rv = rvl.ToArray();
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
///
/// select a set of rows from table t_RBSR_AUFW_r_RoleTcodeAssignment.
/// </summary>
/// <param name="maxRowsToReturn">Max number of rows to return. If null or 0 all rows are returned.</param>
/// <returns>returnListRoleTcodeAssignment[]</returns>
public returnListRoleTcodeAssignment[] ListRoleTcodeAssignment(int?maxRowsToReturn)
{
returnListRoleTcodeAssignment[] rv = null;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
if (maxRowsToReturn.HasValue && maxRowsToReturn.Value > 0)
{
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText = "SELECT TOP " + maxRowsToReturn.Value + " \"c_id\", \"c_r_Role\", \"c_r_TcodeAssignment\" FROM \"t_RBSR_AUFW_r_RoleTcodeAssignment\"";
}
else
{
cmd.CommandText = "SELECT \"c_id\", \"c_r_Role\", \"c_r_TcodeAssignment\" FROM \"t_RBSR_AUFW_r_RoleTcodeAssignment\"" + " LIMIT " + maxRowsToReturn.Value;
}
}
else
{
cmd.CommandText = "SELECT \"c_id\", \"c_r_Role\", \"c_r_TcodeAssignment\" FROM \"t_RBSR_AUFW_r_RoleTcodeAssignment\"";
}
OdbcDataReader dr = cmd.ExecuteReader();
List <returnListRoleTcodeAssignment> rvl = new List <returnListRoleTcodeAssignment>();
while (dr.Read())
{
returnListRoleTcodeAssignment cr = new returnListRoleTcodeAssignment();
if (dr.IsDBNull(0))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'ID'");
}
else
{
cr.ID = dr.GetInt32(0);
}
if (dr.IsDBNull(1))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'RoleID'");
}
else
{
cr.RoleID = dr.GetInt32(1);
}
if (dr.IsDBNull(2))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'TcodeAssignmentID'");
}
else
{
cr.TcodeAssignmentID = dr.GetInt32(2);
}
rvl.Add(cr);
}
dr.Close();
dr.Dispose();
rv = rvl.ToArray();
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
///
/// select a row from table t_RBSR_AUFW_u_SAPsecurityOrgAxis.
/// </summary>
/// <param name="ID"></param>
/// <returns>returnGetSAPsecurityOrgAxis</returns>
public returnGetSAPsecurityOrgAxis GetSAPsecurityOrgAxis(int ID)
{
returnGetSAPsecurityOrgAxis rv = new returnGetSAPsecurityOrgAxis();
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "select \"c_id\",\"c_u_English_Name\",\"c_u_SAP_Name\",\"c_u_LegalValues\" from \"t_RBSR_AUFW_u_SAPsecurityOrgAxis\" where \"c_id\"= ?";
cmd.Parameters.Add("c_id", OdbcType.Int);
cmd.Parameters["c_id"].Value = (object)ID;
cmd.Connection = _dbConnection;
OdbcDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
if (dr.IsDBNull(0))
{
throw new Exception("Value 'null' is not allowed for 'ID'");
}
else
{
rv.ID = dr.GetInt32(0);
}
if (dr.IsDBNull(1))
{
throw new Exception("Value 'null' is not allowed for 'English_Name'");
}
else
{
rv.English_Name = dr.GetString(1);
}
if (dr.IsDBNull(2))
{
throw new Exception("Value 'null' is not allowed for 'SAP_Name'");
}
else
{
rv.SAP_Name = dr.GetString(2);
}
if (dr.IsDBNull(3))
{
rv.LegalValues = null;
}
else
{
rv.LegalValues = dr.GetString(3);
}
}
dr.Close();
dr.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
///
/// select a row from table t_RBSR_AUFW_u_SubProcess.
/// </summary>
/// <param name="ID"></param>
/// <returns>returnGetSubProcess</returns>
public returnGetSubProcess GetSubProcess(int ID)
{
returnGetSubProcess rv = new returnGetSubProcess();
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "select \"c_id\",\"c_u_Name\",\"c_r_Process\",\"c_u_Status\" from \"t_RBSR_AUFW_u_SubProcess\" where \"c_id\"= ?";
cmd.Parameters.Add("c_id", OdbcType.Int);
cmd.Parameters["c_id"].Value = (object)ID;
cmd.Connection = _dbConnection;
OdbcDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
if (dr.IsDBNull(0))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'ID'");
}
else
{
rv.ID = dr.GetInt32(0);
}
if (dr.IsDBNull(1))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'Name'");
}
else
{
rv.Name = dr.GetString(1);
}
if (dr.IsDBNull(2))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'ProcessID'");
}
else
{
rv.ProcessID = dr.GetInt32(2);
}
if (dr.IsDBNull(3))
{
rv.Status = null;
}
else
{
rv.Status = dr.GetString(3);
}
}
dr.Close();
dr.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}
private void StatusPage_Load(object sender, EventArgs e)
{
// set up lead labels
jobNoLabel.Text = jobNo;
partNoLabel.Text = partNo;
customerLabel.Text = customer;
currentUserLabel.Text = Globals.userName;
// update layout shape
UpdateLayout();
// set up detail events double click
step1Details.DoubleClick += this.step1_DoubleClick;
step2Details.DoubleClick += this.step2_DoubleClick;
step3Details.DoubleClick += this.step3_DoubleClick;
step4Details.DoubleClick += this.step4_DoubleClick;
step5Details.DoubleClick += this.step5_DoubleClick;
step1Label.DoubleClick += this.step1_DoubleClick;
step2Label.DoubleClick += this.step2_DoubleClick;
step3Label.DoubleClick += this.step3_DoubleClick;
step4Label.DoubleClick += this.step4_DoubleClick;
step5Label.DoubleClick += this.step5_DoubleClick;
// add workflows to workflow
using (OdbcConnection conn = new OdbcConnection(Globals.odbc_connection_string))
{
conn.Open();
string query =
"SELECT Job, Workflow_ID\n" +
"FROM [ATI_Workflow].[dbo].[StatusData]\n" +
"WHERE Job = '" + jobNo + "';";
OdbcCommand com = new OdbcCommand(query, conn);
OdbcDataReader reader = com.ExecuteReader();
// check that there is more than one valid row
while (reader.Read() && !reader.IsDBNull(0))
{
if (!reader.IsDBNull(1))
{
workflowListBox.Items.Add(reader.GetInt32(1));
}
}
}
// if list is > 0 then select and activate first workflow and load info
if (workflowListBox.Items.Count > 0)
{
workflowListBox.SelectedIndex = 0;
}
}
public IList <PlayerInfo> getPlayers()
{
IList <PlayerInfo> players = new List <PlayerInfo>();
OdbcCommand cmd = new OdbcCommand("{call " + SP_SELECT_PLAYERS + "}");
cmd.CommandType = CommandType.StoredProcedure;
using (OdbcDataReader rdr = SqlHelper.ExecuteReader(cmd, SqlHelper.USABConnectionString))
{
while (rdr.Read())
{
PlayerInfo user = new PlayerInfo();
user.id = rdr.IsDBNull(0) ? 0 : rdr.GetInt32(0);
user.firstname = rdr.IsDBNull(1) ? "" : rdr.GetString(1);
user.lastname = rdr.IsDBNull(2) ? "" : rdr.GetString(2);
user.position = rdr.IsDBNull(3) ? "" : rdr.GetString(3);
user.height = rdr.IsDBNull(4) ? "" : rdr.GetString(4);
user.weight = rdr.IsDBNull(5) ? "" : rdr.GetString(5);
user.city = rdr.IsDBNull(6) ? "" : rdr.GetString(6);
user.state = rdr.IsDBNull(7) ? "" : rdr.GetString(7);
//user.birthdate = rdr.IsDBNull(8) ? "" : rdr.GetString(8);
//user.year = rdr.IsDBNull(9) ? "" : rdr.GetString(9);
//user.bio = rdr.IsDBNull(10) ? "" : rdr.GetString(10);
//user.jersey_nbr = rdr.IsDBNull(11) ? "" : rdr.GetString(11);
players.Add(user);
}
}
return(players);
}
private int SafeGetInt(OdbcDataReader reader, string column, int defaultValue)
{
try
{
if (reader.IsDBNull(reader.GetOrdinal(column)))
{
logger.Debug("Reader column {column} is DbNull. Returning default value of {defaultValue}.", column, defaultValue);
return(defaultValue);
}
else
{
return(reader.GetInt32(reader.GetOrdinal(column)));
}
}
catch (InvalidCastException ex)
{
string columnValue = reader.GetString(reader.GetOrdinal(column));
logger.Info(ex, "Could not cast column {column} value {value} as Int32. returning default value of {defaultValue}.", column, columnValue, defaultValue);
return(defaultValue);
}
catch (Exception ex)
{
logger.Error("An unexpected error occurred while processing reader column {column}. Error: {errorMessage}", column, ex.Message);
throw;
}
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_r_UserEditingWorkspace.
/// </summary>
/// <param name="UserID"></param>
/// <param name="EditingWorkspaceID"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewUserEditingWorkspace(int UserID, int EditingWorkspaceID)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = new OdbcCommand();
cmd.CommandText = "insert into \"t_RBSR_AUFW_r_UserEditingWorkspace\" (\"c_r_User\",\"c_r_EditingWorkspace\") values(?,?) " + SpecSQL("get id");
cmd.Parameters.Add("c_r_User", OdbcType.Int);
cmd.Parameters["c_r_User"].Value = (object)UserID;
cmd.Parameters.Add("c_r_EditingWorkspace", OdbcType.Int);
cmd.Parameters["c_r_EditingWorkspace"].Value = (object)EditingWorkspaceID;
cmd.Connection = _dbConnection;
OdbcDataReader dri = cmd.ExecuteReader();
dri.Read();
rv = (dri.IsDBNull(0) ? 0 : dri.GetInt32(0));
dri.Close();
dri.Dispose();
if (rv == 0)
{
throw new Exception("Insert operation failed!");
}
cmd.Dispose();
DBClose();
return(rv);
}
static void Main(string[] args)
{
// Connection
string connectionString = "DSN=mySqlServer;Uid=SYSTEM;Pwd=password";
OdbcConnection conn = new OdbcConnection();
conn = new OdbcConnection(connectionString);
conn.Open();
//reading from an RDBMS via ODBC. Note the use of isDBNull, use it for
string query = "SELECT ID, Name, Birthdate, Picture FROM Customer ORDER BY ID";
OdbcCommand exe = new OdbcCommand(query, conn);
OdbcDataReader read = exe.ExecuteReader();
byte[] contentDataBuffer = new byte[2097125]; // 2 MB -
while (read.Read())
{
string tmpUid = (read.GetInt64(0)).ToString();
string tmpName = read.IsDBNull(1) == false?read.GetString(1) : null;
DateTime tmpBirthDate = read.GetDateTime(2);
long lCntRead = read.GetBytes(3, 0, contentDataBuffer, 0, 2097125);
}
Form1 form = new Form1();
form.ShowMyImage(ByteToImage(contentDataBuffer)); // byteArr holds byte array value, display image in Form
}
private DateTime GetDateTimeValue(OdbcDataReader reader, int column)
{
try
{
if (reader.IsDBNull(column))
{
logger.Debug("Reader column {column} is DbNull. Returning default value.", column);
return(new DateTime());
}
else
{
return(reader.GetDateTime(column));
}
}
catch (InvalidCastException ex)
{
string columnValue = reader.GetString(column);
logger.Info(ex, "Could not cast column {column} value {value} as DateTime. returning default value.", column, columnValue);
return(new DateTime());
}
catch (Exception ex)
{
logger.Error("An unexpected error occurred while processing reader column {column}. Error: {errorMessage}", column, ex.Message);
throw;
}
}
/// <summary>
/// 查询数据记录
/// </summary>
/// <param name="sql">查询SQL</param>
/// <returns>返回第一行查询数据</returns>
public Dictionary <string, object> Find(string sql)
{
try
{
Dictionary <string, object> res = new Dictionary <string, object>();
OdbcConnection conn = (OdbcConnection)GetConnection();
OdbcCommand cmd = new OdbcCommand(sql, conn);
cmd.CommandTimeout = this.Timeout * 1000;
OdbcDataReader da = cmd.ExecuteReader();
if (da.Read())
{
if (da.FieldCount > 0)
{
for (int i = 0; i < da.FieldCount; i++)
{
if (da.IsDBNull(i))
{
res.Add(da.GetName(i), null);
}
else
{
res.Add(da.GetName(i), da[i]);
}
}
}
}
da.Close();
CloseConnection(conn);
return(res);
}
catch (Exception ex)
{
throw ex;
}
}
private void cargaCreditos()
{
OdbcConnection conexion = ASG_DB.connectionResult();
try
{
string sql = string.Format("select sum(total_factura) from caja_facturas where tipo_factura = 'CREDITO' and id_caja = {0};", codigoCaja);
OdbcCommand cmd = new OdbcCommand(sql, conexion);
OdbcDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
if (reader.IsDBNull(0))
{
label10.Text = string.Format("Q.{0:###,###,###,##0.00##}", 0);
} else
{
label10.Text = string.Format("Q.{0:###,###,###,##0.00##}", reader.GetDouble(0));
}
conexion.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
conexion.Close();
}
public Modulo obtenerModulo(int modulo)
{
Modulo moduloTmp = new Modulo();
try
{
String sComando = String.Format("SELECT PK_ID_MODULO, NOMBRE_MODULO, DESCRIPCION_MODULO, ESTADO_MODULO " +
"FROM TBL_MODULO " +
"WHERE ESTADO_MODULO <> 0 " +
" AND PK_ID_MODULO = {0}; ", modulo.ToString());
OdbcDataReader reader = transaccion.ConsultarDatos(sComando);
if (reader.HasRows)
{
while (reader.Read())
{
moduloTmp.MODULO = reader.GetInt32(0);
moduloTmp.NOMBRE = reader.GetString(1);
moduloTmp.DESCRIPCION = (reader.IsDBNull(2) ? " " : reader.GetString(2));
moduloTmp.ESTADO = reader.GetInt32(3);
}
}
}
catch (OdbcException ex)
{
MessageBox.Show(ex.ToString(), "Error al obtener lista de modulos.");
return(null);
}
return(moduloTmp);
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_User.
/// </summary>
/// <param name="EID"></param>
/// <param name="Name"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewUser(string EID, string Name)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = new OdbcCommand();
cmd.CommandText = "insert into \"t_RBSR_AUFW_u_User\" (\"c_u_EID\",\"c_u_Name\") values(?,?) " + SpecSQL("get id");
if (EID == null)
{
cmd.Dispose(); DBClose(); throw new Exception("EID must not be null!");
}
cmd.Parameters.Add("c_u_EID", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_EID"].Value = (EID != null ? (object)EID : DBNull.Value);
if (Name == null)
{
cmd.Dispose(); DBClose(); throw new Exception("Name must not be null!");
}
cmd.Parameters.Add("c_u_Name", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_Name"].Value = (Name != null ? (object)Name : DBNull.Value);
cmd.Connection = _dbConnection;
OdbcDataReader dri = null; try{ dri = cmd.ExecuteReader(); }catch (Exception edri) { cmd.Dispose(); DBClose(); throw edri; }
try{ dri.Read(); } catch (Exception edri) { cmd.Dispose(); DBClose(); throw edri; }
rv = (dri.IsDBNull(0) ? 0 : dri.GetInt32(0));
dri.Close();
dri.Dispose();
if (rv == 0)
{
cmd.Dispose(); DBClose(); throw new Exception("Insert operation failed!");
}
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_r_UserTcodeAssignmentSet.
/// </summary>
/// <param name="UserID"></param>
/// <param name="TcodeAssignmentSetID"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewUserTcodeAssignmentSet(int UserID, int TcodeAssignmentSetID)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = new OdbcCommand();
cmd.CommandText = "insert into \"t_RBSR_AUFW_r_UserTcodeAssignmentSet\" (\"c_r_User\",\"c_r_TcodeAssignmentSet\") values(?,?) " + SpecSQL("get id");
cmd.Parameters.Add("c_r_User", OdbcType.Int);
cmd.Parameters["c_r_User"].Value = (object)UserID;
cmd.Parameters.Add("c_r_TcodeAssignmentSet", OdbcType.Int);
cmd.Parameters["c_r_TcodeAssignmentSet"].Value = (object)TcodeAssignmentSetID;
cmd.Connection = _dbConnection;
OdbcDataReader dri = null; try{ dri = cmd.ExecuteReader(); }catch (Exception edri) { cmd.Dispose(); DBClose(); throw edri; }
try{ dri.Read(); } catch (Exception edri) { cmd.Dispose(); DBClose(); throw edri; }
rv = (dri.IsDBNull(0) ? 0 : dri.GetInt32(0));
dri.Close();
dri.Dispose();
if (rv == 0)
{
cmd.Dispose(); DBClose(); throw new Exception("Insert operation failed!");
}
cmd.Dispose();
DBClose();
return(rv);
}
private void ganancias()
{
OdbcConnection conexion = ASG_DB.connectionResult();
try
{
string sql = string.Format("SELECT * FROM GANANCIAS;");
OdbcCommand cmd = new OdbcCommand(sql, conexion);
OdbcDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
if (reader.IsDBNull(0))
{
label8.Text = "0";
}
else
{
double totalParcial = reader.GetDouble(0);
double totalVenta = efectivo + credito;
ganancia = totalVenta - totalParcial;
label8.Text = string.Format("Q.{0:###,###,###,##0.00##}", ganancia);
}
}
else
{
label8.Text = "0";
}
}
catch (Exception ex)
{
MessageBox.Show("FALLO LA CONEXION CON LA BASE DE DATOS!" + "\n" + ex.ToString(), "ESTADISTICAS", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
conexion.Close();
}
private string getReaderString(OdbcDataReader reader, int idx)
{
Type type;
string sType;
string str;
if (reader.IsDBNull(idx) == true)
{
str = "";
}
else
{
type = reader.GetFieldType(idx);
sType = type.Name;
if (sType == "String")
{
str = reader.GetString(idx);
}
else if (sType == "Int32")
{
str = reader.GetInt32(idx).ToString();
}
else
{
str = "";
}
}
return(str);
}
public virtual T CreateModel(string sql, string company)
{
var results = new List <T>();
using (clsOdbcDataGetter dg = new clsOdbcDataGetter(company))
{
OdbcDataReader reader = dg.GetDataReader(sql);
var NotMapped = new List <String>();
var props = typeof(T).GetProperties();
while (reader.Read())
{
var item = Activator.CreateInstance <T>();
foreach (var property in typeof(T).GetProperties())
{
if (!NotMapped.Contains(property.Name))
{
if (!reader.IsDBNull(reader.GetOrdinal(property.Name)))
{
Type convertTo = Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType;
property.SetValue(item, Convert.ChangeType(reader[property.Name], convertTo), null);
}
}
}
results.Add(item);
}
dg.KillReader(reader);
}
return(results[0]);
}
private void totalCredito()
{
OdbcConnection conexion = ASG_DB.connectionResult();
try
{
string sql = string.Format("SELECT SUM(TOTAL_FACTURA) FROM FACTURA WHERE ESTADO_TUPLA = TRUE AND ESTADO_FACTURA = 1 AND TIPO_FACTURA = 'CREDITO' AND FECHA_EMISION_FACTURA >= curdate();");
OdbcCommand cmd = new OdbcCommand(sql, conexion);
OdbcDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
if (reader.IsDBNull(0))
{
label6.Text = "0";
}
else
{
credito = reader.GetDouble(0);
label6.Text = string.Format("Q.{0:###,###,###,##0.00##}", credito);
}
}
else
{
label6.Text = "0";
}
}
catch (Exception ex)
{
MessageBox.Show("FALLO LA CONEXION CON LA BASE DE DATOS!" + "\n" + ex.ToString(), "ESTADISTICAS", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
conexion.Close();
}
/// <summary>
///
/// select a set of rows from table t_RBSR_AUFW_r_UserTcodeAssignmentSet.
/// </summary>
/// <param name="maxRowsToReturn">Max number of rows to return. If null or 0 all rows are returned.</param>
/// <returns>returnListUserTcodeAssignmentSet[]</returns>
public returnListUserTcodeAssignmentSet[] ListUserTcodeAssignmentSet(int?maxRowsToReturn)
{
returnListUserTcodeAssignmentSet[] rv = null;
DBConnect();
OdbcCommand cmd = new OdbcCommand();
cmd.CommandText = "select" + (maxRowsToReturn.HasValue && maxRowsToReturn.Value != 0 ? " top " + maxRowsToReturn.Value.ToString() : "") + " " + "\"c_id\",\"c_r_User\",\"c_r_TcodeAssignmentSet\" from \"t_RBSR_AUFW_r_UserTcodeAssignmentSet\"";
cmd.Connection = _dbConnection;
OdbcDataReader dr = cmd.ExecuteReader();
List <returnListUserTcodeAssignmentSet> rvl = new List <returnListUserTcodeAssignmentSet>();
while (dr.Read())
{
returnListUserTcodeAssignmentSet cr = new returnListUserTcodeAssignmentSet();
if (!dr.IsDBNull(0))
{
cr.ID = dr.GetInt32(0);
}
else
{
{ cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'ID'"); }
}
if (!dr.IsDBNull(1))
{
cr.UserID = dr.GetInt32(1);
}
else
{
{ cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'UserID'"); }
}
if (!dr.IsDBNull(2))
{
cr.TcodeAssignmentSetID = dr.GetInt32(2);
}
else
{
{ cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'TcodeAssignmentSetID'"); }
}
rvl.Add(cr);
}
dr.Close();
dr.Dispose();
rv = rvl.ToArray();
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
///
/// select a set of rows from table t_RBSR_AUFW_u_User.
/// </summary>
/// <param name="maxRowsToReturn">Max number of rows to return. If null or 0 all rows are returned.</param>
/// <returns>returnListUser[]</returns>
public returnListUser[] ListUser(int?maxRowsToReturn)
{
returnListUser[] rv = null;
DBConnect();
OdbcCommand cmd = new OdbcCommand();
cmd.CommandText = "select" + (maxRowsToReturn.HasValue && maxRowsToReturn.Value != 0 ? " top " + maxRowsToReturn.Value.ToString() : "") + " " + "\"c_id\",\"c_u_EID\",\"c_u_Name\" from \"t_RBSR_AUFW_u_User\"";
cmd.Connection = _dbConnection;
OdbcDataReader dr = cmd.ExecuteReader();
List <returnListUser> rvl = new List <returnListUser>();
while (dr.Read())
{
returnListUser cr = new returnListUser();
if (!dr.IsDBNull(0))
{
cr.ID = dr.GetInt32(0);
}
else
{
throw new Exception("Value 'null' is not allowed for 'ID'");
}
if (!dr.IsDBNull(1))
{
cr.EID = dr.GetString(1);
}
else
{
throw new Exception("Value 'null' is not allowed for 'EID'");
}
if (!dr.IsDBNull(2))
{
cr.Name = dr.GetString(2);
}
else
{
throw new Exception("Value 'null' is not allowed for 'Name'");
}
rvl.Add(cr);
}
dr.Close();
dr.Dispose();
rv = rvl.ToArray();
cmd.Dispose();
DBClose();
return(rv);
}
public static string GetNameFromPlayerNumbersTableIndividual(OdbcConnection conn, int sectionID, int round, int playerNo)
{
string number = "###";
string name = "";
string SQLString = $"SELECT Number, Name, Round, TimeLog FROM PlayerNumbers WHERE Section={sectionID} AND TabPlayPairNo={playerNo}";
OdbcCommand cmd = new OdbcCommand(SQLString, conn);
OdbcDataReader reader = null;
try
{
ODBCRetryHelper.ODBCRetry(() =>
{
reader = cmd.ExecuteReader();
DateTime latestTimeLog = new DateTime(2010, 1, 1);
while (reader.Read())
{
try
{
int readerRound = reader.GetInt32(2);
DateTime timeLog;
if (reader.IsDBNull(3))
{
timeLog = new DateTime(2010, 1, 1);
}
else
{
timeLog = reader.GetDateTime(3);
}
if (readerRound <= round && timeLog >= latestTimeLog)
{
number = reader.GetString(0);
name = reader.GetString(1);
latestTimeLog = timeLog;
}
}
catch // Record found, but format cannot be parsed
{
if (number == "###")
{
number = "";
}
}
}
});
}
finally
{
cmd.Dispose();
reader.Close();
}
if (number == "###") // Nothing found
{
number = "";
}
return(FormatName(name, number));
}
/// <summary>
///
/// select a row from table t_RBSR_AUFW_r_UserTcodeAssignmentSet.
/// </summary>
/// <param name="ID"></param>
/// <returns>returnGetUserTcodeAssignmentSet</returns>
public returnGetUserTcodeAssignmentSet GetUserTcodeAssignmentSet(int ID)
{
returnGetUserTcodeAssignmentSet rv = new returnGetUserTcodeAssignmentSet();
DBConnect();
OdbcCommand cmd = new OdbcCommand();
cmd.CommandText = "select \"c_id\",\"c_r_User\",\"c_r_TcodeAssignmentSet\" from \"t_RBSR_AUFW_r_UserTcodeAssignmentSet\" where \"c_id\"= ?";
cmd.Parameters.Add("c_id", OdbcType.Int);
cmd.Parameters["c_id"].Value = (object)ID;
cmd.Connection = _dbConnection;
OdbcDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
if (!dr.IsDBNull(0))
{
rv.ID = dr.GetInt32(0);
}
else
{
{ cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'ID'"); }
}
if (!dr.IsDBNull(1))
{
rv.UserID = dr.GetInt32(1);
}
else
{
{ cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'UserID'"); }
}
if (!dr.IsDBNull(2))
{
rv.TcodeAssignmentSetID = dr.GetInt32(2);
}
else
{
{ cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'TcodeAssignmentSetID'"); }
}
}
dr.Close();
dr.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
///
/// select a row from table t_RBSR_AUFW_u_User.
/// </summary>
/// <param name="ID"></param>
/// <returns>returnGetUser</returns>
public returnGetUser GetUser(int ID)
{
returnGetUser rv = new returnGetUser();
DBConnect();
OdbcCommand cmd = new OdbcCommand();
cmd.CommandText = "select \"c_id\",\"c_u_EID\",\"c_u_Name\" from \"t_RBSR_AUFW_u_User\" where \"c_id\"= ?";
cmd.Parameters.Add("c_id", OdbcType.Int);
cmd.Parameters["c_id"].Value = (object)ID;
cmd.Connection = _dbConnection;
OdbcDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
if (!dr.IsDBNull(0))
{
rv.ID = dr.GetInt32(0);
}
else
{
throw new Exception("Value 'null' is not allowed for 'ID'");
}
if (!dr.IsDBNull(1))
{
rv.EID = dr.GetString(1);
}
else
{
throw new Exception("Value 'null' is not allowed for 'EID'");
}
if (!dr.IsDBNull(2))
{
rv.Name = dr.GetString(2);
}
else
{
throw new Exception("Value 'null' is not allowed for 'Name'");
}
}
dr.Close();
dr.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
///
/// select a row from table t_RBSR_AUFW_u_TcodeDictionary.
/// </summary>
/// <param name="ID"></param>
/// <returns>returnGetTcodeDictionary</returns>
public returnGetTcodeDictionary GetTcodeDictionary(int ID)
{
returnGetTcodeDictionary rv = new returnGetTcodeDictionary();
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "select \"c_id\",\"c_u_TcodeID\",\"c_u_Description\" from \"t_RBSR_AUFW_u_TcodeDictionary\" where \"c_id\"= ?";
cmd.Parameters.Add("c_id", OdbcType.Int);
cmd.Parameters["c_id"].Value = (object)ID;
cmd.Connection = _dbConnection;
OdbcDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
if (dr.IsDBNull(0))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'ID'");
}
else
{
rv.ID = dr.GetInt32(0);
}
if (dr.IsDBNull(1))
{
cmd.Dispose(); DBClose(); throw new Exception("Value 'null' is not allowed for 'TcodeID'");
}
else
{
rv.TcodeID = dr.GetString(1);
}
if (dr.IsDBNull(2))
{
rv.Description = null;
}
else
{
rv.Description = dr.GetString(2);
}
}
dr.Close();
dr.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}
public bool IsNewMeeting(string strMeetingID)
{
// Quick error checks
if (strMeetingID == null || strMeetingID.Length == 0)
{
throw new ArgumentException("Invalid meeting ID", "strMeetingID");
}
bool bRetVal = false;
OdbcDataReader dr = null;
try
{
StringBuilder strQueryBuilder = new StringBuilder();
strQueryBuilder.Append(" SELECT COUNT(*) ");
strQueryBuilder.Append(" FROM ");
strQueryBuilder.Append(Constants.MEETINGS_TABLENAME);
strQueryBuilder.Append(" WHERE ");
strQueryBuilder.Append(Constants.MTG_ID);
strQueryBuilder.Append("=");
strQueryBuilder.Append("'" + QueryService.MakeQuotesafe(strMeetingID) + "'");
dr = QueryService.ExecuteReader(this.DBConnect, strQueryBuilder.ToString());
if (dr == null)
{
throw new Exception("Null data reader returned from query");
}
// Advance data reader to first record
if (dr.Read())
{
int nCount = -1;
if (!dr.IsDBNull(0))
{
nCount = dr.GetInt32(0);
}
if (nCount == 0)
{
bRetVal = true;
}
}
}
catch (Exception /*e*/)
{
}
finally
{
if (dr != null)
{
dr.Close();
}
}
return(bRetVal);
}
} // End GetServiceLocation
/* Function determines whether a service has a specific
* method registered.
* Input: strServiceName - name of service
* strMethod - name of method
* Return: true if service has registered method, false
* otherwise.
*/
public bool MethodExists(string strServiceName, string strMethod)
{
bool bRetVal = false;
OdbcDataReader dr = null;
int nCount = 0;
try
{
int nServiceID = GetServiceID(strServiceName);
if (nServiceID == -1)
{
return(false);
}
StringBuilder strSQL = new StringBuilder();
strSQL.Append("SELECT COUNT(*) FROM ");
strSQL.Append(DBC.SERVICE_METHODS_TABLE);
strSQL.Append(" WHERE " + DBC.RS_SERVICE_ID + "=" + nServiceID.ToString());
strSQL.Append(" AND " + DBC.SM_METHODNAME + "=" + "'" + strMethod + "'");
dr = ExecuteReader(strSQL.ToString());
if (dr != null)
{
dr.Read();
if (!dr.IsDBNull(0))
{
nCount = dr.GetInt32(0);
}
if (nCount > 0)
{
bRetVal = true;
}
if (!dr.IsClosed)
{
dr.Close();
}
}
}
catch (System.Exception e)
{
// Report error
EventLog.WriteEntry(SOURCENAME, e.Message, EventLogEntryType.Error);
}
finally // cleanup after exception handled
{
if (dr != null)
{
if (!dr.IsClosed)
{
dr.Close();
}
}
}
return(bRetVal);
} // End MethodExists
// A context msg is treated as a response if there is a match for
// the message id in the context messages sent table
public bool IsContextMessageResponse(ContextMsg ctxMsg)
{
// Quick error checks
if (ctxMsg == null)
{
throw new ArgumentNullException("ctxMsg", "Invalid context message");
}
bool bRetVal = false;
OdbcDataReader dr = null;
try
{
StringBuilder strQueryBuilder = new StringBuilder();
strQueryBuilder.Append(" SELECT COUNT(*) ");
strQueryBuilder.Append(" FROM ");
strQueryBuilder.Append(Constants.CONTEXT_MSGS_SENT_TABLENAME);
strQueryBuilder.Append(" WHERE ");
strQueryBuilder.Append(Constants.CTXMSG_ID);
strQueryBuilder.Append("=");
strQueryBuilder.Append("'" + QueryService.MakeQuotesafe(ctxMsg.MessageID) + "'");
dr = QueryService.ExecuteReader(this.DBConnect, strQueryBuilder.ToString());
if (dr == null)
{
throw new Exception("Null data reader returned from query");
}
// Advance data reader to first record
if (dr.Read())
{
int nCount = -1;
if (!dr.IsDBNull(0))
{
nCount = dr.GetInt32(0);
}
if (nCount >= 1)
{
bRetVal = true;
}
}
}
catch (Exception /*e*/)
{
}
finally
{
if (dr != null)
{
dr.Close();
}
}
return(bRetVal);
}
