private PsqlCommand CreateUpdateCommand(PsqlConnection oConnection, POHeader poHeader)
{
var commandText = "UPDATE PO_Header" +
" SET po_no = @po_no, NewJobNumber = @NewJobNumber," +
" activity = @activity, vendor_id = @vendor_id," +
" po_type = @po_type, release_date = @release_date," +
" cancelled_date = @cancelled_date, payment_amount = @payment_amount," +
" subtotal = @subtotal, tax = @tax," +
" total = @total, egm_amount = @egm_amount," +
" vpo_yes_no = @vpo_yes_no, UserID = @UserID," +
" LastModifiedDate = @LastModifiedDate, Community = @Community," +
" Product = @Product, Building = @Building," +
" Unit = @Unit, taxable_amount = @taxable_amount," +
" job_no = @job_no, eSubmittalDate = @eSubmittalDate," +
" ApprovePaymentDate = @ApprovePaymentDate, Invoice = @Invoice," +
" TaxRate = @TaxRate, eMeasurementPO = @eMeasurementPO," +
" SapphirePONumber = @SapphirePONumber, SapphireObjID = @SapphireObjID," +
" SapphireObjRID = @SapphireObjRID" +
" WHERE SapphireObjID = @SapphireObjID AND SapphireObjRID = @SapphireObjRID ";
var command = new PsqlCommand
{
Connection = oConnection,
CommandText = commandText
};
CreateCommandParameters(command, poHeader);
return(command);
}
public static PsqlCommand getDataCommand(string sSQL, PsqlConnection conn)
{
try
{
if (conn == null)
{
try
{
conn = new PsqlConnection(sConnStr);
conn.Open();
}
catch (PsqlException ex)
{
throw ex;
}
}
else if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
PsqlCommand cmdSQL = new PsqlCommand(sSQL, conn);
return(cmdSQL);
}
catch
{
return(null);
}
}
private void CreateCommandParameters(PsqlCommand command, POHeader poHeader)
{
command.Parameters.AddWithValue("@po_no", poHeader.po_no);
command.Parameters.AddWithValue("@NewJobNumber", poHeader.NewJobNumber);
command.Parameters.AddWithValue("@activity", poHeader.activity);
command.Parameters.AddWithValue("@vendor_id", poHeader.vendor_id);
command.Parameters.AddWithValue("@po_type", poHeader.po_type);
command.Parameters.AddWithValue("@release_date", poHeader.release_date);
command.Parameters.AddWithValue("@cancelled_date", poHeader.cancelled_date);
command.Parameters.AddWithValue("@payment_amount", poHeader.payment_amount);
command.Parameters.AddWithValue("@subtotal", poHeader.subtotal);
command.Parameters.AddWithValue("@tax", poHeader.tax);
command.Parameters.AddWithValue("@total", poHeader.total);
command.Parameters.AddWithValue("@egm_amount", poHeader.egm_amount);
command.Parameters.AddWithValue("@vpo_yes_no", poHeader.vpo_yes_no);
command.Parameters.AddWithValue("@UserID", poHeader.UserID);
command.Parameters.AddWithValue("@LastModifiedDate", poHeader.LastModifiedDate);
command.Parameters.AddWithValue("@Community", poHeader.Community);
command.Parameters.AddWithValue("@Product", poHeader.Product);
command.Parameters.AddWithValue("@Building", poHeader.Building);
command.Parameters.AddWithValue("@Unit", poHeader.Unit);
command.Parameters.AddWithValue("@taxable_amount", poHeader.taxable_amount);
command.Parameters.AddWithValue("@job_no", poHeader.job_no);
command.Parameters.AddWithValue("@eSubmittalDate", poHeader.eSubmittalDate);
command.Parameters.AddWithValue("@ApprovePaymentDate", poHeader.ApprovePaymentDate);
command.Parameters.AddWithValue("@Invoice", poHeader.Invoice);
command.Parameters.AddWithValue("@TaxRate", poHeader.TaxRate);
command.Parameters.AddWithValue("@eMeasurementPO", poHeader.eMeasurementPO);
command.Parameters.AddWithValue("@SapphirePONumber", poHeader.SapphirePONumber);
command.Parameters.AddWithValue("@SapphireObjID", poHeader.SapphireObjID);
command.Parameters.AddWithValue("@SapphireObjRID", poHeader.SapphireObjRID);
}
public string ScalarStoredProcADO(string storedProc, Dictionary <string, string> parameters)
{
string result;
using (var oConnection = new PsqlConnection(_pervasiveDbContext))
{
var oCommand = new PsqlCommand(storedProc, oConnection)
{
CommandType = CommandType.StoredProcedure
};
foreach (var sKey in parameters.Keys)
{
oCommand.Parameters.AddWithValue(sKey, parameters[sKey]);
}
var oAdaptor = new PsqlDataAdapter(oCommand);
var dt = new DataTable();
try
{
oConnection.Open();
oAdaptor.Fill(dt);
oConnection.Close();
result = dt.Rows.Count > 0 ? Convert.ToString(dt.Rows[0][0]) : null;
}
catch (Exception exception)
{
//_logger.Error($"Database Failure Pervasive ADO.NET {exception}");
throw exception;
}
}
return(result);
}
public static string GetDataTable1(string query)
{
var cnn = ConfigurationManager.AppSettings["PervasiveSQLClient"];
PsqlConnection DBConn = new PsqlConnection(cnn.ToString());
PsqlCommand comm = new PsqlCommand();
try
{
DBConn.Open();
comm.Connection = DBConn;
comm.CommandText = "Update_Oper";
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Clear();
// comm.Parameters.Add()
comm.Parameters.Add(":id_oper", PsqlDbType.Integer);
comm.Parameters[comm.Parameters.Count - 1].Value = 1;
comm.Parameters.Add(":Id_Seccion", PsqlDbType.Integer);
comm.Parameters[comm.Parameters.Count - 1].Value = 1;
comm.ExecuteNonQuery();
return("OK");
}
catch (PsqlException ex)
{
// Connection failed
Console.WriteLine(ex.Message);
return(null);
}
finally
{
DBConn.Close();
}
}
static void Main()
{
try
{
string textFile = fileName;
Console.WriteLine("Loading File: " + textFile);
conn = new PsqlConnection(@"ServerDSN=DEMODATA");
conn.Open();
cmd = new PsqlCommand();
cmd.Connection = conn;
cmd.CommandText = @"create table texttable(textfile varchar(255),textdata longvarchar)";
//cmd.ExecuteNonQuery();
cmd.CommandText = @"insert into texttable values (?,?)";
cmd.Parameters.Add("@textfile", PsqlDbType.VarChar, 30);
cmd.Parameters.Add("@textdata", PsqlDbType.LongVarChar, 1000000);
Console.WriteLine("Loading File: " + textFile);
FileStream fs1 = new FileStream(textFile, FileMode.Open, FileAccess.Read);
StreamReader sr1 = new StreamReader(fs1);
string textData = "";
textData = sr1.ReadToEnd();
Console.WriteLine("TextBytes has length {0} bytes.", textData.Length);
//string textData = GetTextFile(textFile);
cmd.Parameters["@textfile"].Value = textFile;
cmd.Parameters["@textdata"].Value = textData;
cmd.CommandText = cmd.CommandText;
cmd.ExecuteNonQuery();
Console.WriteLine("Loaded {0} into texttable.", fileName);
cmd.CommandText = "select * from texttable";
PsqlDataReader dr = cmd.ExecuteReader();
if (dr.HasRows)
{
dr.Read();
textFile = @"Output.txt";
StreamWriter sw = new StreamWriter(textFile);
sw.Write(dr[1].ToString());
Console.WriteLine("TextFile: {0}.\nTextData written to {1}", dr[0].ToString(), textFile);
}
}
catch (PsqlException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
conn.Close();
}
}
public string ScalarQueryStringADO(string queryString)
{
var result = "";
using (var oConnection = new PsqlConnection(_pervasiveDbContext))
{
oConnection.Open();
var oCommand = new PsqlCommand(queryString, oConnection);
result = Convert.ToString(oCommand.ExecuteScalar());
oConnection.Close();
}
return(result);
}
public void NonQueryPervasiveADO(string queryString)
{
try
{
using (var oConnection = new PsqlConnection(_pervasiveDbContext))
{
oConnection.Open();
var oCommand = new PsqlCommand(queryString, oConnection);
oCommand.ExecuteNonQuery();
oConnection.Close();
}
}
catch (Exception exception)
{
//_logger.Error($"Database Failure Pervasive ADO.NET {queryString} {exception.InnerException}");
throw exception;
}
}
private PsqlCommand CreateInsertStatement(PsqlConnection oConnection, POHeader poHeader)
{
var commandText = "INSERT INTO PO_Header (po_no, NewJobNumber, activity, vendor_id, po_type, release_date, cancelled_date, payment_amount," +
"subtotal, tax, total, egm_amount, vpo_yes_no, UserID, LastModifiedDate, Community, Product, Building, Unit," +
"taxable_amount, job_no, eSubmittalDate, ApprovePaymentDate, Invoice, TaxRate, eMeasurementPO, SapphirePONumber, SapphireObjID," +
"SapphireObjRID)" +
"VALUES (@po_no,@NewJobNumber, @activity, @vendor_id, @po_type, @release_date, @cancelled_date, @payment_amount,@subtotal, @tax," +
"@total, @egm_amount,@vpo_yes_no, @UserID, @LastModifiedDate, @Community,@Product, @Building,@Unit, @taxable_amount," +
"@job_no, @eSubmittalDate, @ApprovePaymentDate, @Invoice,@TaxRate, @eMeasurementPO, @SapphirePONumber, @SapphireObjID, @SapphireObjRID)";
var command = new PsqlCommand
{
Connection = oConnection,
CommandText = commandText
};
CreateCommandParameters(command, poHeader);
return(command);
}
public void LoadCadastro()
{
PsqlConnection psqlConnection = new PsqlConnection(Connection);
try
{
psqlConnection.Open();
PsqlCommand command = new PsqlCommand();
string sql = "select codigobtr as Codigo, razsoc as Razao, cnpjcpf as CNPJ, inscest as IE from prg_empresa_btr order by codigobtr";
command.CommandText = sql;
command.Connection = psqlConnection;
PsqlDataAdapter adapter = new PsqlDataAdapter(command);
DataSet dataSet = new DataSet("Prosoft");
adapter.Fill(dataSet, "Empresas");
Empresas = new List <Empresas>();
foreach (DataRow row in dataSet.Tables["Empresas"].Rows)
{
Empresas.Add(new Empresas
{
Codigo = ((string)row.ItemArray[0]).TrimEnd(),
Razao = ((string)row.ItemArray[1]).TrimEnd(),
CNPJ = ((string)row.ItemArray[2]).TrimEnd(),
IE = ((string)row.ItemArray[3]).TrimEnd()
});
}
psqlConnection.Close();
}
catch (PsqlException ex)
{
}
catch (Exception ex)
{
}
finally
{
if (psqlConnection.State == System.Data.ConnectionState.Open)
{
psqlConnection.Close();
}
}
}
public void DoQuery(string query, int rangeCount = 500)
{
bool successful = false;
int retries = 3;
int newTimeout=timeout;
PsqlDataAdapter dataAdapter = new PsqlDataAdapter();
PsqlCommand DBCmd = new PsqlCommand(query, DBConn);
while (!successful && 0 < retries--)
{
DBCmd.CommandTimeout = newTimeout;
newTimeout += timeout;
dataAdapter.SelectCommand = DBCmd;
System.Data.DataSet dataSet = new System.Data.DataSet();
try
{
dataAdapter.Fill(dataSet);
dt = dataSet.Tables["Table"];
this.rows = new List<Dictionary<string, object>>();
this.rangeIndex = 0;
this.rangeCount = Math.Min(rangeCount, dt.Rows.Count);
foreach (DataRow dr in dt.Rows)
{
Dictionary<string, object> row;
row = new Dictionary<string, object>();
foreach (DataColumn col in dt.Columns)
{
row.Add(col.ColumnName, dr[col]);
}
rows.Add(row);
}
}
catch (Exception exc)
{
if (0 == retries)
{
Console.Error.WriteLine("*Exception: {0}", exc.Message);
Console.Error.WriteLine("*Query Failed: {0}", query);
}
}
}
}
public DataTable QueryPervasiveADO(string queryString)
{
var dataTable = new DataTable();
try
{
using (var oConnection = new PsqlConnection(_pervasiveDbContext))
{
oConnection.Open();
var oCommand = new PsqlCommand(queryString, oConnection);
var oAdapter = new PsqlDataAdapter(oCommand);
oAdapter.Fill(dataTable);
}
}
catch (Exception exception)
{
//_logger.Error($"Database Failure Pervasive ADO.NET {exception.InnerException}");
return(null);
}
return(dataTable);
}
public void StoredProcADO(string storedProc, Dictionary <string, string> parameters)
{
using (PsqlConnection oConnection = new PsqlConnection(_pervasiveDbContext))
{
PsqlCommand oCommand = new PsqlCommand(storedProc, oConnection);
oCommand.CommandType = CommandType.StoredProcedure;
foreach (String sKey in parameters.Keys)
{
oCommand.Parameters.AddWithValue(sKey, parameters[sKey]);
}
try
{
oConnection.Open();
oCommand.ExecuteNonQuery();
}
catch (PsqlException pSqlException)
{
//_logger.Error($"Database Failure Pervasive ADO.NET {pSqlException}");
throw pSqlException;
}
}
}
public static PsqlCommand getDataCommand(string sSql, PsqlConnection conn )
{
var cmdSQL = new PsqlCommand(sSql, conn);
return cmdSQL;
}
public static PsqlCommand getDataCommand(string sSQL, PsqlConnection conn)
{
try
{
if (conn == null)
{
try
{
conn = new PsqlConnection(sConnStr);
conn.Open();
}
catch (PsqlException ex)
{
throw ex;
}
}
else if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
PsqlCommand cmdSQL = new PsqlCommand(sSQL, conn);
return cmdSQL;
}
catch
{
return null;
}
}
