public static void InsertDataCSV(string baseName = "ConvertBinFile.csv", HeaderTradeRecord fileBinRead = new HeaderTradeRecord())
{
try
{
string fName = Path.GetFileNameWithoutExtension(baseName);
string path = Path.GetDirectoryName(baseName);
baseName = path + "\\" + fName + ".csv";
StreamWriter file = new StreamWriter(baseName, false, System.Text.Encoding.GetEncoding(1251));
file.WriteLine("id, account, volume, comment");
foreach (Model.TradeRecord t in fileBinRead.trades)
{
file.WriteLine(t.id + ", " + t.account + ", '" + t.volume + "', '" + t.comment);
}
file.Close();
SavePathFileConvert(baseName, "csv");
}
catch (Exception x)
{
Console.WriteLine("Error InsertDataCSV SQL: " + x.Message);
WorkingBD.SaveLog("Error InsertDataCSV SQL: " + x.Message);
}
}
public static void InsertDataBDSQL(string baseName = "ConvertBinFile.db3", HeaderTradeRecord fileBinRead = new HeaderTradeRecord())
{
try
{
string fName = Path.GetFileNameWithoutExtension(baseName);
string path = Path.GetDirectoryName(baseName);
baseName = path + "\\" + fName + ".db3";
WorkingBD.CreateBDSQL(baseName);
baseNameLastSQL = baseName;
using (SQLiteConnection connection = new SQLiteConnection(string.Format("Data Source={0};", baseName)))
{
connection.Open();
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = @"DELETE FROM [Header];";
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
command.CommandText = @"DELETE FROM [TradeRecord];";
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
command.CommandText = @"INSERT INTO [Header] ([version], [type]) VALUES (" + fileBinRead.version + ", '" + fileBinRead.type + "');";
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
foreach (Model.TradeRecord t in fileBinRead.trades)
{
command.CommandText = @"INSERT INTO [TradeRecord] ([id], [account], [volume], [comment]) VALUES ("
+ t.id + ", " + t.account + ", '" + t.volume + "', '" + t.comment + "');";
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
}
}
SavePathFileConvert(baseName, "sql");
}
catch (Exception x)
{
Console.WriteLine("Error InsertDataBD SQL: " + x.Message);
WorkingBD.SaveLog("Error InsertDataBD SQL: " + x.Message);
}
}
public static TradeRecord SelectDataBDSQL(int id = 0)
{
TradeRecord treadRec = new TradeRecord();
string baseName = baseNameLastSQL;
if (baseName.Length != 0)
{
try
{
using (SQLiteConnection connection = new SQLiteConnection(string.Format("Data Source={0};", baseName)))
{
connection.Open();
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = @"SELECT [id], [account], [volume], [comment] FROM [TradeRecord] WHERE [id]=" + id + ";";
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
SQLiteDataReader reader = command.ExecuteReader();
foreach (DbDataRecord record in reader)
{
treadRec.id = Int32.Parse(record["id"].ToString());
treadRec.account = Int32.Parse(record["account"].ToString());
treadRec.volume = (Double)record["volume"];
treadRec.comment = record["comment"].ToString();
}
}
}
}
catch (Exception x)
{
Console.WriteLine("Error InsertDataBD SQL: " + x.Message);
WorkingBD.SaveLog("Error InsertDataBD SQL: " + x.Message);
}
}
else
{
Console.WriteLine("File is null");
}
return(treadRec);
}
public static void SaveSerializXML(string path, List <ConvFile> obj)
{
try
{
if (File.Exists(path))
{
File.Delete(path);
}
XmlSerializer serialiser = new XmlSerializer(typeof(List <ConvFile>));
TextWriter FileStream = new StreamWriter(path, true, System.Text.Encoding.GetEncoding("Windows-1251"));
serialiser.Serialize(FileStream, obj);
FileStream.Close();
}
catch (Exception x)
{
Console.WriteLine("Error SaveSerializXML: " + x.Message);
WorkingBD.SaveLog("Error SaveSerializXML: " + x.Message);
}
}
public static List <ConvFile> LoadSerializXML(string FileName = "")
{
try
{
List <ConvFile> obj = null;
if (File.Exists(FileName))
{
Stream TestFileStream = File.OpenRead(FileName);
XmlSerializer deserializer = new XmlSerializer(typeof(List <ConvFile>));
obj = (List <ConvFile>)deserializer.Deserialize(TestFileStream);
TestFileStream.Close();
}
return(obj);
}
catch (Exception x)
{
Console.WriteLine("Error LoadSerializXML: " + x.Message);
WorkingBD.SaveLog("Error LoadSerializXML: " + x.Message);
}
return(null);
}
public static void CreateBDSQL(string baseName = "ConvertBinFile.db3")
{
try
{
if (!File.Exists(baseName))
{
SQLiteConnection.CreateFile(baseName);
using (SQLiteConnection connection = new SQLiteConnection(string.Format("Data Source={0};", baseName)))
{
connection.ConnectionString = "Data Source = " + baseName;
connection.Open();
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = @"CREATE TABLE [Header] (
[version] int NOT NULL,
[type] char(16) NOT NULL
);";
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
command.CommandText = @"CREATE TABLE [TradeRecord] (
[id] int NOT NULL,
[account] int NOT NULL,
[volume] REAL NOT NULL,
[comment] char(64) NOT NULL
);";
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
}
}
}
catch (Exception x)
{
Console.WriteLine("Error CreateBD SQL: " + x.Message);
WorkingBD.SaveLog("Error CreateBD SQL: " + x.Message);
}
}
