public void TestCaseSensitiveKeyColumn()
{
var path = Path.GetTempFileName();
try
{
var sqlite = new System.Data.SQLite.SQLiteConnection("Data Source=" + path);
sqlite.Open();
var cmd = sqlite.CreateCommand();
cmd.CommandText = "create table test(col_ID integer primary key, name text, shape blob)";
cmd.ExecuteNonQuery();
cmd.Dispose();
sqlite.Close();
sqlite.Dispose();
using (var sq = new ManagedSpatiaLite("Data Source=" + path, "test", "shape", "COL_ID"))
{
var ext = new Envelope();
var ds = new SharpMap.Data.FeatureDataSet();
sq.ExecuteIntersectionQuery(ext, ds);
NUnit.Framework.Assert.AreEqual(0, ds.Tables[0].Count);
}
}
catch (Exception ex)
{
Assert.Fail("Got exception, should not happen");
}
finally
{
File.Delete(path);
}
}
public static void FindOpcodes(string query)
{
var files = System.IO.Directory.GetFiles(@"E:\HFS\WOWDEV\SNIFFS_CLEAN\", "*.sqlite", System.IO.SearchOption.AllDirectories).OrderByDescending(t => t);
foreach (var file in files)
{
using (var con = new System.Data.SQLite.SQLiteConnection("Data Source=" + file))
{
con.Open();
using (var sqlcommand = con.CreateCommand())
{
sqlcommand.CommandText = "select count(*) from packets where opcode in (" + query + ")";
var reader = sqlcommand.ExecuteReader();
while (reader.Read())
{
var found = reader.GetInt32(0);
if (found > 0)
{
System.Diagnostics.Debug.WriteLine(file + "\t" + found);
}
break;
}
}
con.Close();
}
}
}
public void UpdateDatabaseWithArteProps( string ConnectionString )
{
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection( ConnectionString );
conn.Open();
System.Data.SQLite.SQLiteTransaction transaction = conn.BeginTransaction();
System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand( conn );
command.Transaction = transaction;
foreach ( var a in ArteList ) {
string UpdateNames = "UPDATE Text SET IdentifyString = \"" + a.Type.ToString() + ";\" || IdentifyString WHERE IdentifyString LIKE \"%[" + a.NameStringDicId + " / 0x" + a.NameStringDicId.ToString( "X6" ) + "]\"";
Console.WriteLine( UpdateNames );
command.CommandText = UpdateNames;
command.ExecuteNonQuery();
string UpdateDescs = "UPDATE Text SET IdentifyString = \"Description;\" || IdentifyString WHERE IdentifyString LIKE \"%[" + a.DescStringDicId + " / 0x" + a.DescStringDicId.ToString( "X6" ) + "]\"";
Console.WriteLine( UpdateDescs );
command.CommandText = UpdateDescs;
command.ExecuteNonQuery();
if ( a.Type == Arte.ArteType.Generic ) {
string UpdateStatus = "UPDATE Text SET status = 4, updated = 1, updatedby = \"[HyoutaTools]\", updatedtimestamp = " + Util.DateTimeToUnixTime( DateTime.UtcNow ) + " WHERE IdentifyString LIKE \"%[" + a.NameStringDicId + " / 0x" + a.NameStringDicId.ToString( "X6" ) + "]\"";
Console.WriteLine( UpdateStatus );
command.CommandText = UpdateStatus;
command.ExecuteNonQuery();
}
}
command.Dispose();
transaction.Commit();
conn.Close();
conn.Dispose();
}
public DatabaseManager()
{
string createTableQuery = @"CREATE TABLE IF NOT EXISTS LASTPROJECTS (
[ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[address] VARCHAR(2048) NULL
)";
string path = Path.
GetDirectoryName(Path.GetDirectoryName(System.IO.Directory.GetCurrentDirectory()));
path = path + @"\bin\Debug\databaseFile.db3";
if(!File.Exists (path))
System.Data.SQLite.SQLiteConnection.CreateFile("databaseFile.db3");
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=databaseFile.db3"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
com.CommandText = createTableQuery;
com.ExecuteNonQuery();
con.Close();
}
}
Console.WriteLine("ALLO");
}
public static int Execute( List<string> args )
{
// 0xCB20
if ( args.Count != 2 ) {
Console.WriteLine( "Generates a scenario db for use in Tales.Vesperia.Website from a MAPLIST.DAT." );
Console.WriteLine( "Usage: maplist.dat scenario.db" );
return -1;
}
String maplistFilename = args[0];
string connStr = "Data Source=" + args[1];
using ( var conn = new System.Data.SQLite.SQLiteConnection( connStr ) ) {
conn.Open();
using ( var ta = conn.BeginTransaction() ) {
SqliteUtil.Update( ta, "CREATE TABLE descriptions( filename TEXT PRIMARY KEY, shortdesc TEXT, desc TEXT )" );
int i = 0;
foreach ( MapName m in new MapList( System.IO.File.ReadAllBytes( maplistFilename ) ).MapNames ) {
Console.WriteLine( i + " = " + m.ToString() );
List<string> p = new List<string>();
p.Add( "VScenario" + i );
p.Add( m.Name1 != "dummy" ? m.Name1 : m.Name3 );
p.Add( m.Name1 != "dummy" ? m.Name1 : m.Name3 );
SqliteUtil.Update( ta, "INSERT INTO descriptions ( filename, shortdesc, desc ) VALUES ( ?, ?, ? )", p );
++i;
}
ta.Commit();
}
conn.Close();
}
return 0;
}
public List<String> checkProjects()
{
List<String> list = new List<String>();
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=databaseFile.db3"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
com.CommandText = "Select * FROM LASTPROJECTS";
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(reader["address"]);
if (!File.Exists(reader["address"].ToString()))
list.Add(reader["address"].ToString());
}
}
con.Close();
}
}
if(list.Count > 0)
removeProject(list);
return getLastProjects();
}
public static void DumpOpcodes()
{
var files = System.IO.Directory.GetFiles(@"E:\HFS\WOWDEV\SNIFFS_CLEAN\", "*.sqlite", System.IO.SearchOption.AllDirectories).OrderBy(t => t);
var versionOpcodeList = new System.Collections.Generic.SortedList<uint, ClientBuildCache>();
foreach (var file in files)
{
uint clientBuild = 0;
using (var con = new System.Data.SQLite.SQLiteConnection("Data Source=" + file))
{
con.Open();
using (var sqlcommand = con.CreateCommand())
{
sqlcommand.CommandText = "select key, value from header where key = 'clientBuild'";
var reader = sqlcommand.ExecuteReader();
while (reader.Read())
{
clientBuild = (uint)reader.GetInt32(1);
break;
}
}
if (!versionOpcodeList.ContainsKey(clientBuild))
{
versionOpcodeList.Add(clientBuild, new ClientBuildCache() { ClientBuild = clientBuild, OpcodeList = new List<OpcodeCache>() });
}
var clientBuildOpcodes = versionOpcodeList[clientBuild];
using (var sqlcommand = con.CreateCommand())
{
sqlcommand.CommandText = "select distinct opcode, direction from packets order by opcode , direction";
var reader = sqlcommand.ExecuteReader();
while (reader.Read())
{
var opcode = (uint)reader.GetInt32(0);
var direction = (byte)reader.GetInt32(1);
if (!clientBuildOpcodes.OpcodeList.Exists(t => t.Opcode == opcode && t.Direction == direction))
clientBuildOpcodes.OpcodeList.Add(new OpcodeCache() { Direction = direction, Opcode = opcode });
}
}
con.Close();
}
}
var clientBuildOpcodeList = versionOpcodeList.Select(t => t.Value).ToList();
clientBuildOpcodeList.SaveObject("clientBuildOpcodeList.xml");
}
static void ExecuteDDL()
{
var path = System.IO.Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "sample.sqlite");
System.Data.SQLite.SQLiteConnection.CreateFile(path);
var cnStr = new System.Data.SQLite.SQLiteConnectionStringBuilder() { DataSource = path };
using (var cn = new System.Data.SQLite.SQLiteConnection(cnStr.ToString()))
{
cn.Open();
// テーブル名は複数形で指定する(Memberではなく、Members)
var sql = "CREATE TABLE Members (Id INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT, Address TEXT, TelNo TEXT); ";
sql += "CREATE TABLE Items (Id INTEGER PRIMARY KEY AUTOINCREMENT, Price INTEGER, MemberId INTEGER, Name TEXT, SoldAt datetime, FOREIGN KEY(MemberId) REFERENCES Members(Id))";
var cmd = new System.Data.SQLite.SQLiteCommand(sql, cn);
cmd.ExecuteNonQuery();
cn.Close();
}
}
private void btn_cerca_Click(object sender, EventArgs e)
{
if (!tb_nome.Text.ToString().Equals("") && tb_cognome.Text.ToString().Equals(""))
{
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=gestionalePalestra.db"))
{
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open();
tableSearch.Clear();
string command = "Select Ntessera,Nome,Cognome,CodFisc,DataN,Residenza from Iscritto where UPPER(Nome)=UPPER('" + tb_nome.Text.ToString() + "') ORDER BY " + order + ";";
cmd.CommandText = command;
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
tableSearch.Rows.Add(reader["Ntessera"], reader["Nome"], reader["Cognome"], reader["CodFisc"], reader["DataN"], reader["Residenza"]);
}
}
conn.Close();
}
}
}
if (tb_nome.Text.ToString().Equals("") && !tb_cognome.Text.ToString().Equals(""))
{
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=gestionalePalestra.db"))
{
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open();
tableSearch.Clear();
string command = "Select Ntessera,Nome,Cognome,CodFisc,DataN,Residenza from Iscritto where UPPER(Cognome)=UPPER('" + tb_cognome.Text.ToString() + "') ORDER BY " + order + ";";
cmd.CommandText = command;
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
tableSearch.Rows.Add(reader["Ntessera"], reader["Nome"], reader["Cognome"], reader["CodFisc"], reader["DataN"], reader["Residenza"]);
}
}
conn.Close();
}
}
}
if (!tb_nome.Text.ToString().Equals("") && !tb_cognome.Text.ToString().Equals(""))
{
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=gestionalePalestra.db"))
{
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open();
tableSearch.Clear();
string command = "Select Ntessera,Nome,Cognome,DataN,CodFisc,Residenza from Iscritto where UPPER(Nome)=UPPER('" + tb_nome.Text.ToString() + "') and UPPER(Cognome)=UPPER('" + tb_cognome.Text.ToString() + "') ORDER BY " + order + ";";
cmd.CommandText = command;
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
tableSearch.Rows.Add(reader["Ntessera"], reader["Nome"], reader["Cognome"], reader["CodFisc"], reader["DataN"], reader["Residenza"]);
}
}
conn.Close();
}
}
}
if (!tb_nome.Text.ToString().Equals("") && !tb_cognome.Text.ToString().Equals(""))
{
LoadTable();
}
}
public void Close()
{
_connection.Close();
_isOpen = false;
}
private void button1_Click_1(object sender, EventArgs e)
{
bool edadp = textBox2.Text.All(Char.IsNumber);
float pesov;
bool pesop = float.TryParse(textBox6.Text, out pesov);
if (textBox1.Text != "" && textBox2.Text != "" && textBox3.Text != "" && textBox4.Text != "" && textBox5.Text != "" && textBox6.Text != "" && textBox7.Text != "" && textBox8.Text != "" && textBox9.Text != "" && textBox10.Text != "" && textBox11.Text != "" && textBox12.Text != "" && textBox13.Text != "" && textBox14.Text != "" && textBox15.Text != "" && textBox16.Text != "" && textBox17.Text != "" && textBox18.Text != "" && textBox19.Text != "" && textBox20.Text != "" && textBox21.Text != "" && textBox22.Text != "")
{
if (edadp == true)
{
if (pesop == true)
{
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
System.Data.SQLite.SQLiteConnection sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\EXCL.s3db ;Version=3;");
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
//comando sql para insercion
cmd.CommandText = "UPDATE Expediente Set Nombre = '" + textBox1.Text + "' , Sexo = '" + comboBox1.Text + "', Edad = '" + textBox2.Text + "', Ocupacion = '" + textBox4.Text + "', Estadocivil = '" + comboBox2.Text + "', Religion = '" + textBox3.Text + "', TA = '" + textBox5.Text + "', Peso = '" + textBox6.Text + "', Tema = '" + textBox7.Text + "',FC = '" + textBox8.Text + "', FR = '" + textBox9.Text + "', EnfermedadesFamiliares = '" + textBox10.Text + "', AreaAfectada = '" + comboBox3.Text + "', Antecedentes = '" + textBox11.Text + "', Habitos = '"+textBox13.Text+"', GPAC = '"+comboBox4.Text+"', FUMFUP = '"+comboBox5.Text+"', Motivo = '"+textBox14.Text+"', CuadroClinico = '"+textBox15.Text+"', ID = '"+textBox16.Text+"', EstudiosSolicitados = '"+textBox17.Text+"', TX = '"+textBox18.Text+"', PX = '"+textBox19.Text+"', Doctor = '"+textBox20.Text+"', CP = '"+textBox21.Text+"', SSA = '"+textBox22.Text+"' Where Folio =" + foliom + "";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
this.Close();
}
else
{
MessageBox.Show("Solo introduzca numeros en el peso", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Solo introduzca numeros en la edad", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
else
{
MessageBox.Show("Ha dejado campos en blanco", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private RaffleStatus isActive()
{
RaffleStatus status = RaffleStatus.Active;
string sqlQueryRead = "select * from LotObjectsInWorld where ObjectGuid = '" + objectGuid + "'";
Boolean objectRegistered = false;
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=" + HttpContext.Current.Server.MapPath("~/App_Data/Raffle.db")))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); // Open the connection to the database
com.CommandText = sqlQueryRead; // Set CommandText to our query that will create the table
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
objectRegistered = true;
if (reader["LotDrawNumber"] != System.DBNull.Value && reader["LotDrawNumber"].ToString() != string.Empty && Int32.Parse(reader["LotDrawNumber"].ToString()) != 0)
{
string sqlReadDuration = "select * from LotDrawHistory where ObjectGuid = '" + objectGuid + "' and LotDrawNumber = '" + reader["LotDrawNumber"].ToString() + "'";
reader.Close();
com.CommandText = sqlReadDuration;
using (System.Data.SQLite.SQLiteDataReader reader1 = com.ExecuteReader())
{
if (reader1.Read())
{
// check for duration here
int duration = Int32.Parse(reader1["Duration"].ToString());
DateTime LotStartDateTime = DateTime.Parse(reader1["LotStartDateTime"].ToString());
if (duration != -1 && DateTime.Now > LotStartDateTime.AddMinutes(duration))
{
pageResponse.Write("ACTIVEBUTCLOSED");
status = RaffleStatus.ActiveClosed;
}
else
{
pageResponse.Write("ACTIVE");
status = RaffleStatus.Active;
}
pageResponse.Flush();
pageResponse.SuppressContent = true;
con.Close();
return(status);
}
}
}
else
{
pageResponse.Write("INACTIVE");
pageResponse.Flush();
pageResponse.SuppressContent = true;
con.Close();
return(RaffleStatus.InActive);
}
}
}
}
if (!objectRegistered)
{
pageResponse.Write("UNREGISTERED");
pageResponse.Flush();
pageResponse.SuppressContent = true;
con.Close();
return(RaffleStatus.Unregistered);
}
}
return(RaffleStatus.InActive);
}
static int Main(string[] args)
{
//1. Logger initialization
Logger logger = LogManager.GetCurrentClassLogger();
int TimePart = 0;
decimal PercentagePart = 0M;
bool? RunSettingsPart = null;
bool? HDDCheckSettingsPart = null;
if (args.Length > 0)
{
if (args.Where(x => x.ToLower() == "-help").ToList().Count > 0)
{
logger.Info("-----------------------------------------------------------------------------");
logger.Info("-t for Time setting in mins");
logger.Info("-p for Percentage setting");
logger.Info("-r for Run setting (True/False)");
logger.Info("-h for HDD Check (True/False)");
logger.Info("Example : >N3PS.File.Compare.exe -hFalse");
logger.Info("Meaning program will be running 30 mins, 100% random record picked up and New table to be created for processing or not.");
logger.Info("-----------------------------------------------------------------------------");
return(0);
}
try
{
var timePart = args.Where(x => x.ToLower().Contains("-t")).ToList();
if (timePart.Count > 0)
{
TimePart = Convert.ToInt32(timePart[0].ToLower().Replace("-t", ""));
logger.Info($"TimePart Argument Value : {TimePart}");
}
var percentagePart = args.Where(x => x.ToLower().Contains("-p")).ToList();
if (percentagePart.Count > 0)
{
PercentagePart = Convert.ToDecimal(percentagePart[0].ToLower().Replace("-p", ""));
logger.Info($"PercentagePart Argument Value : {PercentagePart}");
}
var runSettingsPart = args.Where(x => x.ToLower().Contains("-r")).ToList();
if (runSettingsPart.Count > 0)
{
RunSettingsPart = Convert.ToBoolean(runSettingsPart[0].ToLower().Replace("-r", ""));
logger.Info($"RunSettingsPart Argument Value : {RunSettingsPart}");
}
var runHddCheckPart = args.Where(x => x.ToLower().Contains("-h")).ToList();
if (runHddCheckPart.Count > 0)
{
HDDCheckSettingsPart = Convert.ToBoolean(runHddCheckPart[0].ToLower().Replace("-h", ""));
logger.Info($"HDDCheckSettingsPart Argument Value : {HDDCheckSettingsPart}");
}
}
catch (Exception excp)
{
logger.Error("Error in processing the command level arguments. " + excp.ToString() + " --- " + excp.StackTrace);
}
}
//2. XML File Initialization
string FlatFileXmlName = @".\XMLFiles\FileFormat.xml";
string SettingsXmlName = @".\XMLFiles\Settings.xml";
//string ValidationRuleXmlName = @".\XMLFiles\ValidationRule.xml";
//3. Convert FlatFile to C# objects
FlatFile flatFile = new FlatFile();
FlatFile fetchedFlatFileObj = flatFile.GetInstance(FlatFileXmlName, logger);
if (fetchedFlatFileObj == null)
{
logger.Error($"Error while loading the Flat File XML : {FlatFileXmlName}");
return(0);
}
//4.Convert Settings File to C# objects
SettingsFile settingsFile = new SettingsFile();
SettingsFile fetchedSettingsObj = settingsFile.GetInstance(SettingsXmlName, logger);
if (fetchedSettingsObj == null)
{
logger.Error($"Error while loading the Settings File XML : {SettingsXmlName}");
return(0);
}
if (TimePart != 0)
{
logger.Info($"Overidden Time Part from Settings.xml: {TimePart}");
fetchedSettingsObj.Time = TimePart;
}
if (PercentagePart != 0M)
{
logger.Info($"Overidden Percentage from Settings.xml: {PercentagePart}");
fetchedSettingsObj.Percentage = PercentagePart;
}
if (RunSettingsPart != null)
{
logger.Info($"Overidden Run Settings from Settings.xml: {RunSettingsPart}");
fetchedSettingsObj.NewRun = RunSettingsPart.Value;
}
logger.Info("Settings : Start ----------------------");
logger.Info($"Time : {fetchedSettingsObj.Time}");
logger.Info($"Percentage : {fetchedSettingsObj.Percentage}");
logger.Info($"NewRun : {fetchedSettingsObj.NewRun}");
logger.Info("Settings : END ----------------------");
////5. Convert ValidationRule to C# objects
//ValidationRuleFile validationRuleFile = new ValidationRuleFile();
//ValidationRuleFile fetchedValidationRuleObj = validationRuleFile.GetInstance(ValidationRuleXmlName, logger);
//if (fetchedValidationRuleObj == null)
//{
// logger.Error($"Error while loading the Validation Rule File XML : {ValidationRuleXmlName}");
// return 0;
//}
//var dllsDetails = fetchedValidationRuleObj.ValidationRules.Where(x => x.DLLInfo != null && !string.IsNullOrEmpty(x.DLLInfo.DLLName) ).ToList();
//Hashtable assemblyDetails = new Hashtable();
//if (dllsDetails.Count() > 0)
//{
// foreach (ValidationsRule rule in dllsDetails)
// {
// FileInfo f = new FileInfo(@".\ExternalDLLs\" + rule.DLLInfo.DLLName);
// logger.Info($"Full File Name : {f.FullName}");
// if (!System.IO.File.Exists(f.FullName))
// {
// logger.Error($"External DLL is not exist {rule.DLLInfo.DLLName} in ExternalDLLs folder.");
// return 0;
// }
// else
// {
// Assembly assembly = Assembly.LoadFile(f.FullName);
// assemblyDetails.Add(rule.ColumnNumber, assembly);
// }
// }
//}
//6. HDD Size Check
if (HDDCheckSettingsPart == null)
{
HDDCheckSettingsPart = true;
}
//Check for free space
if (HDDCheckSettingsPart.Value)
{
HDDCheck check = new HDDCheck();
bool isFreeSpaceAvailable = check.IsEnoughSpaceAvailable(fetchedFlatFileObj.FlatFilePath1, logger);
if (!isFreeSpaceAvailable)
{
return(0);
}
}
else
{
logger.Info("HDD Check is Skipped.");
}
//logger.Info($"Flat file Path : {fetchedFlatFileObj.FlatFilePath}");
string DBName = "ICA";
SQLiteHelper sqlManipulation = new SQLiteHelper();
bool isDBExist = sqlManipulation.IsDBExist(DBName);
if (!isDBExist)
{
sqlManipulation.CreateDB(DBName, logger);
}
//SQLiteHelper sqlLite = new SQLiteHelper();
System.Data.SQLite.SQLiteConnection m_dbConnection = sqlManipulation.OpenDBConnection1(DBName);
string tableName1 = "ICATable1";
string tableName2 = "ICATable2";
string tableNameDeleted1 = "ICADeletedTable1";
string tableNameInserted2 = "ICAInsertedTable2";
string CreateDeletedTableSQLQuery1 = flatFile.GetFlatFileDeletedTableScript(tableNameDeleted1, fetchedFlatFileObj);
string CreateInsertedTableSQLQuery2 = flatFile.GetFlatFileInsertTableScript(tableNameInserted2, fetchedFlatFileObj);
string processedtableName1 = "ProcessedICATable1";
string processedtableName2 = "ProcessedICATable2";
FileHelper helper = new FileHelper();
if (fetchedSettingsObj.NewRun)
{
string CreateTableSQLQuery1 = flatFile.GetFlatFileTableScript(tableName1, fetchedFlatFileObj);
string CreateTableSQLQuery2 = flatFile.GetFlatFileTableScript(tableName2, fetchedFlatFileObj);
string CreateProcessedTableSQLQuery1 = flatFile.CreateFlatFileTableScript(processedtableName1);
string CreateProcessedTableSQLQuery2 = flatFile.CreateFlatFileTableScript(processedtableName2);
bool isExist = sqlManipulation.CheckTableExists(m_dbConnection, tableName1, logger);
if (isExist)
{
sqlManipulation.DeleteTable(m_dbConnection, DBName, tableName1, logger);
}
sqlManipulation.CreateTable(m_dbConnection, DBName, CreateTableSQLQuery1, logger);
isExist = sqlManipulation.CheckTableExists(m_dbConnection, tableName2, logger);
if (isExist)
{
sqlManipulation.DeleteTable(m_dbConnection, DBName, tableName2, logger);
}
sqlManipulation.CreateTable(m_dbConnection, DBName, CreateTableSQLQuery2, logger);
//sqlManipulation.DeleteTable(DBName, processedtableName1, logger);
//sqlManipulation.DeleteTable(DBName, processedtableName2, logger);
//sqlManipulation.CreateTable(DBName, CreateTableSQLQuery1, logger);
//sqlManipulation.CreateTable(DBName, CreateTableSQLQuery2, logger);
isExist = sqlManipulation.CheckTableExists(m_dbConnection, processedtableName1, logger);
if (isExist)
{
sqlManipulation.DeleteTable(m_dbConnection, DBName, processedtableName1, logger);
}
sqlManipulation.CreateTable(m_dbConnection, DBName, CreateProcessedTableSQLQuery1, logger);
isExist = sqlManipulation.CheckTableExists(m_dbConnection, processedtableName2, logger);
if (isExist)
{
sqlManipulation.DeleteTable(m_dbConnection, DBName, processedtableName2, logger);
}
sqlManipulation.CreateTable(m_dbConnection, DBName, CreateProcessedTableSQLQuery2, logger);
isExist = sqlManipulation.CheckTableExists(m_dbConnection, tableNameDeleted1, logger);
if (isExist)
{
sqlManipulation.DeleteTable(m_dbConnection, DBName, tableNameDeleted1, logger);
}
sqlManipulation.CreateTable(m_dbConnection, DBName, CreateDeletedTableSQLQuery1, logger);
isExist = sqlManipulation.CheckTableExists(m_dbConnection, tableNameInserted2, logger);
if (isExist)
{
sqlManipulation.DeleteTable(m_dbConnection, DBName, tableNameInserted2, logger);
}
sqlManipulation.CreateTable(m_dbConnection, DBName, CreateInsertedTableSQLQuery2, logger);
//sqlManipulation.CreateTable(DBName, CreateProcessedTableSQLQuery1, logger);
//sqlManipulation.CreateTable(DBName, CreateProcessedTableSQLQuery2, logger);
//sqlManipulation.CloseDBConnection(m_dbConnection);
//var conn = new SQLiteConnection( "foofoo");
List <DynamicClass1> objList = new List <DynamicClass1>();
//SQLiteConnection m_dbConnection1 = sqlManipulation.OpenDBConnection(DBName);
// m_dbConnection1.CreateTable<DynamicClass1>();
helper.InsertInto(sqlManipulation, fetchedFlatFileObj, fetchedSettingsObj, m_dbConnection, DBName, tableName1, processedtableName1, fetchedFlatFileObj.FlatFilePath1, logger, objList, true);
List <DynamicClass2> objList1 = new List <DynamicClass2>();
helper.InsertInto(sqlManipulation, fetchedFlatFileObj, fetchedSettingsObj, m_dbConnection, DBName, tableName2, processedtableName2, fetchedFlatFileObj.FlatFilePath2, logger, objList1, false);
}
int table1Records = sqlManipulation.GetTotalRecordsInTable1(m_dbConnection, tableName1, logger);
//set
logger.Info("Comaprison is started.");
int table2Records = sqlManipulation.GetTotalRecordsInTable1(m_dbConnection, tableName2, logger);
//List< ProcessedDetails1> list1 = m_dbConnection.Query<ProcessedDetails1>($"SELECT IsError, Count(1) AS TotalRecords FROM {processedtableName1} GROUP BY IsError");
m_dbConnection.Close();
Thread.Sleep(1000);
m_dbConnection = null;
GC.Collect();
GC.WaitForPendingFinalizers();
System.Data.SQLite.SQLiteConnection m_dbConnection1 = sqlManipulation.OpenDBConnection1(DBName);
//m_dbConnection1.Open();
var list1 = sqlManipulation.GetTotalRecordsInTable(m_dbConnection1, $"SELECT IsError, Count(1) AS TotalRecords FROM {processedtableName1} GROUP BY IsError", logger);
//m_dbConnection1.Open();
helper.Compare(fetchedFlatFileObj, fetchedSettingsObj, sqlManipulation, m_dbConnection1, DBName, tableName1, tableName2, processedtableName1, processedtableName2, tableNameDeleted1, tableNameInserted2, table1Records, list1, logger);
int tab1 = sqlManipulation.GetTotalRecordsInTable1(m_dbConnection1, tableName1, logger);
int tab2 = sqlManipulation.GetTotalRecordsInTable1(m_dbConnection1, tableName2, logger);
int insTab = sqlManipulation.GetTotalRecordsInTable1(m_dbConnection1, tableNameInserted2, logger);
int delTab = sqlManipulation.GetTotalRecordsInTable1(m_dbConnection1, tableNameDeleted1, logger);
int fetchedTab1 = sqlManipulation.GetTotalRecordsInTable1(m_dbConnection1, processedtableName1, logger);
int fetchedTab2 = sqlManipulation.GetTotalRecordsInTable1(m_dbConnection1, processedtableName2, logger);
logger.Info("-----------------------Report--------------------------------");
logger.Info($"{tableName1} : {tab1}");
logger.Info($"{tableName2} : {tab2}");
logger.Info($"{tableNameInserted2} : {insTab}");
logger.Info($"{tableNameDeleted1} : {delTab}");
logger.Info($"{processedtableName1} : {fetchedTab1}");
logger.Info($"{processedtableName2} : {fetchedTab2}");
logger.Info("-------------------------------------------------------");
helper.WriteToFile(sqlManipulation, fetchedFlatFileObj, m_dbConnection1, DBName, tableNameInserted2, logger, insTab);
helper.WriteToFile(sqlManipulation, fetchedFlatFileObj, m_dbConnection1, DBName, tableNameDeleted1, logger, delTab);
sqlManipulation.CloseDBConnection(m_dbConnection1);
/*DataSet dsTotalRecords = sqlManipulation.GetTotalRecords(m_dbConnection, tableName, logger);
* FileHelper helper = new FileHelper();
* ProcessedDetails processedDetails = helper.ValidateFile(fetchedFlatFileObj, fetchedSettingsObj, fetchedValidationRuleObj, sqlManipulation, m_dbConnection, DBName, tableName, assemblyDetails, dsTotalRecords, logger);
*
* DataSet dsTotalRecords1 = sqlManipulation.GetTotalRecords(m_dbConnection, tableName, logger);
* int totalRecords = 0;
* int totalError = 0;
* int totalSuccessProcessed = 0;
* if (dsTotalRecords1 != null)
* {
* DataTable dt = dsTotalRecords1.Tables[0];
* foreach(DataRow dr in dt.Rows)
* {
* int tr = 0;
* if(dr["TotalRecords"] != DBNull.Value)
* {
* tr = Convert.ToInt32(dr["TotalRecords"].ToString());
* if (dr["IsError"] != DBNull.Value && Convert.ToBoolean(dr["IsError"].ToString()))
* {
* totalError = totalError + tr;
* }
* else
* {
*
* totalSuccessProcessed = totalSuccessProcessed + tr;
*
* }
*
* totalRecords = totalRecords + tr;
* }
* }
* }
* logger.Info("------------------------------------------------------------");
* logger.Info($"Total Records: " + processedDetails.TotalRecords);
* logger.Info($"Total Records Processed: " + totalRecords);//(processedDetails.TotalErrorRecords + processedDetails.TotalSeccessfullyProcessedRecords));
* logger.Info($"Total Error Records: " + totalError);// processedDetails.TotalErrorRecords);
* logger.Info($"Total Successfully Processed Records: " + totalSuccessProcessed);// processedDetails.TotalSeccessfullyProcessedRecords);
* logger.Info("------------------------------------------------------------");
* sqlManipulation.CloseDBConnection(m_dbConnection);
*
* //sqlLite.CreateTable(DBName, CreateTableSQLQuery, logger);
* //sqlLite.InsertRecord(DBName, tableName, 1, "Nothing", logger);
* //DataSet dt = sqlLite.RetrieveRecord(DBName, tableName, 1, logger);
* //FileHelper f = new FileHelper();
* //f.ValidateFile(@"C:\Users\vishal.chilka\Desktop\ZSB120OM.OUT");
*
* return 0;*/
// sqlManipulation.CloseDBConnection(m_dbConnection);
return(0);
}
/// <summary>
/// Function that export historical price (high, low, close) for the pair
/// <paramref name="ccyBase"/> VS <paramref name="ccyPair"/>.
/// It export it in a SQLite database, located in the the Project Folder,
/// in the ../static/databases folder
/// </summary>
/// <param name="ccyBase">the name of the base cryptocurrency.</param>
/// <param name="ccyPair">the name of the paire cryptocurrency.</param>
/// <param name="nbDays">the length of the history.</param>
/// <returns>
/// A tuple containing, in this order, the binary sucess (true or false),
/// and eventually a message explaining why the order failed
/// </returns>
/// <example>
/// in BTC-EUR price :
/// BTC is the base currency
/// EUR is the paire currency
/// </example>
public (string, int) ExportHistoricalData(string ccyBase, string ccyPair, int nbDays)
{
System.Data.SQLite.SQLiteConnection mainConnexion = new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteCommand mainRequest;
string goodDate = DateTime.Now.ToString("dd-MM-yyy__HHmmss");
string dbName = "DataBase_" + ccyBase + "_" + ccyPair + "_" + nbDays + "_Days_" + goodDate + ".sqlite";
string dbPath = Tools.GetDirectory() + "Static/Databases/";
dbPath += dbName;
string tableName = ccyBase + "-" + ccyPair + "-" + nbDays.ToString() + "Days";
string request = "";
int response;
Dictionary <string, Dictionary <string, string> > BTCPrice;
int i;
// Création de la DB Sqlite3
System.Data.SQLite.SQLiteConnection.CreateFile(dbPath);
// Connection à la BD nouvellement crée
mainConnexion = new System.Data.SQLite.SQLiteConnection("Data Source=" + dbPath + ";Version=3");
mainConnexion.Open();
// Création d'un nouvelle table
request = "CREATE TABLE [" + tableName + "] (";
request += "id INTEGER PRIMARY KEY AUTOINCREMENT,";
request += "date VARCHAR(100),";
request += "open DOUBLE,";
request += "high DOUBLE,";
request += "low DOUBLE,";
request += "close DOUBLE,";
request += "volume INT";
request += ");";
mainRequest = new System.Data.SQLite.SQLiteCommand(request, mainConnexion);
response = mainRequest.ExecuteNonQuery();
// Historique des prix
BTCPrice = GetHistoricalData(ccyBase, ccyPair, nbDays);
// Export des prix historiques vers la base de données
i = 1;
foreach (var price in BTCPrice)
{
request = "INSERT INTO [" + tableName + "] VALUES (";
request += i.ToString() + ",";
request += "'" + price.Key.ToString() + "',";
request += price.Value["open"].ToString() + ",";
request += price.Value["high"].ToString() + ",";
request += price.Value["low"].ToString() + ",";
request += price.Value["close"].ToString() + ",";
request += (Double.Parse(price.Value["volumefrom"], System.Globalization.CultureInfo.InvariantCulture) + Double.Parse(price.Value["volumeto"], System.Globalization.CultureInfo.InvariantCulture)).ToString();
request += ");";
mainRequest = new System.Data.SQLite.SQLiteCommand(request, mainConnexion);
response = mainRequest.ExecuteNonQuery();
i++;
}
mainConnexion.Close();
return(dbName, response);
}
public string RicettarioDb()
{
var sb = new StringBuilder();
var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["RicettarioConnection"].ConnectionString;
using (var con = new System.Data.SQLite.SQLiteConnection(connectionString))
using (var com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
com.CommandText = "Select * FROM WeekSchedule";
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
sb.AppendLine(reader["Id"] + " : " + reader["Name"]);
}
}
con.Close(); // Close the connection to the database
}
return sb.ToString().Substring(0, 300);
}
private void SaveBadgeForItem(String itemPath, String badgePath, Boolean isClear = false) {
var m_dbConnection = new System.Data.SQLite.SQLiteConnection("Data Source=" + this._DBPath + ";Version=3;");
m_dbConnection.Open();
if (isClear) {
var command3 = new System.Data.SQLite.SQLiteCommand("DELETE FROM badges WHERE Path=@Path", m_dbConnection);
command3.Parameters.AddWithValue("Path", itemPath);
command3.ExecuteNonQuery();
} else {
var command1 = new System.Data.SQLite.SQLiteCommand("SELECT * FROM badges WHERE Path=@Path", m_dbConnection);
command1.Parameters.AddWithValue("Path", itemPath);
var Reader = command1.ExecuteReader();
var sql = Reader.Read()
? @"UPDATE badges SET Collection = @Collection, Badge = @Badge WHERE Path = @Path"
: @"INSERT INTO badges (Path, Collection, Badge) VALUES (@Path, @Collection, @Badge)";
var command2 = new System.Data.SQLite.SQLiteCommand(sql, m_dbConnection);
command2.Parameters.AddWithValue("Path", itemPath);
command2.Parameters.AddWithValue("Collection", Path.GetFileName(Path.GetDirectoryName(badgePath)));
command2.Parameters.AddWithValue("Badge", Path.GetFileName(badgePath));
command2.ExecuteNonQuery();
Reader.Close();
}
m_dbConnection.Close();
}
static void Main(string[] args)
{
if (File.Exists(config_fn))
{
StreamReader file = new StreamReader(config_fn);
user = file.ReadLine(); // instructions
user = file.ReadLine();
oauth = file.ReadLine();
channel = file.ReadLine();
}
Thread mainThread = new Thread(Program.MainThread);
System.Console.WriteLine("ShickenBot starting up!");
/* prep database */
bool first = false;
if (!File.Exists(database_fn))
{
System.Data.SQLite.SQLiteConnection.CreateFile(database_fn);
first = true;
System.Console.WriteLine("Initializing empty statistics database!");
}
con = new System.Data.SQLite.SQLiteConnection("data source=" + database_fn);
if (first)
{
/* create the table */
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
com.CommandText = @"CREATE TABLE IF NOT EXISTS [channels] (
[ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[channel_name] TEXT UNIQUE NOT NULL
)";
com.ExecuteNonQuery();
com.CommandText = @"CREATE TABLE IF NOT EXISTS [rounds] (
[ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[chan_id] INTEGER NOT NULL,
[began] TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
[time] INTEGER DEFAULT 0,
FOREIGN KEY (chan_id) REFERENCES channels(ID)
)";
com.ExecuteNonQuery();
com.CommandText = @"CREATE TABLE IF NOT EXISTS [players] (
[ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[chan_id] INTEGER NOT NULL,
[nickname] TEXT NOT NULL,
[points] INTEGER DEFAULT 0,
FOREIGN KEY (chan_id) REFERENCES channels(ID),
UNIQUE (chan_id, nickname) ON CONFLICT REPLACE
)";
com.ExecuteNonQuery();
com.CommandText = @"CREATE TABLE IF NOT EXISTS [guesses] (
[ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[round_id] INTEGER NOT NULL,
[user_id] TEXT NOT NULL,
[chan_id] INTEGER NOT NULL,
[t] TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
[time] INTEGER NOT NULL,
FOREIGN KEY (user_id) REFERENCES players(ID),
FOREIGN KEY (round_id) REFERENCES rounds(ID),
FOREIGN KEY (chan_id) REFERENCES channels(ID)
)";
com.ExecuteNonQuery();
con.Close();
}
}
else
{
long[] stat = stats();
System.Console.WriteLine("Loaded statistics database. " + stat[0] + " viewers, " + stat[1] + " rounds, " + stat[2] + " guesses tracked across " + stat[3] + " channels.");
}
/* launch chat */
mainThread.Start();
while (Console.Read() != 13)
{
;
}
}
public void setPlan(object o, System.EventArgs arg)
{
DataSet ds = new DataSet();
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=|DataDirectory|Madera.db"))
{
con.Open();
using (var cmd = con.CreateCommand())
{
listeOptions.Items.Clear();
string query = "SELECT module.id_module, module.nom, module.prix, plan_module.nb_module " +
"FROM module, plan_module, plan " +
"WHERE plan.id_modele_gamme = @idModele " +
"AND plan_module.id_plan = plan.id_plan " +
"AND plan_module.id_module = module.id_module " +
"AND plan_module.valeur=1 " +
"AND plan.nu_etage = @nuEtage;";
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@idModele", modeleMaison.SelectedValue);
cmd.Parameters.AddWithValue("@nuEtage", choixEtage.SelectedValue);
System.Data.SQLite.SQLiteDataAdapter da = new System.Data.SQLite.SQLiteDataAdapter(cmd);
ds.Clear();
da.Fill(ds);
int i = 0;
while (i < ds.Tables[0].Rows.Count)
{
string id_module = ds.Tables[0].Rows[i].ItemArray[0].ToString();
string nomModule = ds.Tables[0].Rows[i].ItemArray[1].ToString();
string prixModule = ds.Tables[0].Rows[i].ItemArray[2].ToString();
string quantite = ds.Tables[0].Rows[i].ItemArray[3].ToString();
listeOptions.Items.Add(new ListItem(quantite + " : " + nomModule + " - " + prixModule + "€", id_module));
i++;
}
con.Close();
}
}
DataSet ds2 = new DataSet();
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=|DataDirectory|Madera.db"))
{
con.Open();
using (var cmd = con.CreateCommand())
{
choixOption.Items.Clear();
string query = "SELECT module.id_module, module.nom, module.prix, plan_module.nb_module " +
"FROM module, plan_module, plan " +
"WHERE plan.id_modele_gamme = @idModele " +
"AND plan_module.id_plan = plan.id_plan " +
"AND plan_module.id_module = module.id_module " +
"AND plan_module.valeur=0 " +
"AND plan.nu_etage = @nuEtage;";
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@idModele", modeleMaison.SelectedValue);
cmd.Parameters.AddWithValue("@nuEtage", choixEtage.SelectedValue);
System.Data.SQLite.SQLiteDataAdapter da = new System.Data.SQLite.SQLiteDataAdapter(cmd);
ds2.Clear();
da.Fill(ds2);
int i = 0;
while (i < ds2.Tables[0].Rows.Count)
{
string id_module = ds2.Tables[0].Rows[i].ItemArray[0].ToString();
string nomModule = ds2.Tables[0].Rows[i].ItemArray[1].ToString();
string prixModule = ds2.Tables[0].Rows[i].ItemArray[2].ToString();
string quantite = ds2.Tables[0].Rows[i].ItemArray[3].ToString();
choixOption.Items.Add(new ListItem(quantite + " x " + nomModule + " - " + prixModule + "€", id_module));
i++;
}
con.Close();
}
}
DataSet ds3 = new DataSet();
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=|DataDirectory|Madera.db"))
{
con.Open();
using (var cmd = con.CreateCommand())
{
string query = "SELECT id_plan, image " +
"FROM plan " +
"WHERE nu_etage = @nuEtage " +
"AND id_modele_gamme = @idModele;";
cmd.CommandText = query;
cmd.Parameters.AddWithValue("@nuEtage", choixEtage.SelectedValue);
cmd.Parameters.AddWithValue("@idModele", modeleMaison.SelectedValue);
System.Data.SQLite.SQLiteDataAdapter da = new System.Data.SQLite.SQLiteDataAdapter(cmd);
da.Fill(ds3);
int i = 0;
while (i < ds3.Tables[0].Rows.Count)
{
plan.ImageUrl = "/Images/" + ds3.Tables[0].Rows[i].ItemArray[1].ToString();
plan.Height = 430;
plan.Width = 600;
Session["idPlan"] = ds3.Tables[0].Rows[i].ItemArray[0].ToString();
i++;
}
con.Close();
}
}
}
protected void btnValider_Click(object sender, EventArgs e)
{
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=|DataDirectory|Madera.db"))
{
con.Open();
using (var cmd = con.CreateCommand())
{
/* insertion du devis */
cmd.CommandText = "INSERT INTO devis(nom,date_devis,id_client,id_modele_gamme,id_adresse) " +
"VALUES(@nomProjet,@dateProjet,@idClient,@idModele,@idAdresse);";
cmd.Parameters.AddWithValue("@nomProjet", Session["nomProjet"].ToString());
cmd.Parameters.AddWithValue("@dateProjet", Session["dateProjet"].ToString());
cmd.Parameters.AddWithValue("@idClient", Session["idClient"].ToString());
cmd.Parameters.AddWithValue("@idModele", modeleMaison.SelectedValue);
cmd.Parameters.AddWithValue("@idAdresse", Session["idAdresse"].ToString());
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
/* récupération de l'id du devis */
DataSet ds = new DataSet();
cmd.CommandText = "SELECT devis.id_devis FROM devis " +
"WHERE nom=@nomDevis " +
"AND date_devis=@dateDevis " +
"AND id_client=@idClient " +
"AND id_modele_gamme=@idModele " +
"AND id_adresse=@idAdresse;";
cmd.Parameters.AddWithValue("@nomDevis", Session["nomProjet"].ToString());
cmd.Parameters.AddWithValue("@dateDevis", Session["dateProjet"].ToString());
cmd.Parameters.AddWithValue("@idClient", Session["idClient"].ToString());
cmd.Parameters.AddWithValue("@idModele", modeleMaison.SelectedValue);
cmd.Parameters.AddWithValue("@idAdresse", Session["idAdresse"].ToString());
System.Data.SQLite.SQLiteDataAdapter da = new System.Data.SQLite.SQLiteDataAdapter(cmd);
da.Fill(ds);
int i = 0;
while (i < ds.Tables[0].Rows.Count)
{
Session["idDevis"] = ds.Tables[0].Rows[i].ItemArray[0].ToString();
i++;
}
cmd.Parameters.Clear();
/* pour chaque étage */
foreach (ListItem item in choixEtage.Items)
{
/* récupération des caractéristiques du plan */
cmd.CommandText = "SELECT id_plan, nom, image " +
"FROM plan " +
"WHERE nu_etage = @nuEtage " +
"AND id_modele_gamme = @idModele;";
cmd.Parameters.AddWithValue("@nuEtage", item.Value);
cmd.Parameters.AddWithValue("@idModele", modeleMaison.SelectedValue);
da = new System.Data.SQLite.SQLiteDataAdapter(cmd);
ds.Tables.Clear();
da.Fill(ds);
i = 0;
cmd.Parameters.Clear();
while (i < ds.Tables[0].Rows.Count)
{
string idPlan = ds.Tables[0].Rows[i].ItemArray[0].ToString();
string nom = ds.Tables[0].Rows[i].ItemArray[1].ToString();
string image = ds.Tables[0].Rows[i].ItemArray[2].ToString();
/* insertion du plan au devis */
cmd.CommandText = "INSERT INTO devis_plan(nom, nu_etage, image, id_devis) " +
"VALUES(@nomDevis, @nuEtageDevis, @imageDevis, @idDevis);";
cmd.Parameters.AddWithValue("@nomDevis", nom);
cmd.Parameters.AddWithValue("@nuEtageDevis", item.Value);
cmd.Parameters.AddWithValue("@imageDevis", image);
cmd.Parameters.AddWithValue("@idDevis", Session["idDevis"].ToString());
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
/* récupération de l'id du plan qui vient d'être inséré */
DataSet ds2 = new DataSet();
cmd.CommandText = "SELECT id_devis_plan FROM devis_plan " +
"WHERE nom = @nomDevis " +
"AND nu_etage = @nuEtageDevis " +
"AND image = @imageDevis " +
"AND id_devis = @idDevis;";
cmd.Parameters.AddWithValue("@nomDevis", nom);
cmd.Parameters.AddWithValue("@nuEtageDevis", item.Value);
cmd.Parameters.AddWithValue("@imageDevis", image);
cmd.Parameters.AddWithValue("@idDevis", Session["idDevis"].ToString());
da = new System.Data.SQLite.SQLiteDataAdapter(cmd);
da.Fill(ds2);
int j = 0;
while (j < ds2.Tables[0].Rows.Count)
{
Session["idPlanDevis"] = ds2.Tables[0].Rows[j].ItemArray[0].ToString();
j++;
}
cmd.Parameters.Clear();
/* récupération des caractéristiques des modules */
cmd.CommandText = "SELECT module.id_module, plan_module.nb_module " +
"FROM module, plan_module, plan " +
"WHERE plan_module.id_plan = plan.id_plan " +
"AND plan_module.id_module = module.id_module " +
"AND plan_module.valeur=1 " +
"AND plan.id_plan = @idPlan;";
cmd.Parameters.AddWithValue("@idPlan", idPlan);
da = new System.Data.SQLite.SQLiteDataAdapter(cmd);
ds2.Tables.Clear();
da.Fill(ds2);
cmd.Parameters.Clear();
j = 0;
while (j < ds2.Tables[0].Rows.Count)
{
string id_module = ds2.Tables[0].Rows[j].ItemArray[0].ToString();
string quantite = ds2.Tables[0].Rows[j].ItemArray[1].ToString();
/* insertion des différents modules du plan de l'étage dans le devis */
cmd.CommandText = "INSERT INTO devis_module(id_plan, id_module, nb_module) " +
"VALUES(@idPlan, @idModule, @nbModule);";
cmd.Parameters.AddWithValue("@idPlan", Session["idPlanDevis"].ToString());
cmd.Parameters.AddWithValue("@idModule", id_module);
cmd.Parameters.AddWithValue("@nbModule", quantite);
cmd.ExecuteNonQuery();
j++;
}
i++;
}
}
cmd.Parameters.Clear();
/* récupération des caractéristiques du devis */
cmd.CommandText = "SELECT devis.nom as devisNom, devis.date_devis, " +
"client.nom, client.prenom, client.mail, client.telephone, " +
"adresse.numero, adresse.libelle, adresse.code_postal, adresse.ville, adresse.pays, " +
"modele_gamme.nb_etage, modele_gamme.forme, " +
"gamme.nom as gammeNom " +
"FROM devis, client, adresse, modele_gamme, gamme " +
"WHERE devis.id_devis = @idDevis " +
"AND devis.id_client = client.id_client " +
"AND devis.id_modele_gamme = modele_gamme.id_modele_gamme " +
"AND devis.id_adresse = adresse.id_adresse " +
"AND modele_gamme.id_gamme = gamme.id_gamme;";
cmd.Parameters.AddWithValue("idDevis", Session["idDevis"].ToString());
da = new System.Data.SQLite.SQLiteDataAdapter(cmd);
ds.Tables.Clear();
da.Fill(ds);
i = 0;
while (i < ds.Tables[0].Rows.Count)
{
string devisNom = ds.Tables[0].Rows[i].ItemArray[0].ToString();
string dateDevis = ds.Tables[0].Rows[i].ItemArray[1].ToString();
string nom = ds.Tables[0].Rows[i].ItemArray[2].ToString();
string prenom = ds.Tables[0].Rows[i].ItemArray[3].ToString();
string mail = ds.Tables[0].Rows[i].ItemArray[4].ToString();
string telephone = ds.Tables[0].Rows[i].ItemArray[5].ToString();
string numero = ds.Tables[0].Rows[i].ItemArray[6].ToString();
string libelle = ds.Tables[0].Rows[i].ItemArray[7].ToString();
string codePostal = ds.Tables[0].Rows[i].ItemArray[8].ToString();
string ville = ds.Tables[0].Rows[i].ItemArray[9].ToString();
string pays = ds.Tables[0].Rows[i].ItemArray[10].ToString();
string nb_etage = ds.Tables[0].Rows[i].ItemArray[11].ToString();
string forme = ds.Tables[0].Rows[i].ItemArray[12].ToString();
string gammeNom = ds.Tables[0].Rows[i].ItemArray[13].ToString();
using (StreamWriter sw = new StreamWriter(Server.MapPath("~/" + devisNom + "_" + nom + "_" + prenom + ".txt"), true))
{
sw.WriteLine("SOCIETE MADERA");
sw.WriteLine("----------");
sw.WriteLine("DEVIS :");
sw.WriteLine("Nom : " + devisNom);
sw.WriteLine("Créé le : " + dateDevis);
sw.WriteLine("----------");
sw.WriteLine("CLIENT :");
sw.WriteLine("Nom : " + nom);
sw.WriteLine("Prénom : " + prenom);
sw.WriteLine("Mail : " + mail);
sw.WriteLine("Téléphone : " + telephone);
sw.WriteLine("Adresse : " + numero + " " + libelle + ", " + codePostal + " " + ville + ", " + pays);
sw.WriteLine("----------");
sw.WriteLine("MAISON :");
sw.WriteLine("Gamme : " + gammeNom);
sw.WriteLine("Nombre étages : " + nb_etage);
sw.WriteLine("Forme : " + forme);
sw.WriteLine("----------");
sw.WriteLine("DETAILS :");
}
cmd.Parameters.Clear();
/* récupération des modules et de leur prix */
DataSet ds2 = new DataSet();
cmd.CommandText = "SELECT module.nom, module.prix, " +
"devis_module.nb_module, " +
"devis_plan.nu_etage " +
"FROM module, devis_module, devis_plan " +
"WHERE devis_module.id_module = module.id_module " +
"AND devis_module.id_plan = devis_plan.id_devis_plan " +
"AND devis_plan.id_devis = @idDevis;";
cmd.Parameters.AddWithValue("@idDevis", Session["idDevis"].ToString());
da = new System.Data.SQLite.SQLiteDataAdapter(cmd);
da.Fill(ds2);
int j = 0;
double prixTotal = 0;
while (j < ds2.Tables[0].Rows.Count)
{
string moduleNom = ds2.Tables[0].Rows[j].ItemArray[0].ToString();
double prix = Convert.ToDouble(ds2.Tables[0].Rows[j].ItemArray[1].ToString());
int nb_module = Convert.ToInt32(ds2.Tables[0].Rows[j].ItemArray[2].ToString());
string nu_etage = ds2.Tables[0].Rows[j].ItemArray[3].ToString();
prixTotal += prix * nb_module;
using (StreamWriter sw = new StreamWriter(Server.MapPath("~/" + devisNom + "_" + nom + "_" + prenom + ".txt"), true))
{
sw.WriteLine("Etage : " + nu_etage + " -> " + nb_module.ToString() + " " + moduleNom + " : " + (prix * nb_module).ToString() + "€");
}
j++;
}
using (StreamWriter sw = new StreamWriter(Server.MapPath("~/" + devisNom + "_" + nom + "_" + prenom + ".txt"), true))
{
sw.WriteLine("----------");
sw.WriteLine("COUT TOTAL : " + prixTotal + "€");
sw.WriteLine("..........");
}
i++;
}
}
con.Close();
}
Response.Redirect("/home");
}
private void btn_ok_Click(object sender, EventArgs e)
{
if (!tb_costo.Text.Equals(""))
{
int lastNTess;
addNewRata.setCosto(tb_costo.Text);
int tipoAbb = 0;
if (nud_Ningressi.Value != 0)
{
tipoAbb = 1; //abbonameno a ingressi
}
else
{
tipoAbb = 0;
} //abbonamento std
//if (checkUsefulFields() && checkCorsi() && DateTime.Compare(dtp_dataIn.Value, dtp_dataFin.Value) != -1 && DateTime.Compare(dtp_dataIn.Value, dtp_dataFin.Value) != 0)
if (checkUsefulFields() && checkCorsi())
{
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=gestionalePalestra.db"))
{
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open();
//-------------------------------------------------------------------QUERY INSERT ISCRITTI
string command = @"INSERT into Iscritto(Nome,Cognome,DataN,CodFisc,Residenza,Via,Recapito,Email,Ntessera,DataIn,DataFine,NIngressi,TipoAbb,Costo)
values ('" + tb_nome.Text.ToString() + "','" + tb_cognome.Text.ToString() + "','" + dtp_dataN.Value.ToString("yyyy-MM-dd")
+ "','" + tb_codFisc.Text.ToString() + "','" + tb_residenza.Text.ToString() + "','" + tb_via.Text.ToString() + "','" + tb_recapito.Text.ToString() + "','"
+ tb_mail.Text.ToString() + "','" + tb_nTessera.Text.ToString() + "','" + dtp_dataIn.Value.ToString("yyyy-MM-dd") + "','" + dtp_dataFin.Value.ToString("yyyy-MM-dd") + "','" + nud_Ningressi.Value
+ "','" + tipoAbb + "','" + sqliteRealTypeconversion(tb_costo.Text.ToString()) + "');";
cmd.CommandText = command;
cmd.ExecuteNonQuery();
//MessageBox.Show(command);
//-------------------------------------------------------------------QUERY INSERT FREQUENTA
cmd.CommandText = "SELECT CodIscritto FROM Iscritto WHERE CodIscritto = (SELECT MAX(CodIscritto) FROM Iscritto);";
cmd.ExecuteNonQuery();
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
reader.Read();
lastNTess = reader.GetInt32(0);
}
foreach (DataGridViewRow row in dgv_selCorsi.Rows)
{
if (Convert.ToBoolean(row.Cells[0].Value) == true)
{
command = "INSERT INTO Frequenta values ('" + lastNTess + "','" + row.Cells[2].Value + "')";
cmd.CommandText = command;
cmd.ExecuteNonQuery();
}
}
//-------------------------------------------------------------------QUERY INSERT RATE
cmd.CommandText = addNewRata.getRateQuery();
cmd.ExecuteNonQuery();
//-------------------------------------------------------------------QUERY INSERT CERTIFICATO
if (cb_certificato.Checked)
{
command = "INSERT INTO Certificato values('" + lastNTess + "','SI','" + dtp_scadenzaCert.Value.ToString("yyyy-MM-dd") + "')";
}
else
{
command = "INSERT INTO Certificato(CodIscritto,Presente) values('" + lastNTess + "','NO')";
}
cmd.CommandText = command;
cmd.ExecuteNonQuery();
conn.Close();
}
}
MessageBox.Show("Inserimento avvenuto con successo!", "Inserimento");
this.Close();
addNewRata.Close();
}
else
{
MessageBox.Show("Alcuni campi necessari non sono stati compilati oppure per favore ricontrolla se hai inserito correttamente i corsi."
+ "Ricorda che tutti devono essere iscritti a GENERALE!",
"Errore Inserimento");
}
}
else
{
MessageBox.Show("Alcuni campi necessari non sono stati compilati, per favore inserisci un costo abbonameno non nullo",
"Errore Inserimento");
}
}
public void removeProject(List<String> paths)
{
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=databaseFile.db3"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
for (int i = 0; i < paths.Count; i++)
{
com.CommandText = "DELETE FROM LASTPROJECTS WHERE address = '" + paths[i] + "'";
com.ExecuteNonQuery();
}
con.Close();
}
}
}
/// <summary>
/// Create database and fill with mock data.
///
/// If it exists, it will overwrite.
/// </summary>
public bool CreateDb()
{
try
{
DropDb();
}
catch (Exception ex) { }
// CreateFile either creates a file or overwrites it with an empty one if existent and deletes everything
try
{
System.Data.SQLite.SQLiteConnection.CreateFile(@"C:\sqlite\something.db");
}
catch (Exception ex) { }
using (System.Data.SQLite.SQLiteConnection connectDb = new System.Data.SQLite.SQLiteConnection("Data Source=C:\\sqlite\\something.db"))
{
using (System.Data.SQLite.SQLiteCommand cmdCreate = new System.Data.SQLite.SQLiteCommand(connectDb))
{
// 1. open connection
connectDb.Open();
// 2. execute table creation commands
cmdCreate.CommandText = "create table if not exists activities(" +
"id integer primary key autoincrement," +
"activity text not null);";
cmdCreate.ExecuteNonQuery();
cmdCreate.CommandText = "create table if not exists intensities(" +
"id integer primary key autoincrement," +
"intensity text not null);";
cmdCreate.ExecuteNonQuery();
cmdCreate.CommandText = "create table if not exists entries(" +
"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
"date text not null, " +
"activityid integer not null references activities(id), " +
"duration real not null, " +
"intensityid integer not null references intensities(id), " +
"exclude boolean, " +
"notes text);";
cmdCreate.ExecuteNonQuery();
// 3. execute data insertion commands into the already existing tables
// 3.1 intensity levels
try
{
cmdCreate.CommandText = "insert into intensities (" +
"id, intensity) values (" +
"1, 'Low');";
cmdCreate.ExecuteNonQuery();
cmdCreate.CommandText = "insert into intensities (" +
"id, intensity) values (" +
"2, 'Medium');";
cmdCreate.ExecuteNonQuery();
cmdCreate.CommandText = "insert into intensities (" +
"id, intensity) values (" +
"3, 'High');";
cmdCreate.ExecuteNonQuery();
}
catch (Exception ex) { }
// 3.2 activities
try {
foreach (Activity a in Data.Activities)
{
cmdCreate.CommandText = "insert into activities (" +
"id, activity) values (" +
a.Id.ToString() + ", '" +
a.Name + "');";
cmdCreate.ExecuteNonQuery();
}
}
catch (Exception ex) { }
// 3.3 entries
foreach (Entry e in Data.Entries)
{
cmdCreate.CommandText = "insert into entries (" +
"id, date, activityid, duration, intensityid, exclude, notes) values (" +
e.Id.ToString() + ", '" +
e.Date.ToString() + "', " +
e.ActivityId.ToString() + ", " +
e.Duration.ToString() + ", " +
"2, null, null);";
cmdCreate.ExecuteNonQuery();
}
// 4. closing connection
connectDb.Close();
}
return(true); // if success
}
}
public void Disconnect()
{
Connection.Close();
}
public void CreateUserDatabase()
{
// This is the query which will create a new table in our database file with three columns. An auto increment column called "ID", and two NVARCHAR type columns with the names "Key" and "Value"
var createTableQueries = new[] {@"CREATE TABLE IF NOT EXISTS [AspNetRoles] (
[Id] NVARCHAR (128) NOT NULL PRIMARY KEY,
[Name] NVARCHAR (256) NOT NULL
);",
@"CREATE TABLE IF NOT EXISTS [AspNetUsers] (
[Id] NVARCHAR (128) NOT NULL PRIMARY KEY,
[Email] NVARCHAR (256) NULL,
[EmailConfirmed] BIT NOT NULL,
[PasswordHash] NVARCHAR (4000) NULL,
[SecurityStamp] NVARCHAR (4000) NULL,
[PhoneNumber] NVARCHAR (4000) NULL,
[PhoneNumberConfirmed] BIT NOT NULL,
[TwoFactorEnabled] BIT NOT NULL,
[LockoutEndDateUtc] DATETIME NULL,
[LockoutEnabled] BIT NOT NULL,
[AccessFailedCount] INT NOT NULL,
[UserName] NVARCHAR (256) NOT NULL
);",
@"CREATE TABLE IF NOT EXISTS [AspNetUserRoles] (
[UserId] NVARCHAR (128) NOT NULL,
[RoleId] NVARCHAR (128) NOT NULL,
PRIMARY KEY ([UserId], [RoleId]),
FOREIGN KEY(UserId) REFERENCES AspNetUsers(Id) ON DELETE CASCADE,
FOREIGN KEY(RoleId) REFERENCES AspNetRoles(Id) ON DELETE CASCADE
);",
@"CREATE TABLE IF NOT EXISTS [AspNetUserLogins] (
[LoginProvider] NVARCHAR (128) NOT NULL,
[ProviderKey] NVARCHAR (128) NOT NULL,
[UserId] NVARCHAR (128) NOT NULL,
PRIMARY KEY ([LoginProvider], [ProviderKey], [UserId]),
FOREIGN KEY(UserId) REFERENCES AspNetUsers(Id) ON DELETE CASCADE
);",
@"CREATE TABLE IF NOT EXISTS [AspNetUserClaims] (
[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[UserId] NVARCHAR (128) NOT NULL,
[ClaimType] NVARCHAR (4000) NULL,
[ClaimValue] NVARCHAR (4000) NULL,
FOREIGN KEY(UserId) REFERENCES AspNetUsers(Id) ON DELETE CASCADE
);",
@"CREATE TABLE IF NOT EXISTS [__MigrationHistory] (
[MigrationId] NVARCHAR (150) NOT NULL,
[ContextKey] NVARCHAR (300) NOT NULL,
[Model] VARBINARY (4000) NOT NULL,
[ProductVersion] NVARCHAR (32) NOT NULL,
PRIMARY KEY ([MigrationId], [ContextKey])
);"};
System.Data.SQLite.SQLiteConnection.CreateFile(@"d:\work\Ricettario\Ricettario\App_Data ricettario.db3"); // Create the file which will be hosting our database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection(@"data source=d:\work\Ricettario\Ricettario\App_Data\ricettario.db3"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); // Open the connection to the database
foreach (var createTableQuery in createTableQueries)
{
com.CommandText = createTableQuery; // Set CommandText to our query that will create the table
com.ExecuteNonQuery(); // Execute the query
}
//com.CommandText = "Select * FROM MyTable"; // Select all rows from our database table
//using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
//{
// while (reader.Read())
// {
// Console.WriteLine(reader["Key"] + " : " + reader["Value"]); // Display the value of the key and value column for every row
// }
//}
con.Close(); // Close the connection to the database
}
}
}
/// <summary>
/// Returns a single entry for the provided ID.
/// </summary>
/// <param name="id">The ID for the entry to return.</param>
/// <returns>An entry.</returns>
public Entry GetEntry(int id)
{
Entry entry = new Entry();
using (System.Data.SQLite.SQLiteConnection connectDb = new System.Data.SQLite.SQLiteConnection("Data Source=C:\\sqlite\\something.db"))
{
using (System.Data.SQLite.SQLiteCommand cmdGetEntry = new System.Data.SQLite.SQLiteCommand(connectDb))
{
// 1. open connection
connectDb.Open();
// 2. set select command
try
{
cmdGetEntry.CommandText = "select * from entries where id = " + id.ToString() + ";";
// 3. using SQLiteDataReader
using (System.Data.SQLite.SQLiteDataReader readData = cmdGetEntry.ExecuteReader())
{
while (readData.Read())
{
entry.Id = Convert.ToInt32(readData[0]);
entry.Date = Convert.ToDateTime(readData[1]);
entry.ActivityId = Convert.ToInt32(readData[2]);
entry.Duration = Convert.ToDouble(readData[3]);
switch (Convert.ToInt32(readData[4]))
{
case 1:
entry.Intensity = Entry.IntensityLevel.Low;
break;
case 2:
entry.Intensity = Entry.IntensityLevel.Medium;
break;
case 3:
entry.Intensity = Entry.IntensityLevel.High;
break;
}
try
{
entry.Exclude = Convert.ToBoolean(readData[5]);
}
catch (Exception ex)
{
entry.Exclude = false;
}
try
{
entry.Notes = Convert.ToString(readData[6]);
}
catch (Exception ex)
{
entry.Notes = "";
}
}
}
}
catch (Exception ex)
{
// db file not found
}
// 4. close connection
connectDb.Close();
}
}
// Ensure that the activity property is not null.
if (entry.Activity == null)
{
entry.Activity = Data.Activities
.Where(a => a.Id == entry.ActivityId)
.SingleOrDefault();
}
return(entry);
}
private static void Test2(string connString)
{
Console.WriteLine("Begin Test2");
using (var dbConn = new System.Data.SQLite.SQLiteConnection(connString))
{
dbConn.Open();
using (System.Data.SQLite.SQLiteCommand cmd = dbConn.CreateCommand())
{
//create table
cmd.CommandText = @"CREATE TABLE IF NOT EXISTS T1 (ID integer primary key, T text);";
cmd.ExecuteNonQuery();
//parameterized insert - more flexibility on parameter creation
cmd.CommandText = @"INSERT INTO T1 (ID,T) VALUES(@id,@t)";
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@id",
Value = 1
});
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "@t",
Value = "test2"
});
cmd.ExecuteNonQuery();
//read from the table
cmd.CommandText = @"SELECT ID, T FROM T1";
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
long id = reader.GetInt64(0);
string t = reader.GetString(1);
Console.WriteLine("record read as id: {0} t: {1}", id, t);
}
}
}
if (dbConn.State != System.Data.ConnectionState.Closed) dbConn.Close();
}
Console.WriteLine("End Test2");
}
//public List<Activity> Activities { get; set; }
///// <summary>
///// The collection of entries.
///// </summary>
//public List<Entry> Entries { get; set; }
/// <summary>
/// Reading all entries from the database.
/// </summary>
public List <Entry> GetListForIndex()
{
List <Entry> received = new List <Entry>();
using (System.Data.SQLite.SQLiteConnection connectDb = new System.Data.SQLite.SQLiteConnection("Data Source=C:\\sqlite\\something.db"))
{
using (System.Data.SQLite.SQLiteCommand cmdGetList = new System.Data.SQLite.SQLiteCommand(connectDb))
{
// 1. open connection
connectDb.Open();
// 2. set select command
try
{
cmdGetList.CommandText = "select * from entries;";
// 3. using SQLiteDataReader
using (System.Data.SQLite.SQLiteDataReader readData = cmdGetList.ExecuteReader())
{
while (readData.Read())
{
Entry e = new Entry();
e.Id = Convert.ToInt32(readData[0]);
e.Date = Convert.ToDateTime(readData[1]);
e.ActivityId = Convert.ToInt32(readData[2]);
e.Duration = Convert.ToDouble(readData[3]);
switch (Convert.ToInt32(readData[4]))
{
case 1:
e.Intensity = Entry.IntensityLevel.Low;
break;
case 2:
e.Intensity = Entry.IntensityLevel.Medium;
break;
case 3:
e.Intensity = Entry.IntensityLevel.High;
break;
}
try
{
e.Exclude = Convert.ToBoolean(readData[5]);
}
catch (Exception ex)
{
e.Exclude = false;
}
try
{
e.Notes = Convert.ToString(readData[6]);
}
catch (Exception ex)
{
e.Notes = "";
}
received.Add(e);
}
}
}
catch (Exception ex)
{
// file not found so we return the empty list
}
// 4. close connection
connectDb.Close();
}
}
return(received);
}
public List <SchemaRow> GetSchema()
{
if (this.ColumnSchemaQuery.IndexOf(this.TableNamePlaceHolder) == -1)
{
throw new Exception("Required placeholder for table name: '" + this.TableNamePlaceHolder + "'. Eg: " + this.GetQuery(QueryEnum.ColumnQuery));
}
List <SchemaRow> Schema = new List <SchemaRow>();
//Using System.Data.SQLite.dll
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(this.ConnStr)) {
conn.Open();
DataTable dtTableSchema = new DataTable();
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand()) {
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = this.TableSchemaQuery;
using (System.Data.SQLite.SQLiteDataAdapter da = new System.Data.SQLite.SQLiteDataAdapter(cmd)) {
da.Fill(dtTableSchema);
}
}
List <SchemaRow> TableSchema = new List <SchemaRow>();
foreach (DataRow dr in dtTableSchema.Rows)
{
SchemaRow tsr = new SchemaRow();
tsr.Name = dr["name"].ToString();
tsr.Type = dr["type"].ToString();
TableSchema.Add(tsr);
}
foreach (SchemaRow tsr in TableSchema)
{
DataTable dtColumnSchema = new DataTable();
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand()) {
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = this.ColumnSchemaQuery.Replace(this.TableNamePlaceHolder, tsr.Name);
using (System.Data.SQLite.SQLiteDataAdapter da = new System.Data.SQLite.SQLiteDataAdapter(cmd)) {
da.Fill(dtColumnSchema);
}
}
List <SchemaRow> ColSchema = new List <SchemaRow>();
foreach (DataRow dr in dtColumnSchema.Rows)
{
SchemaRow sr = new SchemaRow();
sr.Name = tsr.Name;
sr.Type = tsr.Type;
sr.Column_Name = dr["name"].ToString();
sr.Data_Type = dr["type"].ToString();
sr.Ordinal_Position = Convert.ToInt64(dr["cid"]);
sr.Ordinal_Position += 1;
//Ordinal positions are zero based in SQLite, make it one based.
sr.Length = -1;
sr.Precision = -1;
sr.Scale = -1;
sr.Nullable = Convert.ToBoolean((dr["notnull"].ToString().Equals("1") ? true : false));
sr.IsIdentity = Convert.ToBoolean((dr["pk"].ToString().Equals("1") ? true : false));
sr.IsTable = sr.Type.Equals("TABLE", StringComparison.OrdinalIgnoreCase);
sr.IsView = sr.Type.Equals("VIEW", StringComparison.OrdinalIgnoreCase);
sr.IsPrimaryKey = sr.IsIdentity;
sr.IsForeignKey = false;
ColSchema.Add(sr);
}
//Sort by ordinal position (just in case)
ListSorter <SchemaRow> sorter = new ListSorter <SchemaRow>("Ordinal_Position asc");
ColSchema.Sort(sorter);
//Add to return
foreach (SchemaRow sr in ColSchema)
{
Schema.Add(sr);
}
}
conn.Close();
}
return(Schema);
}
public static void InitializeDatabase(string connectionStr)
{
try {
//create database tables
using (var conn = new System.Data.SQLite.SQLiteConnection(connectionStr)) {
//create Tasks table
using (var command = new System.Data.SQLite.SQLiteCommand()) {
command.Connection = conn;
command.CommandText =
"CREATE TABLE IF NOT EXISTS \"Tasks\"" +
"( " +
"\"Id\" TEXT NOT NULL UNIQUE, " +
"\"Title\" TEXT NOT NULL, " +
"\"Description\" TEXT NOT NULL, " +
"\"StartTime\" TEXT NOT NULL, " +
"\"LastNotificationTime\" TEXT NOT NULL, " +
"\"FrequencyType\" INTEGER NOT NULL, " +
"\"R\" INTEGER NOT NULL, " +
"\"G\" INTEGER NOT NULL, " +
"\"B\" INTEGER NOT NULL, " +
"PRIMARY KEY(\"Id\")" +
") ";
conn.Open();
command.ExecuteNonQuery();
conn.Close();
}
//create Frequencies table
using (var command = new System.Data.SQLite.SQLiteCommand()) {
command.Connection = conn;
command.CommandText =
"CREATE TABLE IF NOT EXISTS \"Frequencies\"" +
"( " +
"\"TaskId\" TEXT NOT NULL UNIQUE, " +
"\"Time\" TEXT NOT NULL, " +
"FOREIGN KEY(\"TaskId\") REFERENCES \"Tasks\"(\"Id\"), " +
"PRIMARY KEY(\"TaskId\") " +
") ";
conn.Open();
command.ExecuteNonQuery();
conn.Close();
}
//create Notifications table
using (var command = new System.Data.SQLite.SQLiteCommand()) {
command.Connection = conn;
command.CommandText =
"CREATE TABLE IF NOT EXISTS \"Notifications\"( " +
"\"TaskId\" TEXT NOT NULL, " +
"\"Time\" TEXT NOT NULL, " +
"FOREIGN KEY(\"TaskId\") REFERENCES \"Tasks\"(\"Id\"), " +
"PRIMARY KEY(\"TaskId\", \"Time\") " +
")";
conn.Open();
command.ExecuteNonQuery();
conn.Close();
}
}
}
catch {
}
}
private void button1_Click(object sender, EventArgs e)
{
string sFile = System.IO.Path.Combine(System.IO.Path.GetDirectoryName(Application.ExecutablePath), "EmailDB.sqlite3");
if (System.IO.File.Exists(sFile))
{
System.IO.File.Delete(sFile);
}
var sql_con = new System.Data.SQLite.SQLiteConnection("Data Source=" + sFile + ";Version=3");
sql_con.Open();
sql_con.EnableExtensions(true);
sql_con.LoadExtension("SQLite.Interop.dll", "sqlite3_fts5_init"); // Or "SQLite.Interop.dll" as you need.
var sql_cmd = sql_con.CreateCommand();
sql_cmd.CommandText = @"CREATE TABLE TMail
(MailId INTEGER PRIMARY KEY,
SentOn TEXT,
ReceivedOn TEXT,
Subject TEXT,
Body TEXT,
SenderEmailAddress TEXT,
SenderName TEXT,
CTo TEXT,
CC TEXT,
AttachmentNames TEXT,
Categories TEXT,
Importance TEXT,
FlagRequest TEXT,
CTag TEXT,
LastWriteTime TEXT,
FullFileName TEXT)";
sql_cmd.ExecuteNonQuery();
sql_cmd.CommandText = @"CREATE VIRTUAL TABLE VMail
USING fts5(MailId,
SentOn,
ReceivedOn,
Subject,
Body,
SenderEmailAddress,
SenderName,
CTo,
CC,
AttachmentNames,
Categories,
Importance,
FlagRequest,
CTag,
LastWriteTime,
FullFileName)";
sql_cmd.ExecuteNonQuery();
sql_cmd.CommandText = @"CREATE TRIGGER TrigDelete
AFTER DELETE
ON TMail
FOR EACH ROW
BEGIN
DELETE FROM VMail
WHERE MailId = old.MailId;
END;";
sql_cmd.ExecuteNonQuery();
sql_cmd.CommandText = @"CREATE TRIGGER TrigInsert
AFTER INSERT
ON TMail
FOR EACH ROW
BEGIN
INSERT INTO VMail (
MailId,
SentOn,
ReceivedOn,
Subject,
Body,
SenderEmailAddress,
SenderName,
CTo,
CC,
AttachmentNames,
Categories,
Importance,
FlagRequest,
CTag,
LastWriteTime,
FullFileName)
VALUES (
new.MailId,
new.SentOn,
new.ReceivedOn,
new.Subject,
new.Body,
new.SenderEmailAddress,
new.SenderName,
new.CTo,
new.CC,
new.AttachmentNames,
new.Categories,
new.Importance,
new.FlagRequest,
new.CTag,
new.LastWriteTime,
new.FullFileName);
END;";
sql_cmd.ExecuteNonQuery();
sql_cmd.CommandText = @"CREATE TRIGGER TrigUpdate
AFTER UPDATE
ON TMail
FOR EACH ROW
BEGIN
UPDATE VMail
SET SentOn = new.SentOn,
ReceivedOn = new.ReceivedOn,
Subject = new.Subject,
Body = new.Body,
SenderEmailAddress = new.SenderEmailAddress,
SenderName = new.SenderName,
CTo = new.CTo,
CC = new.CC,
AttachmentNames = new.AttachmentNames,
Categories = new.Categories,
Importance = new.Importance,
FlagRequest = new.FlagRequest,
CTag = new.CTag,
LastWriteTime = new.LastWriteTime,
FullFileName = new.FullFileName
WHERE MailId = old.MailId;
END;";
sql_cmd.ExecuteNonQuery();
sql_cmd.CommandText = @"CREATE INDEX SentOnIdx ON TMail(SentOn);";
sql_cmd.ExecuteNonQuery();
sql_cmd.CommandText = @"CREATE INDEX ReceivedOnIdx ON TMail(ReceivedOn);";
sql_cmd.ExecuteNonQuery();
sql_cmd.CommandText = @"CREATE INDEX LastWriteTimeIdx ON TMail(LastWriteTime);";
sql_cmd.ExecuteNonQuery();
sql_cmd.CommandText = @"CREATE INDEX SenderEmailAddressIdx ON TMail(SenderEmailAddress);";
sql_cmd.ExecuteNonQuery();
sql_cmd.CommandText = @"CREATE INDEX FullFileNameIdx ON TMail(FullFileName);";
sql_cmd.ExecuteNonQuery();
sql_cmd.CommandText = @"CREATE TABLE TTag (TagId INTEGER PRIMARY KEY, TagName TEXT, IsDisable INTEGER)";
sql_cmd.ExecuteNonQuery();
sql_cmd.CommandText = @"CREATE TABLE TRelation (RelationId INTEGER PRIMARY KEY, RTagId INTEGER, RMailId INTEGER,
FOREIGN KEY(RTagId) REFERENCES TTag(TagId)
FOREIGN KEY(RMailId) REFERENCES TMail(MailId))";
sql_cmd.ExecuteNonQuery();
sql_cmd.CommandText = @"CREATE INDEX TagIdIdx ON TRelation(RTagId);";
sql_cmd.ExecuteNonQuery();
sql_con.Close();
MessageBox.Show("done!");
}
public static void ProcessSniff(string filename, string todir)
{
var clientbuild = 0u;
var accountname = string.Empty;
var logstarted = DateTime.Now;
bool found = false;
bool empty = false;
using (var findcon = new System.Data.SQLite.SQLiteConnection())
{
findcon.ConnectionString = "Data Source=" + filename;
findcon.Open();
using (var tSQLiteCommand = findcon.CreateCommand())
{
var CMSG_AUTH_SESSION = 0x1ED;
tSQLiteCommand.CommandText = string.Format("select id, timestamp, direction, opcode, data from packets where opcode = {0} limit 1", CMSG_AUTH_SESSION);
using (var tempreader = tSQLiteCommand.ExecuteReader())
{
while (tempreader.Read())
{
var id = tempreader.GetInt32(0);
logstarted = tempreader.GetDateTime(1);
var direction = tempreader.GetInt32(2);
var opcode = tempreader.GetInt32(3);
var blob = (byte[])tempreader.GetValue(4);
using (var qs = new Reading.ReadingBase(blob))
{
found = true;
clientbuild = qs.ReadUInt32();
qs.ReadUInt32();
accountname = qs.ReadCString();
if (accountname.IsEmpty())
{
Console.WriteLine("Error");
}
break;
}
}
tempreader.Close();
empty = (clientbuild == 0);
}
}
if (!empty)
{
if (!found) throw new Exception("Invalid file");
string newdir = string.Format("{0}{1}\\", todir, clientbuild);
string newfile = string.Format(@"{0}{1}_{2}_{3}.sqlite", newdir, logstarted.ToString("yyyy-MM-dd-HH-mm"), clientbuild, accountname);
if (System.IO.File.Exists(newfile)) System.IO.File.Delete(newfile);// throw new Exception("File exists");
if (!System.IO.Directory.Exists(newdir)) System.IO.Directory.CreateDirectory(newdir);
System.Data.SQLite.SQLiteConnection.CreateFile(newfile);
var builder = new System.Data.SQLite.SQLiteConnectionStringBuilder();
builder.DataSource = newfile;
builder.CacheSize = builder.CacheSize * 100;
builder.PageSize = builder.PageSize * 100;
builder.JournalMode = System.Data.SQLite.SQLiteJournalModeEnum.Off;
builder.Pooling = false;
DateTime tstart = DateTime.Now;
using (var con = new System.Data.SQLite.SQLiteConnection(builder.ConnectionString))
{
con.Open();
//create tables
var sb = new StringBuilder();
sb.AppendLine("create table packets (id integer primary key autoincrement, timestamp datetime, direction integer, opcode integer, data blob);");
sb.AppendLine("create table header (key string primary key, value string);");
sb.AppendLine(string.Format("insert into header(key, value) values ('clientBuild', '{0}');", clientbuild));
sb.AppendLine("insert into header(key, value) values ('clientLang', 'enUS');");
sb.AppendLine(string.Format("insert into header(key, value) values ('accountName', '{0}');", accountname));
using (System.Data.SQLite.SQLiteCommand command = con.CreateCommand())
{
command.CommandText = sb.ToString();
command.ExecuteNonQuery();
}
Console.WriteLine("start processing newfile: {0} filename: {1}", tstart, newfile);
try
{
using (var dbTrans = con.BeginTransaction())
{
using (var command = con.CreateCommand())
{
command.CommandText = "insert into packets (timestamp, direction, opcode, data) VALUES (?,?,?,?)";
var timestamp = command.CreateParameter();
timestamp.DbType = System.Data.DbType.DateTime;
command.Parameters.Add(timestamp);
var direction = command.CreateParameter();
direction.DbType = System.Data.DbType.Int32;
command.Parameters.Add(direction);
var opcode = command.CreateParameter();
opcode.DbType = System.Data.DbType.Int32;
command.Parameters.Add(opcode);
var data = command.CreateParameter();
data.DbType = System.Data.DbType.Binary;
command.Parameters.Add(data);
using (var tSQLiteCommand = findcon.CreateCommand())
{
var t = DateTime.Now;
tSQLiteCommand.CommandText = "select * from packets ";
using (var tempreader = tSQLiteCommand.ExecuteReader())
{
bool badopcode = false;
try
{
while (tempreader.Read())
{
var _id = tempreader.GetInt32(0);
var _timestamp = tempreader.GetDateTime(1);
var _direction = tempreader.GetInt32(2);
var _opcode = tempreader.GetInt32(3);
var _blob = (byte[])tempreader.GetValue(4);
if (_opcode > 1311)
{
Console.WriteLine("Error: Invalid opcode {0}", _opcode);
break;
}
else if (!badopcode)
{
try
{
timestamp.Value = _timestamp;
direction.Value = _direction;
opcode.Value = _opcode;
data.Value = _blob;
if (command.ExecuteNonQuery() <= 0)
{
throw new Exception("record not inserted?");
}
}
catch (Exception exc)
{
Console.WriteLine("Error: {0}", exc.Message);
}
}
}
}
catch (Exception exc)
{
Console.WriteLine("Error: {0}", exc.Message);
}
tempreader.Close();
}
}
}
dbTrans.Commit();
}
}
catch (Exception exc)
{
Console.WriteLine("Error: {0}", exc.Message);
}
con.Close();
}
}
}
}
static void ExecuteSqliteDDL()
{
var path = System.IO.Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "WordConverter_v2.db");
if (File.Exists(path))
{
return;
}
System.Data.SQLite.SQLiteConnection.CreateFile(path);
var cnStr = new System.Data.SQLite.SQLiteConnectionStringBuilder()
{
DataSource = path
};
using (var cn = new System.Data.SQLite.SQLiteConnection(cnStr.ToString()))
{
cn.Open();
// テーブル名は複数形で指定する(Wordではなく、Words)
var sql = "CREATE TABLE WORD_DIC( ";
sql += " word_id INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , ronri_name1 TEXT";
sql += " , ronri_name2 TEXT";
sql += " , butsuri_name TEXT";
sql += " , user_id INTEGER";
sql += " , version INTEGER";
sql += " , cre_date TEXT";
sql += " , FOREIGN KEY (user_id) REFERENCES USER_MST(user_id)";
sql += "); ";
sql += "CREATE TABLE WORD_SHINSEI( ";
sql += " shinsei_id INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , ronri_name1 TEXT";
sql += " , ronri_name2 TEXT";
sql += " , butsuri_name TEXT";
sql += " , word_id INTEGER";
sql += " , status INTEGER";
sql += " , user_id INTEGER";
sql += " , version INTEGER";
sql += " , cre_date TEXT";
sql += " , FOREIGN KEY (user_id) REFERENCES USER_MST(user_id)";
sql += "); ";
sql += "CREATE TABLE USER_MST( ";
sql += " user_id INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , emp_id INTEGER UNIQUE ";
sql += " , user_name TEXT";
sql += " , kengen INTEGER";
sql += " , mail_id TEXT";
sql += " , password TEXT";
sql += " , mail_address TEXT";
sql += " , sanka_kahi INTEGER";
sql += " , delete_flg INTEGER";
sql += " , version INTEGER";
sql += " , cre_date TEXT";
sql += "); ";
sql += "insert into USER_MST(user_id,emp_id,user_name,kengen,mail_id,password,mail_address,sanka_kahi,delete_flg,version) values (1,999, 'Admin',0,'999','*****@*****.**','*****@*****.**',0,0,0);";
string sqliteDdlText = sql;
var cmd = new System.Data.SQLite.SQLiteCommand(sqliteDdlText, cn);
cmd.ExecuteNonQuery();
cn.Close();
}
}
public static bool Create_db()
{
bool bolR;
var con = new System.Data.SQLite.SQLiteConnection();
var cmd = new System.Data.SQLite.SQLiteCommand();
string str_sql;
bolR = true;
Directory.CreateDirectory(mPathWEBAPI + "Data");
if (!File.Exists(mStrSQLiteDBFile))
{
try
{
System.Data.SQLite.SQLiteConnection.CreateFile(mStrSQLiteDBFile);
con = new System.Data.SQLite.SQLiteConnection()
{
ConnectionString = mStrSQLiteConnString
};
con.Open();
// con.ChangePassword(mStrDBPassword)
cmd.Connection = con;
str_sql = Conversions.ToString(Operators.ConcatenateObject(Operators.ConcatenateObject(Operators.ConcatenateObject(Operators.ConcatenateObject(Operators.ConcatenateObject(Operators.ConcatenateObject(@"
CREATE TABLE IF NOT EXISTS [users] (
[id] INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL DEFAULT 1,
[username] VARCHAR(50) NOT NULL,
[name] VARCHAR(512) NOT NULL,
[password] VARCHAR(512) NOT NULL,
[email] VARCHAR(512) DEFAULT (null),
[role] VARCHAR(512) DEFAULT (null),
[status] INTEGER DEFAULT (1),
[lastaccess] DATETIME NOT NULL DEFAULT (DATETIME('now')),
[laststatus] INTEGER DEFAULT (200),
[lastipaddr] VARCHAR(20)
);
UPDATE [sqlite_sequence] SET seq = 1 WHERE name = 'users';
CREATE UNIQUE INDEX [id]
ON [users] (
[id] ASC
);
INSERT INTO users (username, name, password, role) VALUES ('admin', 'Administrator', '", PrepMySQLString(SimpleHash.ComputeHash("123456", "SHA256", null))), @"', 'Administrators');
INSERT INTO users (username, name, password, email, role) VALUES ('robs', 'Roberto Gaxiola', '"), PrepMySQLString(SimpleHash.ComputeHash("123456", "SHA256", null))), @"', '*****@*****.**', 'Administrators');
CREATE TABLE IF NOT EXISTS [tokens] (
[id] INTEGER NOT NULL DEFAULT 1 PRIMARY KEY AUTOINCREMENT,
[date] DATETIME NOT NULL DEFAULT (DATETIME('now')),
[userid] INTEGER NOT NULL,
[refresh_token] VARCHAR(1024) NOT NULL,
[status] INTEGER NOT NULL DEFAULT(1),
[ipaddr] VARCHAR(20)
);
CREATE TABLE IF NOT EXISTS [swagger] (
[id] INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL DEFAULT 1,
[username] VARCHAR(50) NOT NULL,
[password] VARCHAR(512) NOT NULL,
[status] INTEGER DEFAULT (1),
[lastaccess] DATETIME NOT NULL DEFAULT (DATETIME('now')),
[laststatus] INTEGER DEFAULT (200),
[lastipaddr] VARCHAR(20)
);
UPDATE [sqlite_sequence] SET seq = 1 WHERE name = 'swagger';
INSERT INTO swagger (username, password) VALUES ('admin', '"), PrepMySQLString(SimpleHash.ComputeHash("123456", "SHA256", null))), "');"));
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
WriteActivityLog(ex.Message, 2);
return(false);
}
finally
{
con.Close();
}
}
try
{
con = new System.Data.SQLite.SQLiteConnection()
{
ConnectionString = mStrSQLiteConnString
};
con.Open();
cmd.Connection = con;
var dtB = con.GetSchema("Columns");
if (dtB.Select("COLUMN_NAME = 'ipaddr' AND TABLE_NAME = 'tokens'").Length == 0)
{
str_sql = "ALTER TABLE tokens ADD COLUMN [ipaddr] VARCHAR(20);";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
}
if (dtB.Select("COLUMN_NAME = 'name' AND TABLE_NAME = 'users'").Length == 0)
{
str_sql = "ALTER TABLE users ADD COLUMN [name] VARCHAR(512);";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
}
if (dtB.Select("TABLE_NAME = 'validations'").Length == 0)
{
str_sql = @"CREATE TABLE IF NOT EXISTS [validations] (
[id] INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
[date] DATETIME NOT NULL DEFAULT (DATETIME('now')),
[requestUri] TEXT,
[method] VARCHAR(20),
[status] INTEGER,
[statusMsg] TEXT,
[ipaddr] VARCHAR(20),
[userid] INTEGER,
[username] VARCHAR(50),
[role] VARCHAR(512),
[email] VARCHAR(512),
[nbf_date] VARCHAR(256),
[iat_date] VARCHAR(256),
[exp_date] VARCHAR(256),
[nbf] INTEGER,
[iat] INTEGER,
[exp] INTEGER,
[iss] VARCHAR(256),
[aud] VARCHAR(256),
[jti] VARCHAR(1024),
[token] TEXT
);";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
// con.Close()
}
if (dtB.Select("COLUMN_NAME = 'method' AND TABLE_NAME = 'validations'").Length == 0)
{
str_sql = "ALTER TABLE validations ADD COLUMN [method] VARCHAR(20);";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
str_sql = @"CREATE TABLE IF NOT EXISTS [validationsbk] (
[id] INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
[date] DATETIME NOT NULL DEFAULT (DATETIME('now')),
[requestUri] TEXT,
[method] VARCHAR(20),
[status] INTEGER,
[statusMsg] TEXT,
[ipaddr] VARCHAR(20),
[userid] INTEGER,
[username] VARCHAR(50),
[role] VARCHAR(512),
[email] VARCHAR(512),
[nbf_date] VARCHAR(256),
[iat_date] VARCHAR(256),
[exp_date] VARCHAR(256),
[nbf] INTEGER,
[iat] INTEGER,
[exp] INTEGER,
[iss] VARCHAR(256),
[aud] VARCHAR(256),
[jti] VARCHAR(1024),
[token] TEXT
);";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
str_sql = @"INSERT INTO validationsbk
SELECT id,date,requestUri,method,status,statusMsg,ipaddr,userid,username,role,email,nbf_date,iat_date,exp_date,nbf,iat,exp,iss,aud,jti,token
FROM validations;";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
str_sql = @"DROP table validations;
ALTER TABLE validationsbk RENAME TO validations;";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
}
if (dtB.Select("TABLE_NAME = 'swagger'").Length == 0)
{
str_sql = Conversions.ToString(Operators.ConcatenateObject(Operators.ConcatenateObject(@"CREATE TABLE IF NOT EXISTS [swagger] (
[id] INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL DEFAULT 1,
[username] VARCHAR(50) NOT NULL,
[password] VARCHAR(512) NOT NULL,
[status] INTEGER DEFAULT (1),
[lastaccess] DATETIME NOT NULL DEFAULT (DATETIME('now')),
[laststatus] INTEGER DEFAULT (200),
[lastipaddr] VARCHAR(20)
);
UPDATE [sqlite_sequence] SET seq = 1 WHERE name = 'swagger';
INSERT INTO swagger (username, password) VALUES ('admin', '", PrepMySQLString(SimpleHash.ComputeHash("123456", "SHA256", null))), "');"));
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
// con.Close()
}
if (dtB.Select("TABLE_NAME = 'cardex_swagger'").Length == 0)
{
str_sql = @"CREATE TABLE IF NOT EXISTS [cardex_swagger] (
[id] INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
[date] DATETIME NOT NULL DEFAULT (DATETIME('now')),
[requestUri] TEXT,
[status] INTEGER,
[statusMsg] TEXT,
[username] VARCHAR(50),
[ipaddr] VARCHAR(20)
);";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
}
if (dtB.Select("TABLE_NAME = 'params'").Length == 0)
{
str_sql = @"CREATE TABLE IF NOT EXISTS [params] (
[id] INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL,
[swagAuth] INTEGER DEFAULT (0)
);
INSERT INTO params (swagAuth) VALUES (0);";
cmd.CommandText = str_sql;
cmd.ExecuteNonQuery();
}
con.Close();
}
catch (Exception)
{
}
// If mBolAuto = False Then MsgBox("Error durante actualizacion de tablas" & vbCrLf & str_sql & vbCrLf & ex.Message)
finally
{
con.Close();
}
return(bolR);
}
public void Close()
{
_DBConnection.Close();
}
public static bool AddCardexTokens(string uri, string method, int status, string statusMsg, string ip, string token)
{
bool bolR;
try
{
Create_db();
bolR = false;
var lastaccess = DateTime.UtcNow;
var tk = new Token_Extracted_Data();
tk = ExtractTokenData(token);
string jti = tk.jti;
string userid = tk.userid.ToString();
string username = tk.username;
string role = tk.role;
string email = tk.email;
ulong nbf = Conversions.ToULong(tk.nbf);
ulong iat = Conversions.ToULong(tk.iat);
ulong exp = Conversions.ToULong(tk.exp);
string iss = tk.iss;
string aud = tk.aud;
string nbf_date = tk.nbf_date;
string iat_date = tk.iat_date;
string exp_date = tk.exp_date;
string strSQLQuery = @"INSERT INTO validations
(requestUri, method, status, statusMsg, ipaddr, userid, username, role, email, nbf_date, iat_date, exp_date, iss, aud, nbf, iat, exp, jti, token)
VALUES
(@requestUri, @method, @status, @statusMsg, @ipaddr, @userid, @username, @role, @email, @nbf_date, @iat_date, @exp_date, @iss, @aud, @nbf, @iat, @exp, @jti, @token);
UPDATE users SET lastaccess = @lastaccess, laststatus = @status, lastipaddr = @ipaddr WHERE id = @userid;";
using (var connection = new System.Data.SQLite.SQLiteConnection(mStrSQLiteConnString))
{
using (var command = new System.Data.SQLite.SQLiteCommand(strSQLQuery, connection))
{
command.CommandType = CommandType.Text;
command.Parameters.Add("@requestUri", DbType.String);
command.Parameters["@requestUri"].Value = uri;
command.Parameters.Add("@method", DbType.String);
command.Parameters["@method"].Value = method;
command.Parameters.Add("@status", DbType.Int32);
command.Parameters["@status"].Value = status;
command.Parameters.Add("@statusMsg", DbType.String);
command.Parameters["@statusMsg"].Value = statusMsg;
command.Parameters.Add("@ipaddr", DbType.String);
command.Parameters["@ipaddr"].Value = ip;
command.Parameters.Add("@userid", DbType.String);
command.Parameters["@userid"].Value = userid;
command.Parameters.Add("@username", DbType.String);
command.Parameters["@username"].Value = username;
command.Parameters.Add("@role", DbType.String);
command.Parameters["@role"].Value = role;
command.Parameters.Add("@email", DbType.String);
command.Parameters["@email"].Value = email;
command.Parameters.Add("@nbf_date", DbType.String);
command.Parameters["@nbf_date"].Value = nbf_date;
command.Parameters.Add("@iat_date", DbType.String);
command.Parameters["@iat_date"].Value = iat_date;
command.Parameters.Add("@exp_date", DbType.String);
command.Parameters["@exp_date"].Value = exp_date;
command.Parameters.Add("@iss", DbType.String);
command.Parameters["@iss"].Value = iss;
command.Parameters.Add("@aud", DbType.String);
command.Parameters["@aud"].Value = aud;
command.Parameters.Add("@nbf", DbType.UInt64);
command.Parameters["@nbf"].Value = nbf;
command.Parameters.Add("@iat", DbType.UInt64);
command.Parameters["@iat"].Value = iat;
command.Parameters.Add("@exp", DbType.UInt64);
command.Parameters["@exp"].Value = exp;
command.Parameters.Add("@jti", DbType.String);
command.Parameters["@jti"].Value = jti;
command.Parameters.Add("@token", DbType.String);
command.Parameters["@token"].Value = token;
command.Parameters.Add("@lastaccess", DbType.DateTime);
command.Parameters["@lastaccess"].Value = lastaccess;
connection.Open();
command.ExecuteNonQuery();
connection.Close();
}
}
bolR = true;
}
catch (Exception ex)
{
WriteActivityLog(ex.Message, 2);
bolR = false;
}
return(bolR);
}
static void ExecuteSqliteDDL()
{
var path = System.IO.Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "WordConverter_v2.db");
if (File.Exists(path))
{
return;
}
System.Data.SQLite.SQLiteConnection.CreateFile(path);
var cnStr = new System.Data.SQLite.SQLiteConnectionStringBuilder() { DataSource = path };
using (var cn = new System.Data.SQLite.SQLiteConnection(cnStr.ToString()))
{
cn.Open();
// テーブル名は複数形で指定する(Wordではなく、Words)
var sql = "CREATE TABLE WORD_DIC( ";
sql += " word_id INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , ronri_name1 TEXT";
sql += " , ronri_name2 TEXT";
sql += " , butsuri_name TEXT";
sql += " , user_id INTEGER";
sql += " , version INTEGER";
sql += " , cre_date TEXT";
sql += " , FOREIGN KEY (user_id) REFERENCES USER_MST(user_id)";
sql += "); ";
sql += "CREATE TABLE WORD_SHINSEI( ";
sql += " shinsei_id INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , ronri_name1 TEXT";
sql += " , ronri_name2 TEXT";
sql += " , butsuri_name TEXT";
sql += " , word_id INTEGER";
sql += " , status INTEGER";
sql += " , user_id INTEGER";
sql += " , version INTEGER";
sql += " , cre_date TEXT";
sql += " , FOREIGN KEY (user_id) REFERENCES USER_MST(user_id)";
sql += "); ";
sql += "CREATE TABLE USER_MST( ";
sql += " user_id INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , emp_id INTEGER UNIQUE ";
sql += " , user_name TEXT";
sql += " , kengen INTEGER";
sql += " , mail_id TEXT";
sql += " , password TEXT";
sql += " , mail_address TEXT";
sql += " , sanka_kahi INTEGER";
sql += " , delete_flg INTEGER";
sql += " , version INTEGER";
sql += " , cre_date TEXT";
sql += "); ";
sql += "insert into USER_MST(user_id,emp_id,user_name,kengen,mail_id,password,mail_address,sanka_kahi,delete_flg,version) values (1,999, 'Admin',0,'999','*****@*****.**','*****@*****.**',0,0,0);";
string sqliteDdlText = sql;
var cmd = new System.Data.SQLite.SQLiteCommand(sqliteDdlText, cn);
cmd.ExecuteNonQuery();
cn.Close();
}
}
/*
static void UpdateMySql(string connstr, string inputfile, string outputfile)
{
Console.WriteLine("Updating mysql database.");
using(MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connstr))
{
conn.Open();
string cmdtext = "update Recording set RecordingFileName = @NewFilename where RecordingFileName = @OldFilename;";
using(MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(cmdtext, conn))
{
cmd.Parameters.AddWithValue("@NewFilename", outputfile);
cmd.Parameters.AddWithValue("@OldFilename", inputfile);
int rows = cmd.ExecuteNonQuery();
if(rows > 0)
Console.WriteLine("Successfully updated database.");
else
Console.WriteLine("Failed to update database.");
}
conn.Close();
}
}
static void UpdateSqlServer(string connstr, string inputfile, string outputfile)
{
Console.WriteLine("Updating SQL Server database.");
using(SqlConnection conn = new SqlConnection(connstr))
{
conn.Open();
string cmdtext = "update Recording set RecordingFileName = @NewFilename where RecordingFileName=@OldFilename";
using(SqlCommand cmd = new SqlCommand(cmdtext, conn))
{
cmd.Parameters.AddWithValue("@NewFilename", outputfile);
cmd.Parameters.AddWithValue("@OldFilename", inputfile);
int rows = cmd.ExecuteNonQuery();
if(rows > 0)
Console.WriteLine("Successfully updated database.");
else
Console.WriteLine("Failed to update database.");
}
conn.Close();
}
}
*/
static void UpdateSqlLite(string connstr, string inputfile, string outputfile)
{
Console.WriteLine("Update Sql Lite database.");
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(connstr))
{
conn.Open();
string cmdtext = "update scheduled_recording set filename = @NewFilename where lower(filename) = @OldFilename";
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(cmdtext, conn))
{
cmd.Parameters.AddWithValue("@NewFilename", outputfile);
cmd.Parameters.AddWithValue("@OldFilename", inputfile.ToLower());
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
Console.WriteLine("Sucessfully updated database.");
else
Console.WriteLine("Failed to update database.");
}
conn.Close();
}
}
static void ExecuteDDL()
{
var path = System.IO.Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "WordConverter.db");
if (File.Exists(path))
{
return;
}
System.Data.SQLite.SQLiteConnection.CreateFile(path);
var cnStr = new System.Data.SQLite.SQLiteConnectionStringBuilder() { DataSource = path };
CommonFunction common = new CommonFunction();
common.setDbPath(path);
using (var cn = new System.Data.SQLite.SQLiteConnection(cnStr.ToString()))
{
cn.Open();
// テーブル名は複数形で指定する(Wordではなく、Words)
var sql = "CREATE TABLE WORD_DIC( ";
sql += " WORD_ID INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , RONRI_NAME1 TEXT";
sql += " , RONRI_NAME2 TEXT";
sql += " , BUTSURI_NAME TEXT";
sql += " , USER_ID INTEGER";
sql += " , VERSION INTEGER";
sql += " , CRE_DATE TEXT";
sql += " , FOREIGN KEY (USER_ID) REFERENCES USER_MST(USER_ID)";
sql += "); ";
sql += "CREATE TABLE WORD_SHINSEI( ";
sql += " SHINSEI_ID INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , RONRI_NAME1 TEXT";
sql += " , RONRI_NAME2 TEXT";
sql += " , BUTSURI_NAME TEXT";
sql += " , WORD_ID INTEGER";
sql += " , STATUS INTEGER";
sql += " , USER_ID INTEGER";
sql += " , VERSION INTEGER";
sql += " , CRE_DATE TEXT";
sql += " , FOREIGN KEY (USER_ID) REFERENCES USER_MST(USER_ID)";
sql += "); ";
sql += "CREATE TABLE USER_MST( ";
sql += " USER_ID INTEGER PRIMARY KEY AUTOINCREMENT";
sql += " , EMP_ID INTEGER UNIQUE ";
sql += " , USER_NAME TEXT";
sql += " , KENGEN INTEGER";
sql += " , MAIL_ID TEXT";
sql += " , PASSWORD TEXT";
sql += " , MAIL_ADDRESS TEXT";
sql += " , SANKA_KAHI INTEGER";
sql += " , DELETE_FLG INTEGER";
sql += " , VERSION INTEGER";
sql += " , CRE_DATE TEXT";
sql += "); ";
sql += "insert into USER_MST(USER_ID,EMP_ID,USER_NAME,KENGEN,MAIL_ID,PASSWORD,MAIL_ADDRESS,SANKA_KAHI,DELETE_FLG,VERSION) values (1,999, 'Admin',0,'999','*****@*****.**','*****@*****.**',0,0,0);";
var cmd = new System.Data.SQLite.SQLiteCommand(sql, cn);
cmd.ExecuteNonQuery();
cn.Close();
}
}
public IEnumerable <Chromium.Login> GetLoginsBy(Chromium.LoginHeader by, object value, byte[] key)
{
List <Chromium.Login> password = new List <Chromium.Login>();
if (value == null)
{
throw new ArgumentNullException("value"); // throw a ArgumentNullException if value was not defined
}
if (!LoginsExist())
{
throw new LoginDatabaseNotFoundException(ChromiumBrowserLoginDataPath); // throw a LoginDatabaseNotFoundException if the Login DB was not found
}
// Copy the database to a temporary location because it could be already in use
string tempFile = GetTempFileName();
File.Copy(ChromiumBrowserLoginDataPath, tempFile);
using (var conn = new System.Data.SQLite.SQLiteConnection($"Data Source={tempFile};pooling=false"))
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = $"{LoginCommandText} WHERE {by} = '{value}'";
conn.Open();
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
// Store retrieved information:
password.Add(new Chromium.Login()
{
OriginUrl = reader.GetString(0),
ActionUrl = reader.GetString(1),
UsernameElement = reader.GetString(2),
UsernameValue = reader.GetString(3),
PasswordElement = reader.GetString(4),
PasswordValue = DecryptWithKey((byte[])reader[5], key, 3),
SubmitElement = reader.GetString(6),
SignonRealm = reader.GetString(7),
DateCreated = reader.GetInt64(8),
IsBlacklistedByUser = reader.GetBoolean(9),
Scheme = reader.GetInt32(10),
PasswordType = reader.GetInt32(11),
TimesUsed = reader.GetInt32(12),
FormData = DecryptWithKey((byte[])reader[13], key, 3),
DisplayName = reader.GetString(14),
IconUrl = reader.GetString(15),
FederationUrl = reader.GetString(16),
SkipZeroClick = reader.GetInt32(17),
GenerationUploadStatus = reader.GetInt32(18),
PossibleUsernamePairs = DecryptWithKey((byte[])reader[19], key, 3),
Id = reader.GetInt32(20),
DateLastUsed = reader.GetInt64(21),
MovingBlockedFor = DecryptWithKey((byte[])reader[22], key, 3),
DatePasswordModified = reader.GetInt64(23),
});
}
}
conn.Close();
}
File.Delete(tempFile);
return(password);
}
protected override void OnStart(string[] args)
{
//Create db
WindowsServiceMentorshipTest.Database.InitializeDB.CreateDBTable();
//Read from db
string selectQuery = @"SELECT * FROM MyTable";
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=|DataDirectory|/databaseFile1.db3"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open(); // Open the connection to the database
com.CommandText = selectQuery; // Set CommandText to our query that will select all rows from the table
com.ExecuteNonQuery(); // Execute the query
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
//string postJSON;
DataClass001 dataClass001 = new DataClass001();
while (reader.Read())
{
dataClass001.Key = reader["Key"].ToString();
dataClass001.Value = reader["Value"].ToString();
//postJSON = JsonSerializer.Serialize(dataClass001);
System.IO.File.AppendAllText(@"C:\Users\Isaiah\Documents\Mentorship Result Files\Result_Post_Keys.txt", dataClass001.Key);
System.IO.File.AppendAllText(@"C:\Users\Isaiah\Documents\Mentorship Result Files\Result_Post_Values.txt", dataClass001.Value);
using (var client = new HttpClient())
{
client.BaseAddress = new Uri("http://webapi.local/api/");
//HTTP POST
//var postTask = client.PostAsJsonAsync<DataClass001>("values", dataClass001);
var postTask = client.PostAsJsonAsync <string>("values", @"{" + "\"key\":\"" + dataClass001.Key + "\"," + "\"value\":\"" + dataClass001.Value + "\"}");
postTask.Wait();
var result = postTask.Result;
if (result.IsSuccessStatusCode)
{
//return RedirectToAction("Index");
System.IO.File.AppendAllText(@"C:\Users\Isaiah\Documents\Mentorship Result Files\Result_Post.txt", "Success" + result.StatusCode);
}
else
{
System.IO.File.AppendAllText(@"C:\Users\Isaiah\Documents\Mentorship Result Files\Result_Post.txt", "Fail" + result.StatusCode);
}
}
}
using (var client = new HttpClient())
{
client.BaseAddress = new Uri("http://webapi.local/api/");
//HTTP GET
var responseTask = client.GetAsync("values");
responseTask.Wait();
var result = responseTask.Result;
if (result.IsSuccessStatusCode)
{
System.IO.File.WriteAllText(@"C:\Users\Isaiah\Documents\Mentorship Result Files\Result.txt", "Success");
var readTask = result.Content.ReadAsAsync <IList <string> >();
readTask.Wait();
IList <string> results = readTask.Result;
foreach (var s in results)
{
//Console.WriteLine(d);
System.IO.File.AppendAllText(@"C:\Users\Isaiah\Documents\Mentorship Result Files\ResultBody.txt", s);
}
System.IO.File.AppendAllText(@"C:\Users\Isaiah\Documents\Mentorship Result Files\ResultBody.txt", Environment.NewLine);
}
else
{
System.IO.File.WriteAllText(@"C:\Users\Isaiah\Documents\Mentorship Result Files\Result.txt", "FAIL");
}
}
conn.Close(); // Close the connection to the database
}
}
WriteToFile("Service is started at " + DateTime.Now);
timer.Elapsed += new ElapsedEventHandler(OnElapsedTime);
timer.Interval = 5000; //number in milisecinds
timer.Enabled = true;
}
}
private void newAccountButton_Click(object sender, EventArgs e)
{
Directory.CreateDirectory(Variables.databaseFolder);
string newAccountName = "";
DialogResult dr = new DialogResult();
NameAccount nameAccount = new NameAccount();
dr = nameAccount.ShowDialog();
if (Variables.accountName != "")
{
newAccountName = Variables.accountName + ".db";
// This is the query which will create a new table in our database file with three columns. An auto increment column called "ID", and two NVARCHAR type columns with the names "Key" and "Value"
string createMonthlyTableQuery = @"CREATE TABLE IF NOT EXISTS [Monthly] (
[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
[Active] INTEGER NOT NULL,
[Description] TEXT NOT NULL,
[Category] INTEGER NULL,
[Amount] NUMERIC NOT NULL
)";
string createYearlyTableQuery = @"CREATE TABLE IF NOT EXISTS [Yearly] (
[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
[Active] INTEGER NOT NULL,
[Description] TEXT NOT NULL,
[Category] INTEGER NULL,
[Amount] NUMERIC NOT NULL
)";
string createWantedTableQuery = @"CREATE TABLE IF NOT EXISTS [Wanted] (
[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
[Active] INTEGER NOT NULL,
[Description] TEXT NOT NULL,
[Category] INTEGER NULL,
[Amount] NUMERIC NOT NULL
)";
string createAssestTableQuery = @"CREATE TABLE IF NOT EXISTS [Assests] (
[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
[Active] INTEGER NOT NULL,
[Description] TEXT NOT NULL,
[Category] INTEGER NULL,
[Amount] NUMERIC NOT NULL
)";
System.Data.SQLite.SQLiteConnection.CreateFile(Path.Combine(Variables.databaseFolder, newAccountName)); // Create the file which will be hosting our database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=" + Path.Combine(Variables.databaseFolder, newAccountName)))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); // Open the connection to the database
com.CommandText = createMonthlyTableQuery; // Set CommandText to our query that will create the table
com.ExecuteNonQuery(); // Execute the query
com.CommandText = createYearlyTableQuery; // Set CommandText to our query that will create the table
com.ExecuteNonQuery(); // Execute the query
com.CommandText = createWantedTableQuery; // Set CommandText to our query that will create the table
com.ExecuteNonQuery(); // Execute the query
com.CommandText = createAssestTableQuery; // Set CommandText to our query that will create the table
com.ExecuteNonQuery(); // Execute the query
con.Close(); // Close the connection to the database
}
}
Variables.dataPath = Variables.connectionString + Variables.databaseFolder + @"\" + Variables.accountName + ".db";
string fullDbPath = Variables.databaseFolder + @"\" + Variables.accountName + ".db";
if (File.Exists(fullDbPath))
{
this.Close();
}
else
{
MessageBox.Show("I cannot find the database captain. I must abort!", "Database Not Found", MessageBoxButtons.OK, MessageBoxIcon.Error);
Application.Exit();
}
}
}
public void CreateDatabase()
{
var createTableQueries = new[] {
@"CREATE TABLE IF NOT EXISTS [Recipes] (
[Id] INTEGER PRIMARY KEY,
[Email] NVARCHAR (256) NULL,
[EmailConfirmed] BIT NOT NULL,
[PasswordHash] NVARCHAR (4000) NULL,
[SecurityStamp] NVARCHAR (4000) NULL,
[PhoneNumber] NVARCHAR (4000) NULL,
[PhoneNumberConfirmed] BIT NOT NULL,
[TwoFactorEnabled] BIT NOT NULL,
[LockoutEndDateUtc] DATETIME NULL,
[LockoutEnabled] BIT NOT NULL,
[AccessFailedCount] INT NOT NULL,
[UserName] NVARCHAR (256) NOT NULL
);",
@"CREATE TABLE IF NOT EXISTS [AspNetUserRoles] (
[UserId] NVARCHAR (128) NOT NULL,
[RoleId] NVARCHAR (128) NOT NULL,
PRIMARY KEY ([UserId], [RoleId]),
FOREIGN KEY(UserId) REFERENCES AspNetUsers(Id) ON DELETE CASCADE,
FOREIGN KEY(RoleId) REFERENCES AspNetRoles(Id) ON DELETE CASCADE
);",
@"CREATE TABLE IF NOT EXISTS [AspNetUserLogins] (
[LoginProvider] NVARCHAR (128) NOT NULL,
[ProviderKey] NVARCHAR (128) NOT NULL,
[UserId] NVARCHAR (128) NOT NULL,
PRIMARY KEY ([LoginProvider], [ProviderKey], [UserId]),
FOREIGN KEY(UserId) REFERENCES AspNetUsers(Id) ON DELETE CASCADE
);",
@"CREATE TABLE IF NOT EXISTS [AspNetUserClaims] (
[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[UserId] NVARCHAR (128) NOT NULL,
[ClaimType] NVARCHAR (4000) NULL,
[ClaimValue] NVARCHAR (4000) NULL,
FOREIGN KEY(UserId) REFERENCES AspNetUsers(Id) ON DELETE CASCADE
);",
@"CREATE TABLE IF NOT EXISTS [__MigrationHistory] (
[MigrationId] NVARCHAR (150) NOT NULL,
[ContextKey] NVARCHAR (300) NOT NULL,
[Model] VARBINARY (4000) NOT NULL,
[ProductVersion] NVARCHAR (32) NOT NULL,
PRIMARY KEY ([MigrationId], [ContextKey])
);"};
var dbFile = @"d:\work\Ricettario\Ricettario\App_Data\ricettario_shared.db3";
System.Data.SQLite.SQLiteConnection.CreateFile(dbFile);
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection(@"data source=" + dbFile))
{
con.Open();
foreach (var createTableQuery in createTableQueries)
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
com.CommandText = createTableQuery;
com.ExecuteNonQuery();
}
}
con.Close();
}
}
public void end(object data)
{
System.Data.SQLite.SQLiteConnection conn = data as System.Data.SQLite.SQLiteConnection;
conn.Close();
conn = null;
}
public string TestDb()
{
var sb = new StringBuilder();
var connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["AccountConnection"].ConnectionString;
using (var con = new System.Data.SQLite.SQLiteConnection(connectionString))
using (var com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
com.CommandText = "Select * FROM AspNetRoles";
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
sb.AppendLine(reader["Id"] + " : " + reader["Name"]);
}
}
con.Close(); // Close the connection to the database
}
return sb.ToString();
}
//开始查询
private void Btn_startFindBooks_Click(object sender, System.EventArgs e)
{
//
listview_books.Items.Clear();
//
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(sDataBaseStr);
conn.Open();
//
int nSel = combo_type.SelectedIndex;
string sql_findbook = "select * from RentBookInfo order by personCardNum";
//
if (nSel == 1)
{
sql_findbook = string.Format("select * from RentBookInfo where bookName like '%{0}%' order by personCardNum", tb_condition.Text);
}
else if (nSel == 2)
{
sql_findbook = string.Format("select * from RentBookInfo where bookISBN = '{0}' order by personCardNum", tb_condition.Text);
}
else if (nSel == 3)
{
sql_findbook = string.Format("select * from RentBookInfo where bookPublisher = '{0}' order by personCardNum", tb_condition.Text);
}
//
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandText = sql_findbook;
cmd.Connection = conn;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
this.listview_books.BeginUpdate();
while (reader.Read())
{
string bookName = reader.GetString(6);
string bookISBN = reader.GetString(0);
string personCardNum = reader.GetString(1);
string bookOutDate = reader.GetString(4);
string bookBackDate = reader.GetString(5);
string bookValue = reader.GetString(3);
string publisher = reader.GetString(2);
//
bool isOver = compareIsOverDue(bookBackDate);
//
ListViewItem lvi = new ListViewItem();
lvi.Text = bookName;
lvi.SubItems.Add(bookISBN);
lvi.SubItems.Add(personCardNum);
lvi.SubItems.Add(bookOutDate);
lvi.SubItems.Add(bookBackDate);
lvi.SubItems.Add(bookValue);
lvi.SubItems.Add(publisher);
if (isOver)
{
lvi.BackColor = Color.Red;//过期标红色
}
listview_books.Items.Add(lvi);
}
this.listview_books.EndUpdate();
}
//
reader.Close();
cmd.Dispose();
conn.Close();
conn.Dispose();
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
}
protected void continuer(object o, System.EventArgs arg)
{
DataSet ds = new DataSet();
var connectionString = String.Format("Data Source={0};Version=3;", "./App_Data/Madera.db");
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=|DataDirectory|Madera.db"))
{
con.Open();
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT id_adresse FROM adresse " +
"WHERE libelle = @libelle " +
"AND numero = @numero " +
"AND code_postal = @cp " +
"AND ville = @ville " +
"AND pays = @pays;";
cmd.Parameters.AddWithValue("@libelle", adresseClient.Text);
cmd.Parameters.AddWithValue("@numero", numClient.Text);
cmd.Parameters.AddWithValue("@cp", postalClient.Text);
cmd.Parameters.AddWithValue("@ville", villeClient.Text);
cmd.Parameters.AddWithValue("@pays", paysClient.Text);
System.Data.SQLite.SQLiteDataAdapter da = new System.Data.SQLite.SQLiteDataAdapter(cmd);
da.Fill(ds);
int i = 0;
string id_adresse = "";
if (ds.Tables[0].Rows.Count > 0)
{
while (i < ds.Tables[0].Rows.Count)
{
id_adresse = ds.Tables[0].Rows[i].ItemArray[0].ToString();
i++;
}
}
else
{
cmd.CommandText = "INSERT INTO adresse(libelle,numero,code_postal,ville,pays) " +
"VALUES(@libelle,@numero,@cp,@ville,@pays)";
cmd.Parameters.AddWithValue("@libelle", adresseClient.Text);
cmd.Parameters.AddWithValue("@numero", numClient.Text);
cmd.Parameters.AddWithValue("@cp", postalClient.Text);
cmd.Parameters.AddWithValue("@ville", villeClient.Text);
cmd.Parameters.AddWithValue("@pays", paysClient.Text);
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT id_adresse FROM adresse " +
"WHERE libelle = @libelle " +
"AND numero = @numero " +
"AND code_postal = @cp " +
"AND ville = @ville " +
"AND pays = @pays;";
cmd.Parameters.AddWithValue("@libelle", adresseClient.Text);
cmd.Parameters.AddWithValue("@numero", numClient.Text);
cmd.Parameters.AddWithValue("@cp", postalClient.Text);
cmd.Parameters.AddWithValue("@ville", villeClient.Text);
cmd.Parameters.AddWithValue("@pays", paysClient.Text);
da = new System.Data.SQLite.SQLiteDataAdapter(cmd);
ds.Clear();
da.Fill(ds);
while (i < ds.Tables[0].Rows.Count)
{
id_adresse = ds.Tables[0].Rows[i].ItemArray[0].ToString();
i++;
}
}
con.Close();
Session["nomProjet"] = nomProjet.Text;
Session["dateProjet"] = DateTime.Now.ToString("yyyy/MM/dd");
Session["idClient"] = client.SelectedValue;
Session["idAdresse"] = id_adresse;
}
}
Response.Redirect("/CreationPlan");
}
private void btn_saveMod_Click(object sender, EventArgs e)
{
DialogResult result = MessageBox.Show("Sei sicuro di voler salvare le modifiche? Tutte le vecchie informazioni verranno sovrascritte",
"Attenzione", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
if (checkValidationCert() && cashControl() && checkValidationDate())
{
//sql update
int tipoAbb = 0;
if (!tb_ingressi.Text.ToString().Equals("0") || !tb_ingressi.Text.ToString().Equals(""))
{
tipoAbb = 1; //abbonameno a ingressi
}
else
{
tipoAbb = 0;
} //abonameno std
if (checkUsefulFields() && checkCorsi())
{
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=gestionalePalestra.db"))
{
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open();
//controllo costo rispettato e date inserite bene
//-------------------------------------------------------------------QUERY INSERT ISCRITTI
string command = "UPDATE Iscritto SET Nome='" + tb_nome.Text + "', Cognome='" + tb_cognome.Text + "', DataN='" + tb_dataN.Text + "', " +
"Residenza='" + tb_residenza.Text + "', Via='" + tb_via.Text + "', Recapito='" + tb_recapito.Text + "', Ntessera='" + tb_nTessera.Text +
"', DataIn='" + tb_dataIn.Text + "', " + "DataFine='" + tb_dataFine.Text + "', NIngressi='" + tb_ingressi.Text + "', TipoAbb='" + tipoAbb
+ "', Costo='" + tb_costo.Text + "' " + "WHERE CodIscritto='" + nTessera + "'";
cmd.CommandText = command;
cmd.ExecuteNonQuery();
//MessageBox.Show(command);
//-------------------------------------------------------------------QUERY INSERT FREQUENTA (eliminare tutte quelle esistenti e rifarla)
command = "DELETE FROM Frequenta WHERE CodIscritto='" + nTessera + "'";
cmd.CommandText = command;
cmd.ExecuteNonQuery();
int IdCorso = 0;
foreach (DataGridViewRow row in dgv_corsi.Rows)
{
command = "SELECT Id FROM Corso WHERE Nome='" + row.Cells[0].Value + "'";
cmd.CommandText = command;
cmd.ExecuteNonQuery();
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
reader.Read();
IdCorso = reader.GetInt32(0);
}
command = "INSERT INTO Frequenta values ('" + nTessera + "','" + IdCorso + "')";
cmd.CommandText = command;
cmd.ExecuteNonQuery();
//MessageBox.Show(command);
}
//-------------------------------------------------------------------QUERY INSERT RATE
command = "DELETE FROM Rata WHERE CodIscritto='" + nTessera + "'";
cmd.CommandText = command;
cmd.ExecuteNonQuery();
foreach (DataGridViewRow row in dgv_rate.Rows)
{
command = "INSERT INTO Rata values ('" + nTessera + "','" + row.Cells[0].Value + "','" + row.Cells[2].Value + "','" + row.Cells[1].Value + "')";
cmd.CommandText = command;
cmd.ExecuteNonQuery();
//MessageBox.Show(command);
}
//-------------------------------------------------------------------QUERY INSERT CERTIFICATO
if (tb_pres.Text.ToUpper().Equals("SI"))
{
command = "UPDATE Certificato SET Presente='SI', DataScadenza='" + tb_dataScad.Text + "' WHERE CodIscritto='" + nTessera + "'";
}
else
{
command = "UPDATE Certificato SET Presente='NO', DataScadenza='" + tb_dataScad.Text + "' WHERE CodIscritto='" + nTessera + "'";
}
cmd.CommandText = command;
cmd.ExecuteNonQuery();
//MessageBox.Show(command);
conn.Close();
}
}
MessageBox.Show("Inserimento avvenuto con successo!", "Inserimento");
this.Close();
}
}
}
}
public static void ReadCookies(string dbPath, string domain, bool enc, string name)
{
string connectionString = "Data Source=" + dbPath + ";pooling=false";
using (var conn = new System.Data.SQLite.SQLiteConnection(connectionString))
using (var cmd = conn.CreateCommand())
{
string sql;
if (enc == false)
{
sql = "SELECT path,name,value,expires_utc,host_key FROM cookies WHERE host_key LIKE '%";
}
else
{
sql = "SELECT path,name,encrypted_value,expires_utc,host_key FROM cookies WHERE host_key LIKE '%";
}
sql += domain + "%'";
if (name != string.Empty)
{
sql += " AND name = '" + name + "'";
}
cmd.CommandText = sql;
conn.Open();
using (var reader = cmd.ExecuteReader())
{
string cookieValue;
while (reader.Read())
{
string cookiePath = (string)reader[0];
string cookieName = (string)reader[1];
Int64 cookieExpire = (Int64)reader[3];
string cookieDomain = (string)reader[4];
if (enc == false)
{
cookieValue = (string)reader[2];
}
else
{
var encData = (byte[])reader[2];
var decData = ProtectedData.Unprotect(encData, null, DataProtectionScope.CurrentUser);
cookieValue = Encoding.ASCII.GetString(decData);
}
Cookie cookie = new Cookie
{
path = cookiePath,
domain = cookieDomain,
name = cookieName,
value = cookieValue,
expirationdate = cookieExpire
};
string json = JsonConvert.SerializeObject(cookie);
Console.WriteLine(json);
}
}
conn.Close();
}
}
private async void OnCompleteButtonClickAsync(object sender, RoutedEventArgs e)
{
Quest quest = (Quest)(sender as FrameworkElement).DataContext;
if (!quest.Status.Equals("Complete"))
{
string sqlQuery = "SELECT * FROM Character WHERE Id = " + quest.ArcId;
string path = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "QuestArc.db3");
using (var dbConnection = new System.Data.SQLite.SQLiteConnection("Data Source = " + path))
{
using (var dbcmd = new System.Data.SQLite.SQLiteCommand())
{
dbConnection.Open();
dbcmd.Connection = dbConnection;
dbcmd.CommandText = sqlQuery;
IDataReader reader = dbcmd.ExecuteReader();
string characterId = null;
int points = 0;
while (reader.Read())
{
characterId = reader["Id"].ToString();
points = (int)(long)reader["UnallocatedPoints"];
}
reader.Close();
string difficulty = quest.Difficulty;
int numPoints = 0;
switch (difficulty)
{
case "Easy":
numPoints = 10 + points;
break;
case "Normal":
numPoints = 20 + points;
break;
case "Hard":
numPoints = 30 + points;
break;
}
dbcmd.Parameters.AddWithValue("@id", quest.Id);
dbcmd.Parameters.AddWithValue("@status", "Complete");
dbcmd.CommandText = @"UPDATE Quest SET Status = @status WHERE Id = @id";
dbcmd.ExecuteNonQuery();
dbcmd.Parameters.AddWithValue("@id", characterId);
dbcmd.Parameters.AddWithValue("@points", numPoints);
dbcmd.CommandText = @"UPDATE Character SET UnallocatedPoints = @points WHERE Id = @id";
dbcmd.ExecuteNonQuery();
dbConnection.Close();
}
this.Bindings.Update();
}
ContentDialog dialog = new ContentDialog();
dialog.Title = "Quest Complete!";
dialog.CloseButtonText = "Ok";
await dialog.ShowAsync();
}
else
{
ContentDialog dialog = new ContentDialog();
dialog.Title = "Quest is already Complete";
dialog.CloseButtonText = "Ok";
await dialog.ShowAsync();
}
}
public void insertFile(String file)
{
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=databaseFile.db3"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open();
com.CommandText = "INSERT INTO LASTPROJECTS (address) Values ('"+ file +"')";
com.ExecuteNonQuery();
con.Close();
}
}
}
private static void KML2SQLDO(string filename)
{
Console.WriteLine("Importing file \"{0}\"...", Path.GetFileName(filename));
string filePrefix = Transliteration.Front(Path.GetFileName(filename).ToUpper().Substring(0, 2));
System.Data.SQLite.SQLiteConnection sqlc = new System.Data.SQLite.SQLiteConnection(String.Format("Data Source={0};Version=3;", OruxPalsServerConfig.GetCurrentDir() + @"\StaticObjects.db"));
sqlc.Open();
{
System.Data.SQLite.SQLiteCommand sc = new System.Data.SQLite.SQLiteCommand("SELECT MAX(IMPORTNO) FROM OBJECTS", sqlc);
int import = 1;
try { string a = sc.ExecuteScalar().ToString(); int.TryParse(a, out import); import++; }
catch { };
string importTemplate = "insert into OBJECTS (LAT,LON,SYMBOL,[NAME],COMMENT,IMPORTNO,SOURCE) VALUES ({0},{1},'{2}','{3}','{4}',{5},'{6}')";
XmlDocument xd = new XmlDocument();
using (XmlTextReader tr = new XmlTextReader(filename))
{
tr.Namespaces = false;
xd.Load(tr);
};
string defSymbol = "\\C";
XmlNode NodeSymbol = xd.SelectSingleNode("/kml/symbol");
if (NodeSymbol != null)
{
defSymbol = NodeSymbol.ChildNodes[0].Value;
}
string defFormat = "R{0:000}-{1}"; // {0} - id; {1} - file prefix; {2} - Placemark Name without spaces
XmlNode NodeFormat = xd.SelectSingleNode("/kml/format");
if (NodeFormat != null)
{
defFormat = NodeFormat.ChildNodes[0].Value;
}
XmlNodeList nl = xd.GetElementsByTagName("Placemark");
List <PreloadedObject> fromKML = new List <PreloadedObject>();
if (nl.Count > 0)
{
for (int i = 0; i < nl.Count; i++)
{
string pName = System.Security.SecurityElement.Escape(Transliteration.Front(nl[i].SelectSingleNode("name").ChildNodes[0].Value));
pName = Regex.Replace(pName, "[\r\n\\(\\)\\[\\]\\{\\}\\^\\$\\&\']+", "");
string pName2 = Regex.Replace(pName.ToUpper(), "[^A-Z0-9\\-]+", "");
string symbol = defSymbol;
if (nl[i].SelectSingleNode("symbol") != null)
{
symbol = nl[i].SelectSingleNode("symbol").ChildNodes[0].Value.Trim();
}
if (nl[i].SelectSingleNode("Point/coordinates") != null)
{
string pPos = nl[i].SelectSingleNode("Point/coordinates").ChildNodes[0].Value.Trim();
string[] xyz = pPos.Split(new char[] { ',' }, 3);
sc.CommandText = String.Format(importTemplate, new object[] {
xyz[1], xyz[0], symbol.Replace("'", @"''"),
String.Format(defFormat, i + 1, filePrefix, pName2),
pName, import, Path.GetFileName(filename)
});
sc.ExecuteScalar();
}
;
Console.Write("Import Placemark {0}/{1}", i + 1, nl.Count);
Console.SetCursorPosition(0, Console.CursorTop);
}
}
;
Console.WriteLine();
Console.WriteLine("Done");
};
sqlc.Close();
}
/// <summary>
/// 连接地址是否正确
/// </summary>
/// <returns>true:正确;false:错误</returns>
private bool ConnectAddressIstrue()
{
bool result = false;
using (System.Data.SQLite.SQLiteConnection connection = new System.Data.SQLite.SQLiteConnection(serverAddress))
{
try
{
connection.Open();
connection.Close();
result = true;
}
catch
{
}
finally
{
connection.Close();
}
}
return result;
}
private void newAccountButton_Click(object sender, EventArgs e)
{
Directory.CreateDirectory(Variables.databaseFolder);
string newAccountName = "";
DialogResult dr = new DialogResult();
NameAccount nameAccount = new NameAccount();
dr = nameAccount.ShowDialog();
if (Variables.accountName != "")
{
newAccountName = Variables.accountName + ".db";
// This is the query which will create a new table in our database file with three columns. An auto increment column called "ID", and two NVARCHAR type columns with the names "Key" and "Value"
string createMonthlyTableQuery = @"CREATE TABLE IF NOT EXISTS [Monthly] (
[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
[Active] INTEGER NOT NULL,
[Description] TEXT NOT NULL,
[Category] INTEGER NULL,
[Amount] NUMERIC NOT NULL
)";
string createYearlyTableQuery = @"CREATE TABLE IF NOT EXISTS [Yearly] (
[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
[Active] INTEGER NOT NULL,
[Description] TEXT NOT NULL,
[Category] INTEGER NULL,
[Amount] NUMERIC NOT NULL
)";
string createWantedTableQuery = @"CREATE TABLE IF NOT EXISTS [Wanted] (
[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
[Active] INTEGER NOT NULL,
[Description] TEXT NOT NULL,
[Category] INTEGER NULL,
[Amount] NUMERIC NOT NULL
)";
System.Data.SQLite.SQLiteConnection.CreateFile(Path.Combine(Variables.databaseFolder,newAccountName)); // Create the file which will be hosting our database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=" + Path.Combine(Variables.databaseFolder, newAccountName)))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); // Open the connection to the database
com.CommandText = createMonthlyTableQuery; // Set CommandText to our query that will create the table
com.ExecuteNonQuery(); // Execute the query
com.CommandText = createYearlyTableQuery; // Set CommandText to our query that will create the table
com.ExecuteNonQuery(); // Execute the query
com.CommandText = createWantedTableQuery; // Set CommandText to our query that will create the table
com.ExecuteNonQuery(); // Execute the query
con.Close(); // Close the connection to the database
}
}
Variables.dataPath = Variables.connectionString + Variables.databaseFolder + @"\" + Variables.accountName + ".db";
string fullDbPath = Variables.databaseFolder + @"\" + Variables.accountName + ".db";
if (File.Exists(fullDbPath))
{
this.Close();
}
else
{
MessageBox.Show("I cannot find the database captain. I must abort!", "Database Not Found", MessageBoxButtons.OK, MessageBoxIcon.Error);
Application.Exit();
}
}
}
