C# SqliteCommand类代码示例

        /// <summary>
        /// Добавя категория
        /// </summary>
        private void btnIN_Click(object sender, EventArgs e)
        {
            if ((cbTNCategoriesSub.SelectedValue == null)
              || cbTNCategoriesSub.SelectedValue.ToString().Equals(""))
            { return; }
            if (dgvCatOUT.CurrentRow == null)
            { return; }
            Int32 iCategoryID = Convert.ToInt32(dgvCatOUT.CurrentRow.Cells["gcCategoryID_OUT"].Value);
            try
            {
                using (SqliteConnection sqlCnnctn = new SqliteConnection())
                {
                    sqlCnnctn.ConnectionString = _ConnectionString;
                    sqlCnnctn.Open();

                    using (SqliteCommand sqlCmmnd = new SqliteCommand())
                    {
                        sqlCmmnd.Connection = sqlCnnctn;
                        sqlCmmnd.CommandText =
                            "UPDATE x_news_nweb " +
                            "    SET nweb_category_id = " + cbTNCategoriesSub.SelectedValue.ToString() +
                            " WHERE news_category_id = " + iCategoryID.ToString();
                        sqlCmmnd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            cbTNCategoriesSub_SelectedIndexChanged(null, null);
        }

        public void CreateCrypted(string databaseFile)
        {
            try
             {

            // Check if database already exists
            if (!File.Exists (databaseFile))
            {

               // Create the database
               SqliteConnection.CreateFile (databaseFile);

               // Connect to the database

               // using (SqliteConnection sqlCon = GetConnection (String.Format ("Data Source = {0};", databaseFile),"haluk"))
               using (SqliteConnection sqlCon = new SqliteConnection (String.Format ("Data Source = {0}", databaseFile)))

               {
                  sqlCon.SetPassword("haluk");
                  sqlCon.Open ();

                  // Create a table
                  using (SqliteCommand sqlCom = new SqliteCommand (sqlCon))
                  {
                     sqlCom.CommandText = "CREATE TABLE Personel (ID INTEGER PRIMARY KEY, UserName VARCHAR(20), Password VARCHAR(20))";
                     //veri Ekleme
                     //Update
                     //   sqlCom.CommandText = "UPDATE Customers SET FirstName= 'Haluk' WHERE LastName = @lastName";
                     // sqlCom.Parameters.Add(new SqliteParameter("@lastName","Haluky"));

                     sqlCom.ExecuteNonQuery ();

                     Console.WriteLine(sqlCom.ExecuteNonQuery());
                  }
                  //end using sqlCom

                  sqlCon.Close ();

               }
               //end using sqlCon

               this.durumGostericiT.Text = "Database hazır!";

            }else
            {

               this.durumGostericiT.Text = "Database Mevcut!";

            }//end if else

             } catch (Exception ex)
             {

            this.durumGostericiT.Text = String.Format ("Sqlite error: {0}", ex.Message);

             }//end try catch
        }

 public static int Fill(string commandText, SqliteConnection connection, DataTable dataTable)
 {
     int res = 0;
     using (SqliteCommand command = new SqliteCommand())
     {
         command.Connection = connection;
         command.CommandText = commandText;
         using (SqliteDataAdapter sqlDAdapter = new SqliteDataAdapter(command))
         {
             res = sqlDAdapter.Fill(dataTable);
         }
     }
     return res;
 }

        public AuthenticationData Get(UUID principalID)
        {
            AuthenticationData ret = new AuthenticationData();
            ret.Data = new Dictionary<string, object>();

            SqliteCommand cmd = new SqliteCommand("select * from `" + m_Realm + "` where UUID = :PrincipalID");
            cmd.Parameters.Add(new SqliteParameter(":PrincipalID", principalID.ToString()));

            IDataReader result = ExecuteReader(cmd, m_Connection);

            try
            {
                if (result.Read())
                {
                    ret.PrincipalID = principalID;

                    if (m_ColumnNames == null)
                    {
                        m_ColumnNames = new List<string>();

                        DataTable schemaTable = result.GetSchemaTable();
                        foreach (DataRow row in schemaTable.Rows)
                            m_ColumnNames.Add(row["ColumnName"].ToString());
                    }

                    foreach (string s in m_ColumnNames)
                    {
                        if (s == "UUID")
                            continue;

                        ret.Data[s] = result[s].ToString();
                    }

                    return ret;
                }
                else
                {
                    return null;
                }
            }
            catch
            {
            }
            finally
            {
                //CloseCommand(cmd);
            }

            return null;
        }

        protected IDataReader ExecuteReader(SqliteCommand cmd, SqliteConnection connection)
        {
            lock (connection)
            {
                //SqliteConnection newConnection =
                //        (SqliteConnection)((ICloneable)connection).Clone();
                //newConnection.Open();

                //cmd.Connection = newConnection;
                cmd.Connection = connection;
                //Console.WriteLine("XXX " + cmd.CommandText);

                return cmd.ExecuteReader();
            }
        }

        public void LoadNews()
        {
            try
            {
                using (SqliteConnection sqlCnnctn = new SqliteConnection())
                {
                    sqlCnnctn.ConnectionString = _ConnectionString;
                    sqlCnnctn.Open();
                    //
                    using (SqliteCommand sqlCmmndNews = new SqliteCommand())
                    {
                        using (SqliteCommand sqlCmmndEncl = new SqliteCommand())
                        {
                            sqlCmmndNews.Connection = sqlCnnctn;
                            sqlCmmndEncl.Connection = sqlCnnctn;

                            // сваля и зарежда новините
                            foreach (NewsSource ns in _PNews.NSource)
                            {
                                try
                                {
                                    OnLog(LogMessageType.Event, ns.Title);
                                    //
                                    LoadNewsRSS(sqlCmmndNews, sqlCmmndEncl, ns);
                                    //
                                    OnLog(LogMessageType.Event, _PNews.WSaitID.ToString() + _Msg3_NewsChargedHandle);
                                    TmpNewsRepair(sqlCmmndNews);
                                    //
                                    OnLog(LogMessageType.Event, _PNews.WSaitID.ToString() + _Msg4_LoadingNews);
                                    TmpNewsTransfer(sqlCmmndNews, sqlCmmndEncl);
                                    //
                                    OnLog(LogMessageType.Event, _PNews.WSaitID.ToString() + _Msg5_Done);
                                }
                                catch (Exception ex)
                                {
                                    OnLog(LogMessageType.Error, "Exception /Load:" + ns.URL + "/: " + ex.Message);
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                OnLog(LogMessageType.Error, "Exception: " + ex.Message);
            }
        }

        //////////////////////////////////////////////////////////////
        //
        // All non queries are funneled through one connection
        // to increase performance a little
        //
        protected int ExecuteNonQuery(SqliteCommand cmd, SqliteConnection connection)
        {
            lock (connection)
            {
/*
                SqliteConnection newConnection =
                        (SqliteConnection)((ICloneable)connection).Clone();
                newConnection.Open();

                cmd.Connection = newConnection;
*/
                cmd.Connection = connection;
                //Console.WriteLine("XXX " + cmd.CommandText);

                return cmd.ExecuteNonQuery();
            }
        }

		internal SqliteDataReader (SqliteCommand cmd, csSQLite.Vdbe pVm, int version)
		{
			command = cmd;
			rows = new ArrayList ();
			column_names_sens = new Hashtable ();
#if NET_2_0
			column_names_insens = new Hashtable (StringComparer.InvariantCultureIgnoreCase);
#else
			column_names_insens = new Hashtable (CaseInsensitiveHashCodeProvider.DefaultInvariant,
							     CaseInsensitiveComparer.DefaultInvariant);
#endif
			closed = false;
			current_row = -1;
			reading = true;
			ReadpVm (pVm, version, cmd);
			ReadingDone ();
		}

        /// <summary>
        /// Изтрива невалидни записи
        /// </summary>
        private void btnDeleteInvalidEntries_Click(object sender, EventArgs e)
        {
            try
            {
                listBox1.Items.Clear();
                SetMessage("Старт: Изтрива невалидни записи");

                using (SqliteConnection sqlCnnctn = new SqliteConnection())
                {
                    sqlCnnctn.ConnectionString = _ConnectionString;
                    sqlCnnctn.Open();

                    using (SqliteCommand sqlCmmnd = new SqliteCommand())
                    {
                        sqlCmmnd.Connection = sqlCnnctn;

                        // Новини без картинки
                        sqlCmmnd.CommandText =
                            "DELETE FROM news " +
                            " WHERE news_id NOT IN (SELECT DISTINCT news_id FROM enclosure ) ";
                        Int32 iRows = sqlCmmnd.ExecuteNonQuery();
                        SetMessage("Изтити новини без картинки: " + iRows.ToString());

                        // Картинки без новини
                        sqlCmmnd.CommandText =
                            "DELETE FROM enclosure " +
                            " WHERE news_id NOT IN (SELECT news_id FROM news) ";
                        iRows = sqlCmmnd.ExecuteNonQuery();

                        SetMessage("Изтити картинки без новини: " + iRows.ToString());
                    }
                    sqlCnnctn.Close();
                }
                SetMessage("Край");
            }
            catch (Exception ex)
            {
                SetMessage(ex.Message);
            }
        }

        public override IEnumerable<INodeFactory> GetNodeChildren( IContext context )
        {
            var filter = context.Filter;
            var sql = "SELECT * FROM " + Name;
            if( ! String.IsNullOrEmpty( filter ) )
            {
                sql += " WHERE ( " + filter + " )";
            }
            var command = new SqliteCommand(sql , _context.Connection);
            var table = new DataTable( Name );
            
            context.WriteVerbose( sql );
            using( var data = new SqliteDataAdapter(command) )
            {
                data.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                data.Fill(table);
            }

            foreach( DataRow row in table.Rows )
            {
                yield return new SQLiteRecordNodeFactory( _context, row);
            }
        }

 private void btn_Save_Click(object sender, EventArgs e)
 {
     try
     {
         using (SqliteConnection sqlCnnctn = new SqliteConnection())
         {
             sqlCnnctn.ConnectionString = _ConnectionString;
             sqlCnnctn.Open();
             using (SqliteCommand sqlCmmnd = new SqliteCommand())
             {
                 sqlCmmnd.Connection = sqlCnnctn;
                 sqlCmmnd.CommandText =
                     "UPDATE t_news_category " +
                     "    SET n_dscr = @n_dscr " +
                     " WHERE news_category_id = " + _CatID.ToString();
                 sqlCmmnd.Parameters.Add("@n_dscr", DbType.String).Value = tbx_NDscr.Text.Trim();
                 using (SqliteDataReader sqlDReader = sqlCmmnd.ExecuteReader())
                 {
                     if (sqlDReader.HasRows && sqlDReader.Read())
                     {
                         lbl_NCategory.Text = sqlDReader["news_category"].ToString();
                         lbl_NSource.Text = sqlDReader["n_source"].ToString();
                         tbx_NDscr.Text = sqlDReader["n_dscr"].ToString();
                     }
                     sqlDReader.Close();
                 }
             }
             sqlCnnctn.Close();
         }
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.Message);
         return;
     }
     Close();
 }

        /// <summary>
        /// Returns a list of AssetMetadata objects. The list is a subset of
        /// the entire data set offset by <paramref name="start" /> containing
        /// <paramref name="count" /> elements.
        /// </summary>
        /// <param name="start">The number of results to discard from the total data set.</param>
        /// <param name="count">The number of rows the returned list should contain.</param>
        /// <returns>A list of AssetMetadata objects.</returns>
        public override List<AssetMetadata> FetchAssetMetadataSet(int start, int count)
        {
            List<AssetMetadata> retList = new List<AssetMetadata>(count);

            lock (this)
            {
                using (SqliteCommand cmd = new SqliteCommand(SelectAssetMetadataSQL, m_conn))
                {
                    cmd.Parameters.Add(new SqliteParameter(":start", start));
                    cmd.Parameters.Add(new SqliteParameter(":count", count));

                    using (IDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            AssetMetadata metadata = buildAssetMetadata(reader);
                            retList.Add(metadata);
                        }
                    }
                }
            }

            return retList;
        }

        private EstateSettings DoLoad(SqliteCommand cmd, UUID regionID, bool create)
        {
            EstateSettings es = new EstateSettings();
            es.OnSave += StoreEstateSettings;
            IDataReader r = null;
            try
            {
                 r = cmd.ExecuteReader();
            }
            catch (SqliteException)
            {
                m_log.Error("[SQLITE]: There was an issue loading the estate settings.  This can happen the first time running OpenSimulator with CSharpSqlite the first time.  OpenSimulator will probably crash, restart it and it should be good to go.");
            }

            if (r != null && r.Read())
            {
                foreach (string name in FieldList)
                {
                    if (m_FieldMap[name].GetValue(es) is bool)
                    {
                        int v = Convert.ToInt32(r[name]);
                        if (v != 0)
                            m_FieldMap[name].SetValue(es, true);
                        else
                            m_FieldMap[name].SetValue(es, false);
                    }
                    else if (m_FieldMap[name].GetValue(es) is UUID)
                    {
                        UUID uuid = UUID.Zero;

                        UUID.TryParse(r[name].ToString(), out uuid);
                        m_FieldMap[name].SetValue(es, uuid);
                    }
                    else
                    {
                        m_FieldMap[name].SetValue(es, Convert.ChangeType(r[name], m_FieldMap[name].FieldType));
                    }
                }
                r.Close();
            }
            else if (create)
            {
                r.Close();

                List<string> names = new List<string>(FieldList);

                names.Remove("EstateID");

                string sql = "insert into estate_settings ("+String.Join(",", names.ToArray())+") values ( :"+String.Join(", :", names.ToArray())+")";

                cmd.CommandText = sql;
                cmd.Parameters.Clear();

                foreach (string name in FieldList)
                {
                    if (m_FieldMap[name].GetValue(es) is bool)
                    {
                        if ((bool)m_FieldMap[name].GetValue(es))
                            cmd.Parameters.AddWithValue(":"+name, "1");
                        else
                            cmd.Parameters.AddWithValue(":"+name, "0");
                    }
                    else
                    {
                        cmd.Parameters.AddWithValue(":"+name, m_FieldMap[name].GetValue(es).ToString());
                    }
                }

                cmd.ExecuteNonQuery();

                cmd.CommandText = "select LAST_INSERT_ROWID() as id";
                cmd.Parameters.Clear();

                r = cmd.ExecuteReader();

                r.Read();

                es.EstateID = Convert.ToUInt32(r["id"]);

                r.Close();

                cmd.CommandText = "insert into estate_map values (:RegionID, :EstateID)";
                cmd.Parameters.AddWithValue(":RegionID", regionID.ToString());
                cmd.Parameters.AddWithValue(":EstateID", es.EstateID.ToString());

                // This will throw on dupe key
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception)
                {
                }

                es.Save();
            }

            LoadBanList(es);

            es.EstateManagers = LoadUUIDList(es.EstateID, "estate_managers");
//.........这里部分代码省略.........

        /// <summary>
        /// Avoids the additional database trip that using SqliteCommandBuilder requires.
        /// </summary>
        /// <returns>The insert command.</returns>
        /// <param name="table">Table.</param>
        /// <param name="values">Values.</param>
        /// <param name="conflictResolutionStrategy">Conflict resolution strategy.</param>
        SqliteCommand GetInsertCommand (String table, ContentValues values, ConflictResolutionStrategy conflictResolutionStrategy)
        {
            var builder = new StringBuilder("INSERT");

            if (conflictResolutionStrategy != ConflictResolutionStrategy.None) {
                builder.Append(" OR ");
                builder.Append(conflictResolutionStrategy);
            }

            builder.Append(" INTO ");
            builder.Append(table);
            builder.Append(" (");

            // Append our content column names and create our SQL parameters.
            var valueSet = values.ValueSet();
            var sqlParams = new SqliteParameter[valueSet.LongCount()];
            var valueBuilder = new StringBuilder();
            var index = 0L;

            foreach(var column in valueSet)
            {
                if (index > 0) {
                         builder.Append(",");
                    valueBuilder.Append(",");
                }

                     builder.AppendFormat( "{0}", column.Key);
                valueBuilder.AppendFormat("@{0}", column.Key);

                sqlParams[index++] = new SqliteParameter(column.Key, column.Value);
            }

            builder.Append(") VALUES (");
            builder.Append(valueBuilder);
            builder.Append(")");

            var sql = builder.ToString();
            var command = new SqliteCommand(sql, Connection, currentTransaction);
            command.Parameters.Clear();
            command.Parameters.AddRange(sqlParams);

            return command;
        }

        /// <summary>
        /// Avoids the additional database trip that using SqliteCommandBuilder requires.
        /// </summary>
        /// <returns>The delete command.</returns>
        /// <param name="table">Table.</param>
        /// <param name="whereClause">Where clause.</param>
        /// <param name="whereArgs">Where arguments.</param>
        SqliteCommand GetDeleteCommand (string table, string whereClause, string[] whereArgs)
        {
            var builder = new StringBuilder("DELETE FROM ");
            builder.Append(table);
            if (!whereClause.IsEmpty()) {
                builder.Append(" WHERE ");
                builder.Append(whereClause.ReplacePositionalParams());
            }

            var command = new SqliteCommand(builder.ToString(), Connection, currentTransaction);
            command.Parameters.Clear();
            command.Parameters.AddRange(whereArgs.ToSqliteParameters());

            return command;
        }