public async Task Load(SQLiteConnection connection)
{
using (var command = new SQLiteCommand("SELECT * FROM `Tracks`", connection))
{
var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
{
PlayableBase track;
using (var xmlReader = reader.GetTextReader(8))
track = (PlayableBase) _serializer.Deserialize(xmlReader);
track.Title = reader.GetString(0);
track.Artist = _artistProvider.ArtistDictionary[reader.ReadGuid(1)];
var albumGuid = reader.ReadGuid(2);
if (albumGuid != Guid.Empty)
track.Album = _albumsProvider.Collection[albumGuid];
track.Guid = reader.ReadGuid(3);
track.LastTimePlayed = reader.GetDateTime(4);
track.MusicBrainzId = reader.GetValue(5)?.ToString();
track.Duration = XmlConvert.ToTimeSpan(reader.GetString(6));
var coverId = reader.ReadGuid(7);
if (coverId != Guid.Empty)
track.Cover = _imageProvider.Collection[coverId];
Collection.Add(track.Guid, track);
Tracks.Add(track);
}
}
_connection = connection;
}
/// <summary>
/// Removes the old entries (entries that have been in the database more than the specified time) from the database.
/// </summary>
/// <param name="olderThan"> The number of days in the database after which the entry is considered old... </param>
public static void CleanNewsOlderThan(int olderThan)
{
try
{
using (SQLiteConnection sqLiteConnection = new SQLiteConnection(ConnectionString))
{
sqLiteConnection.Open();
DateTime olderDate = DateTime.Now;
TimeSpan daySpan = new TimeSpan(olderThan, 0, 0, 0);
olderDate = olderDate.Subtract(daySpan);
SQLiteCommand sqLiteCommand = new SQLiteCommand(sqLiteConnection)
{
CommandText = "DELETE " +
"FROM NEWS_STORAGE " +
"WHERE NEWSITEM_AQUISITION_DATE <= ?"
};
sqLiteCommand.Parameters.AddWithValue(null, olderDate);
sqLiteCommand.ExecuteNonQuery();
sqLiteConnection.Close();
}
}
catch (Exception ex)
{
ErrorMessageBox.Show(ex.Message, ex.ToString());
Logger.ErrorLogger("error.txt", ex.ToString());
}
}
public static void insertdb(Message m)
{
string _connstr = getdb();
string ins_ = "insert into mail (from_,to_,subject_,body_,dot_,reci_dt)values(?,?,?,?,?,?);";
using (SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(_connstr))
{
conn.Open();
try
{
using (SQLiteCommand cmd = new SQLiteCommand(ins_, conn))
{
cmd.Parameters.AddWithValue("@from_", m.from);
cmd.Parameters.AddWithValue("@to_", m.to);
cmd.Parameters.AddWithValue("@subject_", m.subject);
cmd.Parameters.AddWithValue("@body_", m.body);
cmd.Parameters.AddWithValue("@dot_", m.dot);
cmd.Parameters.AddWithValue("@reci_dt", DateTime.Now.ToString("yyyyMMddHHmmss"));
cmd.ExecuteNonQuery();
}
}
catch (Exception ep)
{
Console.WriteLine(ep.Message);
}
conn.Close();
}
}
/// <summary>
/// Constructs the transaction object, binding it to the supplied connection
/// </summary>
/// <param name="connection">The connection to open a transaction on</param>
/// <param name="deferredLock">TRUE to defer the writelock, or FALSE to lock immediately</param>
internal SQLiteTransaction(SQLiteConnection connection, bool deferredLock)
{
_cnn = connection;
_version = _cnn._version;
_level = (deferredLock == true) ? IsolationLevel.ReadCommitted : IsolationLevel.Serializable;
if (_cnn._transactionLevel++ == 0)
{
try
{
using (SQLiteCommand cmd = _cnn.CreateCommand())
{
if (!deferredLock)
cmd.CommandText = "BEGIN IMMEDIATE";
else
cmd.CommandText = "BEGIN";
cmd.ExecuteNonQuery();
}
}
catch (SQLiteException)
{
_cnn._transactionLevel--;
_cnn = null;
throw;
}
}
}
public static AttendanceReport[] GetAttendaceSummaryReports(string data_path)
{
SQLiteConnection conn = new SQLiteConnection(@"Data Source=" + data_path);
conn.Open();
SQLiteCommand cmd = new SQLiteCommand(conn);
cmd.CommandText = "select * from AttendanceSummary";
SQLiteDataReader reader = cmd.ExecuteReader();
List<AttendanceReport> attendanceReports = new List<AttendanceReport>();
while (reader.Read())
{
int ID = reader.GetInt32(0);
string timeIN = reader.GetString(1);
string timeOUT = reader.GetString(2);
string deltaTime = reader.GetString(3);
string activity = reader.GetString(4);
string mentor = reader.GetString(5);
int index = reader.GetInt32(6);
attendanceReports.Add(new AttendanceReport(ID, timeIN, timeOUT, deltaTime, activity, mentor, index));
}
reader.Close();
conn.Close();
return attendanceReports.ToArray();
}
private void DeleteCardBase()
{
if (dataGridView1.SelectedRows.Count > 0)
{
id = Int32.Parse(dataGridView1.SelectedRows[0].Cells[0].Value.ToString());
if (dataGridView1.RowCount != 0) index = dataGridView1.SelectedRows[0].Index;
DialogResult result = MessageBox.Show("Czy na pewno chcesz usunąć bazę karty numer " + id + "? \n\nOperacji nie można cofnąć.", "Ważne", MessageBoxButtons.YesNo, MessageBoxIcon.Warning);
if (result == DialogResult.Yes)
using (SQLiteConnection conn = new SQLiteConnection(connString))
{
conn.Open();
SQLiteCommand command = new SQLiteCommand(conn);
command.CommandText = "DELETE FROM [CardBase] WHERE id = @id";
command.Parameters.Add(new SQLiteParameter("@id", id));
command.ExecuteNonQuery();
conn.Close();
Odswierz();
if (dataGridView1.RowCount != 0)
{
if (index == dataGridView1.RowCount) dataGridView1.CurrentCell = dataGridView1.Rows[index - 1].Cells[0];
else dataGridView1.CurrentCell = dataGridView1.Rows[index].Cells[0];
}
}
}
}
public static DataTable GetAllContacts(string sqlitefile)
{
DataTable dt = new DataTable("Contact");
try
{
SQLiteConnection con = new SQLiteConnection("data source=" + sqlitefile);
dt = ExecuteNonQueryDt("Select * From Contact Order By ContactID", con);
return dt;
}
catch(Exception ex)
{
using (FileStream file = new FileStream(AppDomain.CurrentDomain.BaseDirectory + "\\" + System.DateTime.Now.Date.ToString("dd-MMM-yyyy") + "_Log.txt", FileMode.Append, FileAccess.Write))
{
StreamWriter streamWriter = new StreamWriter(file);
streamWriter.WriteLine(System.DateTime.Now + " - " + "GetAllContacts" + " - " + ex.Message.ToString());
streamWriter.Close();
}
if (File.Exists(sqlitefile))
{
File.Delete(sqlitefile);
}
return dt;
}
}
static void Main(string[] args)
{
// Create sqlite connection
SQLiteConnection connection = new SQLiteConnection(string.Format(@"Data Source={0}\SimpleDatabase.s3db", Environment.CurrentDirectory));
// Open sqlite connection
connection.Open();
// Get all rows from example_table
SQLiteDataAdapter db = new SQLiteDataAdapter("SELECT * FROM Names", connection);
// Create a dataset
DataSet ds = new DataSet();
// Fill dataset
db.Fill(ds);
// Create a datatable
DataTable dt = new DataTable("Names");
dt = ds.Tables[0];
// Close connection
connection.Close();
// Print table
foreach (DataRow row in dt.Rows)
{
Console.WriteLine(string.Format("{0} {1}", row["Firstname"], row["Surname"]));
}
Console.ReadLine();
}
public addDeletionInstructionDialog(string workingDirectory, modEditor me, SQLiteConnection conn, int editing)
{
InitializeComponent();
this.workingDirectory = workingDirectory;
this.me = me;
this.conn = conn;
this.editing = editing;
if (editing != 0)
{
string sql = "SELECT file_name, type FROM files_delete WHERE id = " + editing + " LIMIT 1";
SQLiteCommand command = new SQLiteCommand(sql, conn);
SQLiteDataReader reader = command.ExecuteReader();
while (reader.Read())
{
char[] chars = { '/' };
string[] pieces = reader["file_name"].ToString().Split(chars, 2);
if (pieces.Length == 1)
fileName.Text = pieces[0];
else if (pieces.Length == 2)
{
filePrefix.SelectedItem = pieces[0];
fileName.Text = pieces[1];
}
whatIs_Dir.Checked = reader["type"].ToString() == "dir";
whatIs_File.Checked = reader["type"].ToString() == "file";
}
}
}
static public void SQLiteLoadCurrenciesFromDatabase()
{
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("Data Source=MSBase.sqlite;Version=3;");
conn.Open();
//sql_command = "INSERT INTO MSCurrencies (MSCurrencyId, MSCurrencyDate, MSCurrencyType, MSCurrencyCode, MSCurrencyNominal, MSCurrencyName, MSCurrencyValue) "
// + "VALUES ( 0, 1532044620, '', 'AZN', 1, '', 1);";
SQLiteCommand cmd = new SQLiteCommand("", conn);
cmd.CommandText = "SELECT MSCurrencyId, MSCurrencyDate, MSCurrencyType, MSCurrencyCode, MSCurrencyNominal, MSCurrencyName, MSCurrencyValue"
+ " FROM MSCurrencies";
try
{
SQLiteDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Program.currency.MSCurrencyId = int.Parse(dr["MSCurrencyId"].ToString());
Program.currency.MSCurrencyDate = DateTime.Parse((string)dr["MSCurrencyDate"]);
Program.currency.MSCurrencyType = (string)dr["MSCurrencyType"];
Program.currency.MSCurrencyCode = (string)dr["MSCurrencyCode"];
Program.currency.MSCurrencyNominal = int.Parse(dr["MSCurrencyNominal"].ToString());
Program.currency.MSCurrencyName = (string)dr["MSCurrencyName"];
Program.currency.MSCurrencyValue = float.Parse(dr["MSCurrencyValue"].ToString());
Program.currencies.Add(new MSCurrency(Program.currency));
}
dr.Close();
}
catch (SQLiteException ex)
{
Console.WriteLine(ex.Message);
}
conn.Close();
}
/** Function to add a team to be officially registered
@param string for the Team's name (which is country name)
*/
public void AddTeam(string TeamName)
{
//!< Impeded sql statement to insert value into database
string InsertDB = "INSERT INTO 'Team Information' ('TeamName') VALUES('" + TeamName + "');";
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //!< Opens DB
if (con.State == ConnectionState.Open) //!< If connection, open was successful
{
//!< This sends sql commands to database
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(InsertDB, con);
da.Fill(ds);
}
else
{
MessageBox.Show("Connection failed.");
}
}
con.Close();
MessageBox.Show(TeamName + " has been added to database!");
}
}
/** Void function adds female to database for the event
@param string for score entry
@param string for particular event score derived from
@param string for first name of athlete
@param string for last name of athlete
*/
public void EnterScore(double entry, string selectedEvent, string Fname, string Lname)
{
//!< Creates variable for connection string
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //!< Opens DB
//!< Checks connection and fills grid view with Race table
if (con.State == ConnectionState.Open)
{
//!< Adds entry from event to the specific event
string sql = String.Format("UPDATE \"" + selectedEvent + "\" SET Score = " + entry +
" WHERE \"Athlete First Name\" = '" + Fname + "' AND \"Athlete Last Name\" = '" + Lname + "';");
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(sql, con);
da.Fill(ds);
}
else
{
MessageBox.Show("Connection failed.");
}
}
}
MessageBox.Show("Score has been entered into " + Fname + " " + Lname + "'s score");
}
public void deleteAthlete(string Fname, string Lname)
{
//!< Creates variable for connection string
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //!< Opens DB
//!< Checks connection and fills grid view with Race table
if (con.State == ConnectionState.Open)
{
//!< Adds entry to athlete information
string sql = String.Format("DELETE FROM 'Athlete Information' WHERE AthleteFirstName = '" + Fname +
"' AND AthleteLastName = '" + Lname + "';");
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(sql, con);
da.Fill(ds);
}
else
{
MessageBox.Show("Connection failed.");
}
}
MessageBox.Show("Entry has been deleted from database!");
con.Close();
}
}
/** Void function adds male to database for the event
@param string for athlete's team name
@param string for first name of athlete
@param string for last name of athlete
*/
public void registerAthlete(string Fname, string Lname, string Gender, string team)
{
//!< Creates variable for connection string
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //!< Opens DB
//!< Checks connection and fills grid view with Race table
if (con.State == ConnectionState.Open)
{
//!< Adds entry to athlete information
string sql = String.Format("INSERT INTO 'Athlete Information' ('TeamName' , 'AthleteFirstName', 'AthleteLastName', 'Gender') " +
"VALUES ('" + team + "', '" + Fname + "', '" + Lname + "', '" + Gender + "');");
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(sql, con);
da.Fill(ds);
}
else
{
MessageBox.Show("Connection failed.");
}
}
MessageBox.Show("Entry has been entered into Database!");
con.Close();
}
}
public void DeleteTeam(string TeamName)
{
//!< Impeded sql statement to delete value from database
string DeleteDB = "DELETE FROM 'team information' WHERE TeamName = '" + TeamName + "';";
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //!< Opens DB
if (con.State == ConnectionState.Open) //!< If connection, open was successful
{
//!< This sends sql commands to database
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(DeleteDB, con);
da.Fill(ds);
}
else
{
MessageBox.Show("Connection failed.");
}
}
con.Close();
MessageBox.Show(TeamName + " has been removed from database!");
}
}
static void Main(string[] args)
{
string createQuery = @"CREATE TABLE IF NOT EXISTS
[Mytable](
[ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[NAME] NVARCHAR(2048) NOT NULL,
[GENDER] NVARCHAR(2048) NOT NULL)";
System.Data.SQLite.SQLiteConnection.CreateFile("simple.db3");
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("Data Source=database.simple.db3"))
{
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open();
cmd.CommandText = createQuery;
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO mytable(NAME,GENDER) values('alex','male')";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO mytable(NAME,GENDER) values('diane','female')";
cmd.ExecuteNonQuery();
cmd.CommandText = "select * from Mytable";
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(reader["NAME"] + ":" + reader["GENDER"]);
}
conn.Close();
}
}
}
Console.ReadLine();
}
public static DataTable GetDataTable(string sql)
{
DataTable dt = new DataTable();
try
{
SQLiteConnection cnn = new SQLiteConnection(@"Data Source=C:\Projects\showdownsharp\db\showdown.db");
cnn.Open();
SQLiteCommand mycommand = new SQLiteCommand(cnn);
mycommand.CommandText = sql;
SQLiteDataReader reader = mycommand.ExecuteReader();
dt.Load(reader);
reader.Close();
cnn.Close();
} catch {
// Catching exceptions is for communists
}
return dt;
}
static public void SQLiteLoadCategoriesFromDatabase()
{
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("Data Source=MSBase.sqlite;Version=3;");
conn.Open();
//sql_command = "INSERT INTO MSCategories (MSCategoryId, MSIO, MSName, MSAccountId, MSColor, MSImage) "
// + "VALUES ( 0, '+', 'Зарплата', 1, 1, ';)');";
SQLiteCommand cmd = new SQLiteCommand("", conn);
cmd.CommandText = "SELECT MSCategoryId, MSIO, MSName, MSAccountId, MSColor, MSImage FROM MSCategories";
try
{
SQLiteDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Program.category.MSCategoryId = int.Parse(dr["MSCategoryId"].ToString());
Program.category.MSIO = ((string)dr["MSIO"])[0];
Program.category.MSName = (string)dr["MSName"];
Program.category.MSAccountId = int.Parse(dr["MSAccountId"].ToString());
Program.category.MSColor = (ConsoleColor)(int.Parse(dr["MSColor"].ToString()));
Program.category.MSImage = (string)dr["MSImage"];
Program.categories.Add(new MSCategory(Program.category));
}
dr.Close();
}
catch (SQLiteException ex)
{
Console.WriteLine(ex.Message);
}
}
public bool IfGetReaderSchema(string tableName, System.Data.SQLite.SQLiteConnection conn, string sql)
{
cmd = new SQLiteCommand();
cmd.CommandText = sql;
cmd.Connection = conn;
SQLiteDataReader reader = cmd.ExecuteReader();
if (!reader.HasRows)
{
if (!reader.IsClosed)
{
reader.Close();
reader.Dispose();
}
return(false);
}
else
{
if (!reader.IsClosed)
{
reader.Close();
reader.Dispose();
}
return(true);
}
}
public static void GetItemsFromDatabase()
{
using (
System.Data.SQLite.SQLiteConnection con =
new System.Data.SQLite.SQLiteConnection("data source=" + inputDir + ".db3"))
{
con.Open();
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
com.CommandText = "SELECT Spectrum.ID,peptide,charge,path FROM Spectrum, Files Where Spectrum.codex = Files.codex";
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
//store id and peptide into an object holding those values
//holderForPairs.Add(reader["id"].ToString() + "\t" + reader["peptide"].ToString());
inputs.Add(new InputListItems(reader["ID"].ToString(), reader["peptide"].ToString(), reader["charge"].ToString(), reader["path"].ToString()));
}
}
}
con.Close(); // Close the connection to the database
}
new MakeCombinations(inputs);
}
//public static int Insert(string dbFile, string sql,)
public static ObservableCollection<Jewelry> GetAll()
{
ObservableCollection<Jewelry> ob = new ObservableCollection<Jewelry>();
using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=c:/xhz/ms.db;"))
//using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=DB/ms.db;"))
{
conn.Open();
string sql = string.Format("select * from detail");
SQLiteCommand cmd = new SQLiteCommand(sql, conn);
using (SQLiteDataReader dr1 = cmd.ExecuteReader())
{
while (dr1.Read())
{
var d = dr1;
var dd = dr1.GetValue(0);
var data = dr1.GetValue(1);
var insert = dr1.GetValue(2);
var update = dr1.GetValue(3);
}
}
conn.Close();
}
ob.Add(new Jewelry());
return ob;
}
private void button2_Click(object sender, EventArgs e)
{
//stores name of athlete seleceted from DB
string DeleteFName = dataGridView1.SelectedCells[1].Value.ToString();
string DeleteLName = dataGridView1.SelectedCells[2].Value.ToString();
//object created to delete entry
Registration DelAthlete = new Registration();
//member function deletes athlete from registration
DelAthlete.deleteAthlete(DeleteFName, DeleteLName);
//this will call a refreshed table
string ViewTable = "SELECT * FROM 'Athlete Information'";//gets team table from database
using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite"))
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con))
{
con.Open(); //opens DB
if (con.State == ConnectionState.Open) // if connection.Open was successful
{
//this sends sql commands to database
DataSet ds = new DataSet();
var da = new SQLiteDataAdapter(ViewTable, con);
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0].DefaultView;
}
else
{
MessageBox.Show("Connection failed.");
}
}
con.Close();
}
}
private void Consulta_Load(object sender, EventArgs e)
{
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
string connString = @"Data Source=" + appPath + @"\EXCL.s3db ;Version=3;";
DataSet DS = new DataSet();
SQLiteConnection con = new SQLiteConnection(connString);
con.Open();
SQLiteDataAdapter DA = new SQLiteDataAdapter("select * from Expediente", con);
DA.Fill(DS, "Expediente");
dataGridView1.DataSource = DS.Tables["Expediente"];
con.Close();
dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells;
int i = 0;
foreach (DataGridViewColumn c in dataGridView1.Columns)
{
i += c.Width;
}
if ((i + dataGridView1.RowHeadersWidth + 2) > 616)
{
dataGridView1.Width = 616;
}
else
{
dataGridView1.Width = i + dataGridView1.RowHeadersWidth + 2;
}
}
private void button5_Click(object sender, EventArgs e)
{
if (textBox13.Text != "" && textBox14.Text != "" && textBox15.Text != "" && textBox16.Text != "")
{
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
System.Data.SQLite.SQLiteConnection sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBPInc.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 = "INSERT INTO Proveedor Values ('" + textBox13.Text + "', '" + textBox14.Text + "', '" + textBox15.Text + "', '" + textBox16.Text + "')";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
textBox13.Text = "";
textBox14.Text = "";
textBox15.Text = "";
textBox16.Text = "";
}
else
{
MessageBox.Show("Faltaron campos por llenar");
}
}
public void CreateLocalDatabase()
{
try
{
System.Data.SQLite.SQLiteConnection.CreateFile(@"Databases/EmployeesDB.db");
Console.WriteLine("Succesfully Created Local Database");
Console.WriteLine("Creating tables...");
string EmployeesTableQuery = @"CREATE TABLE [Employees] (
[ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
[FullName] TEXT NOT NULL,
[BirthDay] TEXT NOT NULL,
[Qualification] TEXT,
[FirstDay] TEXT NOT NULL
)";
using (connection = new System.Data.SQLite.SQLiteConnection(@"data source=Databases/EmployeesDB.db"))
{
System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand(connection);
connection.Open();
command.CommandText = EmployeesTableQuery;
command.ExecuteNonQuery();
connection.Close();
}
}
catch (Exception exception)
{
Console.WriteLine(exception);
}
}
protected void ExecuteWrite(string query, Dictionary <string, object> args)
{
try
{
using (connection = new System.Data.SQLite.SQLiteConnection(@"data source=Databases/EmployeesDB.db"))
{
connection.Open();
using (var cmd = new SQLiteCommand(query, connection))
{
foreach (var pair in args)
{
cmd.Parameters.AddWithValue(pair.Key, pair.Value);
}
Console.WriteLine("Changes were made in " + cmd.ExecuteNonQuery().ToString() + " places.");
}
connection.Close();
}
}
catch (Exception e)
{ Console.WriteLine(e); }
}
/// <summary>Initialization ctor</summary>
/// <param name="file">Name of database file, will be created if not existing</param>
public WebCache(string file = "Cache.dat")
{
_File = file;
if (!System.IO.File.Exists(_File))
{
SQLiteConnection connection = new SQLiteConnection("UseUTF16Encoding=True;Data Source=" + _File);
try
{
connection.Open();
string[] inits = new string[]{
"CREATE TABLE [InetCache] ([Id] INTEGER PRIMARY KEY, [Timestamp] INTEGER, [Keyword] TEXT NOT NULL, [Content] TEXT NOT NULL)",
"CREATE INDEX [IDX_InetCache] ON [InetCache] ([Keyword])"
};
SQLiteCommand command = connection.CreateCommand();
foreach (string cmd in inits)
{
command.CommandText = cmd;
command.ExecuteNonQuery();
}
}
finally
{
connection.Close();
}
}
}
private void Reporte_Load_1(object sender, EventArgs e)
{
Sistema_Caritas.CrystalReport1 objRpt = new Sistema_Caritas.CrystalReport1();
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
String ConnStr = @"Data Source=" + appPath + @"\DBpinc.s3db ;Version=3;";
System.Data.SQLite.SQLiteConnection myConnection = new System.Data.SQLite.SQLiteConnection(ConnStr);
//String Query1 = "SELECT * FROM Ventashechas Where NVenta = '" + nventas + "'";
String Query1 = "Select NVenta as NVenta, ArticuloID as ArticuloID, Nombrearticulo, Cantidad, Fecha as Fecha, Total as Total, Ventatotal as Ventatotal FROM (SELECT * FROM Ventashechas, Ventas Where Ventashechas.NVenta = Ventas.NVenta) Where NVenta = '" + nventas + "'";
System.Data.SQLite.SQLiteDataAdapter adapter = new System.Data.SQLite.SQLiteDataAdapter(Query1, ConnStr);
DataSet Ds = new DataSet();
// here my_dt is the name of the DataTable which we
// created in the designer view.
adapter.Fill(Ds, "DataTable1");
// Setting data source of our report object
objRpt.SetDataSource(Ds);
// Binding the crystalReportViewer with our report object.
this.crystalReportViewer2.ReportSource = objRpt;
}
public List<AttachmentDetail> getWitAttachments()
{
List<AttachmentDetail> attachments;
try
{
sql_con = new SQLiteConnection(Common.localDatabasePath, true);
sql_cmd = new SQLiteCommand("select * from wit_attachments", sql_con);
sql_con.Open();
SQLiteDataReader reader = sql_cmd.ExecuteReader();
attachments = new List<AttachmentDetail>();
while (reader.Read())
{
AttachmentDetail attachment = new AttachmentDetail();
attachment.fileAssociationId = StringUtils.ConvertFromDBVal<string>(reader["file_association_id"]);
attachment.fileName = StringUtils.ConvertFromDBVal<string>(reader["file_name"]);
attachment.witId = StringUtils.ConvertFromDBVal<string>(reader["wit_id"]);
attachment.fileMimeType = StringUtils.ConvertFromDBVal<string>(reader["file_mime_type"]);
attachment.fileAssociationId = StringUtils.ConvertFromDBVal<string>(reader["file_association_id"]);
attachment.seqNumber = StringUtils.ConvertFromDBVal<string>(reader["seq_number"]);
attachment.extention = StringUtils.ConvertFromDBVal<string>(reader["extention"]);
attachments.Add(attachment);
}
}
catch (SQLiteException e) { throw e; }
finally { sql_con.Close(); }
return attachments;
}
private void button2_Click(object sender, EventArgs e)
{
try
{
folio = Int32.Parse(dataGridView1.SelectedRows[0].Cells[0].Value.ToString());
nombre = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
sexo = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
edad = Int32.Parse(dataGridView1.SelectedRows[0].Cells[3].Value.ToString());
estadocivil = dataGridView1.SelectedRows[0].Cells[5].Value.ToString();
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 = "DELETE FROM Expediente WHERE [Folio] = " + folio + " AND [Nombre] = '" + nombre + "' AND [Sexo] = '" + sexo + "' AND [Edad] = " + edad + " AND [Estadocivil] = '" + estadocivil + "'";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
MessageBox.Show("Expediente eliminado exitosamente");
}
catch
{
MessageBox.Show("No se pueden borrar datos", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture)
{
using (SQLiteConnection conn = new SQLiteConnection("StaticConfig.DataSource"))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
int countCurrent = 0;
string sqlCommandCurrent = String.Format("select count(*) from QuestionInfo where Q_Num={0};", value);//判断当前题号是否存在
try
{
Int32.Parse((string)value);
countCurrent = sh.ExecuteScalar<int>(sqlCommandCurrent);
conn.Close();
if (countCurrent > 0)
{
return "更新";
}
else
{
return "保存";
}
}
catch (Exception)
{
return "保存";
}
}
}
}
private void bt_CreateDataSource_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txt_DataSourceFilePath.Text))
{
MessageBox.Show("请选择路径.");
return;
}
if (string.IsNullOrEmpty(txt_DataSourceFileName.Text))
{
MessageBox.Show("请输入文件名.");
return;
}
instance.SetDataSourcePath(Path.Combine(txt_DataSourceFilePath.Text, txt_DataSourceFileName.Text));
var conn = new System.Data.SQLite.SQLiteConnection(Path.Combine(txt_DataSourceFilePath.Text, txt_DataSourceFileName.Text));
conn.SetPassword("123456");
var parameters = new List <string[]>();
parameters.Add(new[] { "fileID", "varchar", "(50)" });
parameters.Add(new[] { "fileMD5", "varchar", "(32)" });
parameters.Add(new[] { "fileName", "varchar", "(200)" });
parameters.Add(new[] { "filePath", "varchar", "(1000)" });
parameters.Add(new[] { "fileInfo", "blob", "(20480)" });
parameters.Add(new[] { "parentID", "varchar", "(50)" });
instance.CreateTable("FileInfo", parameters.ToArray());
Add(new Model(Guid.NewGuid(), "根目录"));
InitDataSource();
InitUI(false);
}
private bool CreateDatabase()
{
SQLiteConnection tempConnection = null;
try
{
SQLiteConnection.CreateFile(ConfigurationManager.AppSettings["dbPath"]);
tempConnection = new SQLiteConnection("Data Source=" + ConfigurationManager.AppSettings["dbPath"] + ";Version=3;");
tempConnection.Open();
SQLiteCommand command = tempConnection.CreateCommand();
command.CommandText = "CREATE TABLE pelaaja (id INTEGER PRIMARY KEY AUTOINCREMENT, etunimi TEXT NOT NULL, sukunimi TEXT NOT NULL, seura TEXT NOT NULL, hinta FLOAT NOT NULL, kuva_url TEXT NULL)";
command.ExecuteNonQuery();
tempConnection.Close();
}
catch(Exception e)
{
errors.Add("Tietokannan luominen epäonnistui");
return false;
}
finally
{
if(tempConnection != null) tempConnection.Close();
}
return true;
}
public AbstractDatabase(string db_path)
{
conn_ = new SQLiteConnection("Data Source=" + db_path);
tables_ = new Dictionary <string, AbstractTable>();
transaction_ = null;
DBFilePath = db_path;
}
internal static void PrepareCommand(SQLiteCommand command, SQLiteConnection connection, SQLiteTransaction transaction,
CommandType commandType, string commandText, SQLiteParameter[] commandParameters,
out bool mustCloseConnection)
{
if (command == null) throw new ArgumentNullException("command");
if (string.IsNullOrEmpty(commandText)) throw new ArgumentNullException("commandText");
if (connection.State == ConnectionState.Open)
mustCloseConnection = false;
else
{
mustCloseConnection = true;
connection.Open();
}
command.Connection = connection;
command.CommandText = commandText;
if (transaction != null)
{
if (transaction.Connection == null)
throw new ArgumentException(
"The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
command.Transaction = transaction;
}
command.CommandType = commandType;
if (commandParameters != null)
AttachParameters(command, commandParameters);
return;
}
public Database()
{
string _dbPath = "MyDatabase.db3";
// Instanciation de notre connexion
SQLiteConnection sqlite_conn = new SQLiteConnection("Data Source=database.sqlite;Version=3;");
// Utilisation de l'API en mode synchrone
this.conn = sqlite_conn;
sqlite_conn.Open();
SQLiteCommand sqlite_cmd = sqlite_conn.CreateCommand();
// Let the SQLiteCommand object know our SQL-Query:
sqlite_cmd.CommandText = "CREATE TABLE IF NOT EXISTS pass (id INT PRIMARY KEY, login varchar(100), privk varchar(100), pubk varchar(100)) ;";
// Now lets execute the SQL ;-)
sqlite_cmd.ExecuteNonQuery();
/* // Utilisation de l'API asynchrone
* SQLiteAsyncConnection connAsync = new SQLiteAsyncConnection("myDb.db3");
* connection.CreateTableAsync<Personne>();*/
}
public SQLiteCommand CreateAddProductCommand(SQLiteConnection conn, SQLiteTransaction transaction)
{
var cmd = new SQLiteCommand(_sql, conn, transaction);
CreateParameters(cmd);
return cmd;
}
public void Test(SQLiteConnection conn)
{
SQLiteCommand sqlite_cmd = this.conn.CreateCommand();
sqlite_cmd.CommandText = "INSERT INTO pass (login, privk, pubk) VALUES ('Michel', 'TTTEEESSSTTT' , 'Testpub');";
sqlite_cmd.ExecuteNonQuery();
sqlite_cmd.CommandText = "SELECT * FROM pass";
SQLiteDataReader sqlite_datareader = sqlite_cmd.ExecuteReader();
// The SQLiteDataReader allows us to run through each row per loop
while (sqlite_datareader.Read()) // Read() returns true if there is still a result line to read
{
// Print out the content of the text field:
System.Console.WriteLine("----------------------------");
//System.Console.WriteLine("DEBUG Output: '" + sqlite_datareader + "'");
string loginReader = sqlite_datareader.GetString(1);
string keyReader = sqlite_datareader.GetString(2);
System.Console.WriteLine(loginReader);
// OutputTextBox.Text += idReader + " '" + textReader + "' " + "\n";
}
}
private List<Result> QuerySqllite(Doc doc, string key)
{
string dbPath = "Data Source =" + doc.DBPath;
SQLiteConnection conn = new SQLiteConnection(dbPath);
conn.Open();
string sql = GetQuerySqlByDocType(doc.DBType).Replace("{0}", key);
SQLiteCommand cmdQ = new SQLiteCommand(sql, conn);
SQLiteDataReader reader = cmdQ.ExecuteReader();
List<Result> results = new List<Result>();
while (reader.Read())
{
string name = reader.GetString(reader.GetOrdinal("name"));
string docPath = reader.GetString(reader.GetOrdinal("path"));
results.Add(new Result
{
Title = name,
SubTitle = doc.Name.Replace(".docset", ""),
IcoPath = doc.IconPath,
Action = (c) =>
{
string url = string.Format(@"{0}\{1}\Contents\Resources\Documents\{2}#{3}", docsetPath,
doc.Name+".docset", docPath, name);
string browser = GetDefaultBrowserPath();
Process.Start(browser, String.Format("\"file:///{0}\"", url));
return true;
}
});
}
conn.Close();
return results;
}
public void CreateDatabase()
{
string createQuery = @"CREATE TABLE IF NOT EXISTS
[Mytable](
[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[Name] NVARCHAR(50) NULL,
[Location] NVARCHAR(30) NULL)";
System.Data.SQLite.SQLiteConnection.CreateFile("sample.db3");
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=sample.db3"))
{
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open();
cmd.CommandText = createQuery;
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO Mytable(Name,Location) values('Fisch','Irgendwo')";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * from Mytable";
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine(reader["Name"] + ":" + reader["Location"]);
}
}
conn.Close();
Console.ReadLine();
}
}
}
public void Clear(string name)
{
int procCount = Process.GetProcessesByName("firefox").Length;
if (procCount > 0)
throw new ApplicationException(string.Format("There are {0} instances of Firefox still running",
procCount));
try
{
using (var conn = new SQLiteConnection("Data Source=" + GetFirefoxCookiesFileName()))
{
conn.Open();
SQLiteCommand command = conn.CreateCommand();
command.CommandText = "delete from moz_cookies where name='" + name + "'";
int count = command.ExecuteNonQuery();
}
}
catch (SQLiteException ex)
{
if (
!(ex.ErrorCode == Convert.ToInt32(SQLiteErrorCode.Busy) ||
ex.ErrorCode == Convert.ToInt32(SQLiteErrorCode.Locked)))
throw new ApplicationException("The Firefox cookies.sqlite file is locked");
}
}
public void Execute(Action <SqliteConnection, SqliteCommand, SqliteTransaction> ExcuteQuery)
{
Lock = true;
using (var conn = new SqliteConnection()
{
ConnectionString = this.ConnectString
})
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
using (var trans = conn.BeginTransaction())
{
try
{
ExcuteQuery(conn, cmd, trans);
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
}
}
}
conn.Close();
}
Lock = false;
}
public static DataTable ExecuteNonQueryDt(string cmdText, SQLiteConnection con)
{
DataTable dt = new DataTable("Table");
try
{
using (con)
{
SQLiteDataAdapter da = new SQLiteDataAdapter(cmdText, con);
con.Open();
da.Fill(dt);
con.Close();
}
return dt;
}
catch (Exception ex)
{
using (FileStream file = new FileStream(AppDomain.CurrentDomain.BaseDirectory + "\\" + System.DateTime.Now.Date.ToString("dd-MMM-yyyy") + "_Log.txt", FileMode.Append, FileAccess.Write))
{
StreamWriter streamWriter = new StreamWriter(file);
streamWriter.WriteLine(System.DateTime.Now + " - " + "ExecuteNonQueryDt" + " - " + ex.Message.ToString());
streamWriter.Close();
}
return dt;
}
}
public DataSet GetTotalRecordsInTable(System.Data.SQLite.SQLiteConnection m_dbConnection, string query, Logger logger)
{
DataSet myDataSet = new DataSet();
try
{
//string query = $"SELECT count(1) TotalRecords FROM {tableName};";
//records = m_dbConnection.Query<ProcessedDetails1>(query).Count;
//System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand(m_dbConnection);
//command.CommandText = query;
//SQLiteConnection m_dbConnection = new SQLiteConnection($"Data Source={DBName}.sqlite;Version=3;");
//m_dbConnection.Open();
System.Data.SQLite.SQLiteDataAdapter myAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, m_dbConnection);
////myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
myAdapter.Fill(myDataSet, "Records");
//if (myDataSet.Tables[0].Rows.Count > 0)
//{
// records = Convert.ToInt32(myDataSet.Tables[0].Rows[0][0].ToString());
//}
//m_dbConnection.Close();
}
catch (Exception excp)
{
logger.Error("Error while retrieving from sql lite Table : " + excp.ToString() + " --- " + excp.StackTrace);
}
return(myDataSet);
}
public static DataTable GetClients(string sqlitefile)
{
SQLiteConnection con = new SQLiteConnection("data source=" + sqlitefile);
DataTable dt = new DataTable("CompanyInfo");
dt = ExecuteNonQueryDt("SELECT * FROM CompanyInfo", con);
return dt;
}
//public DataSet CheckTableExists(SQLiteConnection m_dbConnection, string tableName, Logger logger)
//{
// DataSet myDataSet = new DataSet();
// try
// {
// string query = $"SELECT name FROM sqlite_master WHERE type='table' AND name='{tableName}';";
// //SQLiteConnection m_dbConnection = new SQLiteConnection($"Data Source={DBName}.sqlite;Version=3;");
// //m_dbConnection.Open();
// SQLiteDataAdapter myAdapter = new SQLiteDataAdapter(query, m_dbConnection);
// //myAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
// myAdapter.Fill(myDataSet, "Records");
// //m_dbConnection.Close();
// }
// catch (Exception excp)
// {
// logger.Error("Error while retrieving from sql lite Table : " + excp.ToString() + " --- " + excp.StackTrace);
// }
// return myDataSet;
//}
public bool InsertRecord(System.Data.SQLite.SQLiteConnection m_dbConnection, string query, Logger logger)
{
bool isRecordInserted = false;
try
{
//SQLiteConnection m_dbConnection = new SQLiteConnection($"Data Source={DBName}.sqlite;Version=3;");
//m_dbConnection.Open();
//string isErrorString = IsError ? "1" : "0";
//string query = $"INSERT INTO {tableName}(FlatFileRowNumber, IsError) VALUES({flatFileRecordNumber},{isErrorString})";
System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand(m_dbConnection);
command.CommandText = query;
command.ExecuteNonQuery();
//m_dbConnection.Close();
isRecordInserted = true;
}
catch (Exception excp)
{
logger.Error("Error while inserting sql lite Table : " + excp.ToString() + " --- " + excp.StackTrace);
}
return(isRecordInserted);
}
/// <summary>
/// Takes a GIS model and a file and writes the model to that file.
/// </summary>
/// <param name="model">
/// The GisModel which is to be persisted.
/// </param>
/// <param name="fileName">
/// The name of the file in which the model is to be persisted.
/// </param>
public void Persist(GisModel model, string fileName)
{
Initialize(model);
PatternedPredicate[] predicates = GetPredicates();
if ( File.Exists(fileName))
{
File.Delete(fileName);
}
using (mDataConnection = new SQLiteConnection("Data Source=" + fileName + ";New=True;Compress=False;Synchronous=Off;UTF8Encoding=True;Version=3"))
{
mDataConnection.Open();
mDataCommand = mDataConnection.CreateCommand();
CreateDataStructures();
using (mDataTransaction = mDataConnection.BeginTransaction())
{
mDataCommand.Transaction = mDataTransaction;
CreateModel(model.CorrectionConstant, model.CorrectionParameter);
InsertOutcomes(model.GetOutcomeNames());
InsertPredicates(predicates);
InsertPredicateParameters(model.GetOutcomePatterns(), predicates);
mDataTransaction.Commit();
}
mDataConnection.Close();
}
}
public System.Data.SQLite.SQLiteConnection OpenDBConnection1(string DBName)
{
System.Data.SQLite.SQLiteConnection m_dbConnection = new System.Data.SQLite.SQLiteConnection($"Data Source={DBName}.sqlite;Version=3;");
m_dbConnection.Open();
return(m_dbConnection);
}
public static void ChapterFinished(string mangaTitle)
{
try
{
using (SQLiteConnection sqLiteConnection = new SQLiteConnection(ConnectionString))
{
sqLiteConnection.Open();
SQLiteCommand sqLiteCommand = new SQLiteCommand(sqLiteConnection)
{
CommandText = "UPDATE READING_LIST " +
"SET READ_CURRENT_CHAPTER = READ_CURRENT_CHAPTER + 1, READ_LAST_TIME = ? " +
"WHERE MANGA_ID = ?"
};
sqLiteCommand.Parameters.AddWithValue(null, DateTime.Now);
sqLiteCommand.Parameters.AddWithValue(null, GetMangaId(mangaTitle));
sqLiteCommand.ExecuteNonQuery();
sqLiteConnection.Close();
}
}
catch (Exception ex)
{
ErrorMessageBox.Show(ex.Message, ex.ToString());
Logger.ErrorLogger("error.txt", ex.ToString());
}
}
/// <summary>
/// Connect to the SQLite database.
/// </summary>
/// <param name="close">If true : Close database connection. If false : Open database connection.</param>
/// <returns>SQLiteCommand cmd if database is getting opened.</returns>
/// <returns>Null if database is getting closed.</returns>
internal static SQLiteCommand ConnectToDatabase(bool close)
{
bool createTable = false;
if (!File.Exists("GLdb.db3"))
{
CreateDatabase();
createTable = true;
}
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=GLdb.db3");
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn);
if (!close)
{
conn.Open();
if (createTable)
{
CreateTable(cmd);
InsertPlatform(cmd);
}
return(cmd);
}
else
{
cmd = null;
conn.Close();
return(cmd);
}
}
private static SQLiteConnection GetConnection(string filePath)
{
SQLiteConnection connection = new SQLiteConnection(
string.Format("Data Source={0};Version=3;", filePath));
return connection;
}
private void ReporteSalidasBitacoraComedor_Load(object sender, EventArgs e)
{
CrystalReport6 objRpt = new CrystalReport6();
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
String ConnStr = @"Data Source=" + appPath + @"\DBBIT.s3db ;Version=3;";
System.Data.SQLite.SQLiteConnection myConnection = new System.Data.SQLite.SQLiteConnection(ConnStr);
String Query1 = "SELECT * FROM Salidas";
System.Data.SQLite.SQLiteDataAdapter adapter = new System.Data.SQLite.SQLiteDataAdapter(Query1, ConnStr);
DataSet Ds = new DataSet();
// here my_dt is the name of the DataTable which we
// created in the designer view.
adapter.Fill(Ds, "DataTable5");
// Setting data source of our report object
objRpt.SetDataSource(Ds);
// Binding the crystalReportViewer with our report object.
this.crystalReportViewer1.ReportSource = objRpt;
objRpt.Refresh();
}
public Block_Edit(int id_)
{
InitializeComponent();
id = id_;
using (SQLiteConnection conn = new SQLiteConnection(connString))
{
conn.Open();
SQLiteCommand command = new SQLiteCommand(conn);
command.CommandText = "SELECT * FROM Block WHERE id=@id";
command.Parameters.Add(new SQLiteParameter("@id", id));
using (command)
{
using (SQLiteDataReader rdr = command.ExecuteReader())
{
while (rdr.Read())
{
labelName.Text = rdr.GetValue(1).ToString();
txtImieNazw.Text = rdr.GetValue(1).ToString();
}
}
}
conn.Close();
}
}
static void Main(string[] args)
{
using(var conn = new SQLiteConnection(@"Data Source=dnd35.db"))
{
//conn.Open();
//string query = @"SELECT * FROM skill;";
//var command = conn.CreateCommand();
//command.CommandText = query;
//command.Connection = conn;
//var reader = command.ExecuteReader();
//var domains = new List<Skill>();
//while (reader.Read())
//{
// var values = reader.GetValues();
// domains.Add(Map(values));
//}
//var domainRepository = new DomainRepository();
//foreach(var domain in domains)
//{
// domainRepository.Create(domain);
//}
}
}
/// <summary>Write data with a given key to cache</summary>
/// <param name="key">Key of data to be stored</param>
/// <param name="data">Data to be stored</param>
public void Store(string key, string data)
{
SQLiteConnection connection = new SQLiteConnection("UseUTF16Encoding=True;Data Source=" + _File);
try
{
DateTime now = DateTime.Now;
connection.Open();
SQLiteCommand command = connection.CreateCommand();
command.CommandText = "DELETE FROM [InetCache] WHERE [Keyword]=?";
command.Parameters.Add("kw", DbType.String).Value = key;
command.ExecuteNonQuery(); // out with the old
command = connection.CreateCommand();
command.CommandText = "INSERT INTO [InetCache] ([Timestamp], [Keyword], [Content]) VALUES (?, ?, ?)";
command.Parameters.Add("ts", DbType.Int32).Value = now.Year * 10000 + now.Month * 100 + now.Day;
command.Parameters.Add("kw", DbType.String).Value = key;
command.Parameters.Add("dt", DbType.String).Value = data;
command.ExecuteNonQuery(); // in with the new
}
finally
{
connection.Close();
}
}
/// <summary>
/// Сохраняет изменения в таблице
/// </summary>
/// <param name="question">вопрос</param>
public void SaveOrUpdate(SQLiteConnection conn, QuestionResult question)
{
if (question.ID == 0)
{
int ID = indexService.NextID(TABLE_NAME);
using (SQLiteCommand insertSQL = new SQLiteCommand("insert into QUESTION_RESULT (ID, QuestionTitle, TEST_RESULT_ID) values (@ID, @QuestionTitle, @TEST_RESULT_ID)", conn))
{
insertSQL.Parameters.AddWithValue("@QuestionTitle", question.QuestionTitle);
insertSQL.Parameters.AddWithValue("@TEST_RESULT_ID", question.testResult.ID);
insertSQL.Parameters.AddWithValue("@ID", ID);
insertSQL.ExecuteNonQuery();
question.ID = ID;
}
}
else
{
using (SQLiteCommand insertSQL = new SQLiteCommand("UPDATE QUESTION_RESULT SET QuestionTitle = @QuestionTitle, TEST_RESULT_ID = @TEST_RESULT_ID WHERE ID = @ID", conn))
{
insertSQL.Parameters.AddWithValue("@QuestionTitle", question.QuestionTitle);
insertSQL.Parameters.AddWithValue("@TEST_RESULT_ID", question.testResult.ID);
insertSQL.Parameters.AddWithValue("@ID", question.ID);
insertSQL.ExecuteNonQuery();
}
}
}
// backgroundWorkerView
//
private async Task SetupAccount()
{
//BinanceDefaults.SetDefaultLogOutput(Console.Out);
using (var client = new BinanceClient())
{
var accountInfo = await client.GetAccountInfoAsync(10000000);
IEnumerable <BinanceBalance> candlearray = accountInfo.Data.Balances.Select(e => e).Where(e => (e.Total + e.Locked) > 0);
}
if (!System.IO.File.Exists(AppDomain.CurrentDomain.BaseDirectory + "\\Database.sqlite"))
{
SQLiteConnection.CreateFile("Database.sqlite");
}
_conn = new SQLiteConnection("Data Source=Database.sqlite;Version=3;");
_conn.Open();
try
{
string sql = "CREATE TABLE IF NOT EXISTS TICKERS (Symbol VARCHAR(20), active INT)";
SQLiteCommand command = new SQLiteCommand(sql, _conn);
await command.ExecuteNonQueryAsync();
sql = "CREATE TABLE IF NOT EXISTS ORDERS (Symbol VARCHAR(20),Scanned DATE, Price decimal, Volume decimal, TakerBuyBaseAssetVolume decimal, TakerBuyQuoteAssetVolume decimal, NumberOfTrades INT, Hr8Av INT, NowPercent decimal, Min15Percent decimal, GreenCandles decimal)";
command = new SQLiteCommand(sql, _conn);
await command.ExecuteNonQueryAsync();
sql = "CREATE VIEW IF NOT EXISTS [V_Symbols] AS SELECT COUNT(Symbol), Symbol FROM ORDERS GROUP BY Symbol ORDER By COUNT(Symbol) DESC;";
await command.ExecuteNonQueryAsync();
}
catch (Exception ex)
{
//_conn.Close();
}
}
private DoorInfo Retrieve(string DoorID)
{
var ret = new DoorInfo();
var cs = ConfigurationManager.ConnectionStrings["DoorSource"].ConnectionString;
var conn = new SQLiteConnection(cs);
conn.Open();
var cmd = new SQLiteCommand(conn);
cmd.CommandText = "SELECT DoorID, Location, Description, EventID FROM Doors WHERE DoorID = @DoorID LIMIT 1";
cmd.Parameters.Add(new SQLiteParameter("@DoorID", DoorID));
SQLiteDataReader res = null;
try
{
res = cmd.ExecuteReader();
if (res.HasRows && res.Read())
{
ret.DoorID = DoorID;
ret.Location = res.GetString(1);
ret.Description = res.GetString(2);
ret.EventID = res.GetInt64(3);
}
return ret;
}
catch(Exception ex)
{
throw;
}
finally
{
if (null != res && !res.IsClosed)
res.Close();
}
}
public DataManager(string dbFilePath)
{
if (string.IsNullOrEmpty(dbFilePath) == true)
{
throw new ArgumentNullException("dbFilePath");
}
_fileExists = File.Exists(CommonConst.DBFileName);
_dbFilePath = dbFilePath;
// DBとの接続を開始する。
_conn = new SQLiteConnection("Data Source=" + CommonConst.DBFileName);
// DBに接続する。
_conn.Open();
_command = _conn.CreateCommand();
// DBファイルが新規作成された場合
if (_fileExists == false)
{
// 空のテーブルを作成する。
this.CreateNewTables();
_fileExists = true;
}
// アプリケーション設定
_applicationSettings.amountSplitCharacter = this.GetAmountsSplitCharacter();
_applicationSettings.commentSplitCharacter = this.GetCommentSplitCharacter();
}
public static void loaddb()
{
string _connstr = getdb();
string ins_ = "select rowid,from_,to_,subject_,body_,dot_ from mail where rowid>" + maxrowid + ";";
using (SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(_connstr))
{
conn.Open();
try
{
using (SQLiteDataReader dr = new SQLiteCommand(ins_, conn).ExecuteReader())
{
while (dr.Read())
{
Message m = new Message();
maxrowid = dr.GetInt32(0);
m.from = dr.GetString(1);
m.to = dr.GetString(2);
m.subject = dr.GetString(3);
m.body = dr.GetString(4);
m.dot = dr.GetBoolean(5);
_mails.Add(m);
}
}
}
catch (Exception ep)
{
Console.WriteLine(ep.Message);
}
conn.Close();
}
}
