public static List <UlovStavka> DohvatiOdDoSveUlovStave(DateTime pocetakdatum, DateTime krajdatum)
{
List <UlovStavka> listaStavki = new List <UlovStavka>();
SQLiteCommand c = Bazapodataka.con.CreateCommand();
c.CommandText = String.Format(@"SELECT UlovStavka.id_riba, Riba.naziv,
SUM(UlovStavka.kolicina) as kolicina,
Ulov.id_kapetan
FROM UlovStavka
LEFT JOIN Riba ON UlovStavka.id_riba = Riba.id
LEFT JOIN Ulov ON UlovStavka.id_ulov = Ulov.id
WHERE Ulov.datum BETWEEN '{0}' and '{1}'
GROUP BY UlovStavka.id_riba", pocetakdatum.ToFileTime(), krajdatum.ToFileTime());
SQLiteDataReader reader = c.ExecuteReader();
while (reader.Read())
{
Riba r = new Riba();
UlovStavka k = new UlovStavka();
r.id = (long)reader["id_riba"];
r.Naziv = (string)reader["naziv"];
k.Kolicina = Convert.ToDouble(reader["kolicina"]);
k.Riba = r;
listaStavki.Add(k);
}
reader.Dispose();
c.Dispose();
return(listaStavki);
}
public void Select()
{
try
{
if (Common.Conn == null)
{
Common.Conn = new SQLiteConnection(Common.ConnectionString);
Common.Conn.Open();
}
SQLiteCommand Com = Common.Conn.CreateCommand();
Com.CommandText = tblFBDBlockPin.SQL_Select;
Com.Parameters.AddRange(GetSqlParameters());
SQLiteDataReader rs = Com.ExecuteReader();
for (
; rs.Read();
)
{
AddFromRecordSet(rs);
}
rs.Close();
rs.Dispose();
Com.Dispose();
}
catch (System.Exception)
{
throw;
}
}
public View(SQLiteConnectionStringBuilder SQLConnSetting, int object_id, Views owner)
{
_Owner = owner;
SQLConnSet.ConnectionString = SQLConnSetting.ConnectionString;
SQLiteConnection Conn = new SQLiteConnection(SQLConnSet.ConnectionString);
Conn.Open();
SQLiteCommand Com = Conn.CreateCommand();
Com.CommandTimeout = 10;
Com.CommandText = "SELECT name, object_id, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published, is_schema_published, is_replicated, has_replication_filter, has_opaque_metadata, has_unchecked_assembly_data, with_check_option, is_date_correlation_view FROM sys.views WHERE object_id=" + object_id + " --ORDER BY name";
SQLiteDataReader rs = Com.ExecuteReader(); //Opretter objektet rs som SQLiteDataReader. (Dette er selve Recordsættet fra databasen).
while (rs.Read()) //Så længe programmet ikke er nået til slutningen af tabellen, så...
{
AddFromRecordSet(rs);
}
rs.Close();//Lukker SQLiteDataReader igen.
Conn.Close();
Com.Dispose();
rs.Dispose();
Conn.Dispose();
_Columns = new Columns(SQLConnSetting, this);
}
public List <string> GetColumnList(string query, string DatabasePath)
{
List <string> List = new List <string>();
using (SQLiteConnection con = new SQLiteConnection(DatabasePath))
{
using (SQLiteCommand command = new SQLiteCommand(con))
{
con.Open();
command.CommandText = query;
command.ExecuteNonQuery();
SQLiteDataReader Reader = command.ExecuteReader();
int i = 0;
if (Reader.Read())
{
while (i < Reader.FieldCount)
{
List.Insert(i, Convert.ToString(Reader.GetValue(i)));
i++;
}
}
Reader.Dispose();
con.Close();
return(List);
}
}
}
public static List <Clube2> lerRegistos()
{
try
{
SQLiteConnection myConn = new SQLiteConnection("Data Source=OrniFile_v1.db; version=3");
myConn.Open();
string sql_select = "SELECT * FROM Clube";
try
{
SQLiteCommand myCommand = new SQLiteCommand(sql_select, myConn);
SQLiteDataReader reader = myCommand.ExecuteReader();
utilC.Clear();
while (reader.Read())
{
Clube2 newclube = new Clube2((long)reader["id_clube"],
(string)reader["nome"]);
utilC.Add(newclube);
}
reader.Dispose();
myConn.Close();
return(utilC);
}
catch (Exception ex)
{
MessageBox.Show("Não consegui ler os registos. " + " " + ex.Message);
return(utilC);
}
}
catch (Exception ex)
{
MessageBox.Show("Não consegui conetar a base de dados." + " " + ex.Message);
return(utilC);
}
}
/// <summary>
/// Return the ID of the object of the table.
/// </summary>
/// <param name="objet">Object we want to know his ID</param>
/// <param name="classe">Table in which the table is.</param>
/// <returns>Return an the ID as int</returns>
private static int ReturnID(String objet, String classe)
{
int id;
sqlConn = new SQLiteConnection("Data Source=Mercure.SQLite;");
sqlConn.Open();
SQLiteCommand sqlCmd = sqlConn.CreateCommand();
sqlCmd.CommandText = "SELECT Ref" + classe + " FROM " + classe + "s WHERE Nom = '" + objet + "'";
SQLiteDataReader reader = sqlCmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
id = reader.GetInt32(0);
}
else
{
id = 0;
}
reader.Close();
reader.Dispose();
sqlConn.Close();
return(id);
}
/// <summary>
/// Iniciação da classe a partir de um usuário de entrada
/// </summary>
/// <param name="user"> Contém informações usadas para ler demais parâmetros do hospital</param>
public Hospital(Usuario user)
{
this.ID_Usuario = user.ID_Usuario;
this.Tipo_Usuario = user.Tipo_Usuario;
this.Nome_Usuario = user.Nome_Usuario;
LocalDatabase db = new LocalDatabase();
//Criação de query para ler informações do hospital
string query = $"SELECT * FROM Hospital Where ID_Usuario = {this.ID_Usuario}";
//Solicita infos ao banco
SQLiteDataReader sqlite_datareader = db.Query(query);
///Preenche atributos com retorno do banco
if (sqlite_datareader.Read())
{
this.ID_Hospital = (Int64)sqlite_datareader[nameof(Hospital.ID_Hospital)];
this.Nome_Hospital = (string)sqlite_datareader[nameof(Hospital.Nome_Hospital)];
this.Cidade = (string)sqlite_datareader[nameof(Hospital.Cidade)];
this.CEP = (string)sqlite_datareader[nameof(Hospital.CEP)];
this.UTI_Adulto = (Int64)sqlite_datareader[nameof(Hospital.UTI_Adulto)];
this.UTI_Neonatal = (Int64)sqlite_datareader[nameof(Hospital.UTI_Neonatal)];
this.UTI_Pediatrica = (Int64)sqlite_datareader[nameof(Hospital.UTI_Pediatrica)];
this.Emergencia = (Int64)sqlite_datareader[nameof(Hospital.Emergencia)];
}
sqlite_datareader.Dispose();
db.Close();
}
//liefert die Objekte und Variablen einer Sequenz
public List <Obj> GetObjects(String Scope)
{
List <Obj> Result = new List <Obj>();
String _SQL = "SELECT tab2.Scope,tab2.ClassID,tab2.Object,ClassType,tab2.Start,tab2.Length from ObjectLinks " +
"inner join ObjectList as tab1 on tab1.ID==ObjectLinks.ID_ObjectList " +
"inner join ObjectList as tab2 on tab2.ID==ObjectLinks.ID_ObjectListRel " +
"inner join ObjectDecl on ObjectDecl.ID==ObjectLinks.ID_ObjectDecl ";
_SQL += " where (tab1.Scope Like('" + Scope + "') AND tab1.ClassID==tab1.Scope AND (ClassType==" +
((int)ObjDecl.TClassType.tCTType).ToString() + " OR ClassType==" + ((int)ObjDecl.TClassType.tCTClass).ToString() + "))" +
" OR (tab2.ClassID Like('" + Scope + "') AND tab1.ClassID==tab2.ClassID AND ClassType==" + ((int)ObjDecl.TClassType.tCTClass).ToString() + ")";
//Todo we dont see what is defined in other sequences
//alt SELECT Scope,Object, ClassID FROM ObjectList where Scope Like('Projects\ZBF\Sequences\test.seq') AND Object!=''
try {
SQLiteCommand command = new SQLiteCommand(_SQL, m_dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Result.Add(new Obj(reader.GetString(reader.GetOrdinal("Scope")),
reader.GetString(reader.GetOrdinal("Object")),
reader.GetString(reader.GetOrdinal("ClassID")),
"",
reader.GetInt32(reader.GetOrdinal("Start")),
reader.GetInt32(reader.GetOrdinal("Length"))));
}
reader.Dispose();
command.Dispose();
} catch (Exception e) {
HandleDBError(e);
} finally {
}
return(Result);
}
public List <Fields> GetFields(string serverId, string dbName, string tableName)
{
List <Fields> fieldsList = new List <Fields>();
using (SQLiteConnection conn = new SQLiteConnection(""))
{
conn.Open();
using (SQLiteCommand cmd = new SQLiteCommand(string.Format("PRAGMA table_info({0})", tableName), conn))
{
SQLiteDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
fieldsList.Add(new Model.Fields()
{
Name = dr["name"].ToString(),
Type = dr["type"].ToString(),
IsNull = "1" != dr["notnull"].ToString(),
IsPrimaryKey = "1" == dr["pk"].ToString(),
Default = dr["dflt_value"].ToString(),
IsIdentity = "1" == dr["pk"].ToString() && "INTEGER" == dr["type"].ToString(),
NetType = GetFieldType(dr["type"].ToString(), "1" != dr["notnull"].ToString()),
SqlType = GetFieldSqlType(dr["type"].ToString()),
Note = "",
Length = -1
});
}
dr.Close();
dr.Dispose();
}
}
return(fieldsList);
}
//writes a value in the config-Table
private void UpdateConfig(String Name, String ValueString, int ValueInt)
{
String sql = "";
Boolean Update = false;
try {
SQLiteCommand command = new SQLiteCommand(
"SELECT ID,Name,ValueText,ValueInt FROM Config where Name='" + Name + "';", m_dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Update = true;
int id = reader.GetInt32(reader.GetOrdinal("ID"));
String name = reader.GetString(reader.GetOrdinal("Name"));
}
reader.Dispose();
command.Dispose();
if (Update)
{
sql = "Update Config Set ValueText='" + ValueString +
"',ValueInt=" + String.Format("{0}", ValueInt) +
" where Name='" + Name + "';";
}
else
{
sql = "INSERT INTO Config (Name,ValueText,ValueInt) " +
"VALUES ( '" + Name + "','" + ValueString + "'," +
String.Format("{0}", ValueInt) + ");";
}
ExecuteSimpleQuery(sql);
} catch (Exception e) {
HandleDBError(e);
}
}
//fetch the primary key for the dataset or set to invalid
int RefreshObjDeclID(ObjDecl theObj)
{
String _SQL = ("SELECT ID from ObjectDecl where ClassID=='");
_SQL += theObj.ClassID() + "' AND Function='" + theObj.Function() + "';";
int Return = 0;
Boolean Exists = false;
try {
SQLiteCommand command = new SQLiteCommand(_SQL, m_dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
//Todo fix non exist
Exists = true;
Return = reader.GetInt32(reader.GetOrdinal("ID"));
}
reader.Dispose();
command.Dispose();
} catch (Exception e) {
HandleDBError(e);
}
theObj.updateID(Return);
return(Return);
}
public List <String> GetReturns(String Scope, String Object, String Function)
{
List <String> Result = new List <String>();
String _SQL;
// Todo maybe its more efficient to put this into stored procedure?
String _SQL2 = " from ObjectLinks inner join ObjectList as tab1 on tab1.ID==ObjectLinks.ID_ObjectList " +
"inner join ObjectDecl on ObjectDecl.ID==ObjectLinks.ID_ObjectDecl" +
"inner join ObjectList as tab2 on tab2.ID==ObjectLinks.ID_ObjectListRel ";
_SQL = "SELECT distinct Returns" + _SQL2 + "where tab1.Scope=='";
_SQL += Scope + "' AND tab2.Object=='" + Object + "' AND Function=='" + Function + "'" + " order by Params;";
try {
SQLiteCommand command = new SQLiteCommand(_SQL, m_dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Result.Add(reader.GetString(reader.GetOrdinal("Returns")));
}
reader.Dispose();
command.Dispose();
} catch (Exception e) {
HandleDBError(e);
} finally {
}
return(Result);
}
//liefert die Funktionen einer Sequenz
public List <ObjDecl> GetFunctions(String Scope)
{
List <ObjDecl> Result = new List <ObjDecl>();
String _SQL2 = " from ObjectLinks inner join ObjectList as tab1 on tab1.ID==ObjectLinks.ID_ObjectList" +
//? " inner join ObjectList as tab2 on tab2.ID==ObjectLinks.ID_ObjectListRel " +
" inner join ObjectDecl on ObjectDecl.ID==ObjectLinks.ID_ObjectDecl ";
String _SQL = "SELECT distinct ObjectDecl.ClassID,Function,Params,Returns,ClassType,ObjectDecl.Start,ObjectDecl.Length ";
_SQL += _SQL2;
_SQL += " where tab1.Scope Like('" + Scope + "') AND tab1.ClassID==tab1.Scope AND ObjectDecl.ClassType==" +
((int)ObjDecl.TClassType.tCTSeq).ToString();
try {
SQLiteCommand command = new SQLiteCommand(_SQL, m_dbConnection);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Result.Add(new ObjDecl(reader.GetString(reader.GetOrdinal("ClassID")),
(ObjDecl.TClassType)reader.GetInt32(reader.GetOrdinal("ClassType")),
reader.GetString(reader.GetOrdinal("Function")),
reader.GetString(reader.GetOrdinal("Params")),
reader.GetString(reader.GetOrdinal("Returns")), "",
reader.GetInt32(reader.GetOrdinal("Start")),
reader.GetInt32(reader.GetOrdinal("Length"))));
}
reader.Dispose();
command.Dispose();
} catch (Exception e) {
HandleDBError(e);
} finally {
}
return(Result);
}
public static List <UlovStavka> DohvatiSveUlovStavkeIRibeUkupno()
{
List <UlovStavka> listaStavki = new List <UlovStavka>();
SQLiteCommand c = Bazapodataka.con.CreateCommand();
c.CommandText = @"SELECT UlovStavka.id_riba, Riba.naziv,
SUM(UlovStavka.kolicina) as kolicina
FROM UlovStavka LEFT JOIN Riba
ON UlovStavka.id_riba = Riba.id GROUP BY UlovStavka.id_riba";
SQLiteDataReader reader = c.ExecuteReader();
while (reader.Read())
{
Riba r = new Riba();
UlovStavka k = new UlovStavka();
r.id = (long)reader["id_riba"];
r.Naziv = (string)reader["naziv"];
k.Kolicina = Convert.ToDouble(reader["kolicina"]);
k.Riba = r;
listaStavki.Add(k);
}
reader.Dispose();
c.Dispose();
return(listaStavki);
}
/// <summary>
/// Get all articles
/// </summary>
/// <returns>Returns a list of articles : { RedfArcticle, description,marque,famille,sousFamille,prix }</returns>
public List <string[]> GetListArticles()
{
List <String[]> list = new List <string[]>();
sqlConn.Open();
SQLiteCommand sqlCmd = sqlConn.CreateCommand();
sqlCmd.CommandText = "SELECT a.Description, a.RefArticle, m.Nom as 'Marque', f.Nom as 'Famille', sf.Nom as 'Sous-famille', a.PrixHT, a.Quantite FROM Articles a, Marques m, SousFamilles sf, Familles f WHERE a.RefMarque = m.RefMarque AND a.RefSousFamille = sf.RefSousFamille AND sf.RefFamille = f.RefFamille";
SQLiteDataReader reader = sqlCmd.ExecuteReader();
//Tant qu'on lit une ligne
while (reader.Read())
{
String description = reader.GetString(0);
String refArticle = reader.GetString(1);
String marque = reader.GetString(2);
String famille = reader.GetString(3);
String sousFamille = reader.GetString(4);
String prix = reader.GetString(5);
String quantite = reader.GetInt32(6).ToString();
String[] article = { description, refArticle, marque, famille, sousFamille, prix.ToString(), quantite };
list.Add(article);
}
reader.Close();
reader.Dispose();
sqlConn.Close();
return(list);
}
public static int GetDomainID(string ConnectionString, string domainname)
{
int ret = -1;
try
{
if (Common.Conn == null)
{
Common.Conn = new SQLiteConnection(Common.ConnectionString);
Common.Conn.Open();
}
SQLiteCommand Com = Common.Conn.CreateCommand();
Com.CommandText = "SELECT [DomainID] FROM [tblDomain] WHERE [DomainName]= '" + domainname + "'";
//Com.Parameters.AddRange(GetSqlParameters());
//Conn.Open();
SQLiteDataReader rs = Com.ExecuteReader();
while (rs.Read())
{
ret = rs.GetInt32(rs.GetOrdinal("DomainID"));
}
rs.Close();
//Conn.Close();
rs.Dispose();
Com.Dispose();
// Conn.Dispose();
}
catch (System.Exception)
{
}
return(ret);
}
/// <summary>
/// Return the max ID of the table
/// </summary>
/// <param name="classe">Table concerned. She has to be entered without 's' at the end</param>
/// <returns> Return an the ID max as int</returns>
private static int ReturnIdMax(String classe)
{
int id;
sqlConn.Open();
SQLiteCommand sqlCmd = sqlConn.CreateCommand();
sqlCmd.CommandText = "SELECT MAX(Ref" + classe + ") FROM " + classe + "s"; //on ajoute un 's'
SQLiteDataReader reader = sqlCmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
if (reader.IsDBNull(0))
{
id = 0;
}
else
{
id = reader.GetInt32(0);
}
}
else
{
id = 0;
}
reader.Close();
reader.Dispose();
sqlConn.Close();
return(id);
}
public static List <GR5_TemplateItem> GetTemplateItems()
{
List <GR5_TemplateItem> result = new List <GR5_TemplateItem>();
SQLiteCommand command = new SQLiteCommand(connection);
command.CommandText = "SELECT * FROM templateitems";
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
GR5_TemplateItem item = new GR5_TemplateItem();
item.m_ItemID = Convert.ToUInt32(reader[0].ToString());
item.m_ItemType = Convert.ToByte(reader[1].ToString());
item.m_ItemName = reader[2].ToString();
item.m_DurabilityType = Convert.ToByte(reader[3].ToString());
item.m_IsInInventory = reader[4].ToString() == "True";
item.m_IsSellable = reader[5].ToString() == "True";
item.m_IsLootable = reader[6].ToString() == "True";
item.m_IsRewardable = reader[7].ToString() == "True";
item.m_IsUnlockable = reader[8].ToString() == "True";
item.m_MaxItemInSlot = Convert.ToUInt32(reader[9].ToString());
item.m_GearScore = Convert.ToUInt32(reader[10].ToString());
item.m_IGCValue = Convert.ToUInt32(reader[11].ToString()) / 100f;
item.m_OasisName = Convert.ToUInt32(reader[12].ToString());
item.m_OasisDesc = Convert.ToUInt32(reader[13].ToString());
result.Add(item);
}
reader.Close();
reader.Dispose();
command.Dispose();
return(result);
}
public static bool checkExists()
{
using (SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=database.sqlite;Version=3;"))
{
try
{
m_dbConnection.Open();
}
catch (SQLiteException)
{
return(false);
}
string sql = "SELECT * FROM users WHERE email='" + Email + "'";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
try
{
SQLiteDataReader reader = command.ExecuteReader();
if (reader.Read())
{
return(true);
}
reader.Close();
reader.Dispose();
}
catch (Exception ex)
{
return(false);
}
}
return(false);
}
public static List <GR5_LoadoutKit> GetLoadoutKits(uint pid)
{
List <GR5_LoadoutKit> result = new List <GR5_LoadoutKit>();
SQLiteCommand command = new SQLiteCommand(connection);
command.CommandText = "SELECT * FROM loadoutkits WHERE pid=" + pid;
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
GR5_LoadoutKit kit = new GR5_LoadoutKit();
kit.m_LoadoutKitID = Convert.ToUInt32(reader[1].ToString());
kit.m_ClassID = Convert.ToUInt32(reader[2].ToString());
kit.m_Weapon1ID = Convert.ToUInt32(reader[3].ToString());
kit.m_Weapon2ID = Convert.ToUInt32(reader[4].ToString());
kit.m_Weapon3ID = Convert.ToUInt32(reader[5].ToString());
kit.m_Item1ID = Convert.ToUInt32(reader[6].ToString());
kit.m_Item2ID = Convert.ToUInt32(reader[7].ToString());
kit.m_Item3ID = Convert.ToUInt32(reader[8].ToString());
kit.m_PowerID = Convert.ToUInt32(reader[9].ToString());
kit.m_HelmetID = Convert.ToUInt32(reader[10].ToString());
kit.m_ArmorID = Convert.ToUInt32(reader[11].ToString());
kit.m_OasisDesc = Convert.ToUInt32(reader[12].ToString());
kit.m_Flag = Convert.ToUInt32(reader[13].ToString());
result.Add(kit);
}
reader.Close();
reader.Dispose();
command.Dispose();
return(result);
}
//public bool CompileController(string _domainname, string _controllername)
//{
// bool ret = false;
// foreach (tblDomain tbldomain in m_tblDomainCollection)
// {
// ret = tbldomain.CompileController(_controllername);
// break;
// }
// return ret;
//}
public bool Load()
{
try
{
if (Common.Conn == null)
{
Common.Conn = new SQLiteConnection(Common.ConnectionString);
Common.Conn.Open();
}
SQLiteCommand Com = Common.Conn.CreateCommand(); //SQLiteCommand Com = Conn.CreateCommand();
Com.CommandText = "SELECT [SolutionID] FROM [tblSolution] ";
Com.Parameters.AddRange(GetSqlParameters());
//Conn.Open();
SQLiteDataReader rs = Com.ExecuteReader();
while (rs.Read())
{
AddFromRecordSet(rs);
}
rs.Close();
//Conn.Close();
rs.Dispose();
Com.Dispose();
this.Select();
//Conn.Dispose();
}
catch (SQLiteException ex)
{
MessageBox.Show(ex.Message);
return(false);
}
//m_tblDomainCollection.Load();
//m_tblFunctionCollection.Load();
// tblHMICollection.Load();
return(true);
}
public static List <GR5_Character> GetUserCharacters(uint pid)
{
List <GR5_Character> result = new List <GR5_Character>();
SQLiteCommand command = new SQLiteCommand(connection);
command.CommandText = "SELECT * FROM characters WHERE pid=" + pid;
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
GR5_Character c = new GR5_Character();
c.PersonaID = pid;
c.ClassID = Convert.ToUInt32(reader[2].ToString());
c.PEC = Convert.ToUInt32(reader[3].ToString());
c.Level = Convert.ToUInt32(reader[4].ToString());
c.UpgradePoints = Convert.ToUInt32(reader[5].ToString());
c.CurrentLevelPEC = Convert.ToUInt32(reader[6].ToString());
c.NextLevelPEC = Convert.ToUInt32(reader[7].ToString());
c.FaceID = Convert.ToByte(reader[8].ToString());
c.SkinToneID = Convert.ToByte(reader[9].ToString());
c.LoadoutKitID = Convert.ToByte(reader[10].ToString());
result.Add(c);
}
reader.Close();
reader.Dispose();
command.Dispose();
return(result);
}
//--This function returns the values of the ID based on the data present in database...
public int DatabaseOperation()
{
int id_return = 1;
string databasePath = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
string databaseFile = databasePath + @"\db_psychrometric_project.s3db";
string connString = @"Data Source=" + databaseFile + ";Version=3;";
SQLiteConnection connection = new SQLiteConnection(connString);
connection.Open();
SQLiteDataReader reader = null;
SQLiteCommand comm = new SQLiteCommand("SELECT * from tbl_language_option where language_id = 1", connection);
//command.Parameters.AddWithValue("@1", userName)
reader = comm.ExecuteReader();
while (reader.Read())
{
//string selecte_location = reader["id"].ToString()+","+reader["country"].ToString() + "," + reader["state"].ToString() + "," + reader["city"].ToString();
//stored_location.Add(selecte_location);
id_return = int.Parse(reader["ID"].ToString());
}
// MessageBox.Show("id ret = " + id_return);
comm.Dispose();
reader.Dispose();
connection.Close();
return(id_return);
}
public static List <GR5_NewsMessage> GetNews(uint pid)
{
List <GR5_NewsMessage> result = new List <GR5_NewsMessage>();
SQLiteCommand command = new SQLiteCommand(connection);
command.CommandText = "SELECT * FROM news";
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
GR5_NewsMessage message = new GR5_NewsMessage();
message.header = new GR5_NewsHeader();
message.header.m_ID = Convert.ToUInt32(reader[0].ToString());
message.header.m_recipientID = pid;
message.header.m_recipientType = Convert.ToUInt32(reader[1].ToString());
message.header.m_publisherPID = Convert.ToUInt32(reader[2].ToString());
message.header.m_publisherName = reader[3].ToString();
message.header.m_displayTime = (ulong)DateTime.UtcNow.Ticks;
message.header.m_publicationTime = (ulong)DateTime.UtcNow.Ticks;
message.header.m_expirationTime = (ulong)DateTime.UtcNow.AddDays(5).Ticks;
message.header.m_title = reader[4].ToString();
message.header.m_link = reader[5].ToString();
message.m_body = reader[6].ToString();
result.Add(message);
}
reader.Close();
reader.Dispose();
command.Dispose();
return(result);
}
public Ticket[] GetAllTicketsFromUser(string useremail)
{
int userid = GetUserId(useremail);
SQLiteCommand cmd = new SQLiteCommand(
"SELECT * FROM tickets WHERE author=" + userid.ToString(),
db);
SQLiteDataReader reader = cmd.ExecuteReader();
List <Ticket> tickets = new List <Ticket>();
while (reader.Read())
{
int id = reader.GetInt32(0);
string authoremail = this.GetUser(reader.GetInt32(1)).email;
string authorname = this.GetUser(reader.GetInt32(1)).name;
string title = reader.GetString(2);
string description = reader.GetString(3);
string creationDate = reader.GetString(4);
string status = reader.GetString(5);
string solveremail = reader.IsDBNull(6) ? null : this.GetUser(reader.GetInt32(6)).email;
string solvername = reader.IsDBNull(6) ? null : this.GetUser(reader.GetInt32(6)).name;
string answer = reader.IsDBNull(7) ? null : reader.GetString(7);
Ticket ticket = new Ticket(id, authoremail, authorname, title, description,
creationDate, status, solveremail, solvername, answer);
tickets.Add(ticket);
}
cmd.Dispose();
reader.Dispose();
return(tickets.ToArray());
}
public Revision getRev(int id)
{
Revision rev = null;
using (SQLiteCommand cmd =
_getCmd(
@"SELECT id, branch, author, created, comment
FROM revisions
WHERE id = @id"))
{
cmd.Parameters.Add("@id", DbType.Int32);
cmd.Parameters[0].Value = id;
SQLiteDataReader rdr = cmd.ExecuteReader();
if (rdr.HasRows && rdr.Read())
{
rev = new Revision();
rev.ID = rdr.GetInt32(0).ToString();
rev.Branch = rdr.GetString(1);
rev.Author = rdr.GetString(2);
rev.Date = rdr.GetDateTime(3);
rev.Log = rdr.GetString(4);
}
rdr.Dispose();
rdr = null;
cmd.Connection.Close();
cmd.Connection = null;
}
return(rev);
}
/// <summary>
/// Executed every time the gamestate is activated.
/// </summary>
public override void Init()
{
base.Init();
timeRows.Clear();
rushRows.Clear();
// Reading data to check correct execution.
int i = 0;
string query = "select * from timeState order by score desc";
SQLiteDataReader reader = Database.GetReader(query);
while (reader.Read() && i++ < 15)
{
string row = reader["name"] + ":\t" + reader["score"];
timeRows.Add(row);
}
i = 0;
query = "select * from rushState order by score desc";
reader = Database.GetReader(query);
while (reader.Read() && i++ < 15)
{
string row = reader["name"] + ":\t" + reader["score"];
rushRows.Add(row);
}
reader.Dispose();
}
/// <summary>
/// Get an article thanks to its reference
/// </summary>
/// <param name="reference">reference (ID) of the article</param>
/// <returns>Returns an object Article</returns>
public Article GetArticleFromReference(String reference)
{
Article article = new Article();
sqlConn.Open();
//Requête
SQLiteCommand sqlCmd = new SQLiteCommand("SELECT a.Description, a.RefArticle, m.Nom as 'Marque', f.Nom as 'Famille', sf.Nom as 'Sous-famille', a.PrixHT, a.Quantite FROM Articles a, Marques m, SousFamilles sf, Familles f WHERE a.RefMarque = m.RefMarque AND a.RefSousFamille = sf.RefSousFamille AND sf.RefFamille = f.RefFamille AND a.RefArticle = @ref", sqlConn);
SQLiteParameter param = new SQLiteParameter("@ref", reference);
sqlCmd.Parameters.Add(param);
SQLiteDataReader reader = sqlCmd.ExecuteReader();
if (reader.Read())
{
article.Description = reader.GetString(0);
article.RefArticle = reader.GetString(1);
article.Marque = reader.GetString(2);
article.Famille = reader.GetString(3);
article.SousFamille = reader.GetString(4);
article.PrixHT = float.Parse(reader.GetString(5));
article.Quantite = reader.GetInt32(6);
}
reader.Close();
reader.Dispose();
sqlConn.Close();
return(article);
}
public int Select()
{
try
{
if (Common.Conn == null)
{
Common.Conn = new SQLiteConnection(Common.ConnectionString);
Common.Conn.Open();
}
SQLiteCommand Com = Common.Conn.CreateCommand();
Com.CommandText = tblModbusServerPoint.SQL_Select;
Com.Parameters.AddRange(GetSqlParameters());
SQLiteDataReader rs = Com.ExecuteReader();
for (
; rs.Read();
)
{
AddFromRecordSet(rs);
}
rs.Close();
rs.Dispose();
Com.Dispose();
return(0);
}
catch (SQLiteException ex)
{
return(ex.ErrorCode);
}
}
public List <string> GetUsers()
{
List <string> users = new List <string>();
SQLiteCommand cmd = conn.CreateCommand();
cmd.CommandText = "select id,username,type from user order by lastLoginTime desc";
SQLiteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
users.Add(reader.GetString(1));
}
reader.Dispose();
if (users.Count == 0)
{
cmd.CommandText = string.Format("insert into user(type,username,password) values(0, '{0}', '{1}')", "admin", CryptoHelper.EncryptAes("admin", CryptoHelper.AesKey));
cmd.ExecuteNonQuery();
cmd.CommandText = string.Format("insert into user(type,username,password) values(1, '{0}', '{1}')", "user", CryptoHelper.EncryptAes("user", CryptoHelper.AesKey));
cmd.ExecuteNonQuery();
users.Add("admin");
users.Add("user");
}
cmd.Dispose();
return(users);
}
