public DataTable FiltrarPorFechaControl(DateTime fechaControl)
{
try
{
this.AbrirConexion();
SqlCommand cmdFiltro = Conn.CreateCommand();
cmdFiltro.CommandType = CommandType.Text;
cmdFiltro.CommandText = "SELECT ca.fecha_control,ca.id_control,ca.rp,a.nombre_animal,a.id_tambo,t.nombre_tambo,c.primer_control,c.segundo_control,c.grasa_primercontrol,c.grasa_segundocontrol from Control_Animal ca inner join Animal a on ca.rp=a.rp inner join Control c on ca.id_control=c.id_control inner join Tambo t on a.id_tambo=t.id_tambo where ca.fecha_control like ('" + fechaControl + "%') and a.habilitado = 'true'";
cmdFiltro.ExecuteNonQuery();
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmdFiltro);
da.Fill(dt);
return(dt);
}
catch (SqlException sqe)
{
throw sqe;
}
catch (Exception ex)
{
Exception exepcionnueva = new Exception("Error al recuperar los datos de los controles", ex);
throw exepcionnueva;
}
finally
{
this.CerrarConexion();
}
}
public void Unknown([Values(true, false)] bool prepareCommand)
{
using (var cmd = Conn.CreateCommand())
{
cmd.CommandText = "Select :p1::timestamp, :p2::timestamp, :p3::int4";
cmd.Parameters.Add(new NpgsqlParameter("p1", NpgsqlDbType.Unknown)
{
Value = "2008-1-1"
});
cmd.Parameters.Add(new NpgsqlParameter {
ParameterName = "p2", Value = null
});
cmd.Parameters.Add(new NpgsqlParameter {
ParameterName = "p3", Value = "3"
});
if (prepareCommand)
{
cmd.Prepare();
Assert.AreEqual(NpgsqlDbType.Timestamp, cmd.Parameters[1].NpgsqlDbType); // Should be inferred by context
Assert.AreEqual(NpgsqlDbType.Text, cmd.Parameters[2].NpgsqlDbType); // Is inferred from the parametert value and not context
}
cmd.Parameters[1].Value = new DateTime(2008, 1, 1);
using (var reader = cmd.ExecuteReader())
{
reader.Read();
Assert.AreEqual(new DateTime(2008, 1, 1), reader.GetValue(0));
Assert.AreEqual(new DateTime(2008, 1, 1), reader.GetValue(1));
}
}
}
public int GetAlertOrdersCount3(int agencyNo)
{
var qTime = DateTime.Now;
Conn.Open();
var cmd = Conn.CreateCommand();
var sql = $"select * from hnotordc order by orderno";
cmd.CommandText = sql;
var rdr = cmd.ExecuteReader();
var count = 0;
var orderno = 0;
while (rdr.Read())
{
var repno = rdr.ReadInt("repno");
var neworderno = rdr.ReadInt("orderno");
if (Math.Round(repno / 10m, 0) == agencyNo && orderno != neworderno)
{
count++;
orderno = neworderno;
}
}
rdr.Close();
Conn.Close();
QueryDebugEnd(qTime, $"GetAlertOrdersCount");
return(count);
}
private DbCommand PrepareCommand(string sql, IDbParms parameters, CommandType CommandType)
{
DbCommand Command;
if (Trans != null)
{
//Command = _ProviderFactory.CreateCommand();
Command = Trans.Connection.CreateCommand();
Command.Transaction = Trans;
}
else
{
//Command = _ProviderFactory.CreateCommand();
Command = Conn.CreateCommand();
//Command = _ProviderFactory.CreateCommand();
//Command.Connection = Conn;
}
Command.CommandType = CommandType;
Command.CommandText = sql;
Command.CommandTimeout = CommandTimeOut;
//Command.SetParameters(parameters);
if (parameters != null)
{
SetParameters(Command, parameters);
}
//Command.Disposed += Command_Disposed;
return(Command);
}
public IDbCommand CommandProxy()
{
if (Conn != null)
{
if (Conn.State == ConnectionState.Open && _command == null)
{
_command = Conn.CreateCommand();
}
else if (Conn.State != ConnectionState.Open && _command != null)
{
Open();
}
else if (Conn.State != ConnectionState.Open && _command == null)
{
Open();
_command = Conn.CreateCommand();
}
}
else
{
Open();
_command = Conn.CreateCommand();
}
_command.CommandTimeout = 30;
return(_command);
}
private void ParameterizedSelectDecimalRoundTrip_Internal(bool prepare)
{
using (var command = Conn.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "SELECT :data";
NpgsqlParameter dataParameter = command.CreateParameter();
dataParameter.Direction = ParameterDirection.Input;
dataParameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Numeric;
dataParameter.ParameterName = "data";
command.Parameters.Add(dataParameter);
dataParameter.Value = 12345678.12345678;
using (var metrics = TestMetrics.Start(TestRunTime, true))
{
while (!metrics.TimesUp)
{
if (prepare)
{
command.Prepare();
}
var data2 = (decimal)command.ExecuteScalar();
metrics.IncrementIterations();
}
}
}
}
public SqlCommand SetContextInfo()
{
string _stage = "Starting";
SqlCommand _cmd = null;
try
{
if (ContextInfo != null)
{
//
_stage = "Creating command";
_cmd = Conn.CreateCommand();
string _sql = "set CONTEXT_INFO @C";
_cmd.CommandText = _sql;
//
_stage = "Creating SQL parameter";
SqlParameter _param = new SqlParameter();
_param.ParameterName = "@C";
_param.DbType = DbType.Binary;
_param.Size = 128;
_param.Value = ContextInfo;
//
_stage = "Adding parameter to command";
_cmd.Parameters.Add(_param);
}
}
catch (Exception ex)
{
throw new Exception($"[{this.GetType().Name}/{System.Reflection.MethodBase.GetCurrentMethod().Name}#{_stage}] {ex.Message}");
}
return(_cmd);
}
/// <summary>
/// Create view on edges
/// </summary>
/// <param name="tableSchema"> The Schema name of node table. Default(null or "") by "dbo".</param>
/// <param name="supperNodeName"> The name of supper node. </param>
/// <param name="edgeViewName"> The name of supper edge. </param>
/// <param name="edges"> The list of message of edges for merging.
/// The message is stored in tuple, containing (node table name, edge column name).</param>
/// <param name="edgeAttribute"> The attributes' names in the supper edge.</param>
/// <param name="attributeMapping"> User-supplied attribute-mapping.
/// Type is List<Tuple<string, List<Tuple<string, string, string>>>>.
/// That is, every attribute in supper edge is mapped into a list of attributes,
/// with the message of <node table name, edge column name, attribute name>
/// If one attribute in supper edge need to be mapped into all the user-supplied edges's same-name attributes,
/// user can pass a null or empty parameter of List<Tuple<string, string, string>>.
/// When "attributeMapping" is empty or null, the program will map the atrributes of supper edge
/// into all the same-name attributes of all the user-supplied edges.</param>
public void CreateEdgeView(string tableSchema, string supperNodeName, string edgeViewName,
List <Tuple <string, string> > edges, List <string> edgeAttribute,
List <Tuple <string, List <Tuple <string, string, string> > > > attributeMapping = null)
{
supperNode = supperNodeName;
var transaction = Conn.BeginTransaction();
var command = Conn.CreateCommand();
command.CommandTimeout = 0;
command.Transaction = transaction;
#if DEBUG
transaction.Commit();
#endif
try
{
CreateEdgeViewDecoder(tableSchema, edgeViewName, edges, edgeAttribute, command, attributeMapping);
updateEdgeViewMetaData(tableSchema, edgeViewName, command);
#if !DEBUG
transaction.Commit();
#endif
}
catch (Exception error)
{
throw new EdgeViewException(error.Message);
}
}
public void DataAdapterUpdateReturnValue2()
{
var cmd = Conn.CreateCommand();
var da = new NpgsqlDataAdapter("select * from tabled", Conn);
var cb = new NpgsqlCommandBuilder(da);
var ds = new DataSet();
da.Fill(ds);
//## Insert a new row with id = 1
ds.Tables[0].Rows.Add(new Object[] { 0.4, 0.5 });
da.Update(ds);
//## change id from 1 to 2
cmd.CommandText = "update tabled set field_float4 = 0.8";
cmd.ExecuteNonQuery();
//## change value to newvalue
ds.Tables[0].Rows[0][1] = 0.7;
//## update should fail, and make a DBConcurrencyException
var count = da.Update(ds);
//## count is 1, even if the isn't updated in the database
Assert.AreEqual(0, count);
}
//もしもSQLiteのDBファイルがなければテーブルを作成
private void CreateTable()
{
Conn.Open();
SQLiteCommand cmd = Conn.CreateCommand();
//テーブル作成
cmd.CommandText = @"
CREATE TABLE `category` (
`id` integer,
`name` text,
PRIMARY KEY(`id`)
);
";
cmd.ExecuteNonQuery();
//テーブル作成
cmd.CommandText = @"
CREATE TABLE `memo` (
`id` integer,
`category_id` integer,
`title` text,
`detail` text,
PRIMARY KEY(`id`)
);
";
cmd.ExecuteNonQuery();
Conn.Close();
}
public void ParameterizedPrepareManyFields()
{
using (var command = Conn.CreateCommand())
{
StringWriter sql = new StringWriter();
sql.WriteLine("SELECT");
sql.WriteLine(":p01, :p02, :p03, :p04, :p05, :p06, :p07, :p08, :p09, :p10,");
sql.WriteLine(":p11, :p12, :p13, :p14, :p15, :p16, :p17, :p18, :p19, :p20");
command.CommandText = sql.ToString();
for (int i = 0; i < 20; i++)
{
command.Parameters.AddWithValue(string.Format("p{0:00}", i + 1), NpgsqlDbType.Text, string.Format("String parameter value {0}", i + 1));
}
using (var metrics = TestMetrics.Start(TestRunTime, true))
{
while (!metrics.TimesUp)
{
command.Prepare();
metrics.IncrementIterations();
}
}
}
}
//もしもSQLiteのDBファイルがなければテーブルを作成
private void CreateTable()
{
Conn.Open();
SQLiteCommand cmd = Conn.CreateCommand();
//テーブル作成
cmd.CommandText = @"
CREATE TABLE `category` (
`id` INTEGER,
`name` TEXT,
PRIMARY KEY(`id`)
);
";
cmd.ExecuteNonQuery();
//テーブル作成
cmd.CommandText = @"
CREATE TABLE `question` (
`id` INTEGER,
`category_id` INTEGER,
`content` TEXT,
`correct` TEXT,
`incorrect1` TEXT,
`incorrect2` TEXT,
`incorrect3` TEXT,
`comment` TEXT,
PRIMARY KEY(`id`)
);
";
cmd.ExecuteNonQuery();
Conn.Close();
}
public void ParameterizedSelectMoneyArrayRoundTrip()
{
using (var command = Conn.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "SELECT :data";
decimal[] data = new decimal[1000];
for (int i = 0; i < 1000; i++)
{
data[i] = i;
}
NpgsqlParameter dataParameter = command.CreateParameter();
dataParameter.Direction = ParameterDirection.Input;
dataParameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Money | NpgsqlTypes.NpgsqlDbType.Array;
dataParameter.ParameterName = "data";
command.Parameters.Add(dataParameter);
dataParameter.Value = data;
command.Prepare();
using (var metrics = TestMetrics.Start(TestRunTime, true))
{
while (!metrics.TimesUp)
{
command.ExecuteScalar();
metrics.IncrementIterations();
}
}
}
}
public void NotificationAfterData()
{
var receivedNotification = false;
using (var cmd = Conn.CreateCommand())
{
cmd.CommandText = "LISTEN notifytest1";
cmd.ExecuteNonQuery();
Conn.Notification += (o, e) => receivedNotification = true;
cmd.CommandText = "SELECT generate_series(1,10000)";
using (var reader = cmd.ExecuteReader()) {
//After "notify notifytest1", a notification message will be sent to client,
//And so the notification message will stick with the last response message of "select generate_series(1,10000)" in Npgsql's tcp receiving buffer.
using (var connection = new NpgsqlConnection(ConnectionString)) {
connection.Open();
using (var command = connection.CreateCommand()) {
command.CommandText = "NOTIFY notifytest1";
command.ExecuteNonQuery();
}
}
Assert.IsTrue(reader.Read());
Assert.AreEqual(1, reader.GetValue(0));
}
Assert.That(ExecuteScalar("SELECT 1"), Is.EqualTo(1));
Assert.IsTrue(receivedNotification);
}
}
public void ParameterizedPreparedInsert()
{
using (var command = Conn.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "INSERT INTO data (field_text) values (:data)";
IDbDataParameter dataParameter = command.CreateParameter();
dataParameter.Direction = ParameterDirection.Input;
dataParameter.DbType = DbType.String;
dataParameter.ParameterName = "data";
command.Parameters.Add(dataParameter);
command.Prepare();
using (var metrics = TestMetrics.Start(TestRunTime, true))
{
while (!metrics.TimesUp)
{
dataParameter.Value = "yo";
command.ExecuteScalar();
metrics.IncrementIterations();
}
}
}
}
private void ParameterizedSelectByteaRoundTrip_Internal(bool prepare)
{
using (var command = Conn.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "SELECT :data";
byte[] data = new byte[100000];
for (int i = 0; i < data.Length; i++)
{
data[i] = (byte)(i % 255);
}
NpgsqlParameter dataParameter = command.CreateParameter();
dataParameter.Direction = ParameterDirection.Input;
dataParameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Bytea;
dataParameter.ParameterName = "data";
command.Parameters.Add(dataParameter);
dataParameter.Value = data;
if (prepare)
{
command.Prepare();
}
using (var metrics = TestMetrics.Start(TestRunTime, true))
{
while (!metrics.TimesUp)
{
var data2 = (byte[])command.ExecuteScalar();
metrics.IncrementIterations();
}
}
}
}
public void LerEmpresa(int Terminal, out Types.Empregador empregador)
{
SqlCommand comm = Conn.CreateCommand();
comm.CommandText = "SELECT TOP 1 (CASE WHEN ISNULL(TRM_CNPJ, '') <> '' THEN 'J' ELSE 'F' END) AS PESSOATIPO, " +
"TRM_CNPJ, TRM_CPF, TRM_RAZAOSOCIAL, TRM_ENDERECO FROM Terminais WHERE TRM_IND = " + Terminal.ToString();
SqlDataReader reader = comm.ExecuteReader();
reader.Read();
empregador.PessoaTipo = reader["PESSOATIPO"].ToString().Trim();
if (empregador.PessoaTipo == "J")
{
empregador.Pessoa = rStrings.FormataCnpj(reader["TRM_CNPJ"].ToString().Trim());
}
else
{
empregador.Pessoa = rStrings.FormataCnpj(reader["TRM_CPF"].ToString().Trim());
}
empregador.Nome = reader["TRM_RAZAOSOCIAL"].ToString().Trim();
empregador.Cei = "";
empregador.Endereco = reader["TRM_ENDERECO"].ToString().Trim();
reader.Close();
}
public void ParameterizedSelectBigIntRoundTrip()
{
using (var command = Conn.CreateCommand())
{
command.CommandType = CommandType.Text;
command.CommandText = "SELECT :data1, :data2, :data3, :data4, :data5, :data6, :data7, :data8, :data9, :data10";
for (int i = 0; i < 10; i++)
{
NpgsqlParameter dataParameter = command.CreateParameter();
dataParameter.Direction = ParameterDirection.Input;
dataParameter.NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Bigint;
dataParameter.ParameterName = string.Format("data{0}", i + 1);
command.Parameters.Add(dataParameter);
dataParameter.Value = 0xFFFFFFFFFFFFFFF;
}
command.Prepare();
using (var metrics = TestMetrics.Start(TestRunTime, true))
{
while (!metrics.TimesUp)
{
using (IDataReader r = command.ExecuteReader())
{
r.Read();
Int64 r10 = (Int64)r[9];
}
metrics.IncrementIterations();
}
}
}
}
public int GetAlertOrdersCount(int agencyNo)
{
var qTime = DateTime.Now;
Conn.Open();
var cmd = Conn.CreateCommand();
cmd.CommandType = CommandType.TableDirect;
cmd.CommandText = "hnotordc";
var rdr = cmd.ExecuteExtendedReader();
var count = 0;
var orderno = 0;
try {
while (rdr.Read())
{
var repno = rdr.ReadInt("repno");
var neworderno = rdr.ReadInt("orderno");
if (agencyNo == 0 || Math.Round(repno / 10m, 0) == agencyNo && orderno != neworderno)
{
orderno = neworderno;
count++;
}
}
}
catch (Exception e) {
Debug.WriteLine(e);
count = GetAlertOrdersCount2(agencyNo);
}
rdr.Close();
Conn.Close();
QueryDebugEnd(qTime, $"GetAlertOrdersCount");
return(count);
}
/// <summary>
/// Drop Edge View
/// </summary>
/// <param name="nodeViewSchema">The name of node view. Default(null or "") by "dbo".</param>
/// <param name="nodeViewName">The name of node view.</param>
/// <param name="externalTransaction">An existing SqlTransaction instance under which the drop edge view will occur.</param>
public void DropNodeView(string nodeViewSchema, string nodeViewName, SqlTransaction externalTransaction = null)
{
SqlTransaction transaction = externalTransaction ?? Conn.BeginTransaction();
var command = Conn.CreateCommand();
command.Transaction = transaction;
try
{
//drop view
const string dropView = @"drop view [{0}]";
command.CommandText = string.Format(dropView, nodeViewName);
command.ExecuteNonQuery();
//update metatable
const string deleteNodeViewColumn = @"
Delete from [{0}]
where [NodeViewColumnId] in
(
Select columnId
From [{1}] NodeTableColumn
Where TableSchema = @schema and TableName = @tablename and ColumnRole = @role
)
Delete from [{1}]
Where TableSchema = @schema and TableName = @tablename and ColumnRole = @role";
command.Parameters.AddWithValue("schema", nodeViewSchema);
command.Parameters.AddWithValue("tablename", nodeViewName);
command.Parameters.AddWithValue("role", 4);
command.CommandText = string.Format(deleteNodeViewColumn, MetadataTables[5], MetadataTables[1]);
command.ExecuteNonQuery();
const string deleteNodeView = @"
Delete from [{0}]
where NodeViewTableId in
(
select tableid
from [{1}]
where TableRole = @role and TableSchema = @schema and TableName = @tablename
)
delete from [{1}]
where TableRole = @role and TableSchema = @schema and TableName = @tablename";
command.Parameters["role"].Value = 1;
command.CommandText = string.Format(deleteNodeView, MetadataTables[7], MetadataTables[0]);
command.ExecuteNonQuery();
if (externalTransaction == null)
{
transaction.Commit();
}
}
catch (Exception error)
{
if (externalTransaction == null)
{
transaction.Rollback();
}
throw new NodeViewException("Drop node view:" + error.Message);
}
}
public void ParseStringWithSpecialChars()
{
using (var cmd = Conn.CreateCommand()) {
cmd.CommandText = "SELECT 'b''la'";
Assert.That(cmd.ExecuteScalar(), Is.EqualTo("b'la"));
cmd.CommandText = "SELECT 'type(''m.response'')#''O''%'";
Assert.That(cmd.ExecuteScalar(), Is.EqualTo("type('m.response')#'O'%"));
}
}
public void PostgisTestRead(TestAtt att)
{
using (var cmd = Conn.CreateCommand())
{
var a = att;
cmd.CommandText = "Select " + a.SQL;
var p = cmd.ExecuteScalar();
Assert.IsTrue(p.Equals(a.Geom));
}
}
public void PostgisTestArrayRead()
{
using (var cmd = Conn.CreateCommand())
{
cmd.CommandText = "Select ARRAY(select st_makepoint(1,1))";
var p = cmd.ExecuteScalar() as PostgisGeometry[];
var p2 = new PostgisPoint(1d, 1d);
Assert.IsTrue(p != null && p[0] is PostgisPoint && p2 == (PostgisPoint)p[0]);
}
}
public void PostgisTestReadSrid(TestAtt a)
{
using (var cmd = Conn.CreateCommand())
{
cmd.CommandText = "Select st_setsrid(" + a.SQL + ",3942)";
var p = cmd.ExecuteScalar();
Assert.IsTrue(p.Equals(a.Geom));
Assert.IsTrue((p as PostgisGeometry).SRID == 3942);
}
}
public void PlusInfinityDateTimeSupport()
{
var command = Conn.CreateCommand();
command.Parameters.Add(new NpgsqlParameter("p0", DateTime.MaxValue));
command.CommandText = "select 1 where current_date=:p0";
var result = command.ExecuteScalar();
Assert.AreEqual(null, result);
}
private void Excecute_Bugs_240_and_296_query(string query)
{
using (var cmd = Conn.CreateCommand()) {
cmd.CommandText = query;
cmd.Parameters.AddWithValue("p", DBNull.Value);
// syntax error at or near ":"
cmd.ExecuteReader().Dispose();
}
}
public void PostgisTestWriteSrid(TestAtt a)
{
using (var cmd = Conn.CreateCommand())
{
cmd.Parameters.AddWithValue("p1", NpgsqlDbType.Geometry, a.Geom);
a.Geom.SRID = 3942;
cmd.CommandText = "Select st_asewkb(:p1) = st_asewkb(st_setsrid(" + a.SQL + ",3942))";
var p = (bool)cmd.ExecuteScalar();
Assert.IsTrue(p);
}
}
public void TsVector()
{
using (var cmd = Conn.CreateCommand())
{
var inputVec = NpgsqlTsVector.Parse(" a:12345C a:24D a:25B b c d 1 2 a:25A,26B,27,28");
cmd.CommandText = "Select :p";
cmd.Parameters.AddWithValue("p", inputVec);
var outputVec = cmd.ExecuteScalar();
Assert.AreEqual(inputVec.ToString(), outputVec.ToString());
}
}
public void PostgisTestArrayWrite()
{
using (var cmd = Conn.CreateCommand())
{
var p = new PostgisPoint[1] {
new PostgisPoint(1d, 1d)
};
cmd.Parameters.AddWithValue(":p1", NpgsqlDbType.Array | NpgsqlDbType.Geometry, p);
cmd.CommandText = "SELECT :p1 = array(select st_makepoint(1,1))";
Assert.IsTrue((bool)cmd.ExecuteScalar());
}
}
public void TsQuery()
{
using (var cmd = Conn.CreateCommand())
{
var query = NpgsqlTsQuery.Parse("(a & !(c | d)) & (!!a&b) | ä | d | e");
cmd.CommandText = "Select :p";
cmd.Parameters.AddWithValue("p", query);
var output = cmd.ExecuteScalar();
Assert.AreEqual(query.ToString(), output.ToString());
}
}
