public bool Validar(string usuario, string senha)
{
if (_meuBd.VerificarStatusConexao() == ConnectionState.Closed)
{
_meuBd.Conectar();
}
OdbcCommand odbcCMD = new OdbcCommand(_valida, _meuBd.Connection);
odbcCMD.Parameters.Add("codigo_usuario", OdbcType.VarChar, 20).Value = usuario;
odbcCMD.Parameters.Add("senha_usuario", OdbcType.VarChar, 20).Value = senha;
OdbcDataReader reader = odbcCMD.ExecuteReader();
if (reader.HasRows)
{
clsGlobal.Login = usuario;
clsGlobal.Perfil = Convert.ToInt16(reader["tipo_usuario"].ToString());
reader.Close();
reader.Dispose();
return(true);
}
else
{
reader.Close();
reader.Dispose();
return(false);
}
}
/* Given Username and Password, this method does authentication/validation of datas
* locally.
*/
public bool ValidateUser(string userName, string passWord)
{
General g = Session["app"] as General;
string pwdMD5 = g.getMd5Hash(passWord);
string SQL = "SELECT iduser, username, password, type "
+ "FROM usertab "
+ "WHERE username='******' "
//+"WHERE username=@userName "
+ "AND password = '******' "
+ "AND enabled = 1 "
+ "AND typeauthentication='Local'";
/*OleDbCommand cmd = new OleDbCommand(SQL, conDB);
* OleDbDataReader Dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);*/
/*SqlCommand cmd = new SqlCommand(SQL, conDB);
* SqlDataReader Dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);*/
OdbcCommand cmd = g.DB.PrepareSQL(SQL);
/*cmd.CommandType = CommandType.Text;
* cmd.Parameters.Add("@userName", OdbcType.VarChar, 20);
* cmd.Parameters["@userName"].Value = userName;*/
/*prm = new OdbcParameter("@username", OdbcType.VarChar, 150);
* prm.Direction = ParameterDirection.Input;
* prm.Value = userName;
* cmd.Parameters.Add(prm);*/
try
{
OdbcDataReader result = cmd.ExecuteReader();
if (result.HasRows)
{
if (g.verifyMd5Hash(passWord, pwdMD5))
{
_idUser = result["iduser"].ToString();
result.Close();
result.Dispose();
cmd.Dispose();
_Status = true;
return(true);
}
}
result.Close();
result.Dispose();
}
catch (OdbcException o)
{
g.ErrorMSG = g.DB.catchODBCException(o, g.ErrorLevel);
}
finally
{
cmd.Dispose();
}
return(false);
}
/// <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);
}
private void button2_Click(object sender, EventArgs e)
{
string mDataPostavka = dataGridView1.CurrentCell.Value.ToString();
OdbcConnection odConnect = new OdbcConnection("DSN=bdr");
odConnect.Open();
OdbcCommand odbCommand = new OdbcCommand("Select Postavka.IdPostavka,Postavka.DatePostavka from Postavka where Postavka.DatePostavka = '" + mDataPostavka + "'", odConnect);
OdbcDataReader odRead = odbCommand.ExecuteReader();
int idPostavka = 0;
if (odRead.Read())
{
idPostavka = Convert.ToInt32(odRead["IdPostavka"].ToString());
}
odRead.Dispose();
odConnect.Close();
odConnect.Open();
OdbcCommand odCommand = new OdbcCommand("delete from Postavka where Postavka.IdPostavka = '" + idPostavka + "'", odConnect);
OdbcDataReader dtRead = odCommand.ExecuteReader();
dtRead.Dispose();
odConnect.Close();
postavkaTableAdapter.Fill(mebelBDDataSet.Postavka);
}
/// <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_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>
/// Permite valorar si el empleado tiene justificado el día ingresado
/// </summary>
/// <param name="EmpleadoId"></param>
/// <param name="Fecha"></param>
/// <returns></returns>
public bool GetJustificacion(int EmpleadoId, DateTime Fecha)
{
bool DiaJustificado = false;
try
{
string QueryString = @"EXEC stp_GetJustificacionFalta " + EmpleadoId + ", '" + Fecha.ToString("yyyyMMdd") + "'";
System.Data.Odbc.OdbcCommand command = new System.Data.Odbc.OdbcCommand(QueryString);
using (System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(ConnectionString))
{
command.Connection = connection;
connection.Open();
using (OdbcDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
DiaJustificado = reader.GetBoolean(0);
}
reader.Close();
reader.Dispose();
}
connection.Close();
connection.Dispose();
}
}
catch (Exception ex)
{
Log.EscribeLog("Error: ElsabonDA.GetJustificacion - " + ex.Message);
}
return(DiaJustificado);
}
/// <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);
}
private void button2_Click(object sender, EventArgs e)
{
int ftCon = 0;
string stDolznosti = dataGridView1.CurrentCell.Value.ToString();
OdbcConnection onConect = new OdbcConnection("DSN=bdr");
onConect.Open();
OdbcCommand idDolzh = new OdbcCommand("select Dolzhnosti.IdDolzhnosti,Dolzhnosti.NameDolzhnosti from Dolzhnosti where Dolzhnosti.NameDolzhnosti ='" + stDolznosti.Replace("'", "") + "'", onConect);
OdbcDataReader dataRead = idDolzh.ExecuteReader();
string dolzhnosti = "";
if (dataRead.Read())
{
ftCon = Convert.ToInt32(dataRead["IdDolzhnosti"].ToString());
dolzhnosti = dataRead["NameDolzhnosti"].ToString();
}
dataRead.Dispose();
onConect.Close();
dolzhnostiTableAdapter.Delete(ftCon, dolzhnosti);
mebelBDDataSet.Clear();
dolzhnostiTableAdapter.Fill(mebelBDDataSet.Dolzhnosti);
}
private void button2_Click(object sender, EventArgs e)
{
int ftCon = 0;
string stCategory = dataGridView1.CurrentCell.Value.ToString();
OdbcConnection odConnect = new OdbcConnection("DSN=bdr");
odConnect.Open();
OdbcCommand odCom = new OdbcCommand("SELECT Kategori.IdKategori, Kategori.NameKategori FROM Kategori where Kategori.NameKategori ='" + stCategory.Replace("'", "") + "'", odConnect);
OdbcDataReader odRead = odCom.ExecuteReader();
string nameCategori = "";
if (odRead.Read())
{
ftCon = Convert.ToInt32(odRead["IdKategori"].ToString());
nameCategori = odRead["NameKategori"].ToString();
}
odRead.Dispose();
odConnect.Close();
kategoriTableAdapter.Delete(ftCon, nameCategori);
mebelBDDataSet.Clear();
kategoriTableAdapter.Fill(mebelBDDataSet.Kategori);
}
public bool Carregar(string usuario)
{
try
{
if (_meuBd.VerificarStatusConexao() == ConnectionState.Closed)
{
_meuBd.Conectar();
}
OdbcCommand odbcCMD = new OdbcCommand(_carrega, _meuBd.Connection);
odbcCMD.Parameters.Add("codigo_usuario", OdbcType.VarChar, 20).Value = usuario;
OdbcDataReader reader = odbcCMD.ExecuteReader();
while (reader.Read())
{
_codigoUsuario = reader[0].ToString();
_nomeUsuario = reader[1].ToString();
_senhaUsuario = reader[2].ToString();
_emailUsuario = reader[3].ToString();
_tipoUsuario = Convert.ToInt16(reader[4]);
_usuarioAtivo = Convert.ToInt16(reader[5]);
}
reader.Close();
reader.Dispose();
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
public bool Carregar(int codigo)
{
try
{
if (_meuBd.VerificarStatusConexao() == ConnectionState.Closed)
{
_meuBd.Conectar();
}
OdbcCommand odbcCMD = new OdbcCommand(_carrega, _meuBd.Connection);
odbcCMD.Parameters.Add("codigo_tipo", OdbcType.Int).Value = codigo;
OdbcDataReader reader = odbcCMD.ExecuteReader();
while (reader.Read())
{
_codigoTipo = Convert.ToInt16(reader[0]);
_descricaoTipo = reader[1].ToString();
}
reader.Close();
reader.Dispose();
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
/// <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);
}
public byte[] DlookupArrayByte(string Campo, string Tabela, string Criterio)
{
try
{
if (_meuBd.VerificarStatusConexao() == ConnectionState.Closed)
{
_meuBd.Conectar();
}
string strSQL = "select " + Campo + " from " + Tabela + (Criterio != "" ? " where " + Criterio : "");
OdbcCommand odbcCMD = new OdbcCommand(strSQL, _meuBd.Connection);
OdbcDataReader dr = odbcCMD.ExecuteReader();
if (dr.HasRows)
{
return((byte[])dr.GetValue(0));
}
else
{
return(null);
}
dr.Close();
dr.Dispose();
}
catch (Exception ex)
{
throw ex;
}
}
/// <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);
}
public bool Carregar(int empresa)
{
try
{
if (_meuBd.VerificarStatusConexao() == ConnectionState.Closed)
{
_meuBd.Conectar();
}
OdbcCommand odbcCMD = new OdbcCommand(_carrega, _meuBd.Connection);
OdbcDataReader reader = odbcCMD.ExecuteReader();
while (reader.Read())
{
_codigoEmpresa = Convert.ToInt16(reader[0]);
_razaoSocial = reader[1].ToString();
_protocoloEnvio = Convert.ToInt32(reader[2]);
_protocoloAutorizacao = Convert.ToInt32(reader[3]);
}
reader.Close();
reader.Dispose();
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
public void selectLanguage(object sender, EventArgs e, string id)
{
General g = Session["app"] as General;
string tmsg = "", SQL = "SELECT * "
+ "FROM languagetab "
+ "WHERE idlanguage=" + id;
OdbcCommand cmd = g.DB.PrepareSQL(SQL);
try
{
OdbcDataReader result = cmd.ExecuteReader();
if (result.HasRows)
{
lblidLanguage.Text = result["idlanguage"].ToString();
txtTitle.Text = result["title"].ToString();
txtAbreviation.Text = result["abreviation"].ToString();
populateMedia(sender, e, result["idmedia"].ToString());
if (result["enabled"].ToString() == "0")
{
chbEnabled.Checked = false;
}
else
{
chbEnabled.Checked = true;
}
}
else
{
msg.InnerHtml = "We are sorry, we are experiencing technical problems ...";
}
result.Close();
result.Dispose();
}
catch (OdbcException o)
{
tmsg = g.DB.catchODBCException(o, g.ErrorLevel);
}
finally
{
cmd.Dispose();
if (tmsg.Length == 0)
{
msg.InnerHtml = "Selected successfully!";
}
else
{
msg.InnerHtml = tmsg;
}
}
statusEdit(sender, e, true);
listLanguages(sender, e);
}
/// <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);
}
public bool Carregar(int arquivo)
{
try
{
if (_meuBd.VerificarStatusConexao() == ConnectionState.Closed)
{
_meuBd.Conectar();
}
OdbcCommand odbcCMD = new OdbcCommand(_carrega, _meuBd.Connection);
odbcCMD.Parameters.Add("codigo_envio", OdbcType.Int).Value = arquivo;
OdbcDataReader reader = odbcCMD.ExecuteReader();
while (reader.Read())
{
_codigoEnvio = Convert.ToInt16(reader["codigo_envio"].ToString());
_tipoArquivo = Convert.ToInt16(reader["tipo_arquivo"].ToString());
_descricaoTipo = reader["descricao_tipo"].ToString();
_descricaoArquivo = reader["descricao_arquivo"].ToString();
_nomeArquivo = reader["nome_arquivo"].ToString();
_tamanhoArquivo = Convert.ToInt16(reader["tamanho_arquivo"].ToString());
_codigoUsuarioEnvio = reader["codigo_usuario_envio"].ToString();
_dataEnvio = Convert.ToDateTime(reader["data_envio"].ToString());
_codigoUsuario_aprov = reader["codigo_usuario_aprov"].ToString();
if (reader["data_aprov"].ToString() != "")
{
_dataAprov = Convert.ToDateTime(reader["data_aprov"].ToString());
}
_codigoUsuarioReprov = reader["codigo_usuario_reprov"].ToString();
if (reader["data_reprov"].ToString() != "")
{
_dataReprov = Convert.ToDateTime(reader["data_reprov"].ToString());
}
_motivoReprov = reader["motivo_reprov"].ToString();
_protocoloEnvio = Convert.ToInt32(reader["protocolo_envio"].ToString());
if (reader["protocolo_aut"].ToString() != "")
{
_protocoloAutorizacao = Convert.ToInt32(reader["protocolo_aut"].ToString());
}
}
reader.Close();
reader.Dispose();
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
//Select group of menu by id
public void selectGroupMenu(object sender, EventArgs e, string id)
{
General g = Session["app"] as General;
string SQL = "SELECT * "
+ "FROM menugrouptab "
+ "WHERE idgroupmenu=" + id;
string tmsg = "";
OdbcCommand cmd = g.DB.PrepareSQL(SQL);
try
{
OdbcDataReader result = cmd.ExecuteReader();
if (result.HasRows)
{
lblidGroupMenu.Text = id;
populateLanguages(sender, e, result["idlanguage"].ToString());
txtTitle.Text = result["title"].ToString();
if (result["enabled"].ToString() == "0")
{
chbEnabled.Checked = false;
}
else
{
chbEnabled.Checked = true;
}
}
else
{
msg.InnerHtml = "We are sorry, we are experiencing technical problems ...";
}
result.Close();
result.Dispose();
}
catch (OdbcException o)
{
tmsg = g.DB.catchODBCException(o, g.ErrorLevel);
}
finally
{
if (tmsg.Length != 0)
{
msg.InnerHtml = tmsg;
}
else
{
msg.InnerHtml = tmsg;
}
cmd.Dispose();
}
statusEdit(sender, e, true);
}
public void selectTag(object sender, EventArgs e, string id)
{
General g = Session["app"] as General;
string tmsg = "", SQL = "SELECT * "
+ "FROM tagtab "
+ "WHERE idtag=" + id.ToString();
OdbcCommand cmd = g.DB.PrepareSQL(SQL);
try
{
OdbcDataReader result = cmd.ExecuteReader();
if (result.HasRows)
{
lblidTag.Text = result["idtag"].ToString();
txtTag.Enabled = true;
txtTag.Text = result["tag"].ToString();
cbEnabled.Enabled = true;
if (result["enabled"].ToString() == "1")
{
cbEnabled.Checked = true;
}
else
{
cbEnabled.Checked = false;
}
}
else
{
msg.InnerHtml = "We are sorry, we are experiencing technical problems ...";
}
result.Close();
result.Dispose();
}
catch (OdbcException o)
{
tmsg = g.DB.catchODBCException(o, g.ErrorLevel);
}
finally
{
cmd.Dispose();
if (tmsg.Length == 0)
{
msg.InnerHtml = "Selected successfully!";
}
else
{
msg.InnerHtml = tmsg;
}
}
statusEdit(sender, e, true);
}
/// <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);
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_SAProle.
/// </summary>
/// <param name="Name"></param>
/// <param name="SubProcessID"></param>
/// <param name="System"></param>
/// <param name="Platform"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewSAProle(string Name, int SubProcessID, string System, string Platform)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_SAProle\"(\"c_u_Name\",\"c_r_SubProcess\",\"c_u_System\",\"c_u_Platform\") VALUES(?,?,?,?)";
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
if (Name == null)
{
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.Parameters.Add("c_r_SubProcess", OdbcType.Int);
cmd.Parameters["c_r_SubProcess"].Value = (object)SubProcessID;
if (System == null)
{
throw new Exception("System must not be null!");
}
cmd.Parameters.Add("c_u_System", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_System"].Value = (System != null ? (object)System : DBNull.Value);
if (Platform == null)
{
throw new Exception("Platform must not be null!");
}
cmd.Parameters.Add("c_u_Platform", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_Platform"].Value = (Platform != null ? (object)Platform : DBNull.Value);
OdbcDataReader dri = cmd.ExecuteReader();
if (_dbConnection.Driver.ToLower().StartsWith("myodbc"))
{
cmd = _dbConnection.CreateCommand();
cmd.CommandText = "SELECT LAST_INSERT_ID()";
dri = cmd.ExecuteReader();
}
dri.Read();
rv = (dri.IsDBNull(0) ? 0 : (typeof(long).Equals(dri.GetFieldType(0)) ? (int)dri.GetInt64(0) : (int)dri.GetInt32(0)));
dri.Close();
if (rv == 0)
{
throw new Exception("Insert operation failed!");
}
dri.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}
private void button1_Click(object sender, EventArgs e)
{
string mNameTovar = textBox1.Text;
string mKolvo = textBox2.Text;
string mStoimost = textBox3.Text;
string mOpisannie = textBox4.Text;
string mCombText = comboBox1.Text;
int IdCombo = 0;
OdbcConnection odConnect = new OdbcConnection("DSN=bdr");
odConnect.Open();
OdbcCommand odComand = new OdbcCommand("select Kategori.IdKategori,Kategori.NameKategori from Kategori where Kategori.NameKategori ='" + mCombText.Replace("'", "") + "'", odConnect);
OdbcDataReader odRead = odComand.ExecuteReader();
if (odRead.Read())
{
IdCombo = Convert.ToInt32(odRead["IdKategori"].ToString());
}
// MessageBox.Show(IdCombo.ToString());
// MessageBox.Show("insert into Tovari values('" + mNameTovar + "'," + "'"+ mStoimost + "',"+ "'" + mKolvo + "'," + "'" + mOpisannie +"'," + "'" + IdCombo+ "')");
odRead.Dispose();
odConnect.Close();
odConnect.Open();
OdbcCommand odTableCom = new OdbcCommand("insert into Tovari values('" + mNameTovar + "'," + "'" + mStoimost + "'," + "'" + mKolvo + "'," + "'" + mOpisannie + "'," + "'" + IdCombo + "')", odConnect);
OdbcDataReader od = odTableCom.ExecuteReader();
od.Dispose();
odConnect.Close();
tovariTableAdapter.Fill(mebelBDDataSet.Tovari);
textBox1.Text = "";
textBox2.Text = "";
textBox3.Text = "";
textBox4.Text = "";
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_BusRoleOwner.
/// </summary>
/// <param name="EID"></param>
/// <param name="Geography"></param>
/// <param name="Rank"></param>
/// <param name="BusRoleID"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewBusRoleOwner(string EID, string Geography, string Rank, int BusRoleID)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_BusRoleOwner\"(\"c_u_EID\",\"c_u_Geography\",\"c_u_Rank\",\"c_r_BusRole\") VALUES(?,?,?,?)";
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
if (EID == null)
{
cmd.Dispose(); DBClose(); throw new Exception("EID must not be null!");
}
cmd.Parameters.Add("c_u_EID", OdbcType.NVarChar, 10);
cmd.Parameters["c_u_EID"].Value = (EID != null ? (object)EID : DBNull.Value);
if (Geography == null)
{
cmd.Dispose(); DBClose(); throw new Exception("Geography must not be null!");
}
cmd.Parameters.Add("c_u_Geography", OdbcType.NVarChar, 10);
cmd.Parameters["c_u_Geography"].Value = (Geography != null ? (object)Geography : DBNull.Value);
if (Rank == null)
{
cmd.Dispose(); DBClose(); throw new Exception("Rank must not be null!");
}
cmd.Parameters.Add("c_u_Rank", OdbcType.NVarChar, 10);
cmd.Parameters["c_u_Rank"].Value = (Rank != null ? (object)Rank : DBNull.Value);
cmd.Parameters.Add("c_r_BusRole", OdbcType.Int);
cmd.Parameters["c_r_BusRole"].Value = (object)BusRoleID;
OdbcDataReader dri = null; try{ dri = cmd.ExecuteReader(); }catch (Exception edri) { cmd.Dispose(); DBClose(); throw edri; }
if (_dbConnection.Driver.ToLower().StartsWith("myodbc"))
{
cmd = _dbConnection.CreateCommand();
cmd.CommandText = "SELECT LAST_INSERT_ID()";
dri = cmd.ExecuteReader();
}
try{ dri.Read(); } catch (Exception edri) { cmd.Dispose(); DBClose(); throw edri; }
rv = (dri.IsDBNull(0) ? 0 : (typeof(long).Equals(dri.GetFieldType(0)) ? (int)dri.GetInt64(0) : dri.GetInt32(0)));
dri.Close();
if (rv == 0)
{
cmd.Dispose(); DBClose(); throw new Exception("Insert operation failed!");
}
dri.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}
protected virtual void Dispose(bool disposing)
{
if (_reader != null)
{
_reader.Close();
_reader.Dispose();
}
if (_command != null)
{
_command.Dispose();
}
}
private void button1_Click(object sender, EventArgs e)
{
string mDateTovar = DateTime.Now.ToString("yyyy-MM-dd");
string mTovar = comboBox1.Text;
string mPostavhik = comboBox2.Text;
string mKolvo = textBox2.Text;
int idCombo = 0;
OdbcConnection odConnect = new OdbcConnection("DSN=bdr");
odConnect.Open();
OdbcCommand odbCommand = new OdbcCommand("select Tovari.IdTovar,Tovari.NameTovar from Tovari where NameTovar ='" + mTovar.Replace("'", "") + "'", odConnect);
OdbcDataReader odRead = odbCommand.ExecuteReader();
if (odRead.Read())
{
idCombo = Convert.ToInt32(odRead["IdTovar"].ToString());
}
odRead.Dispose();
odConnect.Close();
int idCombo2 = 0;
odConnect.Open();
OdbcCommand odCom = new OdbcCommand("select Postavhiki.IdPostavhik,Postavhiki.NamePostavhik from Postavhiki where Postavhiki.NamePostavhik = '" + mPostavhik.Replace("'", "") + "'", odConnect);
OdbcDataReader odbRead = odCom.ExecuteReader();
if (odbRead.Read())
{
idCombo2 = Convert.ToInt32(odbRead["IdPostavhik"].ToString());
}
odbRead.Dispose();
odConnect.Close();
odConnect.Open();
OdbcCommand odCommand = new OdbcCommand("INSERT INTO Postavka VALUES('" + mDateTovar + "','" + idCombo + "','" + idCombo2 + "','" + mKolvo + "')", odConnect);
OdbcDataReader dtRead = odCommand.ExecuteReader();
dtRead.Dispose();
odConnect.Close();
postavkaTableAdapter.Fill(mebelBDDataSet.Postavka);
}
//Select Media Group to display in a form
public void selectMediaGroup(object sender, EventArgs e, string id)
{
General g = Session["app"] as General;
string tmsg = "", SQL = "SELECT *, if (enabled, 'true', 'false') as enabled "
+ "FROM mediagrouptab "
+ "WHERE idmediagroup=" + id;
OdbcCommand cmd = g.DB.PrepareSQL(SQL);
try
{
OdbcDataReader result = cmd.ExecuteReader();
if (result.HasRows)
{
lblidGroupMedia.Text = result["idmediagroup"].ToString();
txtTitle.Text = result["title"].ToString();
txtDescription.Text = result["description"].ToString();
cbEnabled.Checked = Boolean.Parse(result["enabled"].ToString());
}
else
{
msg.InnerHtml = "We are sorry, we are experiencing technical problems ...";
}
result.Close();
result.Dispose();
}
catch (OdbcException o)
{
tmsg = g.DB.catchODBCException(o, g.ErrorLevel);
}
finally
{
cmd.Dispose();
if (tmsg.Length == 0)
{
msg.InnerHtml = "Selected successfully!";
}
else
{
msg.InnerHtml = tmsg;
}
}
statusEdit(sender, e, true);
listMediaGroup(sender, e);
populateMediaGroup(sender, e);
}
public int MySQLquery()
{
if (this.MySQLconnection == null)
{
return(-1);
}
if (this.MySQLconnection.State == ConnectionState.Closed)
{
MessageBox.Show(StringsDictionary.KeyValue("FormDB_MySQLquery_closed", this.culture),
StringsDictionary.KeyValue("FormDB_MySQLquery_closed_header", this.culture),
MessageBoxButtons.OK, MessageBoxIcon.Error);
return(-1);
}
int r = 0;
string MySQLcmd = "";
MySQLcmd = "SELECT COUNT(*) FROM "
+ this.ServerInfo.Tablename
+ " WHERE ( prefix BETWEEN inet6_aton('" + this.prefix.Split('/')[0] + "')"
+ " AND inet6_aton('" + this.end.Split('/')[0] + "')"
+ " AND parentpflen= " + parentpflen + " AND pflen= " + pflen + " ) ";
OdbcCommand MyCommand = new OdbcCommand(MySQLcmd, this.MySQLconnection);
try
{
MyDataReader = MyCommand.ExecuteReader();
MyDataReader.Read();
r = int.Parse(MyDataReader.GetString(0));
MyDataReader.Close();
if (MyDataReader is IDisposable)
{
MyDataReader.Dispose();
}
return(r);
}
//catch (System.InvalidOperationException ex)
catch (Exception ex)
{
MessageBox.Show(ex.Message +
StringsDictionary.KeyValue("FormDB_MySQLquery_exception", this.culture),
StringsDictionary.KeyValue("FormDB_MySQLquery_exception_header", this.culture),
MessageBoxButtons.OK, MessageBoxIcon.Error);
return(-1);
}
}