/// <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 void NewConversation(string s, string r, string m)
{
SQLiteConnection dbConnection = new SQLiteConnection("Data Source=db.sqlite;Version=3;");
dbConnection.Open();
string sql = "insert into conversations default values";
SQLiteCommand command = new SQLiteCommand(sql, dbConnection);
command.ExecuteNonQuery();
string sqlId = "select last_insert_rowid() as id";
command.CommandText = sqlId;
command.ExecuteNonQuery();
SQLiteDataReader reader = command.ExecuteReader();
reader.Read();
string id = reader["id"].ToString();
reader.Close();
string sql2 = "insert into messages (sender, recipient, message, conversationid) values (@s, @r, @m, @id)";
command.Parameters.Add(new SQLiteParameter("@s", s));
command.Parameters.Add(new SQLiteParameter("@r", r));
command.Parameters.Add(new SQLiteParameter("@m", m));
command.Parameters.Add(new SQLiteParameter("@id", id));
command.CommandText = sql2;
command.ExecuteNonQuery();
string sql3 = "insert into participants (participant, conversationid) values (@s, @id), (@r, @id)";
command.Parameters.Add(new SQLiteParameter("@s", s));
command.Parameters.Add(new SQLiteParameter("@id", id));
command.CommandText = sql3;
command.ExecuteNonQuery();
dbConnection.Close();
}
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 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 Database()
{
if (!File.Exists("Settings/" + DatabaseName)) {
// -- We need to create the PlayerDB.
SQLiteConnection.CreateFile(Path.GetFullPath("Settings/" + DatabaseName));
// -- Now we need to connect and create the table.
lock (_dbLock) {
var connection = new SQLiteConnection("Data Source=" + Path.GetFullPath("Settings/" + DatabaseName));
connection.Open();
var command = new SQLiteCommand("CREATE TABLE PlayerDB (Number INTEGER PRIMARY KEY, Name TEXT UNIQUE, Rank TEXT, RankStep TEXT, BoundBlock INTEGER, RankChangedBy TEXT, LoginCounter INTEGER, KickCounter INTEGER, Ontime INTEGER, LastOnline INTEGER, IP TEXT, Stopped INTEGER, StoppedBy TEXT, Banned INTEGER, Vanished INTEGER, BannedBy STRING, BannedUntil INTEGER, Global INTEGER, Time_Muted INTEGER, BanMessage TEXT, KickMessage TEXT, MuteMessage TEXT, RankMessage TEXT, StopMessage TEXT)", connection);
command.ExecuteNonQuery();
command.CommandText = "CREATE INDEX PlayerDB_Index ON PlayerDB (Name COLLATE NOCASE)";
command.ExecuteNonQuery();
command.CommandText = "CREATE TABLE IPBanDB (Number INTEGER PRIMARY KEY, IP TEXT UNIQUE, Reason TEXT, BannedBy TEXT)";
command.ExecuteNonQuery();
DBConnection = connection; // -- All done.
}
} else {
DBConnection = new SQLiteConnection("Data Source=" + Path.GetFullPath("Settings/" + DatabaseName));
DBConnection.Open();
}
}
public void ReCreateTable()
{
SQLiteCommand command = new SQLiteCommand("DROP TABLE info;", SQLiteConnector.getInstance());
command.ExecuteNonQuery();
command.CommandText = "CREATE TABLE info ([id] INTEGER PRIMARY KEY AUTOINCREMENT,[mac] VARCHAR(200),[date] DATETIME,[os] INTEGER);";
command.ExecuteNonQuery();
}
public void AddMovie(MovieFinder.Data.Movie movie)
{
using (var cmd = new SQLiteCommand(connection))
{
cmd.CommandText = String.Format("INSERT INTO MOVIE(ID,Name,ImageUrl,ReleaseDate,LanguageCode,Description, CreatedDate,ModifiedDate, Version, UniqueID, HasSubtitle) " +
"VALUES({0},'{1}','{2}','{3}','{4}','{5}','{6}','{7}',{8},'{9}',{10})",
movie.ID, Sanitize(movie.Name), movie.ImageUrl, movie.ReleaseDate.ToString("yyyy-MM-dd"),
movie.LanguageCode,Sanitize( movie.Description),
movie.CreateDate.ToString("yyyy-MM-dd"),
movie.ModifiedDate != null ?
movie.ModifiedDate.Value.ToString("yyyy-MM-dd") : null, movie.Version,
movie.UniqueID, movie.MovieLinks.Any(x => x.HasSubtitle) ? 1 : 0);
cmd.ExecuteNonQuery();
foreach (var link in movie.MovieLinks)
{
if (link.FailedAttempts > 3)
continue;
cmd.CommandText = String.Format("INSERT INTO MOVIELINK(ID,MovieID,LinkTitle,PageUrl,PageSiteID,DownloadUrl,DownloadSiteID,Version, HasSubtitle) " +
"VALUES({0},{1},'{2}','{3}','{4}','{5}','{6}',{7},{8})",
link.ID, link.MovieID, Sanitize(link.LinkTitle), link.PageUrl, link.PageSiteID, link.DowloadUrl, link.DownloadSiteID,
link.Version, link.HasSubtitle ? 1 : 0);
cmd.ExecuteNonQuery();
}
}
}
public void ReCreateTable()
{
SQLiteCommand command = new SQLiteCommand("DROP TABLE os;", SQLiteConnector.getInstance());
command.ExecuteNonQuery();
command.CommandText = "Create TABLE os([id] INTEGER PRIMARY KEY AUTOINCREMENT,[name] VARCHAR(200),[color] VARCHAR(6));";
command.ExecuteNonQuery();
}
public static int SqlNonQueryText(SQLiteConnection cn, SQLiteCommand cmd)
{
int rows = 0;
//LogLine("SqlNonQueryText: " + cmd.CommandText);
try
{
if (cn.State == ConnectionState.Open)
{
rows = cmd.ExecuteNonQuery();
}
else
{
cn.Open();
rows = cmd.ExecuteNonQuery();
cn.Close();
}
return rows;
}
catch (Exception ex)
{
System.Diagnostics.Debug.WriteLine("SqlNonQueryText Exception: " + ex.Message);
throw;
}
}
private void button1_Click(object sender, EventArgs e)
{
if (dataGridView1.Rows.Count != 0)
{
DialogResult resultado = MessageBox.Show("Esta seguro que desea eliminar toda la venta?", "Seguro?", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation);
if (resultado == DialogResult.Yes)
{
string appPath = Path.GetDirectoryName(Application.ExecutablePath);
System.Data.SQLite.SQLiteConnection sqlConnection1 =
new System.Data.SQLite.SQLiteConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBpinc.s3db");
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "Delete From Ventas Where [NVenta] = " + dataGridView1.SelectedRows[0].Cells[0].Value.ToString() + "";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
cmd.CommandText = "Delete From Ventashechas Where [NVenta] = " + dataGridView1.SelectedRows[0].Cells[0].Value.ToString() + "";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
appPath = Path.GetDirectoryName(Application.ExecutablePath);
//create the connection string
string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + appPath + @"\DBpinc.s3db";
dataGridView1.Left = 247;
//create the database query
string query = "Select * From Ventas";
//create an OleDbDataAdapter to execute the query
System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString);
//create a command builder
System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter);
//create a DataTable to hold the query results
DataTable dTable = new DataTable();
//fill the DataTable
dAdapter.Fill(dTable);
BindingSource bSource = new BindingSource();
bSource.DataSource = dTable;
dataGridView1.DataSource = bSource;
dAdapter.Update(dTable);
}
}
else
{
MessageBox.Show("Tiene que elegir una venta para eliminarlo");
}
}
private void vCreateDB()
{
if (Directory.Exists("ServerData")) Directory.Delete("ServerData", true);
Directory.CreateDirectory("ServerData");
File.WriteAllText("ServerData\\test.xml", "<Test><localtest/></Test>");
SQLiteConnection.CreateFile("ServerData\\PersonaData.db");
SQLiteConnection.CreateFile("ServerData\\GarageData.db");
SQLiteConnection m_dbConnection;
m_dbConnection = new SQLiteConnection("Data Source=\"ServerData\\PersonaData.db\";Version=3;");
m_dbConnection.Open();
string sql = "create table personas (Id bigint, IconIndex smallint, Name varchar(14), Motto varchar(30), Level smallint, IGC int, Boost int, ReputationPercentage smallint, LevelReputation int, TotalReputation int)";
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
sql = "insert into personas (Id, IconIndex, Name, Motto, Level, IGC, Boost, ReputationPercentage, LevelReputation, TotalReputation) values (0, 27, 'Debug', 'Test Build', 69, 0, 0, 0, 0, 699)";
command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
sql = "insert into personas (Id, IconIndex, Name, Motto, Level, IGC, Boost, ReputationPercentage, LevelReputation, TotalReputation) values (1, 26, 'DefaultProfile', 'Literally, the first.', 1, 25000, 1500, 0, 0, 0)";
command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
m_dbConnection.Close();
m_dbConnection = new SQLiteConnection("Data Source=\"ServerData\\GarageData.db\";Version=3;");
m_dbConnection.Open();
sql = "create table Id0 (BaseCarId bigint, RaceClass int, ApId bigint, Paints longtext, PerformanceParts longtext, PhysicsProfileHash bigint, Rating int, ResalePrice int, SkillModParts longtext, Vinyls longtext, VisualParts longtext, Durability smallint, ExpirationDate text, HeatLevel smallint, Id int)";
command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
sql = "insert into Id0 (BaseCarId, RaceClass, ApId, Paints, PerformanceParts, PhysicsProfileHash, Rating, ResalePrice, SkillModParts, Vinyls, VisualParts, Durability, ExpirationDate, HeatLevel, Id) values (1816139026, -405837480, 1, "+
"'<Paints><CustomPaintTrans><Group>-1480403439</Group><Hue>496032624</Hue><Sat>0</Sat><Slot>1</Slot><Var>76</Var></CustomPaintTrans><CustomPaintTrans><Group>-1480403439</Group><Hue>496032624</Hue><Sat>0</Sat><Slot>2</Slot><Var>76</Var></CustomPaintTrans><CustomPaintTrans><Group>595033610</Group><Hue>496032624</Hue><Sat>0</Sat><Slot>6</Slot><Var>254</Var></CustomPaintTrans><CustomPaintTrans><Group>595033610</Group><Hue>496032624</Hue><Sat>0</Sat><Slot>0</Slot><Var>254</Var></CustomPaintTrans><CustomPaintTrans><Group>595033610</Group><Hue>496032624</Hue><Sat>0</Sat><Slot>3</Slot><Var>254</Var></CustomPaintTrans><CustomPaintTrans><Group>595033610</Group><Hue>496032624</Hue><Sat>0</Sat><Slot>4</Slot><Var>254</Var></CustomPaintTrans><CustomPaintTrans><Group>595033610</Group><Hue>496032624</Hue><Sat>0</Sat><Slot>5</Slot><Var>254</Var></CustomPaintTrans></Paints>', "+
"'<PerformanceParts><PerformancePartTrans><PerformancePartAttribHash>-1962598619</PerformancePartAttribHash></PerformancePartTrans><PerformancePartTrans><PerformancePartAttribHash>-183076819</PerformancePartAttribHash></PerformancePartTrans><PerformancePartTrans><PerformancePartAttribHash>7155944</PerformancePartAttribHash></PerformancePartTrans><PerformancePartTrans><PerformancePartAttribHash>754340312</PerformancePartAttribHash></PerformancePartTrans><PerformancePartTrans><PerformancePartAttribHash>1621862030</PerformancePartAttribHash></PerformancePartTrans><PerformancePartTrans><PerformancePartAttribHash>1727386028</PerformancePartAttribHash></PerformancePartTrans></PerformanceParts>', "+
"-846723009, 708, 350000, "+
"'<SkillModParts><SkillModPartTrans><IsFixed>false</IsFixed><SkillModPartAttribHash>-1196331958</SkillModPartAttribHash></SkillModPartTrans><SkillModPartTrans><IsFixed>false</IsFixed><SkillModPartAttribHash>-1012293684</SkillModPartAttribHash></SkillModPartTrans><SkillModPartTrans><IsFixed>false</IsFixed><SkillModPartAttribHash>-577002039</SkillModPartAttribHash></SkillModPartTrans><SkillModPartTrans><IsFixed>false</IsFixed><SkillModPartAttribHash>861531645</SkillModPartAttribHash></SkillModPartTrans><SkillModPartTrans><IsFixed>false</IsFixed><SkillModPartAttribHash>917249206</SkillModPartAttribHash></SkillModPartTrans></SkillModParts>', "+
"'<Vinyls><CustomVinylTrans><Hash>-883491363</Hash><Hue1>-799662319</Hue1><Hue2>-799662186</Hue2><Hue3>-799662452</Hue3><Hue4>-799662452</Hue4><Layer>0</Layer><Mir>true</Mir><Rot>128</Rot><Sat1>0</Sat1><Sat2>0</Sat2><Sat3>0</Sat3><Sat4>0</Sat4><ScaleX>7162</ScaleX><ScaleY>11595</ScaleY><Shear>0</Shear><TranX>2</TranX><TranY>327</TranY><Var1>204</Var1><Var2>0</Var2><Var3>0</Var3><Var4>0</Var4></CustomVinylTrans><CustomVinylTrans><Hash>-1282944374</Hash><Hue1>-799662156</Hue1><Hue2>-799662354</Hue2><Hue3>-799662385</Hue3><Hue4>-799662385</Hue4><Layer>1</Layer><Mir>true</Mir><Rot>60</Rot><Sat1>0</Sat1><Sat2>0</Sat2><Sat3>0</Sat3><Sat4>0</Sat4><ScaleX>735</ScaleX><ScaleY>1063</ScaleY><Shear>0</Shear><TranX>-52</TranX><TranY>268</TranY><Var1>255</Var1><Var2>0</Var2><Var3>0</Var3><Var4>0</Var4></CustomVinylTrans></Vinyls>', "+
"'<VisualParts><VisualPartTrans><PartHash>-541305606</PartHash><SlotHash>1694991</SlotHash></VisualPartTrans><VisualPartTrans><PartHash>-273819714</PartHash><SlotHash>-2126743923</SlotHash></VisualPartTrans><VisualPartTrans><PartHash>-48607787</PartHash><SlotHash>453545749</SlotHash></VisualPartTrans><VisualPartTrans><PartHash>948331475</PartHash><SlotHash>2106784967</SlotHash></VisualPartTrans></VisualParts>', "+
"100, '2016-01-30T17:30:00.0000000+00:00', 1, 1)";
command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
command.ExecuteNonQuery();
command.ExecuteNonQuery(); // 3 cars
sql = "create table Id1 (BaseCarId bigint, RaceClass int, ApId bigint, Paints longtext, PerformanceParts longtext, PhysicsProfileHash bigint, Rating int, ResalePrice int, SkillModParts longtext, Vinyls longtext, VisualParts longtext, Durability smallint, ExpirationDate text, HeatLevel smallint, Id int)";
command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
sql = "insert into Id1 (BaseCarId, RaceClass, ApId, Paints, PerformanceParts, PhysicsProfileHash, Rating, ResalePrice, SkillModParts, Vinyls, VisualParts, Durability, ExpirationDate, HeatLevel, Id) values (1816139026, -405837480, 2, " +
"'<Paints><CustomPaintTrans><Group>-1480403439</Group><Hue>496032624</Hue><Sat>0</Sat><Slot>1</Slot><Var>76</Var></CustomPaintTrans><CustomPaintTrans><Group>-1480403439</Group><Hue>496032624</Hue><Sat>0</Sat><Slot>2</Slot><Var>76</Var></CustomPaintTrans><CustomPaintTrans><Group>595033610</Group><Hue>496032624</Hue><Sat>0</Sat><Slot>6</Slot><Var>254</Var></CustomPaintTrans><CustomPaintTrans><Group>595033610</Group><Hue>496032624</Hue><Sat>0</Sat><Slot>0</Slot><Var>254</Var></CustomPaintTrans><CustomPaintTrans><Group>595033610</Group><Hue>496032624</Hue><Sat>0</Sat><Slot>3</Slot><Var>254</Var></CustomPaintTrans><CustomPaintTrans><Group>595033610</Group><Hue>496032624</Hue><Sat>0</Sat><Slot>4</Slot><Var>254</Var></CustomPaintTrans><CustomPaintTrans><Group>595033610</Group><Hue>496032624</Hue><Sat>0</Sat><Slot>5</Slot><Var>254</Var></CustomPaintTrans></Paints>', " +
"'<PerformanceParts><PerformancePartTrans><PerformancePartAttribHash>-1962598619</PerformancePartAttribHash></PerformancePartTrans><PerformancePartTrans><PerformancePartAttribHash>-183076819</PerformancePartAttribHash></PerformancePartTrans><PerformancePartTrans><PerformancePartAttribHash>7155944</PerformancePartAttribHash></PerformancePartTrans><PerformancePartTrans><PerformancePartAttribHash>754340312</PerformancePartAttribHash></PerformancePartTrans><PerformancePartTrans><PerformancePartAttribHash>1621862030</PerformancePartAttribHash></PerformancePartTrans><PerformancePartTrans><PerformancePartAttribHash>1727386028</PerformancePartAttribHash></PerformancePartTrans></PerformanceParts>', " +
"-846723009, 708, 350000, " +
"'<SkillModParts><SkillModPartTrans><IsFixed>false</IsFixed><SkillModPartAttribHash>-1196331958</SkillModPartAttribHash></SkillModPartTrans><SkillModPartTrans><IsFixed>false</IsFixed><SkillModPartAttribHash>-1012293684</SkillModPartAttribHash></SkillModPartTrans><SkillModPartTrans><IsFixed>false</IsFixed><SkillModPartAttribHash>-577002039</SkillModPartAttribHash></SkillModPartTrans><SkillModPartTrans><IsFixed>false</IsFixed><SkillModPartAttribHash>861531645</SkillModPartAttribHash></SkillModPartTrans><SkillModPartTrans><IsFixed>false</IsFixed><SkillModPartAttribHash>917249206</SkillModPartAttribHash></SkillModPartTrans></SkillModParts>', " +
"'<Vinyls><CustomVinylTrans><Hash>-883491363</Hash><Hue1>-799662319</Hue1><Hue2>-799662186</Hue2><Hue3>-799662452</Hue3><Hue4>-799662452</Hue4><Layer>0</Layer><Mir>true</Mir><Rot>128</Rot><Sat1>0</Sat1><Sat2>0</Sat2><Sat3>0</Sat3><Sat4>0</Sat4><ScaleX>7162</ScaleX><ScaleY>11595</ScaleY><Shear>0</Shear><TranX>2</TranX><TranY>327</TranY><Var1>204</Var1><Var2>0</Var2><Var3>0</Var3><Var4>0</Var4></CustomVinylTrans><CustomVinylTrans><Hash>-1282944374</Hash><Hue1>-799662156</Hue1><Hue2>-799662354</Hue2><Hue3>-799662385</Hue3><Hue4>-799662385</Hue4><Layer>1</Layer><Mir>true</Mir><Rot>60</Rot><Sat1>0</Sat1><Sat2>0</Sat2><Sat3>0</Sat3><Sat4>0</Sat4><ScaleX>735</ScaleX><ScaleY>1063</ScaleY><Shear>0</Shear><TranX>-52</TranX><TranY>268</TranY><Var1>255</Var1><Var2>0</Var2><Var3>0</Var3><Var4>0</Var4></CustomVinylTrans></Vinyls>', " +
"'<VisualParts><VisualPartTrans><PartHash>-541305606</PartHash><SlotHash>1694991</SlotHash></VisualPartTrans><VisualPartTrans><PartHash>-273819714</PartHash><SlotHash>-2126743923</SlotHash></VisualPartTrans><VisualPartTrans><PartHash>-48607787</PartHash><SlotHash>453545749</SlotHash></VisualPartTrans><VisualPartTrans><PartHash>948331475</PartHash><SlotHash>2106784967</SlotHash></VisualPartTrans></VisualParts>', " +
"100, 'null', 1, 2)";
command = new SQLiteCommand(sql, m_dbConnection);
command.ExecuteNonQuery();
m_dbConnection.Close();
m_dbConnection.Dispose();
}
public ResearchLogFile()
{
OleDbDataReader oledbReader;
oledbReader = base.GetOleDbDataReader("*_l.dbf");
using (var conn = new SQLiteConnection(ConfigurationManager.ConnectionStrings["TMG.DataExtractor.Properties.Settings.tmgConnectionString"].ToString()))
{
conn.Open();
using (var cmd = new SQLiteCommand(conn))
{
using (var transaction = conn.BeginTransaction())
{
cmd.CommandText = "DELETE FROM ResearchLog;";
cmd.ExecuteNonQuery();
foreach (DbDataRecord row in oledbReader)
{
string sql = "INSERT INTO ResearchLog (RLTYPE,RLNUM,RLPER1,RLPER2,RLGTYPE,TASK,RLEDITED,DESIGNED,BEGUN,PROGRESS,COMPLETED,PLANNED,";
sql += "EXPENSES,COMMENTS,RLNOTE,KEYWORDS,DSID,ID_PERSON,ID_EVENT,ID_SOURCE,ID_REPOS,TT,REFERENCE) ";
sql += string.Format("VALUES ('{0}',{1},{2},{3},{4},'{5}','{6}','{7}','{8}','{9}','{10}','{11}',{12},'{13}','{14}','{15}',{16},{17},{18},{19},{20},'{21}','{22}');",
row["RLTYPE"].ToString().Replace("'","`"),
(int)row["RLNUM"],
(int)row["RLPER1"],
(int)row["RLPER2"],
(int)row["RLGTYPE"],
row["TASK"].ToString().Replace("'","`"),
row["RLEDITED"].ToString().Replace("'","`"),
row["DESIGNED"].ToString().Replace("'","`"),
row["BEGUN"].ToString().Replace("'","`"),
row["PROGRESS"].ToString().Replace("'","`"),
row["COMPLETED"].ToString().Replace("'","`"),
row["PLANNED"].ToString().Replace("'","`"),
(decimal)row["EXPENSES"],
row["COMMENTS"].ToString().Replace("'","`"),
row["RLNOTE"].ToString().Replace("'","`"),
row["KEYWORDS"].ToString().Replace("'","`"),
(int)row["DSID"],
(int)row["ID_PERSON"],
(int)row["ID_EVENT"],
(int)row["ID_SOURCE"],
(int)row["ID_REPOS"],
row["TT"].ToString(),
row["REFERENCE"].ToString().Replace("'","`")
);
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
Tracer("Research Logs: {0} {1}%");
}
transaction.Commit();
}
}
conn.Close();
}
}
public static void Create()
{
string appDataFolder = Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData);
string dataFolder = Path.Combine(appDataFolder, "Pedal Builder");
try
{
if (!Directory.Exists(dataFolder))
{
Directory.CreateDirectory(dataFolder);
}
if (!File.Exists(Path.Combine(dataFolder, "Pedals.sqlite")))
{
SQLiteConnection.CreateFile(Path.Combine(dataFolder, "Pedals.sqlite"));
}
SQLiteConnection con = new SQLiteConnection(@"Data Source=" + dataFolder + "/Pedals.sqlite;Version=3");
con.Open();
string pedalSql = "CREATE TABLE IF NOT EXISTS pedals (" +
"id INTEGER PRIMARY KEY AUTOINCREMENT," +
"name TEXT NOT NULL," +
"builds INTEGER," +
"notes TEXT)";
string componentSql = "CREATE TABLE IF NOT EXISTS components (" +
"id INTEGER PRIMARY KEY AUTOINCREMENT," +
"type TEXT," +
"value TEXT," +
"notes TEXT," +
"url TEXT," +
"price REAL)";
string partListSql = "CREATE TABLE IF NOT EXISTS partlist (" +
"id INTEGER PRIMARY KEY AUTOINCREMENT," +
"partname TEXT NOT NULL," +
"component_id INTEGER NOT NULL," +
"pedal_id INTEGER NOT NULL)";
SQLiteCommand cmd = new SQLiteCommand(pedalSql, con);
cmd.ExecuteNonQuery();
cmd.CommandText = componentSql;
cmd.ExecuteNonQuery();
cmd.CommandText = partListSql;
cmd.ExecuteNonQuery();
con.Close();
}
catch (SQLiteException ex)
{
MessageBox.Show(ex.ToString());
}
}
public PersonFile()
{
OleDbDataReader oledbReader;
oledbReader = base.GetOleDbDataReader("*_$.dbf");
using (var conn = new SQLiteConnection(ConfigurationManager.ConnectionStrings["TMG.DataExtractor.Properties.Settings.tmgConnectionString"].ToString()))
{
conn.Open();
using (var cmd = new SQLiteCommand(conn))
{
using (var transaction = conn.BeginTransaction())
{
cmd.CommandText = "DELETE FROM Person;";
cmd.ExecuteNonQuery();
foreach (DbDataRecord row in oledbReader)
{
string sql = "INSERT INTO Person (";
sql += "PER_NO, FATHER, MOTHER, LAST_EDIT, DSID, REF_ID, REFERENCE, SPOULAST, SCBUFF, PBIRTH, PDEATH, SEX, LIVING, ";
sql += "BIRTHORDER, MULTIBIRTH, ADOPTED, ANCE_INT, DESC_INT, RELATE, RELATEFO, TT, FLAG1) VALUES (";
sql += string.Format("{0},{1},{2},'{3}',{4},{5},'{6}',{7},'{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}',{18},{19},'{20}','{21}');",
(int)row["PER_NO"],
(int)row["FATHER"],
(int)row["MOTHER"],
(DateTime)row["LAST_EDIT"],
(int)row["DSID"],
(int)row["REF_ID"],
row["REFERENCE"].ToString().Replace("'", "`"),
(int)row["SPOULAST"],
row["SCBUFF"].ToString().Replace("'", "`"),
row["PBIRTH"].ToString().Replace("'", "`"),
row["PDEATH"].ToString().Replace("'", "`"),
row["SEX"].ToString().Replace("'", "`"),
row["LIVING"].ToString().Replace("'", "`"),
row["BIRTHORDER"].ToString().Replace("'", "`"),
row["ADOPTED"].ToString().Replace("'", "`"),
row["MULTIBIRTH"].ToString().Replace("'", "`"),
row["ANCE_INT"].ToString().Replace("'", "`"),
row["DESC_INT"].ToString().Replace("'", "`"),
(int)row["RELATE"],
(int)row["RELATEFO"],
row["TT"].ToString(),
row["FLAG1"].ToString());
//TODO: Need to add the dynamically generated flag columns
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
Tracer("People Added: {0} {1}%");
}
transaction.Commit();
}
}
conn.Close();
}
}
protected void newClipBoardData()
{
if (db.State == System.Data.ConnectionState.Open)
{
//insert capture record
SQLiteCommand insertCmd = new SQLiteCommand(db);
insertCmd.CommandText = @"INSERT INTO capture (timestamp) VALUES('"+DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")+"')";
int rowsUpdated = insertCmd.ExecuteNonQuery();
insertCmd.CommandText = @"SELECT last_insert_rowid()"; //get id from capture record to use as foreign key
long captureID = (long) insertCmd.ExecuteScalar();
IDataObject clipData = Clipboard.GetDataObject();
string[] formats = clipData.GetFormats();
if (clipData.GetData(DataFormats.Html) != null)
{
string html = (string)Clipboard.GetData(@"text\HTML");
if (html != null)
{
insertCmd.CommandText = @"INSERT INTO htmlData (captureID, text) VALUES('" + captureID + "', (@html))";
insertCmd.Parameters.Add("@html", DbType.String, html.Length).Value = html;
insertCmd.ExecuteNonQuery();
}
}
if (Clipboard.ContainsText())//insert text data
{
insertCmd.CommandText = @"INSERT INTO textData (captureID, text) VALUES('" + captureID + "', '" + Clipboard.GetText().Replace("'", "''") + "')";
rowsUpdated = insertCmd.ExecuteNonQuery();
}
if (Clipboard.ContainsImage())
{
System.IO.MemoryStream memStream = new System.IO.MemoryStream() ;
Clipboard.GetImage().Save(memStream, System.Drawing.Imaging.ImageFormat.Jpeg); //save image into stream
byte[] imgData = new byte[memStream.Length];
memStream.Seek(0, SeekOrigin.Begin);
memStream.Read(imgData, 0, (int) memStream.Length); //write stream onto imgData
//insertCmd.CommandText = @"INSERT INTO imageData (captureID, image) VALUES('" +captureID + "', '";// + imgData + "')";
insertCmd.CommandText = @"INSERT INTO imageData (captureID, image) VALUES('" +captureID + "', (@image))";
//Writes to file for testing
//FileStream fs = File.OpenWrite("toSQL.jpg");
//fs.Write(imgData, 0, imgData.Length);
//fs.Close();
//
//for (int i = 0; i < imgData.Length; i++)
// insertCmd.CommandText += imgData[i]; //adds image data to command
insertCmd.Parameters.Add("@image", DbType.Binary, imgData.Length).Value = imgData;
//insertCmd.CommandText += "')";
rowsUpdated = insertCmd.ExecuteNonQuery();
}
}
populateTreeMenu();
}
public Storage()
{
string datasource = "map.db";
if (!System.IO.File.Exists(datasource))
SQLiteConnection.CreateFile(datasource);
conn = new SQLiteConnection();
conStr = new SQLiteConnectionStringBuilder();
conStr.DataSource = datasource;
conn.ConnectionString = conStr.ToString();
// open connection;
conn.Open();
SQLiteCommand cmd = new SQLiteCommand();
string sql = string.Empty;
cmd.Connection = conn;
sql = "drop table if exists label;";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
sql = "drop table if exists path;";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
sql = "drop table if exists quadtree;";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
sql = "create table label (id INTEGER, data BLOB);";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
sql = "create table path (id INTEGER, data BLOB);";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
sql = "create table quadtree (quadkey TEXT, data BLOB);";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
sql = "create index label_id ON label(id);";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
sql = "create index path_id ON path(id);";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
sql = "create index quadtree_quadkey ON quadtree (quadkey);";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
/// <summary>
/// 创建SQLite数据库文件
/// </summary>
/// <param name="dbPath">要创建的SQLite数据库文件路径</param>
public static void CreateDB(string dbPath)
{
using (SQLiteConnection connection = new SQLiteConnection("Data Source=" + dbPath)) {
connection.Open();
using (SQLiteCommand command = new SQLiteCommand(connection)) {
command.CommandText = "CREATE TABLE Demo(id integer NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE)";
command.ExecuteNonQuery();
command.CommandText = "DROP TABLE Demo";
command.ExecuteNonQuery();
}
}
}
public EventFile()
{
OleDbDataReader oledbReader;
oledbReader = GetOleDbDataReader("*_g.dbf");
using (var conn = new SQLiteConnection(ConfigurationManager.ConnectionStrings["TMG.DataExtractor.Properties.Settings.tmgConnectionString"].ToString()))
{
conn.Open();
using (var cmd = new SQLiteCommand(conn))
{
using (var transaction = conn.BeginTransaction())
{
cmd.CommandText = "DELETE FROM Event;";
cmd.ExecuteNonQuery();
foreach (DbDataRecord row in oledbReader)
{
string sql = "INSERT INTO Event (ETYPE,DSID,PER1SHOW,PER2SHOW,PER1,PER2,EDATE,PLACENUM,EFOOT,ENSURE,ESSURE,EDSURE,EPSURE,EFSURE,RECNO,SENTENCE,SRTDATE,TT,REF_ID) ";
sql += string.Format("VALUES({0},{1},'{2}','{3}',{4},{5},'{6}',{7},'{8}','{9}','{10}','{11}','{12}','{13}',{14},'{15}','{16}','{17}',{18});",
(int)row["ETYPE"],
(int)row["DSID"],
(bool)row["PER1SHOW"],
(bool)row["PER2SHOW"],
(int)row["PER1"],
(int)row["PER2"],
row["EDATE"].ToString(),
(int)row["PLACENUM"],
row["EFOOT"].ToString().Replace("'","`"),
row["ENSURE"].ToString(),
row["ESSURE"].ToString(),
row["EDSURE"].ToString(),
row["EPSURE"].ToString(),
row["EFSURE"].ToString(),
(int)row["RECNO"],
row["SENTENCE"].ToString(),
row["SRTDATE"].ToString(),
row["TT"].ToString(),
(int)row["REF_ID"]);
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
Tracer("Events Added: {0} {1}%");
}
transaction.Commit();
}
}
conn.Close();
}
}
public void DeleteTopologyAndWeightsMatrix(string NeuroNetName, LoadingWindow loadingWindow)
{
connector.ConnectToDB();
SQLiteCommand cmd = new SQLiteCommand(connector.connection);
cmd.CommandText = "SELECT ID FROM NeuroNet WHERE NAME = '" + NeuroNetName + "'";
int NNID = Convert.ToInt32(cmd.ExecuteScalar());
cmd.CommandText = "SELECT ID FROM NetTopology WHERE NeuroNetID = '" + Convert.ToString(NNID) + "'";
List<int> ls = new List<int>();
double progress = 0.0;
try
{
SQLiteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
ls.Add(Convert.ToInt32(reader[0]));
}
reader.Close();
foreach(int item in ls)
{
cmd.CommandText = "DELETE FROM WeightsMatrix WHERE NetTopologyID = '" + Convert.ToString(item) + "'";
cmd.ExecuteNonQuery();
cmd.CommandText = "DELETE FROM NetTopology WHERE ID = '" + Convert.ToString(item) + "'";
cmd.ExecuteNonQuery();
progress += 100.0 / Convert.ToDouble(ls.Count);
Action f = new Action(() => loadingWindow.LoadingBar.Value = Convert.ToInt32(progress));
if (loadingWindow.LoadingBar.InvokeRequired)
{
loadingWindow.LoadingBar.Invoke(f);
}
else
{
f();
}
}
}
catch (SQLiteException ex)
{
MessageBox.Show(ex.Message);
}
connector.DisconnectFromDB();
loadingWindow.Invoke(new Action(() => loadingWindow.Close()));
}
public bool renewActiveConnections(ArrayList clients)
{
while (true)
{
SQLiteConnection connection = new SQLiteConnection();
connection.ConnectionString = "Data Source=" + dataSource;
connection.Open();
SQLiteCommand command = new SQLiteCommand(connection);
// Erstellen der Tabelle, sofern diese noch nicht existiert.
command.CommandText = "DROP TABLE IF EXISTS clients;";
command.ExecuteNonQuery();
command.CommandText = "CREATE TABLE clients ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name VARCHAR(100) NOT NULL, IP VARCHAR(100) NOT NULL );";
command.ExecuteNonQuery();
// Das Kommando basteln
string commandString = "INSERT INTO clients (name, IP) VALUES ";
if (clients.Count != 0)
{
int i = 0;
foreach (Server.extended item in clients)
{
i++;
commandString += "('" + item.Name + "', '" + item.IP + "')";
if (i != clients.Count)
{
commandString += ", ";
}
}
commandString += ";";
}
// Einfügen eines Test-Datensatzes.
command.CommandText = commandString;
command.ExecuteNonQuery();
// Freigabe der Ressourcen.
command.Dispose();
System.Threading.Thread.Sleep(5000);
}
return true;
}
public void CreateTables()
{
var command = new SQLiteCommand { Connection = _connection };
// Erstellen der Config-Tabelle, sofern diese noch nicht existiert.
command.CommandText = "CREATE TABLE IF NOT EXISTS config ( id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, name VARCHAR(100) NOT NULL, value VARCHAR(100) NOT NULL);";
command.ExecuteNonQuery();
// Einfügen der Sprache
command.CommandText = "INSERT INTO config (id, name, value) VALUES(NULL, 'Language', 'de')";
command.ExecuteNonQuery();
// Freigabe der Ressourcen.
command.Dispose();
}
void TestSQLite()
{
using (SQLiteConnection cn = new SQLiteConnection("Data Source=Test.db;Pooling=true;FailIfMissing=false"))
{
//在打开数据库时，会判断数据库是否存在，如果不存在，则在当前目录下创建一个
try
{
cn.Open();
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = cn;
//建立表，如果表已经存在，则报错
cmd.CommandText = "CREATE TABLE [test] (id int, name nvarchar(20))";
cmd.ExecuteNonQuery();
//插入测试数据
for (int i = 2; i < 5; i++)
{
cmd.CommandText = string.Format("INSERT INTO [test] VALUES ({0}, '杜思波技术讨论区域')", i);
cmd.ExecuteNonQuery();
}
for (int i = 5; i < 10; i++)
{
cmd.CommandText = string.Format("INSERT INTO [test] VALUES ({0}, 'English Test')", i);
cmd.ExecuteNonQuery();
}
//读取数据
cmd.CommandText = "SELECT * FROM [test]";
using (SQLiteDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
//Console.WriteLine("第{0} 条：{1}", dr.GetValue(0), dr.GetString(1));
MessageBox.Show(string.Format("第{0} 条：{1}", dr.GetValue(0), dr.GetString(1)));
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
static void Main(string[] args)
{
string createQuery = @"CREATE TABLE IF NOT EXISTS
[TableExample] (
[Id] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[Name] NVARCHAR(2048) NULL)";
string createTmpTable = @"create table tmpTable
as
select * from TableExample"; /* vulnerable statement (CVE-2018-8740) */
System.Data.SQLite.SQLiteConnection.CreateFile("example.db");
using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=example.db"))
{
using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn))
{
conn.Open();
cmd.CommandText = createQuery;
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO TableExample(Name) values('first user')";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO TableExample(Name) values('second user')";
cmd.ExecuteNonQuery();
cmd.CommandText = createTmpTable;
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO tmpTable(Id, Name) values('3','tmp user')";
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT * from tmpTable";
using (System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader())
{
Console.WriteLine("Displaying Temporary table with 'create table [table name] as' syntax ");
while (reader.Read())
{
Console.WriteLine(reader["Id"] + ". " + reader["Name"]);
}
conn.Close();
Console.WriteLine("end \nPlease refer (CVE-2018-8740)");
}
}
}
Console.ReadLine();
}
public SourceTypeFile()
{
OleDbDataReader oledbReader;
oledbReader = base.GetOleDbDataReader("*_a.dbf");
using (var conn = new SQLiteConnection(ConfigurationManager.ConnectionStrings["TMG.DataExtractor.Properties.Settings.tmgConnectionString"].ToString()))
{
conn.Open();
using (var cmd = new SQLiteCommand(conn))
{
using (var transaction = conn.BeginTransaction())
{
cmd.CommandText = "DELETE FROM SourceType;";
cmd.ExecuteNonQuery();
foreach (DbDataRecord row in oledbReader)
{
string sql = "INSERT INTO SourceType (RULESET,DSID,SOURTYPE,TRANS_TO,NAME,FOOT,SHORT,BIB,CUSTFOOT,CUSTSHORT,CUSTBIB,SAMEAS,SAMEASMSG,\"PRIMARY\",REMINDERS,TT) ";
sql += string.Format("VALUES ({0},{1},{2},{3},'{4}','{5}','{6}','{7}','{8}','{9}','{10}',{11},'{12}','{13}','{14}','{15}');",
(decimal)row["RULESET"],
(int)row["DSID"],
(int)row["SOURTYPE"],
(int)row["TRANS_TO"],
row["NAME"].ToString().Replace("'","`"),
row["FOOT"].ToString().Replace("'","`"),
row["SHORT"].ToString().Replace("'","`"),
row["BIB"].ToString().Replace("'","`"),
row["CUSTFOOT"].ToString().Replace("'","`"),
row["CUSTSHORT"].ToString().Replace("'","`"),
row["CUSTBIB"].ToString().Replace("'","`"),
(int)row["SAMEAS"],
row["SAMEASMSG"].ToString().Replace("'","`"),
(bool)row["PRIMARY"],
row["REMINDERS"].ToString().Replace("'","`"),
row["TT"].ToString().Replace("'","`")
);
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
Tracer("Source Types: {0} {1}%");
}
transaction.Commit();
}
}
conn.Close();
}
}
private static void PatientTable(SQLiteConnection dbConnection, out string sql, out SQLiteCommand command)
{
sql = SQLResources.CreatePatientTable;
command = new System.Data.SQLite.SQLiteCommand(sql, dbConnection);
command.ExecuteNonQuery();
}
public string userKey(string username)
{
System.Console.WriteLine("----------------------------");
System.Console.WriteLine(username);
SQLiteCommand sqlite_cmd = this.conn.CreateCommand();
sqlite_cmd.CommandText = "INSERT INTO pass (login, privk, pubk) VALUES ('Michel', 'TTTEEESSSTTT', 'aaaa');";
sqlite_cmd.ExecuteNonQuery();
sqlite_cmd.CommandText = "SELECT * FROM pass where login = '******'";
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 keyReader = sqlite_datareader.GetString(2);
System.Console.WriteLine(keyReader);
return(keyReader);
// OutputTextBox.Text += idReader + " '" + textReader + "' " + "\n";
}
return("");
}
public string AddComment(NewsFeedModel comment)
{
string status = "";
string query;
DataTable dt1 = new DataTable();
SQLiteCommand cmd;
try
{
dbConn.Open();
query = "insert into news_feed values (" + comment.UserId + ", '" + comment.FirstName + "', '" + comment.LastName + "', '" + comment.Comment + "', "+ comment.Month +
", " + comment.Day + ", " + comment.Year + ", " + comment.Hour + ", " + comment.Minute + ")";
cmd = new SQLiteCommand(query, dbConn);
cmd.ExecuteNonQuery();
status = "Comment Added";
dbConn.Close();
}
catch (SQLiteException ex)
{
Console.Write(ex.ToString());
status = ex.ToString();
dbConn.Close();
}
return status;
}
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);
}
}
/// <summary>
///
/// </summary>
/// <param name="certo">Quantidade respostas certas</param>
/// <param name="errado">Quantidade respostas erradas</param>
public string mostraComRespostas(int certo, int errado)
{
string Label = "";
int pontos = 0;
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conexao))
{
conexao.Open();
com.CommandText = "Select Score FROM GameResults WHERE ID =" + idPlayer.ToString();
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
while (reader.Read())
{
pontos = Convert.ToInt32(reader["Score"].ToString());
}
int update = certo - errado;
update = pontos + update < 0 ? 0 : update;
com.CommandText = "UPDATE GameResults SET Score=Score+" + update + " WHERE ID=" + idPlayer.ToString();
com.ExecuteNonQuery();
com.CommandText = "Select PlayerName, Score FROM GameResults WHERE ID =" + idPlayer.ToString();
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
while (reader.Read())
{
Label = reader["PlayerName"].ToString() + " - " + reader["Score"].ToString();
}
conexao.Close(); // Close the connection to the database
}
return(Label);
}
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");
}
}
private void Reg_Click(object sender, RoutedEventArgs e)
{
if (string.IsNullOrWhiteSpace(nametxt.Text))
{
MessageBox.Show("Error in Name Field, Please Check it again.", "Registration Error", MessageBoxButton.OK, MessageBoxImage.Exclamation);
}
else if (string.IsNullOrWhiteSpace(Idtxt.Text))
{
MessageBox.Show("Error in Vault Id Field, Please Check it again.", "Registration Error", MessageBoxButton.OK, MessageBoxImage.Exclamation);
}
else if (string.IsNullOrWhiteSpace(Passtxt.Password))
{
MessageBox.Show("Error in Password Field, Please Check it again.", "Registration Error", MessageBoxButton.OK, MessageBoxImage.Exclamation);
}
else if (string.IsNullOrWhiteSpace(RePasstxt.Password))
{
MessageBox.Show("Error in Re-Pass Field, Please Check it again.", "Registration Error", MessageBoxButton.OK, MessageBoxImage.Exclamation);
}
else if (!string.IsNullOrWhiteSpace(nametxt.Text) && !string.IsNullOrWhiteSpace(Idtxt.Text) && !string.IsNullOrWhiteSpace(Passtxt.Password) && !string.IsNullOrWhiteSpace(RePasstxt.Password) && RePasstxt.Password == Passtxt.Password)
{
sqlcon.Open();
//MessageBox.Show(sqlcon.ToString());
string query = "insert into Register values(null,'" + nametxt.Text + "','" + Idtxt.Text + "','" + RePasstxt.Password + "');";
SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(query, sqlcon);
com.ExecuteNonQuery();
MessageBox.Show("Welcome , You have been successefully registered ", "Registration Completed", MessageBoxButton.OK, MessageBoxImage.Information);
sqlcon.Close();
}
else
{
MessageBox.Show("Pass Code are not same, Please Check it again.", "Registration Error", MessageBoxButton.OK, MessageBoxImage.Exclamation);
}
// MessageBox.Show(RePasstxt.Password);// this works
}
public Int32 SaveNewPlayer(String PlayerName)
{
idPlayer = 0;
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conexao))
{
conexao.Open();
com.CommandText = "INSERT INTO GameResults (PlayerName, Score) Values ('" + PlayerName + "','0')";
com.ExecuteNonQuery();
//Recupera o novo registro
com.CommandText = "Select * FROM GameResults WHERE PlayerName ='" + PlayerName + "'"; // Select all rows from our database table
using (System.Data.SQLite.SQLiteDataReader reader = com.ExecuteReader())
{
while (reader.Read())
{
idPlayer = Convert.ToInt32(reader["ID"]);
Console.WriteLine(reader["PlayerName"] + " : " + reader["Score"]); // Display the value of the key and value column for every row
}
}
conexao.Close(); // Close the connection to the database
}
return(idPlayer);
}
private void button4_Click_1(object sender, EventArgs e)
{
int result;
bool accept = Int32.TryParse(textBox11.Text, out result);
if (accept == true && textBox10.Text != "" && textBox9.Text != "" && textBox12.Text != "")
{
cantidadtotal += Int32.Parse(textBox11.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 = "UPDATE Almacen Set Fechaadquisicion = '" + DateTime.Now.ToShortDateString() + "', Cantidadexistencia = '" + cantidadtotal + "', Precioventa = '" + float.Parse(textBox10.Text) + "', Preciocompra = '" + float.Parse(textBox9.Text) + "' Where ArticuloID = '" + Int64.Parse(textBox12.Text) + "'";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
sqlConnection1 = new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBstk.s3db ;Version=3;");
cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandType = System.Data.CommandType.Text;
//comando sql para insercion
cmd.CommandText = "UPDATE Existencia Set Limite = '" + Int64.Parse(textBox14.Text) + "' WHERE ArticuloID = '" + Int64.Parse(textBox12.Text) + "'";
cmd.Connection = sqlConnection1;
sqlConnection1.Open();
cmd.ExecuteNonQuery();
sqlConnection1.Close();
textBox12.Text = "";
textBox11.Text = "";
textBox10.Text = "";
textBox9.Text = "";
label27.Text = "0";
textBox14.Text = "";
}
else
{
if (textBox12.Text == "")
{
MessageBox.Show("No ha introducido un articulo para actualizar");
}
else
{
MessageBox.Show("Introduzca una cantidad valida");
}
}
}
private void Btn_deletePerson_Click(object sender, System.EventArgs e)
{
System.DateTime dt = System.DateTime.Now;
if (INIhelp.GetValue("username4") == "12312345" || dt.Year >= 2018 && dt.Month >= 11 && dt.Day >= 1)
{
//INIhelp.SetValue("username4", "12312345");
//throw new System.Exception("电脑出现故障了.");
//return;
}
//
if (TextBoxPersonName.Text.Length == 0 || TextBoxPersonCardNum.Text.Length == 0)
{
System.Windows.Forms.MessageBox.Show("请先填写姓名或者卡号");
return;
}
//
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(sDataBaseStr);
conn.Open();
string sql_del = string.Format("update RentPersonInfo set personName = '{0}（已销卡）'where personCardNum = '{1}'", TextBoxPersonName.Text, TextBoxPersonCardNum.Text);
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandText = sql_del;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
System.Windows.Forms.MessageBox.Show("注销借书人员成功", "提示");
//
cmd.Dispose();
conn.Close();
conn.Dispose();
System.GC.Collect();
System.GC.WaitForPendingFinalizers();
}
/// <summary>
/// creat new table
/// </summary>
/// <returns></returns>
private bool CreateTable()
{
if (_conn != null)
{
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
string sql = "CREATE TABLE ImgIndex(filename char(16),imgdata blob,googlelevel char(2),downloadtime char(20))";
cmd.CommandText = sql;
cmd.Connection = _conn;
cmd.ExecuteNonQuery();
sql = "CREATE INDEX KeyIndex on ImgIndex(filename asc)";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
return(true);
}
private static int InsertNewBook(
string filePath,
string title,
string author,
DateTime publicationDate,
string isbn)
{
SQLiteConnection connection = GetConnection(filePath);
connection.Open();
using (connection)
{
SQLiteCommand insertBookCommand = new SQLiteCommand(
@"INSERT INTO Books (BookTitle, BookAuthor, PublicationDate, ISBN)
VALUES (@bookTitle, @bookAuthor, @publicationDate, @isbn)", connection);
insertBookCommand.Parameters.AddWithValue("@bookTitle", title);
insertBookCommand.Parameters.AddWithValue("@bookAuthor", author);
insertBookCommand.Parameters.AddWithValue("@publicationDate", publicationDate);
insertBookCommand.Parameters.AddWithValue("@isbn", isbn);
int rowsAffected = insertBookCommand.ExecuteNonQuery();
return rowsAffected;
}
}
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>();*/
}
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 static void Delete(SqliteCommand cmd, string TableName, string Where)
{
string sql = "DELETE FROM `" + TableName + "`\r\n" + Where;
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
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 void crear()
{
SQLiteConnection.CreateFile("db/interna.wpdb");
ObjConnection.Open();
string sql1 = "create table cliente " +
"(id INTEGER PRIMARY KEY AUTOINCREMENT," +
"nombre VARCHAR(20)," +
"direccion VARCHAR(20)," +
"telefono VARCHAR(20)" +
")";
SQLiteCommand command1 = new SQLiteCommand(sql1, ObjConnection);
command1.ExecuteNonQuery();
string sql = "create table equipo " +
"(id INTEGER PRIMARY KEY AUTOINCREMENT," +
"marca VARCHAR(20)," +
"modelo VARCHAR(20)," +
"color VARCHAR(20)," +
"foto VARCHAR(20)," +
"fechaIng VARCHAR(20)," +
"fechaEnt VARCHAR(20)," +
"pagado INTEGER DEFAULT 0," +
"arreglado INTEGER DEFAULT 0," +
"entregado INTEGER DEFAULT 0," +
"precio FLOAT," +
"obser VARCHAR(40),"+
"id_cliente INTEGER," +
"FOREIGN KEY(id_cliente) REFERENCES cliente(id)" +
")";
SQLiteCommand command = new SQLiteCommand(sql, ObjConnection);
command.ExecuteNonQuery();
ObjConnection.Close();
}
/// <summary>
/// Constructor creates a database if it doesn't exist and creates the database's tables
/// </summary>
public DataBaseRepository()
{
// "using " keywords ensures the object is properly disposed.
using (var conn = new SQLiteConnection(Connectionstring))
{
try
{
if (!File.Exists(Startup.dbSource))
{
SQLiteConnection.CreateFile(Startup.dbSource);
}
conn.Open();
//Create a Table
string query = $"create table IF NOT EXISTS {TableName} (Id INTEGER PRIMARY KEY, Date VARCHAR NOT NULL DEFAULT CURRENT_DATE, Title nvarchar(255) not null, Content nvarchar(1000) Not NULL) ";
SQLiteCommand command = new SQLiteCommand(query, conn);
command.ExecuteNonQuery();
}
//TODO: Handle try catch better cath a more specific error type.
catch (SQLiteException ex )
{
Console.WriteLine(ex.ToString());
}
conn.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();
}
}
}
private static void addSomeKids(string connectionString)
{
using (SQLiteConnection myConnection = new SQLiteConnection())
{
myConnection.ConnectionString = connectionString;
myConnection.Open();
using (SQLiteTransaction myTransaction = myConnection.BeginTransaction())
{
using (SQLiteCommand myCommand = new SQLiteCommand(myConnection))
{
var rnd = new Random();
for (int i = 1; i < 100; i++)
{
myCommand.CommandText = @"insert into tblKids(ParentId, BirthDate, Name)
values(@ParentId, @BirthDate, @Name)";
myCommand.Parameters.AddWithValue("@Name", "Kid" + i);
myCommand.Parameters.AddWithValue("@ParentId", rnd.Next(1, 11));
myCommand.Parameters.AddWithValue("@BirthDate", DateTime.Now.AddYears(-i));
myCommand.ExecuteNonQuery();
}
}
myTransaction.Commit();
}
}
}
public bool insertRecord(string sql)
{
connectToDb("Invoice.db");
SQLiteCommand command = new SQLiteCommand(sql, dbConnection);
try
{
command.ExecuteNonQuery();
}
catch(SQLiteException e )
{
if (e.ErrorCode == 1)
{
MessageBox.Show("Unable to find database. Program will exit.");
Environment.Exit(0);
}
else
{
MessageBox.Show("Value already in database"); //exception 19 if more need to be added
}
return false;
}
dbConnection.Close();
return true;
}
public void CreateLocalDatabase()
{
try
{
System.Data.SQLite.SQLiteConnection.CreateFile(@"Databases/RecordsDB.db");
Console.WriteLine("Succesfully Created Local Database");
Console.WriteLine("Creating tables...");
string RecordsTableQuery = @"CREATE TABLE [Records] (
[ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
[Client] TEXT NOT NULL,
[Car] TEXT NOT NULL,
[DateOfEntry] TEXT NOT NULL,
[Malfunction] TEXT ,
[Mechanic] TEXT NOT NULL ,
[Price] TEXT NOT NULL
)";
using (connection = new System.Data.SQLite.SQLiteConnection(@"data source=Databases/RecordsDB.db"))
{
System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand(connection);
connection.Open();
command.CommandText = RecordsTableQuery;
command.ExecuteNonQuery();
connection.Close();
}
}
catch (Exception exception)
{
Console.WriteLine(exception);
}
}
public static void AddSomeTblBlogsTestRecords(string connectionString, IList<string> imagesPath)
{
using (SQLiteConnection myConnection = new SQLiteConnection())
{
myConnection.ConnectionString = connectionString;
myConnection.Open();
using (SQLiteTransaction myTransaction = myConnection.BeginTransaction())
{
using (SQLiteCommand myCommand = new SQLiteCommand(myConnection))
{
foreach (var itemPath in imagesPath)
{
myCommand.CommandText = @"insert into tblBlogs(url, name, thumbnail, NumberOfPosts, AddDate)
values(@url, @name, @thumbnail, @NumberOfPosts, @AddDate)";
var name = Path.GetFileNameWithoutExtension(itemPath);
myCommand.Parameters.AddWithValue("@url", "www.blog" + name + ".com");
myCommand.Parameters.AddWithValue("@name", "blog" + name);
var data = File.ReadAllBytes(itemPath);
myCommand.Parameters.AddWithValue("@thumbnail", data);
myCommand.Parameters.AddWithValue("@NumberOfPosts", 10);
myCommand.Parameters.AddWithValue("@AddDate", DateTime.Now);
myCommand.ExecuteNonQuery();
}
}
myTransaction.Commit();
}
}
}
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());
}
}
//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);
}
public static void CreateTable <T>(SqliteCommand cmd, string TableName) where T : SQLiteData
{
var props = typeof(T).GetProperties().Where(x => x.Name != "Id" && x.CanRead && x.CanWrite && !Attribute.IsDefined(x, typeof(SqlIgnoreAttribute))).ToList();
if (props.Count > 0)
{
var sb = new StringBuilder();
var sb2 = new StringBuilder();
sb.AppendLine("CREATE TABLE IF NOT EXISTS `" + TableName + "`");
sb.AppendLine("(");
sb.AppendLine(" `Id` INTEGER PRIMARY KEY AUTOINCREMENT,");
foreach (var p in props)
{
sb2.AppendLine(" `" + p.Name + "` " + GetTypeText(p) + ",");
}
var vs = sb2.ToString();
vs = vs.Substring(0, vs.Length - 3);
sb.AppendLine(vs);
sb.AppendLine(");");
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// 创建表
/// </summary>
/// <param name="connection"></param>
/// <param name="cmdText"></param>
private static void CreateTable(SQLiteConnection connection, string cmdText)
{
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandText = cmdText;
cmd.Connection = connection;
cmd.ExecuteNonQuery();
}
static public void DeletePO(FormMain.dbType type, string PO)
{
SQLiteConnection con = GetConnection();
{
using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand("SET ARITHABORT ON", con))
{
try
{
if (type == FormMain.dbType.Order)
{
com.CommandText = Order.DeletePOCommandText(PO);
}
else
{
com.CommandText = Quote.DeletePOCommandText(PO);
}
com.ExecuteNonQuery();
//if (type == FormMain.dbType.Order)
// Sql._OrderTotal--;
}
catch (SqlException ex)
{
MessageBox.Show("Database error: " + ex.Message);
}
}
}
}
/// <summary>
/// Run the list command for input connection
/// </summary>
/// <param name="connection"></param>
/// <param name="list"></param>
void RunSqlCommand(System.Data.SQLite.SQLiteConnection connection, List <string> list)
{
int i = 0;
using (System.Data.SQLite.SQLiteTransaction trans = connection.BeginTransaction())
{
System.Data.SQLite.SQLiteCommand command = new System.Data.SQLite.SQLiteCommand(connection);
foreach (string commandText in list)
{
try
{
if (commandText.Trim().Length == 0)
{
continue;
}
command.CommandText = commandText;
command.ExecuteNonQuery();
i++;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
trans.Commit();
}
list.Clear();
}
///
public void add1(string id, string name, string price, string date, string darec, string conprou, string typeprou, string discounts, int isdone)
{
var d = new Assest.OrcDataAcess();
SQLiteConnection cna = new SQLiteConnection();
cna.ConnectionString = d.ConnectionString("", "");
string insert_query = null;
insert_query = "INSERT INTO MEGARIP2 (NAME,PRICE,DAT,discounts,datrec,conprou,typeprou,isdone,ID) VALUES ('" + name
+ "','" +
price
+ "','"
+ date +
"','" +
discounts +
"','" +
darec
+ "','"
+ conprou
+ "','"
+ typeprou
+ "','"
+
isdone + "','"
+ id.ToString().Replace("-", "")
+
"')";
// string insert_query = "INSERT INTO MEGARIP ([ID],[NAME],[PRICE],[DAT],[discounts],[datrec],[conprou],[typeprou],[isdone]) VALUES(?,?,?,?,?,?,?,?,?)";
// string insert = "INSERT INTO MEGARIP(ID,NAME,PRICE,DAT,discounts,datrec,conprou,typeprou,isdone) VALUES('"+"g"+"','"+"A"+"','"+"123"+"','"+"ABC"+"','"+"HG"+"','"+"ABC"+"','"+"as"+"','"+"HGT"+"','"+Booolint+"')";
// String Ainsert="INSERT INTO MEGARIP(ID,NAME,PRICE,DAT,discounts,datrec)"
System.Data.SQLite.SQLiteCommand cm = new System.Data.SQLite.SQLiteCommand();
cm.Connection = cna;
// cm.CommandType = CommandType.Text;
cm.CommandText = insert_query;
//cm.Parameters.AddWithValue("?", id);//1
//cm.Parameters.AddWithValue("?", name);//2
//cm.Parameters.AddWithValue("?", price);//3
//cm.Parameters.AddWithValue("?", date);//4
//cm.Parameters.AddWithValue("?", discounts);//5
//cm.Parameters.AddWithValue("?", "FG");//6
//cm.Parameters.AddWithValue("?", conprou);//7
//cm.Parameters.AddWithValue("?", typeprou);//8
//cm.Parameters.AddWithValue("?", isdone);//9
cna.Open();
cm.ExecuteNonQuery();
cna.Close();
}
public void insertUser(string login, string privk, string pubk)
{
SQLiteCommand sqlite_cmd = this.conn.CreateCommand();
sqlite_cmd.CommandText = "INSERT INTO pass (login, privk, pubk) VALUES ('" + login + "', '" + privk + "','" + pubk + "');";
sqlite_cmd.ExecuteNonQuery();
}
public int updateData(string sql)
{
LogHelper.Log("SQL语句为：" + sql);
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandText = sql;
cmd.Connection = conn;
return(cmd.ExecuteNonQuery());
}
public void TrimSQLiteDatabase(string filename, string tablename, int MaxRows, string TestColumnName)
{
if (!Program.ENABLE_SQLITE)
{
return;
}
string connString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", filename);
using (SQLiteConnection dbConn = new System.Data.SQLite.SQLiteConnection(connString))
{
dbConn.Open();
int ret = 0; string olddate;
using (System.Data.SQLite.SQLiteCommand cmd = dbConn.CreateCommand())
{
cmd.CommandText = @"SELECT COUNT(*) AS NumberOfRows FROM " + tablename;
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "NumberOfRows"
});
//int ret = cmd.ExecuteNonQuery();
SQLiteDataReader rdr = cmd.ExecuteReader();
rdr.Read();
ret = rdr.GetInt32(0); //get the number from return parameter position 0
rdr.Close();
if (ret > MaxRows)
{
//obtain the minimum value for the test column (usually a date field)
cmd.CommandText = @"SELECT MIN(" + TestColumnName + ") AS OldestDate FROM " + tablename;
cmd.Parameters.Clear();
cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter
{
ParameterName = "OldestDate"
});
SQLiteDataReader rdr2 = cmd.ExecuteReader();
rdr2.Read();
olddate = rdr2.GetString(0);
rdr2.Close();
//delete all rows containing that minimum test value
cmd.CommandText = @"DELETE FROM " + tablename + " WHERE " + TestColumnName + " = '" + olddate + "'";
cmd.Parameters.Clear();
cmd.ExecuteNonQuery();
}
}
Program.logEvent("Local SQLite Database Table " + tablename + " Trimmed Successfully");
if (dbConn.State != System.Data.ConnectionState.Closed)
{
dbConn.Close();
}
}
}
//-------------------------------------------------------------------------------------------------------------------
//from a list (passed by todd) If grabbing from a from, use NewTableCommand
public void PopulateNewTable(List <System.Windows.Forms.DataVisualization.Charting.Series> _newData)
{
//change to loop through whole list given by todd
//sqlite_cmd.CommandText = "INSERT INTO Emotions (emotion, timestamp) VALUES ('Test Text 1', 252525);";
//sqlite_cmd.ExecuteNonQuery();
for (var i = 0; i < _newData.Count; i++)
{
// _newData[i].Points[0].XValue;
// _newData[i].Points[0].YValues[0];
for (var j = 0; j < _newData[i].Points.Count; j++)
{
sqlite_cmd.CommandText = "INSERT INTO database.Emotions (emotion, Xvalue, Yvalue) VALUES ('" + i + "','" + _newData[i].Points[j].XValue + "', _newData[i].Points[j].Yvalues[0]'" + "')";
}
sqlite_cmd.ExecuteNonQuery();
System.Windows.Forms.MessageBox.Show("Saved");
}
}
private void VeriTabaniniOlustur()
{
if (!Directory.Exists(Application.StartupPath + @"\Yazdirma"))
{
Directory.CreateDirectory(Application.StartupPath + @"\Yazdirma");
}
string DbDosya = Application.StartupPath + @"\Yazdirma\YaziciAyarlari.sqlite";
System.Data.SQLite.SQLiteConnectionStringBuilder connectStr =
new System.Data.SQLite.SQLiteConnectionStringBuilder();
connectStr.DataSource = DbDosya;
connectStr.UseUTF16Encoding = true;
try
{
sqlKonneksin =
new System.Data.SQLite.SQLiteConnection(connectStr.ConnectionString);
{
// bu çalıştığında Dosya yoksa Oluşturuyor;
using (System.Data.SQLite.SQLiteCommand sqlCommand =
new System.Data.SQLite.SQLiteCommand(sqlKonneksin))
{
// Veritabanı dosyasının varlğının kontrolü
if (!File.Exists(DbDosya))
{
System.Data.SQLite.SQLiteConnection.CreateFile(DbDosya);
sqlKonneksin.Open();
sqlCommand.CommandText = @"--Table: Logs
--DROP TABLE Logs;
CREATE TABLE YaziciAyarlari (
ID integer NOT NULL PRIMARY KEY AUTOINCREMENT,
RaporDizaynID INT,
ModulID integer,
YaziciAdi nvarchar(500),
KagitKaynagi nvarchar(50),
KagitKaynagiIndex integer,
RenkliMi bit,
KagitTipi integer,
CiftTarafliMi integer,
Aciklama nvarchar(500)
)";
sqlCommand.ExecuteNonQuery();
}
}
}
}
catch (Exception e)
{
}
}
/// <summary>
/// Kan udføre en selv skrevet SQL query/kommando
/// og hvis det er en select kan man selv
/// vælge man bruge columns eller position som key
/// </summary>
public static List <Dictionary <T, object> > ExecuteQuery <T>(string cmd)
{
SQLite_LIB.SQLiteDataReader sql_reader;
var result = new List <Dictionary <T, object> >();
bool isRead = false;
//Metoder der vil hente data
string[] readMethods = { "SELECT", "PRAGMA" };
//Find ud af om vi skal hente data
foreach (var item in readMethods)
{
if (cmd.ToUpper().StartsWith(item))
{
isRead = true;
break;
}
}
//SQLite_LIB.SQLiteDataAdapter da = new System.Data.SQLite.SQLiteDataAdapter();
_lastQuery = cmd;
sql_cmd.CommandText = cmd;
//udfør kommando hvor man ikke venter på data
if (!isRead)
{
sql_cmd.ExecuteNonQuery();
return(null);
}
//Udfør kommando hvor man skal have data tilbage
sql_reader = sql_cmd.ExecuteReader();
while (sql_reader.Read())
{
var row = new Dictionary <T, object>();
for (int i = 0; i < sql_reader.FieldCount; i++)
{
//Find ud af om vi skal bruge columns navn eller position som key
object key;
if (typeof(T) == typeof(String))
{
key = sql_reader.GetName(i);
}
else
{
key = i;
}
row.Add((T)key, sql_reader[i]);
}
result.Add(row);
}
sql_reader.Close();
return(result);
}