C# SqlCeParameter类代码示例

        public static bool AccountClearLockout(Guid userGuid)
        {
            StringBuilder sqlCommand = new StringBuilder();
            sqlCommand.Append("UPDATE mp_Users ");
            sqlCommand.Append("SET  ");
            sqlCommand.Append("FailedPasswordAttemptCount = 0, ");
            sqlCommand.Append("FailedPwdAnswerAttemptCount = 0, ");
            sqlCommand.Append("IsLockedOut = 0 ");

            sqlCommand.Append("WHERE  ");
            sqlCommand.Append("UserGuid = @UserGuid ");
            sqlCommand.Append(";");

            SqlCeParameter[] arParams = new SqlCeParameter[1];

            arParams[0] = new SqlCeParameter("@UserGuid", SqlDbType.UniqueIdentifier);
            arParams[0].Direction = ParameterDirection.Input;
            arParams[0].Value = userGuid;

            int rowsAffected = SqlHelper.ExecuteNonQuery(
                GetConnectionString(),
                CommandType.Text,
                sqlCommand.ToString(),
                arParams);

            return (rowsAffected > -1);
        }

        public static bool AccountLockout(Guid userGuid, DateTime lockoutTime)
        {
            StringBuilder sqlCommand = new StringBuilder();
            sqlCommand.Append("UPDATE mp_Users ");
            sqlCommand.Append("SET  ");
            sqlCommand.Append("LastLockoutDate = @LastLockoutDate, ");
            sqlCommand.Append("IsLockedOut = 1 ");

            sqlCommand.Append("WHERE  ");
            sqlCommand.Append("UserGuid = @UserGuid ");
            sqlCommand.Append(";");

            SqlCeParameter[] arParams = new SqlCeParameter[2];

            arParams[0] = new SqlCeParameter("@UserGuid", SqlDbType.UniqueIdentifier);
            arParams[0].Direction = ParameterDirection.Input;
            arParams[0].Value = userGuid;

            arParams[1] = new SqlCeParameter("@LastLockoutDate", SqlDbType.DateTime);
            arParams[1].Direction = ParameterDirection.Input;
            arParams[1].Value = lockoutTime;

            int rowsAffected = SqlHelper.ExecuteNonQuery(
                GetConnectionString(),
                CommandType.Text,
                sqlCommand.ToString(),
                arParams);

            return (rowsAffected > -1);
        }

        public DbDataReader GetUserCountByYearMonth(int siteId)
        {
            StringBuilder sqlCommand = new StringBuilder();
            sqlCommand.Append("SELECT ");
            sqlCommand.Append("DatePart(year,DateCreated) As Y, ");
            sqlCommand.Append("DatePart(month, DateCreated) As M, ");
            //TODO: this line causes an error, not sure what the solution is in SqlCE
            //sqlCommand.Append("(CONVERT(varchar(10),YEAR(DateCreated)) + '-' + CONVERT(varchar(3),MONTH(DateCreated))) As Label, ");
            sqlCommand.Append("'label' AS Label, ");
            sqlCommand.Append("COUNT(*) As Users ");

            sqlCommand.Append("FROM	mp_Users ");
            sqlCommand.Append("WHERE ");
            sqlCommand.Append("SiteID = @SiteID ");
            sqlCommand.Append("GROUP BY ");
            sqlCommand.Append("DatePart(year, DateCreated), DatePart(month,DateCreated) ");
            sqlCommand.Append("ORDER BY ");
            sqlCommand.Append("DatePart(year, DateCreated), DatePart(month,DateCreated) ");
            sqlCommand.Append(";");

            SqlCeParameter[] arParams = new SqlCeParameter[1];

            arParams[0] = new SqlCeParameter("@SiteID", SqlDbType.Int);
            arParams[0].Value = siteId;

            return AdoHelper.ExecuteReader(
                connectionString,
                CommandType.Text,
                sqlCommand.ToString(),
                arParams);
        }

        public SqlCeParameter AddInParameter(string name, object val, SqlDbType type)
        {
            SqlCeParameter sp = new SqlCeParameter(name, val);
            sp.SqlDbType = type;
            return sp;

        }

        static void Main(string[] args)
        {
            SqlCeConnection connection = new SqlCeConnection("data source=D:\\Git\\MTGCardTracker\\Data\\MyDatabase#1.sdf");
            connection.Open();
            string setSQL = "INSERT INTO SETS(Name, Code, ReleaseDate, Border, Type, Block) VALUES ('{1}', '{2}', @ReleaseDate, '{3}', '{4}', '{5}')";
            string cardSQL = "INSERT INTO CARD(Name, CMC, Rarity, Text, Flavor, Layout, MultiverseID, Printing, Names, ManaCost, Colours, SuperTypes, Types, SubTypes, Artist, Number, Power, Toughness, Loyalty, Variations, imageName, watermark, border, rulings, foreignNames, printings) VALUES ('{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}', '{18}', '{19}', '{20}', '{21}', '{22}', '{23}', '{24}', '{25}', '{26}')";
            int cardID = 0;
            int setID = 0;
            var jsd = new JavaScriptSerializer();
            string[] files = Directory.GetFiles("D:\\Git\\mtgjson\\json", "*.json", SearchOption.AllDirectories);
            foreach (string file in files)
            {
                setID++;
                string json = new StreamReader(file).ReadToEnd();
                Set set = jsd.Deserialize<Set>(json);
                SqlCeParameter param = new SqlCeParameter("@ReleaseDate", SqlDbType.DateTime, 32, "ReleaseDate");
                param.Value = set.releaseDate;
                SqlCeCommand setCommand = new SqlCeCommand(String.Format(setSQL, setID, set.name, set.code, set.border, set.type, set.block));
                setCommand.Parameters.Add(param);
                setCommand.Connection = connection;
                setCommand.ExecuteNonQuery();
                foreach (Card card in set.cards)
                {
                    cardID++;

                    SqlCeCommand cardCommand = new SqlCeCommand(String.Format(cardSQL,
                        cardID, card.name.Replace("'", @"\'"), card.cmc, card.rarity, card.text != null ? card.text.Replace("'", @"\'") : null, card.flavor != null ? card.flavor.Replace("'", @"\'") : null, card.layout, card.multiverseid, card.printings, card.names, card.manaCost, card.colors, card.supertypes, card.types, card.subtypes, card.artist, card.number, card.power, card.toughness, card.loyalty, card.variations, card.imageName, card.watermark != null ? card.watermark.Replace("'", @"\'") : null, card.border, "", "", ""));

                    cardCommand.Connection = connection;
                    cardCommand.ExecuteNonQuery();
                }
            }
            connection.Close();
        }

        /// <summary>
        /// Updates the TrackOrder values for the tracks that remain for the PlayerID by incrementing any Tracks that have a TrackOrder value
        /// greater than the provided trackOrder.
        /// </summary>
        /// <param name="playerID">The ID of the Player.</param>
        /// <param name="trackOrder">The TrackOrder value.</param>
        /// <returns>The number of rows affected by the update.</returns>
        public static int AdjustTrackOrdersForDelete(int playerId, int trackOrder)
        {
            StringBuilder sqlCommand = new StringBuilder();
            sqlCommand.Append("UPDATE mp_MediaTrack ");
            sqlCommand.Append("SET TrackOrder = TrackOrder - 1 ");
            sqlCommand.Append("WHERE ");
            sqlCommand.Append("PlayerID = @PlayerID ");
            sqlCommand.Append("AND TrackOrder > @TrackOrder ");
            sqlCommand.Append(";");

            SqlCeParameter[] arParams = new SqlCeParameter[2];

            arParams[0] = new SqlCeParameter("@PlayerID", SqlDbType.Int);
            arParams[0].Direction = ParameterDirection.Input;
            arParams[0].Value = playerId;

            arParams[1] = new SqlCeParameter("@TrackOrder", SqlDbType.Int);
            arParams[1].Direction = ParameterDirection.Input;
            arParams[1].Value = trackOrder;

            int rowsAffected = SqlHelper.ExecuteNonQuery(
                ConnectionString.GetConnectionString(),
                CommandType.Text,
                sqlCommand.ToString(),
                arParams);

            return rowsAffected;
        }

        //public static void Delete(string ipFrom, string ipTo)
        //{
        //    string command = "delete top 1 from ip_ranges where ip_from = @ip_from and ip_to = @ip_to";
        //    SqlCeParameter pIpFrom = new SqlCeParameter() { ParameterName = "ip_from", Value = ipFrom, SqlDbType = SqlDbType.NVarChar };
        //    SqlCeParameter pIpTo = new SqlCeParameter() { ParameterName = "ip_to", Value = ipTo, SqlDbType = SqlDbType.NVarChar };
        //    Db.ExequteSqlCommand(command, pIpFrom, pIpTo);
        //}
        public static void Delete(int id)
        {
            string command = "delete ip_ranges where id = @id";
            SqlCeParameter pId = new SqlCeParameter() { ParameterName = "id", Value = id, SqlDbType = SqlDbType.Int };

            Db.ExequteSqlCommand(command, pId);
        }

        public static bool DeleteByModule(int moduleId)
        {
            StringBuilder sqlCommand = new StringBuilder();
            sqlCommand.Append("DELETE FROM mp_MediaFile ");
            sqlCommand.Append("WHERE ");
            sqlCommand.Append("FileID  ");
            sqlCommand.Append("IN (");
            sqlCommand.Append("SELECT FileID FROM mp_MediaFile WHERE TrackID IN (");
            sqlCommand.Append("SELECT TrackID FROM mp_MediaTrack WHERE PlayerID IN (");
            sqlCommand.Append("SELECT PlayerID FROM mp_MediaPlayer WHERE ModuleID = @ModuleID");
            sqlCommand.Append(")");
            sqlCommand.Append(")");
            sqlCommand.Append(")");
            sqlCommand.Append(";");

            SqlCeParameter[] arParams = new SqlCeParameter[1];

            arParams[0] = new SqlCeParameter("@ModuleID", SqlDbType.Int);
            arParams[0].Direction = ParameterDirection.Input;
            arParams[0].Value = moduleId;

            int rowsAffected = SqlHelper.ExecuteNonQuery(
                ConnectionString.GetConnectionString(),
                CommandType.Text,
                sqlCommand.ToString(),
                arParams);

            return (rowsAffected > -1);
        }

        /// <summary>
        /// Inserts a row in the mp_SurveyResponses table. Returns rows affected count.
        /// </summary>
        /// <param name="responseGuid"> responseGuid </param>
        /// <param name="surveyGuid"> surveyGuid </param>
        /// <param name="userId"> userId </param>
        /// <param name="submissionDate"> submissionDate </param>
        /// <param name="annonymous"> annonymous </param>
        /// <param name="complete"> complete </param>
        /// <returns>int</returns>
        public static int Add(
            Guid responseGuid,
            Guid surveyGuid,
            Guid userGuid,
            bool annonymous,
            bool complete)
        {
            StringBuilder sqlCommand = new StringBuilder();
            sqlCommand.Append("INSERT INTO mp_SurveyResponses ");
            sqlCommand.Append("(");
            sqlCommand.Append("ResponseGuid, ");
            sqlCommand.Append("SurveyGuid, ");
            //sqlCommand.Append("SubmissionDate, ");
            sqlCommand.Append("Annonymous, ");
            sqlCommand.Append("Complete, ");
            sqlCommand.Append("UserGuid ");
            sqlCommand.Append(")");

            sqlCommand.Append(" VALUES ");
            sqlCommand.Append("(");
            sqlCommand.Append("@ResponseGuid, ");
            sqlCommand.Append("@SurveyGuid, ");
            //sqlCommand.Append("@SubmissionDate, ");
            sqlCommand.Append("@Annonymous, ");
            sqlCommand.Append("@Complete, ");
            sqlCommand.Append("@UserGuid ");
            sqlCommand.Append(")");
            sqlCommand.Append(";");

            SqlCeParameter[] arParams = new SqlCeParameter[5];

            arParams[0] = new SqlCeParameter("@ResponseGuid", SqlDbType.UniqueIdentifier);
            arParams[0].Direction = ParameterDirection.Input;
            arParams[0].Value = responseGuid;

            arParams[1] = new SqlCeParameter("@SurveyGuid", SqlDbType.UniqueIdentifier);
            arParams[1].Direction = ParameterDirection.Input;
            arParams[1].Value = surveyGuid;

            arParams[2] = new SqlCeParameter("@Annonymous", SqlDbType.Bit);
            arParams[2].Direction = ParameterDirection.Input;
            arParams[2].Value = annonymous;

            arParams[3] = new SqlCeParameter("@Complete", SqlDbType.Bit);
            arParams[3].Direction = ParameterDirection.Input;
            arParams[3].Value = complete;

            arParams[4] = new SqlCeParameter("@UserGuid", SqlDbType.UniqueIdentifier);
            arParams[4].Direction = ParameterDirection.Input;
            arParams[4].Value = userGuid;

            int rowsAffected = SqlHelper.ExecuteNonQuery(
                GetConnectionString(),
                CommandType.Text,
                sqlCommand.ToString(),
                arParams);

            return rowsAffected;
        }

 public void AddParm( string Name, SqlDbType Type, Object Value, ParameterDirection Direction )
 {
     // Add a parameter to a stored procedure.
     SqlCeParameter p = new SqlCeParameter( Name, Type );
     p.Value = Value;
     p.Direction = Direction;
     _Command.Parameters.Add( p );
 }

 /// <summary>
 ///  获取一个新参数对象
 /// </summary>
 /// <param name="paraName">参数名</param>
 /// <param name="dbType">参数数据类型</param>
 /// <param name="size">参数大小</param>
 /// <returns>特定于数据源的参数对象</returns>
 public override IDataParameter GetParameter(string paraName, System.Data.DbType dbType, int size)
 {
     SqlCeParameter para = new SqlCeParameter();
     para.ParameterName = paraName;
     para.DbType = dbType;
     para.Size = size;
     return para;
 }

        public void Save()
        {
            string command = "insert into ip_ranges (ip_from, ip_to, dattim1) values(@ip_from, @ip_to, @dattim1)";
            SqlCeParameter pIpFrom = new SqlCeParameter() { ParameterName = "ip_from", Value = IpFrom, SqlDbType = SqlDbType.NVarChar };
            SqlCeParameter pIpTo = new SqlCeParameter() { ParameterName = "ip_to", Value = IpTo, SqlDbType = SqlDbType.NVarChar };
            SqlCeParameter pDattim1 = new SqlCeParameter() { ParameterName = "dattim1", Value = DateTime.Now, SqlDbType = SqlDbType.DateTime };

            Db.ExequteSqlCommand(command, pIpFrom, pIpTo, pDattim1);
        }

        /// <summary>
        /// Inserts a row in the mp_IndexingQueue table. Returns new integer id.
        /// </summary>
        /// <param name="indexPath"> indexPath </param>
        /// <param name="serializedItem"> serializedItem </param>
        /// <param name="itemKey"> itemKey </param>
        /// <param name="removeOnly"> removeOnly </param>
        /// <returns>int</returns>
        public static Int64 Create(
            int siteId,
            string indexPath,
            string serializedItem,
            string itemKey,
            bool removeOnly)
        {
            StringBuilder sqlCommand = new StringBuilder();
            sqlCommand.Append("INSERT INTO mp_IndexingQueue ");
            sqlCommand.Append("(");
            sqlCommand.Append("SiteID, ");
            sqlCommand.Append("IndexPath, ");
            sqlCommand.Append("SerializedItem, ");
            sqlCommand.Append("ItemKey, ");
            sqlCommand.Append("RemoveOnly ");
            sqlCommand.Append(")");

            sqlCommand.Append(" VALUES ");
            sqlCommand.Append("(");
            sqlCommand.Append("@SiteID, ");
            sqlCommand.Append("@IndexPath, ");
            sqlCommand.Append("@SerializedItem, ");
            sqlCommand.Append("@ItemKey, ");
            sqlCommand.Append("@RemoveOnly ");
            sqlCommand.Append(")");
            sqlCommand.Append(";");

            SqlCeParameter[] arParams = new SqlCeParameter[5];

            arParams[0] = new SqlCeParameter("@IndexPath", SqlDbType.NVarChar, 255);
            arParams[0].Direction = ParameterDirection.Input;
            arParams[0].Value = indexPath;

            arParams[1] = new SqlCeParameter("@SerializedItem", SqlDbType.NText);
            arParams[1].Direction = ParameterDirection.Input;
            arParams[1].Value = serializedItem;

            arParams[2] = new SqlCeParameter("@ItemKey", SqlDbType.NVarChar, 255);
            arParams[2].Direction = ParameterDirection.Input;
            arParams[2].Value = itemKey;

            arParams[3] = new SqlCeParameter("@RemoveOnly", SqlDbType.Bit);
            arParams[3].Direction = ParameterDirection.Input;
            arParams[3].Value = removeOnly;

            arParams[4] = new SqlCeParameter("@SiteID", SqlDbType.Int);
            arParams[4].Direction = ParameterDirection.Input;
            arParams[4].Value = siteId;

            Int64 newId = Convert.ToInt64(SqlHelper.DoInsertGetIdentitiy(
                GetConnectionString(),
                CommandType.Text,
                sqlCommand.ToString(),
                arParams));

            return newId;
        }

        public static int Add(
            Guid optionGuid,
            Guid pollGuid,
            string answer,
            int order)
        {
            StringBuilder sqlCommand = new StringBuilder();
            sqlCommand.Append("INSERT INTO mp_PollOptions ");
            sqlCommand.Append("(");
            sqlCommand.Append("OptionGuid, ");
            sqlCommand.Append("PollGuid, ");
            sqlCommand.Append("Answer, ");
            sqlCommand.Append("Votes, ");
            sqlCommand.Append("[Order] ");
            sqlCommand.Append(")");

            sqlCommand.Append(" VALUES ");
            sqlCommand.Append("(");
            sqlCommand.Append("@OptionGuid, ");
            sqlCommand.Append("@PollGuid, ");
            sqlCommand.Append("@Answer, ");
            sqlCommand.Append("@Votes, ");
            sqlCommand.Append("@Order ");
            sqlCommand.Append(")");
            sqlCommand.Append(";");

            SqlCeParameter[] arParams = new SqlCeParameter[5];

            arParams[0] = new SqlCeParameter("@OptionGuid", SqlDbType.UniqueIdentifier);
            arParams[0].Direction = ParameterDirection.Input;
            arParams[0].Value = optionGuid;

            arParams[1] = new SqlCeParameter("@PollGuid", SqlDbType.UniqueIdentifier);
            arParams[1].Direction = ParameterDirection.Input;
            arParams[1].Value = pollGuid;

            arParams[2] = new SqlCeParameter("@Answer", SqlDbType.NVarChar, 255);
            arParams[2].Direction = ParameterDirection.Input;
            arParams[2].Value = answer;

            arParams[3] = new SqlCeParameter("@Votes", SqlDbType.Int);
            arParams[3].Direction = ParameterDirection.Input;
            arParams[3].Value = 0;

            arParams[4] = new SqlCeParameter("@Order", SqlDbType.Int);
            arParams[4].Direction = ParameterDirection.Input;
            arParams[4].Value = order;

            int rowsAffected = SqlHelper.ExecuteNonQuery(
                GetConnectionString(),
                CommandType.Text,
                sqlCommand.ToString(),
                arParams);

            return rowsAffected;
        }

        public static int Add(
            Guid answerGuid,
            Guid questionGuid,
            Guid responseGuid,
            string answer)
        {
            StringBuilder sqlCommand = new StringBuilder();
            sqlCommand.Append("INSERT INTO mp_SurveyQuestionAnswers ");
            sqlCommand.Append("(");
            sqlCommand.Append("AnswerGuid, ");
            sqlCommand.Append("QuestionGuid, ");
            sqlCommand.Append("ResponseGuid, ");
            sqlCommand.Append("Answer, ");
            sqlCommand.Append("AnsweredDate ");
            sqlCommand.Append(")");

            sqlCommand.Append(" VALUES ");
            sqlCommand.Append("(");
            sqlCommand.Append("@AnswerGuid, ");
            sqlCommand.Append("@QuestionGuid, ");
            sqlCommand.Append("@ResponseGuid, ");
            sqlCommand.Append("@Answer, ");
            sqlCommand.Append("@AnsweredDate ");
            sqlCommand.Append(")");
            sqlCommand.Append(";");

            SqlCeParameter[] arParams = new SqlCeParameter[5];

            arParams[0] = new SqlCeParameter("@AnswerGuid", SqlDbType.UniqueIdentifier);
            arParams[0].Direction = ParameterDirection.Input;
            arParams[0].Value = answerGuid;

            arParams[1] = new SqlCeParameter("@QuestionGuid", SqlDbType.UniqueIdentifier);
            arParams[1].Direction = ParameterDirection.Input;
            arParams[1].Value = questionGuid;

            arParams[2] = new SqlCeParameter("@ResponseGuid", SqlDbType.UniqueIdentifier);
            arParams[2].Direction = ParameterDirection.Input;
            arParams[2].Value = responseGuid;

            arParams[3] = new SqlCeParameter("@Answer", SqlDbType.NText);
            arParams[3].Direction = ParameterDirection.Input;
            arParams[3].Value = answer;

            arParams[4] = new SqlCeParameter("@AnsweredDate", SqlDbType.DateTime);
            arParams[4].Direction = ParameterDirection.Input;
            arParams[4].Value = DateTime.UtcNow;

            int rowsAffected = SqlHelper.ExecuteNonQuery(
                GetConnectionString(),
                CommandType.Text,
                sqlCommand.ToString(),
                arParams);

            return rowsAffected;
        }