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 DataTable GetDataTable(string query)
{
var cnn = ConfigurationManager.AppSettings["PervasiveSQLClient"];
PsqlConnection DBConn = new PsqlConnection(cnn.ToString());
try
{
DataTable dt = new DataTable();
// Open the connection
DBConn.Open();
PsqlDataAdapter da = new PsqlDataAdapter();
da.SelectCommand = new PsqlCommand(query, DBConn);
da.Fill(dt);
//Console.WriteLine("Connection Successful!");
return(dt);
}
catch (PsqlException ex)
{
// Connection failed
Console.WriteLine(ex.Message);
return(null);
}
finally
{
DBConn.Close();
}
}
public static DataSet getDataSet(string sSQL, string sTable, PsqlConnection conn)
{
DataSet ds = new DataSet();
// create a data adapter
PsqlDataAdapter da = new PsqlDataAdapter(sSQL, conn);
da.Fill(ds, sTable);
return (ds);
}
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 static DataTable GetDataTable1proc(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(":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;
PsqlDataAdapter da = new PsqlDataAdapter(comm);
var dt = new DataTable();
da.Fill(dt);
return(dt);
}
catch (PsqlException ex)
{
// Connection failed
Console.WriteLine(ex.Message);
return(null);
}
finally
{
DBConn.Close();
}
}
private void BusinessUnitTrackingSetup_Load(object sender, EventArgs e)
{
BindingSource bsSource = new BindingSource();
string sSql = "select * from SOLMBS order by UnitID";
dsUnits = new DataSet();
da = new PsqlDataAdapter(sSql, oConn);
da.Fill(dsUnits, "Unit");
bsSource.DataSource = dsUnits.Tables["Unit"];
bindingNavigator1.BindingSource = bsSource;
this.txtID.DataBindings.Add(new Binding("Text", bsSource, "UnitID", true));
this.txtUnitName.DataBindings.Add(new Binding("Text", bsSource, "UnitName", true));
this.txtUnitLeader.DataBindings.Add(new Binding("Text", bsSource, "UnitLeader", true));
this.txtDescription.DataBindings.Add(new Binding("Text", bsSource, "UnitDescription", true));
builItemList(false);
buildOperatorsList();
}
public static DataTable Query(string query, string database)
{
conn.ConnectionString = $"Server Name=localhost;Database Name={database};User ID=louis;";
using (var adapter = new PsqlDataAdapter(query, conn))
{
var table = new DataTable();
adapter.Fill(table);
return table;
}
}
public static DataSet Query(string table, string query, string database)
{
conn.ConnectionString = $"Server Name=localhost;Database Name={database};User ID=louis;";
using (var data = new PsqlDataAdapter("SELECT * FROM " + table + " WHERE " + query, conn))
{
var returnset = new DataSet();
data.Fill(returnset, table);
return returnset;
}
}
