public CardRefundPayService()
{
string localDriver = "C";
if (Directory.Exists("D:\\"))
{
localDriver = "D";
}
string dbPath = localDriver + ":\\DataBase\\top4pos.db3";
if (!File.Exists(dbPath))
{
//创建数据库
SQLiteConnection.CreateFile(dbPath);
}
SQLiteConnectionStringBuilder connectionString = new SQLiteConnectionStringBuilder
{
DataSource = dbPath,
Password = "******",
Pooling = true,
FailIfMissing = false
};
m_SQLiteHelper = new SQLiteHelper(connectionString.ToString());
//判断表是否存在
string strSql = "SELECT COUNT(*) FROM sqlite_master where type='table' and name='CardRefundPay'";
if (!m_SQLiteHelper.Exists(strSql))
{
string createTableSql = @"create table CardRefundPay(StoreValueID INTEGER primary key autoincrement,
CardNo varchar(50), ShopID varchar(36),
TradePayNo varchar(30),
PayAmount decimal(12, 4), EmployeeNo varchar(20),
DeviceNo varchar(16), IsFixed Integer,
CreateTime datetime, LastTime datetime);";
m_SQLiteHelper.ExecuteSql(createTableSql);
}
}
protected SqlHelper(String tableName)
{
Directory.CreateDirectory(Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), "SolderHelper"));
String databaseName = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData), "SolderHelper", "SolderHelper.db");
try
{
if (!File.Exists(databaseName))
{
if (IsUnix())
{
SqliteConnection.CreateFile(databaseName);
}
else
{
SQLiteConnection.CreateFile(databaseName);
}
}
}
catch (Exception)
{
// ignored
}
if (IsUnix())
{
SqliteConnectionStringBuilder c = new SqliteConnectionStringBuilder { DataSource = databaseName };
ConnectionString = c.ConnectionString;
}
else
{
SQLiteConnectionStringBuilder c = new SQLiteConnectionStringBuilder { DataSource = databaseName };
ConnectionString = c.ConnectionString;
}
TableName = tableName;
}
public static List <T> GetAllObject <T>() where T : BaseObject
{
List <T> list = new List <T>();
string tableName = typeof(T).Name;//.Substring(typeof(T).Name.LastIndexOf('.'));
if (commonConnection == null)
{
commonConnection = new SQLiteConnection();
SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = DbFileName;
commonConnection.ConnectionString = connstr.ToString();
}
try
{
commonConnection.Open();
SQLiteCommand cmd = new SQLiteCommand(commonConnection);
cmd.CommandText = "Select * from " + tableName + "";
SQLiteDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
T obj = Activator.CreateInstance <T>();
obj.SetProperties(sdr);
list.Add(obj);
}
commonConnection.Close();
}
catch (Exception e)
{
commonConnection.Close();
throw e;
}
return(list);
}
/// <summary>
/// Initializes a new instance of the SQLiteRepository class.
/// </summary>
/// <param name="connectionString">The connection string to use when connecting to the database.</param>
public SQLiteRepository(string connectionString)
{
connectionString = (connectionString ?? string.Empty).Trim();
if (string.IsNullOrEmpty(connectionString))
{
connectionString = "data source=|DataDirectory|BlueCollar.sqlite;synchronous=Off;journal mode=Off;version=3";
}
SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder(connectionString);
builder.DataSource = BlueCollarSection.Section.ResolvePath(builder.DataSource);
builder.DateTimeKind = DateTimeKind.Utc;
EnsureDatabase(builder.DataSource);
this.ConnectionString = builder.ConnectionString;
try
{
this.defaultIsolationLevel = builder.DefaultIsolationLevel;
}
catch (NullReferenceException)
{
this.defaultIsolationLevel = IsolationLevel.Serializable;
}
this.connection = new SQLiteConnection(this.ConnectionString);
this.connection.Open();
}
/// <summary>
/// New Sqlite.
/// </summary>
public Database()
{
try
{
if (!File.Exists($"{Core.Setting.Directory.DataDirectory}\\{Core.Setting.Server.Feature.Sqlite.Name}.db"))
SQLiteConnection.CreateFile($"{Core.Setting.Directory.DataDirectory}\\{Core.Setting.Server.Feature.Sqlite.Name}.db");
}
catch (Exception ex) { ex.CatchError(); }
SQLiteConnectionStringBuilder Builder = new SQLiteConnectionStringBuilder();
Builder.DataSource = $"{Core.Setting.Directory.DataDirectory}\\{Core.Setting.Server.Feature.Sqlite.Name}.db";
Builder.Version = 3;
Builder.Pooling = true;
Builder.SyncMode = SynchronizationModes.Full;
ConnectionString = Builder.ConnectionString;
using (SQLiteDataReader Result = EnqueueTransaction(
$@"create table if not exists Player_Info (ID integer primary key, Name text, Gamejolt_ID integer default -1, IP_Address text, Last_Activity integer);
create table if not exists Channel_Info (ID integer primary key, Name text, Motd text, Max_Limit integer default -1);
create table if not exists Global_BlackList (ID integer primary key, Player_ID integer, Channel_ID integer default -1, Reason text default 'No Reason.', StartTime integer, Duration integer default -1);
create table if not exists Global_IPBlackList (ID integer primary key, Player_ID integer, Channel_ID integer default -1, Reason text default 'No Reason.', StartTime integer, Duration integer default -1);
create table if not exists Global_MuteList (ID integer primary key, Player_ID integer default -1, Channel_ID integer default -1, Mute_ID integer, Reason text default 'No Reason.', StartTime integer, Duration integer default -1);
create table if not exists Global_OperatorList (ID integer primary key, Player_ID integer, Channel_ID default -1, Reason text default 'No Reason.', Permission integer default {(int)Player.OperatorTypes.Player});
create table if not exists Global_WhiteList (ID integer primary key, Player_ID integer, Channel_ID default -1, Reason text default 'No Reason.');
create table if not exists Global_ChatHistory (ID integer primary key, Player_ID integer, Channel_ID default -1, Message text, TimeStamp integer);
create table if not exists Global_TradeHistory (ID integer primary key, Channel_ID integer default -1, Host_ID integer, Host_Pokemon text, Client_ID integer, Client_Pokemon text, TimeStamp integer);
create table if not exists Global_WorldList (ID integer primary key, Channel_ID integer, Season integer default {(int)World.SeasonType.DefaultSeason}, Weather integer default {(int)World.WeatherType.DefaultWeather}, Time_Offset integer default 0, DoDayCycle integer default 1);
create table if not exists Player_WorldList (ID integer primary key, Player_ID integer, Channel_ID integer default -1, Season integer default {(int)World.SeasonType.DefaultSeason}, Weather integer default {(int)World.WeatherType.DefaultWeather}, Time_Offset integer default 0, DoDayCycle integer Default 1);"))
Core.Logger.Log("Database initialized.", Logger.LogTypes.Info);
}
public override void CreateDatabase(string constr)
{
// SQLite молча пересоздаст файл если такой уже есть.
//
var csb = new SQLiteConnectionStringBuilder(constr);
if (!File.Exists(csb.DataSource)
|| MessageBox.Show(
string.Format(Resources.FileExistedMessage, Path.GetFileName(csb.DataSource)),
@"SQLite",
MessageBoxButtons.YesNo,
MessageBoxIcon.Question,
MessageBoxDefaultButton.Button2) == DialogResult.Yes)
{
// Create file
SQLiteConnection.CreateFile(csb.DataSource);
// Change default page size
using (var con = new SQLiteConnection(constr))
using (var cmd = con.CreateCommand())
{
con.Open();
cmd.CommandText = @"pragma page_size=" + PageSize + @"; VACUUM;";
cmd.ExecuteNonQuery();
}
}
}
public static SQLiteBlockchainStorage Open(string storageLocation)
{
if (!Directory.Exists(storageLocation))
{
Directory.CreateDirectory(storageLocation);
}
SQLiteConnectionStringBuilder connStrBuilder = new SQLiteConnectionStringBuilder();
connStrBuilder.DataSource = Path.Combine(storageLocation, BlockchainFilename);
connStrBuilder.JournalMode = SQLiteJournalModeEnum.Wal;
string connStr = connStrBuilder.ConnectionString;
SQLiteConnection conn = new SQLiteConnection(connStr);
conn.Open();
ApplySchemaSettings(conn, "main");
//todo: SQLite in WAL mode does not guarantee atomic commits across all attached databases
AttachDatabase(conn, "blocks", Path.Combine(storageLocation, BlocksFilename));
AttachDatabase(conn, "utxo", Path.Combine(storageLocation, UtxoFilename));
ApplySchemaSettings(conn, "blocks");
ApplySchemaSettings(conn, "utxo");
//todo: handle exceptions, describe ant test them
CheckSchema(conn);
return new SQLiteBlockchainStorage(storageLocation, conn);
}
protected virtual bool ConnectToDB(string dbPath)
{
SQLiteConnectionStringBuilder connectionstr = new SQLiteConnectionStringBuilder();
connectionstr.PageSize = 4096;
connectionstr.CacheSize = 4096;
connectionstr.SyncMode = SynchronizationModes.Normal;
connectionstr.DataSource = dbPath;
connectionstr.JournalMode = SQLiteJournalModeEnum.Delete;
connection = new SQLiteConnection(connectionstr.ConnectionString);
int retries = 0;
bool connected = false;
while (!connected && retries < MAX_RETRIES)
{
try
{
connection.Open();
connected = true;
}
catch (Exception e)
{
Logger.ReportException("Error connecting to database "+dbPath+"! Will retry " + MAX_RETRIES + " times.", e);
retries++;
Thread.Sleep(250);
}
}
return connected;
}
private static void SetPathToParentDirectoryOfDatabaseFile()
{
var builder =
new SQLiteConnectionStringBuilder(WebConfigurationManager.ConnectionStrings["SQLite"].ConnectionString);
_pathToDatabaseFile = Consts.Consts.GetPath(builder.DataSource);
_pathToDatabase = Directory.GetParent(_pathToDatabaseFile).FullName + @"\";
}
public void TestInitialise()
{
_OriginalClassFactory = Factory.TakeSnapshot();
_RuntimeEnvironment = TestUtilities.CreateMockSingleton<IRuntimeEnvironment>();
_ConfigurationStorage = TestUtilities.CreateMockSingleton<IConfigurationStorage>();
_Configuration = new Configuration();
_ConfigurationStorage.Setup(s => s.Load()).Returns(_Configuration);
_CreateDatabaseFileName = Path.Combine(TestContext.TestDeploymentDir, "CreatedDatabase.sqb");
if(File.Exists(_CreateDatabaseFileName)) File.Delete(_CreateDatabaseFileName);
_EmptyDatabaseFileName = Path.Combine(TestContext.TestDeploymentDir, "TestCopyBaseStation.sqb");
File.Copy(Path.Combine(TestContext.TestDeploymentDir, "BaseStation.sqb"), _EmptyDatabaseFileName, true);
_Database = Factory.Singleton.Resolve<IBaseStationDatabase>();
_Database.FileName = _EmptyDatabaseFileName;
_Provider = new Mock<IBaseStationDatabaseProvider>() { DefaultValue = DefaultValue.Mock }.SetupAllProperties();
_Database.Provider = _Provider.Object;
_Provider.Setup(p => p.UtcNow).Returns(DateTime.UtcNow);
_ConnectionStringBuilder = new SQLiteConnectionStringBuilder() { DataSource = _EmptyDatabaseFileName };
_Criteria = new SearchBaseStationCriteria() {
FromDate = DateTime.MinValue,
ToDate = DateTime.MaxValue,
};
_FileNameChangingEvent = new EventRecorder<EventArgs>();
_FileNameChangedEvent = new EventRecorder<EventArgs>();
}
/// <summary>
/// Creates a new SQLite profiling data provider and opens or creates a new database stored in a file.
/// </summary>
public ProfilingDataSQLiteWriter(string fileName, bool profileUnitTests, string[] unitTestNames)
{
if (File.Exists(fileName))
throw new IOException("File already exists!");
SQLiteConnectionStringBuilder conn = new SQLiteConnectionStringBuilder();
conn.Add("Data Source", fileName);
conn.Add("New", true);
// Disable protecting the database on crashes - it's a new database,
// it may go corrupt if we crash during DB creation. Disabling journalling
// makes Inserts faster.
conn.Add("Journal Mode", "OFF");
conn.Add("Synchronous", "OFF");
this.connection = new SQLiteConnection(conn.ConnectionString);
this.connection.Open();
InitializeTables();
File.SetAttributes(fileName, FileAttributes.Compressed);
this.profileUnitTests = profileUnitTests;
this.unitTestNames = unitTestNames;
if (profileUnitTests && unitTestNames == null)
throw new InvalidOperationException("Please add unit tests to filter!");
}
public void Migrate()
{
hash = SHA256.Create();
System.Data.SQLite.SQLiteConnectionStringBuilder cnstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
cnstr.DataSource = "Transfercontext.sdb";
conn = new System.Data.SQLite.SQLiteConnection(cnstr.ConnectionString);
transferContext = new TransferContext(conn, true);
sourceClient = new ImapClient();
destinationClient = new ImapClient();
sourceClient.Connect(SourceServer.Server, SourceServer.Port, SourceServer.SSL);
Console.WriteLine("Source server connected");
destinationClient.Connect(DestinationServer.Server, DestinationServer.Port, DestinationServer.SSL);
Console.WriteLine("Destination server connected");
sourceClient.Authenticate(SourceServer.Username, SourceServer.Password);
Console.WriteLine("Source server authenticated");
destinationClient.Authenticate(DestinationServer.Username, DestinationServer.Password);
Console.WriteLine("Destination server authenticated");
foreach (var ns in sourceClient.PersonalNamespaces)
{
CopyNamespace(ns);
}
}
private static PhpResource Open(string filename, int mode, PhpReference error, bool persistent)
{
if (persistent) PhpException.FunctionNotSupported(PhpError.Notice);
SQLiteConnectionStringBuilder csb = new SQLiteConnectionStringBuilder();
csb.DataSource = filename;
csb.Version = 3;
try
{
PhpSQLiteDbConnection connection = new PhpSQLiteDbConnection(csb.ConnectionString);
if (error != null)
{
error.Value = null;
}
return connection;
}
catch (Exception ex)
{
if (error != null)
{
error.Value = ex.Message;
}
return null;
}
}
public SqLiteDatabaseProvider(SQLiteConnectionStringBuilder connectionStringBuilder)
: base(connectionStringBuilder)
{
Enforce.Argument(() => connectionStringBuilder);
this.connectionStringBuilder = connectionStringBuilder;
}
public SqLiteDatabaseProvider(string connectionString)
: base(connectionString)
{
Enforce.Argument(() => connectionString);
connectionStringBuilder = new SQLiteConnectionStringBuilder(connectionString);
}
public DatabaseCreator(string filePath)
{
SQLiteConnection.CreateFile(filePath);
var csb = new SQLiteConnectionStringBuilder();
csb.DataSource = filePath;
_connectionString = csb.ConnectionString;
}
public bool Connect()
{
Debug.Log("DataBase connect: " + Predefine.DatabaseName);
//Debug.Log("sqlite3 version: " + SQLite3.LibVersionNumber());
if (_connection != null)
{
Debug.Log("dataBase has connected, needn't connect again");
return(true);
}
#if !RUNINSERVER
var dbPath = "";
Debug.Log("DataBase file path: " + dbPath);
_connection = new SQLiteConnection(Key, dbPath, SQLiteOpenFlags.ReadOnly);
#else
#if DEBUG
var dbPath = "D:/Work/Unity/Project/Client/p25/Assets/StreamingAssets/cfg";
#else
var dbPath = "./cfg";
#endif
Debug.Log("DataBase file path: " + dbPath);
_connection = new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = dbPath;
//connstr.
_connection.ConnectionString = connstr.ToString();
_connection.Open();
#endif
return(true);
}
/// <summary>
/// コンストラクタ
/// </summary>
/// <param name="dbname">接続するDB名</param>
public SQLiteDB(String dbname)
{
DBName = dbname;
SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder();
builder.DataSource = DBName;
Conn = new SQLiteConnection(builder.ConnectionString);
}
public static T GetObject <T>(string key, object value) where T : BaseObject
{
T obj = Activator.CreateInstance <T>();
string tableName = obj.GetType().Name;//.Substring(obj.GetType().Name.LastIndexOf('.'));
if (commonConnection == null)
{
commonConnection = new SQLiteConnection();
SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = DbFileName;
commonConnection.ConnectionString = connstr.ToString();
}
try
{
commonConnection.Open();
SQLiteCommand cmd = new SQLiteCommand(commonConnection);
cmd.CommandText = "Select * from " + tableName + " where " + key + "='" + value.ToString() + "'";
SQLiteDataReader sdr = cmd.ExecuteReader();
while (sdr.Read())
{
obj.SetProperties(sdr);
}
commonConnection.Close();
}
catch//(Exception ex)
{
commonConnection.Close();
return(null);
}
return(obj);
}
public DatabaseReader(string databaseFilename)
{
FileAttributes attr = File.GetAttributes(databaseFilename);
if (attr.ToString() == "")
{
_databaseConnection = null;
}
SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder();
builder.DataSource = databaseFilename;
builder.ReadOnly = true;
_databaseConnection = new SQLiteConnection(builder.ConnectionString);
try
{
_databaseConnection.Open();
}
catch (SQLiteException ex)
{
if (ex.ErrorCode == SQLiteErrorCode.NotADatabase)
{
throw new UnreadableDatabaseFileException(databaseFilename, ex);
}
else
{
throw;
}
}
}
static partial void ApplyRestrictedCredentials(SQLiteConnectionStringBuilder b, bool admin = false)
{
//b.Add("InternalHost", "invalidhost");
b.Add("InternalUser", "user3");
b.Password = "******";
}
public static void UpdataObjectById <T>(T newInstance) where T : BaseObject
{
SQLiteTransaction transaction = null;
string tableName = typeof(T).Name;
try
{
if (commonConnection == null)
{
commonConnection = new SQLiteConnection();
SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = DbFileName;
commonConnection.ConnectionString = connstr.ToString();
}
commonConnection.Open();
transaction = commonConnection.BeginTransaction();
SQLiteCommand cmd = new SQLiteCommand(commonConnection);
cmd.Transaction = transaction;
cmd.CommandText = "Delete from " + tableName + " where id='" + newInstance.Id.ToString() + "'";;
cmd.ExecuteNonQuery();
cmd.CommandText = BuildInsertCommand(tableName, newInstance);;
cmd.ExecuteNonQuery();
transaction.Commit();
commonConnection.Close();
}
catch (Exception e)
{
if (transaction != null)
{
transaction.Rollback();
}
commonConnection.Close();
throw e;
}
}
static SQLiteConnection Connection(string filePath)
{
if (filePath.IsNullOrBlank()) throw new ArgumentNullException("filePath");
SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder() { DataSource = filePath };
SQLiteConnection connection = new SQLiteConnection(builder.ConnectionString, true);
return connection;
}
/// <summary>
/// Initializes a new instance of the MainViewModel class.
/// </summary>
public DBViewModel(string dbName, string dbPath)
{
this.dbName = dbName;
this.dbPath = dbPath;
try
{
SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder();
builder.DataSource = DBPath;
conn = new SQLiteConnection(builder.ConnectionString);
conn.Open();
var schema = conn.GetSchema("Tables");
foreach (System.Data.DataRow row in schema.Rows)
{
try
{
TableViewModel table = new TableViewModel(row.ItemArray[2].ToString(), row.ItemArray[6].ToString(), conn);
Tables.Add(table);
}
catch(Exception)
{
continue;
}
}
IsValid = true;
}
catch (Exception ex)
{
IsValid = false;
System.Windows.MessageBox.Show(ex.Message);
}
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="Engine">The string name, from the GetDatabaseEngine() method</param>
/// <param name="Host">The Database server IP Address</param>
/// <param name="Port">The Database server Port Number</param>
/// <param name="DatabaseName">The name of the database</param>
/// <param name="User">A username, with database privliages</param>
/// <param name="Pass">The password to the User</param>
public DatabaseDriver(string Engine, string Host, int Port, string DatabaseName, string User, string Pass)
{
// Set class variables, and create a new connection builder
this.DatabaseEngine = GetDatabaseEngine(Engine);
DbConnectionStringBuilder Builder;
if (this.DatabaseEngine == DatabaseEngine.Sqlite)
{
string FullPath = Path.Combine(MainForm.Root, DatabaseName + ".sqlite3");
IsNewDatabase = !File.Exists(FullPath) || new FileInfo(FullPath).Length == 0;
Builder = new SQLiteConnectionStringBuilder();
Builder.Add("Data Source", FullPath);
Connection = new SQLiteConnection(Builder.ConnectionString);
}
else if (this.DatabaseEngine == DatabaseEngine.Mysql)
{
IsNewDatabase = false;
Builder = new MySqlConnectionStringBuilder();
Builder.Add("Server", Host);
Builder.Add("Port", Port);
Builder.Add("User ID", User);
Builder.Add("Password", Pass);
Builder.Add("Database", DatabaseName);
Connection = new MySqlConnection(Builder.ConnectionString);
}
else
{
throw new Exception("Invalid Database type.");
}
}
public void TestQuery()
{
var builder = new SQLiteConnectionStringBuilder();
builder.DataSource = "test.db";
using (DbConnection connection = new SQLiteConnection(builder.ToString()))
{
connection.Open();
using (var cmd1 = connection.CreateCommand())
{
cmd1.CommandText = @"SELECT name FROM sqlite_master WHERE type='table' AND name='table_test';";
var reader = cmd1.ExecuteReader();
if (reader.Read())
{
var tableName = reader.GetString(0);
System.Diagnostics.Trace.WriteLine(String.Format("table name={0}", tableName));
}
else
{
using (var cmd2 = connection.CreateCommand())
{
cmd2.CommandText = @"Create Table 'table_test' (num Integer, str)";
cmd2.ExecuteNonQuery();
}
}
}
}
}
/// <summary>
/// Connects to db.
/// </summary>
public static async Task<IDbConnection> ConnectToDb(string dbPath, bool isReadOnly, bool enablePooling, int? cacheSize, ILogger logger)
{
if (string.IsNullOrEmpty(dbPath))
{
throw new ArgumentNullException("dbPath");
}
SQLiteConnection.SetMemoryStatus(false);
var connectionstr = new SQLiteConnectionStringBuilder
{
PageSize = 4096,
CacheSize = cacheSize ?? 2000,
SyncMode = SynchronizationModes.Normal,
DataSource = dbPath,
JournalMode = SQLiteJournalModeEnum.Wal,
// This is causing crashing under linux
Pooling = enablePooling && Environment.OSVersion.Platform == PlatformID.Win32NT,
ReadOnly = isReadOnly
};
var connectionString = connectionstr.ConnectionString;
if (!enablePooling)
{
logger.Info("Sqlite {0} opening {1}", SQLiteConnection.SQLiteVersion, connectionString);
}
var connection = new SQLiteConnection(connectionString);
await connection.OpenAsync().ConfigureAwait(false);
return connection;
}
//Set up the connection with the password
public static SQLiteConnection Connection(string dbname, string pwd)
{
string datasource = dbname;
SQLiteConnection.CreateFile(datasource);
//连接数据库
SQLiteConnection connection = new SQLiteConnection();
SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
connstr.Password = pwd;//设置密码，SQLite ADO.NET实现了数据库密码保护
//将连接的信息传递给connection
connection.ConnectionString = connstr.ToString();
if (connection == null)
{
connection = new SQLiteConnection();
connection.ConnectionString = connstr.ToString();
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Broken)
{
connection.Close();
connection.Open();
}
return connection;
}
/// <summary>
/// Constructor
/// </summary>
/// <param name="Engine">The string name, for the GetDatabaseEngine() method</param>
/// <param name="Host">The Database server IP Address</param>
/// <param name="Port">The Database server Port Number</param>
/// <param name="DatabaseName">The name of the database</param>
/// <param name="User">A username, with database privliages</param>
/// <param name="Pass">The password to the User</param>
public DatabaseDriver(string Engine, string Host, int Port, string DatabaseName, string User, string Pass)
{
// Set class variables, and create a new connection builder
this.DatabaseEngine = GetDatabaseEngine(Engine);
DbConnectionStringBuilder Builder;
// Establish the connection
if (this.DatabaseEngine == DatabaseEngine.Sqlite)
{
// Create the connection
Builder = new SQLiteConnectionStringBuilder();
Builder.Add("Data Source", Path.Combine(Program.RootPath, DatabaseName + ".sqlite3"));
Connection = new SQLiteConnection(Builder.ConnectionString);
}
else if (this.DatabaseEngine == DatabaseEngine.Mysql)
{
// Create the connection
Builder = new MySqlConnectionStringBuilder();
Builder.Add("Server", Host);
Builder.Add("Port", Port);
Builder.Add("User ID", User);
Builder.Add("Password", Pass);
Builder.Add("Database", DatabaseName);
Builder.Add("Convert Zero Datetime", "true");
Connection = new MySqlConnection(Builder.ConnectionString);
}
else
{
throw new Exception("Invalid Database type.");
}
}
public DBManager(String dataSource)
{
// build connection string
SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder();
builder.DataSource = dataSource;
connectionString = builder.ToString();
}
public DatabaseDriver()
{
this.DatabaseEngine = Config.GetDatabaseEngine();
DbConnectionStringBuilder Builder;
if (this.DatabaseEngine == DatabaseEngine.Sqlite)
{
Builder = new SQLiteConnectionStringBuilder();
string FullPath = Path.Combine(Utils.AssemblyPath, Config.GetType<string>("Database", "Database") + ".sqlite3");
IsNewDatabase = !File.Exists(FullPath) || new FileInfo(FullPath).Length == 0;
Builder.Add("Data Source", FullPath);
Connection = new SQLiteConnection(Builder.ConnectionString);
}
else if (this.DatabaseEngine == DatabaseEngine.Mysql)
{
Builder = new MySqlConnectionStringBuilder();
Builder.Add("Server", Config.GetType<string>("Database", "Hostname"));
Builder.Add("Port", Config.GetType<int>("Database", "Port"));
Builder.Add("User ID", Config.GetType<string>("Database", "Username"));
Builder.Add("Password", Config.GetType<string>("Database", "Password"));
Builder.Add("Database", Config.GetType<string>("Database", "Database"));
Connection = new MySqlConnection(Builder.ConnectionString);
}
else
{
throw new Exception("Invalid Database type.");
}
}
public SQLiteImageCache(string dbPath)
{
if (sqliteAssembly == null)
{
sqliteAssembly = System.Reflection.Assembly.LoadFile(System.IO.Path.Combine(ApplicationPaths.AppConfigPath, "system.data.sqlite.dll"));
AppDomain.CurrentDomain.AssemblyResolve += new ResolveEventHandler(SqliteResolver);
}
SQLiteConnectionStringBuilder connectionstr = new SQLiteConnectionStringBuilder();
connectionstr.PageSize = 4096;
connectionstr.CacheSize = 4096;
connectionstr.SyncMode = SynchronizationModes.Normal;
connectionstr.DataSource = dbPath;
connection = new SQLiteConnection(connectionstr.ConnectionString);
connection.Open();
string[] queries = {"create table if not exists images (guid, width, height, updated, stream_size, data blob)",
"create unique index if not exists idx_images on images(guid, width)",
};
foreach (var query in queries) {
try {
connection.Exec(query);
} catch (Exception e) {
Logger.ReportInfo(e.ToString());
}
}
alive = true; // tell writer to keep going
Async.Queue("ImageCache Writer", DelayedWriter);
}
private void dataGridView1_DragDrop(object sender, DragEventArgs e)
{
var f = e.Data.GetFormats();
// FileDrop
var x = f.Select(k => e.Data.GetData(k)).ToArray();
var FileDrop = (string[])e.Data.GetData(DataFormats.FileDrop);
FileDrop.WithEach(
k =>
{
var fi = new FileInfo(k);
dataGridView1.Rows.Add(
fi.Extension,
fi.Name,
fi.Length
);
#region add to db
// http://code.activestate.com/recipes/252531-storing-binary-data-in-sqlite/
var csb = new SQLiteConnectionStringBuilder
{
DataSource = DataSource,
Version = 3
};
using (var c = new SQLiteConnection(csb.ConnectionString))
{
c.Open();
{
var sql = "create table if not exists Table1 (ContentKey INTEGER PRIMARY KEY AUTOINCREMENT, ContentValue text not null, ContentBytes blob)";
using (var reader = new SQLiteCommand(sql, c).ExecuteReader())
{
}
}
{
var sql = "insert into Table1 (ContentValue, ContentBytes) values (?, ?)";
var cmd = new SQLiteCommand(sql, c);
cmd.Parameters.AddWithValue("", fi.Name);
cmd.Parameters.AddWithValue("", File.ReadAllBytes(fi.FullName));
using (var reader = cmd.ExecuteReader())
{
}
}
}
#endregion
}
);
SystemSounds.Beep.Play();
//Console.Beep();
}
/// <summary>
/// 获得连接数据库
/// </summary>
/// <param name="datasource"></param>
/// <returns></returns>
public SQLiteConnection getSqlLiteDB(String datasource)
{
SQLiteConnection conn = new SQLiteConnection();
SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
connstr.DataSource = "databases/" + datasource + ".db";
conn.ConnectionString = connstr.ToString();
return conn;
}
private static string GetConnectionString(string path)
{
var csb = new SQLiteConnectionStringBuilder();
csb.DataSource = path;
csb.Version = 3;
return csb.ConnectionString;
}
/// <inheritDoc />
protected override string BuildConnectionString(string dsn, string user, string password, PhpArray options)
{
var csb = new ConnectionStringBuilder();
csb.DataSource = dsn;
csb.Add("Password", password);
return(csb.ConnectionString);
}
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)
{
}
}
public static void SqlServerToSqlLite(string db, string sql)
{
//string sql = "select [gzDay],[gzMonth],[gzYear],DateValue=rtrim([DateValue])+' '+ substring(JieQi,4,5),[weekDay],[constellation],JieQi,[nlMonth],[nlDay] from [ChineseTenThousandCalendar] where left(ltrim(JieQi),2) in (" + JieQiHelper.GetInJieQis() + ")";
System.Data.SqlClient.SqlConnection sqlCon = new System.Data.SqlClient.SqlConnection();
sqlCon.ConnectionString = "server=(local);user id=sa;password=***;initial catalog=HanZiMisc;TimeOut=10000;Packet Size=4096;Pooling=true;Max Pool Size=100;Min Pool Size=1";
System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand(sql);
sqlCmd.Connection = sqlCon;
sqlCon.Open();
System.Data.SqlClient.SqlDataReader sqlReader = sqlCmd.ExecuteReader();
if (sqlReader != null)
{
string datasource = db;// Application.StartupPath + "/JieQi.db";
System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
//连接数据库
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
connstr.Password = "******";//可以设密码
conn.ConnectionString = connstr.ToString();
conn.Open();
//conn.ChangePassword("nguchen");//可以改已打开CON的密码
//创建表
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
string sqlc = "CREATE TABLE ChinaJiQiTable(ID Integer PRIMARY KEY,DayGZ TEXT(4) NULL,MonthGZ TEXT(4) NULL,YearGZ TEXT(4) NULL, DateValue datetime NULL,Week TEXT(6) NULL,Star TEXT(6) NULL, JieQi TEXT(30) NULL, NongLiMonth TEXT(6) NULL,NongLiDay TEXT(4) NULL)";
cmd.CommandText = sqlc;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
//插入数据
SQLiteParameter[] sqlparams = new SQLiteParameter[]
{
new SQLiteParameter("@ID", DbType.Int64, 10),
new SQLiteParameter("@dG", DbType.String, 4),
new SQLiteParameter("@mG", DbType.String, 4),
new SQLiteParameter("@yG", DbType.String, 4),
new SQLiteParameter("@start", DbType.String, 6),
new SQLiteParameter("@wk", DbType.String, 6),
new SQLiteParameter("@date", DbType.DateTime),
new SQLiteParameter("@jieqi", DbType.String, 30),
new SQLiteParameter("@nM", DbType.String, 6),
new SQLiteParameter("@nD", DbType.String, 6),
};
cmd.Parameters.AddRange(sqlparams);
while (sqlReader.Read())
{
InsertSQLiteGZTable(sqlReader["DateValue"].ToString().Trim(), sqlReader["weekDay"].ToString().Trim(), sqlReader["constellation"].ToString().Trim(),
sqlReader["gzYear"].ToString().Trim(), sqlReader["gzMonth"].ToString().Trim(), sqlReader["gzDay"].ToString().Trim(), sqlReader["JieQi"].ToString().Trim(),
sqlReader["nlMonth"].ToString().Trim(), sqlReader["nlDay"].ToString().Trim(), conn, cmd);
}
sqlReader.Close();
conn.Close();
cmd.Dispose();
}
sqlCon.Close();
sqlCmd = null;
sqlCon.Dispose();
}
public string GetConnection()
{
var d = new System.Data.SQLite.SQLiteConnectionStringBuilder()
{
DataSource = DB_NAME
};
d.Add("mode", "memory");
d.Add("cache", "shared");
return(d.ToString());
}
private static Lite.SQLiteConnection GetSqLiteConnection(string exeFolder)
{
var connStringBuilder = new Lite.SQLiteConnectionStringBuilder()
{
DataSource = exeFolder + "\\Metadata.db",
ForeignKeys = true
};
return(new Lite.SQLiteConnection()
{
ConnectionString = connStringBuilder.ConnectionString
});
}
public static string ConnectionStringExample()
{
var b = new System.Data.SQLite.SQLiteConnectionStringBuilder(@"Data Source=e:\\StorageDB\\StorageDB.sqlite");
b.CacheSize = 8192;
b.JournalMode = System.Data.SQLite.SQLiteJournalModeEnum.Wal;
b.ForeignKeys = true;
b.RecursiveTriggers = true;
b.Enlist = true;
b.SyncMode = System.Data.SQLite.SynchronizationModes.Normal;
b.ReadOnly = true;
b.Pooling = true;
return(b.ToString());
}
public static string GetConnectString(string sqliteDataSource)
{
var builder = new System.Data.SQLite.SQLiteConnectionStringBuilder {
DataSource = sqliteDataSource,
Version = 3,
LegacyFormat = false,
//PageSize = 8192,
//CacheSize = 81920,
SyncMode = SynchronizationModes.Full, //途中で強制的に電源をOFFにすることも考えられるため。
JournalMode = SQLiteJournalModeEnum.Default
};
return(builder.ToString());
}
/// <summary>
/// 创建数据库
/// </summary>
/// <param name="datasource"></param>
/// <param name="pwd"></param>
public static void CreateDB(string datasource, string pwd)
{
//创建一个数据库文件
//string datasource = @"F:\Source\Solution_Wanxiang2011\WinAppTestSQLite\bin\Debug\test1234.db";
System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
//连接数据库
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
connstr.Password = pwd; // "adminwx";//设置密码，SQLite ADO.NET实现了数据库密码保护
conn.ConnectionString = connstr.ToString();
conn.Open();
//创建表
}
private void buttonConnectOption_Click()
{
try
{
//-----< オプションを指定して接続 >-----
var builder = new System.Data.SQLite.SQLiteConnectionStringBuilder
{
DataSource = CONNECTION_STRING,
SyncMode = SynchronizationModes.Normal,
JournalMode = SQLiteJournalModeEnum.Persist,
};
string connectionString = builder.ToString();
List <Artist> artists = new List <Artist>();
//-----< オプションを指定して接続 >-----
//using (var con = new SQLiteConnection(connectionString)) //上手く行かない・・・。
using (var con = new SQLiteConnection(CONNECTION_STRING))
{
con.Open();
using (var cmd = con.CreateCommand())
{
cmd.CommandText = "SELECT * FROM artists";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Artist _artist = new Artist();
_artist.ID = reader.GetInt16(0);
_artist.Name = reader.GetString(1);
//_artist.CreatedAt = reader.GetValue(0);
//_artist.UpdatedAt = reader.GetValues(0);
artists.Add(_artist);
}
}
}
}
//-----< オプションを指定して接続 >-----
myDataGrid01.ItemsSource = artists;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
/// 创建
/// </summary>
private void connectDatabase()
{
//连接
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
//connstr.Password = "******";//设置密码，SQLite ADO.NET实现了数据库密码保护
conn.ConnectionString = connstr.ToString();
conn.Open();
//创建表格
//System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
//string sql = "CREATE TABLE control_authority(ElementName varchar(32), MinLevel int,ToStr varchar(32))";
//cmd.CommandText = sql;
//cmd.Connection = conn;
//cmd.ExecuteNonQuery();
}
public static SQLiteDataReader ExecuteDataReader(string sql, string db)
{
string datasource = db;// Application.StartupPath + "/nongli.db";
//System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
//连接数据库
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
connstr.Password = "******";
conn.ConnectionString = connstr.ToString();
conn.Open();
//取出数据
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.Connection = conn;
cmd.CommandText = sql;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
return(reader);
}
public static void Main(string[] args)
{
// create options builder
/*builder. Mode = SqliteOpenMode.ReadOnly;
* builder.Cache = SqliteCacheMode.Shared;
* builder.ForeignKeys = true;
* builder.RecursiveTriggers = true;*/
// configure connection string
//builder.UseSQLite(@"e:\StorageDB\StorageDB.sqlite");
var b = new System.Data.SQLite.SQLiteConnectionStringBuilder(@"Data Source=e:\\StorageDB\\StorageDB.sqlite");
b.CacheSize = 8192;
b.JournalMode = System.Data.SQLite.SQLiteJournalModeEnum.Wal;
b.ForeignKeys = true;
b.RecursiveTriggers = true;
b.Enlist = true;
b.PageSize = 4096;
b.SyncMode = System.Data.SQLite.SynchronizationModes.Full;
b.ReadOnly = true;
b.Pooling = true;
var o = new LinqToDbConnectionOptionsBuilder();
var p = o.UseSQLite(b.ToString());
//System.Data.SQLite.SQLiteFunction
// pass configured options to data connection constructor
//List<Item> items = null;
/*using (var dc = new SQLiteProvider(p.Build()))
* {
*
* // items = (from i in dc.Items where i.ItemFileName.StartsWith("P") select i).ToList();
* }
*
* foreach (var i in items)
* Console.WriteLine($"{i}");*/
Console.ReadKey();
}
/// <summary>
/// 创建数据库
/// </summary>
/// <param name="dbName"></param>
/// <returns></returns>
public static bool CreatNewDB(string dbName)
{
try
{
if (dbName == "")
{
dbName = "yanshanshuo.db";
}
string json = File.ReadAllText(ConfigurationManager.AppSettings["DataBaseCreate"]);
List <Model.TableFrame> tables = LitJson.JsonMapper.ToObject <List <Model.TableFrame> >(json);;
//判断数据文件是否存在，存在的话就删除
string datasource = GetPath() + "App_Data\\" + dbName;
System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
//connstr.Password = "******";//设置密码，SQLite ADO.NET实现了数据库密码保护
conn.ConnectionString = connstr.ToString();
conn.Open();
foreach (Model.TableFrame table in tables)
{
string columnString = "";
foreach (Model.ColumnFrame c in table.column)
{
columnString += c.ColumnName + " " + c.DataType + " " + c.Condition + ",";
}
columnString = columnString.Substring(0, columnString.Length - 1);
//创建表
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
string sql = "CREATE TABLE " + table.TableName + "(" + columnString + ")";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
}
conn.Close();
conn.Dispose();
return(true);
}
catch (Exception ex) { return(false); }
}
public void OpenDb()
{
if (_isopen && _conn != null)
{
return;
}
try
{
System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = this._kqfilepath;
//connstr.Password = "******";
//_conn = new SQLiteConnection(string.Format(@"Data Source={0};", _kqfilepath));
_conn = new SQLiteConnection();
_conn.ConnectionString = connstr.ToString();
if (File.Exists(this._kqfilepath))
{
_conn.Open();
_isopen = true;
}
else
{
_conn.Open();
if (CreateTable())
{
_isopen = true;
}
}
return;
}
catch (System.Exception ex)
{
MessageBox.Show("打开KQDB失败");
_isopen = false;
if (_conn != null)
{
_conn = null;
}
return;
}
}
/// <summary>
/// 查询配置
/// </summary>
/// <param name="name"></param>
/// <returns></returns>
public static List <ConfigClass> query(string name)
{
try
{
List <ConfigClass> list = new List <ConfigClass>();
SQLiteConnection conn = new SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = SqlLiteHelper.getSQLiteConn();
connstr.Password = "******"; //设置密码，SQLite ADO.NET实现了数据库密码保护
conn.ConnectionString = connstr.ToString();
SQLiteCommand cmd = new SQLiteCommand(); //是不是很熟悉呢？
DateTime StartComputerTime = DateTime.Now;
cmd.Connection = conn;
if (name == null)
{
cmd.CommandText = "select * from t_config order by id asc";
}
else
{
cmd.CommandText = "select * from t_config where name='" + name + "' order by id asc";
}
conn.Close();
conn.Open();
SQLiteDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
ConfigClass config = new ConfigClass();
config.Id = reader["id"].ToString();
config.Name = reader["name"].ToString();
config.FormalValue = reader["formalValue"].ToString();
config.TestValue = reader["testValue"].ToString();
config.Remark = reader["remark"].ToString();
list.Add(config);
}
conn.Close();
return(list);
}catch (Exception ex) { return(new List <ConfigClass>()); }
}
static void Main(string[] args)
{
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connStr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connStr.DataSource = @"F:\John's Dir\Principal Project\更新发布工具\Deployer\beta0.3\srvDB";
conn.ConnectionString = connStr.ToString();
conn.Open();
string str = "select * from Deployer_WeblogicAppInfo_ML";
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
cmd.CommandText = str;
cmd.Connection = conn;
SQLiteDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (reader.Read())
{
System.Console.WriteLine(reader.GetValue(1));
}
System.Console.Read();
}
/// <summary>
/// 修改配置
/// </summary>
/// <param name="config"></param>
/// <returns></returns>
public static bool update(ConfigClass config)
{
try
{
SQLiteConnection conn = new SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = SqlLiteHelper.getSQLiteConn();
connstr.Password = "******";//设置密码，SQLite ADO.NET实现了数据库密码保护
conn.ConnectionString = connstr.ToString();
SQLiteCommand comm = new SQLiteCommand(conn);
comm.CommandText = "update t_config set testValue='" + config.FormalValue + "',formalValue='" + config.FormalValue + "' where name='" + config.Name + "'";
conn.Open();
int result = comm.ExecuteNonQuery();
if (result > 0)
{
return(true);
}
conn.Close();
return(false);
}
catch (Exception ex) { return(false); }
}
public static void InsertRangeData(Guid id, List <PublicProtocal.Coordinate> queue)
{
if (dataConnection == null)
{
dataConnection = new SQLiteConnection();
SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = DbFileName;
dataConnection.ConnectionString = connstr.ToString();
}
try
{
commonConnection.Open();
SQLiteCommand cmd = new SQLiteCommand(commonConnection);
cmd.CommandText = string.Format("Insert into CurveData([X],[Y],[id]) values(@p0,@p1,@p2)");
cmd.Parameters.Add("@p0", System.Data.DbType.Decimal);
cmd.Parameters.Add("@p1", System.Data.DbType.Decimal);
cmd.Parameters.Add("@p2", System.Data.DbType.Decimal);
cmd.CommandType = System.Data.CommandType.Text;
cmd.ExecuteNonQuery();
for (int i = 0; i < queue.Count; i++)
{
try
{
cmd.Parameters["@p0"].Value = queue[i].X;
cmd.Parameters["@p1"].Value = queue[i].Y;
cmd.Parameters["@p2"].Value = id;
cmd.ExecuteNonQuery();
}
catch { continue; }
}
}
catch { return; }
finally
{
commonConnection.Close();
}
}
public static void InsertObject <T>(T instance) where T : BaseObject
{
try
{
string tableName = typeof(T).Name;//;.Substring(typeof(T).Name.LastIndexOf('.'));
if (commonConnection == null)
{
commonConnection = new SQLiteConnection();
SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = DbFileName;
commonConnection.ConnectionString = connstr.ToString();
}
commonConnection.Open();
SQLiteCommand cmd = new SQLiteCommand(commonConnection);
cmd.CommandText = BuildInsertCommand(tableName, instance);
cmd.ExecuteNonQuery();
commonConnection.Close();
}
catch (Exception e)
{
commonConnection.Close();
throw e;
}
}
private void SqliteTest(object sender, RoutedEventArgs e)
{
string datasource = "test.db";
System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
connstr.CacheSize = 1024;
// connstr.Password = "******";//设置密码，SQLite ADO.NET实现了数据库密码保护
conn.ConnectionString = connstr.ToString();
conn.Open();
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
string sql = "CREATE TABLE test(username varchar(20),password varchar(20))";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
sql = "INSERT INTO test VALUES('a','b')";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
sql = "SELECT * FROM test";
cmd.CommandText = sql;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
while (reader.Read())
{
sb.Append("username:"******"\n")
.Append("password:").Append(reader.GetString(1));
}
MessageBox.Show(sb.ToString());
}
public static void DeleteObjectById <T>(Guid id)
{
try
{
string tableName = typeof(T).Name;
if (commonConnection == null)
{
commonConnection = new SQLiteConnection();
SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = DbFileName;
commonConnection.ConnectionString = connstr.ToString();
}
commonConnection.Open();
SQLiteCommand cmd = new SQLiteCommand(commonConnection);
cmd.CommandText = "Delete from " + tableName + " where id='" + id.ToString() + "'";
cmd.ExecuteNonQuery();
commonConnection.Close();
}
catch (Exception e)
{
commonConnection.Close();
throw e;
}
}
public static void CheckDataFile(string datasource)
{
//string datasource = Application.StartupPath + "\\data.db";
System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
//连接数据库
System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
//connstr.Password = "******";//设置密码，SQLite ADO.NET实现了数据库密码保护
conn.ConnectionString = connstr.ToString();
conn.Open();
//创建表
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
string sql = "CREATE TABLE billInfo (BillID varchar(50) PRIMARY KEY,SiteName varchar(50),SiteUserName varchar(50),TotalMoney decimal(5,2),YearRete decimal(5,2),Deadline interger ,DeadlineType interger,ReceivedPeriod interger,ReceivablePeriod interger,ReceivablePrincipalAndInterest decimal(5,2),ReceivedPrincipalAndInterest decimal(5,2),WayOfRepayment interger,Reward decimal(5,2),BeginDay varchar(20),EndDay varchar(20),YuQiCount interger,Deleted interger,Flag interger,Remark varchar(200),UpdateTime datetime,CreateTime datetime)";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
sql = "Create Table billdetail(BillDetailID varchar(50) PRIMARY KEY,BillID varchar(50),Periods varchar(50),ReceivableDay varchar(20),ReceivedDay varchar(20),ReceivablePrincipalAndInterest decimal(5,2),ReceivableInterest decimal(5,2),ReceivedPrincipalAndInterest decimal(5,2),IsYuQi interger ,Deleted interger,Flag interger,UpdateTime datetime,CreateTime datetime)";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
conn.Close();
}
public override void Open()
{
VerifyNotDisposed();
if (State != ConnectionState.Closed)
{
throw new InvalidOperationException("Cannot Open when State is {0}.".FormatInvariant(State));
}
var connectionStringBuilder = new SQLiteConnectionStringBuilder {
ConnectionString = ConnectionString
};
m_dataSource = connectionStringBuilder.DataSource;
if (string.IsNullOrEmpty(m_dataSource))
{
throw new InvalidOperationException("Connection String Data Source must be set.");
}
SQLiteOpenFlags openFlags = connectionStringBuilder.ReadOnly ? SQLiteOpenFlags.ReadOnly : SQLiteOpenFlags.ReadWrite;
if (!connectionStringBuilder.FailIfMissing && !connectionStringBuilder.ReadOnly)
{
openFlags |= SQLiteOpenFlags.Create;
}
SetState(ConnectionState.Connecting);
Match m = s_vfsRegex.Match(m_dataSource);
string fileName = m.Groups["fileName"].Value;
string vfsName = m.Groups["vfsName"].Value;
var errorCode = NativeMethods.sqlite3_open_v2(ToNullTerminatedUtf8(fileName), out m_db, openFlags, string.IsNullOrEmpty(vfsName) ? null : ToNullTerminatedUtf8(vfsName));
bool success = false;
try
{
if (errorCode != SQLiteErrorCode.Ok)
{
SetState(ConnectionState.Broken);
errorCode.ThrowOnError();
}
if (!string.IsNullOrEmpty(connectionStringBuilder.Password))
{
byte[] passwordBytes = Encoding.UTF8.GetBytes(connectionStringBuilder.Password);
NativeMethods.sqlite3_key(m_db, passwordBytes, passwordBytes.Length).ThrowOnError();
}
int isReadOnly = NativeMethods.sqlite3_db_readonly(m_db, "main");
if (isReadOnly == 1 && !connectionStringBuilder.ReadOnly)
{
throw new SQLiteException(SQLiteErrorCode.ReadOnly);
}
if (connectionStringBuilder.CacheSize != 0)
{
this.ExecuteNonQuery("pragma cache_size={0}".FormatInvariant(connectionStringBuilder.CacheSize));
}
if (connectionStringBuilder.PageSize != 0)
{
this.ExecuteNonQuery("pragma page_size={0}".FormatInvariant(connectionStringBuilder.PageSize));
}
if (connectionStringBuilder.ContainsKey(SQLiteConnectionStringBuilder.MmapSizeKey))
{
this.ExecuteNonQuery("pragma mmap_size={0}".FormatInvariant(connectionStringBuilder.MmapSize));
}
if (connectionStringBuilder.ForeignKeys)
{
this.ExecuteNonQuery("pragma foreign_keys = on");
}
if (connectionStringBuilder.JournalMode != SQLiteJournalModeEnum.Default)
{
this.ExecuteNonQuery("pragma journal_mode={0}".FormatInvariant(connectionStringBuilder.JournalMode));
}
if (connectionStringBuilder.ContainsKey(SQLiteConnectionStringBuilder.SynchronousKey))
{
this.ExecuteNonQuery("pragma synchronous={0}".FormatInvariant(connectionStringBuilder.SyncMode));
}
if (connectionStringBuilder.TempStore != SQLiteTemporaryStore.Default)
{
this.ExecuteNonQuery("pragma temp_store={0}".FormatInvariant(connectionStringBuilder.TempStore));
}
if (m_statementCompleted != null)
{
NativeMethods.sqlite3_profile(m_db, m_profileCallback, IntPtr.Zero);
}
SetState(ConnectionState.Open);
success = true;
}
finally
{
if (!success)
{
Utility.Dispose(ref m_db);
}
}
}
public override void Open()
{
VerifyNotDisposed();
if (State != ConnectionState.Closed)
{
throw new InvalidOperationException("Cannot Open when State is {0}.".FormatInvariant(State));
}
var connectionStringBuilder = new SQLiteConnectionStringBuilder {
ConnectionString = ConnectionString
};
m_dataSource = connectionStringBuilder.DataSource;
if (string.IsNullOrEmpty(m_dataSource))
{
throw new InvalidOperationException("Connection String Data Source must be set.");
}
SQLiteOpenFlags openFlags = connectionStringBuilder.ReadOnly ? SQLiteOpenFlags.ReadOnly : SQLiteOpenFlags.ReadWrite;
if (!connectionStringBuilder.FailIfMissing && !connectionStringBuilder.ReadOnly)
{
openFlags |= SQLiteOpenFlags.Create;
}
SetState(ConnectionState.Connecting);
Match m = s_vfsRegex.Match(m_dataSource);
string fileName = m.Groups["fileName"].Value;
string vfsName = m.Groups["vfsName"].Value;
var errorCode = NativeMethods.sqlite3_open_v2(ToNullTerminatedUtf8(fileName), out m_db, openFlags, string.IsNullOrEmpty(vfsName) ? null : ToNullTerminatedUtf8(vfsName));
bool success = false;
try
{
if (errorCode != SQLiteErrorCode.Ok)
{
SetState(ConnectionState.Broken);
throw new SQLiteException(errorCode, m_db);
}
if (!string.IsNullOrEmpty(connectionStringBuilder.Password))
{
byte[] passwordBytes = Encoding.UTF8.GetBytes(connectionStringBuilder.Password);
errorCode = NativeMethods.sqlite3_key(m_db, passwordBytes, passwordBytes.Length);
if (errorCode != SQLiteErrorCode.Ok)
{
throw new SQLiteException(errorCode, m_db);
}
}
bool allowOpenReadOnly = true;
#if MONOANDROID
// opening read-only throws "EntryPointNotFoundException: sqlite3_db_readonly" on Android API 15 and below (JellyBean is API 16)
allowOpenReadOnly = Android.OS.Build.VERSION.SdkInt >= Android.OS.BuildVersionCodes.JellyBean;
#endif
if (allowOpenReadOnly)
{
int isReadOnly = NativeMethods.sqlite3_db_readonly(m_db, "main");
if (isReadOnly == 1 && !connectionStringBuilder.ReadOnly)
{
throw new SQLiteException(SQLiteErrorCode.ReadOnly);
}
}
// wait up to ten seconds (in native code) when there is DB contention, but still give managed code a
// chance to respond to cancellation periodically
NativeMethods.sqlite3_busy_timeout(m_db, 10000);
if (connectionStringBuilder.CacheSize != 0)
{
this.ExecuteNonQuery("pragma cache_size={0}".FormatInvariant(connectionStringBuilder.CacheSize));
}
if (connectionStringBuilder.PageSize != 0)
{
this.ExecuteNonQuery("pragma page_size={0}".FormatInvariant(connectionStringBuilder.PageSize));
}
if (connectionStringBuilder.ContainsKey(SQLiteConnectionStringBuilder.MmapSizeKey))
{
this.ExecuteNonQuery("pragma mmap_size={0}".FormatInvariant(connectionStringBuilder.MmapSize));
}
if (connectionStringBuilder.ForeignKeys)
{
this.ExecuteNonQuery("pragma foreign_keys = on");
}
if (connectionStringBuilder.JournalMode != SQLiteJournalModeEnum.Default)
{
this.ExecuteNonQuery("pragma journal_mode={0}".FormatInvariant(connectionStringBuilder.JournalMode));
}
if (connectionStringBuilder.ContainsKey(SQLiteConnectionStringBuilder.SynchronousKey))
{
this.ExecuteNonQuery("pragma synchronous={0}".FormatInvariant(connectionStringBuilder.SyncMode));
}
if (connectionStringBuilder.TempStore != SQLiteTemporaryStore.Default)
{
this.ExecuteNonQuery("pragma temp_store={0}".FormatInvariant(connectionStringBuilder.TempStore));
}
if (m_statementCompleted != null)
{
SetProfileCallback(s_profileCallback);
}
SetState(ConnectionState.Open);
success = true;
}
finally
{
if (!success)
{
Utility.Dispose(ref m_db);
}
}
}
private void button1_Click(object sender, EventArgs e)
{
//创建一个数据库文件
string datasource = "test.db";
System.Data.SQLite.SQLiteConnection.CreateFile(datasource);
//连接数据库
System.Data.SQLite.SQLiteConnection conn =
new System.Data.SQLite.SQLiteConnection();
System.Data.SQLite.SQLiteConnectionStringBuilder connstr =
new System.Data.SQLite.SQLiteConnectionStringBuilder();
connstr.DataSource = datasource;
connstr.Password = "******";//设置密码，SQLite ADO.NET实现了数据库密码保护
conn.ConnectionString = connstr.ToString();
conn.Open();
//创建表
System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand();
string sql = "CREATE TABLE test(username varchar(20),password varchar(20))";
cmd.CommandText = sql;
cmd.Connection = conn;
cmd.ExecuteNonQuery();
//插入数据
sql = "INSERT INTO test VALUES(’dotnetthink’,'mypassword’)";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
//取出数据
sql = "SELECT * FROM test";
cmd.CommandText = sql;
System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader();
StringBuilder sb = new StringBuilder();
while (reader.Read())
{
sb.Append("username:"******"\n")
.Append("password:").Append(reader.GetString(1));
}
MessageBox.Show(sb.ToString());
}