/// <summary>
/// Create a SQL neural data set.
/// </summary>
/// <param name="sql">The SQL to execute.</param>
/// <param name="inputSize">The size of the input data being read.</param>
/// <param name="idealSize">The size of the ideal output data being read.</param>
/// <param name="connectString">The connection string.</param>
public SQLCODEC(String sql, int inputSize,
int idealSize, String connectString)
{
_inputSize = inputSize;
_idealSize = idealSize;
_connection = new OleDbConnection(connectString);
_connection.Open();
_statement = _connection.CreateCommand();
_statement.CommandText = sql;
_statement.Prepare();
_statement.Connection = _connection;
}
/// <summary>
/// Exclui um objeto Modelo na Base de Dados
/// </summary>
private void Delete(Dictionary <string, object> DadosOriginal, GerenciadorDB mngBD, ref string sDetalhesLog)
{
sDetalhesLog += string.Format("{0} apagado(a). Detalhes:\r\n", NomeEntidade);
using (System.Data.Common.DbCommand cmd = mngBD.getCommand())
{
string sSQL = string.Format("DELETE FROM {0}.{1} WHERE ", GerenciadorDB.NameSpaceDB, NomeTabela);
// inclui todos os parametros no where para verificar concorrencia
foreach (KeyValuePair <string, object> itemOriginal in DadosOriginal)
{
// Verifica se valor é NULL
string VerifNull = itemOriginal.Value == null || (itemOriginal.Value is string && string.IsNullOrEmpty((string)itemOriginal.Value)) ||
(itemOriginal.Value is bool && (bool)itemOriginal.Value == false) || (itemOriginal.Value is int && (int)itemOriginal.Value == 0) ? "1" : "0";
// SQL do parâmetro
sSQL += "((1=" + VerifNull + " AND " + itemOriginal.Key + " is NULL) OR (" + itemOriginal.Key + "=?" + (VerifNull == "1" && itemOriginal.Value is string? " OR " + itemOriginal.Key + "=\'\'" : "") + ")) AND ";
// Adiciona parâmetro
cmd.Parameters.Add(Parametro(itemOriginal, mngBD));
// descrição LOG
sDetalhesLog += itemOriginal.Key + ": [" + (itemOriginal.Value == null ? "NULL" : itemOriginal.Value.ToString()) + "]\r\n";
}
sSQL = sSQL.Substring(0, sSQL.Length - 5);
cmd.CommandText = sSQL;
cmd.Prepare();
// Executa
int iAffectedRows = cmd.ExecuteNonQuery();
if (!iAffectedRows.Equals(1))
{
throw new Exception(string.Format("Erro ao executar delete. Não foi afetado o número esperado de linhas.\r\nLinhas afetadas: {0}.\r\nDetalhes: {1}.", iAffectedRows, sDetalhesLog));
}
}
}
public bool CheckLogDb(string file)
{
bool ret = true;
try
{
string dir = Path.GetDirectoryName(file);
if(!Directory.Exists(dir))
{
Directory.CreateDirectory(dir);
}
cn = new SQLiteConnection();
{
cn.ConnectionString = string.Format("Data Source=\"{0}\";FailIfMissing=False;", file);
cn.Open();
{
using(DbTransaction tr = cn.BeginTransaction())
{
try
{
using(DbCommand cmd = cn.CreateCommand())
{
cmd.Transaction = tr;
cmd.CommandText = @"CREATE TABLE IF NOT EXISTS GPS (id INTEGER NOT NULL PRIMARY KEY,
TimeUTC DATETIME NOT NULL,
SessionCounter INTEGER NOT NULL,
Delta DOUBLE,
Speed DOUBLE,
SeaLevelAltitude DOUBLE,
EllipsoidAltitude DOUBLE,
SatellitesInView TINYINT,
SatelliteCount TINYINT,
Lat DOUBLE NOT NULL,
Lng DOUBLE NOT NULL,
PositionDilutionOfPrecision DOUBLE,
HorizontalDilutionOfPrecision DOUBLE,
VerticalDilutionOfPrecision DOUBLE,
FixQuality TINYINT NOT NULL,
FixType TINYINT NOT NULL,
FixSelection TINYINT NOT NULL);
CREATE INDEX IF NOT EXISTS IndexOfGPS ON GPS (TimeUTC, PositionDilutionOfPrecision);";
cmd.ExecuteNonQuery();
}
this.cmd = cn.CreateCommand();
{
cmd.CommandText = @"INSERT INTO GPS
(TimeUTC,
SessionCounter,
Delta,
Speed,
SeaLevelAltitude,
EllipsoidAltitude,
SatellitesInView,
SatelliteCount,
Lat,
Lng,
PositionDilutionOfPrecision,
HorizontalDilutionOfPrecision,
VerticalDilutionOfPrecision,
FixQuality,
FixType,
FixSelection) VALUES(@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16);";
cmd.Parameters.Add(new SQLiteParameter("@p1"));
cmd.Parameters.Add(new SQLiteParameter("@p2"));
cmd.Parameters.Add(new SQLiteParameter("@p3"));
cmd.Parameters.Add(new SQLiteParameter("@p4"));
cmd.Parameters.Add(new SQLiteParameter("@p5"));
cmd.Parameters.Add(new SQLiteParameter("@p6"));
cmd.Parameters.Add(new SQLiteParameter("@p7"));
cmd.Parameters.Add(new SQLiteParameter("@p8"));
cmd.Parameters.Add(new SQLiteParameter("@p9"));
cmd.Parameters.Add(new SQLiteParameter("@p10"));
cmd.Parameters.Add(new SQLiteParameter("@p11"));
cmd.Parameters.Add(new SQLiteParameter("@p12"));
cmd.Parameters.Add(new SQLiteParameter("@p13"));
cmd.Parameters.Add(new SQLiteParameter("@p14"));
cmd.Parameters.Add(new SQLiteParameter("@p15"));
cmd.Parameters.Add(new SQLiteParameter("@p16"));
cmd.Prepare();
}
tr.Commit();
}
catch
{
tr.Rollback();
ret = false;
}
}
}
}
}
catch(Exception ex)
{
if(cn != null)
{
cn.Dispose();
cn = null;
}
if(cmd != null)
{
cmd.Dispose();
cmd = null;
}
Debug.WriteLine("CreateEmptyDB: " + ex.ToString());
ret = false;
}
if(ret)
{
LogDb = file;
}
else
{
LogDb = null;
}
return ret;
}
/// <summary>
/// Open a connection to the database.
/// </summary>
private void Open()
{
try
{
if (options != null)
{
connection = new OleDbConnection(this.options.DbConnectionString);
connection.Open();
stmtStatus = this.connection.CreateCommand();
stmtStatus.CommandText = MainForm.sqlStatus;
stmtStatus.Prepare();
stmtDepth = this.connection.CreateCommand();
stmtDepth.CommandText = MainForm.sqlDepth;
stmtDepth.Prepare();
}
else
{
errorMessage = "Data base options were not loaded";
}
}
catch (Exception e)
{
errorMessage = e.Message;
}
}
protected void PrepareCmd(DbCommand cmd, DbConnection conn, DbTransaction trans)
{
if (null == cmd || null == conn)
{
throw new ArgumentNullException(Properties.DbResource.DbCmdNull);
}
if (null == cmd.Connection)
{
cmd.Connection = conn;
}
if (null == cmd.Connection)
{
throw new ArgumentNullException(Properties.DbResource.DbConnNull);
}
if (cmd.Connection.State != ConnectionState.Open)
{
cmd.Connection.Open();
}
if (null != trans)
{
cmd.Transaction = trans;
}
cmd.Prepare();
}
private static void PrepareChannelInsert(DbCommand cmd)
{
var cols = new[] { "listid", "slot", "seq", "isdel", "progmask", "prognr", "progfix", "name", "tpnr", "satnr", "onid", "tsid", "ssid", "uid", "favcrypt", "lockskiphide" };
cmd.Parameters.Clear();
var sb = new StringBuilder();
sb.Append("insert into channel (");
var comma = "";
foreach (var col in cols)
{
sb.Append(comma).Append(col);
comma = ",";
}
sb.Append(") values (");
comma = "";
foreach (var col in cols)
{
sb.Append(comma).Append('@').Append(col);
comma = ",";
}
sb.Append(")");
cmd.CommandText = sb.ToString();
foreach (var col in cols)
{
DbParameter parm = cmd.CreateParameter();
parm.ParameterName = "@" + col;
if (col == "name" || col == "uid")
{
parm.DbType = DbType.String;
parm.Size = 40;
}
else
parm.DbType = DbType.Int32;
cmd.Parameters.Add(parm);
}
cmd.Prepare();
}
/// <summary>
/// Creates a SqlCommand object that for performing the MERGE.
/// This SqlCommand object does not contain a SQL statement in its CommandText.
/// Instead, its CommandText contains the name of the stored procedure that performs the MERGE.
/// As a result, CommandType is set to CommandType.StoredProcedure.
/// Recall that the stored procedure accepts a TVP as input parameter.
/// This SqlCommand object sends the ADO.NET DataTable named m_table as the value of this parameter.
///
/// The SqlCommand object created is stored in the member m_mergeCommand and is prepared once.
/// This is an optimization, that avoids preparing the same command repeatedly for each batch.
/// </summary>
private void createMergeCommand()
{
m_mergeCommand = new SqlCommand();
m_mergeCommand.CommandText = m_mergeProcedureName;
m_mergeCommand.Connection = m_DbConnection;
m_mergeCommand.CommandType = CommandType.StoredProcedure;
((SqlCommand)m_mergeCommand).Parameters.AddWithValue("tmpTable", m_table);
m_mergeCommand.Prepare();
}
