private void button9_Click( object sender, EventArgs e )
{
SQLiteConnection connection = new SQLiteConnection( GetConnectionString() );
connection.SetPassword( new byte[] { 0xDE, 0xAD, 0xBE, 0xEF, 0xBA, 0xDC, 0xC0, 0xDE } );
connection.Open();
connection.ChangePassword( "" );
}
private void NullPwdClick(object sender, RoutedEventArgs e)
{
using (var sqliteConn = new SQLiteConnection(string.Format(@"Data Source={0}\Abiz.db3;UTF8Encoding=True;Version=3;Pooling=True", txt.Text)))
{
sqliteConn.SetPassword(_pwd);
sqliteConn.Open();
sqliteConn.ChangePassword(string.Empty);
sqliteConn.Close();
}
}
bool TestSQLiteConnection(string connStr, string sqlitePassword)
{
try
{
using (SQLiteConnection conn = new SQLiteConnection(connStr))
{
if (!string.IsNullOrEmpty(sqlitePassword))
{
conn.SetPassword(sqlitePassword);
}
conn.Open();
}
return true;
}
catch (Exception)
{
return false;
}
}
private DbConnection CreateConnection()
{
var connectionString = string.Format("Data Source={0}", this.DatabasePath);
var connection = new SQLiteConnection(connectionString);
if (this.Password != null && this.Password.Length > 0)
{
connection.SetPassword(this.Password);
}
connection.Open();
return connection;
}
private void OpenConnection()
{
try
{
if (mConn != null && mConn.State == ConnectionState.Open)
{
mConn.Close();
mConn = null;
}
mConn = new SQLiteConnection();
mConn.ConnectionString = "Data Source=" + mDatabaseFileLocation;
if (!String.IsNullOrEmpty(mPassword))
{
mConn.SetPassword(mPassword);
}
mConn.Open();
mCmd = new SQLiteCommand(mConn);
}
catch (Exception e)
{
throw;
}
}
/// <summary>
/// Create a sql table based on a DataTable
/// </summary>
/// <param name="tableName">SQL Database table name</param>
/// <param name="data">Source DataTable</param>
/// <param name="insert">Overload to populate rows</param>
/// <param name="dropOld">Drop old table if exists.</param>
/// <returns>Number of records or tables affected.</returns>
public override int CreateTable(string tableName, DataTable data, bool insert = false, bool dropOld = false)
{
if (dropOld)
ExecuteNonQuery(string.Format("DROP TABLE IF EXISTS {0}", tableName));
int recordsAffected = 0;
using (var cnn = new SQLiteConnection(DbConnection))
{
if (string.IsNullOrWhiteSpace(_pass))
cnn.SetPassword(_pass);
cnn.Open();
using (SQLiteTransaction trans = cnn.BeginTransaction())
using (SQLiteCommand cmd = cnn.CreateCommand())
{
string cols = data.Columns.Cast<DataColumn>().Aggregate(string.Empty, (current, col) => current + string.Format("{0},", col.ColumnName)).TrimEnd(',');
string pkey = null;
if (data.PrimaryKey.Length > 0)
pkey = data.PrimaryKey.Aggregate(", PRIMARY KEY (", (current, col) => current + string.Format("{0},", col.ColumnName)).TrimEnd(',') + ")";
cmd.CommandText = string.Format("CREATE TABLE {0} ({1}{2})", tableName, cols, pkey);
recordsAffected = cmd.ExecuteNonQuery();
trans.Commit();
}
cnn.Close();
}
if (insert)
{
recordsAffected = BulkInsert(tableName, data);
}
return recordsAffected;
}
public Database()
{
var cstr = (new SQLiteConnectionStringBuilder()
{
DataSource = Path.Combine(Program.Configuration.DatabasePath, "cbackup.db"),
FailIfMissing = false,
}).ConnectionString;
log.DebugFormat("Database - Opening database [{0}]",cstr);
_cnx = new SQLiteConnection(cstr);
if (!DisableEncryption())
{
log.Info("Applying security password");
_cnx.SetPassword(GetDatabasePassword());
}
else
log.Warn("NOTICE - The database is not encrypted !");
_cnx.Open();
bool needInitDb = false;
using (var cmd = _cnx.CreateCommand())
{
cmd.CommandText = "SELECT count(*) FROM sqlite_master WHERE tbl_name='Version' and type='table'";
var count = cmd.ExecuteScalar();
if (Convert.ToInt32(count) != 1)
{
log.Info("Version table not found - Assume database is empty");
needInitDb = true;
}
else
{
cmd.CommandText = "SELECT value FROM Version WHERE id=2";
var verText = cmd.ExecuteScalar().ToString();
var version = new Version(verText);
log.DebugFormat("Database - Version [{0}] ({1})", verText, version);
if (version.CompareTo(DbVersion) > 0)
{
log.Fatal("This database is incompatible - Stopping");
throw new Exception("Fatal - Incompatible database");
}
}
}
if (needInitDb)
{
log.Info("Database initialisation required - Running start script");
// ReSharper disable once AssignNullToNotNullAttribute
using (var strm = System.Reflection.Assembly.GetEntryAssembly().GetManifestResourceStream(
typeof(Database),"SetupDb.sql"))
using (var srdr = new StreamReader(strm))
using (var cmd = _cnx.CreateCommand())
{
cmd.CommandText = srdr.ReadToEnd();
cmd.ExecuteNonQuery();
}
}
log.Info("Database is ready");
_nextJobUid = (int)( JobProxy.GetJobMaxId() + 1 );
}
private void bt_CreateDataSource_Click(object sender, EventArgs e)
{
if (string.IsNullOrEmpty(txt_DataSourceFilePath.Text))
{
MessageBox.Show("请选择路径.");
return;
}
if (string.IsNullOrEmpty(txt_DataSourceFileName.Text))
{
MessageBox.Show("请输入文件名.");
return;
}
instance.SetDataSourcePath(Path.Combine(txt_DataSourceFilePath.Text, txt_DataSourceFileName.Text));
var conn = new System.Data.SQLite.SQLiteConnection(Path.Combine(txt_DataSourceFilePath.Text, txt_DataSourceFileName.Text));
conn.SetPassword("123456");
var parameters = new List <string[]>();
parameters.Add(new[] { "fileID", "varchar", "(50)" });
parameters.Add(new[] { "fileMD5", "varchar", "(32)" });
parameters.Add(new[] { "fileName", "varchar", "(200)" });
parameters.Add(new[] { "filePath", "varchar", "(1000)" });
parameters.Add(new[] { "fileInfo", "blob", "(20480)" });
parameters.Add(new[] { "parentID", "varchar", "(50)" });
instance.CreateTable("FileInfo", parameters.ToArray());
Add(new Model(Guid.NewGuid(), "根目录"));
InitDataSource();
InitUI(false);
}
private int SaveOrder(string Production, int OrderQty, string Consignee, string Phone, string sheng, string shi, string xian, string Address, string Description)
{
int vcount = 0;
using (SQLiteConnection conn = new SQLiteConnection(config.DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.SetPassword(config.DBPwd);
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
var dic = new Dictionary<string, object>();
dic["Production"] = Production;
dic["OrderQty"] = OrderQty;
dic["Consignee"] = Consignee;
dic["Phone"] = Phone;
dic["sheng"] = sheng;
dic["shi"] = shi;
dic["xian"] = xian;
dic["Address"] = Address;
dic["Description"] = Description;
vcount = sh.Insert("T_Order", dic);
conn.Close();
}
}
return vcount;
}
private static void SetPwd(string oldpwd,string newpwd)
{
using (SQLiteConnection conn = new SQLiteConnection(@"data source=F:\github\DFZ\source\DFZ.BenSeLing\App_Data\benseling.db"))
{
conn.SetPassword(oldpwd);
conn.Open();
conn.ChangePassword(newpwd);
conn.Close();
}
}
public string GetOrderList(int pageRows,int page)
{
Dictionary<string, object> pageResult = new Dictionary<string, object>();
using (SQLiteConnection conn = new SQLiteConnection(config.DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.SetPassword(config.DBPwd);
conn.Open();
//conn.ChangePassword(config.DBPwd);
SQLiteHelper sh = new SQLiteHelper(cmd);
//总记录数
string totalSql = "Select count(1) From T_order";
long total = (long)sh.ExecuteScalar(totalSql);
int offset = (page - 1) * pageRows;
string sql = string.Format(@"SELECT ID,[Production]
,[Consignee]
,[Phone]
,[sheng]
,[shi]
,[xian]
,[Address]
,[Description],
case when status = 1 then
'√'
else
' '
end as status,
BuildTime
FROM [T_Order]
Order by Status asc ,buildTime desc
limit {0} offset {1} ",pageRows,offset);
DataTable dt = sh.Select(sql);
conn.Close();
pageResult.Add("total", total);
pageResult.Add("rows", dt);
}
}
return JSON.Encode(pageResult);
}
// 连接数据库
public void ConnectionToSqliteDB() // 连接数据库
{
try
{
if (connState == ConnectionState.Closed)
{
conn.SetPassword(Password); // 用密码打开数据库
conn.Open(); // 打开数据库
cmd.Connection = conn; // 连接到数据库
}
}
catch (Exception e)
{
MessageBox.Show(e.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
}
protected void btnOK_Click(object sender, EventArgs e)
{
string loginName = txtUser.Text;
string pwd = txtPwd.Text;
int count = 0;
using (SQLiteConnection conn = new SQLiteConnection(config.DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.SetPassword(config.DBPwd);
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
Dictionary<string,object> para = new Dictionary<string,object>();
para.Add("@LoginName",loginName);
para.Add("@PassWord",pwd);
count = sh.ExecuteScalar<int>("select count(1) from T_User Where LoginName=@LoginName And PassWord=@PassWord;",para);
conn.Close();
}
}
if (count > 0)
{
FormsAuthentication.SetAuthCookie(loginName, false);
if(string.IsNullOrEmpty(Request["ReturnUrl"]))
{
Response.Redirect("~/Admin/Order.aspx");
}
else
{
Response.Redirect(Request["ReturnUrl"]);
}
System.Web.Security.FormsAuthentication.RedirectFromLoginPage(loginName, false);
}
else
{
Response.Write("<script>alert('用户名或密码错误！')</script>");
}
}
private static SQLiteConnection CreateConn(string dbName)
{
SQLiteConnection _conn = new SQLiteConnection();
try
{
string pstr = "pwd";
SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
connstr.DataSource = Environment.CurrentDirectory + dbName;
_conn.ConnectionString = connstr.ToString();
_conn.SetPassword(pstr);
_conn.Open();
return _conn;
}
catch (Exception exp)
{
Console.WriteLine("===CONN CREATE ERR====\r\n{0}", exp.ToString());
return null;
}
}
private void LoadDatabase(string databasePath)
{
try
{
sqlCon = new SQLiteConnection("Data Source=" + databasePath + ";Version=3;New=True;Compress=True;");
String dbFilename = System.IO.Path.GetFileName(databasePath);
String[] password = dbFilename.Split('@');
String[] dbPassword = password[1].Split('_');
String p = password[0] + dbPassword[0];
sqlCon.SetPassword(password[0] + dbPassword[0]);
sqlCon.Open(); //open the connection
sqlCmd = sqlCon.CreateCommand();
//Update this command to nates new table structure...
string commandText = "select filename, filePath, count, priority, pattern_D9_Count, pattern_D324_Count from Scanned";
sqlDataAdapter = new SQLiteDataAdapter(commandText, sqlCon);
dSetScanned.Reset();
sqlDataAdapter.Fill(dSetScanned);
dTableScanned = dSetScanned.Tables[0];
dataGridView_Social.DataSource = dTableScanned;
commandText = "select filename, filePath, count, priority, visa, mastercard, americanExpress, discover, dinerClub, JCB from CreditCard";
sqlDataAdapter = new SQLiteDataAdapter(commandText, sqlCon);
dSetCreditCard.Reset();
sqlDataAdapter.Fill(dSetCreditCard);
dTableCreditCard = dSetCreditCard.Tables[0];
dataGridView_Credit.DataSource = dTableCreditCard;
commandText = "select filePath from UnScannable";
sqlDataAdapter = new SQLiteDataAdapter(commandText, sqlCon);
dSetNotScanned.Reset();
sqlDataAdapter.Fill(dSetNotScanned);
dTableNotScanned = dSetNotScanned.Tables[0];
dataGridView_NotScanned.DataSource = dTableNotScanned;
}
catch (SQLiteException e)
{
MessageBox.Show(e.Message);
}
}
public int DeleteOrder(string orderId)
{
int count = 0;
using (SQLiteConnection conn = new SQLiteConnection(config.DataSource))
{
using (SQLiteCommand cmd = new SQLiteCommand())
{
cmd.Connection = conn;
conn.SetPassword(config.DBPwd);
conn.Open();
SQLiteHelper sh = new SQLiteHelper(cmd);
string sql = @"Delete From [T_Order] Where ID = @ID";
Dictionary<string, object> para = new Dictionary<string, object>();
para.Add("@ID", orderId);
count = sh.Execute(sql,para);
conn.Close();
}
}
return count;
}
/// <summary>
/// Create DB if not exists
/// Create Default tables
/// </summary>
public static void DatabaseCreateTables()
{
// We use these three SQLite objects:
SQLiteConnection sqliteConn;
SQLiteCommand sqliteCmd;
// create a new database connection:
sqliteConn = new SQLiteConnection("Data Source=database.db;Version=3;New=True;Compress=True;Password="******";");
sqliteConn.SetPassword(GameConstans.DB_PASSWORD);
// open the connection:
sqliteConn.Open();
// create a new SQL command:
sqliteCmd = sqliteConn.CreateCommand();
sqliteCmd.CommandText = "SELECT * FROM sqlite_master";
var name = sqliteCmd.ExecuteScalar();
// check account table exist or not
// if exist do nothing
if (name == null)
{
for (int row = 4; row < 8; row++)
{
for (int col = 4; col < 8; col++)
{
// Tables not exist, create tables
sqliteCmd.CommandText = "CREATE TABLE '" + row + col + "' (rowID INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE DEFAULT 1, playerName VARCHAR(20), moves INT, time INT, score INT)";
sqliteCmd.ExecuteNonQuery();
}
}
}
// We are ready, now lets cleanup and close our connection:
sqliteConn.Close();
}
/// <summary>
/// 获取数据库链接
/// </summary>
/// <returns></returns>
public static SQLiteConnection GetDBConnection()
{
SQLiteConnection conn = new SQLiteConnection();
SQLiteConnectionStringBuilder connstr = new SQLiteConnectionStringBuilder();
connstr.JournalMode = SQLiteJournalModeEnum.Wal;
connstr.DataSource = dataSource;
if (new XMLReader().ReadXML("注册").Equals("true"))
{
conn.SetPassword(dbPassword);
}
conn.ConnectionString = connstr.ToString();
return conn;
}
private void DBhandler()
{
_path = Path.Combine(RepairShoprUtils.folderPath, "RepairShopr.db3");
FileInfo fileInfo = new FileInfo(_path);
if (!fileInfo.Exists)
{
SQLiteConnection.CreateFile(_path);
try
{
using (SQLiteConnection con = new SQLiteConnection("data source=" + _path + ";PRAGMA journal_mode=WAL;"))
{
con.SetPassword("shyam");
con.Open();
SQLiteCommand cmdTableAccount = new SQLiteCommand("CREATE TABLE Account (Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT ,AccountId nvarchar(50),CustomerId nvarchar(50))", con);
cmdTableAccount.ExecuteNonQuery();
SQLiteCommand cmdTableTicket = new SQLiteCommand("CREATE TABLE Ticket (Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,TicketId nvarchar(50),RTicketId nvarchar(30))", con);
cmdTableTicket.ExecuteNonQuery();
SQLiteCommand cmdTableInvoice = new SQLiteCommand("CREATE TABLE Invoice (Id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,InvoiceId nvarchar(50),RinvoiceId nvarchar(50))", con);
cmdTableInvoice.ExecuteNonQuery();
con.Close();
}
}
catch (Exception ex)
{
RepairShoprUtils.LogWriteLineinHTML("Failed to Create Datebase Setting", MessageSource.Initialization, ex.StackTrace, messageType.Error);
}
}
}
/// <summary>
/// Fast update of many records
/// </summary>
/// <param name="tableName">SQL Database table name</param>
/// <param name="data">Data to update</param>
/// <returns>Number of records affected.</returns>
public override int BulkUpdate(string tableName, DataTable data)
{
throw new NotImplementedException();
int recordsAffected = 0;
using (var cnn = new SQLiteConnection(DbConnection))
{
if (string.IsNullOrWhiteSpace(_pass))
cnn.SetPassword(_pass);
cnn.Open();
using (SQLiteTransaction trans = cnn.BeginTransaction())
using (SQLiteCommand cmd = cnn.CreateCommand())
{
string cols = data.Columns.Cast<DataColumn>().Aggregate(string.Empty, (current, col) => current + string.Format("{0}=@{0},", col.ColumnName)).TrimEnd(',');
string pkey = data.PrimaryKey.Aggregate(string.Empty, (current, col) => current + string.Format("{0}=@{0},", col.ColumnName)).TrimEnd(',');
cmd.CommandText = string.Format("UPDATE {0} SET {1} WHERE {2}", tableName, cols, pkey);
foreach (DataColumn col in data.Columns)
{
cmd.Parameters.Add(col.ColumnName);
}
for (int rowIndex = 0; rowIndex < data.Rows.Count; rowIndex++)
{
for (int colIndex = 0; colIndex < data.Columns.Count; colIndex++)
{
cmd.Parameters[data.Columns[colIndex].ColumnName].Value = data.Rows[rowIndex][colIndex];
}
recordsAffected += cmd.ExecuteNonQuery();
}
trans.Commit();
}
cnn.Close();
}
return recordsAffected;
}
/// <summary>
/// Allows the programmer to interact with the database for purposes other than a query.
/// </summary>
/// <param name="sql">The SQL to be run.</param>
/// <returns>Number of records affected.</returns>
public override int ExecuteNonQuery(string sql)
{
using (var cnn = new SQLiteConnection(DbConnection))
using (var mycommand = new SQLiteCommand(cnn))
{
if (string.IsNullOrWhiteSpace(_pass))
cnn.SetPassword(_pass);
cnn.Open();
mycommand.CommandText = sql;
int recordsAffected = mycommand.ExecuteNonQuery();
cnn.Close();
return recordsAffected;
}
}
/// <summary>
/// Allows the programmer to run a query against the Database.
/// </summary>
/// <param name="sql">The SQL to run</param>
/// <returns>A DataTable containing the result set.</returns>
public override DataTable ExecuteQuery(string sql)
{
using (var cnn = new SQLiteConnection(DbConnection))
using (var mycommand = new SQLiteCommand(cnn))
{
var dt = new DataTable();
if (string.IsNullOrWhiteSpace(_pass))
cnn.SetPassword(_pass);
cnn.Open();
mycommand.CommandText = sql;
SQLiteDataReader reader = mycommand.ExecuteReader();
dt.Load(reader);
reader.Close();
cnn.Close();
return dt;
}
}
/// <summary>
/// Allows the programmer to retrieve single items from the DB.
/// </summary>
/// <param name="sql">The query to run.</param>
/// <returns>A string.</returns>
public override string ExecuteScalar(string sql)
{
using (var cnn = new SQLiteConnection(DbConnection))
{
if (string.IsNullOrWhiteSpace(_pass))
cnn.SetPassword(_pass);
cnn.Open();
using (var mycommand = new SQLiteCommand(cnn))
{
mycommand.CommandText = sql;
object value = mycommand.ExecuteScalar();
cnn.Close();
if (value != null)
return value.ToString();
return string.Empty;
}
}
}
/// <summary>
/// Set the username and password for the database
/// </summary>
/// <param name="user">SQL Username</param>
/// <param name="pass">SQL Password</param>
/// <returns>True if login succes, otherwise false</returns>
public override bool Login(string user, string pass)
{
using (var cnn = new SQLiteConnection(DbConnection))
{
try
{
if (!string.IsNullOrWhiteSpace(pass))
{
cnn.SetPassword(pass);
cnn.Open();
cnn.Close();
_pass = pass;
}
}
catch (Exception)
{
return false;
}
return true;
}
}
public DbConnection GetNewConnection()
{
switch (DatabaseProvider)
{
case DatabaseProvider.SQLServer:
return new SqlConnection(GetConnectionString());
case DatabaseProvider.Oracle:
return new OracleConnection(GetConnectionString());
case DatabaseProvider.SqlCe4:
return new SqlCeConnection(GetConnectionString());
case DatabaseProvider.SQLite:
var conn = new SQLiteConnection(GetConnectionString());
if (!string.IsNullOrEmpty(SqlPassword))
{
conn.SetPassword(SqlPassword);
}
return conn;
default:
throw new NotSupportedException("Database type is not supported");
}
}
/// <summary>
/// 执行数据库查询，返回SqlDataReader对象
/// </summary>
/// <param name="connectionString">连接字符串</param>
/// <param name="commandText">执行语句或存储过程名</param>
/// <param name="commandType">执行类型</param>
/// <returns>SqlDataReader对象</returns>
public static DbDataReader ExecuteReader(string connectionString, string commandText, CommandType commandType)
{
DbDataReader reader = null;
if (connectionString == null || connectionString.Length == 0)
throw new ArgumentNullException("connectionString");
if (commandText == null || commandText.Length == 0)
throw new ArgumentNullException("commandText");
SQLiteConnection con = new SQLiteConnection(connectionString);
con.SetPassword("123456");
SQLiteCommand cmd = new SQLiteCommand();
SQLiteTransaction trans = null;
PrepareCommand(cmd, con, ref trans, false, commandType, commandText);
try
{
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
throw ex;
}
return reader;
}
//public List<String> tVacation { get; set; }
//public List<String> tSick { get; set; }
//public List<String> tBTrip { get; set; }
public void CreateBase()
{
if (!File.Exists(DataBaseName))
{
SQLiteConnection.CreateFile(DataBaseName);
SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=DBTels.sqlite;Version=3;");
m_dbConnection.SetPassword(pass);
}
}
/// <summary>
/// Fast insert of many records
/// </summary>
/// <param name="tableName">SQL Database table name</param>
/// <param name="data">Data to insert</param>
/// <returns>Number of records affected.</returns>
public override int BulkInsert(string tableName, DataTable data)
{
int recordsAffected = 0;
using (var cnn = new SQLiteConnection(DbConnection))
{
if (string.IsNullOrWhiteSpace(_pass))
cnn.SetPassword(_pass);
cnn.Open();
using (SQLiteTransaction trans = cnn.BeginTransaction())
using (SQLiteCommand cmd = cnn.CreateCommand())
{
string cols = data.Columns.Cast<DataColumn>().Aggregate(string.Empty, (current, col) => current + (string.Format("{0},", col.ColumnName ))).TrimEnd(',');
string colsp = data.Columns.Cast<DataColumn>().Aggregate(string.Empty, (current, col) => current + (string.Format("@{0},", col.ColumnName))).TrimEnd(',');
cmd.CommandText = string.Format("INSERT INTO {0}({1}) VALUES({2})", tableName, cols, colsp);
foreach (DataColumn col in data.Columns)
{
cmd.Parameters.Add(new SQLiteParameter(col.ColumnName));
}
for (int rowIndex = 0; rowIndex < data.Rows.Count; rowIndex++)
{
for (int colIndex = 0; colIndex < data.Columns.Count; colIndex++)
{
string colName = data.Columns[colIndex].ColumnName;
cmd.Parameters[colName].Value = data.Rows[rowIndex][colName];
}
recordsAffected += cmd.ExecuteNonQuery();
}
trans.Commit();
}
cnn.Close();
}
return recordsAffected;
}
