C# SqlCeCommand.Prepare方法代码示例

    public static bool IsPersonOnBoard(string boardId, string personId)
    {
        string query = "SELECT * FROM [boards] WHERE [email protected] AND [email protected]";

        if (String.IsNullOrEmpty(boardId) || String.IsNullOrEmpty(personId))
        {
            return false;
        }
        using (SqlCeConnection conn = new SqlCeConnection())
        {
            conn.ConnectionString = CommonState.ConnectionString;
            conn.Open();
            using (SqlCeCommand cmd = new SqlCeCommand(null, conn))
            {
                cmd.CommandText = query;
                cmd.Parameters.Add("@boardId", boardId);
                cmd.Parameters.Add("@personId", personId);
                cmd.Prepare();
                using (SqlCeDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        long last = (long)reader["person_lastactivity"];
                        if (last > (CommonState.EpochTime - Configure.CONNECTOIN_TIMEOUT))
                        {
                            return true;
                        }
                    }
                }
            }
        }
        return false;
    }

        public static void ImportWinners()
        {
            using (FileStream fs = new FileStream(@"Y:\LDYC\Winners.txt", FileMode.Open, FileAccess.Read)) {

            string[] headers;
            using (StreamReader reader = new StreamReader(fs)) {
              headers = reader.ReadLine().Split('\t');

              using (SqlCeConnection conn = Connection) {
            conn.Open();
            using (SqlCeTransaction trans = conn.BeginTransaction()) {

              using (SqlCeCommand comm = new SqlCeCommand()) {
                comm.CommandText = @"INSERT INTO tblWinners(fldTrophyID, fldClassID, fldYear,
            fldSailNumber, fldHelm, fldOwner, fldCreated, fldCrew, fldNotes) VALUES(@fldTrophyID, @fldClassID, @fldYear,
            @fldSailNumber, @fldHelm, @fldOwner, @fldCreated, '', '');
            ";
                comm.Parameters.Add("fldTrophyID", SqlDbType.Int);
                comm.Parameters.Add("fldClassID", SqlDbType.Int);
                comm.Parameters.Add("fldYear", SqlDbType.SmallInt);
                comm.Parameters.Add("fldSailNumber", SqlDbType.NVarChar);
                comm.Parameters.Add("fldHelm", SqlDbType.NVarChar);
                comm.Parameters.Add("fldOwner", SqlDbType.NVarChar);
                comm.Parameters.AddWithValue("fldCreated", DateTime.Now);
                comm.Connection = conn;
                comm.Transaction = trans;
                comm.Prepare();

                string line;
                while ((line = reader.ReadLine()) != null) {
                  string[] parts = line.Split('\t');
                  for (int i = 0; i < headers.Length; i++) {
                    comm.Parameters[headers[i]].Value = parts[i];
                  }

                  comm.ExecuteNonQuery();
                }
              }

              trans.Commit();
            }
              }

            }
              }
        }

 private static Person FindPersonByACol(string colName, string colVal)
 {
     if (String.IsNullOrEmpty(colName) || String.IsNullOrEmpty(colVal))
     {
         return null;
     }
     string query = String.Format("SELECT * FROM [persons] WHERE {0}[email protected]", colName);
     Person p = null;
     using (SqlCeConnection conn = new SqlCeConnection())
     {
         conn.ConnectionString = CommonState.ConnectionString;
         conn.Open();
         using (SqlCeCommand cmd = new SqlCeCommand(null, conn))
         {
             cmd.CommandText = query;
             cmd.Parameters.Add("@colVal", colVal);
             cmd.Prepare();
             using (SqlCeDataReader reader = cmd.ExecuteReader())
             {
                 if (reader.Read())
                 {
                     p = new Person();
                     p.Id = (string)reader[COL_ID];
                     p.Name = (string)reader[COL_NAME];
                     p.LastActivity = (long)reader[COL_LAST];
                 }
             }
         }
     }
     return p;
 }

 private static bool DeletePersonByACol(string colName, string colVal)
 {
     string delete = String.Format("DELETE FROM [persons] WHERE {0}[email protected]", colName);
     using (SqlCeConnection conn = new SqlCeConnection())
     {
         conn.ConnectionString = CommonState.ConnectionString;
         conn.Open();
         using (SqlCeCommand cmd = new SqlCeCommand(null, conn))
         {
             cmd.CommandText = delete;
             cmd.Parameters.Add("@colVal", colVal);
             cmd.Prepare();
             cmd.ExecuteNonQuery();
             // TODO: check if success
         }
     }
     return true;
 }

 public static bool SignUp(string name, string id)
 {
     using (SqlCeConnection conn = new SqlCeConnection())
     {
         string add = String.Format("INSERT INTO [persons] ({0}, {1}, {2}) VALUES (@name, @id, @last)",
             COL_NAME, COL_ID, COL_LAST);
         conn.ConnectionString = CommonState.ConnectionString;
         conn.Open();
         using (SqlCeCommand cmd = new SqlCeCommand(null, conn))
         {
             cmd.CommandText = add;
             cmd.Parameters.Add("@id", id);
             cmd.Parameters.Add("@name", name);
             cmd.Parameters.Add("@last", CommonState.EpochTime);
             cmd.Prepare();
             cmd.ExecuteNonQuery();
             // TODO: check if success
         }
     }
     return true;
 }

 public static List<string> ListPersonOnBoard(string boardId)
 {
     string query = String.Format("SELECT * FROM [boards] WHERE {0}[email protected] AND {1} > {2}",
         COL_BID, COL_LAST, CommonState.EpochTime - Configure.CONNECTOIN_TIMEOUT);
     string query2 =
         "SELECT persons.person_name " +
         "FROM persons INNER JOIN " +
         "         boards ON boards.person_id = persons.person_id " +
         "WHERE [email protected] " +
         "         AND boards.person_lastactivity > " +
         (CommonState.EpochTime - Configure.CONNECTOIN_TIMEOUT).ToString();
     List<string> list = new List<string>();
     using (SqlCeConnection conn = new SqlCeConnection())
     {
         conn.ConnectionString = CommonState.ConnectionString;
         conn.Open();
         using (SqlCeCommand cmd = new SqlCeCommand(null, conn))
         {
             cmd.CommandText = query2;
             cmd.Parameters.Add("@boardId", boardId);
             cmd.Prepare();
             using (SqlCeDataReader reader = cmd.ExecuteReader())
             {
                 while (reader.Read())
                 {
                     list.Add((string)reader[COL_PNAME]);
                 }
             }
         }
     }
     return list;
 }

    public static void KillPersonOnBoard(string boardId, string personId)
    {
        if (String.IsNullOrEmpty(boardId) || String.IsNullOrEmpty(personId))
            return;

        string query = String.
            Format("SELECT * FROM [boards] WHERE {0}[email protected] AND {1}[email protected]", COL_BID, COL_PID);
        string delete = String.
            Format("DELETE FROM [boards] WHERE {0}[email protected] AND {1}[email protected]", COL_BID, COL_PID);

        using (SqlCeConnection conn = new SqlCeConnection())
        {
            conn.ConnectionString = CommonState.ConnectionString;
            conn.Open();
            using (SqlCeCommand cmd = new SqlCeCommand(null, conn))
            {
                cmd.CommandText = query;
                cmd.Parameters.Add("@boardId", boardId);
                cmd.Parameters.Add("@personId", personId);
                cmd.Prepare();
                using (SqlCeDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        cmd.CommandText = delete;
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }
    }

    public static void KeepPersonAliveOnBoard(string boardId, string personId)
    {
        string query = String.
            Format("SELECT * FROM [boards] WHERE {0}[email protected] AND {1}[email protected]", COL_BID, COL_PID);
        string update = String.
            Format("UPDATE [boards] SET {0}[email protected] WHERE {1}[email protected] AND {2}[email protected]", COL_LAST, COL_BID, COL_PID);
        string add = String.
            Format("INSERT INTO [boards] ({0}, {1}, {2}) VALUES (@boardId, @personId, @last)", COL_BID, COL_PID, COL_LAST);
        string updatePerson = String.
            Format("UPDATE [persons] SET {0}[email protected] WHERE {1}[email protected]", COL_LAST, COL_PID);

        using (SqlCeConnection conn = new SqlCeConnection())
        {
            conn.ConnectionString = CommonState.ConnectionString;
            conn.Open();
            using (SqlCeCommand cmd = new SqlCeCommand(null, conn))
            {
                cmd.CommandText = query;
                cmd.Parameters.Add("@boardId", boardId);
                cmd.Parameters.Add("@personId", personId);
                cmd.Parameters.Add("@last", CommonState.EpochTime);
                cmd.Prepare();
                using (SqlCeDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        cmd.CommandText = update;
                        cmd.ExecuteNonQuery();
                    }
                    else
                    {
                        cmd.CommandText = add;
                        cmd.ExecuteNonQuery();
                    }
                }

                // now update person table
                cmd.CommandText = updatePerson;
                cmd.ExecuteNonQuery();
            }
        }
    }

 private SqlCeCommand DoCreateCommandUpdateHistory()
 {
     lock (_connProfile)
     {
         SqlCeCommand cmd = new SqlCeCommand(PracticeStrings.SQL_UPDATE_HISTORY, _connProfile);
         cmd.Parameters.Add(new SqlCeParameter("VocabularyId", SqlDbType.UniqueIdentifier));
         cmd.Parameters.Add(new SqlCeParameter("MeaningId", SqlDbType.UniqueIdentifier));
         cmd.Parameters.Add(new SqlCeParameter("ActionId", SqlDbType.NVarChar));
         cmd.Prepare();
         return cmd;
     }
 }

 private void AddCommand(SqlCeCommand cmd)
 {
     cmd.Prepare();
     _commands[cmd.CommandText] = cmd;
 }

        /// <summary>
        /// Prepare the SQL-statements for every static SQL-Command.
        /// </summary>
        private void PrepareStatements()
        {
            // Creates the commands.
            DoesNewsItemExistCmd1 = DatabaseConnection.CreateCommand();
            DoesNewsItemExistCmd2 = DatabaseConnection.CreateCommand();
            GetIdfCmd1 = DatabaseConnection.CreateCommand();
            GetIdfCmd2 = DatabaseConnection.CreateCommand();
            StoreTermsCmd1 = DatabaseConnection.CreateCommand();
            StoreTermsCmd2 = DatabaseConnection.CreateCommand();
            FindCategoryCmd1 = DatabaseConnection.CreateCommand();
            FindCategoryCmd2 = DatabaseConnection.CreateCommand();
            FindCategoryCmd3 = DatabaseConnection.CreateCommand();
            StoreTfValuesCmd = DatabaseConnection.CreateCommand();
            GetNewsSourceIdCmd = DatabaseConnection.CreateCommand();
            StoreNewsItemCmd1 = DatabaseConnection.CreateCommand();
            StoreNewsItemCmd2 = DatabaseConnection.CreateCommand();
            StoreNewsItemCmd3 = DatabaseConnection.CreateCommand();
            AddNewsSourceCmd = DatabaseConnection.CreateCommand();
            GetUniqueTermCountCmd = DatabaseConnection.CreateCommand();
            GetNewsSourcesCmd1 = DatabaseConnection.CreateCommand();
            GetNewsSourcesCmd2 = DatabaseConnection.CreateCommand();
            RemoveNewsSourceCmd1 = DatabaseConnection.CreateCommand();
            RemoveNewsSourceCmd2 = DatabaseConnection.CreateCommand();
            SetNewsInterestingStatusCmd = DatabaseConnection.CreateCommand();
            SetNewsReadStatusCmd = DatabaseConnection.CreateCommand();
            SetReadingSpeedCmd = DatabaseConnection.CreateCommand();
            UpdateNewsSourceCmd = DatabaseConnection.CreateCommand();
            UpdateIdfValuesCmd = DatabaseConnection.CreateCommand();
            GetReadingSpeedCmd = DatabaseConnection.CreateCommand();
            GetTfIdfVectorCmd = DatabaseConnection.CreateCommand();
            MarkCategoryInterestingCmd = DatabaseConnection.CreateCommand();
            AddCategoryCmd1 = DatabaseConnection.CreateCommand();
            AddCategoryCmd2 = DatabaseConnection.CreateCommand();
            RemoveCategoryCmd1 = DatabaseConnection.CreateCommand();
            RemoveCategoryCmd2 = DatabaseConnection.CreateCommand();
            RemoveCategoryCmd3 = DatabaseConnection.CreateCommand();
            GetCategoriesCmd = DatabaseConnection.CreateCommand();
            UpdateTermCountTableCmd = DatabaseConnection.CreateCommand();

            // Prepare DoesNewsItemExistCmd statements.
            DoesNewsItemExistCmd1.CommandText = "SELECT id FROM news WHERE guid=?";
            DoesNewsItemExistCmd1.Parameters.Add(new SqlCeParameter("p1", SqlDbType.NVarChar));
            DoesNewsItemExistCmd1.Parameters["p1"].Size = NEWS_GUID_SIZE;
            DoesNewsItemExistCmd1.Prepare();

            DoesNewsItemExistCmd2.CommandText = "DELETE FROM news_term_joins WHERE news_id=?";
            DoesNewsItemExistCmd2.Parameters.Add(new SqlCeParameter("p1", SqlDbType.Int));
            DoesNewsItemExistCmd2.Prepare();

            // Prepare GetIdfCmd statements.
            GetIdfCmd1.CommandText = "SELECT COUNT(id) FROM news";
            GetIdfCmd1.Prepare();

            GetIdfCmd2.CommandText = "SELECT COUNT(news_id) FROM news_term_joins WHERE term_id=?";
            GetIdfCmd2.Parameters.Add(new SqlCeParameter("p1", SqlDbType.Int));
            GetIdfCmd2.Prepare();

            // Prepare StoreTermsCmd statements.
            StoreTermsCmd1.CommandText = "INSERT INTO terms (term, idf, created_at, updated_at) " +
                "VALUES (?, ?, ?, ?)";
            StoreTermsCmd1.Parameters.Add(new SqlCeParameter("p1", SqlDbType.NVarChar));
            StoreTermsCmd1.Parameters["p1"].Size = TERMS_TERM_SIZE;
            StoreTermsCmd1.Parameters.Add(new SqlCeParameter("p2", SqlDbType.Float));
            StoreTermsCmd1.Parameters.Add(new SqlCeParameter("p3", SqlDbType.DateTime));
            StoreTermsCmd1.Parameters.Add(new SqlCeParameter("p4", SqlDbType.DateTime));
            StoreTermsCmd1.Prepare();

            StoreTermsCmd2.CommandText = "UPDATE terms SET term=?, idf=?, updated_at=? "
                + "WHERE id=?";
            StoreTermsCmd2.Parameters.Add(new SqlCeParameter("p1", SqlDbType.NVarChar));
            StoreTermsCmd2.Parameters["p1"].Size = TERMS_TERM_SIZE;
            StoreTermsCmd2.Parameters.Add(new SqlCeParameter("p2", SqlDbType.Float));
            StoreTermsCmd2.Parameters.Add(new SqlCeParameter("p3", SqlDbType.DateTime));
            StoreTermsCmd2.Parameters.Add(new SqlCeParameter("p4", SqlDbType.Int));
            StoreTermsCmd2.Prepare();

            // Prepare FindCategoryCmd statements.
            FindCategoryCmd1.CommandText = "SELECT id, name FROM categories";
            FindCategoryCmd1.Prepare();

            FindCategoryCmd2.CommandText = "SELECT COUNT(id) FROM news WHERE category_id=?";
            FindCategoryCmd2.Parameters.Add(new SqlCeParameter("p1", SqlDbType.Int));
            FindCategoryCmd2.Prepare();

            FindCategoryCmd3.CommandText =
                "SELECT terms.term, term_counts.interesting_count, " +
                "term_counts.uninteresting_count, term_counts.unknown_count " +
                "FROM term_counts " +
                "INNER JOIN terms ON term_counts.term_id=terms.id " +
                "WHERE category_id=?";
            FindCategoryCmd3.Parameters.Add(new SqlCeParameter("p1", SqlDbType.Int));
            FindCategoryCmd3.Prepare();

            // Prepare StoreTfValuesCmd statements.
            StoreTfValuesCmd.CommandText = "INSERT INTO news_term_joins(news_id, term_id, tf, median_index, " +
                "created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?)";
            StoreTfValuesCmd.Parameters.Add(new SqlCeParameter("p1", SqlDbType.Int));
//.........这里部分代码省略.........