public DataTable Listar()
{
var conn = Conexao.GetInstancia();
conn.Open();
const string mSql = @"Select p.ID_PESSOA as ""Cod."", p.NOME_PESSOA as ""Nome"", p.EMAIL_PESSOA as ""Email"", p.TELEFONE_PESSOA as ""Telefone"",
p.DATA_NASCIMENTO_PESSOA as ""Data de nascimento"", c.DATA_CADASTRO_CLIENTE as ""Data de cadastro""
from PESSOA p, CLIENTE c where c.ID_PESSOA = p.ID_PESSOA";
var cmd = new FbCommand(mSql, conn);
try
{
var dataAdapter = new FbDataAdapter(cmd);
var dataTable = new DataTable();
dataAdapter.Fill(dataTable);
dataAdapter.Dispose();
return(dataTable);
}
finally
{
cmd.Dispose();
conn.Close();
}
}
public void UpdateClobTest()
{
string sql = "select * from TEST where int_field = @int_field";
FbTransaction transaction = this.Connection.BeginTransaction();
FbCommand command = new FbCommand(sql, Connection, transaction);
FbDataAdapter adapter = new FbDataAdapter(command);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
FbCommandBuilder builder = new FbCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0]["CLOB_FIELD"] = "ONE THOUSAND";
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
}
public static DataTable GenericSelect(string sql, string connectionstring)
{
FbConnection cn = null;
FbCommand cmd = null;
DataSet ds = new DataSet();
FbDataAdapter da = null;
DataTable result = null;
try
{
cn = new FbConnection(connectionstring);
cn.Open();
cmd = new FbCommand(sql, cn);
da = new FbDataAdapter(cmd);
da.Fill(ds);
result = ds.Tables[0];
}
catch (FbException ex)
{
ExceptionHelper.Log(ex);
}
finally {
da.Dispose();
cmd.Dispose();
cn.Dispose();
}
//handle nulls?
return(result);
}
public virtual DataTable GetSchema(FbConnection connection, object[] restrictions)
{
restrictions = this.ParseRestrictions(restrictions);
FbCommand command = this.BuildCommand(connection, restrictions);
FbDataAdapter adapter = new FbDataAdapter(command);
DataSet dataSet = new DataSet(this.schemaName);
try
{
adapter.Fill(dataSet, this.schemaName);
}
catch (Exception ex)
{
throw new FbException(ex.Message);
}
finally
{
adapter.Dispose();
command.Dispose();
}
TrimStringFields(dataSet.Tables[this.schemaName]);
return(this.ProcessResult(dataSet.Tables[this.schemaName]));
}
public virtual DataTable GetSchema(FbConnection connection, object[] restrictions)
{
restrictions = this.ParseRestrictions(restrictions);
FbCommand command = this.BuildCommand(connection, restrictions);
FbDataAdapter adapter = new FbDataAdapter(command);
DataSet dataSet = new DataSet(this.schemaName);
try
{
adapter.Fill(dataSet, this.schemaName);
}
catch (Exception ex)
{
throw new FbException(ex.Message);
}
finally
{
adapter.Dispose();
command.Dispose();
}
TrimStringFields(dataSet.Tables[this.schemaName]);
return this.ProcessResult(dataSet.Tables[this.schemaName]);
}
private void DisposeAll()
{
//Prevent Dispos if have transaction
if (_tran != null && _tran.Connection != null)
{
return;
}
if (_tran != null)
{
_tran.Dispose();
_tran = null;
}
if (_com != null)
{
_com.Dispose();
_com = null;
}
if (_con != null)
{
_con.Dispose();
_con = null;
}
if (_adapter != null)
{
_adapter.Dispose();
_adapter = null;
}
}
public void DeleteTest()
{
var sql = "select * from TEST where int_field = @int_field";
var transaction = Connection.BeginTransaction();
var command = new FbCommand(sql, Connection, transaction);
var adapter = new FbDataAdapter(command);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 10;
var builder = new FbCommandBuilder(adapter);
var ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0].Delete();
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
}
/// <summary>
/// 执行SQL,返回读取到的数据
/// </summary>
public DataTable getSqlToDt()
{
DataTable dt = new DataTable();
string sql = "SELECT a.SHOP, a.EQID, a.EQIP, a.UPDATETIME FROM EQDATA a";
if (ConnectTest())
{
try
{
MyOpen();
FbCommand cmd = new FbCommand(sql, _conn);
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 60;
FbDataAdapter fbAda = new FbDataAdapter(cmd);
fbAda.Fill(dt);
fbAda.Dispose();
cmd.Dispose();
MyClose();
}
catch (Exception ex)
{
MessageBox.Show(string.Format($"读取失败，原因如下：{ex.Message}"));
}
}
return(dt);
}
public void UpdateTimeStampTest()
{
var sql = "select * from TEST where int_field = @int_field";
var transaction = Connection.BeginTransaction();
var command = new FbCommand(sql, Connection, transaction);
var adapter = new FbDataAdapter(command);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
var builder = new FbCommandBuilder(adapter);
var ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
var dtValue = DateTime.Now;
ds.Tables["TEST"].Rows[0]["TIMESTAMP_FIELD"] = dtValue;
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
transaction = Connection.BeginTransaction();
sql = "SELECT timestamp_field FROM TEST WHERE int_field = @int_field";
command = new FbCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
var val = (DateTime)command.ExecuteScalar();
transaction.Commit();
Assert.AreEqual(dtValue.Day, val.Day, "timestamp_field has not correct day");
Assert.AreEqual(dtValue.Month, val.Month, "timestamp_field has not correct month");
Assert.AreEqual(dtValue.Year, val.Year, "timestamp_field has not correct year");
Assert.AreEqual(dtValue.Hour, val.Hour, "timestamp_field has not correct hour");
Assert.AreEqual(dtValue.Minute, val.Minute, "timestamp_field has not correct minute");
Assert.AreEqual(dtValue.Second, val.Second, "timestamp_field has not correct second");
}
public void UpdateDateTest()
{
string sql = "select * from TEST where int_field = @int_field";
FbTransaction transaction = this.Connection.BeginTransaction();
FbCommand command = new FbCommand(sql, Connection, transaction);
FbDataAdapter adapter = new FbDataAdapter(command);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
FbCommandBuilder builder = new FbCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
DateTime dtValue = DateTime.Now;
ds.Tables["TEST"].Rows[0]["DATE_FIELD"] = dtValue;
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
transaction = Connection.BeginTransaction();
sql = "SELECT date_field FROM TEST WHERE int_field = @int_field";
command = new FbCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
DateTime val = (DateTime)command.ExecuteScalar();
transaction.Commit();
Assert.AreEqual(dtValue.Day, val.Day, "date_field has not correct day");
Assert.AreEqual(dtValue.Month, val.Month, "date_field has not correct month");
Assert.AreEqual(dtValue.Year, val.Year, "date_field has not correct year");
}
public void UpdateTimeTest()
{
string sql = "select * from TEST where int_field = @int_field";
FbTransaction transaction = this.Connection.BeginTransaction();
FbCommand command = new FbCommand(sql, Connection, transaction);
FbDataAdapter adapter = new FbDataAdapter(command);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
FbCommandBuilder builder = new FbCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
TimeSpan dtValue = new TimeSpan(5, 6, 7);
ds.Tables["TEST"].Rows[0]["TIME_FIELD"] = dtValue;
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
transaction = Connection.BeginTransaction();
sql = "SELECT time_field FROM TEST WHERE int_field = @int_field";
command = new FbCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
TimeSpan val = (TimeSpan)command.ExecuteScalar();
transaction.Commit();
Assert.AreEqual(dtValue.Hours, val.Hours, "time_field has not correct hour");
Assert.AreEqual(dtValue.Minutes, val.Minutes, "time_field has not correct minute");
Assert.AreEqual(dtValue.Seconds, val.Seconds, "time_field has not correct second");
}
public void FillTest()
{
FbTransaction transaction = this.Connection.BeginTransaction();
FbCommand command = new FbCommand("select * from TEST", Connection, transaction);
FbDataAdapter adapter = new FbDataAdapter(command);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
FbCommandBuilder builder = new FbCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(100, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
Console.WriteLine();
Console.WriteLine("DataAdapter - Fill Method - Test");
foreach (DataTable table in ds.Tables)
{
foreach (DataColumn col in table.Columns)
{
Console.Write(col.ColumnName + "\t\t");
}
Console.WriteLine();
foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
Console.Write(row[i] + "\t\t");
}
Console.WriteLine("");
}
}
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
}
public void FillMultipleWithImplicitTransactionTest()
{
FbCommand command = new FbCommand("select * from TEST", Connection);
FbDataAdapter adapter = new FbDataAdapter(command);
FbCommandBuilder builder = new FbCommandBuilder(adapter);
DataSet ds1 = new DataSet();
DataSet ds2 = new DataSet();
adapter.Fill(ds1, "TEST");
adapter.Fill(ds2, "TEST");
Assert.AreEqual(100, ds1.Tables["TEST"].Rows.Count, "Incorrect row count (ds1)");
Assert.AreEqual(100, ds2.Tables["TEST"].Rows.Count, "Incorrect row count (ds2)");
adapter.Dispose();
builder.Dispose();
command.Dispose();
}
public void UpdateSmallIntTest()
{
string sql = "select * from TEST where int_field = @int_field";
FbTransaction transaction = Connection.BeginTransaction();
FbCommand command = new FbCommand(sql, Connection, transaction);
FbDataAdapter adapter = new FbDataAdapter(command);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
FbCommandBuilder builder = new FbCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0]["SMALLint_field"] = System.Int16.MaxValue;
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
transaction = Connection.BeginTransaction();
sql = "SELECT smallint_field FROM TEST WHERE int_field = @int_field";
command = new FbCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
short val = (short)command.ExecuteScalar();
transaction.Commit();
Assert.AreEqual(System.Int16.MaxValue, val, "smallint_field has not correct value");
}
public void UpdateFloatTest()
{
string sql = "select * from TEST where int_field = @int_field";
FbTransaction transaction = this.Connection.BeginTransaction();
FbCommand command = new FbCommand(sql, Connection, transaction);
FbDataAdapter adapter = new FbDataAdapter(command);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
FbCommandBuilder builder = new FbCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0]["FLOAT_FIELD"] = (float)100.20;
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
transaction = Connection.BeginTransaction();
sql = "SELECT float_field FROM TEST WHERE int_field = @int_field";
command = new FbCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
float val = (float)command.ExecuteScalar();
transaction.Commit();
Assert.AreEqual((float)100.20, val, "double_field has not correct value");
}
public void UpdateDecimalTest()
{
var sql = "select * from TEST where int_field = @int_field";
var transaction = Connection.BeginTransaction();
var command = new FbCommand(sql, Connection, transaction);
var adapter = new FbDataAdapter(command);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
var builder = new FbCommandBuilder(adapter);
var ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0]["DECIMAL_FIELD"] = System.Int32.MaxValue;
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
transaction = Connection.BeginTransaction();
sql = "SELECT decimal_field FROM TEST WHERE int_field = @int_field";
command = new FbCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
var val = (decimal)command.ExecuteScalar();
transaction.Commit();
Assert.AreEqual(System.Int32.MaxValue, val, "decimal_field has not correct value");
}
public void UpdateVarCharTest()
{
var sql = "select * from TEST where int_field = @int_field";
var transaction = Connection.BeginTransaction();
var command = new FbCommand(sql, Connection, transaction);
var adapter = new FbDataAdapter(command);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
var builder = new FbCommandBuilder(adapter);
var ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0]["VARCHAR_FIELD"] = "ONE VAR THOUSAND";
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
transaction = Connection.BeginTransaction();
sql = "SELECT varchar_field FROM TEST WHERE int_field = @int_field";
command = new FbCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
var val = (string)command.ExecuteScalar();
transaction.Commit();
Assert.AreEqual("ONE VAR THOUSAND", val.Trim(), "varchar_field has not correct value");
}
public void DataAdapterFillTest()
{
FbCommand command = new FbCommand("select * from TEST where DATE_FIELD <> ?", Connection);
FbDataAdapter adapter = new FbDataAdapter(command);
adapter.SelectCommand.Parameters.Add("@DATE_FIELD", FbDbType.Date, 4, "DATE_FIELD").Value = new DateTime(2003, 1, 5);
FbCommandBuilder builder = new FbCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
Assert.AreEqual(1, ds.Tables.Count);
Assert.Greater(ds.Tables[0].Rows.Count, 0);
Assert.Greater(ds.Tables[0].Columns.Count, 0);
}
public void FillTest()
{
var transaction = Connection.BeginTransaction();
var command = new FbCommand("select * from TEST", Connection, transaction);
var adapter = new FbDataAdapter(command);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
var builder = new FbCommandBuilder(adapter);
var ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(100, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
}
public void DataAdapterFillTest()
{
FbCommand command = new FbCommand("select * from TEST where DATE_FIELD = ?", Connection);
FbDataAdapter adapter = new FbDataAdapter(command);
adapter.SelectCommand.Parameters.Add("@DATE_FIELD", FbDbType.Date, 4, "DATE_FIELD").Value = new DateTime(2003, 1, 5);
FbCommandBuilder builder = new FbCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
TestContext.WriteLine();
TestContext.WriteLine("Implicit transactions - DataAdapter Fill Method - Test");
foreach (DataTable table in ds.Tables)
{
foreach (DataColumn col in table.Columns)
{
TestContext.Write(col.ColumnName + "\t\t");
}
TestContext.WriteLine();
foreach (DataRow row in table.Rows)
{
for (int i = 0; i < table.Columns.Count; i++)
{
TestContext.Write(row[i] + "\t\t");
}
TestContext.WriteLine("");
}
}
adapter.Dispose();
builder.Dispose();
command.Dispose();
}
public void InsertTest()
{
var transaction = Connection.BeginTransaction();
var command = new FbCommand("select * from TEST", Connection, transaction);
var adapter = new FbDataAdapter(command);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
var builder = new FbCommandBuilder(adapter);
var ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(100, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
var newRow = ds.Tables["TEST"].NewRow();
newRow["int_field"] = 101;
newRow["CHAR_FIELD"] = "ONE THOUSAND";
newRow["VARCHAR_FIELD"] = ":;,.{}`+^*[]\\!|@#$%&/()?_-<>";
newRow["BIGint_field"] = 100000;
newRow["SMALLint_field"] = 100;
newRow["DOUBLE_FIELD"] = 100.01;
newRow["NUMERIC_FIELD"] = 100.01;
newRow["DECIMAL_FIELD"] = 100.01;
newRow["DATE_FIELD"] = new DateTime(100, 10, 10);
newRow["TIME_FIELD"] = new TimeSpan(10, 10, 10);
newRow["TIMESTAMP_FIELD"] = new DateTime(100, 10, 10, 10, 10, 10, 10);
newRow["CLOB_FIELD"] = "ONE THOUSAND";
ds.Tables["TEST"].Rows.Add(newRow);
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
}
public void UpdateNumericTest()
{
string sql = "select * from TEST where int_field = @int_field";
FbTransaction transaction = this.Connection.BeginTransaction();
FbCommand command = new FbCommand(sql, Connection, transaction);
FbDataAdapter adapter = new FbDataAdapter(command);
adapter.SelectCommand.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
FbCommandBuilder builder = new FbCommandBuilder(adapter);
DataSet ds = new DataSet();
adapter.Fill(ds, "TEST");
Assert.AreEqual(1, ds.Tables["TEST"].Rows.Count, "Incorrect row count");
ds.Tables["TEST"].Rows[0]["NUMERIC_FIELD"] = System.Int32.MaxValue;
adapter.Update(ds, "TEST");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
transaction = Connection.BeginTransaction();
sql = "SELECT numeric_field FROM TEST WHERE int_field = @int_field";
command = new FbCommand(sql, Connection, transaction);
command.Parameters.Add("@int_field", FbDbType.Integer).Value = 1;
decimal val = (decimal)command.ExecuteScalar();
transaction.Commit();
Assert.AreEqual(System.Int32.MaxValue, val, "numeric_field has not correct value");
}
public DataTable GetSchema(FbConnection connection, string collectionName, string[] restrictions)
{
DataTable dataTable = new DataTable(collectionName);
FbCommand command = BuildCommand(connection, collectionName, ParseRestrictions(restrictions));
FbDataAdapter adapter = new FbDataAdapter(command);
try
{
adapter.Fill(dataTable);
}
catch (Exception ex)
{
throw new FbException(ex.Message);
}
finally
{
adapter.Dispose();
command.Dispose();
}
TrimStringFields(dataTable);
return(ProcessResult(dataTable));
}
public void FillMultipleTest()
{
FbTransaction transaction = this.Connection.BeginTransaction();
FbCommand command = new FbCommand("select * from TEST", Connection, transaction);
FbDataAdapter adapter = new FbDataAdapter(command);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
FbCommandBuilder builder = new FbCommandBuilder(adapter);
DataSet ds1 = new DataSet();
DataSet ds2 = new DataSet();
adapter.Fill(ds1, "TEST");
adapter.Fill(ds2, "TEST");
Assert.AreEqual(100, ds1.Tables["TEST"].Rows.Count, "Incorrect row count (ds1)");
Assert.AreEqual(100, ds2.Tables["TEST"].Rows.Count, "Incorrect row count (ds2)");
adapter.Dispose();
builder.Dispose();
command.Dispose();
transaction.Commit();
}
public void DisposeAll()
{
if (_com != null)
{
_com.Dispose();
_com = null;
}
if (_con != null)
{
_con.Dispose();
_con = null;
}
if (_adapter != null)
{
_adapter.Dispose();
_adapter = null;
}
_tran = null;
}
public override void TearDown()
{
_adapter.Dispose();
base.TearDown();
}
public override async Task TearDown()
{
_adapter.Dispose();
await base.TearDown();
}
