public Error Insert(AccountModel.Account _account) { string query = string.Format("INSERT INTO Account (UUID, Profile, CreatedAt, UpdatedAt) VALUES ('{0}', '{1}', '{2}', '{3}')", _account.accountID, _account.profile, ModelUtility.NewUtcNow(), ModelUtility.NewUtcNow()); return(SQLiteUtility.Execute(connection, query)); }
protected void Page_Load(object sender, EventArgs e) { try { string path = Server.MapPath("./"); string dataSource = Path.Combine(path, "Dentaku.db"); using (SQLiteUtility util = new SQLiteUtility(dataSource)) { util.Connect(); string sql = "SELECT ZEIRITU FROM ZEIRITU"; Dictionary <string, dynamic> parameters = null; DataTable dataTable = util.Fill(sql, parameters); if (dataTable.Rows.Count > 0) { textBoxZei.Text = dataTable.Rows[0]["ZEIRITU"].ToString(); } else { textBoxZei.Text = "0"; } } } catch (Exception ex) { labelMessage.Text = ex.Message; } }
public DataSet FindActiveDS() { DataSet ds = new DataSet(); SQLiteUtility.ExecuteDataAdapter(SQLiteRepositoryFactory.GetConnection(), "select * from company", "company", ds); return(ds); }
public IActionResult Tables() { TablesModel model = new TablesModel(); model.TablesList = SQLiteUtility.GetTables(connectionString); return(View(model)); }
public override void Run() { CompanyForm f = new CompanyForm(new Company()); f.CompanySave += delegate(object sender, CompanyEventArgs e) { using (var d = new SaveFileDialog()) { d.Title = "Filename for New Company"; d.FileName = f.Company.Name + ".sqlite"; if (d.ShowDialog() == DialogResult.OK) { SQLiteConnection con = new SQLiteConnection("Data Source=" + d.FileName + ";Version=3"); con.Open(); SQLiteUtility.ExecuteNonQuery(con, new StreamReader(Path.Combine(ApplicationUtility.RootDirectory, "sqlscript.sql")).ReadToEnd()); con.Close(); NHibernateHelper.CloseSession(); NHibernateHelper.Configuration = new SQLiteDatabase(con.ConnectionString).CreateConfiguration(); new NHibernateCompanyRepository().SaveOrUpdate(f.Company); new NHibernateUserRepository().SaveOrUpdate(new User("admin", "root")); } } }; WorkbenchSingleton.AddDialog(f); }
protected void Page_Load(object sender, EventArgs e) { try { string path = Server.MapPath("./sqlite/"); string dataSource = Path.Combine(path, "Kotsuhi.db"); using (SQLiteUtility util = new SQLiteUtility(dataSource)) { util.Connect(); string createTbl = "CREATE TABLE IF NOT EXISTS kotsuhi(" + "data_id INTEGER PRIMARY KEY AUTOINCREMENT, " + "userid TEXT, " + "target_date TEXT, " + "transport TEXT, " + "station_from TEXT, " + "station_to TEXT, " + "oneway_cost INTEGER, " + "ido_kbn TEXT " + ")"; util.Execute(createTbl, null); } } catch (Exception ex) { } }
private void migrateData() { string srcDir = Path.Combine(Application.streamingAssetsPath, "db"); string srcFile = Path.Combine(srcDir, Constant.DataBaseFile); if (!Directory.Exists(Constant.DataBaseDir)) { Directory.CreateDirectory(Constant.DataBaseDir); } if (!File.Exists(Constant.DataBasePath)) { File.Copy(srcFile, Constant.DataBasePath); } Error error; SQLiteUtility.Open(Constant.DataBasePath, out error); if (Error.OK != error) { this.LogError(error); return; } AccountMock.AutoMigrate(); SQLiteUtility.Close(Constant.DataBasePath, out error); if (Error.OK != error) { this.LogError(error); } }
public DataSet FindOpenDS() { DataSet ds = new DataSet(); SQLiteUtility.ExecuteDataAdapter(SQLiteRepositoryFactory.GetConnection(), "select * from company", "company", ds); SQLiteUtility.ExecuteDataAdapter(SQLiteRepositoryFactory.GetConnection(), "select * from invoices where status = 0", "invoices", ds); SQLiteUtility.ExecuteDataAdapter(SQLiteRepositoryFactory.GetConnection(), "select * from customers where inactive = 0", "customers", ds); return(ds); }
public DataSet FindOpenDS() { DataSet ds = new DataSet(); SQLiteUtility.ExecuteDataAdapter(SQLiteRepositoryFactory.GetConnection(), "select * from company", "company", ds); SQLiteUtility.ExecuteDataAdapter(SQLiteRepositoryFactory.GetConnection(), "select * from purchase_orders where status = 0", "purchase_orders", ds); SQLiteUtility.ExecuteDataAdapter(SQLiteRepositoryFactory.GetConnection(), "select * from vendors where inactive = 0", "vendors", ds); return(ds); }
protected void ButtonLogin_Click(object sender, EventArgs e) { try { string path = Server.MapPath("./"); string dataSource = Path.Combine(path, "WebApp.db"); using (SQLiteUtility util = new SQLiteUtility(dataSource)) { util.Connect(); string sql = "SELECT * FROM userinfo WHERE userid = @userid"; string userid = TextBoxId.Text; Dictionary <string, dynamic> parameters = new Dictionary <string, dynamic> { { "userid", userid }, }; DataTable dataTable = util.Fill(sql, parameters); if (dataTable.Rows.Count > 0) { if (dataTable.Rows[0]["password"].ToString().Equals(TextBoxPw.Text)) { LabelMessage.Text = "認証に成功しました。"; SessionManager.UserInfo userInfo = new SessionManager.UserInfo(Session) { UserId = userid, }; Server.Transfer("~/Menu.aspx", true); } else { LabelMessage.Text = "パスワードが違います。"; } } else { LabelMessage.Text = "ユーザーが未登録です。"; } } } catch (Exception ex) { LabelMessage.Text = ex.Message; } }
static void Main(string[] args) { if (args == null) { } using (SQLiteUtility util = new SQLiteUtility("ConsolApp.db")) { util.Connect(); string sql = "CREATE TABLE IF NOT EXISTS decimal_table(" + "id INTEGER PRIMARY KEY AUTOINCREMENT," + "decimal REAL)"; util.Execute(sql); } }
public void AutoMigrate() { string query = @"CREATE TABLE IF NOT EXISTS Account( ID INTEGER PRIMARY KEY NOT NULL, UUID CHAR(32) UNIQUE NOT NULL, Profile TEXT NOT NULL, CreatedAt CHAR(19) NOT NULL, UpdatedAt CHAR(19) NOT NULL )"; Error error = SQLiteUtility.Execute(connection, query); if (error != Error.OK) { this.LogError(error.message); } }
public AccountModel.Account QueryProfile(string _accountID, out Error _err) { _err = Error.OK; AccountModel.Account account = new AccountModel.Account(); string query = string.Format("SELECT * FROM Account WHERE UUID='{0}'", _accountID); _err = SQLiteUtility.Execute(connection, query, (_reader) => { while (_reader.Read()) { account.profile = _reader.GetString(_reader.GetOrdinal("Profile")); } }); return(account); }
void Start() { Debug.Log("--------------- Start ------------------------"); Error error; SQLiteUtility.Open(Constant.DataBasePath, out error); if (Error.OK != error) { this.LogError(error); } // setup data modelAccount.SaveActiveAccount(DataCache.activeAccountID); executeBootloader(); }
protected void cmdToroku_Click(object sender, EventArgs e) { //DataTable dt = (DataTable)MeisaiData.DataSource; //DataRow dr = dt.NewRow(); //dr["gNo"] = "1"; //dr.EndEdit(); //dt.Rows.Add(dr); //dt.AcceptChanges(); //this.MeisaiData.DataSource = dt; //this.MeisaiData.DataBind(); try { string path = Server.MapPath("./sqlite/"); string dataSource = Path.Combine(path, "Kotsuhi.db"); using (SQLiteUtility util = new SQLiteUtility(dataSource)) { util.Connect(); string sql = "INSERT INTO kotsuhi" + "(userid, target_date, transport, station_from, station_to, oneway_cost, ido_kbn) " + "VALUES(@userid, @target_date, @transport, @station_from, @station_to, @oneway_cost, @ido_kbn)"; Dictionary <string, dynamic> prams = new Dictionary <string, dynamic> { { "userid", new SessionManager.UserInfo(Page.Session).UserId }, { "target_date", cdrDate.SelectedDate.ToShortDateString() }, { "transport", txtKikan.Text }, { "station_from", txtFrom.Text }, { "station_to", txtTo.Text }, { "oneway_cost", txtKingaku.Text }, { "ido_kbn", drpIkoKbn.SelectedValue } }; util.Execute(sql, prams); } cmdShow_Click(sender, e); } catch (Exception ex) { // 何も見なかったことにする。 } }
protected void Page_Load(object sender, EventArgs e) { string path = Server.MapPath("./sqlite/"); string dataSource = Path.Combine(path, "todo.db"); using (SQLiteUtility util = new SQLiteUtility(dataSource)) { util.Connect(); string sql = "SELECT * FROM Todo"; Dictionary <string, dynamic> parameters = null; DataTable table = util.Fill(sql, parameters); MainRepeater.DataSource = table; MainRepeater.DataBind(); } }
protected void btnInsert_Click(object sender, EventArgs e) { try { string path = Server.MapPath("./sqlite/"); string dataSource = Path.Combine(path, "yougo.db"); using (SQLiteUtility util = new SQLiteUtility(dataSource)) { util.Connect(); string sql = "INSERT INTO yougo VALUES(\"\",\"\",@name,@biko,@URL);"; string name = this.txtNameItem.Text; string biko = this.txtBikoItem.Text; string url = this.txtUrlItem.Text; Dictionary <string, dynamic> parameters = new Dictionary <string, dynamic> { { "name", name }, { "biko", biko }, { "url", url }, }; util.Execute(sql, parameters); sql = "SELECT * FROM yougo"; DataTable dataTable = util.Fill(sql); if (dataTable.Rows.Count > 0) { Repeater1.DataSource = dataTable; Repeater1.DataBind(); } } } catch (Exception ex) { var aaa = ex; } }
void OnDestroy() { Debug.Log("--------------- OnDestroy ------------------------"); UIFacade[] facades = this.transform.Find("UIFacades").GetComponentsInChildren <UIFacade>(); foreach (UIFacade facade in facades) { facade.Cancel(); } foreach (UIFacade facade in uIFacades) { facade.Cancel(); } release(); Error error; SQLiteUtility.Close(Constant.DataBasePath, out error); }
protected void Page_Load(object sender, EventArgs e) { try { string path = Server.MapPath("./sqlite/"); string dataSource = Path.Combine(path, "yougo.db"); using (SQLiteUtility util = new SQLiteUtility(dataSource)) { util.Connect(); string sql = "SELECT * FROM yougo"; string name = this.txtNameItem.Text; string biko = this.txtBikoItem.Text; string url = this.txtUrlItem.Text; Dictionary <string, dynamic> parameters = new Dictionary <string, dynamic> { { "name", name }, { "biko", biko }, { "url", url }, }; DataTable dataTable = util.Fill(sql, parameters); if (dataTable.Rows.Count > 0) { Repeater1.DataSource = dataTable; Repeater1.DataBind(); } } } catch (Exception ex) { this.LabelMessage.Text = ex.Message; } }
public IActionResult CommandSubmit(string commandText) { SQLiteUtility.ExecuteCommand(connectionString, commandText); return(RedirectToAction("Command", "Data", new { area = "DataWarehouse" })); }
protected void cmdShow_Click(object sender, EventArgs e) { DataTable dt = (DataTable)MeisaiData.DataSource; try { string path = Server.MapPath("./sqlite/"); string dataSource = Path.Combine(path, "Kotsuhi.db"); using (SQLiteUtility util = new SQLiteUtility(dataSource)) { util.Connect(); string usr = new SessionManager.UserInfo(Page.Session).UserId; string sql = "SELECT " + " data_id" + ", target_date" + ", transport " + ", station_from" + ", station_to " + ", oneway_cost" + ", ido_kbn " + ", case when ido_kbn = '片道' then oneway_cost else oneway_cost*2 end as cost" + " FROM kotsuhi WHERE userid = '" + usr + "' ORDER BY userid , data_id"; DataTable dataTable = util.Fill(sql, null); dataTable.Columns.Add("day"); dataTable.Columns.Add("station_nyoro"); //dataTable.Columns.Add("cost"); if (dataTable.Rows.Count > 0) { foreach (DataRow dr in dataTable.Rows) { dr["day"] = ""; dr["station_nyoro"] = "~"; //if (dr["ido_kbn"] == "0") //{ // dr["ido_kbn"] = "片道"; // dr["cost"] = dr["oneway_cost"]; //} //else //{ // dr["ido_kbn"] = "往復"; // dr["cost"] = (int.Parse(dr["oneway_cost"].ToString()) * 2).ToString(); //} //dt.Rows.Add(dr); } //dataTable.Rows[0][]; // this.MeisaiData.DataSource = dataTable; //this.MeisaiData.DataBind(); dataTable.AcceptChanges(); this.MeisaiData.DataSource = dataTable; this.MeisaiData.DataBind(); } else { } } } catch (Exception ex) { } }
public IActionResult QuerySubmit(string query) { return(View(SQLiteUtility.GetQueryResult(connectionString, query))); }
public IActionResult QueryExecute(string name) { return(View("QuerySubmit", SQLiteUtility.GetQueryResult(connectionString, savedQueries[name]))); }
public static async Task Run(string path, long startId, bool ignoreHashCheck, bool includeDeleted) { string tempFolderPath = Path.Combine(path, "_temp"); string imageFolderPath = Path.Combine(path, "images"); string metadataDatabasePath = Path.Combine(path, "danbooru.sqlite"); string lastPostJsonPath = Path.Combine(path, "last_post.json"); PathUtility.CreateDirectoryIfNotExists(path); PathUtility.CreateDirectoryIfNotExists(tempFolderPath); PathUtility.CreateDirectoryIfNotExists(imageFolderPath); using (SqliteConnection connection = new SqliteConnection(new SqliteConnectionStringBuilder { DataSource = metadataDatabasePath, }.ToString())) { connection.Open(); SQLiteUtility.TryCreateTable(connection); while (true) { // Get posts metadata as json JObject[] postJObjects = null; await TaskUtility.RunWithRetry(async() => { Log.Info($"Downloading metadata ... ({startId} ~ )"); postJObjects = await DanbooruUtility.GetPosts(startId); }, e => { Log.Error(e); return(true); }, 10, 3000); if (postJObjects.Length == 0) { Log.Info("There is no posts."); break; } // Validate post Log.Info($"Checking {postJObjects.Length} posts ..."); Post[] posts = postJObjects.Select(p => ConvertToPost(p)).Where(p => p != null).ToArray(); Parallel.ForEach(posts, post => { if (string.IsNullOrEmpty(post.Md5)) { Log.Debug($"Skip for empty MD5 : Id={post.Id}"); return; } if (string.IsNullOrEmpty(post.ImageUrl)) { Log.Debug($"Skip for empty image URL : Id={post.Id}"); return; } if (post.IsDeleted && !includeDeleted) { return; } if (post.IsPending) { return; } post.IsValid = true; string metadataPath = GetPostLocalMetadataPath(imageFolderPath, post); try { if (File.Exists(metadataPath)) { Post cachedPost = ConvertToPost(JObject.Parse(File.ReadAllText(metadataPath))); if (cachedPost == null || post.UpdatedDate > cachedPost.UpdatedDate) { post.ShouldSaveMetadata = true; post.ShouldUpdateImage = true; } } else { post.ShouldSaveMetadata = true; post.ShouldUpdateImage = true; } } catch (Exception e) { post.ShouldSaveMetadata = true; Log.Error(e); } string imagePath = GetPostLocalImagePath(imageFolderPath, post); if (!File.Exists(imagePath)) { post.ShouldDownloadImage = true; return; } else { if (post.ShouldUpdateImage || !ignoreHashCheck) { string cachedImageMd5 = GetMd5Hash(imagePath); if (post.Md5 != cachedImageMd5) { post.ShouldDownloadImage = true; Log.Info($"MD5 is different to cached image : Id={post.Id}, {post.Md5} (new) != {cachedImageMd5} (cached)"); return; } } } }); int shouldDownloadCount = posts.Where(p => p.ShouldDownloadImage).Count(); int shouldUpdateCount = posts.Where(p => p.ShouldSaveMetadata).Count(); int pendingCount = posts.Where(p => p.IsPending).Count(); if (shouldUpdateCount > 0 || shouldDownloadCount > 0) { Log.Info($"{shouldUpdateCount}/{posts.Length} posts are updated. {pendingCount} posts are pending. Downloading {shouldDownloadCount} posts ..."); } foreach (Post post in posts) { if (!post.IsValid) { continue; } string metadataPath = GetPostLocalMetadataPath(imageFolderPath, post); string imagePath = GetPostLocalImagePath(imageFolderPath, post); string tempImagePath = GetPostTempImagePath(tempFolderPath, post); PathUtility.CreateDirectoryIfNotExists(Path.GetDirectoryName(imagePath)); try { await TaskUtility.RunWithRetry(async() => { if (post.ShouldDownloadImage) { Log.Info($"Downloading post {post.Id} ..."); await Download(post.ImageUrl, tempImagePath); string downloadedMd5 = GetMd5Hash(tempImagePath); if (downloadedMd5 != post.Md5) { Log.Warn($"MD5 hash of downloaded image is different : Id={post.Id}, {post.Md5} (metadata) != {downloadedMd5} (downloaded)"); try { File.Delete(tempImagePath); } finally { } try { File.Delete(metadataPath); } finally { } throw new Exception(); } File.Delete(imagePath); File.Move(tempImagePath, imagePath); } if (post.ShouldDownloadImage || post.ShouldSaveMetadata) { PathUtility.ChangeFileTimestamp(imagePath, post.CreatedDate, post.UpdatedDate); } if (post.ShouldSaveMetadata) { File.WriteAllText(metadataPath, post.JObject.ToString()); } }, e => { return(!(e is NotRetryableException)); }, 10, 3000); } catch (NotRetryableException) { Log.Error($"Can't retryable exception was occured : Id={post.Id}"); post.IsValid = false; } } Log.Info("Updating database ..."); SQLiteUtility.InsertOrReplace(connection, posts.Where(p => p.IsValid).Select(p => p.JObject)); long lastId = long.Parse(posts.Last().Id); startId = lastId + 1; } try { Directory.Delete(tempFolderPath, true); } catch (Exception e) { Log.Warn(e); } Log.Info("Dump command is complete."); } }
public IActionResult ExportCSV(string query) { return(File(Encoding.UTF8.GetBytes(SQLiteUtility.GetQueryResult(connectionString, query).ConvertToCSV()), "text/csv", "data.csv")); }
public IActionResult CreateTableSubmit(string tableName, IList <TableAddColumnModel> tableColumns) { SQLiteUtility.CreateTable(connectionString, tableName, tableColumns); return(RedirectToAction("Tables", "Data", new { area = "DataWarehouse" })); }
public IActionResult EditTable(string tableName) { TableModel model = SQLiteUtility.GetTableModel(connectionString, tableName); return(View(model)); }
public IActionResult EditRow(string tableName, IList <string> row, IList <string> newRow) { SQLiteUtility.EditRow(connectionString, tableName, row, newRow); return(RedirectToAction("EditTable", "Data", new { area = "DataWarehouse", tableName = tableName })); }
public Error Delete(AccountModel.Account _account) { string query = string.Format("DELETE FROM Account WHERE UUID='{0}'", _account.accountID); return(SQLiteUtility.Execute(connection, query)); }
public IActionResult CommandExecute(string name) { SQLiteUtility.ExecuteCommand(connectionString, savedCommands[name]); return(RedirectToAction("Command", "Data", new { area = "DataWarehouse" })); }