/// <summary> /// Removes the old entries (entries that have been in the database more than the specified time) from the database. /// </summary> /// <param name="olderThan"> The number of days in the database after which the entry is considered old... </param> public static void CleanNewsOlderThan(int olderThan) { try { using (SQLiteConnection sqLiteConnection = new SQLiteConnection(ConnectionString)) { sqLiteConnection.Open(); DateTime olderDate = DateTime.Now; TimeSpan daySpan = new TimeSpan(olderThan, 0, 0, 0); olderDate = olderDate.Subtract(daySpan); SQLiteCommand sqLiteCommand = new SQLiteCommand(sqLiteConnection) { CommandText = "DELETE " + "FROM NEWS_STORAGE " + "WHERE NEWSITEM_AQUISITION_DATE <= ?" }; sqLiteCommand.Parameters.AddWithValue(null, olderDate); sqLiteCommand.ExecuteNonQuery(); sqLiteConnection.Close(); } } catch (Exception ex) { ErrorMessageBox.Show(ex.Message, ex.ToString()); Logger.ErrorLogger("error.txt", ex.ToString()); } }
public static void Try(string[] parameters) { Player.PlayerStruct p = Player.GetPlayer(parameters[1]); if (!string.IsNullOrEmpty(p.Squad) && !string.IsNullOrEmpty(parameters[3]) && p.name.ToLower() != parameters[3].ToLower()) { /* Begin Database Connection */ DataTable dt = new DataTable(); SQLiteConnection SConnection = new SQLiteConnection(); SConnection.ConnectionString = SQLite.ConnectionString; SConnection.Open(); SQLiteCommand cmd = new SQLiteCommand(SConnection); cmd.CommandText = @"SELECT owner FROM squads WHERE name = @nsquad"; SQLiteParameter nsquad = new SQLiteParameter("@nsquad"); SQLiteParameter pname = new SQLiteParameter("@pname"); cmd.Parameters.Add(nsquad); cmd.Parameters.Add(pname); nsquad.Value = p.Squad; pname.Value = p.name; SQLiteDataReader Reader = cmd.ExecuteReader(); dt.Load(Reader); Reader.Close(); SConnection.Close(); /* End Database Connection */ if (dt.Rows.Count > 0) { if (dt.Rows[0][0].ToString().ToLower() == p.name.ToLower()) { /* Begin Database Connection */ SConnection.Open(); cmd.CommandText = @"UPDATE players SET squad = '' WHERE name = @kname AND squad = @nsquad"; SQLiteParameter kname = new SQLiteParameter("@kname"); cmd.Parameters.Add(kname); kname.Value = parameters[3]; cmd.ExecuteNonQuery(); SConnection.Close(); /* End Database Connection */ //TODO: do we send the kicked player a message? Message.Send = "MSG:" + parameters[1] + ":0:If said player was a member of your squad, he or she has been kicked."; SiusLog.Log(SiusLog.INFORMATION, "?squadkick", p.name + " kicked " + parameters[3] + " from squad <" + p.Squad + ">."); } else { Message.Send = "MSG:" + parameters[1] + ":0:You do not own this squad."; SiusLog.Log(SiusLog.INFORMATION, "?squadkick", p.name + " attempted to kick " + parameters[3] + " from squad <" + p.Squad + ">, but is not owner."); return; } } } }
public static DataSet ExecuteDataSet(string SqlRequest, SQLiteConnection Connection) { DataSet dataSet = new DataSet(); dataSet.Reset(); SQLiteCommand cmd = new SQLiteCommand(SqlRequest, Connection); try { Connection.Open(); SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(cmd); dataAdapter.Fill(dataSet); } catch (SQLiteException ex) { Log.Write(ex); //Debug.WriteLine(ex.Message); throw; // пересылаем исключение на более высокий уровень } finally { Connection.Dispose(); } return dataSet; }
public DataManager(string dbFilePath) { if (string.IsNullOrEmpty(dbFilePath) == true) { throw new ArgumentNullException("dbFilePath"); } _fileExists = File.Exists(CommonConst.DBFileName); _dbFilePath = dbFilePath; // DBとの接続を開始する。 _conn = new SQLiteConnection("Data Source=" + CommonConst.DBFileName); // DBに接続する。 _conn.Open(); _command = _conn.CreateCommand(); // DBファイルが新規作成された場合 if (_fileExists == false) { // 空のテーブルを作成する。 this.CreateNewTables(); _fileExists = true; } // アプリケーション設定 _applicationSettings.amountSplitCharacter = this.GetAmountsSplitCharacter(); _applicationSettings.commentSplitCharacter = this.GetCommentSplitCharacter(); }
public List<AttachmentDetail> getWitAttachments() { List<AttachmentDetail> attachments; try { sql_con = new SQLiteConnection(Common.localDatabasePath, true); sql_cmd = new SQLiteCommand("select * from wit_attachments", sql_con); sql_con.Open(); SQLiteDataReader reader = sql_cmd.ExecuteReader(); attachments = new List<AttachmentDetail>(); while (reader.Read()) { AttachmentDetail attachment = new AttachmentDetail(); attachment.fileAssociationId = StringUtils.ConvertFromDBVal<string>(reader["file_association_id"]); attachment.fileName = StringUtils.ConvertFromDBVal<string>(reader["file_name"]); attachment.witId = StringUtils.ConvertFromDBVal<string>(reader["wit_id"]); attachment.fileMimeType = StringUtils.ConvertFromDBVal<string>(reader["file_mime_type"]); attachment.fileAssociationId = StringUtils.ConvertFromDBVal<string>(reader["file_association_id"]); attachment.seqNumber = StringUtils.ConvertFromDBVal<string>(reader["seq_number"]); attachment.extention = StringUtils.ConvertFromDBVal<string>(reader["extention"]); attachments.Add(attachment); } } catch (SQLiteException e) { throw e; } finally { sql_con.Close(); } return attachments; }
private void Consulta_Load(object sender, EventArgs e) { string appPath = Path.GetDirectoryName(Application.ExecutablePath); string connString = @"Data Source=" + appPath + @"\EXCL.s3db ;Version=3;"; DataSet DS = new DataSet(); SQLiteConnection con = new SQLiteConnection(connString); con.Open(); SQLiteDataAdapter DA = new SQLiteDataAdapter("select * from Expediente", con); DA.Fill(DS, "Expediente"); dataGridView1.DataSource = DS.Tables["Expediente"]; con.Close(); dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.DisplayedCells; int i = 0; foreach (DataGridViewColumn c in dataGridView1.Columns) { i += c.Width; } if ((i + dataGridView1.RowHeadersWidth + 2) > 616) { dataGridView1.Width = 616; } else { dataGridView1.Width = i + dataGridView1.RowHeadersWidth + 2; } }
private void loadHeroDiary() { // Load the latest Hero Diary entries using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=" + dbPath + @"\gv.db;Version=3;New=False;Compress=True;")) { conn.Open(); using (SQLiteCommand cmd = conn.CreateCommand()) { string commandText = "select Diary_ID as ID, Updated, EntryTime, Entry from Diary where HeroName=@HeroName order by Diary_ID desc limit 1000"; cmd.CommandText = commandText; cmd.Parameters.AddWithValue("@HeroName", this.HeroName); SQLiteDataAdapter da = new SQLiteDataAdapter(cmd); DataSet ds = new DataSet(); da = new SQLiteDataAdapter(cmd); ds = new DataSet(); da.Fill(ds); BindingSource bindingSource = new BindingSource(); bindingSource.DataSource = ds.Tables[0]; grdDiary.DataSource = bindingSource; grdDiary.AutoGenerateColumns = true; grdDiary.AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.DisplayedCellsExceptHeaders; grdDiary.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells; } } }
/// <summary>Write data with a given key to cache</summary> /// <param name="key">Key of data to be stored</param> /// <param name="data">Data to be stored</param> public void Store(string key, string data) { SQLiteConnection connection = new SQLiteConnection("UseUTF16Encoding=True;Data Source=" + _File); try { DateTime now = DateTime.Now; connection.Open(); SQLiteCommand command = connection.CreateCommand(); command.CommandText = "DELETE FROM [InetCache] WHERE [Keyword]=?"; command.Parameters.Add("kw", DbType.String).Value = key; command.ExecuteNonQuery(); // out with the old command = connection.CreateCommand(); command.CommandText = "INSERT INTO [InetCache] ([Timestamp], [Keyword], [Content]) VALUES (?, ?, ?)"; command.Parameters.Add("ts", DbType.Int32).Value = now.Year * 10000 + now.Month * 100 + now.Day; command.Parameters.Add("kw", DbType.String).Value = key; command.Parameters.Add("dt", DbType.String).Value = data; command.ExecuteNonQuery(); // in with the new } finally { connection.Close(); } }
//public static int Insert(string dbFile, string sql,) public static ObservableCollection<Jewelry> GetAll() { ObservableCollection<Jewelry> ob = new ObservableCollection<Jewelry>(); using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=c:/xhz/ms.db;")) //using (SQLiteConnection conn = new SQLiteConnection(@"Data Source=DB/ms.db;")) { conn.Open(); string sql = string.Format("select * from detail"); SQLiteCommand cmd = new SQLiteCommand(sql, conn); using (SQLiteDataReader dr1 = cmd.ExecuteReader()) { while (dr1.Read()) { var d = dr1; var dd = dr1.GetValue(0); var data = dr1.GetValue(1); var insert = dr1.GetValue(2); var update = dr1.GetValue(3); } } conn.Close(); } ob.Add(new Jewelry()); return ob; }
public static void addAccount(string name, string passwd, string url, string provider, string blogname) { string conn_str = "Data Source=" + path + pwd_str; SQLiteConnection conn = new SQLiteConnection(conn_str); conn.Open(); SQLiteCommand cmd = new SQLiteCommand(); String sql = "Insert INTO BlogAccount(BlogUrl,BlogName,Provider,AccountName,Password) Values(@url,@blogname,@provider,@name,@password)"; cmd.CommandText = sql; cmd.Connection = conn; cmd.Parameters.Add(new SQLiteParameter("url", url)); cmd.Parameters.Add(new SQLiteParameter("blogname", blogname)); cmd.Parameters.Add(new SQLiteParameter("provider", provider)); cmd.Parameters.Add(new SQLiteParameter("name", name)); cmd.Parameters.Add(new SQLiteParameter("password", passwd)); cmd.ExecuteNonQuery(); conn.Close(); }
public static DataTable GetDataTable(string sql) { DataTable dt = new DataTable(); try { SQLiteConnection cnn = new SQLiteConnection(@"Data Source=C:\Projects\showdownsharp\db\showdown.db"); cnn.Open(); SQLiteCommand mycommand = new SQLiteCommand(cnn); mycommand.CommandText = sql; SQLiteDataReader reader = mycommand.ExecuteReader(); dt.Load(reader); reader.Close(); cnn.Close(); } catch { // Catching exceptions is for communists } return dt; }
static void Main(string[] args) { // Create sqlite connection SQLiteConnection connection = new SQLiteConnection(string.Format(@"Data Source={0}\SimpleDatabase.s3db", Environment.CurrentDirectory)); // Open sqlite connection connection.Open(); // Get all rows from example_table SQLiteDataAdapter db = new SQLiteDataAdapter("SELECT * FROM Names", connection); // Create a dataset DataSet ds = new DataSet(); // Fill dataset db.Fill(ds); // Create a datatable DataTable dt = new DataTable("Names"); dt = ds.Tables[0]; // Close connection connection.Close(); // Print table foreach (DataRow row in dt.Rows) { Console.WriteLine(string.Format("{0} {1}", row["Firstname"], row["Surname"])); } Console.ReadLine(); }
private List<Result> QuerySqllite(Doc doc, string key) { string dbPath = "Data Source =" + doc.DBPath; SQLiteConnection conn = new SQLiteConnection(dbPath); conn.Open(); string sql = GetQuerySqlByDocType(doc.DBType).Replace("{0}", key); SQLiteCommand cmdQ = new SQLiteCommand(sql, conn); SQLiteDataReader reader = cmdQ.ExecuteReader(); List<Result> results = new List<Result>(); while (reader.Read()) { string name = reader.GetString(reader.GetOrdinal("name")); string docPath = reader.GetString(reader.GetOrdinal("path")); results.Add(new Result { Title = name, SubTitle = doc.Name.Replace(".docset", ""), IcoPath = doc.IconPath, Action = (c) => { string url = string.Format(@"{0}\{1}\Contents\Resources\Documents\{2}#{3}", docsetPath, doc.Name+".docset", docPath, name); string browser = GetDefaultBrowserPath(); Process.Start(browser, String.Format("\"file:///{0}\"", url)); return true; } }); } conn.Close(); return results; }
public object Convert(object value, Type targetType, object parameter, System.Globalization.CultureInfo culture) { using (SQLiteConnection conn = new SQLiteConnection("StaticConfig.DataSource")) { using (SQLiteCommand cmd = new SQLiteCommand()) { cmd.Connection = conn; conn.Open(); SQLiteHelper sh = new SQLiteHelper(cmd); int countCurrent = 0; string sqlCommandCurrent = String.Format("select count(*) from QuestionInfo where Q_Num={0};", value);//判断当前题号是否存在 try { Int32.Parse((string)value); countCurrent = sh.ExecuteScalar<int>(sqlCommandCurrent); conn.Close(); if (countCurrent > 0) { return "更新"; } else { return "保存"; } } catch (Exception) { return "保存"; } } } }
public EntityStore(string databaseFullPath, DestructorType destructorType = DestructorType.None) { _databaseFullPath = databaseFullPath; _destructorType = destructorType; _connectionString = String.Format("Data Source={0}; Version=3; Read Only=False; Pooling=True; Max Pool Size=10", _databaseFullPath); if (!File.Exists(_databaseFullPath)) { SQLiteConnection.CreateFile(_databaseFullPath); Log.Info("Successfully created the EntityStore database file at {0}", databaseFullPath); } else { Log.Info("Successfully confirmed that the EntityStore database exists at {0}", databaseFullPath); } using (var connection = new SQLiteConnection(_connectionString)) { connection.Open(); using (var cmd = connection.CreateCommand()) { Log.Verbose("About to create or check for the Entities table in the EntityStore database."); cmd.CommandText = "CREATE TABLE IF NOT EXISTS Entities (entityType TEXT, entityKey TEXT, entityBlob TEXT, entityTag TEXT, lastModified DATETIME, PRIMARY KEY (entityType, entityKey))"; cmd.CommandType = CommandType.Text; cmd.ExecuteNonQuery(); Log.Info("Successfully created or checked that the Entities table exists in the EntityStore database."); } } }
public void create(string file) { db_file = file; conn = new SQLiteConnection("Data Source=" + db_file); if (!File.Exists(db_file)) { conn.Open(); using (SQLiteCommand command = conn.CreateCommand()) { command.CommandText = @"CREATE TABLE `erogamescape` ( `id` INTEGER NOT NULL, `title` TEXT, `saleday` TEXT, `brand` TEXT, PRIMARY KEY(id)); CREATE TABLE `tableinfo` ( `tablename` TEXT NOT NULL, `version` INTEGER, PRIMARY KEY(tablename))"; command.ExecuteNonQuery(); command.CommandText = @"INSERT INTO tableinfo VALUES('erogamescape',0)"; command.ExecuteNonQuery(); } conn.Close(); } }
private bool CreateDatabase() { SQLiteConnection tempConnection = null; try { SQLiteConnection.CreateFile(ConfigurationManager.AppSettings["dbPath"]); tempConnection = new SQLiteConnection("Data Source=" + ConfigurationManager.AppSettings["dbPath"] + ";Version=3;"); tempConnection.Open(); SQLiteCommand command = tempConnection.CreateCommand(); command.CommandText = "CREATE TABLE pelaaja (id INTEGER PRIMARY KEY AUTOINCREMENT, etunimi TEXT NOT NULL, sukunimi TEXT NOT NULL, seura TEXT NOT NULL, hinta FLOAT NOT NULL, kuva_url TEXT NULL)"; command.ExecuteNonQuery(); tempConnection.Close(); } catch(Exception e) { errors.Add("Tietokannan luominen epäonnistui"); return false; } finally { if(tempConnection != null) tempConnection.Close(); } return true; }
private void DeleteCardBase() { if (dataGridView1.SelectedRows.Count > 0) { id = Int32.Parse(dataGridView1.SelectedRows[0].Cells[0].Value.ToString()); if (dataGridView1.RowCount != 0) index = dataGridView1.SelectedRows[0].Index; DialogResult result = MessageBox.Show("Czy na pewno chcesz usunąć bazę karty numer " + id + "? \n\nOperacji nie można cofnąć.", "Ważne", MessageBoxButtons.YesNo, MessageBoxIcon.Warning); if (result == DialogResult.Yes) using (SQLiteConnection conn = new SQLiteConnection(connString)) { conn.Open(); SQLiteCommand command = new SQLiteCommand(conn); command.CommandText = "DELETE FROM [CardBase] WHERE id = @id"; command.Parameters.Add(new SQLiteParameter("@id", id)); command.ExecuteNonQuery(); conn.Close(); Odswierz(); if (dataGridView1.RowCount != 0) { if (index == dataGridView1.RowCount) dataGridView1.CurrentCell = dataGridView1.Rows[index - 1].Cells[0]; else dataGridView1.CurrentCell = dataGridView1.Rows[index].Cells[0]; } } } }
/// <summary> /// Takes a GIS model and a file and writes the model to that file. /// </summary> /// <param name="model"> /// The GisModel which is to be persisted. /// </param> /// <param name="fileName"> /// The name of the file in which the model is to be persisted. /// </param> public void Persist(GisModel model, string fileName) { Initialize(model); PatternedPredicate[] predicates = GetPredicates(); if ( File.Exists(fileName)) { File.Delete(fileName); } using (mDataConnection = new SQLiteConnection("Data Source=" + fileName + ";New=True;Compress=False;Synchronous=Off;UTF8Encoding=True;Version=3")) { mDataConnection.Open(); mDataCommand = mDataConnection.CreateCommand(); CreateDataStructures(); using (mDataTransaction = mDataConnection.BeginTransaction()) { mDataCommand.Transaction = mDataTransaction; CreateModel(model.CorrectionConstant, model.CorrectionParameter); InsertOutcomes(model.GetOutcomeNames()); InsertPredicates(predicates); InsertPredicateParameters(model.GetOutcomePatterns(), predicates); mDataTransaction.Commit(); } mDataConnection.Close(); } }
public static AttendanceReport[] GetAttendaceSummaryReports(string data_path) { SQLiteConnection conn = new SQLiteConnection(@"Data Source=" + data_path); conn.Open(); SQLiteCommand cmd = new SQLiteCommand(conn); cmd.CommandText = "select * from AttendanceSummary"; SQLiteDataReader reader = cmd.ExecuteReader(); List<AttendanceReport> attendanceReports = new List<AttendanceReport>(); while (reader.Read()) { int ID = reader.GetInt32(0); string timeIN = reader.GetString(1); string timeOUT = reader.GetString(2); string deltaTime = reader.GetString(3); string activity = reader.GetString(4); string mentor = reader.GetString(5); int index = reader.GetInt32(6); attendanceReports.Add(new AttendanceReport(ID, timeIN, timeOUT, deltaTime, activity, mentor, index)); } reader.Close(); conn.Close(); return attendanceReports.ToArray(); }
public static void ChapterFinished(string mangaTitle) { try { using (SQLiteConnection sqLiteConnection = new SQLiteConnection(ConnectionString)) { sqLiteConnection.Open(); SQLiteCommand sqLiteCommand = new SQLiteCommand(sqLiteConnection) { CommandText = "UPDATE READING_LIST " + "SET READ_CURRENT_CHAPTER = READ_CURRENT_CHAPTER + 1, READ_LAST_TIME = ? " + "WHERE MANGA_ID = ?" }; sqLiteCommand.Parameters.AddWithValue(null, DateTime.Now); sqLiteCommand.Parameters.AddWithValue(null, GetMangaId(mangaTitle)); sqLiteCommand.ExecuteNonQuery(); sqLiteConnection.Close(); } } catch (Exception ex) { ErrorMessageBox.Show(ex.Message, ex.ToString()); Logger.ErrorLogger("error.txt", ex.ToString()); } }
public Block_Edit(int id_) { InitializeComponent(); id = id_; using (SQLiteConnection conn = new SQLiteConnection(connString)) { conn.Open(); SQLiteCommand command = new SQLiteCommand(conn); command.CommandText = "SELECT * FROM Block WHERE id=@id"; command.Parameters.Add(new SQLiteParameter("@id", id)); using (command) { using (SQLiteDataReader rdr = command.ExecuteReader()) { while (rdr.Read()) { labelName.Text = rdr.GetValue(1).ToString(); txtImieNazw.Text = rdr.GetValue(1).ToString(); } } } conn.Close(); } }
/// <summary>Initialization ctor</summary> /// <param name="file">Name of database file, will be created if not existing</param> public WebCache(string file = "Cache.dat") { _File = file; if (!System.IO.File.Exists(_File)) { SQLiteConnection connection = new SQLiteConnection("UseUTF16Encoding=True;Data Source=" + _File); try { connection.Open(); string[] inits = new string[]{ "CREATE TABLE [InetCache] ([Id] INTEGER PRIMARY KEY, [Timestamp] INTEGER, [Keyword] TEXT NOT NULL, [Content] TEXT NOT NULL)", "CREATE INDEX [IDX_InetCache] ON [InetCache] ([Keyword])" }; SQLiteCommand command = connection.CreateCommand(); foreach (string cmd in inits) { command.CommandText = cmd; command.ExecuteNonQuery(); } } finally { connection.Close(); } } }
/// <summary> /// Constructor creates a database if it doesn't exist and creates the database's tables /// </summary> public DataBaseRepository() { // "using " keywords ensures the object is properly disposed. using (var conn = new SQLiteConnection(Connectionstring)) { try { if (!File.Exists(Startup.dbSource)) { SQLiteConnection.CreateFile(Startup.dbSource); } conn.Open(); //Create a Table string query = $"create table IF NOT EXISTS {TableName} (Id INTEGER PRIMARY KEY, Date VARCHAR NOT NULL DEFAULT CURRENT_DATE, Title nvarchar(255) not null, Content nvarchar(1000) Not NULL) "; SQLiteCommand command = new SQLiteCommand(query, conn); command.ExecuteNonQuery(); } //TODO: Handle try catch better cath a more specific error type. catch (SQLiteException ex ) { Console.WriteLine(ex.ToString()); } conn.Close(); } }
public static DataTable SearchMusicDatabase( FileInfo theDatabaseFileInfo, string theCriteria) { using (DataSet theDataSet = new DataSet()) { using (SQLiteConnection theConnection = new SQLiteConnection( DatabaseLayer.ToConnectionString(theDatabaseFileInfo))) { try { theConnection.Open(); using (SQLiteCommand theCommand = theConnection.CreateCommand()) { StringBuilder theCommandBuilder = new StringBuilder( "SELECT [ID], [Path], [Details], [Extension], 0, " + "[Path] || '\\' || [Details] || [Extension] AS [FullPath] "); theCommandBuilder.Append("FROM [Tracks] "); theCommandBuilder.Append("WHERE "); bool IsFirstParameter = true; foreach (string thisCriteria in theCriteria.Split(' ')) { if (!IsFirstParameter) { theCommandBuilder.Append(" AND "); } theCommandBuilder.Append("([Details] LIKE ? OR [Path] LIKE ?)"); SQLiteParameter thisCriteriaParameter = theCommand.CreateParameter(); theCommand.Parameters.Add(thisCriteriaParameter); theCommand.Parameters.Add(thisCriteriaParameter); thisCriteriaParameter.Value = String.Format( CultureInfo.CurrentCulture, "%{0}%", thisCriteria.ToString()); IsFirstParameter = false; } theCommandBuilder.Append(" ORDER BY [Path], [Details]"); theCommand.CommandText = theCommandBuilder.ToString(); using (SQLiteDataAdapter theAdapter = new SQLiteDataAdapter(theCommand)) { theAdapter.Fill(theDataSet); } } } finally { theConnection.Close(); } } return theDataSet.Tables[0]; } }
private DoorInfo Retrieve(string DoorID) { var ret = new DoorInfo(); var cs = ConfigurationManager.ConnectionStrings["DoorSource"].ConnectionString; var conn = new SQLiteConnection(cs); conn.Open(); var cmd = new SQLiteCommand(conn); cmd.CommandText = "SELECT DoorID, Location, Description, EventID FROM Doors WHERE DoorID = @DoorID LIMIT 1"; cmd.Parameters.Add(new SQLiteParameter("@DoorID", DoorID)); SQLiteDataReader res = null; try { res = cmd.ExecuteReader(); if (res.HasRows && res.Read()) { ret.DoorID = DoorID; ret.Location = res.GetString(1); ret.Description = res.GetString(2); ret.EventID = res.GetInt64(3); } return ret; } catch(Exception ex) { throw; } finally { if (null != res && !res.IsClosed) res.Close(); } }
public static DataTable buscaSing4() { DataTable tabla = new dataCredito.sing4DataTable(); using (SQLiteConnection con = new SQLiteConnection(Datos.conexion)) { using (SQLiteCommand comando = new SQLiteCommand()) { comando.CommandText = "select * from sing4"; comando.Connection = con; con.Open(); SQLiteDataReader lector = comando.ExecuteReader(); while (lector.Read()) { DataRow fila = tabla.NewRow(); fila["encabezado"] = lector.GetString(0); fila["saludo"] = lector.GetString(1); fila["pie"] = lector.GetString(2); fila["asesor"] = lector.GetString(3); fila["fecha"] = lector.GetDateTime(4); fila["id"] = lector.GetString(5); fila["idCliente"] = lector.GetInt32(6); tabla.Rows.Add(fila); } } con.Close(); } return (tabla); }
public static DataTable ExecuteNonQueryDt(string cmdText, SQLiteConnection con) { DataTable dt = new DataTable("Table"); try { using (con) { SQLiteDataAdapter da = new SQLiteDataAdapter(cmdText, con); con.Open(); da.Fill(dt); con.Close(); } return dt; } catch (Exception ex) { using (FileStream file = new FileStream(AppDomain.CurrentDomain.BaseDirectory + "\\" + System.DateTime.Now.Date.ToString("dd-MMM-yyyy") + "_Log.txt", FileMode.Append, FileAccess.Write)) { StreamWriter streamWriter = new StreamWriter(file); streamWriter.WriteLine(System.DateTime.Now + " - " + "ExecuteNonQueryDt" + " - " + ex.Message.ToString()); streamWriter.Close(); } return dt; } }
private static SQLiteConnection ConnectToDatabase() { var dbConnection = new SQLiteConnection(Settings.Default.SQLiteConnectionString); dbConnection.Open(); return dbConnection; }
public void Clear(string name) { int procCount = Process.GetProcessesByName("firefox").Length; if (procCount > 0) throw new ApplicationException(string.Format("There are {0} instances of Firefox still running", procCount)); try { using (var conn = new SQLiteConnection("Data Source=" + GetFirefoxCookiesFileName())) { conn.Open(); SQLiteCommand command = conn.CreateCommand(); command.CommandText = "delete from moz_cookies where name='" + name + "'"; int count = command.ExecuteNonQuery(); } } catch (SQLiteException ex) { if ( !(ex.ErrorCode == Convert.ToInt32(SQLiteErrorCode.Busy) || ex.ErrorCode == Convert.ToInt32(SQLiteErrorCode.Locked))) throw new ApplicationException("The Firefox cookies.sqlite file is locked"); } }
public List <CookieItem> ReadChromeCookies(string hostName) { if (hostName == null) { throw new ArgumentNullException("hostName"); } List <CookieItem> ret = new List <CookieItem>(); var dbPath = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + @"\Google\Chrome\User Data\Default\Cookies"; if (!System.IO.File.Exists(dbPath)) { throw new System.IO.FileNotFoundException("Cant find cookie store", dbPath); // race condition, but i'll risk it } var connectionString = "Data Source=" + dbPath + ";pooling=false"; using (var conn = new System.Data.SQLite.SQLiteConnection(connectionString)) using (var cmd = conn.CreateCommand()) { var prm = cmd.CreateParameter(); prm.ParameterName = "hostName"; prm.Value = hostName; cmd.Parameters.Add(prm); cmd.CommandText = "SELECT name,encrypted_value FROM cookies WHERE host_key like '%" + hostName + "%'"; conn.Open(); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { var name = reader[0].ToString(); var encryptedData = (byte[])reader[1]; string encKey = File.ReadAllText(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + @"\Google\Chrome\User Data\Local State"); encKey = JObject.Parse(encKey)["os_crypt"]["encrypted_key"].ToString(); var decodedKey = System.Security.Cryptography.ProtectedData.Unprotect(Convert.FromBase64String(encKey).Skip(5).ToArray(), null, System.Security.Cryptography.DataProtectionScope.LocalMachine); var _cookie = _decryptWithKey(encryptedData, decodedKey, 3); if (ret.Find(x => x.Name == name) == null) { ret.Add(new CookieItem { Name = name, Value = _cookie }); } } } conn.Close(); } return(ret); }
public List <CookieItem> ReadCookies(string hostName) { if (hostName == null) { throw new ArgumentNullException("hostName"); } List <CookieItem> ret = new List <CookieItem>(); var dbPath = Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData) + @"\Google\Chrome\User Data\Default\Cookies"; if (!System.IO.File.Exists(dbPath)) { throw new System.IO.FileNotFoundException("Cant find cookie store", dbPath); // race condition, but i'll risk it } var connectionString = "Data Source=" + dbPath + ";pooling=false"; using (var conn = new System.Data.SQLite.SQLiteConnection(connectionString)) using (var cmd = conn.CreateCommand()) { var prm = cmd.CreateParameter(); prm.ParameterName = "hostName"; prm.Value = hostName; cmd.Parameters.Add(prm); cmd.CommandText = "SELECT name,encrypted_value FROM cookies WHERE host_key like '%" + hostName + "%'"; conn.Open(); using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { var name = reader[0].ToString(); var encryptedData = (byte[])reader[1]; var decodedData = System.Security.Cryptography.ProtectedData.Unprotect(encryptedData, null, System.Security.Cryptography.DataProtectionScope.CurrentUser); var plainText = Encoding.ASCII.GetString(decodedData); // Looks like ASCII if (ret.Find(x => x.Name == name) == null) { ret.Add(new CookieItem { Name = name, Value = plainText }); } } } conn.Close(); } return(ret); }
private void button1_Click(object sender, EventArgs e) { if (textBox1.Text != "" && textBox2.Text != "" && textBox3.Text != "" && textBox4.Text != "") { string appPath = Path.GetDirectoryName(Application.ExecutablePath); System.Data.SQLite.SQLiteConnection sqlConnection1 = new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBUC.s3db ;Version=3;"); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandType = System.Data.CommandType.Text; //comando sql para insercion cmd.CommandText = "INSERT INTO Usuarios (Nombre, Usuario, Contrasena, FechaContrasena, TipoDeUsuario, Huella) VALUES ('" + textBox1.Text + "', '" + textBox2.Text + "', '" + textBox3.Text + "', '" + DateTime.Now.ToShortDateString() + "', '" + comboBox1.Text + "', @0)"; SQLiteParameter param = new SQLiteParameter("@0", System.Data.DbType.Binary); cmd.Connection = sqlConnection1; param.Value = fp_image; cmd.Parameters.Add(param); sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); MessageBox.Show("Usuario Guardado con Exito"); textBox1.Text = ""; textBox2.Text = ""; textBox3.Text = ""; textBox4.Text = ""; label5.Text = ""; pictureBox1.Image = null; progressBar1.Value = 0; } else { MessageBox.Show("Hay campos en blancos sin llenar"); } }
/// <summary> /// 更新表格中的一些项目 /// </summary> /// <param name="collect">权限集合</param> /// <returns>更新计数</returns> public int UpdateControlAuthorityTale(ObservableCollection <ControlAuthority> collect) { try { int cn = 0; using (var conn = new System.Data.SQLite.SQLiteConnection()) { var connstr = new SQLiteConnectionStringBuilder(); connstr.DataSource = datasource; //connstr.Password = "******";//设置密码,SQLite ADO.NET实现了数据库密码保护 conn.ConnectionString = connstr.ToString(); conn.Open(); using (SQLiteCommand cmd = new SQLiteCommand()) { cmd.Connection = conn; string sql = ""; foreach (var m in collect) { if (m.UpdateData) { sql = string.Format("update control_authority set MinLevel = {0} where ElementName = '{1}' ", m.MinLevel, m.ElementName); cmd.CommandText = sql; cmd.ExecuteNonQuery(); m.UpdateData = false; cn++; } } } conn.Close(); return(cn); } } catch (Exception ex) { throw ex; } }
static public void SQLiteLoadTransactionsFromDatabase() { System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("Data Source=MSBase.sqlite;Version=3;"); conn.Open(); //sql_command = "INSERT INTO MSTransactions (MSTransactionId, MSIO, MSValue, MSCurrencyCode, MSAccountId, MSCategoryId, MSNote, MSDateTime, MSMulticurrency) " // + "VALUES ( 0, '+', 0.01, 'AZN', 1, 1, 'Test', '20.07.2018 00:40:00', 0);"; SQLiteCommand cmd = new SQLiteCommand("", conn); cmd.CommandText = "SELECT MSTransactionId, MSIO, MSValue, " + "MSCurrencyCode, MSAccountId, MSCategoryId, " + "MSNote, MSDateTime, MSMulticurrency " + "FROM MSTransactions"; try { SQLiteDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { Program.transaction.MSTransactionId = int.Parse(dr["MSTransactionId"].ToString()); Program.transaction.MSIO = ((string)dr["MSIO"])[0]; Program.transaction.MSValue = float.Parse(dr["MSValue"].ToString()); Program.transaction.MSCurrencyCode = (string)dr["MSCurrencyCode"]; Program.transaction.MSAccountId = int.Parse(dr["MSAccountId"].ToString()); Program.transaction.MSCategoryId = int.Parse(dr["MSCategoryId"].ToString()); Program.transaction.MSNote = (string)dr["MSNote"]; Program.transaction.MSDateTime = DateTime.Parse((string)dr["MSDateTime"]); Program.transaction.MSMulticurrency = (string)dr["MSDateTime"] == "1" ? true : false; Program.transactions.Add(new MSTransaction(Program.transaction)); } dr.Close(); } catch (SQLiteException ex) { Console.WriteLine(ex.Message); } }
public void GetAllClassesTest() { QuizardDatabase db = null; Assert.DoesNotThrow(delegate { db = new QuizardDatabase(); db.Open(); if (!File.Exists("quizard.db")) { int x = db.buildDB(); Assert.AreEqual(x, 0); } }); Console.WriteLine("Database created"); List <Class> allClasses = db.GetAllClasses(); Console.WriteLine("Query successful"); Console.WriteLine("Class count = " + allClasses.Count); Assert.DoesNotThrow(delegate { using (SQLiteConnection database = new System.Data.SQLite.SQLiteConnection("Data Source = quizard.db")) { database.Open(); using (SQLiteCommand command = database.CreateCommand()) { Console.WriteLine("SELECT COUNT(*) FROM classes;"); command.CommandText = "SELECT COUNT(*) FROM classes;"; Assert.AreEqual(command.ExecuteScalar(), allClasses.Count); Console.WriteLine("Count was equal"); } } }); }
public static void SetupDB() { // Check if Directory where we save db exists. If it doesn't. Create it. if (!Directory.Exists("C:\\SQLite")) { Directory.CreateDirectory("C:\\SQLite"); } // Check if db exists. If it doesnt, Create it, create tables and fill it with some data. if (!System.IO.File.Exists("C:\\SQLite\\ProjectFodMap.db")) { Console.WriteLine("Trying to write database to: C:\\SQLite\\ProjectFodMap.db"); System.Data.SQLite.SQLiteConnection.CreateFile("C:\\SQLite\\ProjectFodMap.db"); using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection(conString)) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); com.CommandText = @"CREATE TABLE Meals( MealID INTEGER PRIMARY KEY AUTOINCREMENT, MealName CHAR(40) NOT NULL, MealIngredients VARCHAR(max) NOT NULL, MealInstructions VARCHAR(max), MealNotes VARCHAR(max), MealRisks VARCHAR(max) );"; com.ExecuteNonQuery(); con.Close(); } } InsertTestMeals(); } }
public static void CheckDataFile(string datasource) { //string datasource = Application.StartupPath + "\\data.db"; System.Data.SQLite.SQLiteConnection.CreateFile(datasource); //连接数据库 System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(); System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder(); connstr.DataSource = datasource; //connstr.Password = "******";//设置密码,SQLite ADO.NET实现了数据库密码保护 conn.ConnectionString = connstr.ToString(); conn.Open(); //创建表 System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); string sql = "CREATE TABLE billInfo (BillID varchar(50) PRIMARY KEY,SiteName varchar(50),SiteUserName varchar(50),TotalMoney decimal(5,2),YearRete decimal(5,2),Deadline interger ,DeadlineType interger,ReceivedPeriod interger,ReceivablePeriod interger,ReceivablePrincipalAndInterest decimal(5,2),ReceivedPrincipalAndInterest decimal(5,2),WayOfRepayment interger,Reward decimal(5,2),BeginDay varchar(20),EndDay varchar(20),YuQiCount interger,Deleted interger,Flag interger,Remark varchar(200),UpdateTime datetime,CreateTime datetime)"; cmd.CommandText = sql; cmd.Connection = conn; cmd.ExecuteNonQuery(); sql = "Create Table billdetail(BillDetailID varchar(50) PRIMARY KEY,BillID varchar(50),Periods varchar(50),ReceivableDay varchar(20),ReceivedDay varchar(20),ReceivablePrincipalAndInterest decimal(5,2),ReceivableInterest decimal(5,2),ReceivedPrincipalAndInterest decimal(5,2),IsYuQi interger ,Deleted interger,Flag interger,UpdateTime datetime,CreateTime datetime)"; cmd.CommandText = sql; cmd.ExecuteNonQuery(); conn.Close(); }
public void createDB(string createQuery) { try { using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=gestionalePalestra.db")) { using (System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn)) { conn.Open(); cmd.CommandText = createQuery; cmd.ExecuteNonQuery(); conn.Close(); MessageBox.Show("Prima connessione al database stabilita, per favore entra con le credenziali Username: Admin e Password: Password" + " e aggiungi un account utente per iniziare ad utilizzare questo software!", "Prima connessione stabilita!"); } } } catch (SQLiteException e) { MessageBox.Show("Impossibile connettersi al database! contattare l'amministratore del sistema.", "Errore Connessione DB"); } }
private void BindGrid() // Esse Form de Consulta vai realizar a conexão com banco de dados e mostrar o que tem na tabela com os dados //inseridos no Form de cadastro. Basicamente é realizar um Select e associalos ao Fill para que possa ser compreendido //pelo Data Grid View , o Data Grid View é um recurso para manipular dados provenientes de um banco de dados. //Algumas adaptações via dll e Program.cs(https://stackoverflow.com/questions/3179028/mixed-mode-assembly-in-net-4) foram realizadas para //que o Data Grid View pudesse ser utilizado com Drive ODBC do SQLite. Devido a isso a sintaxe de conexão foi alterada...De todo modo funcionou { String connectionString = @"Data Source=C:\EncontreUmTrampo\cadastro.db"; System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(connectionString); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand("select ID,Vaga,Empresa,Area,Local,Data,Etapa from Trampo"); cmd.Connection = conn; conn.Open(); cmd.ExecuteScalar(); System.Data.SQLite.SQLiteDataAdapter da = new System.Data.SQLite.SQLiteDataAdapter(cmd); System.Data.DataSet ds = new System.Data.DataSet(); da.Fill(ds); dataGridView1.DataSource = ds; dataGridView1.DataMember = ds.Tables[0].TableName; conn.Close(); }
public override bool Login(string Address, string Data, string UserName, string Password) { lock (lockObject) { bool result = true; try { conn = new SQLiteConnection(string.Format("Data Source={0}\\{1};Password={2}", Address, Data, Password)); conn.Open(); result = (conn.State == ConnectionState.Open); } catch (Exception e) { if (conn != null) { All.Class.Error.Add("连接字符", string.Format("Data Source={0}\\{1};Password={2}", Address, Data, Password)); } All.Class.Error.Add(e); } return(result); } }
private void SqliteTest(object sender, RoutedEventArgs e) { string datasource = "test.db"; System.Data.SQLite.SQLiteConnection.CreateFile(datasource); System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(); System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder(); connstr.DataSource = datasource; connstr.CacheSize = 1024; // connstr.Password = "******";//设置密码,SQLite ADO.NET实现了数据库密码保护 conn.ConnectionString = connstr.ToString(); conn.Open(); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); string sql = "CREATE TABLE test(username varchar(20),password varchar(20))"; cmd.CommandText = sql; cmd.Connection = conn; cmd.ExecuteNonQuery(); sql = "INSERT INTO test VALUES('a','b')"; cmd.CommandText = sql; cmd.ExecuteNonQuery(); sql = "SELECT * FROM test"; cmd.CommandText = sql; System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader(); StringBuilder sb = new StringBuilder(); while (reader.Read()) { sb.Append("username:"******"\n") .Append("password:").Append(reader.GetString(1)); } MessageBox.Show(sb.ToString()); }
/// <summary> /// Connect to the SQLite database /// </summary> /// <param name="close">If true : Close database connection. If false : Open database connection</param> /// <returns>SQLiteCommand cmd if database is getting opened.</returns> /// <returns>Null if database is getting closed</returns> private static SQLiteCommand ConnectToDatabase(bool close) { if (!File.Exists("GLdb.db3")) { System.Data.SQLite.SQLiteConnection.CreateFile( Path.Combine( Directory.GetCurrentDirectory(), "GLdb.db3")); //Create the database } System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=GLdb.db3"); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(conn); if (!close) { conn.Open(); //Open connection to the SQLite database return(cmd); } else { cmd = null; conn.Close(); return(cmd); } }
private static void CreateDatabase() { try { System.Data.SQLite.SQLiteConnection.CreateFile(_dataBase); string connectionString = string.Format("Data Source={0};Version=3;", _dataBase); System.Data.SQLite.SQLiteConnection dbConnection = new System.Data.SQLite.SQLiteConnection(connectionString); dbConnection.Open(); string sql; SQLiteCommand command; PatientTable(dbConnection, out sql, out command); ScheduleTable(dbConnection, out sql, out command); dbConnection.Close(); } catch (Exception ex) { string er = ex.ToString(); } }
protected override DbConnection _getdbconn() { //return base._getdbconn(); if (dbname.Split('_').Length < 1) { forms.utls.alert("db_name error!"); } String fileName = this.getAppPath() + @"\" + dbname; FileInfo finfo = new FileInfo(fileName); _connstr = "Data Source=" + fileName; if (!finfo.Exists) { System.Data.SQLite.SQLiteConnection.CreateFile(fileName); OpenFileDialog ofd = new OpenFileDialog(); if (ofd.ShowDialog() == DialogResult.OK) { string sql = File.ReadAllText(ofd.FileName); using (SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(_connstr)) { conn.Open(); using (SQLiteCommand cmd = new SQLiteCommand(sql, conn)) { MessageBox.Show(String.Format("Create DB {0}", cmd.ExecuteNonQuery())); } using (SQLiteCommand cmd = new SQLiteCommand("insert into sl_ctrldata(curr_ym)values('" + dbname.Split('_')[1] + "')", conn)) { MessageBox.Show(String.Format("Create DB {0}", cmd.ExecuteNonQuery())); } conn.Close(); } } } return(new System.Data.SQLite.SQLiteConnection(_connstr)); }
private void Form6_Load(object sender, EventArgs e) { //stores countries to compare medal winners string first, second, third; string[] countries = { "Team America", "Team Great Britain", "Team Norway", "Team Germany", "Team Canada", "Team South Korea", "Team China", "Team Japan", "Team Switzerland", "Team Sweden", "Team Azerbaijan", "Team Bosnia" }; //count stores the medal count for each country as they get added incrimently int[] count = { 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0 }; //this is used to populate an event table string ViewTable = "SELECT * FROM \"ss500m Times Men\" ORDER BY Times DESC;";//gets team table from database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB if (con.State == ConnectionState.Open) // if connection.Open was successful { //this sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(ViewTable, con); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0].DefaultView; } else { MessageBox.Show("Connection failed."); } } con.Close(); } //stores country name of first second and third places first = dataGridView1.Rows[0].Cells[3].Value.ToString(); second = dataGridView1.Rows[1].Cells[3].Value.ToString(); third = dataGridView1.Rows[2].Cells[3].Value.ToString(); //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for first if so add to count if (first == countries[i]) { count[i]++; } } //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for second if so add to count if (second == countries[i]) { count[i]++; } } //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for third if so add to count if (third == countries[i]) { count[i]++; } } string View1 = "SELECT * FROM \"ss500m Times Women\" ORDER BY Times DESC;";//gets team table from database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB if (con.State == ConnectionState.Open) // if connection.Open was successful { //this sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(View1, con); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0].DefaultView; } else { MessageBox.Show("Connection failed."); } } con.Close(); } first = dataGridView1.Rows[0].Cells[3].Value.ToString(); second = dataGridView1.Rows[1].Cells[3].Value.ToString(); third = dataGridView1.Rows[2].Cells[3].Value.ToString(); //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for first if so add to count if (first == countries[i]) { count[i]++; } } //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for second if so add to count if (second == countries[i]) { count[i]++; } } //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for third if so add to count if (third == countries[i]) { count[i]++; } } string View2 = "SELECT * FROM \"ss1000m Times Men\" ORDER BY Times DESC;";//gets team table from database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB if (con.State == ConnectionState.Open) // if connection.Open was successful { //this sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(View2, con); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0].DefaultView; } else { MessageBox.Show("Connection failed."); } } con.Close(); } first = dataGridView1.Rows[0].Cells[3].Value.ToString(); second = dataGridView1.Rows[1].Cells[3].Value.ToString(); third = dataGridView1.Rows[2].Cells[3].Value.ToString(); //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for first if so add to count if (first == countries[i]) { count[i]++; } } //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for second if so add to count if (second == countries[i]) { count[i]++; } } //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for third if so add to count if (third == countries[i]) { count[i]++; } } string View3 = "SELECT * FROM \"ss1000m Times Women\" ORDER BY Times DESC;";//gets team table from database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB if (con.State == ConnectionState.Open) // if connection.Open was successful { //this sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(View3, con); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0].DefaultView; } else { MessageBox.Show("Connection failed."); } } con.Close(); } first = dataGridView1.Rows[0].Cells[3].Value.ToString(); second = dataGridView1.Rows[1].Cells[3].Value.ToString(); third = dataGridView1.Rows[2].Cells[3].Value.ToString(); //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for first if so add to count if (first == countries[i]) { count[i]++; } } //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for second if so add to count if (second == countries[i]) { count[i]++; } } //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for third if so add to count if (third == countries[i]) { count[i]++; } } string View4 = "SELECT * FROM \"ss1500m Times Men\" ORDER BY Times DESC;";//gets team table from database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB if (con.State == ConnectionState.Open) // if connection.Open was successful { //this sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(View4, con); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0].DefaultView; } else { MessageBox.Show("Connection failed."); } } con.Close(); } first = dataGridView1.Rows[0].Cells[3].Value.ToString(); second = dataGridView1.Rows[1].Cells[3].Value.ToString(); third = dataGridView1.Rows[2].Cells[3].Value.ToString(); //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for first if so add to count if (first == countries[i]) { count[i]++; } } //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for second if so add to count if (second == countries[i]) { count[i]++; } } //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for third if so add to count if (third == countries[i]) { count[i]++; } } string View5 = "SELECT * FROM \"ss1500m Times Women\" ORDER BY Times DESC;";//gets team table from database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB if (con.State == ConnectionState.Open) // if connection.Open was successful { //this sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(View5, con); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0].DefaultView; } else { MessageBox.Show("Connection failed."); } } con.Close(); } first = dataGridView1.Rows[0].Cells[3].Value.ToString(); second = dataGridView1.Rows[1].Cells[3].Value.ToString(); third = dataGridView1.Rows[2].Cells[3].Value.ToString(); //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for first if so add to count if (first == countries[i]) { count[i]++; } } //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for second if so add to count if (second == countries[i]) { count[i]++; } } //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for third if so add to count if (third == countries[i]) { count[i]++; } } string View6 = "SELECT * FROM \"Ice Dance Mens Scores\" ORDER BY Score DESC;";//gets team table from database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB if (con.State == ConnectionState.Open) // if connection.Open was successful { //this sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(View6, con); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0].DefaultView; } else { MessageBox.Show("Connection failed."); } } con.Close(); } first = dataGridView1.Rows[0].Cells[3].Value.ToString(); second = dataGridView1.Rows[1].Cells[3].Value.ToString(); third = dataGridView1.Rows[2].Cells[3].Value.ToString(); //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for first if so add to count if (first == countries[i]) { count[i]++; } } //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for second if so add to count if (second == countries[i]) { count[i]++; } } //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for third if so add to count if (third == countries[i]) { count[i]++; } } string View7 = "SELECT * FROM \"Ice Dance Womens Scores\" ORDER BY Score DESC;";//gets team table from database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB if (con.State == ConnectionState.Open) // if connection.Open was successful { //this sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(View7, con); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0].DefaultView; } else { MessageBox.Show("Connection failed."); } } con.Close(); } first = dataGridView1.Rows[0].Cells[3].Value.ToString(); second = dataGridView1.Rows[1].Cells[3].Value.ToString(); third = dataGridView1.Rows[2].Cells[3].Value.ToString(); //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for first if so add to count if (first == countries[i]) { count[i]++; } } //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for second if so add to count if (second == countries[i]) { count[i]++; } } //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for third if so add to count if (third == countries[i]) { count[i]++; } } string View8 = "SELECT * FROM \"Pair Skating Scores\" ORDER BY Score DESC;";//gets team table from database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB if (con.State == ConnectionState.Open) // if connection.Open was successful { //this sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(View8, con); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0].DefaultView; } else { MessageBox.Show("Connection failed."); } } con.Close(); } first = dataGridView1.Rows[0].Cells[3].Value.ToString(); second = dataGridView1.Rows[1].Cells[3].Value.ToString(); third = dataGridView1.Rows[2].Cells[3].Value.ToString(); //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for first if so add to count if (first == countries[i]) { count[i]++; } } //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for second if so add to count if (second == countries[i]) { count[i]++; } } //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for third if so add to count if (third == countries[i]) { count[i]++; } } string View9 = "SELECT * FROM \"Single Skating Men Scores\" ORDER BY Score DESC;";//gets team table from database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB if (con.State == ConnectionState.Open) // if connection.Open was successful { //this sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(View9, con); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0].DefaultView; } else { MessageBox.Show("Connection failed."); } } con.Close(); } first = dataGridView1.Rows[0].Cells[3].Value.ToString(); second = dataGridView1.Rows[1].Cells[3].Value.ToString(); third = dataGridView1.Rows[2].Cells[3].Value.ToString(); //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for first if so add to count if (first == countries[i]) { count[i]++; } } //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for second if so add to count if (second == countries[i]) { count[i]++; } } //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for third if so add to count if (third == countries[i]) { count[i]++; } } string View10 = "SELECT * FROM \"Single Skating Womens Scores\" ORDER BY Score DESC;";//gets team table from database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB if (con.State == ConnectionState.Open) // if connection.Open was successful { //this sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(View10, con); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0].DefaultView; } else { MessageBox.Show("Connection failed."); } } con.Close(); } first = dataGridView1.Rows[0].Cells[3].Value.ToString(); second = dataGridView1.Rows[1].Cells[3].Value.ToString(); third = dataGridView1.Rows[2].Cells[3].Value.ToString(); //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for first if so add to count if (first == countries[i]) { count[i]++; } } //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for second if so add to count if (second == countries[i]) { count[i]++; } } //for loop loops through all possible countries to check for (int i = 0; i < 12; i++) { //if statement checks each country if they match for third if so add to count if (third == countries[i]) { count[i]++; } } //this will display county names in the datagridview string ViewT = "SELECT * FROM 'Team Information'";//gets team table from database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB if (con.State == ConnectionState.Open) // if connection.Open was successful { //this sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(ViewT, con); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0].DefaultView; } else { MessageBox.Show("Connection failed."); } } con.Close(); } //for loop used to populate the medal column for (int i = 0; i < 12; i++) { dataGridView1.Rows[i].Cells[0].Value = count[i]; } }
/// <summary> /// Executes the query. /// </summary> /// <param name="dataSet">The data set to return containing the data.</param> /// <param name="tables">The datatable schema to add.</param> /// <param name="queryText">The query text to execute.</param> /// <param name="commandType">The command type.</param> /// <param name="connectionString">The connection string to use.</param> /// <param name="values">The collection of sql parameters to include.</param> /// <returns>The sql command containing any return values.</returns> public DbCommand ExecuteQuery(ref System.Data.DataSet dataSet, DataTable[] tables, string queryText, CommandType commandType, string connectionString, params DbParameter[] values) { // Initial connection objects. DbCommand dbCommand = null; SqliteClient.SQLiteConnection sqlConnection = null; IDataReader dataReader = null; try { // Create a new connection. using (sqlConnection = new SqliteClient.SQLiteConnection(connectionString)) { // Open the connection. sqlConnection.Open(); // Create the command and assign any parameters. dbCommand = new SqliteClient.SQLiteCommand(DataTypeConversion.GetSqlConversionDataTypeNoContainer( ConnectionContext.ConnectionDataType.SqlDataType, queryText), sqlConnection); dbCommand.CommandType = commandType; if (values != null) { foreach (SqliteClient.SQLiteParameter sqlParameter in values) { dbCommand.Parameters.Add(sqlParameter); } } // Load the data into the table. using (dataReader = dbCommand.ExecuteReader()) { dataSet = new System.Data.DataSet(); dataSet.Tables.AddRange(tables); dataSet.EnforceConstraints = false; dataSet.Load(dataReader, LoadOption.OverwriteChanges, tables); dataReader.Close(); } // Close the database connection. sqlConnection.Close(); } // Return the sql command, including // any parameters that have been // marked as output direction. return(dbCommand); } catch (Exception ex) { // Throw a general exception. throw new Exception(ex.Message, ex.InnerException); } finally { if (dataReader != null) { dataReader.Close(); } if (sqlConnection != null) { sqlConnection.Close(); } } }
void OpenIt() { conn = new System.Data.SQLite.SQLiteConnection("Data Source=" + dbpath + ";New=False"); conn.Open(); }
private void UpdateDBTransaction() { /// ///UPDATE TRANSACTION INTO LOCAL SQLite DATABASE /// string dbFile = @Program.SQLITE_DATABASE_NAME; string connString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", dbFile); using (SQLiteConnection dbConn = new System.Data.SQLite.SQLiteConnection(connString)) { dbConn.Open(); using (System.Data.SQLite.SQLiteCommand cmd = dbConn.CreateCommand()) { // ------------------------------- // Current KioskData.db data // ------------------------------- cmd.CommandText = @"UPDATE RES_KEY SET IN_DATETIME = @ReturnDate, KEY_BOX_IN_NO = @KeyBoxNumIn, TRANSACTION_IN_NO = @TransactionNum, CAR_DAMAGED = @CarDamaged, CAR_CLEANED = @CarCleaned, CAR_REFUELED = @CarRefueled WHERE RESV_RESERV_NO = @ResvNum "; //parameterized update - more flexibility on parameter creation cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter {// SQLite date format is: yyyy-MM-dd HH:mm:ss ParameterName = "@ReturnDate", Value = this.SQLiteDateAndTime, }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@KeyBoxNumIn", Value = this.BoxNumber, }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@TransactionNum", Value = this.IdentificationType, }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@CarDamaged", Value = this.CarDamaged, }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@CarCleaned", Value = this.CarCleaned, }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@CarRefueled", Value = this.CarRefueled, }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@ResvNum", Value = this.AccessCode }); cmd.ExecuteNonQuery(); } Program.logEvent("Local SQLite Database Transaction Updated Successfully"); if (dbConn.State != System.Data.ConnectionState.Closed) { dbConn.Close(); } } }
private void button4_Click(object sender, EventArgs e) { if (comboBox2.SelectedIndex == 1) { int cantidad; if (int.TryParse(textBox12.Text, out cantidad)) { float peso; if (float.TryParse(textBox14.Text, out peso)) { string appPath = Path.GetDirectoryName(Application.ExecutablePath); System.Data.SQLite.SQLiteConnection sqlConnection1 = new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBBIT.s3db ;Version=3;"); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandType = System.Data.CommandType.Text; //comando sql para insercion cmd.CommandText = "INSERT INTO Salidas (Numero, Salida, Descripcion, Cantidad, Peso, Equivalentes, Nota, TipoDeApoyo, Responsable, Huella) values ('" + label12.Text + "', '" + textBox9.Text + "', '" + textBox11.Text + "', '" + textBox12.Text + "', '" + textBox14.Text + "', '" + textBox16.Text + "', '" + textBox15.Text + "', '" + textBox13.Text + "', '" + textBox10.Text + "',@0 )"; SQLiteParameter param = new SQLiteParameter("@0", System.Data.DbType.Binary); cmd.Connection = sqlConnection1; param.Value = fp_image; cmd.Parameters.Add(param); sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); MessageBox.Show("Salida guardada con exito."); textBox9.Text = ""; textBox11.Text = ""; textBox12.Text = ""; textBox14.Text = ""; textBox16.Text = ""; textBox15.Text = ""; textBox13.Text = ""; textBox10.Text = ""; ///create the connection string string connString = @"Data Source= " + appPath + @"\DBBIT.s3db ;Version=3;"; //create the database query string query = "SELECT * FROM Salidas"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); dAdapter.Update(dTable); if (dTable.Rows.Count != 0) { DataRow Row = dTable.Rows[dTable.Rows.Count - 1]; string num = Row["Numero"].ToString(); int autonum = Int32.Parse(num); label12.Text = (autonum + 1).ToString(); } else { label12.Text = "1"; } } else { MessageBox.Show("Revise de nuevo su campo de peso, solo se permiten numeros flotantes."); } } else { MessageBox.Show("Revise de nuevo su campo de cantidad, solo se permiten numeros."); } } }
private void button3_Click(object sender, EventArgs e) { double total; double[] num = new double[7]; num[0] = Convert.ToDouble(textBox2.Text); num[1] = Convert.ToDouble(textBox3.Text); num[2] = Convert.ToDouble(textBox4.Text); num[3] = Convert.ToDouble(textBox5.Text); num[4] = Convert.ToDouble(textBox6.Text); num[5] = Convert.ToDouble(textBox7.Text); num[6] = Convert.ToDouble(textBox8.Text); total = num[0] + num[1] + num[2] + num[3] + num[4] + num[5] + num[6]; total = total - (num.Min() + num.Max()); total = total / 5; //class created to store athletes info Athlete AthleteScore = new Athlete(); //stores name of athlete AthleteScore.firstName = dataGridView1.SelectedCells[1].Value.ToString(); AthleteScore.lastName = dataGridView1.SelectedCells[2].Value.ToString(); //creates variable for connection string using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open();//opens DB //checks connection and fills grid view with Race table if (con.State == ConnectionState.Open) { //adds entry from event to the specific event string sql = String.Format("UPDATE " + SelectedEvent + " SET Score = " + total + " WHERE \"Athlete First Name\" = '" + AthleteScore.firstName + "' AND \"Athlete Last Name\" = '" + AthleteScore.lastName + "';"); DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(sql, con); da.Fill(ds); } else { MessageBox.Show("Connection failed."); } } } MessageBox.Show("Score has been entered into " + AthleteScore.firstName + " " + AthleteScore.lastName + "'s score"); //this will update the score board to reflect the changes string View = "SELECT * FROM " + SelectedEvent + " ORDER BY Score DESC;";//gets team table from database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB if (con.State == ConnectionState.Open) // if connection.Open was successful { //this sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(View, con); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0].DefaultView; } else { MessageBox.Show("Connection failed."); } } con.Close(); } }
private void button1_Click(object sender, EventArgs e) { //class created to store athletes info Athlete AddAthlete = new Athlete(); //objects used to store the athletes info AddAthlete.firstName = dataGridView1.SelectedCells[1].Value.ToString(); AddAthlete.lastName = dataGridView1.SelectedCells[2].Value.ToString(); //stores country name string country = dataGridView1.SelectedCells[0].Value.ToString(); //select puts the selected event into a string string selected = listBox1.GetItemText(listBox1.SelectedItem); if (selected == "Mens 500m Speed Skating, Monday, 12:00pm, Rink 1") { //creates variable for connection string using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB //checks connection and fills grid view with Race table if (con.State == ConnectionState.Open) { //adds entry to athlete information string sql = String.Format("INSERT INTO Events ('Event Name', 'Team Name', 'Athlete First Name'," + "'Athlete Last Name', 'Event_Time', 'Event Day', 'Rink') VALUES('Mens 500m Speed Skating', '" + country + "', '" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '12:00pm', 'Monday', 'Rink 1');"); DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(sql, con); da.Fill(ds); } else { MessageBox.Show("Connection failed."); } } con.Close(); MessageBox.Show("Athlete has been entered into Event!"); //creates variable for connection string using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conn)) { conn.Open(); //opens DB //checks connection and fills grid view with Race table if (conn.State == ConnectionState.Open) { //adds entry to athlete information string sql = String.Format("INSERT INTO \"ss500m Times Men\" ('Athlete First Name', 'Athlete Last Name', " + "'Team Name') VALUES('" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '" + country + "');"); DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(sql, conn); da.Fill(ds); conn.Close(); } else { MessageBox.Show("Connection failed."); } } } } } if (selected == "Womens 500m Speed Skating, Monday, 12:00pm Rink 2") { //creates variable for connection string using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB //checks connection and fills grid view with Race table if (con.State == ConnectionState.Open) { //adds entry to athlete information string sql = String.Format("INSERT INTO Events ('Event Name', 'Team Name', 'Athlete First Name'," + "'Athlete Last Name', 'Event_Time', 'Event Day', 'Rink') VALUES('Womens 500m Speed Skating', '" + country + "', '" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '12:00pm', 'Monday', 'Rink 2');"); DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(sql, con); da.Fill(ds); } else { MessageBox.Show("Connection failed."); } } MessageBox.Show("Athlete has been entered into Event!"); con.Close(); } //creates variable for connection string using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conn)) { conn.Open(); //opens DB //checks connection and fills grid view with Race table if (conn.State == ConnectionState.Open) { //adds entry to athlete information string sql = String.Format("INSERT INTO \"ss500m Times Women\" ('Athlete First Name', 'Athlete Last Name', " + "'Team Name') VALUES('" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '" + country + "');"); DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(sql, conn); da.Fill(ds); conn.Close(); } else { MessageBox.Show("Connection failed."); } } } } if (selected == "Mens 1000m Speed Skating, Tueday, 12:00pm, Rink 1") { //creates variable for connection string using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB //checks connection and fills grid view with Race table if (con.State == ConnectionState.Open) { //adds entry to athlete information string sql = String.Format("INSERT INTO Events ('Event Name', 'Team Name', 'Athlete First Name'," + "'Athlete Last Name', 'Event_Time', 'Event Day', 'Rink') VALUES('Mens 1000m Speed Skating', '" + country + "', '" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '12:00pm', 'Tuesday', 'Rink 1');"); DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(sql, con); da.Fill(ds); } else { MessageBox.Show("Connection failed."); } } MessageBox.Show("Athlete has been entered into Event!"); con.Close(); } //creates variable for connection string using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conn)) { conn.Open(); //opens DB //checks connection and fills grid view with Race table if (conn.State == ConnectionState.Open) { //adds entry to athlete information string sql = String.Format("INSERT INTO \"ss1000m Times Men\" ('Athlete First Name', 'Athlete Last Name', " + "'Team Name') VALUES('" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '" + country + "');"); DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(sql, conn); da.Fill(ds); conn.Close(); } else { MessageBox.Show("Connection failed."); } } } } if (selected == "Womens 1000m Speed Skating, Tuesday, 12:00pm, Rink 2") { //creates variable for connection string using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB //checks connection and fills grid view with Race table if (con.State == ConnectionState.Open) { //adds entry to athlete information string sql = String.Format("INSERT INTO Events ('Event Name', 'Team Name', 'Athlete First Name'," + "'Athlete Last Name', 'Event_Time', 'Event Day', 'Rink') VALUES('Womens 1000m Speed Skating', '" + country + "', '" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '12:00pm', 'Tuesday', 'Rink 2');"); DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(sql, con); da.Fill(ds); } else { MessageBox.Show("Connection failed."); } } MessageBox.Show("Athlete has been entered into Event!"); con.Close(); } //creates variable for connection string using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conn)) { conn.Open(); //opens DB //checks connection and fills grid view with Race table if (conn.State == ConnectionState.Open) { //adds entry to athlete information string sql = String.Format("INSERT INTO \"ss1000m Times Women\" ('Athlete First Name', 'Athlete Last Name', " + "'Team Name') VALUES('" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '" + country + "');"); DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(sql, conn); da.Fill(ds); conn.Close(); } else { MessageBox.Show("Connection failed."); } } } } if (selected == "Mens 1500m Speed Skating, Wednesday, 12:00pm, Rink 1") { //creates variable for connection string using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB //checks connection and fills grid view with Race table if (con.State == ConnectionState.Open) { //adds entry to athlete information string sql = String.Format("INSERT INTO Events ('Event Name', 'Team Name', 'Athlete First Name'," + "'Athlete Last Name', 'Event_Time', 'Event Day', 'Rink') VALUES('Mens 1500m Speed Skating', '" + country + "', '" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '12:00pm', 'Wednesday', 'Rink 1');"); DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(sql, con); da.Fill(ds); } else { MessageBox.Show("Connection failed."); } } MessageBox.Show("Athlete has been entered into Event!"); con.Close(); } //creates variable for connection string using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conn)) { conn.Open(); //opens DB //checks connection and fills grid view with Race table if (conn.State == ConnectionState.Open) { //adds entry to athlete information string sql = String.Format("INSERT INTO \"ss1500m Times Men\" ('Athlete First Name', 'Athlete Last Name', " + "'Team Name') VALUES('" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '" + country + "');"); DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(sql, conn); da.Fill(ds); conn.Close(); } else { MessageBox.Show("Connection failed."); } } } } if (selected == "Womens 1500m Speed Skating, Wednesday, 12:00pm, Rink 2") { //creates variable for connection string using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB //checks connection and fills grid view with Race table if (con.State == ConnectionState.Open) { //adds entry to athlete information string sql = String.Format("INSERT INTO Events ('Event Name', 'Team Name', 'Athlete First Name'," + "'Athlete Last Name', 'Event_Time', 'Event Day', 'Rink') VALUES('Womens 1500m Speed Skating', '" + country + "', '" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '12:00pm', 'Wednesday', 'Rink 2');"); DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(sql, con); da.Fill(ds); } else { MessageBox.Show("Connection failed."); } } MessageBox.Show("Athlete has been entered into Event!"); con.Close(); } //creates variable for connection string using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conn)) { conn.Open(); //opens DB //checks connection and fills grid view with Race table if (conn.State == ConnectionState.Open) { //adds entry to athlete information string sql = String.Format("INSERT INTO \"ss1500m Times Women\" ('Athlete First Name', 'Athlete Last Name', " + "'Team Name') VALUES('" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '" + country + "');"); DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(sql, conn); da.Fill(ds); conn.Close(); } else { MessageBox.Show("Connection failed."); } } } } if (selected == "Single Skating Mens, Monday, 11:00am, Rink 3") { //creates variable for connection string using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB //checks connection and fills grid view with Race table if (con.State == ConnectionState.Open) { //adds entry to athlete information string sql = String.Format("INSERT INTO Events ('Event Name', 'Team Name', 'Athlete First Name'," + "'Athlete Last Name', 'Event_Time', 'Event Day', 'Rink') VALUES('Single Skating Mens', '" + country + "', '" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '11:00am', 'Monday', 'Rink 3');"); DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(sql, con); da.Fill(ds); } else { MessageBox.Show("Connection failed."); } } MessageBox.Show("Athlete has been entered into Event!"); con.Close(); } //creates variable for connection string using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conn)) { conn.Open(); //opens DB //checks connection and fills grid view with Race table if (conn.State == ConnectionState.Open) { //adds entry to athlete information string sql = String.Format("INSERT INTO \"Single Skating Men Scores\" ('Athlete First Name', 'Athlete Last Name', " + "'Team Name') VALUES('" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '" + country + "');"); DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(sql, conn); da.Fill(ds); conn.Close(); } else { MessageBox.Show("Connection failed."); } } } } if (selected == "Single Skating Womens, Monday, 11:00am, Rink 4") { //creates variable for connection string using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB //checks connection and fills grid view with Race table if (con.State == ConnectionState.Open) { //adds entry to athlete information string sql = String.Format("INSERT INTO Events ('Event Name', 'Team Name', 'Athlete First Name'," + "'Athlete Last Name', 'Event_Time', 'Event Day', 'Rink') VALUES('Single Skating Womens', '" + country + "', '" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '11:00am', 'Monday', 'Rink 4');"); DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(sql, con); da.Fill(ds); } else { MessageBox.Show("Connection failed."); } } MessageBox.Show("Athlete has been entered into Event!"); con.Close(); } //creates variable for connection string using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conn)) { conn.Open(); //opens DB //checks connection and fills grid view with Race table if (conn.State == ConnectionState.Open) { //adds entry to athlete information string sql = String.Format("INSERT INTO \"Single Skating Womens Scores\" ('Athlete First Name', 'Athlete Last Name', " + "'Team Name') VALUES('" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '" + country + "');"); DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(sql, conn); da.Fill(ds); conn.Close(); } else { MessageBox.Show("Connection failed."); } } } } if (selected == "Ice Dance Mens, Tuesday, 2:00pm, Rink 3") { //creates variable for connection string using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB //checks connection and fills grid view with Race table if (con.State == ConnectionState.Open) { //adds entry to athlete information string sql = String.Format("INSERT INTO Events ('Event Name', 'Team Name', 'Athlete First Name'," + "'Athlete Last Name', 'Event_Time', 'Event Day', 'Rink') VALUES('Ice Dance Mens', '" + country + "', '" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '2:00pm', 'Tuesday', 'Rink 3');"); DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(sql, con); da.Fill(ds); } else { MessageBox.Show("Connection failed."); } } MessageBox.Show("Athlete has been entered into Event!"); con.Close(); } //creates variable for connection string using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conn)) { conn.Open(); //opens DB //checks connection and fills grid view with Race table if (conn.State == ConnectionState.Open) { //adds entry to athlete information string sql = String.Format("INSERT INTO \"Ice Dance Mens Scores\" ('Athlete First Name', 'Athlete Last Name', " + "'Team Name') VALUES('" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '" + country + "');"); DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(sql, conn); da.Fill(ds); conn.Close(); } else { MessageBox.Show("Connection failed."); } } } } if (selected == "Ice Dance Womens, Tuesday, 2:00pm, Rink 4") { //creates variable for connection string using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB //checks connection and fills grid view with Race table if (con.State == ConnectionState.Open) { //adds entry to athlete information string sql = String.Format("INSERT INTO Events ('Event Name', 'Team Name', 'Athlete First Name'," + "'Athlete Last Name', 'Event_Time', 'Event Day', 'Rink') VALUES('Ice Dance Womens', '" + country + "', '" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '2:00pm', 'Tuesday', 'Rink 4');"); DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(sql, con); da.Fill(ds); } else { MessageBox.Show("Connection failed."); } } MessageBox.Show("Athlete has been entered into Event!"); con.Close(); } //creates variable for connection string using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conn)) { conn.Open(); //opens DB //checks connection and fills grid view with Race table if (conn.State == ConnectionState.Open) { //adds entry to athlete information string sql = String.Format("INSERT INTO \"Ice Dance Womens Scores\" ('Athlete First Name', 'Athlete Last Name', " + "'TeamName') VALUES('" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '" + country + "');"); DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(sql, conn); da.Fill(ds); conn.Close(); } else { MessageBox.Show("Connection failed."); } } } } if (selected == "Couple Skating, Monday, 5:00pm, Rink 3") { //creates variable for connection string using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB //checks connection and fills grid view with Race table if (con.State == ConnectionState.Open) { //adds entry to athlete information string sql = String.Format("INSERT INTO Events ('Event Name', 'Team Name', 'Athlete First Name'," + "'Athlete Last Name', 'Event_Time', 'Event Day', 'Rink') VALUES('Couple Skating', '" + country + "', '" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '5:00pm', 'Tuesday', 'Rink 3');"); DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(sql, con); da.Fill(ds); } else { MessageBox.Show("Connection failed."); } } MessageBox.Show("Athlete has been entered into Event!"); con.Close(); } //creates variable for connection string using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(conn)) { conn.Open(); //opens DB //checks connection and fills grid view with Race table if (conn.State == ConnectionState.Open) { //adds entry to athlete information string sql = String.Format("INSERT INTO \"Pair Skating Scores\" ('Athlete First Name', 'Athlete Last Name', " + "'Team Name') VALUES('" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '" + country + "');"); DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(sql, conn); da.Fill(ds); conn.Close(); } else { MessageBox.Show("Connection failed."); } } } } //this will repopulate Event with the added entry string View = "SELECT * FROM 'Events'"; //gets team table from database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB if (con.State == ConnectionState.Open) // if connection.Open was successful { //this sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(View, con); da.Fill(ds); dataGridView2.DataSource = ds.Tables[0].DefaultView; } else { MessageBox.Show("Connection failed."); } } con.Close(); } }
/// <summary> /// Open失敗時は例外を投げる /// </summary> public void Open() { conn_.Open(); }
static public void CreateNewDbase() { #region OrderTable creator string // This is the query which will create a new table in our database file. An auto increment column called "ID", and many NVARCHAR type columns string createTableQuery1 = @"CREATE TABLE IF NOT EXISTS [OrderTable] ( [ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [PO] NVARCHAR(80) NULL, [Date] VARCHAR(10) NULL, [EndUser] VARCHAR(512) NULL, [Equipment] VARCHAR(512) NULL, [VendorName] VARCHAR(512) NULL, [JobNumber] VARCHAR(512) NULL, [CustomerPO] VARCHAR(512) NULL, [VendorNumber] VARCHAR(512) NULL, [SalesAss] VARCHAR(512) NULL, [SoldTo] VARCHAR(512) NULL, [Street1] VARCHAR(512) NULL, [Street2] VARCHAR(512) NULL, [City] VARCHAR(512) NULL, [State] VARCHAR(512) NULL, [Zip] VARCHAR(512) NULL, [ShipTo] VARCHAR(512) NULL, [ShipStreet1] VARCHAR(512) NULL, [ShipStreet2] VARCHAR(512) NULL, [ShipCity] VARCHAR(512) NULL, [ShipState] VARCHAR(512) NULL, [ShipZip] VARCHAR(512) NULL, [Carrier] VARCHAR(512) NULL, [ShipDate] VARCHAR(10) NULL, [IsComOrder] VARCHAR(1) NULL, [IsComPaid] VARCHAR(1) NULL, [Grinder] VARCHAR(512) NULL, [SerialNo] VARCHAR(512) NULL, [PumpStk] VARCHAR(512) NULL, [ReqDate] VARCHAR(10) NULL, [SchedShip] VARCHAR(512) NULL, [PODate] VARCHAR(10) NULL, [POShipVia] VARCHAR(512) NULL, [TrackDate1] VARCHAR(10) NULL, [TrackBy1] VARCHAR(512) NULL, [TrackSource1] VARCHAR(512) NULL, [TrackNote1] VARCHAR(512) NULL, [TrackDate2] VARCHAR(10) NULL, [TrackBy2] VARCHAR(512) NULL, [TrackSource2] VARCHAR(512) NULL, [TrackNote2] VARCHAR(512) NULL, [TrackDate3] VARCHAR(10) NULL, [TrackBy3] VARCHAR(512) NULL, [TrackSource3] VARCHAR(512) NULL, [TrackNote3] VARCHAR(512) NULL, [TrackDate4] VARCHAR(10) NULL, [TrackBy4] VARCHAR(512) NULL, [TrackSource4] VARCHAR(512) NULL, [TrackNote4] VARCHAR(512) NULL, [TrackDate5] VARCHAR(10) NULL, [TrackBy5] VARCHAR(512) NULL, [TrackSource5] VARCHAR(512) NULL, [TrackNote5] VARCHAR(512) NULL, [TrackDate6] VARCHAR(10) NULL, [TrackBy6] VARCHAR(512) NULL, [TrackSource6] VARCHAR(512) NULL, [TrackNote6] VARCHAR(512) NULL, [TrackDate7] VARCHAR(10) NULL, [TrackBy7] VARCHAR(512) NULL, [TrackSource7] VARCHAR(512) NULL, [TrackNote7] VARCHAR(512) NULL, [TrackDate8] VARCHAR(10) NULL, [TrackBy8] VARCHAR(512) NULL, [TrackSource8] VARCHAR(512) NULL, [TrackNote8] VARCHAR(512) NULL, [TrackDate9] VARCHAR(10) NULL, [TrackBy9] VARCHAR(512) NULL, [TrackSource9] VARCHAR(512) NULL, [TrackNote9] VARCHAR(512) NULL, [TrackDate10] VARCHAR(10) NULL, [TrackBy10] VARCHAR(512) NULL, [TrackSource10] VARCHAR(512) NULL, [TrackNote10] VARCHAR(512) NULL, [TrackDate11] VARCHAR(10) NULL, [TrackBy11] VARCHAR(512) NULL, [TrackSource11] VARCHAR(512) NULL, [TrackNote11] VARCHAR(512) NULL, [TrackDate12] VARCHAR(10) NULL, [TrackBy12] VARCHAR(512) NULL, [TrackSource12] VARCHAR(512) NULL, [TrackNote12] VARCHAR(512) NULL, [TrackDate13] VARCHAR(10) NULL, [TrackBy13] VARCHAR(512) NULL, [TrackSource13] VARCHAR(512) NULL, [TrackNote13] VARCHAR(512) NULL, [TrackDate14] VARCHAR(10) NULL, [TrackBy14] VARCHAR(512) NULL, [TrackSource14] VARCHAR(512) NULL, [TrackNote14] VARCHAR(512) NULL, [TrackDate15] VARCHAR(10) NULL, [TrackBy15] VARCHAR(512) NULL, [TrackSource15] VARCHAR(512) NULL, [TrackNote15] VARCHAR(512) NULL, [TrackDate16] VARCHAR(10) NULL, [TrackBy16] VARCHAR(512) NULL, [TrackSource16] VARCHAR(512) NULL, [TrackNote16] VARCHAR(512) NULL, [TrackDate17] VARCHAR(10) NULL, [TrackBy17] VARCHAR(512) NULL, [TrackSource17] VARCHAR(512) NULL, [TrackNote17] VARCHAR(512) NULL, [TrackDate18] VARCHAR(10) NULL, [TrackBy18] VARCHAR(512) NULL, [TrackSource18] VARCHAR(512) NULL, [TrackNote18] VARCHAR(512) NULL, [QuotePrice] VARCHAR(512) NULL, [Credit] VARCHAR(512) NULL, [Freight] VARCHAR(512) NULL, [ShopTime] VARCHAR(512) NULL, [TotalCost] VARCHAR(512) NULL, [GrossProfit] VARCHAR(512) NULL, [Profit] VARCHAR(512) NULL, [Description] VARCHAR(512) NULL, [Quant1] VARCHAR(512) NULL, [Descr1] VARCHAR(512) NULL, [Costs1] VARCHAR(512) NULL, [ECost1] VARCHAR(512) NULL, [Quant2] VARCHAR(512) NULL, [Descr2] VARCHAR(512) NULL, [Costs2] VARCHAR(512) NULL, [ECost2] VARCHAR(512) NULL, [Quant3] VARCHAR(512) NULL, [Descr3] VARCHAR(512) NULL, [Costs3] VARCHAR(512) NULL, [ECost3] VARCHAR(512) NULL, [Quant4] VARCHAR(512) NULL, [Descr4] VARCHAR(512) NULL, [Costs4] VARCHAR(512) NULL, [ECost4] VARCHAR(512) NULL, [Quant5] VARCHAR(512) NULL, [Descr5] VARCHAR(512) NULL, [Costs5] VARCHAR(512) NULL, [ECost5] VARCHAR(512) NULL, [Quant6] VARCHAR(512) NULL, [Descr6] VARCHAR(512) NULL, [Costs6] VARCHAR(512) NULL, [ECost6] VARCHAR(512) NULL, [Quant7] VARCHAR(512) NULL, [Descr7] VARCHAR(512) NULL, [Costs7] VARCHAR(512) NULL, [ECost7] VARCHAR(512) NULL, [Quant8] VARCHAR(512) NULL, [Descr8] VARCHAR(512) NULL, [Costs8] VARCHAR(512) NULL, [ECost8] VARCHAR(512) NULL, [Quant9] VARCHAR(512) NULL, [Descr9] VARCHAR(512) NULL, [Costs9] VARCHAR(512) NULL, [ECost9] VARCHAR(512) NULL, [Quant10] VARCHAR(512) NULL, [Descr10] VARCHAR(512) NULL, [Costs10] VARCHAR(512) NULL, [ECost10] VARCHAR(512) NULL, [Quant11] VARCHAR(512) NULL, [Descr11] VARCHAR(512) NULL, [Costs11] VARCHAR(512) NULL, [ECost11] VARCHAR(512) NULL, [Quant12] VARCHAR(512) NULL, [Descr12] VARCHAR(512) NULL, [Costs12] VARCHAR(512) NULL, [ECost12] VARCHAR(512) NULL, [Quant13] VARCHAR(512) NULL, [Descr13] VARCHAR(512) NULL, [Costs13] VARCHAR(512) NULL, [ECost13] VARCHAR(512) NULL, [Quant14] VARCHAR(512) NULL, [Descr14] VARCHAR(512) NULL, [Costs14] VARCHAR(512) NULL, [ECost14] VARCHAR(512) NULL, [Quant15] VARCHAR(512) NULL, [Descr15] VARCHAR(512) NULL, [Costs15] VARCHAR(512) NULL, [ECost15] VARCHAR(512) NULL, [Quant16] VARCHAR(512) NULL, [Descr16] VARCHAR(512) NULL, [Costs16] VARCHAR(512) NULL, [ECost16] VARCHAR(512) NULL, [Quant17] VARCHAR(512) NULL, [Descr17] VARCHAR(512) NULL, [Costs17] VARCHAR(512) NULL, [ECost17] VARCHAR(512) NULL, [Quant18] VARCHAR(512) NULL, [Descr18] VARCHAR(512) NULL, [Costs18] VARCHAR(512) NULL, [ECost18] VARCHAR(512) NULL, [Quant19] VARCHAR(512) NULL, [Descr19] VARCHAR(512) NULL, [Costs19] VARCHAR(512) NULL, [ECost19] VARCHAR(512) NULL, [Quant20] VARCHAR(512) NULL, [Descr20] VARCHAR(512) NULL, [Costs20] VARCHAR(512) NULL, [ECost20] VARCHAR(512) NULL, [Quant21] VARCHAR(512) NULL, [Descr21] VARCHAR(512) NULL, [Costs21] VARCHAR(512) NULL, [ECost21] VARCHAR(512) NULL, [Quant22] VARCHAR(512) NULL, [Descr22] VARCHAR(512) NULL, [Costs22] VARCHAR(512) NULL, [ECost22] VARCHAR(512) NULL, [Quant23] VARCHAR(512) NULL, [Descr23] VARCHAR(512) NULL, [Costs23] VARCHAR(512) NULL, [ECost23] VARCHAR(512) NULL, [InvInstructions] VARCHAR(512) NULL, [InvNotes] VARCHAR(512) NULL, [VendorNotes] VARCHAR(512) NULL, [CrMemo] VARCHAR(512) NULL, [InvNumber] VARCHAR(512) NULL, [InvDate] VARCHAR(10) NULL, [Status] VARCHAR(512) NULL, [CheckNumbers] VARCHAR(512) NULL, [CheckDates] VARCHAR(512) NULL, [ComDate1] VARCHAR(10) NULL, [ComCheckNumber1] VARCHAR(512) NULL, [ComPaid1] VARCHAR(512) NULL, [ComDate2] VARCHAR(10) NULL, [ComCheckNumber2] VARCHAR(512) NULL, [ComPaid2] VARCHAR(512) NULL, [ComDate3] VARCHAR(10) NULL, [ComCheckNumber3] VARCHAR(512) NULL, [ComPaid3] VARCHAR(512) NULL, [ComDate4] VARCHAR(10) NULL, [ComCheckNumber4] VARCHAR(512) NULL, [ComPaid4] VARCHAR(512) NULL, [ComDate5] VARCHAR(10) NULL, [ComCheckNumber5] VARCHAR(512) NULL, [ComPaid5] VARCHAR(512) NULL, [ComAmount] VARCHAR(512) NULL, [ComBalance] VARCHAR(512) NULL, [DeliveryNotes] VARCHAR(512) NULL, [PONotes] VARCHAR(512) NULL, [Spare1] VARCHAR(512) NULL, [Spare2] VARCHAR(512) NULL, [Spare3] VARCHAR(512) NULL, [Spare4] VARCHAR(512) NULL, [Spare5] VARCHAR(512) NULL )"; #endregion #region QuoteTable creator string // This is the query which will create a new table in our database file. An auto increment column called "ID", and many NVARCHAR type columns string createTableQuery2 = @"CREATE TABLE IF NOT EXISTS [QuoteTable] ( [ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [PO] NVARCHAR(80) NULL, [Date] VARCHAR(10) NULL, [Status] VARCHAR(512) NULL, [Company] VARCHAR(512) NULL, [VendorName] VARCHAR(512) NULL, [JobName] VARCHAR(512) NULL, [CustomerPO] VARCHAR(512) NULL, [VendorNumber] VARCHAR(512) NULL, [SalesAss] VARCHAR(512) NULL, [SoldTo] VARCHAR(512) NULL, [Street1] VARCHAR(512) NULL, [Street2] VARCHAR(512) NULL, [City] VARCHAR(512) NULL, [State] VARCHAR(512) NULL, [Zip] VARCHAR(512) NULL, [Delivery] VARCHAR(512) NULL, [Terms] VARCHAR(10) NULL, [FreightSelect] VARCHAR(512) NULL, [IsQManual] VARCHAR(2) NULL, [IsPManual] VARCHAR(2) NULL, [Location] VARCHAR(512) NULL, [Equipment] VARCHAR(10) NULL, [EquipCategory] VARCHAR(512) NULL, [QuotePrice] VARCHAR(512) NULL, [Credit] VARCHAR(512) NULL, [Freight] VARCHAR(512) NULL, [ShopTime] VARCHAR(512) NULL, [TotalCost] VARCHAR(512) NULL, [GrossProfit] VARCHAR(512) NULL, [Profit] VARCHAR(512) NULL, [MarkUp] VARCHAR(512) NULL, [Description] VARCHAR(512) NULL, [Quant1] VARCHAR(512) NULL, [Descr1] VARCHAR(512) NULL, [Costs1] VARCHAR(512) NULL, [ECost1] VARCHAR(512) NULL, [Price1] VARCHAR(512) NULL, [Quant2] VARCHAR(512) NULL, [Descr2] VARCHAR(512) NULL, [Costs2] VARCHAR(512) NULL, [ECost2] VARCHAR(512) NULL, [Price2] VARCHAR(512) NULL, [Quant3] VARCHAR(512) NULL, [Descr3] VARCHAR(512) NULL, [Costs3] VARCHAR(512) NULL, [ECost3] VARCHAR(512) NULL, [Price3] VARCHAR(512) NULL, [Quant4] VARCHAR(512) NULL, [Descr4] VARCHAR(512) NULL, [Costs4] VARCHAR(512) NULL, [ECost4] VARCHAR(512) NULL, [Price4] VARCHAR(512) NULL, [Quant5] VARCHAR(512) NULL, [Descr5] VARCHAR(512) NULL, [Costs5] VARCHAR(512) NULL, [ECost5] VARCHAR(512) NULL, [Price5] VARCHAR(512) NULL, [Quant6] VARCHAR(512) NULL, [Descr6] VARCHAR(512) NULL, [Costs6] VARCHAR(512) NULL, [ECost6] VARCHAR(512) NULL, [Price6] VARCHAR(512) NULL, [Quant7] VARCHAR(512) NULL, [Descr7] VARCHAR(512) NULL, [Costs7] VARCHAR(512) NULL, [ECost7] VARCHAR(512) NULL, [Price7] VARCHAR(512) NULL, [Quant8] VARCHAR(512) NULL, [Descr8] VARCHAR(512) NULL, [Costs8] VARCHAR(512) NULL, [ECost8] VARCHAR(512) NULL, [Price8] VARCHAR(512) NULL, [Quant9] VARCHAR(512) NULL, [Descr9] VARCHAR(512) NULL, [Costs9] VARCHAR(512) NULL, [ECost9] VARCHAR(512) NULL, [Price9] VARCHAR(512) NULL, [Quant10] VARCHAR(512) NULL, [Descr10] VARCHAR(512) NULL, [Costs10] VARCHAR(512) NULL, [ECost10] VARCHAR(512) NULL, [Price10] VARCHAR(512) NULL, [Quant11] VARCHAR(512) NULL, [Descr11] VARCHAR(512) NULL, [Costs11] VARCHAR(512) NULL, [ECost11] VARCHAR(512) NULL, [Price11] VARCHAR(512) NULL, [Quant12] VARCHAR(512) NULL, [Descr12] VARCHAR(512) NULL, [Costs12] VARCHAR(512) NULL, [ECost12] VARCHAR(512) NULL, [Price12] VARCHAR(512) NULL, [Quant13] VARCHAR(512) NULL, [Descr13] VARCHAR(512) NULL, [Costs13] VARCHAR(512) NULL, [ECost13] VARCHAR(512) NULL, [Price13] VARCHAR(512) NULL, [Quant14] VARCHAR(512) NULL, [Descr14] VARCHAR(512) NULL, [Costs14] VARCHAR(512) NULL, [ECost14] VARCHAR(512) NULL, [Price14] VARCHAR(512) NULL, [Quant15] VARCHAR(512) NULL, [Descr15] VARCHAR(512) NULL, [Costs15] VARCHAR(512) NULL, [ECost15] VARCHAR(512) NULL, [Price15] VARCHAR(512) NULL, [Quant16] VARCHAR(512) NULL, [Descr16] VARCHAR(512) NULL, [Costs16] VARCHAR(512) NULL, [ECost16] VARCHAR(512) NULL, [Price16] VARCHAR(512) NULL, [Quant17] VARCHAR(512) NULL, [Descr17] VARCHAR(512) NULL, [Costs17] VARCHAR(512) NULL, [ECost17] VARCHAR(512) NULL, [Price17] VARCHAR(512) NULL, [Quant18] VARCHAR(512) NULL, [Descr18] VARCHAR(512) NULL, [Costs18] VARCHAR(512) NULL, [ECost18] VARCHAR(512) NULL, [Price18] VARCHAR(512) NULL, [Quant19] VARCHAR(512) NULL, [Descr19] VARCHAR(512) NULL, [Costs19] VARCHAR(512) NULL, [ECost19] VARCHAR(512) NULL, [Price19] VARCHAR(512) NULL, [Quant20] VARCHAR(512) NULL, [Descr20] VARCHAR(512) NULL, [Costs20] VARCHAR(512) NULL, [ECost20] VARCHAR(512) NULL, [Price20] VARCHAR(512) NULL, [Quant21] VARCHAR(512) NULL, [Descr21] VARCHAR(512) NULL, [Costs21] VARCHAR(512) NULL, [ECost21] VARCHAR(512) NULL, [Price21] VARCHAR(512) NULL, [Quant22] VARCHAR(512) NULL, [Descr22] VARCHAR(512) NULL, [Costs22] VARCHAR(512) NULL, [ECost22] VARCHAR(512) NULL, [Price22] VARCHAR(512) NULL, [Quant23] VARCHAR(512) NULL, [Descr23] VARCHAR(512) NULL, [Costs23] VARCHAR(512) NULL, [ECost23] VARCHAR(512) NULL, [Price23] VARCHAR(512) NULL, [InvNotes] VARCHAR(512) NULL, [DeliveryNotes] VARCHAR(512) NULL, [QuoteNotes] VARCHAR(512) NULL, [Spare1] VARCHAR(512) NULL, [Spare2] VARCHAR(512) NULL, [Spare3] VARCHAR(512) NULL, [Spare4] VARCHAR(512) NULL, [Spare5] VARCHAR(512) NULL )"; #endregion #region CompanyTable creator string // This is the query which will create a new table in our database file. An auto increment column called "ID", and many NVARCHAR type columns string createTableQueryCompany = @"CREATE TABLE IF NOT EXISTS [CompanyTable] ( [ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [Company] NVARCHAR(512) NULL, [Street1] VARCHAR(512) NULL, [Street2] VARCHAR(512) NULL, [City] VARCHAR(512) NULL, [State] VARCHAR(512) NULL, [Zip] VARCHAR(512) NULL, [Phone] VARCHAR(80) NULL, [Fax] VARCHAR(80) NULL )"; #endregion #region PartsTable creator string // This is the query which will create a new table in our database file. An auto increment column called "ID", and many NVARCHAR type columns string createTableQueryParts = @"CREATE TABLE IF NOT EXISTS [PartsTable] ( [ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [Category] NVARCHAR(512) NULL, [Description] VARCHAR(512) NULL, [Price] VARCHAR(512) NULL )"; #endregion #region ReportsTable creator string // This is the query which will create a new table in our database file. An auto increment column called "ID", and many NVARCHAR type columns string createTableQueryReports = @"CREATE TABLE IF NOT EXISTS [ReportsTable] ( [ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [TableName] NVARCHAR(512) NULL, [Name] NVARCHAR(512) NULL, [Columns] VARCHAR(512) NULL )"; #endregion Killconnection(); System.Data.SQLite.SQLiteConnection.CreateFile(dbName); // Create the file which will be hosting our database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=" + dbName)) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); // Open the connection to the database com.CommandText = createTableQuery1; // Set CommandText to our query that will create a table com.ExecuteNonQuery(); // Execute the query com.CommandText = createTableQuery2; // Set CommandText to our query that will create a table com.ExecuteNonQuery(); // Execute the query com.CommandText = createTableQueryCompany; // Set CommandText to our query that will create a table com.ExecuteNonQuery(); // Execute the query com.CommandText = createTableQueryParts; // Set CommandText to our query that will create a table com.ExecuteNonQuery(); // Execute the query com.CommandText = createTableQueryReports; // Set CommandText to our query that will create a table com.ExecuteNonQuery(); // Execute the query } } }
public void connectToDatabase() { m_dbConnection = new SQLiteConnection("Data Source=STOCKSSQLITE_DB.db;Version=3;"); m_dbConnection.Open(); }
private void button1_Click(object sender, EventArgs e) { //创建一个数据库文件 string datasource = "test.db"; System.Data.SQLite.SQLiteConnection.CreateFile(datasource); //连接数据库 System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(); System.Data.SQLite.SQLiteConnectionStringBuilder connstr = new System.Data.SQLite.SQLiteConnectionStringBuilder(); connstr.DataSource = datasource; connstr.Password = "******";//设置密码,SQLite ADO.NET实现了数据库密码保护 conn.ConnectionString = connstr.ToString(); conn.Open(); //创建表 System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); string sql = "CREATE TABLE test(username varchar(20),password varchar(20))"; cmd.CommandText = sql; cmd.Connection = conn; cmd.ExecuteNonQuery(); //插入数据 sql = "INSERT INTO test VALUES(’dotnetthink’,'mypassword’)"; cmd.CommandText = sql; cmd.ExecuteNonQuery(); //取出数据 sql = "SELECT * FROM test"; cmd.CommandText = sql; System.Data.SQLite.SQLiteDataReader reader = cmd.ExecuteReader(); StringBuilder sb = new StringBuilder(); while (reader.Read()) { sb.Append("username:"******"\n") .Append("password:").Append(reader.GetString(1)); } MessageBox.Show(sb.ToString()); }
private void button1_Click(object sender, EventArgs e) { string selected = listBox1.GetItemText(listBox1.SelectedItem); if (selected == "Mens 500m Speed Skating") { //global string used to place event name into it SelectedEvent = "\"ss500m Times Men\""; label1.Text = "Mens 500m Speed Skating"; label1.Refresh(); string ViewTable = "SELECT * FROM \"ss500m Times Men\" ORDER BY Times DESC;";//gets team table from database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB if (con.State == ConnectionState.Open) // if connection.Open was successful { //this sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(ViewTable, con); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0].DefaultView; } else { MessageBox.Show("Connection failed."); } } con.Close(); } //this gets the number of entries the event has int GridCount = dataGridView1.Rows.Count; //for loop used to populate the scores column with places for (int i = 0; i < GridCount; i++) { dataGridView1.Rows[i].Cells[0].Value = i + 1; } //make buttons visible that will work with this table to enter times in button2.Visible = true; label4.Visible = true; textBox1.Visible = true; } if (selected == "Womens 500m Speed Skating") { //global string used to place event name into it SelectedEvent = "\"ss500m Times Women\""; label1.Text = "Womens 500m Speed Skating"; label1.Refresh(); string ViewTable = "SELECT * FROM \"ss500m Times Women\" ORDER BY Times DESC";//gets team table from database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB if (con.State == ConnectionState.Open) // if connection.Open was successful { //this sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(ViewTable, con); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0].DefaultView; } else { MessageBox.Show("Connection failed."); } } con.Close(); //make buttons visible that will work with this table to enter times in button2.Visible = true; label4.Visible = true; textBox1.Visible = true; //makes sure unused components are disabled label6.Visible = false; label7.Visible = false; button3.Visible = false; textBox2.Visible = false; textBox3.Visible = false; textBox4.Visible = false; textBox5.Visible = false; textBox6.Visible = false; textBox7.Visible = false; textBox8.Visible = false; } //this gets the number of entries the event has int GridCount = dataGridView1.Rows.Count; //for loop used to populate the scores column with places for (int i = 0; i < GridCount; i++) { dataGridView1.Rows[i].Cells[0].Value = i + 1; } } if (selected == "Mens 1000m Speed Skating") { SelectedEvent = "\"ss1000m Times Men\""; label1.Text = "Mens 1000m Speed Skating"; label1.Refresh(); string ViewTable = "SELECT * FROM \"ss1000m Times Men\" ORDER BY Times DESC";//gets team table from database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB if (con.State == ConnectionState.Open) // if connection.Open was successful { //this sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(ViewTable, con); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0].DefaultView; } else { MessageBox.Show("Connection failed."); } } con.Close(); //make buttons visible that will work with this table to enter times in button2.Visible = true; label4.Visible = true; textBox1.Visible = true; //makes sure unused components are disabled label6.Visible = false; label7.Visible = false; button3.Visible = false; textBox2.Visible = false; textBox3.Visible = false; textBox4.Visible = false; textBox5.Visible = false; textBox6.Visible = false; textBox7.Visible = false; textBox8.Visible = false; } //this gets the number of entries the event has int GridCount = dataGridView1.Rows.Count; //for loop used to populate the scores column with places for (int i = 0; i < GridCount; i++) { dataGridView1.Rows[i].Cells[0].Value = i + 1; } } if (selected == "Womens 1000m Speed Skating") { SelectedEvent = "\"ss1000m Times Women\""; label1.Text = "Womens 1000m Speed Skating"; label1.Refresh(); string ViewTable = "SELECT * FROM \"ss1000m Times Women\" ORDER BY Times DESC";//gets team table from database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB if (con.State == ConnectionState.Open) // if connection.Open was successful { //this sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(ViewTable, con); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0].DefaultView; } else { MessageBox.Show("Connection failed."); } } con.Close(); //make buttons visible that will work with this table to enter times in button2.Visible = true; label4.Visible = true; textBox1.Visible = true; //makes sure unused components are disabled label6.Visible = false; label7.Visible = false; button3.Visible = false; textBox2.Visible = false; textBox3.Visible = false; textBox4.Visible = false; textBox5.Visible = false; textBox6.Visible = false; textBox7.Visible = false; textBox8.Visible = false; } //this gets the number of entries the event has int GridCount = dataGridView1.Rows.Count; //for loop used to populate the scores column with places for (int i = 0; i < GridCount; i++) { dataGridView1.Rows[i].Cells[0].Value = i + 1; } } if (selected == "Mens 1500m Speed Skating") { SelectedEvent = "\"ss1500m Times Men\""; label1.Text = "Mens 1500m Speed Skating"; label1.Refresh(); string ViewTable = "SELECT * FROM \"ss1500m Times Men\" ORDER BY Times DESC";//gets team table from database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB if (con.State == ConnectionState.Open) // if connection.Open was successful { //this sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(ViewTable, con); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0].DefaultView; } else { MessageBox.Show("Connection failed."); } } con.Close(); } //make buttons visible that will work with this table to enter times in button2.Visible = true; label4.Visible = true; textBox1.Visible = true; //makes sure unused components are disabled label6.Visible = false; label7.Visible = false; button3.Visible = false; textBox2.Visible = false; textBox3.Visible = false; textBox4.Visible = false; textBox5.Visible = false; textBox6.Visible = false; textBox7.Visible = false; textBox8.Visible = false; //this gets the number of entries the event has int GridCount = dataGridView1.Rows.Count; //for loop used to populate the scores column with places for (int i = 0; i < GridCount; i++) { dataGridView1.Rows[i].Cells[0].Value = i + 1; } } if (selected == "Womens 1500m Speed Skating") { SelectedEvent = "\"ss1500m Times Women\""; label1.Text = "Womens 1500m Speed Skating"; label1.Refresh(); string ViewTable = "SELECT * FROM \"ss1500m Times Women\" ORDER BY Times DESC";//gets team table from database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB if (con.State == ConnectionState.Open) // if connection.Open was successful { //this sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(ViewTable, con); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0].DefaultView; } else { MessageBox.Show("Connection failed."); } } con.Close(); //make buttons visible that will work with this table to enter times in button2.Visible = true; label4.Visible = true; textBox1.Visible = true; //makes sure unused components are disabled label6.Visible = false; label7.Visible = false; button3.Visible = false; textBox2.Visible = false; textBox3.Visible = false; textBox4.Visible = false; textBox5.Visible = false; textBox6.Visible = false; textBox7.Visible = false; textBox8.Visible = false; } //this gets the number of entries the event has int GridCount = dataGridView1.Rows.Count; //for loop used to populate the scores column with places for (int i = 0; i < GridCount; i++) { dataGridView1.Rows[i].Cells[0].Value = i + 1; } } if (selected == "Ice Dance Mens") { SelectedEvent = "\"Ice Dance Mens Scores\""; label1.Text = "Ice Dance Mens"; label1.Refresh(); string ViewTable = "SELECT * FROM \"Ice Dance Mens Scores\" ORDER BY Score DESC";//gets team table from database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB if (con.State == ConnectionState.Open) // if connection.Open was successful { //this sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(ViewTable, con); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0].DefaultView; } else { MessageBox.Show("Connection failed."); } } con.Close(); } //this gets the number of entries the event has int GridCount = dataGridView1.Rows.Count; //for loop used to populate the scores column with places for (int i = 0; i < GridCount; i++) { dataGridView1.Rows[i].Cells[0].Value = i + 1; } //makes the correct components are visible to enter scores label6.Visible = true; label7.Visible = true; button3.Visible = true; textBox2.Visible = true; textBox3.Visible = true; textBox4.Visible = true; textBox5.Visible = true; textBox6.Visible = true; textBox7.Visible = true; textBox8.Visible = true; //makes sure the other components are disabled button2.Visible = false; label4.Visible = false; textBox1.Visible = false; } if (selected == "Ice Dance Womens") { SelectedEvent = "\"Ice Dance Womens Scores\""; label1.Text = "Ice Dance Womens"; label1.Refresh(); string ViewTable = "SELECT * FROM \"Ice Dance Womens Scores\" ORDER BY Score DESC";//gets team table from database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB if (con.State == ConnectionState.Open) // if connection.Open was successful { //this sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(ViewTable, con); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0].DefaultView; } else { MessageBox.Show("Connection failed."); } } con.Close(); } //this gets the number of entries the event has int GridCount = dataGridView1.Rows.Count; //for loop used to populate the scores column with places for (int i = 0; i < GridCount; i++) { dataGridView1.Rows[i].Cells[0].Value = i + 1; } //makes the correct components are visible to enter scores label6.Visible = true; label7.Visible = true; button3.Visible = true; textBox2.Visible = true; textBox3.Visible = true; textBox4.Visible = true; textBox5.Visible = true; textBox6.Visible = true; textBox7.Visible = true; textBox8.Visible = true; //makes sure the other components are disabled button2.Visible = false; label4.Visible = false; textBox1.Visible = false; } if (selected == "Single Skating Mens") { SelectedEvent = "\"Single Skating Men Scores\""; label1.Text = "Single Skating Mens"; label1.Refresh(); string ViewTable = "SELECT * FROM \"Single Skating Men Scores\" ORDER BY Score DESC";//gets team table from database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB if (con.State == ConnectionState.Open) // if connection.Open was successful { //this sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(ViewTable, con); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0].DefaultView; } else { MessageBox.Show("Connection failed."); } } con.Close(); } //this gets the number of entries the event has int GridCount = dataGridView1.Rows.Count; //for loop used to populate the scores column with places for (int i = 0; i < GridCount; i++) { dataGridView1.Rows[i].Cells[0].Value = i + 1; } //makes the correct components are visible to enter scores label6.Visible = true; label7.Visible = true; button3.Visible = true; textBox2.Visible = true; textBox3.Visible = true; textBox4.Visible = true; textBox5.Visible = true; textBox6.Visible = true; textBox7.Visible = true; textBox8.Visible = true; //makes sure the other components are disabled button2.Visible = false; label4.Visible = false; textBox1.Visible = false; } if (selected == "Single Skating Womens") { SelectedEvent = "\"Single Skating Womens Scores\""; label1.Text = "Single Skating Womens"; label1.Refresh(); string ViewTable = "SELECT * FROM \"Single Skating Womens Scores\" ORDER BY Score DESC";//gets team table from database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB if (con.State == ConnectionState.Open) // if connection.Open was successful { //this sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(ViewTable, con); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0].DefaultView; } else { MessageBox.Show("Connection failed."); } } con.Close(); } //this gets the number of entries the event has int GridCount = dataGridView1.Rows.Count; //for loop used to populate the scores column with places for (int i = 0; i < GridCount; i++) { dataGridView1.Rows[i].Cells[0].Value = i + 1; } //makes the correct components are visible to enter scores label6.Visible = true; label7.Visible = true; button3.Visible = true; textBox2.Visible = true; textBox3.Visible = true; textBox4.Visible = true; textBox5.Visible = true; textBox6.Visible = true; textBox7.Visible = true; textBox8.Visible = true; //makes sure the other components are disabled button2.Visible = false; label4.Visible = false; textBox1.Visible = false; } if (selected == "Pair Skating Scores") { SelectedEvent = "\"Pair Skating Scores\""; label1.Text = "Pair Skating Scores"; label1.Refresh(); string ViewTable = "SELECT * FROM \"Pair Skating Scores\" ORDER BY Score DESC";//gets team table from database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB if (con.State == ConnectionState.Open) // if connection.Open was successful { //this sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(ViewTable, con); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0].DefaultView; } else { MessageBox.Show("Connection failed."); } } con.Close(); } //this gets the number of entries the event has int GridCount = dataGridView1.Rows.Count; //for loop used to populate the scores column with places for (int i = 0; i < GridCount; i++) { dataGridView1.Rows[i].Cells[0].Value = i + 1; } //makes the correct components are visible to enter scores label6.Visible = true; label7.Visible = true; button3.Visible = true; textBox2.Visible = true; textBox3.Visible = true; textBox4.Visible = true; textBox5.Visible = true; textBox6.Visible = true; textBox7.Visible = true; textBox8.Visible = true; //makes sure the other components are disabled button2.Visible = false; label4.Visible = false; textBox1.Visible = false; } }
private void button1_Click(object sender, EventArgs e) { if (comboBox2.SelectedIndex == 0) { int cantidad; if (int.TryParse(textBox3.Text, out cantidad)) { float peso; if (float.TryParse(textBox4.Text, out peso)) { string appPath = Path.GetDirectoryName(Application.ExecutablePath); System.Data.SQLite.SQLiteConnection sqlConnection1 = new System.Data.SQLite.SQLiteConnection(@"Data Source=" + appPath + @"\DBBIT.s3db ;Version=3;"); System.Data.SQLite.SQLiteCommand cmd = new System.Data.SQLite.SQLiteCommand(); cmd.CommandType = System.Data.CommandType.Text; //comando sql para insercion cmd.CommandText = "INSERT INTO Entradas values ('" + label1.Text + "', '" + textBox1.Text + "', '" + textBox2.Text + "', '" + textBox3.Text + "', '" + textBox4.Text + "', '" + textBox5.Text + "', '" + textBox6.Text + "', '" + textBox7.Text + "', '" + comboBox1.Text + "', '" + textBox8.Text + "')"; cmd.Connection = sqlConnection1; sqlConnection1.Open(); cmd.ExecuteNonQuery(); sqlConnection1.Close(); MessageBox.Show("Entrada guardada con exito."); textBox1.Text = ""; textBox2.Text = ""; textBox3.Text = ""; textBox4.Text = ""; textBox5.Text = ""; textBox6.Text = ""; textBox7.Text = ""; comboBox1.SelectedIndex = 0; textBox8.Text = ""; ///create the connection string string connString = @"Data Source= " + appPath + @"\DBBIT.s3db ;Version=3;"; //create the database query string query = "SELECT * FROM Entradas"; //create an OleDbDataAdapter to execute the query System.Data.SQLite.SQLiteDataAdapter dAdapter = new System.Data.SQLite.SQLiteDataAdapter(query, connString); //create a command builder System.Data.SQLite.SQLiteCommandBuilder cBuilder = new System.Data.SQLite.SQLiteCommandBuilder(dAdapter); //create a DataTable to hold the query results DataTable dTable = new DataTable(); //fill the DataTable dAdapter.Fill(dTable); dAdapter.Update(dTable); if (dTable.Rows.Count != 0) { DataRow Row = dTable.Rows[dTable.Rows.Count - 1]; string num = Row["Numero"].ToString(); int autonum = Int32.Parse(num); label1.Text = (autonum + 1).ToString(); } else { label1.Text = "1"; } } else { MessageBox.Show("Revise de nuevo su campo de peso, solo se permiten numeros flotantes."); } } else { MessageBox.Show("Revise de nuevo su campo de cantidad, solo se permiten numeros."); } } }
private void InsertDBTransaction() { /// ///INSERT TRANSACTION INTO LOCAL SQLite DATABASE /// string dbFile = @Program.SQLITE_DATABASE_NAME; string connString = string.Format(@"Data Source={0}; Pooling=false; FailIfMissing=false;", dbFile); using (SQLiteConnection dbConn = new System.Data.SQLite.SQLiteConnection(connString)) { dbConn.Open(); using (System.Data.SQLite.SQLiteCommand cmd = dbConn.CreateCommand()) { // ------------------------------- // Current KioskData.db data // ------------------------------- cmd.CommandText = @"INSERT INTO RES_KEY ( OUT_DATETIME, KEY_BOX_OUT_NO, RESV_RESERV_NO, TRANSACTION_OUT_NO, KIOSK_ID ) VALUES ( @KioskDateOut, @BoxNum, @ReservNum, @IdentificationType, @KioskId )"; //parameterized insert - more flexibility on parameter creation cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter {// SQLite date format is: yyyy-MM-dd HH:mm:ss ParameterName = "@KioskDateOut", Value = this.SQLiteDateAndTime, }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@BoxNum", Value = this.BoxNumber, }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@ReservNum", Value = this.AccessCode, }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@IdentificationType", Value = this.IdentificationType, }); cmd.Parameters.Add(new System.Data.SQLite.SQLiteParameter { ParameterName = "@KioskId", Value = Program.KIOSK_ID, }); cmd.ExecuteNonQuery(); } Program.logEvent("Local SQLite Database Transaction Inserted Successfully"); if (dbConn.State != System.Data.ConnectionState.Closed) { dbConn.Close(); } } }
private void button1_Click(object sender, EventArgs e) { //string used to place cell contents into string country; //gets selected row int rowindex = dataGridView2.CurrentCell.RowIndex; //enters selected row into a string variable country = dataGridView2.CurrentCell.Value.ToString(); //string to store selected gender string selected = listBox1.GetItemText(listBox1.SelectedItem); //object used to store athletes info Athlete AddAthlete = new Athlete(); //add athletes info to object AddAthlete.firstName = textBox1.Text; AddAthlete.lastName = textBox2.Text; AddAthlete.gender = selected; //creates variable for connection string using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open();//opens DB //checks connection and fills grid view with Race table if (con.State == ConnectionState.Open) { //adds entry to athlete information string sql = String.Format("INSERT INTO 'Athlete Information' ('TeamName' , 'AthleteFirstName', 'AthleteLastName', 'Gender') " + "VALUES ('" + country + "', '" + AddAthlete.firstName + "', '" + AddAthlete.lastName + "', '" + selected + "');"); DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(sql, con); da.Fill(ds); } else { MessageBox.Show("Connection failed."); } } MessageBox.Show("Entry has been entered into Database!"); con.Close(); } string ViewTable = "SELECT * FROM 'Athlete Information'";//gets team table from database using (System.Data.SQLite.SQLiteConnection con = new System.Data.SQLite.SQLiteConnection("data source=OlympiaDB.sqlite")) { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand(con)) { con.Open(); //opens DB if (con.State == ConnectionState.Open) // if connection.Open was successful { //this sends sql commands to database DataSet ds = new DataSet(); var da = new SQLiteDataAdapter(ViewTable, con); da.Fill(ds); dataGridView1.DataSource = ds.Tables[0].DefaultView; } else { MessageBox.Show("Connection failed."); } } con.Close(); } }