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; }