internal void Sync(long rowid) { IsValid = false; _command.Parameters[0].Value = rowid; _reader = _command.ExecuteReader(); _reader.Read(); }
// TODO: Honor behavior public new SQLiteDataReader ExecuteReader(CommandBehavior behavior) { if (OpenReader != null) { throw new InvalidOperationException(Strings.OpenReaderExists); } if (_connection == null || _connection.State != ConnectionState.Open) { throw new InvalidOperationException(Strings.FormatCallRequiresOpenConnection("ExecuteReader")); } if (string.IsNullOrWhiteSpace(_commandText)) { throw new InvalidOperationException(Strings.FormatCallRequiresSetCommandText("ExecuteReader")); } ValidateTransaction(); Prepare(); Bind(); var changes = 0; var resultHandles = new List<StatementHandle>(); foreach (var handle in _handles) { var hasResults = NativeMethods.sqlite3_stmt_readonly(handle) != 0; var rc = NativeMethods.sqlite3_step(handle); if (rc == Constants.SQLITE_ROW || (rc == Constants.SQLITE_DONE && hasResults)) { resultHandles.Add(handle); continue; } rc = NativeMethods.sqlite3_reset(handle); MarshalEx.ThrowExceptionForRC(rc); changes += NativeMethods.sqlite3_changes(_connection.Handle); } return OpenReader = new SQLiteDataReader(this, resultHandles, changes); }
/// <summary> /// This function does all the nasty work at determining what keys need to be returned for /// a given statement. /// </summary> /// <param name="cnn"></param> /// <param name="reader"></param> /// <param name="stmt"></param> internal SQLiteKeyReader(SQLiteConnection cnn, SQLiteDataReader reader, SQLiteStatement stmt) { Dictionary<string, int> catalogs = new Dictionary<string, int>(); Dictionary<string, List<string>> tables = new Dictionary<string, List<string>>(); List<string> list; List<KeyInfo> keys = new List<KeyInfo>(); // Record the statement so we can use it later for sync'ing _stmt = stmt; // Fetch all the attached databases on this connection using (DataTable tbl = cnn.GetSchema("Catalogs")) { foreach (DataRow row in tbl.Rows) { catalogs.Add((string)row["CATALOG_NAME"], Convert.ToInt32(row["ID"], CultureInfo.InvariantCulture)); } } // Fetch all the unique tables and catalogs used by the current statement using (DataTable schema = reader.GetSchemaTable(false, false)) { foreach (DataRow row in schema.Rows) { // Check if column is backed to a table if (row[SchemaTableOptionalColumn.BaseCatalogName] == DBNull.Value) continue; // Record the unique table so we can look up its keys string catalog = (string)row[SchemaTableOptionalColumn.BaseCatalogName]; string table = (string)row[SchemaTableColumn.BaseTableName]; if (tables.ContainsKey(catalog) == false) { list = new List<string>(); tables.Add(catalog, list); } else list = tables[catalog]; if (list.Contains(table) == false) list.Add(table); } // For each catalog and each table, query the indexes for the table. // Find a primary key index if there is one. If not, find a unique index instead foreach (KeyValuePair<string, List<string>> pair in tables) { for (int i = 0; i < pair.Value.Count; i++) { string table = pair.Value[i]; DataRow preferredRow = null; using (DataTable tbl = cnn.GetSchema("Indexes", new string[] { pair.Key, null, table })) { // Loop twice. The first time looking for a primary key index, // the second time looking for a unique index for (int n = 0; n < 2 && preferredRow == null; n++) { foreach (DataRow row in tbl.Rows) { if (n == 0 && (bool)row["PRIMARY_KEY"] == true) { preferredRow = row; break; } else if (n == 1 && (bool)row["UNIQUE"] == true) { preferredRow = row; break; } } } if (preferredRow == null) // Unable to find any suitable index for this table so remove it { pair.Value.RemoveAt(i); i--; } else // We found a usable index, so fetch the necessary table details { using (DataTable tblTables = cnn.GetSchema("Tables", new string[] { pair.Key, null, table })) { // Find the root page of the table in the current statement and get the cursor that's iterating it int database = catalogs[pair.Key]; int rootPage = Convert.ToInt32(tblTables.Rows[0]["TABLE_ROOTPAGE"], CultureInfo.InvariantCulture); int cursor = stmt._sql.GetCursorForTable(stmt, database, rootPage); // Now enumerate the members of the index we're going to use using (DataTable indexColumns = cnn.GetSchema("IndexColumns", new string[] { pair.Key, null, table, (string)preferredRow["INDEX_NAME"] })) { KeyQuery query = null; List<string> cols = new List<string>(); for (int x = 0; x < indexColumns.Rows.Count; x++) { bool addKey = true; // If the column in the index already appears in the query, skip it foreach (DataRow row in schema.Rows) { if (row.IsNull(SchemaTableColumn.BaseColumnName)) continue; if ((string)row[SchemaTableColumn.BaseColumnName] == (string)indexColumns.Rows[x]["COLUMN_NAME"] && (string)row[SchemaTableColumn.BaseTableName] == table && (string)row[SchemaTableOptionalColumn.BaseCatalogName] == pair.Key) { indexColumns.Rows.RemoveAt(x); x--; addKey = false; break; } } if (addKey == true) cols.Add((string)indexColumns.Rows[x]["COLUMN_NAME"]); } // If the index is not a rowid alias, record all the columns // needed to make up the unique index and construct a SQL query for it if ((string)preferredRow["INDEX_NAME"] != "sqlite_master_PK_" + table) { // Whatever remains of the columns we need that make up the index that are not // already in the query need to be queried separately, so construct a subquery if (cols.Count > 0) { string[] querycols = new string[cols.Count]; cols.CopyTo(querycols); query = new KeyQuery(cnn, pair.Key, table, querycols); } } // Create a KeyInfo struct for each column of the index for (int x = 0; x < indexColumns.Rows.Count; x++) { string columnName = (string)indexColumns.Rows[x]["COLUMN_NAME"]; KeyInfo key = new KeyInfo(); key.rootPage = rootPage; key.cursor = cursor; key.database = database; key.databaseName = pair.Key; key.tableName = table; key.columnName = columnName; key.query = query; key.column = x; keys.Add(key); } } } } } } } } // Now we have all the additional columns we have to return in order to support // CommandBehavior.KeyInfo _keyInfo = new KeyInfo[keys.Count]; keys.CopyTo(_keyInfo); }
private int?ReadNullableInt(SQLiteDataReader reader, string columnName) { var value = reader[columnName]; return(value == DBNull.Value ? (int?)null : Convert.ToInt32(value)); }
//读取数据库配置到From1 static public void ReadConfigsFormSQL() { int count = 0; SQLiteConnection conn = null; string strSQLiteDB = Environment.CurrentDirectory; // 获取目录 try { string dbPath = "Data Source=" + strSQLiteDB + "\\data.db"; conn = new SQLiteConnection(dbPath); //创建数据库实例,指定文件位置 conn.Open(); //打开数据库,若文件不存在会自动创建 string sql = "CREATE TABLE IF NOT EXISTS config(key varchar(250), value varchar(250));"; //建表语句 ,key为配置,value为值 SQLiteCommand cmdCreateTable = new SQLiteCommand(sql, conn); cmdCreateTable.ExecuteNonQuery(); //如果表不存在,创建数据表 //连接到数据库文件,使用PRAGMA命令准备 SQLiteCommand cmd = conn.CreateCommand(); cmd.CommandText = "SELECT * FROM config"; SQLiteDataReader dataReader = cmd.ExecuteReader(); while (dataReader.Read()) { count++; String key = dataReader.GetString(0); if (key == "NameOfCompany") { Form1.NameOfCompany = dataReader.GetString(1); } if (key == "LogoPath") { Form1.LogoPath = dataReader.GetString(1); } if (key == "MultipleLuck") { String multipleLuck = dataReader.GetString(1); Form1.MultipleLuck = Boolean.Parse(multipleLuck); } if (key == "LuckType") { String sweepstakeType = dataReader.GetString(1); Form1.LuckType = Boolean.Parse(sweepstakeType); } if (key == "HideLuckType") { String hideLuckType = dataReader.GetString(1); Form1.HideLuckType = Boolean.Parse(hideLuckType); } if (key == "ProgramTitle") { String programTitle = dataReader.GetString(1); Form1.ProgramTitle = Boolean.Parse(programTitle); } } dataReader.Close(); conn.Close(); } catch (Exception ex) { throw; // <------------------- } return; }
private void LoadParameter() { string sql_command = ""; //0 , 1 , 2 , 3 , 4 , 5 , 6 sql_command += "Select S7Adress, S7Symbol, S7SymbolType, S7Comment, SymbolType, cast(RankingGroup as integer) as RankingGroup, GroupComment, "; //7 , 8 , 9 , 10 , 11 , 12 , 13 , 14 sql_command += "cast(RankingSymbol as integer) as RankingSymbol, Comment, Unit, SymbolFormat, UpperLimit, LowerLimit, UserRightEnable, UserRightVisible, "; //15 sql_command += "Column "; if (this.m_formularType == FormularType.Parameter) { sql_command += "from plcitems where (SymbolType='P' or SymbolType='PO') order by RankingGroup, RankingSymbol"; } if (this.m_formularType == FormularType.Info) { sql_command += "from plcitems where (SymbolType='I') order by RankingGroup, RankingSymbol"; } if (this.m_formularType == FormularType.Service) { sql_command += "from plcitems where (SymbolType='S') order by RankingGroup, RankingSymbol"; } this.m_sqliteCommand.CommandText = sql_command; if (this.m_sqliteDataReader != null) { this.m_sqliteDataReader.Close(); this.m_sqliteDataReader = null; } this.m_sqliteDataReader = this.m_sqliteCommand.ExecuteReader(); int i = 0; TabControl tab_control = this.CreateTabControl(4, 20, this.GbxOutput.Width - 8, this.GbxOutput.Height - 25, this.GbxOutput); tab_control.TabStop = false; this.m_tabControl = tab_control; TabPage tab_page = null; GroupBox group_box = null; Label label_text = null; Label label_unit = null; CompToggleSwitch toggle_switch = null; CompTxtBox txt_box = null; CompInputBox input_box = null; PlcItemList plc_item_list; CompLedRectangle led_rectangle = null; string old_group_comment = ""; int row = 0; int old_ranging_symbol = 0; bool first_start = true; while (this.m_sqliteDataReader.Read()) { plc_item_list.S7Adress = this.m_sqliteDataReader.GetValue(0).ToString(); plc_item_list.S7Symbol = this.m_sqliteDataReader.GetValue(1).ToString(); plc_item_list.S7SymbolType = this.m_sqliteDataReader.GetValue(2).ToString(); plc_item_list.SymbolType = this.m_sqliteDataReader.GetValue(4).ToString(); plc_item_list.GroupComment = this.m_sqliteDataReader.GetValue(6).ToString(); plc_item_list.RankingSymbol = Convert.ToInt32(this.m_sqliteDataReader.GetValue(7).ToString()); if (first_start) { first_start = false; old_ranging_symbol = plc_item_list.RankingSymbol + 1; } plc_item_list.Comment = this.m_sqliteDataReader.GetValue(8).ToString(); plc_item_list.Unit = this.m_sqliteDataReader.GetValue(9).ToString(); plc_item_list.Format = this.m_sqliteDataReader.GetValue(10).ToString(); plc_item_list.Column = Convert.ToInt32(this.m_sqliteDataReader.GetValue(15).ToString()); plc_item_list.UpperLimit = Convert.ToDouble(this.m_sqliteDataReader.GetValue(11).ToString()); plc_item_list.LowerLimit = Convert.ToDouble(this.m_sqliteDataReader.GetValue(12).ToString()); plc_item_list.UserRightEnable = Convert.ToInt32(this.m_sqliteDataReader.GetValue(13).ToString()); string[] adress_info = plc_item_list.S7Adress.Split('.'); string db_number = adress_info[0]; string varname = db_number + "." + plc_item_list.S7Symbol; plc_item_list.Varname = varname; if (plc_item_list.GroupComment != old_group_comment) { old_ranging_symbol = plc_item_list.RankingSymbol - 1; row = 0; old_group_comment = plc_item_list.GroupComment; tab_page = (TabPage)this.CreateTabPage(plc_item_list.GroupComment, tab_control); group_box = (GroupBox)this.CreateGroupBox(4, 4, tab_control.Width - 18, tab_control.Height - 56, old_group_comment, tab_page); } int top = row * (GlobalVar.ConstParameterTextHeigt + GlobalVar.ConstControlSpacing) + GlobalVar.ConstGroupBoxOffset; int text_width = group_box.Width - (5 * GlobalVar.ConstControlSpacing + GlobalVar.ConstParameterTxtBoxWidth + GlobalVar.ConstParameterTxtBoxWidth + GlobalVar.ConstParameterTxtBoxWidth) - GlobalVar.ConstOffsetCorrectWidth; label_text = (Label)this.CreateLabel(GlobalVar.ConstControlSpacing, top, text_width, GlobalVar.ConstParameterTextHeigt, plc_item_list.Comment, group_box, System.Drawing.ContentAlignment.MiddleRight); int left_unit = 0; if (plc_item_list.S7SymbolType == "BOOL") { if (plc_item_list.SymbolType == "P" || plc_item_list.SymbolType == "S") { toggle_switch = (CompToggleSwitch)this.CreateToggleSwitch(GlobalVar.ConstControlSpacing + label_text.Width + label_text.Left, top, group_box); toggle_switch.Click += new System.EventHandler(this.ToggleSwitch_Click); toggle_switch.Symbol = varname; toggle_switch.DoSwitch = false; this.m_dataBinding.AddList(this, toggle_switch.Name.ToString(), "State", varname); led_rectangle = this.CreateLedRectangle(GlobalVar.ConstControlSpacing + toggle_switch.Width + toggle_switch.Left, top, group_box); this.m_dataBinding.AddList(this, led_rectangle.Name.ToString(), "State", varname); this.m_userManagement.AddUserRightControl(toggle_switch, plc_item_list.UserRightEnable); } if (plc_item_list.SymbolType == "I") { led_rectangle = this.CreateLedRectangle(GlobalVar.ConstControlSpacing + label_text.Width + label_text.Left, top, group_box); this.m_dataBinding.AddList(this, led_rectangle.Name.ToString(), "State", varname); } } else { if (plc_item_list.SymbolType == "PO" || plc_item_list.SymbolType == "I" || plc_item_list.SymbolType == "SO") { int left = GlobalVar.ConstControlSpacing + (plc_item_list.Column - 1) * (GlobalVar.ConstParameterTxtBoxWidth + GlobalVar.ConstControlSpacing) + label_text.Left + label_text.Width; txt_box = this.CreateTxtBox(left, top, plc_item_list.Format, group_box); this.m_dataBinding.AddList(this, txt_box.Name.ToString(), "Text", varname); left_unit = txt_box.Left + txt_box.Width + GlobalVar.ConstControlSpacing; } if (plc_item_list.SymbolType == "P" || plc_item_list.SymbolType == "S") { int left = GlobalVar.ConstControlSpacing + (plc_item_list.Column - 1) * (GlobalVar.ConstParameterTxtBoxWidth + GlobalVar.ConstControlSpacing) + label_text.Left + label_text.Width; input_box = this.CreateInputBox(left, top, plc_item_list.Format, group_box); input_box.Symbol = varname; input_box.KeyPress += new System.Windows.Forms.KeyPressEventHandler(this.InputBox_KeyPress); input_box.Leave += new System.EventHandler(this.InputBox_Leave); this.m_dataBinding.AddList(this, input_box.Name.ToString(), "Text", varname); left_unit = input_box.Left + input_box.Width + GlobalVar.ConstControlSpacing; this.m_plcItemList.Add(input_box, plc_item_list); this.m_userManagement.AddUserRightControl(input_box, plc_item_list.UserRightEnable); left_unit = input_box.Left + input_box.Width + GlobalVar.ConstControlSpacing; } } label_unit = (Label)this.CreateLabel(left_unit, top, GlobalVar.ConstParameterTxtBoxWidth, GlobalVar.ConstParameterTextHeigt, plc_item_list.Unit, group_box, System.Drawing.ContentAlignment.MiddleLeft); if (plc_item_list.RankingSymbol != old_ranging_symbol) { old_ranging_symbol = plc_item_list.RankingSymbol; row++; } i++; } this.m_sqliteDataReader.Close(); this.m_sqliteDataReader = null; }
private int ReadInt(SQLiteDataReader reader, string columnName) { return(Convert.ToInt32(reader[columnName])); }
private void Table() { Application.VisualStyleState = VisualStyleState.NoneEnabled; pnlNav.Height = sum.Height; pnlNav.Top = sum.Top; pnlNav.Left = sum.Left; Info.BackColor = Color.FromArgb(24, 30, 54); sum.BackColor = Color.FromArgb(46, 51, 73); btnCal.BackColor = Color.FromArgb(24, 30, 54); btnDashboard.BackColor = Color.FromArgb(24, 30, 54); statPanel.Hide(); carbPanel.Hide(); fatPanel.Hide(); protPanel.Hide(); infoLabel.Hide(); calPanel.Hide(); add.Hide(); calendar.Hide(); all.Columns.Clear(); all.Columns.Add("ID", "ID"); all.Columns.Add("Username", "Felhasználónév"); all.Columns.Add("Food", "Étel/Ital"); all.Columns.Add("cal", "Kalória"); all.Columns.Add("prot", "Fehérje"); all.Columns.Add("fat", "Zsír"); all.Columns.Add("carb", "Szénhidrát"); all.Columns.Add("date", "Dátum"); all.Columns["ID"].Visible = false; all.Size = new Size(625, 400); all.Location = new Point(statusLabel.Left, 75); all.AllowUserToAddRows = false; all.AllowUserToDeleteRows = false; all.ForeColor = Color.White; all.GridColor = Color.FromArgb(158, 161, 176); all.BorderStyle = BorderStyle.None; all.SelectionMode = DataGridViewSelectionMode.FullRowSelect; all.ScrollBars = ScrollBars.Vertical; all.EnableHeadersVisualStyles = false; all.MultiSelect = false; all.RowHeadersVisible = false; all.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill; all.ColumnHeadersDefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter; all.ColumnHeadersBorderStyle = DataGridViewHeaderBorderStyle.Single; all.ColumnHeadersDefaultCellStyle.SelectionBackColor = Color.FromArgb(46, 51, 73); all.DefaultCellStyle.BackColor = Color.FromArgb(46, 51, 73); all.AlternatingRowsDefaultCellStyle.BackColor = Color.FromArgb(46, 51, 73); all.ColumnHeadersDefaultCellStyle.BackColor = Color.FromArgb(46, 51, 73); all.ColumnHeadersDefaultCellStyle.ForeColor = Color.White; all.RowHeadersDefaultCellStyle.BackColor = Color.FromArgb(46, 51, 73); all.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; all.Columns["Food"].ValueType = typeof(String); all.BackgroundColor = Color.FromArgb(46, 51, 73); all.BackColor = Color.FromArgb(46, 51, 73); this.Controls.Add(all); LogBase userData = new LogBase(); userData.OpenConnection(); string query = "SELECT ROWID, * FROM DATAS WHERE USERNAME='******' AND DATE='" + DateTime.Now.ToString("yyyyMMdd") + "'"; SQLiteCommand ossz = new SQLiteCommand(query, userData.myConnection); SQLiteDataReader result = ossz.ExecuteReader(); if (result.HasRows) { while (result.Read()) { string[] temp = Convert.ToString(result["Cal"]).Split('/'); var tempDate = DateTime.ParseExact(result["Date"].ToString(), "yyyyMMdd", CultureInfo.InvariantCulture).ToString("yyyy. MM. dd."); all.Rows.Add(result["ROWID"], result["Username"], result["Food"], temp[1] + " kcal", result["Prot"] + " g", result["Fat"] + " g", result["carb"] + " g", tempDate.ToString()); this.Controls.Add(all); } } delete.Location = new Point(862, 515); delete.Size = new Size(100, 50); delete.Image = Image.FromFile("delete.png"); delete.SizeMode = PictureBoxSizeMode.Zoom; delete.Cursor = Cursors.Hand; this.Controls.Add(delete); delete.Show(); }
private string ReadString(SQLiteDataReader reader, string columnName) { return(reader[columnName].ToString()); }
public Food(string name) { this.calendar.ValueChanged += new System.EventHandler(this.calendar_ValueChanged); this.delete.Click += new System.EventHandler(this.delete_Click); username = name; InitializeComponent(); Region = System.Drawing.Region.FromHrgn(CreateRoundRectRgn(0, 0, Width, Height, 25, 25)); userName.Text = name; pnlNav.Height = btnDashboard.Height; pnlNav.Top = btnDashboard.Top; pnlNav.Left = btnDashboard.Left; calendar.Location = new Point(statusLabel.Left, 55); this.Controls.Add(calendar); calendar.Hide(); btnDashboard.BackColor = Color.FromArgb(46, 51, 73); LogBase userData = new LogBase(); string query = "SELECT * FROM USER WHERE USERNAME='******'"; userData.OpenConnection(); SQLiteCommand getUser = new SQLiteCommand(query, userData.myConnection); SQLiteDataReader result = getUser.ExecuteReader(); double calFull = 0; double calBevitt = 0; if (result.HasRows) { while (result.Read()) { if (Convert.ToInt32(result["Sex"]) == 1) { calFull = 66.46 + (13.7 * Convert.ToInt32(result["Weight"])) + (5 * Convert.ToInt32(result["Height"])) - (6.8 * Convert.ToInt32(result["Age"])); } if (Convert.ToInt32(result["Sex"]) == 0) { calFull = 655.1 + (9.6 * Convert.ToInt32(result["Weight"])) + (1.8 * Convert.ToInt32(result["Height"])) - (4.7 * Convert.ToInt32(result["Age"])); } goal = Convert.ToInt32(result["Goal"]); } } query = "SELECT * FROM DATAS WHERE USERNAME='******'"; SQLiteCommand getData = new SQLiteCommand(query, userData.myConnection); result = getData.ExecuteReader(); double carbBevitt = 0; double protBevitt = 0; double fatBevitt = 0; if (result.HasRows) { while (result.Read()) { if (DateTime.Now.ToString("yyyyMMdd") == result["Date"].ToString()) { string[] temp = Convert.ToString(result["Cal"]).Split('/'); calBevitt = Convert.ToDouble(temp[1]) + calBevitt; carbBevitt = carbBevitt + Convert.ToDouble(result["Carb"]); protBevitt = protBevitt + Convert.ToDouble(result["Prot"]); fatBevitt = fatBevitt + Convert.ToDouble(result["Fat"]); } } } userData.CloseConnection(); if (goal == 0) { calFull -= 200; } if (goal == 1) { calFull += 500; } double carbFull = (calFull * 0.45) / 4; double protFull = (calFull * 0.35) / 4; double fatFull = (calFull * 0.20) / 8; double percent = Math.Round((calBevitt / calFull) * 100, MidpointRounding.AwayFromZero); if (percent < 100) { statPercent.Text = percent.ToString() + "%"; statPercent.Value = Convert.ToInt32(percent); } else { statPercent.Text = percent.ToString() + "%"; statPercent.Value = 100; } fatLabel.Text = Math.Round(fatBevitt, MidpointRounding.AwayFromZero) + "/" + Math.Round(fatFull, MidpointRounding.AwayFromZero) + " g"; protLabel.Text = Math.Round(protBevitt, MidpointRounding.AwayFromZero) + "/" + Math.Round(protFull, MidpointRounding.AwayFromZero) + " g"; carbLabel.Text = Math.Round(carbBevitt, MidpointRounding.AwayFromZero) + "/" + Math.Round(carbFull, MidpointRounding.AwayFromZero) + " g"; cal.Text = calBevitt + "/" + Math.Ceiling(calFull) + " kcal"; }
private void Food_Activated(object sender, EventArgs e) { LogBase userData = new LogBase(); string query = "SELECT * FROM USER WHERE USERNAME='******'"; userData.OpenConnection(); SQLiteCommand getUser = new SQLiteCommand(query, userData.myConnection); SQLiteDataReader result = getUser.ExecuteReader(); double calFull = 0; double calBevitt = 0; if (result.HasRows) { while (result.Read()) { if (Convert.ToInt32(result["Sex"]) == 1) { calFull = 66.46 + (13.7 * Convert.ToInt32(result["Weight"])) + (5 * Convert.ToInt32(result["Height"])) - (6.8 * Convert.ToInt32(result["Age"])); } if (Convert.ToInt32(result["Sex"]) == 0) { calFull = 655.1 + (9.6 * Convert.ToInt32(result["Weight"])) + (1.8 * Convert.ToInt32(result["Height"])) - (4.7 * Convert.ToInt32(result["Age"])); } goal = Convert.ToInt32(result["Goal"]); } } query = "SELECT * FROM DATAS WHERE USERNAME='******'"; SQLiteCommand getData = new SQLiteCommand(query, userData.myConnection); result = getData.ExecuteReader(); double carbBevitt = 0; double protBevitt = 0; double fatBevitt = 0; if (result.HasRows) { while (result.Read()) { if (DateTime.Now.ToString("yyyyMMdd") == result["Date"].ToString()) { string[] temp = Convert.ToString(result["Cal"]).Split('/'); calBevitt = Convert.ToDouble(temp[1]) + calBevitt; carbBevitt = carbBevitt + Convert.ToDouble(result["Carb"]); protBevitt = protBevitt + Convert.ToDouble(result["Prot"]); fatBevitt = fatBevitt + Convert.ToDouble(result["Fat"]); } } } userData.CloseConnection(); if (goal == 0) { calFull -= 200; } if (goal == 1) { calFull += 500; } double carbFull = (calFull * 0.45) / 4; double protFull = (calFull * 0.35) / 4; double fatFull = (calFull * 0.20) / 8; double percent = Math.Round((calBevitt / calFull) * 100, MidpointRounding.AwayFromZero); if (percent < 100) { statPercent.Text = percent.ToString() + "%"; statPercent.Value = Convert.ToInt32(percent); } else { statPercent.Text = percent.ToString() + "%"; statPercent.Value = 100; } fatLabel.Text = Math.Round(fatBevitt, MidpointRounding.AwayFromZero) + "/" + Math.Round(fatFull, MidpointRounding.AwayFromZero) + " g"; protLabel.Text = Math.Round(protBevitt, MidpointRounding.AwayFromZero) + "/" + Math.Round(protFull, MidpointRounding.AwayFromZero) + " g"; carbLabel.Text = Math.Round(carbBevitt, MidpointRounding.AwayFromZero) + "/" + Math.Round(carbFull, MidpointRounding.AwayFromZero) + " g"; cal.Text = calBevitt + "/" + Math.Ceiling(calFull) + " kcal"; }
private void setCalendar() { if (Convert.ToDateTime(calendar.Value) > DateTime.Now) { Application.VisualStyleState = VisualStyleState.ClientAndNonClientAreasEnabled; MessageBox.Show("A kiválasztott dátum nem lehet későbbi mint a mai dátum!"); } else { LogBase userData = new LogBase(); statPanel.Show(); carbPanel.Show(); fatPanel.Show(); protPanel.Show(); calPanel.Show(); string query = "SELECT * FROM USER WHERE USERNAME='******'"; userData.OpenConnection(); SQLiteCommand getUser = new SQLiteCommand(query, userData.myConnection); SQLiteDataReader result = getUser.ExecuteReader(); double calFull = 0; double calBevitt = 0; if (result.HasRows) { while (result.Read()) { if (Convert.ToInt32(result["Sex"]) == 1) { calFull = 66.46 + (13.7 * Convert.ToInt32(result["Weight"])) + (5 * Convert.ToInt32(result["Height"])) - (6.8 * Convert.ToInt32(result["Age"])); } if (Convert.ToInt32(result["Sex"]) == 0) { calFull = 655.1 + (9.6 * Convert.ToInt32(result["Weight"])) + (1.8 * Convert.ToInt32(result["Height"])) - (4.7 * Convert.ToInt32(result["Age"])); } goal = Convert.ToInt32(result["Goal"]); } } query = "SELECT * FROM DATAS WHERE USERNAME='******' AND DATE='" + Convert.ToDateTime(calendar.Value).ToString("yyyyMMdd") + "'"; SQLiteCommand keres = new SQLiteCommand(query, userData.myConnection); result = keres.ExecuteReader(); double carbBevitt = 0; double protBevitt = 0; double fatBevitt = 0; if (result.HasRows) { while (result.Read()) { string[] temp = Convert.ToString(result["Cal"]).Split('/'); calBevitt = Convert.ToDouble(temp[1]) + calBevitt; carbBevitt = carbBevitt + Convert.ToDouble(result["Carb"]); protBevitt = protBevitt + Convert.ToDouble(result["Prot"]); fatBevitt = fatBevitt + Convert.ToDouble(result["Fat"]); } } userData.CloseConnection(); if (goal == 0) { calFull -= 200; } if (goal == 1) { calFull += 500; } double carbFull = (calFull * 0.45) / 4; double protFull = (calFull * 0.35) / 4; double fatFull = (calFull * 0.20) / 8; double percent = Math.Round((calBevitt / calFull) * 100, MidpointRounding.AwayFromZero); if (percent < 100) { statPercent.Text = percent.ToString() + "%"; statPercent.Value = Convert.ToInt32(percent); } else { statPercent.Text = percent.ToString() + "%"; statPercent.Value = 100; } fatLabel.Text = Math.Round(fatBevitt, MidpointRounding.AwayFromZero) + "/" + Math.Round(fatFull, MidpointRounding.AwayFromZero) + " g"; protLabel.Text = Math.Round(protBevitt, MidpointRounding.AwayFromZero) + "/" + Math.Round(protFull, MidpointRounding.AwayFromZero) + " g"; carbLabel.Text = Math.Round(carbBevitt, MidpointRounding.AwayFromZero) + "/" + Math.Round(carbFull, MidpointRounding.AwayFromZero) + " g"; cal.Text = calBevitt + "/" + Math.Ceiling(calFull) + " kcal"; dailyCal.Text = "Összesen elfogyasztott kalória\n" + Convert.ToDateTime(calendar.Value).ToString("yyyy. MM. dd."); dailyFat.Text = "Összesen elfogyasztott zsír\n" + Convert.ToDateTime(calendar.Value).ToString("yyyy. MM. dd."); dailyProt.Text = "Összesen elfogyasztott fehérje\n" + Convert.ToDateTime(calendar.Value).ToString("yyyy. MM. dd."); dailyCarb.Text = "Összesen elfogyasztott szénhidrát\n" + Convert.ToDateTime(calendar.Value).ToString("yyyy. MM. dd."); dailyPercent.Text = "Összesen elfogyasztott kalória %\n" + Convert.ToDateTime(calendar.Value).ToString("yyyy. MM. dd."); } }
private void btnDashboard_Click(object sender, EventArgs e) { Application.VisualStyleState = VisualStyleState.ClientAndNonClientAreasEnabled; pnlNav.Height = btnDashboard.Height; pnlNav.Top = btnDashboard.Top; pnlNav.Left = btnDashboard.Left; btnDashboard.BackColor = Color.FromArgb(46, 51, 73); btnCal.BackColor = Color.FromArgb(24, 30, 54); Info.BackColor = Color.FromArgb(24, 30, 54); infoLabel.Hide(); statPanel.Show(); carbPanel.Show(); fatPanel.Show(); protPanel.Show(); calPanel.Show(); add.Show(); calendar.Hide(); all.Hide(); delete.Hide(); dailyCal.Text = "Összesen elfogyasztott kalória"; dailyFat.Text = "Összesen elfogyasztott zsír"; dailyProt.Text = "Összesen elfogyasztott fehérje"; dailyCarb.Text = "Összesen elfogyasztott szénhidrát"; dailyPercent.Text = "Összesen elfogyasztott kalória %"; sum.BackColor = Color.FromArgb(24, 30, 54); LogBase userData = new LogBase(); string query = "SELECT * FROM USER WHERE USERNAME='******'"; userData.OpenConnection(); SQLiteCommand getUser = new SQLiteCommand(query, userData.myConnection); SQLiteDataReader result = getUser.ExecuteReader(); double calFull = 0; double calBevitt = 0; if (result.HasRows) { while (result.Read()) { if (Convert.ToInt32(result["Sex"]) == 1) { calFull = 66.46 + (13.7 * Convert.ToInt32(result["Weight"])) + (5 * Convert.ToInt32(result["Height"])) - (6.8 * Convert.ToInt32(result["Age"])); } if (Convert.ToInt32(result["Sex"]) == 0) { calFull = 655.1 + (9.6 * Convert.ToInt32(result["Weight"])) + (1.8 * Convert.ToInt32(result["Height"])) - (4.7 * Convert.ToInt32(result["Age"])); } goal = Convert.ToInt32(result["Goal"]); } } query = "SELECT * FROM DATAS WHERE USERNAME='******'"; SQLiteCommand getData = new SQLiteCommand(query, userData.myConnection); result = getData.ExecuteReader(); double carbBevitt = 0; double protBevitt = 0; double fatBevitt = 0; if (result.HasRows) { while (result.Read()) { if (DateTime.Now.ToString("yyyyMMdd") == result["Date"].ToString()) { string[] temp = Convert.ToString(result["Cal"]).Split('/'); calBevitt = Convert.ToDouble(temp[1]) + calBevitt; carbBevitt = carbBevitt + Convert.ToDouble(result["Carb"]); protBevitt = protBevitt + Convert.ToDouble(result["Prot"]); fatBevitt = fatBevitt + Convert.ToDouble(result["Fat"]); } } } userData.CloseConnection(); if (goal == 0) { calFull -= 200; } if (goal == 1) { calFull += 500; } double carbFull = (calFull * 0.45) / 4; double protFull = (calFull * 0.35) / 4; double fatFull = (calFull * 0.20) / 8; double percent = Math.Round((calBevitt / calFull) * 100, MidpointRounding.AwayFromZero); if (percent < 100) { statPercent.Text = percent.ToString() + "%"; statPercent.Value = Convert.ToInt32(percent); } else { statPercent.Text = percent.ToString() + "%"; statPercent.Value = 100; } fatLabel.Text = Math.Round(fatBevitt, MidpointRounding.AwayFromZero) + "/" + Math.Round(fatFull, MidpointRounding.AwayFromZero) + " g"; protLabel.Text = Math.Round(protBevitt, MidpointRounding.AwayFromZero) + "/" + Math.Round(protFull, MidpointRounding.AwayFromZero) + " g"; carbLabel.Text = Math.Round(carbBevitt, MidpointRounding.AwayFromZero) + "/" + Math.Round(carbFull, MidpointRounding.AwayFromZero) + " g"; cal.Text = calBevitt + "/" + Math.Ceiling(calFull) + " kcal"; statusLabel.Text = "Főoldal"; }
private void bwPopuate_DoWork(object sender, DoWorkEventArgs e) { string kit = (string)e.Argument; SQLiteConnection cnn = GGKUtilLib.getDBConnection(); //kit master SQLiteCommand query = new SQLiteCommand(@"SELECT name, sex from kit_master where kit_no=@kit_no", cnn); query.Parameters.AddWithValue("@kit_no", kit); SQLiteDataReader reader = query.ExecuteReader(); string sex = "U"; if (reader.Read()) { this.Invoke(new MethodInvoker(delegate { txtName.Text = reader.GetString(0); sex = reader.GetString(1); if (sex == "U") { cbSex.SelectedIndex = 0; } else if (sex == "M") { cbSex.SelectedIndex = 1; } else if (sex == "F") { cbSex.SelectedIndex = 2; } })); } reader.Close(); query.Dispose(); // kit autosomal - RSID,Chromosome,Position,Genotypoe query = new SQLiteCommand(@"SELECT rsid 'RSID',chromosome 'Chromosome',position 'Position',genotype 'Genotype' from kit_autosomal where kit_no=@kit_no order by chromosome,position", cnn); query.Parameters.AddWithValue("@kit_no", kit); reader = query.ExecuteReader(); DataTable dt = new DataTable(); dt.Load(reader); this.Invoke(new MethodInvoker(delegate { dataGridViewAutosomal.Rows.Clear(); dataGridViewAutosomal.Columns.Clear(); dataGridViewAutosomal.DataSource = dt; dataGridViewAutosomal.Columns[0].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; dataGridViewAutosomal.Columns[1].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; dataGridViewAutosomal.Columns[2].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; dataGridViewAutosomal.Columns[3].AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill; ((DataGridViewTextBoxColumn)dataGridViewAutosomal.Columns[0]).MaxInputLength = 20; ((DataGridViewTextBoxColumn)dataGridViewAutosomal.Columns[1]).MaxInputLength = 2; ((DataGridViewTextBoxColumn)dataGridViewAutosomal.Columns[2]).MaxInputLength = 15; ((DataGridViewTextBoxColumn)dataGridViewAutosomal.Columns[3]).MaxInputLength = 2; })); reader.Close(); query.Dispose(); // kit - ysnp query = new SQLiteCommand(@"SELECT ysnps from kit_ysnps where kit_no=@kit_no", cnn); query.Parameters.AddWithValue("@kit_no", kit); reader = query.ExecuteReader(); if (reader.Read()) { ysnps = reader.GetString(0); } reader.Close(); query.Dispose(); this.Invoke(new MethodInvoker(delegate { textBoxYDNA.Text = ysnps; })); // kit - ystr query = new SQLiteCommand(@"SELECT marker,value from kit_ystr where kit_no=@kit_no", cnn); query.Parameters.AddWithValue("@kit_no", kit); reader = query.ExecuteReader(); string marker = null; string value = null; Dictionary <string, string> ystr_dict = new Dictionary <string, string>(); while (reader.Read()) { marker = reader.GetString(0); value = reader.GetString(1); ystr_dict.Add(marker, value); } reader.Close(); query.Dispose(); this.Invoke(new MethodInvoker(delegate { // //YDNA12 for (int i = 0; i < ydna12.Length; i++) { if (ystr_dict.ContainsKey(ydna12[i])) { dgvy12.Rows[i].Cells[1].Value = ystr_dict[ydna12[i]]; ystr_dict.Remove(ydna12[i]); } } //YDNA25 for (int i = 0; i < ydna25.Length; i++) { if (ystr_dict.ContainsKey(ydna25[i])) { dgvy25.Rows[i].Cells[1].Value = ystr_dict[ydna25[i]]; ystr_dict.Remove(ydna25[i]); } } //YDNA37 for (int i = 0; i < ydna37.Length; i++) { if (ystr_dict.ContainsKey(ydna37[i])) { dgvy37.Rows[i].Cells[1].Value = ystr_dict[ydna37[i]]; ystr_dict.Remove(ydna37[i]); } } //YDNA67 for (int i = 0; i < ydna67.Length; i++) { if (ystr_dict.ContainsKey(ydna67[i])) { dgvy67.Rows[i].Cells[1].Value = ystr_dict[ydna67[i]]; ystr_dict.Remove(ydna67[i]); } } //YDNA111 for (int i = 0; i < ydna111.Length; i++) { if (ystr_dict.ContainsKey(ydna111[i])) { dgvy111.Rows[i].Cells[1].Value = ystr_dict[ydna111[i]]; ystr_dict.Remove(ydna111[i]); } } //YDNA MISC dgvymisc.Rows.Clear(); foreach (string str in ystr_dict.Keys) { dgvymisc.Rows.Add(new string[] { str, ystr_dict[str] }); } })); // kit - mtdna query = new SQLiteCommand(@"SELECT mutations,fasta from kit_mtdna where kit_no=@kit_no", cnn); query.Parameters.AddWithValue("@kit_no", kit); reader = query.ExecuteReader(); string fasta = null; if (reader.Read()) { this.Invoke(new MethodInvoker(delegate { textBoxMtDNA.Text = reader.GetString(0); fasta = reader.GetString(1); if (fasta != null) { tbFASTA.Text = fasta; } else { tbFASTA.Text = ""; } })); } reader.Close(); query.Dispose(); }
/// <summary> /// Overrides the default behavior to return a SQLiteDataReader specialization class /// </summary> /// <param name="behavior">The flags to be associated with the reader.</param> /// <returns>A SQLiteDataReader</returns> public new SQLiteDataReader ExecuteReader(CommandBehavior behavior) { CheckDisposed(); SQLiteConnection.Check(_cnn); InitializeForReader(); SQLiteDataReader rd = new SQLiteDataReader(this, behavior); _activeReader = new WeakReference(rd, false); return rd; }
private string ReadNullableString(SQLiteDataReader reader, string columnName) { var value = reader[columnName]; return(value == DBNull.Value ? null : value.ToString()); }
private static SQLiteDataReader CreateReader() { var command = new SQLiteCommand(); var reader = new SQLiteDataReader(command, new StatementHandle[0], 0); command.OpenReader = reader; return reader; }
private void LoadError() { string sql_command = ""; //0 , 1 , 2 , 3 , 4 , 5 , 6 sql_command += "Select S7Adress, S7Symbol, S7SymbolType, S7Comment, SymbolType, cast(RankingGroup as integer) as RankingGroup, GroupComment, "; //7 , 8 , 9 , 10 , 11 , 12 , 13 , 14 sql_command += "cast(RankingSymbol as integer) as RankingSymbol, Comment, Unit, SymbolFormat, UpperLimit, LowerLimit, UserRightEnable, UserRightVisible, "; //15 sql_command += "Column "; if (this.m_formularType == FormularType.Error) { sql_command += "from plcitems where (SymbolType='E') order by RankingGroup, RankingSymbol"; } if (this.m_formularType == FormularType.Release) { sql_command += "from plcitems where (SymbolType='R') order by RankingGroup, RankingSymbol"; } this.m_sqliteCommand.CommandText = sql_command; if (this.m_sqliteDataReader != null) { this.m_sqliteDataReader.Close(); this.m_sqliteDataReader = null; } this.m_sqliteDataReader = this.m_sqliteCommand.ExecuteReader(); TabControl tab_control = this.CreateTabControl(4, 20, this.GbxOutput.Width - 8, this.GbxOutput.Height - 25, this.GbxOutput); tab_control.TabStop = false; this.m_tabControl = tab_control; TabPage tab_page = null; int page_no = 0; tab_page = this.CreateTabPage("Seite " + (page_no + 1), tab_control); int gbx_row = 0; int gbx_col = 0; GroupBox group_box = null; Label label_text = null; PlcItemList plc_item_list; CompLedRound led_round = null; string old_group_comment = ""; int row_comp = 0; int gbx_width = (tab_control.DisplayRectangle.Width + 10) / GlobalVar.ConstNumberOfColumns - (GlobalVar.ConstNumberOfColumns + 1) * 4; while (this.m_sqliteDataReader.Read()) { plc_item_list.S7Adress = this.m_sqliteDataReader.GetValue(0).ToString(); plc_item_list.S7Symbol = this.m_sqliteDataReader.GetValue(1).ToString(); plc_item_list.S7SymbolType = this.m_sqliteDataReader.GetValue(2).ToString(); plc_item_list.SymbolType = this.m_sqliteDataReader.GetValue(4).ToString(); plc_item_list.GroupComment = this.m_sqliteDataReader.GetValue(6).ToString(); plc_item_list.RankingSymbol = Convert.ToInt32(this.m_sqliteDataReader.GetValue(7).ToString()); plc_item_list.Comment = this.m_sqliteDataReader.GetValue(8).ToString(); plc_item_list.Unit = this.m_sqliteDataReader.GetValue(9).ToString(); plc_item_list.Format = this.m_sqliteDataReader.GetValue(10).ToString(); plc_item_list.Column = Convert.ToInt32(this.m_sqliteDataReader.GetValue(15).ToString()); plc_item_list.UpperLimit = Convert.ToDouble(this.m_sqliteDataReader.GetValue(11).ToString()); plc_item_list.LowerLimit = Convert.ToDouble(this.m_sqliteDataReader.GetValue(12).ToString()); plc_item_list.UserRightEnable = Convert.ToInt32(this.m_sqliteDataReader.GetValue(13).ToString()); string[] adress_info = plc_item_list.S7Adress.Split('.'); string db_number = adress_info[0]; string varname = db_number + "." + plc_item_list.S7Symbol; plc_item_list.Varname = varname; int top = 0; int left = 0; if (old_group_comment != plc_item_list.GroupComment) { old_group_comment = plc_item_list.GroupComment; top = 0; left = 0; if (gbx_row == 0) { top = 4; left = 4; gbx_row++; } else { left = group_box.Left; top = group_box.Top + group_box.Height + 4; } group_box = this.CreateGroupBox(left, top, gbx_width, 50, plc_item_list.GroupComment, tab_page); row_comp = 0; } int height = (row_comp) * 27 + 50; int height_max = height + group_box.Top; if (height_max > tab_control.DisplayRectangle.Height) { gbx_col++; if (gbx_col > 2) { page_no++; tab_page = this.CreateTabPage("Seite " + (page_no + 1), tab_control); gbx_row = 0; gbx_col = 0; } else { left = gbx_col * (gbx_width + 4) + 4; top = 4; group_box = this.CreateGroupBox(left, top, gbx_width, 50, plc_item_list.GroupComment, tab_page); row_comp = 0; } } height = (row_comp) * 27 + 50; group_box.Height = height; int top_comp = row_comp * 27 + 20; int left_comp = 4; if (this.m_formularType == FormularType.Release) { led_round = this.CreateLedRound(left_comp, top_comp, group_box, CompLedRound.LEDType.Release); } if (this.m_formularType == FormularType.Error) { led_round = this.CreateLedRound(left_comp, top_comp, group_box, CompLedRound.LEDType.Error); } this.m_dataBinding.AddList(this, led_round.Name.ToString(), "State", varname); label_text = this.CreateLabel(left_comp + 40, top_comp, 200, 25, plc_item_list.Comment, group_box, ContentAlignment.MiddleLeft); row_comp++;; } this.m_sqliteDataReader.Close(); this.m_sqliteDataReader = null; }