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
}
//Consulta
public List <string> RNFN_Pesquisa(string CNPJ)
{
Ender_Banco Conexao = new Ender_Banco();
List <string> Valid = new List <string>();
string Query = "select programa_instituicao.nome_programa,programa_instituicao.id_instituicao from " +
"programa_instituicao," +
"cadastro_instituicao " +
"where cadastro_instituicao.cnpj=? " +
"and programa_instituicao.id_programa = cadastro_instituicao.id_instituicao;";
OdbcConnection Data_Conection = new OdbcConnection(Conexao.Conexao_Banco);
OdbcCommand command = new OdbcCommand(Query, Data_Conection);
try
{
Data_Conection.Open();
command.CommandText = Query;
command.Parameters.AddWithValue("@cadastro_instituicao.cnpj", CNPJ);
command.ExecuteNonQuery();
OdbcDataReader Reader = command.ExecuteReader();
while (Reader.Read())
{
Valid.Add(Reader.GetString(0));
Valid.Add(Reader.GetInt64(1).ToString());
}
}
catch (OdbcException Erro)
{
Valid.Add(Erro.Message);
}
finally
{
Data_Conection.Close();
}
return(Valid);
}
public List <string> CE_dispesa_realizada(string Query, string[] Coluna, string[] Filtros)
{
Ender_Banco Conexao = new Ender_Banco();
List <string> Valid = new List <string>();
OdbcConnection Data_Conection = new OdbcConnection(Conexao.Conexao_Banco);
OdbcCommand command = new OdbcCommand(Query, Data_Conection);
try
{
Data_Conection.Open();
command.CommandText = Query;
for (int cont = 0; cont < Coluna.Count(); cont++)
{
command.Parameters.AddWithValue("@" + Coluna[cont], Filtros[cont]);
}
command.ExecuteNonQuery();
OdbcDataReader Reader = command.ExecuteReader();
while (Reader.Read())
{
Valid.Add(Reader.GetInt64(0).ToString());
Valid.Add(Reader.GetString(1).ToString());
Valid.Add(Reader.GetString(2).ToString());
Valid.Add(Reader.GetString(3).ToString());
Valid.Add(Reader.GetInt16(4).ToString());
Valid.Add(Reader.GetInt64(5).ToString());
Valid.Add(Reader.GetDate(6).ToString("yyyy-MM-dd"));
}
}
catch (OdbcException Erro)
{
Valid.Add(Erro.Message);
}
finally
{
Data_Conection.Close();
}
return(Valid);
}
public static int GetMaxPromotionID(DBManager db)
{
OdbcDataReader reader = db.Query("SELECT MAX(promotion_id) from promotion");
if (reader.Read())
{
if (reader.IsDBNull(0))
{
return(1);
}
int maxID = (int)reader.GetInt64(0);
return(maxID);
}
return(0);
}
public static int GetMaxTransRegisID(DBManager db, int branchId)
{
OdbcDataReader reader = db.Query("SELECT MAX(transaction_id) from registration where branch_id=" + branchId);
if (reader.Read())
{
if (reader.IsDBNull(0))
{
return(1);
}
int maxID = (int)reader.GetInt64(0);
return(maxID);
}
return(0);
}
public static int GetMaxRecord()
{
DBManager db = new MySQLDBManager(Config.DB_SERVER, Config.DB_NAME, Config.DB_USER, Config.DB_PASSWORD, Config.DB_CHAR_ENC);
db.Connect();
OdbcDataReader reader = db.Query("SELECT MAX(teacher_id) FROM teacher");
if (!reader.Read())
{
return(0);
}
long max = reader.IsDBNull(0) ? -1 : reader.GetInt64(0);
db.Close();
return((int)(max + 1));
}
private void llenarPieChartMantenimientosPuntoVenta()
{
SeriesCollection seriesNumActivos = new SeriesCollection();
Func <ChartPoint, string> labelNumActivos = pcMantenimientosPuntoVenta => string.Format("{0} ({1:P})", pcMantenimientosPuntoVenta.Y, pcMantenimientosPuntoVenta.Participation);
try
{
string sBuscar = "SELECT COUNT(tbl_bitacora_mantenimiento.PK_idBitacora)" +
", tbl_punto_Venta.nombre" +
" FROM tbl_bitacora_mantenimiento INNER JOIN tbl_activo " +
" ON tbl_bitacora_mantenimiento.PK_idActivo = tbl_activo.PK_idActivo" +
" INNER JOIN tbl_empleado ON tbl_activo.cod_empleado_asignado = tbl_empleado.PK_idEmpleado" +
" INNER JOIN tbl_punto_Venta ON tbl_empleado.cod_punto_Venta= tbl_punto_Venta.PK_idPuntoVenta" +
" WHERE fecha BETWEEN '" + dtpInicial.Value.ToString("yyyy-MM-dd") + "' AND' " + dtpFinal.Value.ToString("yyyy-MM-dd") + "' " +
" GROUP BY tbl_punto_venta.PK_idPuntoVenta";
Console.WriteLine(sBuscar);
OdbcCommand sqlBuscar = new OdbcCommand(sBuscar, con);
OdbcDataReader almacena = sqlBuscar.ExecuteReader();
while (almacena.Read())
{
int valor = Convert.ToInt32(almacena.GetInt64(0));
seriesNumActivos.Add(new PieSeries()
{
Title = almacena.GetString(1).ToString(),
Values = new ChartValues <int> {
valor
},
DataLabels = true,
LabelPoint = labelNumActivos
});
}
almacena.Close();
pcMantenimientosPuntoVenta.Series = seriesNumActivos;
pcMantenimientosPuntoVenta.LegendLocation = LegendLocation.Right;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
private void llenarPieChartActivosPuntoVenta()
{
SeriesCollection seriesNumActivos = new SeriesCollection();
Func <ChartPoint, string> labelNumActivos = pcActivosPuntoVenta => string.Format("{0} ({1:P})", pcActivosPuntoVenta.Y, pcActivosPuntoVenta.Participation);
try
{
string sBuscar = "SELECT SUM(tbl_inventario_hardware.cantidad)" +
", tbl_punto_venta.nombre" +
" FROM tbl_inventario_hardware INNER JOIN tbl_activo " +
" ON tbl_inventario_hardware.PK_idACtivo = tbl_activo.PK_idActivo" +
" INNER JOIN tbl_empleado ON tbl_activo.cod_empleado_asignado = tbl_empleado.PK_idEmpleado" +
" INNER JOIN tbl_punto_venta ON tbl_empleado.cod_punto_venta = tbl_punto_venta.PK_idPuntoVenta " +
" GROUP BY tbl_punto_venta.PK_idPuntoVenta";
OdbcCommand sqlBuscar = new OdbcCommand(sBuscar, con);
OdbcDataReader almacena = sqlBuscar.ExecuteReader();
while (almacena.Read())
{
int valor = Convert.ToInt32(almacena.GetInt64(0));
seriesNumActivos.Add(new PieSeries()
{
Title = almacena.GetString(1).ToString(),
Values = new ChartValues <int> {
valor
},
DataLabels = true,
LabelPoint = labelNumActivos
});
}
almacena.Close();
pcActivosPuntoVenta.Series = seriesNumActivos;
pcActivosPuntoVenta.LegendLocation = LegendLocation.Right;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
private void llenarPieChartAuditoriasPendients()
{
SeriesCollection seriesNumActivos = new SeriesCollection();
Func <ChartPoint, string> labelNumActivos = pcAuditoriasPlanificadas => string.Format("{0} ({1:P})", pcAuditoriasPlanificadas.Y, pcAuditoriasPlanificadas.Participation);
try
{
string sBuscar = "SELECT COUNT(tbl_auditoria_encabezado.PK_idAuditoria)" +
", tbl_area.nombre" +
" FROM tbl_auditoria_encabezado INNER JOIN tbl_area " +
" ON tbl_auditoria_encabezado.cod_area = tbl_area.cod_area" +
" WHERE tbl_auditoria_encabezado.estado = 0 " +
" AND fecha BETWEEN '" + dtpInicial.Value.ToString("yyyy-MM-dd") + "' AND' " + dtpFinal.Value.ToString("yyyy-MM-dd") + "' " +
" GROUP BY tbl_auditoria_encabezado.cod_area ";
OdbcCommand sqlBuscar = new OdbcCommand(sBuscar, con);
OdbcDataReader almacena = sqlBuscar.ExecuteReader();
while (almacena.Read())
{
int valor = Convert.ToInt32(almacena.GetInt64(0));
seriesNumActivos.Add(new PieSeries()
{
Title = almacena.GetString(1).ToString(),
Values = new ChartValues <int> {
valor
},
DataLabels = true,
LabelPoint = labelNumActivos
});
}
almacena.Close();
pcAuditoriasPlanificadas.Series = seriesNumActivos;
pcAuditoriasPlanificadas.LegendLocation = LegendLocation.Right;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
static void Main(string[] args)
{
using (OdbcConnection conexao = ConnectionFactory.CreateConnection())
{
string sql = @"SELECT livros.id, livros.titulo, livros.preco, editoras.nome FROM livros
INNER JOIN editoras
ON editoras.id = livros.editora_id";
OdbcCommand command = new OdbcCommand(sql, conexao);
conexao.Open();
OdbcDataReader reader = command.ExecuteReader();
while (reader.Read())
{
long id = 0;
string titulo = "";
decimal preco = 0;
string editora = "";
if (!reader.IsDBNull(0))
{
id = reader.GetInt64(0);
}
if (!reader.IsDBNull(1))
{
titulo = reader.GetString(1);
}
if (!reader.IsDBNull(2))
{
preco = reader.GetDecimal(2);
}
if (!reader.IsDBNull(3))
{
editora = reader.GetString(3);
}
Console.WriteLine("{0} : {1} - {2} - {3}", id, titulo, preco, editora);
}
}
}
static void Main(string[] args)
{
using (OdbcConnection conexao = ConnectionFactory.CreateConnection())
{
string sql = "SELECT id, nome, email FROM Editoras";
OdbcCommand command = new OdbcCommand(sql, conexao);
conexao.Open();
OdbcDataReader reader = command.ExecuteReader();
List <Editora> lista = new List <Editora>();
while (reader.Read())
{
Editora editora = new Editora();
if (!reader.IsDBNull(0))
{
editora.Id = reader.GetInt64(0);
}
if (!reader.IsDBNull(1))
{
editora.Nome = reader.GetString(1);
}
if (!reader.IsDBNull(2))
{
editora.Email = reader.GetString(2);
}
lista.Add(editora);
}
foreach (var editora in lista)
{
Console.WriteLine("{0} : {1} - {2}", editora.Id, editora.Nome, editora.Email);
}
}
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_FuncApplNotes.
/// </summary>
/// <param name="BusRoleID"></param>
/// <param name="REFapplication"></param>
/// <param name="Comment"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewFuncApplNotes(int BusRoleID, int REFapplication, string Comment)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_FuncApplNotes\"(\"c_r_BusRole\",\"c_u_REFapplication\",\"c_u_Comment\") VALUES(?,?,?)";
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
cmd.Parameters.Add("c_r_BusRole", OdbcType.Int);
cmd.Parameters["c_r_BusRole"].Value = (object)BusRoleID;
cmd.Parameters.Add("c_u_REFapplication", OdbcType.Int);
cmd.Parameters["c_u_REFapplication"].Value = (object)REFapplication;
cmd.Parameters.Add("c_u_Comment", OdbcType.NText);
cmd.Parameters["c_u_Comment"].Value = (object)Comment;
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);
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_SAPauthObj.
/// </summary>
/// <param name="Name"></param>
/// <param name="Description"></param>
/// <param name="SAPauthClassID"></param>
/// <param name="Status"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewSAPauthObj(string Name, string Description, int SAPauthClassID, string Status)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_SAPauthObj\"(\"c_u_Name\",\"c_u_Description\",\"c_r_SAPauthClass\",\"c_u_Status\") 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, 30);
cmd.Parameters["c_u_Name"].Value = (Name != null ? (object)Name : DBNull.Value);
cmd.Parameters.Add("c_u_Description", OdbcType.NText);
cmd.Parameters["c_u_Description"].Value = (object)Description;
cmd.Parameters.Add("c_r_SAPauthClass", OdbcType.Int);
cmd.Parameters["c_r_SAPauthClass"].Value = (object)SAPauthClassID;
if (Status == null)
{
throw new Exception("Status must not be null!");
}
cmd.Parameters.Add("c_u_Status", OdbcType.NVarChar, 1);
cmd.Parameters["c_u_Status"].Value = (Status != null ? (object)Status : 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);
}
/// <summary>
/// Gets the value of the column at the specified index as a long integer.
/// </summary>
/// <param name="columnIndex">Index of column to examine.</param>
/// <returns>Value of the column as a long integer.</returns>
public long GetInt64(int columnIndex)
{
return(_reader.GetInt64(columnIndex));
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_SAPsecurityOrgAxis.
/// </summary>
/// <param name="English_Name"></param>
/// <param name="SAP_Name"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewSAPsecurityOrgAxis(string English_Name, string SAP_Name)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_SAPsecurityOrgAxis\"(\"c_u_English_Name\",\"c_u_SAP_Name\") VALUES(?,?)";
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
if (English_Name == null)
{
throw new Exception("English_Name must not be null!");
}
cmd.Parameters.Add("c_u_English_Name", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_English_Name"].Value = (English_Name != null ? (object)English_Name : DBNull.Value);
if (SAP_Name == null)
{
throw new Exception("SAP_Name must not be null!");
}
cmd.Parameters.Add("c_u_SAP_Name", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_SAP_Name"].Value = (SAP_Name != null ? (object)SAP_Name : 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) : dri.GetInt32(0)));
dri.Close();
if (rv == 0)
{
throw new Exception("Insert operation failed!");
}
dri.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_MVFormula.
/// </summary>
/// <param name="KEYapplication"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewMVFormula(string KEYapplication)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_MVFormula\"(\"c_u_KEYapplication\") VALUES(?)";
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
if (KEYapplication == null)
{
cmd.Dispose(); DBClose(); throw new Exception("KEYapplication must not be null!");
}
cmd.Parameters.Add("c_u_KEYapplication", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_KEYapplication"].Value = (KEYapplication != null ? (object)KEYapplication : DBNull.Value);
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);
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_EventLog.
/// </summary>
/// <param name="TStamp"></param>
/// <param name="IDuser"></param>
/// <param name="IPaddr"></param>
/// <param name="Action"></param>
/// <param name="ObjType"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewEventLog(DateTime TStamp, int IDuser, string IPaddr, string Action, string ObjType)
{
// Work around bug in the new SQL Server 2008 ODBC driver
TStamp = TStamp.AddMilliseconds(0 - TStamp.Millisecond);
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_EventLog\"(\"c_u_TStamp\",\"c_u_IDuser\",\"c_u_IPaddr\",\"c_u_Action\",\"c_u_ObjType\") VALUES(?,?,?,?,?)";
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
if (_dbConnection.Driver.ToLower().StartsWith("sqlncli"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
cmd.Parameters.Add("c_u_TStamp", OdbcType.DateTime);
cmd.Parameters["c_u_TStamp"].Value = (object)TStamp;
cmd.Parameters.Add("c_u_IDuser", OdbcType.Int);
cmd.Parameters["c_u_IDuser"].Value = (object)IDuser;
if (IPaddr == null)
{
throw new Exception("IPaddr must not be null!");
}
cmd.Parameters.Add("c_u_IPaddr", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_IPaddr"].Value = (IPaddr != null ? (object)IPaddr : DBNull.Value);
if (Action == null)
{
throw new Exception("Action must not be null!");
}
cmd.Parameters.Add("c_u_Action", OdbcType.NVarChar, 10);
cmd.Parameters["c_u_Action"].Value = (Action != null ? (object)Action : DBNull.Value);
if (ObjType == null)
{
throw new Exception("ObjType must not be null!");
}
cmd.Parameters.Add("c_u_ObjType", OdbcType.NVarChar, 3);
cmd.Parameters["c_u_ObjType"].Value = (ObjType != null ? (object)ObjType : 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) : dri.GetInt32(0)));
dri.Close();
if (rv == 0)
{
throw new Exception("Insert operation failed!");
}
dri.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_AuthRow1251.
/// </summary>
/// <param name="SAPauthObjID"></param>
/// <param name="SAPauthFieldID"></param>
/// <param name="TcodeAssignmentSetID"></param>
/// <param name="SAProleID"></param>
/// <param name="EditStatus"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewAuthRow1251(int SAPauthObjID, int SAPauthFieldID, int TcodeAssignmentSetID, int SAProleID, int EditStatus)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_AuthRow1251\"(\"c_r_SAPauthObj\",\"c_r_SAPauthField\",\"c_r_TcodeAssignmentSet\",\"c_r_SAProle\",\"c_u_EditStatus\") VALUES(?,?,?,?,?)";
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
cmd.Parameters.Add("c_r_SAPauthObj", OdbcType.Int);
cmd.Parameters["c_r_SAPauthObj"].Value = (object)SAPauthObjID;
cmd.Parameters.Add("c_r_SAPauthField", OdbcType.Int);
cmd.Parameters["c_r_SAPauthField"].Value = (object)SAPauthFieldID;
cmd.Parameters.Add("c_r_TcodeAssignmentSet", OdbcType.Int);
cmd.Parameters["c_r_TcodeAssignmentSet"].Value = (object)TcodeAssignmentSetID;
cmd.Parameters.Add("c_r_SAProle", OdbcType.Int);
cmd.Parameters["c_r_SAProle"].Value = (object)SAProleID;
cmd.Parameters.Add("c_u_EditStatus", OdbcType.Int);
cmd.Parameters["c_u_EditStatus"].Value = (object)EditStatus;
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);
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_METADATA_SubprToActivityList.
/// </summary>
/// <param name="NodeType"></param>
/// <param name="SubProcessID"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewMETADATA_SubprToActivityList(string NodeType, int SubProcessID)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_METADATA_SubprToActivityList\"(\"c_u_NodeType\",\"c_r_SubProcess\") VALUES(?,?)";
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
if (NodeType == null)
{
cmd.Dispose(); DBClose(); throw new Exception("NodeType must not be null!");
}
cmd.Parameters.Add("c_u_NodeType", OdbcType.NVarChar, 5);
cmd.Parameters["c_u_NodeType"].Value = (NodeType != null ? (object)NodeType : DBNull.Value);
cmd.Parameters.Add("c_r_SubProcess", OdbcType.Int);
cmd.Parameters["c_r_SubProcess"].Value = (object)SubProcessID;
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);
}
/// <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);
}
/// <summary>
/// Test OdbcTransaction class
/// </summary>
public static void Test_Various_DataTypes()
{
TestCases.ExecuteSQL("drop table if exists t", conn);
string sql = "create table t(";
sql += "c_integer_ai integer AUTO_INCREMENT, "; //1
sql += "c_smallint smallint, "; //2
sql += "c_integer integer, "; //3
sql += "c_bigint bigint, "; //4
sql += "c_numeric numeric(15,1), "; //5
sql += "c_float float, "; //6
sql += "c_decimal decimal(15,3), "; //7
sql += "c_double double, "; //8
sql += "c_char char, "; //9
sql += "c_varchar varchar(4096), "; //10
sql += "c_time time, "; //11
sql += "c_date date, "; //12
sql += "c_timestamp timestamp, "; //13
sql += "c_datetime datetime, "; //14
sql += "c_bit bit(1), "; //15
sql += "c_varbit bit varying(4096), "; //16
sql += "c_monetary monetary, "; //17
sql += "c_string string"; //18
sql += ")";
TestCases.ExecuteSQL(sql, conn);
sql = "insert into t values(";
sql += "1, "; //0
sql += "11, "; //1
sql += "111, "; //2
sql += "1111, "; //3
sql += "1.1, "; //4
sql += "1.11, "; //5
sql += "1.111, "; //6
sql += "1.1111, "; //7
sql += "'a', "; //8
sql += "'abcdfghijk', "; //9
sql += "TIME '13:15:45 pm', "; //10
sql += "DATE '00-10-31', "; //11
sql += "TIMESTAMP '13:15:45 10/31/2008', "; //12
sql += "DATETIME '13:15:45 10/31/2008', "; //13
sql += "B'1', "; //14
sql += "B'111', "; //15
sql += "123456789, "; //16
sql += "'qwerty'"; //17
sql += ")";
TestCases.ExecuteSQL(sql, conn);
sql = "select * from t";
using (OdbcCommand cmd = new OdbcCommand(sql, conn))
{
OdbcDataReader reader = cmd.ExecuteReader();
while (reader.Read()) //only one row will be available
{
Assert.IsTrue(reader.GetInt32(0) == 1);
Assert.IsTrue(reader.GetInt16(1) == 11);
Assert.IsTrue(reader.GetInt32(2) == 111);
Assert.IsTrue(reader.GetInt64(3) == 1111);
Assert.IsTrue(reader.GetDecimal(4) == (decimal)1.1);
Assert.IsTrue(reader.GetFloat(5) == (float)1.11); //"Single"
Assert.IsTrue(reader.GetDecimal(6) == (decimal)1.111);
Assert.IsTrue(reader.GetDouble(7) == (double)1.1111);
//We use GetString() because GetChar() is not supported or System.Data.OleDb.
//http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdatareader.getchar
Assert.IsTrue(reader.GetString(8) == "a"); //"String" ("Char" in CUBRID)
Assert.IsTrue(reader.GetString(9) == "abcdfghijk"); //"String" ("String in CUBRID)
//GetGateTime cannot cast just the time value in a DateTime object, so we use TimeSpan
//Assert.IsTrue(reader.GetTimeSpan(10) == new TimeSpan(13, 15, 45));//"TimeSpan"
Assert.IsTrue(reader.GetDateTime(11) == new DateTime(2000, 10, 31)); //"DateTime"
Assert.IsTrue(reader.GetDateTime(12) == new DateTime(2008, 10, 31, 13, 15, 45)); //"DateTime"
// Console.WriteLine(reader.GetValue(13));
// Assert.IsTrue(reader.GetDateTime(13) == new DateTime(2008, 10, 31, 13, 15, 45)); //"DateTime"
//The GetByte() method does not perform any conversions and the driver does not give tha data as Byte
//http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbdatareader.getbyte
//Use GetValue() or GetBytes() methods to retrieve BIT coulumn value
// Assert.IsTrue((reader.GetValue(14) as byte[])[0] == (byte)0); //"Byte[]" ("bit(1)" in CUBRID)
//Or
Assert.IsTrue(reader.GetString(14) == "80");
Assert.IsTrue(reader.GetString(15) == "E0");
Assert.IsTrue(reader.GetString(16) == "123456789.0000000000000000");
Assert.IsTrue(reader.GetString(17) == "qwerty"); //"String"
}
}
TestCases.ExecuteSQL("drop table if exists t", conn);
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_ReconcReport.
/// </summary>
/// <param name="CreationTime"></param>
/// <param name="UserID"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewReconcReport(DateTime CreationTime, int UserID)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_ReconcReport\"(\"c_u_CreationTime\",\"c_r_User\") VALUES(?,?)";
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
cmd.Parameters.Add("c_u_CreationTime", OdbcType.DateTime);
cmd.Parameters["c_u_CreationTime"].Value = (object)CreationTime;
cmd.Parameters.Add("c_r_User", OdbcType.Int);
cmd.Parameters["c_r_User"].Value = (object)UserID;
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);
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_EASfileAttachment.
/// </summary>
/// <param name="Filename"></param>
/// <param name="Comment"></param>
/// <param name="UploadDate"></param>
/// <param name="EntAssignmentSetID"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewEASfileAttachment(string Filename, string Comment, DateTime UploadDate, int EntAssignmentSetID)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_EASfileAttachment\"(\"c_u_Filename\",\"c_u_Comment\",\"c_u_UploadDate\",\"c_r_EntAssignmentSet\") VALUES(?,?,?,?)";
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
if (Filename == null)
{
throw new Exception("Filename must not be null!");
}
cmd.Parameters.Add("c_u_Filename", OdbcType.NVarChar, 256);
cmd.Parameters["c_u_Filename"].Value = (Filename != null ? (object)Filename : DBNull.Value);
if (Comment == null)
{
throw new Exception("Comment must not be null!");
}
cmd.Parameters.Add("c_u_Comment", OdbcType.NVarChar, 1024);
cmd.Parameters["c_u_Comment"].Value = (Comment != null ? (object)Comment : DBNull.Value);
cmd.Parameters.Add("c_u_UploadDate", OdbcType.DateTime);
cmd.Parameters["c_u_UploadDate"].Value = HELPERS.SetSafeDBDate(UploadDate);
cmd.Parameters.Add("c_r_EntAssignmentSet", OdbcType.Int);
cmd.Parameters["c_r_EntAssignmentSet"].Value = (object)EntAssignmentSetID;
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) : dri.GetInt32(0)));
dri.Close();
if (rv == 0)
{
throw new Exception("Insert operation failed!");
}
dri.Dispose();
cmd.Dispose();
DBClose();
return(rv);
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_SAPChangeManagementEvent.
/// </summary>
/// <param name="TcodeAssignmentSetID"></param>
/// <param name="EventType"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewSAPChangeManagementEvent(int TcodeAssignmentSetID, string EventType)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_SAPChangeManagementEvent\"(\"c_r_TcodeAssignmentSet\",\"c_u_EventType\") VALUES(?,?)";
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
cmd.Parameters.Add("c_r_TcodeAssignmentSet", OdbcType.Int);
cmd.Parameters["c_r_TcodeAssignmentSet"].Value = (object)TcodeAssignmentSetID;
if (EventType == null)
{
cmd.Dispose(); DBClose(); throw new Exception("EventType must not be null!");
}
cmd.Parameters.Add("c_u_EventType", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_EventType"].Value = (EventType != null ? (object)EventType : DBNull.Value);
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);
}
public static void Test_Various_DataTypes2()
{
TestCases.ExecuteSQL("drop table if exists t", conn);
string sql = "create table t(";
sql += "c_integer_ai integer AUTO_INCREMENT, "; //1
sql += "c_smallint smallint, "; //2
sql += "c_integer integer, "; //3
sql += "c_bigint bigint, "; //4
sql += "c_numeric numeric(15,1), "; //5
sql += "c_float float, "; //6
sql += "c_decimal decimal(15,3), "; //7
sql += "c_double double, "; //8
sql += "c_char char, "; //9
sql += "c_varchar varchar(4096), "; //10
sql += "c_time time, "; //11
sql += "c_date date, "; //12
sql += "c_timestamp timestamp, "; //13
sql += "c_datetime datetime, "; //14
sql += "c_bit bit(1), "; //15
sql += "c_varbit bit varying(4096), "; //16
sql += "c_monetary monetary, "; //17
sql += "c_string string"; //18
sql += ")";
TestCases.ExecuteSQL(sql, conn);
using (OdbcCommand cmd = new OdbcCommand(null, conn))
{
sql = "insert into t values(?, ?, ?, ?, ?, ?,";
sql += "?, ?, ?, ?, ?, ?,";
sql += "?, ?, ?, ?, ?, ?";
sql += ")";
OdbcParameter p1 = new OdbcParameter("?p1", OdbcType.Int);
p1.Value = 1;
cmd.Parameters.Add(p1);
OdbcParameter p2 = new OdbcParameter("?p2", OdbcType.SmallInt);
p2.Value = 11;
cmd.Parameters.Add(p2);
OdbcParameter p3 = new OdbcParameter("?p3", OdbcType.Int);
p3.Value = 111;
cmd.Parameters.Add(p3);
OdbcParameter p4 = new OdbcParameter("?p4", OdbcType.BigInt);
p4.Value = 1111;
cmd.Parameters.Add(p4);
OdbcParameter p5 = new OdbcParameter("?p5", OdbcType.Numeric);
p5.Value = 1.1;
cmd.Parameters.Add(p5);
OdbcParameter p6 = new OdbcParameter("?p6", OdbcType.Double);
p6.Value = 1.11;
cmd.Parameters.Add(p6);
OdbcParameter p7 = new OdbcParameter("?p7", OdbcType.Decimal);
p7.Value = 1.111;
cmd.Parameters.Add(p7);
OdbcParameter p8 = new OdbcParameter("?p8", OdbcType.Double);
p8.Value = 1.1111;
cmd.Parameters.Add(p8);
OdbcParameter p9 = new OdbcParameter("?p9", OdbcType.Char);
p9.Value = 'a';
cmd.Parameters.Add(p9);
OdbcParameter p10 = new OdbcParameter("?p10", OdbcType.VarChar);
p10.Value = "abcdfghijk";
cmd.Parameters.Add(p10);
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
sql = "select * from t";
using (OdbcCommand cmd = new OdbcCommand(sql, conn))
{
OdbcDataReader reader = cmd.ExecuteReader();
while (reader.Read()) //only one row will be available
{
Assert.IsTrue(reader.GetInt32(0) == 1);
Assert.IsTrue(reader.GetInt16(1) == 11);
Assert.IsTrue(reader.GetInt32(2) == 111);
Assert.IsTrue(reader.GetInt64(3) == 1111);
Assert.IsTrue(reader.GetDecimal(4) == (decimal)1.1);
Assert.IsTrue(reader.GetFloat(5) == (float)1.11); //"Single"
Assert.IsTrue(reader.GetDecimal(6) == (decimal)1.111);
Assert.IsTrue(reader.GetDouble(7) == (double)1.1111);
Assert.IsTrue(reader.GetString(8) == "a"); //"String" ("Char" in CUBRID)
Assert.IsTrue(reader.GetString(9) == "abcdfghijk"); //"String" ("String in CUBRID)
}
}
TestCases.ExecuteSQL("drop table if exists t", conn);
}
/// <summary>
/// Inserta un Regitro
/// </summary>
/// <typeparam name="T">Objeto entidad (clase)</typeparam>
/// <param name="objeto">Etnidad</param>
/// <returns>Mayor a cero</returns>
public int Insertar <T>(T objeto)
{
long ultimoId = 0;
ObjetoTransformar obj = ProcesarEntidad(objeto.GetType());
int filasAfectadas = 0;
Type tipo = objeto.GetType();
var propiedades = tipo.GetProperties();
var campos = new StringBuilder();
var variables = new StringBuilder();
for (int i = 0; i < propiedades.Length; i++)
{
if (propiedades[i].Name != "Nuevo" &&
propiedades[i].Name != "Borrado" && propiedades[i].Name != "Modificado" && propiedades[i].Name != "TimeStamp")
{
object valor = propiedades[i].GetValue(objeto, null);
CamposTabla campo = obj.ListaCampos.Find(p => p.NombreEntidad == propiedades[i].Name);
if (campo != null)
{
campo.ValorEntidad = valor;
}
}
}
if (obj.ListaCampos.Count > 0)
{
for (int i = 0; i < obj.ListaCampos.Count; i++)
{
string nombreCampo = obj.ListaCampos[i].NombreColumna;
if (nombreCampo != "ID")
{
campos.Append(obj.ListaCampos[i].NombreColumna);
variables.Append("?");
if (i < obj.ListaCampos.Count - 1)
{
campos.Append(", ");
variables.Append(", ");
}
}
}
}
//en este momento ya tenemos los valores de los elementos y la lista de elementos en sql
string conString = WebConfigurationManager.
ConnectionStrings[Utiles.CNS].ConnectionString;
using (OdbcConnection con = new OdbcConnection(conString))
using (OdbcCommand cmd = new OdbcCommand(string.Format("INSERT INTO {0} ({1}) VALUES ({2})", obj.NombreTabla, campos, variables), con))
{
con.Open();
foreach (CamposTabla campo in obj.ListaCampos)
{
if (campo.NombreColumna != "ID")
{
cmd.Parameters.Add("@" + campo.NombreColumna, ObtenerTipoODBC(campo)).Value = campo.ValorEntidad;
}
}
cmd.ExecuteNonQuery();
//pruebas de last insert id
cmd.CommandText = "SELECT LAST_INSERT_ID()";
OdbcDataReader reader = cmd.ExecuteReader();
if (reader != null && reader.Read())
{
ultimoId = reader.GetInt64(0);
}
filasAfectadas++;
con.Close();
}
return(Convert.ToInt32(ultimoId));
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_EntAssignmentSet.
/// </summary>
/// <param name="SubProcessID"></param>
/// <param name="UserID"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewEntAssignmentSet(int SubProcessID, int UserID)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_EntAssignmentSet\"(\"c_r_SubProcess\",\"c_r_User\") VALUES(?,?)";
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
cmd.Parameters.Add("c_r_SubProcess", OdbcType.Int);
cmd.Parameters["c_r_SubProcess"].Value = (object)SubProcessID;
cmd.Parameters.Add("c_r_User", OdbcType.Int);
cmd.Parameters["c_r_User"].Value = (object)UserID;
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);
}
/// <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);
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_Entitlement.
/// </summary>
/// <param name="StandardActivity"></param>
/// <param name="RoleType"></param>
/// <param name="System"></param>
/// <param name="Platform"></param>
/// <param name="EntitlementName"></param>
/// <param name="EntitlementValue"></param>
/// <param name="Application"></param>
/// <param name="CHECKSUM"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewEntitlement(string StandardActivity, string RoleType, string System, string Platform, string EntitlementName, string EntitlementValue, string Application, string CHECKSUM)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_Entitlement\"(\"c_u_StandardActivity\",\"c_u_RoleType\",\"c_u_System\",\"c_u_Platform\",\"c_u_EntitlementName\",\"c_u_EntitlementValue\",\"c_u_Application\",\"c_u_CHECKSUM\") VALUES(?,?,?,?,?,?,?,?)";
if (_dbConnection.Driver.ToLower().StartsWith("sqlsrv"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
if (StandardActivity == null)
{
throw new Exception("StandardActivity must not be null!");
}
cmd.Parameters.Add("c_u_StandardActivity", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_StandardActivity"].Value = (StandardActivity != null ? (object)StandardActivity : DBNull.Value);
if (RoleType == null)
{
throw new Exception("RoleType must not be null!");
}
cmd.Parameters.Add("c_u_RoleType", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_RoleType"].Value = (RoleType != null ? (object)RoleType : DBNull.Value);
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);
if (EntitlementName == null)
{
throw new Exception("EntitlementName must not be null!");
}
cmd.Parameters.Add("c_u_EntitlementName", OdbcType.NVarChar, 100);
cmd.Parameters["c_u_EntitlementName"].Value = (EntitlementName != null ? (object)EntitlementName : DBNull.Value);
if (EntitlementValue == null)
{
throw new Exception("EntitlementValue must not be null!");
}
cmd.Parameters.Add("c_u_EntitlementValue", OdbcType.NVarChar, 1024);
cmd.Parameters["c_u_EntitlementValue"].Value = (EntitlementValue != null ? (object)EntitlementValue : DBNull.Value);
if (Application == null)
{
throw new Exception("Application must not be null!");
}
cmd.Parameters.Add("c_u_Application", OdbcType.NVarChar, 50);
cmd.Parameters["c_u_Application"].Value = (Application != null ? (object)Application : DBNull.Value);
if (CHECKSUM == null)
{
throw new Exception("CHECKSUM must not be null!");
}
cmd.Parameters.Add("c_u_CHECKSUM", OdbcType.NVarChar, 100);
cmd.Parameters["c_u_CHECKSUM"].Value = (CHECKSUM != null ? (object)CHECKSUM : 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);
}
/// <summary>
///
/// insert a row in table t_RBSR_AUFW_u_ReconcDiffItem.
/// </summary>
/// <param name="DiffType"></param>
/// <param name="ReconcReportID"></param>
/// <param name="DiffObject"></param>
/// <returns>The integer ID of the new object.</returns>
public int NewReconcDiffItem(string DiffType, int ReconcReportID, string DiffObject)
{
int rv = 0;
DBConnect();
OdbcCommand cmd = _dbConnection.CreateCommand();
cmd.CommandText = "INSERT INTO \"t_RBSR_AUFW_u_ReconcDiffItem\"(\"c_u_DiffType\",\"c_r_ReconcReport\",\"c_u_DiffObject\") VALUES(?,?,?)";
if (_dbConnection.Driver.ToLower().StartsWith("sql"))
{
cmd.CommandText += " SELECT convert(int,SCOPE_IDENTITY())";
}
if (DiffType == null)
{
throw new Exception("DiffType must not be null!");
}
cmd.Parameters.Add("c_u_DiffType", OdbcType.NVarChar, 3);
cmd.Parameters["c_u_DiffType"].Value = (DiffType != null ? (object)DiffType : DBNull.Value);
cmd.Parameters.Add("c_r_ReconcReport", OdbcType.Int);
cmd.Parameters["c_r_ReconcReport"].Value = (object)ReconcReportID;
if (DiffObject == null)
{
throw new Exception("DiffObject must not be null!");
}
cmd.Parameters.Add("c_u_DiffObject", OdbcType.NVarChar, 4);
cmd.Parameters["c_u_DiffObject"].Value = (DiffObject != null ? (object)DiffObject : 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);
}
