/// <summary> /// Execute a scalar update with the open database /// connection. /// </summary> /// <param name="sql">sql string.</param> public static void ScalarUpdate(string sql) { try { SQLiteCommand mycommmand = new SQLiteCommand(sql, manual_cnn); mycommmand.ExecuteScalar(); mycommmand.CommandText = "SELECT last_insert_rowid()"; Console.WriteLine(mycommmand.ExecuteScalar()); } catch (Exception ex) { throw ex; } }
public static void AddBook(string Title, string authorName, DateTime publish, string ISBN) { string connectionStr = "Data Source=..\\..\\book.db;Version=3;"; var connection = new SQLiteConnection(connectionStr); connection.Open(); using (connection) { var addAuthorCommand = new SQLiteCommand("INSERT INTO author(Name) VALUES(@name)", connection); addAuthorCommand.Parameters.AddWithValue("@name", authorName); addAuthorCommand.ExecuteNonQuery(); var getAuthIDCommand = new SQLiteCommand("SELECT idAuthor FROM author WHERE Name=" + "@name", connection); getAuthIDCommand.Parameters.AddWithValue("@name", authorName); var authID = getAuthIDCommand.ExecuteScalar(); var addBookCommand = new SQLiteCommand( "INSERT INTO book(Name,date,ISBN,AuthorID) VALUES(@title,@publish,@ISBN,@authID)", connection); addBookCommand.Parameters.AddWithValue("@title", Title); addBookCommand.Parameters.AddWithValue("@publish", publish); addBookCommand.Parameters.AddWithValue("@ISBN", ISBN); addBookCommand.Parameters.AddWithValue("@authID", authID); addBookCommand.ExecuteNonQuery(); } }
public List<Stroke> MatchStroke(string stroke, AI.Quadrant q, int language) { bool b = true; REDO: conn = new System.Data.SQLite.SQLiteConnection(@"Data Source=CoreInkLib.dll"); conn.Open(); SQLiteCommand sqc = new SQLiteCommand("SELECT * FROM strokes WHERE directions = '" + stroke + "' AND quadrant='" + Quadrant2Str(q) + "' AND lang = " + language.ToString() + ";", conn); SQLiteDataReader sqr = sqc.ExecuteReader(); List<Stroke> l = new List<Stroke>(); while (sqr.Read()) { Stroke st = new Stroke(); st.sid = Convert.ToInt32(sqr[0]); st.vid = Convert.ToInt32(sqr[1]); st.cid = Convert.ToInt32(sqr[2]); st.thisstroke = Convert.ToInt32(sqr[5]); st.quadrant = sqr[4].ToString(); sqc = new SQLiteCommand("SELECT ifnull(phase,0) FROM strokes WHERE vid = " + st.vid.ToString() + " AND phase >" + st.thisstroke.ToString() + " AND lang=" + language.ToString() + ";", conn); st.nextstroke = Convert.ToInt32(sqc.ExecuteScalar()); foreach (char c in sqr[3].ToString()) { st.directions.Add(Convert.ToInt32(c.ToString())); } if (b == false) st.quadrant = "C"; l.Add(st); } conn.Close(); if (q.third && l.Count == 0 && b) { q.third = false; q.second = true; b = false; goto REDO; } return l; }
/// <summary> /// Given a GUID, returns the current status for that node. /// </summary> /// <param name="guid">A unique string that identifies a specific node.</param> /// <returns>A string with the node status for the given guid</returns> public string GetStatus(string guid) { string status = ""; string statusQuery = "SELECT status FROM Node_Status WHERE guid = @pGUID"; SQLiteCommand statusCmd = new SQLiteCommand(statusQuery, conn); SQLiteParameter pGUID = new SQLiteParameter("@pGUID", guid); statusCmd.Parameters.Add(pGUID); try { conn.Open(); status = statusCmd.ExecuteScalar().ToString(); conn.Close(); } catch (SQLiteException ex) { //if anything is wrong with the sql statement or the database, //a SQLiteException will show information about it. Debug.Print(ex.Message); //always make sure the database connection is closed. if (conn.State == ConnectionState.Open) { conn.Close(); } } return status; }
// Добавляем новый счет private void button1_Click(object sender, EventArgs e) { String NewSchet = textBox1.Text; if (String.IsNullOrWhiteSpace(NewSchet)) // Проверяем название счета { DialogResult Result = MessageBox.Show("Недопустимое название", "", MessageBoxButtons.OK); } else { string q = "SELECT COUNT (*) FROM scheta WHERE schet = ('" + NewSchet + "')"; // Проверяем сколько таких же SQLiteConnection Connection = new SQLiteConnection(string.Format("Data Source={0};", "budget.db")); SQLiteCommand sqlitecommand = new SQLiteCommand(q, Connection); Connection.Open(); sqlitecommand.ExecuteNonQuery(); int ans = 0; ans = Convert.ToInt32(sqlitecommand.ExecuteScalar()); if (ans == 0) // Если таких 0 { q = "INSERT INTO scheta (schet) VALUES ('" + NewSchet + "')"; // Добавляем Connection = new SQLiteConnection(string.Format("Data Source={0};", "budget.db")); sqlitecommand = new SQLiteCommand(q, Connection); Connection.Open(); sqlitecommand.ExecuteNonQuery(); Connection.Close(); UpdateTable(); } else // Если есть - сообщаем { DialogResult Result = MessageBox.Show("Счет не уникален", "", MessageBoxButtons.OK); } } }
public static Project LoadProject(string existingProject) { try { string projectsPath = System.IO.Path.GetDirectoryName( System.Reflection.Assembly.GetExecutingAssembly().Location); Project loadedProj=new Project(); string projectSQL = "SELECT project_base64 FROM project";// WHERE id_project=0"+id_samples.ToString(); SQLiteConnection projectConn = new SQLiteConnection("Data Source="+ projectsPath + @"\\Projects\\" + existingProject + ".db;Version=3;New=False;Compress=True;Synchronous=Off"); SQLiteCommand cmd = new SQLiteCommand(projectSQL,projectConn); projectConn.Open(); loadedProj = (Project)ConversionUtilities.DeserializeBase64((string)cmd.ExecuteScalar()); // using(SQLiteDataReader sqReader = cmd.ExecuteReader()) // { // if (sqReader.Read()) // { // loadedProj = (Project)ConversionUtilities.DeserializeBase64(sqReader.GetString(0)); // } // } projectConn.Close(); return loadedProj; } catch (Exception ex) { throw ex; } }
//static private int _OrderTotal = 0; static public int GetRowCounts(FormMain.dbType type) { int?count = 0; SQLiteConnection con = GetConnection(); { using (System.Data.SQLite.SQLiteCommand com = new System.Data.SQLite.SQLiteCommand("SET ARITHABORT ON", con)) { try { if (type == FormMain.dbType.Order) { com.CommandText = Order.GetRowCountCommandText; } else { com.CommandText = Quote.GetRowCountCommandText; } object o = com.ExecuteScalar(); count = Convert.ToInt32(o); } catch// (Exception ex) { } } } //if (type == FormMain.dbType.Order && count.HasValue) //{ // _OrderTotal = count.Value; //} return((count == null) ? 0 : count.Value); }
public static bool Check(string username) { objConnection.Open(); string cmdstring = "select Username from userdata where Username = '******'"; SQLiteCommand sqlcmd = new SQLiteCommand(cmdstring,objConnection); try { string result = (string)sqlcmd.ExecuteScalar(); if (result == "" || result == null) { objConnection.Close(); return false; } else { objConnection.Close(); return true; } } catch (System.Data.SQLite.SQLiteException) { objConnection.Close(); return false; } }
/// <summary> /// Authenticate User with giver login data. Returns null if user does not exist /// </summary> /// <param name="username"></param> /// <param name="password"></param> /// <returns></returns> public static UserDatabaseError Authenticate(string username, string password, out User user) { user = null; SQLiteConnection connection = new SQLiteConnection(DatabaseManager.CONNECTION_STRING); SQLiteCommand command = new SQLiteCommand("SELECT salt FROM users WHERE username=@username and active = 1", connection); //SQLiteCommand command = new SQLiteCommand("SELECT count(*) FROM users", connection); command.Parameters.AddWithValue("@username", username); try { connection.Open(); } catch { return UserDatabaseError.ERR_DATABASE_CONNECTION; } String salt = (string)command.ExecuteScalar(); if (salt == null || salt == string.Empty) { connection.Close(); return UserDatabaseError.ERR_USER_DOES_NOT_EXIST; } string hash = DatabaseManager.GetSha256(password + salt); command.CommandText = "SELECT name, privilege FROM users WHERE username=@username AND sha256p =@password"; command.Parameters.AddWithValue("@password", hash); SQLiteDataReader reader = command.ExecuteReader(); while (reader.Read()) user = new User(username, (string)reader["name"], (User.UserPrivilege)(Int64)reader["privilege"]); if (user == null) return UserDatabaseError.ERR_AUTH; connection.Close(); return UserDatabaseError.ERR_SUCCESS; }
public int Insert() { try { this.Connection.Open(); // // TODO: Check for existing ListID // SQLiteCommand command = new SQLiteCommand("INSERT INTO List (ListID, ListName) VALUES (@ListID, @ListName); SELECT last_insert_rowid();", this.Connection); command.Parameters.AddWithValue("@ListID", this.ListID); command.Parameters.AddWithValue("@ListName", this.ListName); Object result = command.ExecuteScalar(); this.ID = int.Parse(result.ToString()); } catch (Exception exception) { } finally { this.Connection.Close(); } return this.ID; }
public string Get(string id, string key) { try { using (var conn = new SQLiteConnection(this._connectionString)) { const string query = "SELECT value FROM cupcake WHERE id = @id AND key = @key"; using (var cmd = new SQLiteCommand(query, conn)) { cmd.Parameters.AddWithValue("@id", id); cmd.Parameters.AddWithValue("@key", key); conn.Open(); object result = cmd.ExecuteScalar(); if (result is DBNull) return null; return (string)result; } } } catch (SQLiteException ex) { throw new StorageException(ex.Message, ex); } }
public object Count(String comando) { AbreConexao(); SQLiteCommand cmd = new SQLiteCommand(comando, _cnx); return cmd.ExecuteScalar(); }
public ViewResult Index(PriceLookup price) { var connectionString = @"Data Source=" + Server.MapPath("App_Data/prices.s3db"); var priceDb = new PriceDb(connectionString); if (ModelState.IsValid) { if (!string.IsNullOrEmpty(price.Upc)) { priceDb.GetPriceByUpc(price); if(price.Price == null) ModelState.AddModelError("NotFound", "UPC not found"); } else if (!string.IsNullOrEmpty(price.Isbn)) { var sql = "SELECT Price FROM Prices WHERE Isbn = @isbn"; using (var conn = new SQLiteConnection(connectionString)) { conn.Open(); var sqlCommand = new SQLiteCommand(sql, conn); sqlCommand.Parameters.AddWithValue("isbn", price.Isbn); var result = sqlCommand.ExecuteScalar(); if (result == null) ModelState.AddModelError("NotFound", "ISBN not found"); else price.Price = (decimal)result; conn.Close(); } } else ModelState.AddModelError("Required", "You must enter a UPC or an ISBN"); } return View(price); }
public long AddUser(string name, string fullName) { string query = QueryBuilder.Insert("persons", GetNameColumnPair(name), GetFullNameColumnPair(fullName)); using (SQLiteCommand insert = new SQLiteCommand(query, m_conn)) return (long)insert.ExecuteScalar(); }
void addRowSQL(string sName, string sNote) { //change sql data using (SQLiteConnection connection = new SQLiteConnection(cs)){ connection.Open(); SQLiteCommand cmd=new SQLiteCommand( string.Format("INSERT INTO Cars (id,name,note) VALUES(NULL,'{0}sql','{1}');", sName, sNote),connection); cmd.ExecuteNonQuery(); //get autoincrement value cmd=new SQLiteCommand("SELECT last_insert_rowid()",connection); long lastID = (long) cmd.ExecuteScalar(); System.Console.WriteLine("added new row with "+lastID); } //change datagrid // DataTable dt = ds.Tables[0]; // DataRow dr = dt.NewRow(); // dr["name"]=sName; // dr["note"]=sNote; // dr["id"]=lastID; // dt.Rows.Add(dr); // dt.AcceptChanges(); // ds.AcceptChanges(); // da.Update(ds.Tables[0]); doRefresh(); }
public static bool Login(int accountId, string password) { using (SQLiteConnection con = new SQLiteConnection(Settings.Default.AccountingConnectionString)) { using (SQLiteCommand cmd = new SQLiteCommand()) { cmd.Connection = con; cmd.CommandText = "SELECT COUNT(accountId) FROM vAccountPassword WHERE accountId = @account AND password = @password"; SQLiteParameter pAccount = new SQLiteParameter("account",accountId); pAccount.Direction = ParameterDirection.Input; SQLiteParameter pPassword = new SQLiteParameter("password", password); pPassword.Direction = ParameterDirection.Input; cmd.Parameters.AddRange(new SQLiteParameter[] { pAccount, pPassword }); con.Open(); if (Convert.ToInt32(cmd.ExecuteScalar()) == 1) { con.Close(); return true; } con.Close(); return false; } } }
public bool Exists(string SQLString) { using (SQLiteConnection connection = new SQLiteConnection(connectionString)) { using (SQLiteCommand cmd = new SQLiteCommand(SQLString, connection)) { try { connection.Open(); object obj = cmd.ExecuteScalar(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return false; } else { return true; } } catch (MySql.Data.MySqlClient.MySqlException e) { connection.Close(); throw new Exception(e.Message); } finally { cmd.Dispose(); connection.Close(); } } } }
public int numberOfUsernameMatches(string username) { string sql = "SELECT COUNT(*) FROM playerData WHERE username=@username"; SQLiteCommand command = new SQLiteCommand(sql, this.p_dbConnection); command.Parameters.AddWithValue("@username", username); return Convert.ToInt32(command.ExecuteScalar()); }
public void AddDirectoriesRecursively(string folder, int? p_id) { DirectoryInfo di = new DirectoryInfo(folder); using (SQLiteCommand command = new SQLiteCommand(ConnectionManager.connection)) { command.CommandText = @"Insert into [folders] (p_id,name) values (@p_id,@name); SELECT last_insert_rowid() AS [ID]"; command.Parameters.Add(new SQLiteParameter("@p_id", p_id == null ? "null" : p_id.ToString())); command.Parameters.Add(new SQLiteParameter("@name", di.Name)); p_id = int.Parse(command.ExecuteScalar().ToString()); } using (SQLiteCommand command = new SQLiteCommand(ConnectionManager.connection)) { command.CommandText = @"Insert into [files] (p_id,name) values (@p_id,@name)"; SQLiteParameter _name = new SQLiteParameter("@name"); command.Parameters.Add(_name); command.Parameters.Add(new SQLiteParameter("@p_id", p_id)); foreach (FileInfo cfi in di.GetFiles()) { _name.Value = cfi.Name; command.ExecuteNonQuery(); } } foreach (DirectoryInfo cdi in di.GetDirectories()) AddDirectoriesRecursively(cdi.FullName, p_id); }
void addButton_Click(object sender, RoutedEventArgs e) { Button okButton = sender as Button; WrapPanel wp = okButton.Parent as WrapPanel; using (SQLiteCommand command = new SQLiteCommand(ConnectionManager.Connection)) { command.CommandText = "insert into param (param_name,param_type,question) values (@param_name,@param_type,@question); SELECT last_insert_rowid();"; command.Parameters.Add(new SQLiteParameter("@param_name",(wp.Children.FindByName(paramname) as TextBox).Text)); command.Parameters.Add(new SQLiteParameter("@param_type",((wp.Children.FindByName(paramtype) as ComboBox).SelectedItem as ComboBoxItem).Content.ToString())); command.Parameters.Add(new SQLiteParameter("@question", (wp.Children.FindByName(paramquestion) as TextBox).Text)); //SQLiteParameter p = new SQLiteParameter("@term_group_id"); //p.Value = DBNull.Value; //command.Parameters.Add(p); int paramId = int.Parse(command.ExecuteScalar().ToString()); if ((wp.Children.FindByName(termgroup) as ComboBox).IsEnabled) { command.CommandText = "insert into term_param (term_group_id,param_id) values (@term_group_id,@param_id)"; command.Parameters.Add(new SQLiteParameter("@term_group_id", ((wp.Children.FindByName(termgroup) as ComboBox).SelectedItem as ComboBoxItem).Tag.ToString())); command.Parameters.Add(new SQLiteParameter("@param_id", paramId)); command.ExecuteNonQuery(); } } UpdateTable(); }
static void Main(string[] args) { // On crée nos variables: SQLiteConnection connection = new SQLiteConnection(); // Disponible sur le site http://www.connectionstrings.com // "Data Source=filename;Version=3;" connection.ConnectionString = "Data Source=test.db;Version=3"; // Il serait bien sûr possible d'en faire plus, par exemple, mot de passe, mais on va s'en tenir à ça. // Ouvre la connexion à la base de donnée connection.Open(); // À présent, il faut se rappeler le SQL que nous avons appris pendant les cours de base de donneés: // Créons donc nos tables: SQLiteCommand command = new SQLiteCommand("DROP TABLE IF EXISTS Étudiants; CREATE TABLE Étudiants(" + " ID integer Primary Key, " + " Nom string, " + " Prenom string, " + " DateDeNaissance date, " + " Ville string, " + " Technique string, " + " NuméroTéléphone string); "); command.Connection = connection; command.ExecuteNonQuery(); // Il est possible de faire nos opérations en plus qu'une étape: SQLiteCommand command2 = new SQLiteCommand("INSERT INTO Étudiants VALUES ( @ID, @Nom, @Prenom, @DateNaissance, @Ville, @Technique,@No);", connection); command2.Parameters.AddWithValue("@ID", null); command2.Parameters.AddWithValue("@Nom", "Di Croci"); SQLiteParameter param3 = new SQLiteParameter("@Prenom"); param3.Value = "Michel"; command2.Parameters.Add(param3); command2.Parameters.AddWithValue("@DateNaissance", "13/10/1979"); command2.Parameters.AddWithValue("@Ville", "L'Assomption"); command2.Parameters.AddWithValue("@Technique", "Informatique"); command2.Parameters.AddWithValue("@No", "haha!"); command2.ExecuteNonQuery(); // Comme vous le constatez, on ne sait pas quel numéro d'enregistrement vient d'être entré... // Dans le cas de l'utilisation de clé étrangère (comme dnas notre TP), il peut devenir pratique d'avoir ce numéro // dans ce cas, nous devons utiliser select last_insert_rowid command.CommandText = "SELECT last_insert_rowid() FROM Étudiants"; // Comme vous le savez, SELECT nous retourne un élément contrairement à un INSERT ou un UPDATE OU UN DELETE // Cela siginifie entre autre que la requête précédente va nous retourner qu'un seul scalaire, sinon nous aurions // utilisé le SQLiteDataReader que nous verrons ultérieurement object id = command.ExecuteScalar(); Console.WriteLine(id); connection.Close(); }
private static void AddBook(string bookName, DateTime datePublish, long isbn, string author) { db.Open(); using (db) { string bookStr = "INSERT INTO books " + "(titleBook, publishDate, ISBN) VALUES " + "(@title, @date, @isbn)"; SQLiteCommand addBook = new SQLiteCommand(bookStr, db.ConnectionHandle); addBook.Parameters.AddWithValue("@title", bookName); addBook.Parameters.AddWithValue("@date", datePublish); addBook.Parameters.AddWithValue("@isbn", isbn); addBook.ExecuteNonQuery(); SQLiteCommand cmdSelectIdentity = new SQLiteCommand("SELECT last_insert_rowid()", db.ConnectionHandle); long insertedRecordId = (long)cmdSelectIdentity.ExecuteScalar(); string authorStr = "INSERT INTO authors " + "(Books_idBooks, AuthorName) VALUES " + "(@bookId, @name)"; SQLiteCommand addAuthor = new SQLiteCommand(authorStr, db.ConnectionHandle); addAuthor.Parameters.AddWithValue("@bookId", (int)insertedRecordId); addAuthor.Parameters.AddWithValue("@name", author); addAuthor.ExecuteNonQuery(); } }
public object ExecuteScalar(string sql) { object value = null; try { var con = CONNECTION.OpenCon(); var cmd = new SQLiteCommand(sql, con); value = cmd.ExecuteScalar(); cmd.Dispose(); CONNECTION.CloseCon(con); } catch(Exception ex) { SLLog.WriteError(new LogData { Source = ToString(), FunctionName = "ExecuteScalar Error!", Ex = ex, }); return null; } return value; }
public int AddPerson(Person newPerson) { CheckAndCreatePersonTable(); long newId; using (SQLiteConnection connection = new SQLiteConnection(GetConnectionString())) { connection.Open(); using (SQLiteCommand command = new SQLiteCommand(connection)) { command.CommandText = "INSERT INTO Persons (FirstName, Surname, EMail, Age, Salary) VALUES($FirstName,$Surname,$EMail,$Age,$Salary)"; command.Parameters.AddWithValue("$FirstName", newPerson.FirstName); command.Parameters.AddWithValue("$Surname", newPerson.Surname); command.Parameters.AddWithValue("$EMail", newPerson.EMail); command.Parameters.AddWithValue("$Age", newPerson.Age); command.Parameters.AddWithValue("$Salary", newPerson.Salary); command.CommandType = CommandType.Text; command.ExecuteNonQuery(); command.CommandText = "SELECT last_insert_rowid()"; newId = (long)command.ExecuteScalar(); } connection.Close(); } return (int)newId; }
public static void CheckDBSchema() { try { using (SQLiteConnection conn = GetConnection()) { conn.Open(); using (var comm = new SQLiteCommand(conn)) { comm.CommandText = "SELECT value from DBProperties WHERE name='dbversion'"; var versionNum = (string) comm.ExecuteScalar(); if (versionNum == DBVersion) { return; } } conn.Close(); } } catch (SQLiteException) { } SpecialTimeLinesRepository.Load(); SpecialTimeLinesRepository.Export(); DeleteDB(); CreateDB(); SpecialTimeLinesRepository.Import(); SpecialTimeLinesRepository.Save(); }
public long Count() { var sql = "SELECT COUNT(*) FROM Patient"; using (var command = new SQLiteCommand(sql, this.Connection)) { return (long)command.ExecuteScalar(); } }
public static object ExecuteScalar(string query) { lock(lootLock) { if (lootConn == null) return null; SQLiteCommand command = new SQLiteCommand(query, lootConn); return command.ExecuteScalar(); } }
public long Add(string name, long parentId, CategoryType type) { string query = QueryBuilder.Insert("categories", GetNameColumnPair(name), GetParentIdColumnPair(parentId), GetTypeColumnPair(type)); using (SQLiteCommand insert = new SQLiteCommand(query, m_conn)) return (long) insert.ExecuteScalar(); }
private void button1_Click(object sender, EventArgs e) { com.Open(); SQLiteCommand cmd_r = new SQLiteCommand("SELECT COUNT(*) FROM MEMBER", com); int Tabrows = Convert.ToInt32(cmd_r.ExecuteScalar()); com.Close(); com.Open(); SQLiteCommand cmdsearch = new SQLiteCommand("SELECT * FROM MEMBER", com); SQLiteDataReader readsearch = cmdsearch.ExecuteReader(); Form1 f = new Form1(); string idkmitl = id_d; //MessageBox.Show(idkmitl+"naaaa"); while (readsearch.Read()) { if (readsearch["StanaM"].ToString().Equals("God") || readsearch["StanaM"].ToString().Equals("Admin")) { //MessageBox.Show(readsearch["User"].ToString()+"//"+ readsearch["Pass"].ToString()); if (readsearch["User"].ToString().Equals(textBox1.Text) && readsearch["Pass"].ToString().Equals(textBox2.Text)) { if (readsearch["IDKMITL"].ToString().Equals(idkmitl)) { MessageBox.Show("ขออภัยคุณไม่สามารถอนุญาติตัวเองได้", "unsuccessful", MessageBoxButtons.OK, MessageBoxIcon.Error); com.Close(); return; } //com.Close(); _ctaa = true; ch = true; MessageBox.Show("login sucessful"); //return; } else if (Convert.ToInt32(readsearch["ID"].ToString()) == Tabrows && ctaa == false) { MessageBox.Show("ขออภัยคุณกรอก Username หรือ Password(God, Admin) ไม่ถูกต้อง1", "unsuccessful", MessageBoxButtons.OK, MessageBoxIcon.Error); com.Close(); ch = true; return; } } else if (Convert.ToInt32(readsearch["ID"].ToString()) == Tabrows && ctaa == false) { MessageBox.Show("ขออภัยคุณกรอก Username หรือ Password(God, Admin) ไม่ถูกต้อง2", "unsuccessful", MessageBoxButtons.OK, MessageBoxIcon.Error); com.Close(); ch = true; return; } } ch = true; com.Close(); this.Close(); }
public static long LastID() { using (SQLiteCommand command = new SQLiteCommand(Controller.Connections["Main"])) { command.CommandText = "SELECT last_insert_rowid();"; return (long)command.ExecuteScalar(); } }
// check to see if a value exist in the table public bool existsInTable(String name) { string sql = "SELECT count(*) FROM playerInfo WHERE name=:Name"; SQLiteCommand command = new SQLiteCommand(sql, swarchDatabase); command.Parameters.AddWithValue(":Name", name); int count = Convert.ToInt32(command.ExecuteScalar()); return (count != 0); }
private void Open(string cinString) { this.con = new sl.SQLiteConnection("Data Source=" + cinString + "; FailIfMissing=True"); this.con.Open(); var scheme = this.con.GetSchema(); { sl.SQLiteCommand cmd = new sl.SQLiteCommand("select sqlite_version();", con); var res = cmd.ExecuteScalar(); Debug.Assert(this.con.ServerVersion == res.ToString(), "Версия базы не совпадает"); } }
// 创建表 public void CreateTableToSqlite(string tableName, string createTableSql) { string sql_query_data = string.Format("SELECT count(*) FROM sqlite_master WHERE type='table' AND name='{0}'", tableName); cmd.CommandText = sql_query_data; cmd.Connection = conn; try { if (0 == Convert.ToInt32(cmd.ExecuteScalar())) // 如果表不存在则创建表 { cmd.CommandText = createTableSql; // 创建注册用户表 cmd.Connection = conn; cmd.ExecuteNonQuery(); } } catch (Exception e) { MessageBox.Show(e.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Warning); return; } }
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 object ExecuteScalar(string sql) { cmd.CommandText = sql; return(cmd.ExecuteScalar()); }
/// <summary> /// This method creates a new connection, executes the query using the given /// execution type and command behavior, closes the connection, and returns /// the results. If the connection string is null, a temporary in-memory /// database connection will be used. /// </summary> /// <param name="commandText"> /// The text of the command to be executed. /// </param> /// <param name="executeType"> /// The execution type for the command. This is used to determine which method /// of the command object to call, which then determines the type of results /// returned, if any. /// </param> /// <param name="commandBehavior"> /// The command behavior flags for the command. /// </param> /// <param name="connectionString"> /// The connection string to the database to be opened, used, and closed. If /// this parameter is null, a temporary in-memory databse will be used. /// </param> /// <param name="args"> /// The SQL parameter values to be used when building the command object to be /// executed, if any. /// </param> /// <returns> /// The results of the query -OR- null if no results were produced from the /// given execution type. /// </returns> public static object Execute( string commandText, SQLiteExecuteType executeType, CommandBehavior commandBehavior, string connectionString, params object[] args ) { if (connectionString == null) { connectionString = DefaultConnectionString; } using (SQLiteConnection connection = new SQLiteConnection(connectionString)) { connection.Open(); using (SQLiteCommand command = connection.CreateCommand()) { command.CommandText = commandText; if (args != null) { foreach (object arg in args) { if (arg is SQLiteParameter) { command.Parameters.Add((SQLiteParameter)arg); } else { command.Parameters.Add(new SQLiteParameter(DbType.Object, arg)); } } } switch (executeType) { case SQLiteExecuteType.None: { // // NOTE: Do nothing. // break; } case SQLiteExecuteType.NonQuery: { return(command.ExecuteNonQuery(commandBehavior)); } case SQLiteExecuteType.Scalar: { return(command.ExecuteScalar(commandBehavior)); } case SQLiteExecuteType.Reader: { return(command.ExecuteReader(commandBehavior)); } } } } return(null); }
/// <summary> /// This method creates a new connection, executes the query using the given /// execution type and command behavior, closes the connection unless a data /// reader is created, and returns the results. If the connection string is /// null, a temporary in-memory database connection will be used. /// </summary> /// <param name="commandText"> /// The text of the command to be executed. /// </param> /// <param name="executeType"> /// The execution type for the command. This is used to determine which method /// of the command object to call, which then determines the type of results /// returned, if any. /// </param> /// <param name="commandBehavior"> /// The command behavior flags for the command. /// </param> /// <param name="connectionString"> /// The connection string to the database to be opened, used, and closed. If /// this parameter is null, a temporary in-memory databse will be used. /// </param> /// <param name="args"> /// The SQL parameter values to be used when building the command object to be /// executed, if any. /// </param> /// <returns> /// The results of the query -OR- null if no results were produced from the /// given execution type. /// </returns> public static object Execute( string commandText, SQLiteExecuteType executeType, CommandBehavior commandBehavior, string connectionString, params object[] args ) { SQLiteConnection connection = null; try { if (connectionString == null) { connectionString = DefaultConnectionString; } using (connection = new SQLiteConnection(connectionString)) { connection.Open(); using (SQLiteCommand command = connection.CreateCommand()) { command.CommandText = commandText; if (args != null) { foreach (object arg in args) { SQLiteParameter parameter = arg as SQLiteParameter; if (parameter == null) { parameter = command.CreateParameter(); parameter.DbType = DbType.Object; parameter.Value = arg; } command.Parameters.Add(parameter); } } switch (executeType) { case SQLiteExecuteType.None: { // // NOTE: Do nothing. // break; } case SQLiteExecuteType.NonQuery: { return(command.ExecuteNonQuery(commandBehavior)); } case SQLiteExecuteType.Scalar: { return(command.ExecuteScalar(commandBehavior)); } case SQLiteExecuteType.Reader: { bool success = true; try { // // NOTE: The CloseConnection flag is being added here. // This should force the returned data reader to // close the connection when it is disposed. In // order to prevent the containing using block // from disposing the connection prematurely, // the innermost finally block sets the internal // no-disposal flag to true. The outer finally // block will reset the internal no-disposal flag // to false so that the data reader will be able // to (eventually) dispose of the connection. // return(command.ExecuteReader( commandBehavior | CommandBehavior.CloseConnection)); } catch { success = false; throw; } finally { // // NOTE: If an exception was not thrown, that can only // mean the data reader was successfully created // and now owns the connection. Therefore, set // the internal no-disposal flag (temporarily) // in order to exit the containing using block // without disposing it. // if (success) { connection._noDispose = true; } } } } } } } finally { // // NOTE: Now that the using block has been exited, reset the // internal disposal flag for the connection. This is // always done if the connection was created because // it will be harmless whether or not the data reader // now owns it. // if (connection != null) { connection._noDispose = false; } } return(null); }