public void SetUp()
{
#if NETFX_CORE
m_path = Path.GetRandomFileName();
#else
m_path = Path.Combine(Path.GetTempPath(), Path.GetRandomFileName());
#endif
m_csb = new SQLiteConnectionStringBuilder { DataSource = m_path, MmapSize = m_mmapSize };
}
public void SetUp()
{
#if NETFX_CORE
m_path = Path.GetRandomFileName();
#else
m_path = Path.Combine(Path.GetTempPath(), Path.GetRandomFileName());
#endif
m_csb = new SQLiteConnectionStringBuilder { DataSource = m_path, JournalMode = SQLiteJournalModeEnum.Truncate };
}
static IX509CertificateDatabase OpenDatabase (string fileName)
{
var builder = new SQLiteConnectionStringBuilder ();
builder.DateTimeFormat = SQLiteDateFormats.Ticks;
builder.DataSource = fileName;
if (!File.Exists (fileName))
SQLiteConnection.CreateFile (fileName);
var sqlite = new SQLiteConnection (builder.ConnectionString);
sqlite.Open ();
return new SqliteCertificateDatabase (sqlite, "password");
}
public void Defaults()
{
var csb = new SQLiteConnectionStringBuilder();
Assert.AreEqual(0, csb.CacheSize);
Assert.AreEqual("", csb.DataSource);
Assert.AreEqual(0, csb.DefaultTimeout);
Assert.IsFalse(csb.FailIfMissing);
Assert.IsFalse(csb.ForeignKeys);
Assert.AreEqual(SQLiteJournalModeEnum.Default, csb.JournalMode);
Assert.AreEqual(0, csb.PageSize);
Assert.IsNull(csb.Password);
Assert.IsFalse(csb.ReadOnly);
Assert.AreEqual(SynchronizationModes.Normal, csb.SyncMode);
Assert.AreEqual(SQLiteTemporaryStore.Default, csb.TempStore);
}
public void ParseConnectionString()
{
var csb = new SQLiteConnectionStringBuilder { ConnectionString = "Data Source=\"C:\\temp\\test.db\";Synchronous=full;Read Only=true;FailIfMissing=True;Foreign Keys=true;Cache Size=6000;Page Size=2048;default timeout=30;password=S3kr3t;_TempStore=memory" };
Assert.AreEqual(6000, csb.CacheSize);
Assert.AreEqual(@"C:\temp\test.db", csb.DataSource);
Assert.AreEqual(30, csb.DefaultTimeout);
Assert.IsTrue(csb.FailIfMissing);
Assert.IsTrue(csb.ForeignKeys);
Assert.AreEqual(SQLiteJournalModeEnum.Default, csb.JournalMode);
Assert.AreEqual(2048, csb.PageSize);
Assert.AreEqual("S3kr3t", csb.Password);
Assert.IsTrue(csb.ReadOnly);
Assert.AreEqual(SynchronizationModes.Full, csb.SyncMode);
Assert.AreEqual(SQLiteTemporaryStore.Memory, csb.TempStore);
}
private void create_db()
{
saveFileDialog1.ShowDialog();
var path = saveFileDialog1.FileName;
//var name = System.IO.Path.GetFileName(path);
SQLiteConnection.CreateFile(path);
SQLiteFactory factory = DbProviderFactories.GetFactory("System.Data.SQLite") as SQLiteFactory;
SQLiteConnection connection = factory.CreateConnection() as SQLiteConnection;
connection.ConnectionString = "Data Source=" + path;
connection.Open();
SQLiteCommand command = new SQLiteCommand(connection);
command.CommandText = @"CREATE TABLE [Ciphers_Table] ( [ID] integer PRIMARY KEY AUTOINCREMENT NOT NULL,
[Cycle] text,
[Current_pos] text,
[Start_set] text ); ";
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
connection.Close();
//создали базу
System.Data.SQLite.SQLiteConnectionStringBuilder connect = new SQLiteConnectionStringBuilder("Data Source=" + path);
System.Data.EntityClient.EntityConnectionStringBuilder entity_connect = new System.Data.EntityClient.EntityConnectionStringBuilder();
entity_connect.Provider = @"System.Data.SQLite";
entity_connect.ProviderConnectionString = connect.ConnectionString;
entity_connect.Metadata = @"res://*/DB_Model.csdl|res://*/DB_Model.ssdl|res://*/DB_Model.msl";
data_source = new Cipher_catEntities(entity_connect.ConnectionString);
MessageBox.Show("База удачно создана и подключена");
//data_source.Ciphers_Table.AddObject(new Ciphers_Table
//{
// Current_pos = "test",
// Cycle = "test",
// Start_set = "test"
//});
//data_source.SaveChanges();
}
/// <summary>
/// 根据ID获取账号下的最近一条数据
/// </summary>
/// <returns></returns>
public Info GetOne(string patentNo)
{
Info model = null;
SQLiteConnectionStringBuilder sb = new SQLiteConnectionStringBuilder();
sb.DataSource = DataSource;
using (IDbConnection con = new SQLiteConnection(sb.ToString()))
{
con.Open();
string sql = "select * from 'Info' where PatentNo='" + patentNo + "' ORDER BY ID DESC LIMIT 1 ";
List <Info> list = Dapper.SqlMapper.Query <Info>(con, sql).ToList();
if (list != null && list.Count == 1)
{
model = list.Single();
}
con.Close();
}
return(model);
}
public void TestConnection()
{
/*
* DbProviderFactory fact = DbProviderFactories.GetFactory("System.Data.SQLite");
* using (DbConnection cnn = fact.CreateConnection())
* {
* cnn.ConnectionString = "Data Source=test.db3";
* cnn.Open();
* }
*/
var builder = new SQLiteConnectionStringBuilder();
builder.DataSource = "test.db";
SQLiteConnection sql = new SQLiteConnection(builder.ToString());
sql.Open();
sql.Close();
sql.Dispose();
}
private string _errorInfo; //最后一次错误信息
/// <summary>
/// 初始化
/// </summary>
/// <param name="dataSource">数据库文件路径</param>
public SQLHelper(string dataSource)
{
try
{
var connectionStringBuilder = new SQLiteConnectionStringBuilder
{
Version = 3,
Pooling = true,
FailIfMissing = false,
DataSource = dataSource
};
var mConnectionString = connectionStringBuilder.ConnectionString;
_mDbConnection = new SQLiteConnection(mConnectionString);
}
catch (Exception ex)
{
throw ex;
}
}
public void Init(Uri providerUri)
{
var builder
= new SQLiteConnectionStringBuilder
{
SyncMode = SynchronizationModes.Off,
DataSource = _tmDatabasePath,
Enlist = false,
Pooling = false
};
_db = Database.OpenConnection(builder.ToString());
var persistance = new Persistance();
var addSourceTmConfigurations = persistance.Load();
_addSourceTmConfiguration = addSourceTmConfigurations.Configurations.FirstOrDefault(x => x.ProviderUri == providerUri) ??
addSourceTmConfigurations.Default;
}
public static string GetConnectionString(SqlDbConnectionType connectionType, String strServer, String strDatabase, String strUsername, String strPassword)
{
switch (connectionType)
{
case SqlDbConnectionType.MySql:
string[] lstServer = strServer.Split(':');
MySqlConnectionStringBuilder mysqlBuilder = new MySqlConnectionStringBuilder
{
Server = lstServer.FirstOrDefault(),
Port = Convert.ToUInt32(lstServer.LastOrDefault()),
Database = strDatabase,
UserID = strUsername,
Password = strPassword,
ConvertZeroDateTime = true,
AllowUserVariables = true
};
_connectionString = mysqlBuilder.ConnectionString;
break;
case SqlDbConnectionType.Sqlite:
var builder = new SQLiteConnectionStringBuilder
{
DataSource = strServer,
Version = 3
};
_connectionString = builder.ConnectionString;
break;
default:
SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder
{
DataSource = strServer,
UserID = strUsername,
Password = strPassword,
InitialCatalog = strDatabase
};
_connectionString = sqlBuilder.ConnectionString;
break;
}
return(_connectionString);
}
public SQLiteDatabase(string path)
{
if (!File.Exists(path))
{
throw new FileNotFoundException(path);
}
var connectionstring = new SQLiteConnectionStringBuilder
{
DataSource = path
};
SQLiteConnection connection = new SQLiteConnection(connectionstring.ToString());
SqliteCompiler compiler = new SqliteCompiler();
connection.Open();
this.db = new QueryFactory(connection, compiler);
}
public static List <string[]> Find(string pattern)
{
var tuples = new List <string[]>();
if (pattern.Length == 0 || pattern == null)
{
return(tuples);
}
var connectionSb = new SQLiteConnectionStringBuilder {
DataSource = App.DB_PATH, Version = 3
};
using (var conn = new SQLiteConnection(connectionSb.ToString()))
{
conn.Open();
using (DbCommand command = conn.CreateCommand())
{
command.CommandText = "SELECT s.name, s.url, k.user, k.secret FROM services AS s " +
"LEFT JOIN keypairs as k ON s.id = k.service " +
"WHERE s.name LIKE '%' || @pattern || '%' OR s.url LIKE '%' || @pattern || '%' " +
"ORDER BY s.name, s.url, k.created DESC LIMIT 10";
command.Parameters.Add(new SQLiteParameter("pattern", pattern));
using (var reader = command.ExecuteReader())
{
while (reader.Read() == true)
{
string[] record = new string[4];
record[0] = reader["name"].ToString();
record[1] = reader["url"].ToString();
record[2] = reader["user"].ToString();
record[3] = reader["secret"].ToString();
tuples.Add(record);
}
reader.Close();
}
}
conn.Close();
}
return(tuples);
}
/// <summary>
/// システムテーブルの作成
/// </summary>
/// <param name="dbPath">データベースのパス</param>
/// <param name="schemePath">スキーマのパス</param>
public void CreateSystemTableByScheme(string dbPath, string schemePath)
{
var connectionString = new SQLiteConnectionStringBuilder
{
DataSource = dbPath
};
var sql = File.ReadAllText(schemePath, Encoding.UTF8);
using (var connection = new SQLiteConnection(connectionString.ConnectionString))
{
connection.Open();
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = sql;
command.ExecuteNonQuery();
}
}
}
/// <summary>
/// DBを最適化する。
/// </summary>
/// <param name="path">DBのパス</param>
public void CompactDB(string path)
{
SQLiteConnectionStringBuilder connectionString = new SQLiteConnectionStringBuilder
{
DataSource = path
};
string compactDB = $"VACUUM;";
using (SQLiteConnection connection = new SQLiteConnection(connectionString.ConnectionString))
{
connection.Open();
using (SQLiteCommand command = new SQLiteCommand(connection))
{
command.CommandText = compactDB;
command.ExecuteNonQuery();
}
}
}
// Insert in to table
public static bool Insert(string filename, string tableName, Dictionary <string, string> data)
{
string columns = "";
string values = "";
bool returnCode = true;
foreach (KeyValuePair <String, String> val in data)
{
columns += String.Format(" {0},", val.Key);
values += String.Format(" '{0}',", val.Value);
}
columns = columns.Substring(0, columns.Length - 1);
values = values.Substring(0, values.Length - 1);
string myInsertQuery = string.Format("INSERT INTO {0}({1}) VALUES ({2});", tableName, columns, values);
Console.WriteLine(myInsertQuery);
try
{
var connStr = new SQLiteConnectionStringBuilder()
{
DataSource = filename
};
using (SQLiteConnection conn = new SQLiteConnection(connStr.ToString()))
{
conn.Open();
SQLiteCommand cmd = new SQLiteCommand(conn)
{
CommandText = myInsertQuery
};
cmd.ExecuteNonQuery();
conn.Close();
}
}
catch (Exception)
{
//System.Console.WriteLine(fail.Message);
returnCode = false;
}
return(returnCode);
}
/// <summary>
/// Opens/Creates the database and starts writing a new session to it.
/// </summary>
/// <exception cref="IncompatibleDatabaseException">The database version is not compatible with this
/// version of the AnalyticsSessionWriter.</exception>
public UsageDataSessionWriter(string databaseFileName, Func <Guid> guidProvider = null)
{
SQLiteConnectionStringBuilder conn = new SQLiteConnectionStringBuilder();
conn.DataSource = databaseFileName;
connection = new SQLiteConnection(conn.ConnectionString);
connection.Open();
try {
InitializeTables(guidProvider ?? Guid.NewGuid);
StartSession();
} catch {
connection.Close();
throw;
}
timer = new Timer(OnTimer, 0, TimeSpan.FromSeconds(60), TimeSpan.FromSeconds(60));
}
private static SQLiteConnection OpenConnection()
{
var sb = new SQLiteConnectionStringBuilder
{
DataSource = ":memory:"
};
var connection = new SQLiteConnection(sb.ToString());
connection.Open();
using (var cmd = connection.CreateCommand())
{
cmd.CommandText = "CREATE TABLE t (c INTEGER);";
cmd.ExecuteNonQuery();
}
return(connection);
}
/// <summary>
/// 使用数据文件路径和数据库连接密码初始化SQLiteHelper,
/// 没有数据库文件的时候将创建数据库文件
/// </summary>
/// <param name="filePath">数据库文件路径</param>
/// <param name="password">数据库连接密码</param>
public SQLiteHelper(string filePath, string password, params string[] commands)
{
SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
connstr.DataSource = filePath;
connstr.Password = password;
this.connStr = connstr.ToString();
if (!File.Exists(filePath))
{
SQLiteConnection.CreateFile(filePath);
if (commands != null)
{
foreach (var sql in commands)
{
this.ExecuteNonQuery(sql);
}
}
}
}
/// <summary>
/// 根据ID获取账号下的最近一条数据
/// </summary>
/// <returns></returns>
public Order GetMaxOrder(string account)
{
Order model = null;
SQLiteConnectionStringBuilder sb = new SQLiteConnectionStringBuilder();
sb.DataSource = DataSource;
using (IDbConnection con = new SQLiteConnection(sb.ToString()))
{
con.Open();
string sql = "select * from 'order' where account='" + account + "' ORDER BY ID DESC LIMIT 1 ";
List <Order> list = Dapper.SqlMapper.Query <Order>(con, sql).ToList();
if (list != null && list.Count == 1)
{
model = list.Single();
}
con.Close();
}
return(model);
}
private IDbConnection GetConn()
{
try
{
SQLiteConnectionStringBuilder sb = new SQLiteConnectionStringBuilder();
if (!Directory.Exists(dbFolder))
{
Directory.CreateDirectory(dbFolder);
}
sb.DataSource = string.Format(@"{0}\{1}", dbFolder, dbName);
IDbConnection con = new SQLiteConnection(sb.ToString());
return(con);
}
catch (
Exception ex)
{
return(null);
}
}
public void CleanUpDateStore()
{
var sqlConnectionSb = new SQLiteConnectionStringBuilder {
DataSource = "reserve.db"
};
using (var cn = new SQLiteConnection(sqlConnectionSb.ToString()))
{
cn.Open();
using (var cmd = new SQLiteCommand(cn))
{
// テーブル作成
cmd.CommandText = "DROP TABLE IF EXISTS reserve";
cmd.ExecuteNonQuery();
}
}
}
private void btn_lockdb_Click(object sender, EventArgs e)
{
if (MyConfigs.IsDbPassActive)
{
string constr = new SQLiteConnectionStringBuilder()
{
DataSource = @"Contacts.db",
ForeignKeys = true,
Version = 3
}
.ConnectionString;
Clipboard.SetText(constr);
MessageBox.Show(constr);
SQLiteConnection con = new SQLiteConnection(constr);
con.Open();
con.ChangePassword(MyConfigs.DbPassword);
con.Close();
}
}
/// <summary>
/// Creates a new instance
/// </summary>
public Database(string fileName)
{
lockNonQuery = new object();
DatabaseFileName = new FileInfo(fileName).FullName;
// if now exists, creates one (can be done in the ConnectionString)
if (!File.Exists(DatabaseFileName))
{
SQLiteConnection.CreateFile(DatabaseFileName);
}
// uses builder to avoid escape issues
SQLiteConnectionStringBuilder sb = new SQLiteConnectionStringBuilder
{
DataSource = DatabaseFileName,
Version = 3
};
cnnString = sb.ToString();
}
static public void Connect(string DataBasePath)
{
var builder = new SQLiteConnectionStringBuilder();
builder.DataSource = DataBasePath;
builder.FailIfMissing = false;
builder.JournalMode = SQLiteJournalModeEnum.Persist;
if (connection == null)
{
connection = new SQLiteConnection();
}
connection.ConnectionString = builder.ConnectionString;
if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
InitTables();
}
static void initDB()
{
try
{
SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
connstr.DataSource = MainStaticData.datasource;
MainStaticData.conn.ConnectionString = connstr.ToString();
MainStaticData.conn.Open();
db_ConfigDao.UpdateSchema();
int r = db_ConfigDao.addIfNoExist("http://localhost:8080/zxweb/", "localhost", "8080", "192.168.1.123", MainStaticData.ConnTypeData[0]);//不存在则添加
object[] configs = db_ConfigDao.getConfig();
MainStaticData.ServerAddr = configs[0].ToString();
MainStaticData.ServerIP = configs[1].ToString();
MainStaticData.ServerPort = configs[2].ToString();
MainStaticData.DeviceIP = configs[3].ToString();
MainStaticData.ConnType = configs[4].ToString();
MainStaticData.FtpIP = configs[5].ToString();
MainStaticData.FtpPort = configs[6].ToString();
MainStaticData.FtpUserName = configs[7].ToString();
MainStaticData.FtpPwd = configs[8].ToString();
if (null != configs[9])
{
MainStaticData.cbNoLogin = Boolean.Parse(configs[9].ToString());
}
MainStaticData.isNetwork = MainStaticData.ConnType == MainStaticData.ConnTypeData[0];
if (MainStaticData.FtpIP != "" && MainStaticData.FtpUserName != null)
{
MainStaticData.ftpHelper = new FtpHelper(MainStaticData.FtpIP, "/", MainStaticData.FtpUserName, MainStaticData.FtpPwd);
}
}
catch (Exception ex)
{
LogHelper.WriteError(typeof(Program), ex);
}
}
internal static void Create(string connectionString, IDictionary <string, string> metadata)
{
var csb = new SQLiteConnectionStringBuilder(connectionString);
if (File.Exists(csb.DataSource))
{
File.Delete(csb.DataSource);
}
using (var cn = new SQLiteConnection(connectionString))
{
cn.Open();
using (var cmd = cn.CreateCommand())
{
cmd.CommandText =
"CREATE TABLE metadata (name text, value text);" +
"CREATE TABLE tiles (zoom_level integer, tile_column integer, tile_row integer, tile_data blob);" +
"CREATE UNIQUE INDEX idx_tiles ON tiles (zoom_level, tile_colum, tile_row);";
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO metadata VALUES (?, ?);";
var pName = new SQLiteParameter("PName", DbType.String); cmd.Parameters.Add(pName);
var pValue = new SQLiteParameter("PValue", DbType.String); cmd.Parameters.Add(pValue);
if (metadata == null || metadata.Count == 0)
{
metadata = new Dictionary <string, string>();
}
if (!metadata.ContainsKey("bounds"))
{
metadata.Add("bounds", "-180,-85,180,85");
}
foreach (var kvp in metadata)
{
pName.Value = kvp.Key;
pValue.Value = kvp.Value;
cmd.ExecuteNonQuery();
}
}
}
}
static void RemoveSearchEngine()
{
_stat.Status = "Removing Conduit Search Engine";
Console.WriteLine("Connecting to Web Data SQLite database");
using (SQLiteConnection conn = new SQLiteConnection())
{
SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder();
builder.DataSource = Path.Combine(DefaultProfile, "Web Data");
conn.ConnectionString = builder.ConnectionString;
conn.Open();
using (SQLiteCommand comm = new SQLiteCommand())
{
comm.Connection = conn;
Console.WriteLine("Deleting Conduit from keywords table");
comm.CommandText = "delete from keywords where short_name='Conduit'";
comm.ExecuteNonQuery();
try
{
Console.WriteLine("Deleting Conduit from keywords_backup table");
comm.CommandText = "delete from keywords_backup where short_name='Conduit'";
comm.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine("Failed deleting stuff in keywords_backup table");
}
Console.WriteLine("Updating default search engine");
comm.CommandText = "update meta set value='2' where key='Default Search Provider ID'";
comm.ExecuteNonQuery();
Console.WriteLine("Updating default search engine backup");
comm.CommandText = "update meta set value='2' where key='Default Search Provider ID Backup'";
comm.ExecuteNonQuery();
}
}
}
private void InitializeDatabase()
{
var connectionString = new SQLiteConnectionStringBuilder()
{
DataSource = $"EFCoreRepositoryTests.{Thread.CurrentThread.ManagedThreadId}.db"
}.ConnectionString;
var options = new DbContextOptionsBuilder <PatientContext>()?.UseSqlite(connectionString).Options;
using (var db = new PatientContext(options))
{
var foo = new Patient
{
Name = "Foo",
Sex = Gender.Male,
DateAdded = new DateTime(2012, 1, 1),
AdmitDate = new DateTime(2012, 1, 2)
};
var bar = new Patient
{
Name = "Bar",
Sex = Gender.Female,
DateAdded = new DateTime(2012, 1, 3),
AdmitDate = new DateTime(2012, 1, 2)
};
var doh = new Patient
{
Name = "Doh",
Sex = Gender.Female,
DateAdded = new DateTime(2012, 2, 3),
AdmitDate = new DateTime(2012, 2, 2)
};
db.Patients.Add(foo);
db.Patients.Add(bar);
db.Patients.Add(doh);
db.SaveChanges();
}
}
static void RefreshDB()
{
string sql = $"select ts Timestamp, Call, Band, Mode from DXLOG where contestnr = {contestnr}";
var csb = new SQLiteConnectionStringBuilder();
string dir = @"C:\Users\tomandels\Nextcloud\Radio\N1MM Logger+\Databases";
string fn = "m0lte.s3db";
csb.DataSource = Path.Combine(dir, fn);
csb.ReadOnly = true;
List <LogEntry> dbResults;
using (var conn = new SQLiteConnection(csb.ToString()))
{
conn.Open();
dbResults = conn.Query <LogEntry>(sql).ToList();
}
foreach (LogEntry dbrow in dbResults)
{
var matches = GetMatches(dbrow.Call);
if (matches.Count != 1)
{
continue;
}
var m = matches.Single();
lock (BandSquares)
{
if (!BandSquares.Any(mem => mem.Band == dbrow.Band && mem.Prefix == m.PrimaryPrefix))
{
BandSquares.Add(new BandSquare {
Band = dbrow.Band, Country = m.CountryName, Prefix = m.PrimaryPrefix
});
}
}
}
}
public void Start()
{
var filename = Path.Combine(System.AppContext.BaseDirectory, "users.db");
var create = false;
if (!File.Exists(filename))
{
SQLiteConnection.CreateFile(filename);
create = true;
}
var connectionString = new SQLiteConnectionStringBuilder()
{
DataSource = filename,
Version = 3
};
_connection = new SQLiteConnection(connectionString.ToString());
_connection.Open();
if (create)
{
new SQLiteCommand(@"
CREATE TABLE `users` (
`Id` INTEGER PRIMARY KEY AUTOINCREMENT,
`Username` TEXT,
`Password` TEXT,
`Group` TEXT
);"
, _connection).ExecuteNonQuery();
}
LoadGroups();
ConnectionEvents.OnJoin.Add(OnJoin);
ConnectionEvents.OnDisconnect.Add(OnLeave);
_gameServer.RegisterCommand("register", OnRegister);
_gameServer.RegisterCommand("login", OnLogin);
_gameServer.RegisterCommand("setgroup", OnSetGroup);
}
//update table
public static bool Update(string filename, string tableName, Dictionary <string, string> data, string where)
{
string vals = "";
bool returnCode = true;
if (data.Count >= 1)
{
// ReSharper disable once LoopCanBeConvertedToQuery
foreach (KeyValuePair <String, String> val in data)
{
vals += String.Format(" {0} = '{1}',", val.Key.ToString(CultureInfo.InvariantCulture), val.Value.ToString(CultureInfo.InvariantCulture));
}
vals = vals.Substring(0, vals.Length - 1);
}
string myUpdateQuery = string.Format("UPDATE {0} SET {1} WHERE {2};", tableName, vals, where);
try
{
var connStr = new SQLiteConnectionStringBuilder()
{
DataSource = filename
};
using (SQLiteConnection conn = new SQLiteConnection(connStr.ToString()))
{
conn.Open();
SQLiteCommand cmd = new SQLiteCommand(conn)
{
CommandText = myUpdateQuery
};
cmd.ExecuteNonQuery();
conn.Close();
}
}
catch (Exception)
{
//System.Console.WriteLine(fail.Message);
returnCode = false;
}
return(returnCode);
}
public MainWindow()
{
InitializeComponent();
var connectionString = new SQLiteConnectionStringBuilder {
DataSource = dataSource_
}.ToString();
var dataTable = new DataTable();
var sql =
Select(SHOHIN_ID, SHOHIN_MEI, HANBAI_TANKA)
.From(T_SHOHIN)
.Where(GreaterThan(HANBAI_TANKA,
Select(Avg(HANBAI_TANKA))
.From(T_SHOHIN)
.EndWithBrackets()))
.End();
try
{
using (var connection = new SQLiteConnection(connectionString))
using (var command = new SQLiteCommand(sql, connection))
using (var adapter = new SQLiteDataAdapter(command))
{
connection.Open();
AddParams(command,
("@id", DbType.String, "0008"),
("@name", DbType.String, "ボールペン"),
("@bunrui", DbType.String, "キッチン用品"),
("@htanka", DbType.Int32, 1000),
("@stanka", DbType.Int32, 500),
("@torokubi", DbType.Date, null)
);
adapter.Fill(dataTable);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
data_grid.ItemsSource = dataTable.DefaultView;
text_block.Text = sql;
}
private void toolStripMenuItem10_Click(object sender, EventArgs e)
{
label1.Visible = false;
label2.Visible = false;
string dbconstring3 = GetExecutingDirectoryName();
SQLiteConnectionStringBuilder myBuilder3 = new SQLiteConnectionStringBuilder()
{
DataSource = @"" + dbconstring3 + @"\\proje_db\\eys_db.db"
};
SQLiteConnection con7 = new SQLiteConnection(myBuilder3.ConnectionString);
con7.Open();
string query9 = "SELECT Urun_Adi as 'Ürün Adı', Tarih, Eski_Fiyat as 'Eski Fİyatı', Yeni_Fiyat as 'Yeni Fiyatı' from FIYAT_DEGISIKLIK";
SQLiteCommand cmd9 = new SQLiteCommand(query9, con7);
SQLiteDataAdapter adapter9 = new SQLiteDataAdapter(cmd9);
DataTable dt9 = new DataTable();
adapter9.Fill(dt9);
dataGridViewFiyatDegisikligi.AutoResizeColumns();
dataGridViewFiyatDegisikligi.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
dataGridViewFiyatDegisikligi.DataSource = dt9;
con7.Close();
dataGridViewAsgariStok.Visible = false;
dataGridViewHaftalikSatislar.Visible = false;
dataGridViewTumSatislar.Visible = false;
dataGridViewTumMusteriler.Visible = false;
dataGridViewTumUrunler.Visible = false;
dataGridViewKrediKarti.Visible = false;
dataGridViewHavale.Visible = false;
dataGridViewNakit.Visible = false;
dataGridTumSaticilar.Visible = false;
dataGridViewFiyatDegisikligi.Visible = true;
dataGridViewAylikSatislar.Visible = false;
dataGridViewYillikSatislar.Visible = false;
dataGridViewİade.Visible = false;
dataGridViewAlislar.Visible = false;
}
private string GenerateConnectionString()
{
string DBName = "KDBase.db";
string DBFullPath = Path.Combine(KCore.FS.PathDB, DBName);
SQLiteConnectionStringBuilder csb = new SQLiteConnectionStringBuilder();
csb.DataSource = DBFullPath;
if (!File.Exists(DBFullPath))
{
SQLiteConnection.CreateFile(DBFullPath);
using (SQLiteConnection conn = new SQLiteConnection(csb.ToString()))
{
conn.Open();
SQLiteCommand cmd = conn.CreateCommand();
cmd.CommandText = @"BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS `tbl_positions` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`nam` TEXT NOT NULL UNIQUE
);
CREATE TABLE IF NOT EXISTS `tbl_employees` (
`ID` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`FIO` TEXT NOT NULL,
`PositionID` INTEGER NOT NULL,
`DB` TEXT,
`DE` TEXT,
`Rem` TEXT,
FOREIGN KEY(`PositionID`) REFERENCES `tbl_positions`(`id`)
);
CREATE TABLE IF NOT EXISTS `tbl_admins` (
`id` INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
`login` TEXT NOT NULL UNIQUE,
`pass` TEXT NOT NULL
);
COMMIT;";
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
conn.Close();
}
}
return(csb.ToString());
}
public void JournalMode(string text, SQLiteJournalModeEnum expected)
{
SQLiteConnectionStringBuilder csb = new SQLiteConnectionStringBuilder { ConnectionString = "Journal Mode=" + text };
Assert.AreEqual(expected, csb.JournalMode);
}
public void DefaultTimeout(string text, int expected)
{
SQLiteConnectionStringBuilder csb = new SQLiteConnectionStringBuilder { ConnectionString = "Default Timeout=" + text };
Assert.AreEqual(expected, csb.DefaultTimeout);
}
public void FailIfMissing(string text, bool expected)
{
SQLiteConnectionStringBuilder csb = new SQLiteConnectionStringBuilder { ConnectionString = "FailIfMissing=" + text };
Assert.AreEqual(expected, csb.FailIfMissing);
}
public void ReadOnly(string text, bool expected)
{
SQLiteConnectionStringBuilder csb = new SQLiteConnectionStringBuilder { ConnectionString = "Read Only=" + text };
Assert.AreEqual(expected, csb.ReadOnly);
}
public void ForeignKeys(string text, bool expected)
{
SQLiteConnectionStringBuilder csb = new SQLiteConnectionStringBuilder { ConnectionString = "Foreign Keys=" + text };
Assert.AreEqual(expected, csb.ForeignKeys);
}
public void MmapSize(string text, long expected)
{
SQLiteConnectionStringBuilder csb = new SQLiteConnectionStringBuilder { ConnectionString = "_MmapSize=" + text };
Assert.AreEqual(expected, csb.MmapSize);
}
public void CacheSize(string text, int expected)
{
SQLiteConnectionStringBuilder csb = new SQLiteConnectionStringBuilder { ConnectionString = "Cache Size=" + text };
Assert.AreEqual(expected, csb.CacheSize);
}
public void TempStore(string text, SQLiteTemporaryStore expected)
{
SQLiteConnectionStringBuilder csb = new SQLiteConnectionStringBuilder { ConnectionString = "_TempStore=" + text };
Assert.AreEqual(expected, csb.TempStore);
}
public void Synchronous(string text, SynchronizationModes expected)
{
SQLiteConnectionStringBuilder csb = new SQLiteConnectionStringBuilder { ConnectionString = "Synchronous=" + text };
Assert.AreEqual(expected, csb.SyncMode);
}
public void DataSource(string keyName, string dataSource)
{
SQLiteConnectionStringBuilder csb = new SQLiteConnectionStringBuilder { ConnectionString = keyName + "=\"" + dataSource + "\";Read Only=false" };
Assert.AreEqual(dataSource, csb.DataSource);
}
