C# DynamicParameters.Add方法代码示例

        public void EditPage(StaticPage staticPage)
        {
            using (SqlConnection cn = new SqlConnection(Settings.ConnectionString))
            {
                var p = new DynamicParameters();

                try
                {
                    p.Add("StaticPageID", staticPage.StaticPageID);
                    p.Add("ButtonName", staticPage.ButtonName);
                    p.Add("UserID", staticPage.UserID);
                    p.Add("Body", staticPage.Body);

                    cn.Execute("EditPage", p, commandType: CommandType.StoredProcedure);

                }
                //catch (Exception e)
                //{
                //    // Write failure to database
                //    var ep = new DynamicParameters();

                //    ep.Add("ExceptionType", e.GetType());
                //    ep.Add("ExceptionMessage", e.Message);
                //    cn.Execute("AddError", ep, commandType: CommandType.StoredProcedure);
                //}
                finally
                {
                    cn.Close();
                }
            }
        }

        public static async Task<long> Update(Device device)
        {
            var parameters = new DynamicParameters();
            const string commandText = "Update Devices Set " + "ClientId = @clientId, " + "IsActived = @isActived, " +
                                       "DeviceKey = @deviceKey, " + "DeviceSecret = @deviceSecret, " +
                                       "SerialNumber = @serialNumber, " + "IMEI = @iMEI, " + "Manufacturer = @manufacturer, " +
                                       "Model = @model, " + "Platform = @platform, " + "PlatformVersion = @platformVersion, " +
                                       "DeviceName = @deviceName, " + "DeviceDescription = @deviceDescription " +
                                       "where Id = @id";
            parameters.Add("id", device.Id);
            parameters.Add("clientId", device.ClientId);
            parameters.Add("@isActived", (device.IsActived ? 1 : 0));
            parameters.Add("deviceKey", device.DeviceKey);
            parameters.Add("deviceSecret", device.DeviceSecret);
            parameters.Add("serialNumber", device.SerialNumber);
            parameters.Add("iMEI", device.IMEI);
            parameters.Add("manufacturer", device.Manufacturer);
            parameters.Add("model", device.Model);
            parameters.Add("platform", device.Platform);
            parameters.Add("platformVersion", device.PlatformVersion);
            parameters.Add("@deviceName", device.DeviceName);
            parameters.Add("deviceDescription", device.DeviceDescription);
            long results;
            using (var conn = await DataAccessBase.GetOpenAsync(DataAccessBase.QuangAuthConn))
            {

                var data = await conn.QueryAsync<long>(commandText, parameters);
                results = data.FirstOrDefault();
            }
            return results;



        }

        public bool UpdateUnitWiseProductAllotment(Hashtable ht)
        {
            int res = 0;
            try
            {
                var sql = "[dbo].[UnitWiseProductAllotmentUpdate]";
                DynamicParameters param = new DynamicParameters();
                param.Add("@userid", ht["userid"]);
                param.Add("@ip", ht["ip"]);
                param.Add("@id", ht["id"]);
                param.Add("@field", ht["field"]);
                param.Add("@value", ht["value"]);

                cn.Open();
                res = cn.Execute(sql, param, commandType: CommandType.StoredProcedure);
                cn.Close();

            }
            catch (Exception ex)
            {

            }
            finally
            {
                cn.Close();
            }
            return Convert.ToBoolean(res);
        }

        public XStockViewModel GetStock(int page, int size, string stockCode, string stockName, string store, int type,
            int category, string enable)
        {
            var model = new XStockViewModel();
            var paramss = new DynamicParameters();
            paramss.Add("page", page);
            paramss.Add("size", size);
            paramss.Add("stockCode", stockCode);
            paramss.Add("stockName", stockName);
            paramss.Add("store", store);
            paramss.Add("type", type);
            paramss.Add("stockName", stockName);
            paramss.Add("category", category);
            paramss.Add("enable", enable);
            paramss.Add("out", dbType: DbType.Int32, direction: ParameterDirection.Output);

            using (var sql = GetSqlConnection())
            {
                var data = sql.Query<XStock>("XGetListStock", paramss, commandType: CommandType.StoredProcedure);
                sql.Close();
                model.StockVs = data.ToList();
                var total = paramss.Get<int>("out");
                model.TotalRecords = total;
                var totalTemp = Convert.ToDecimal(total) / Convert.ToDecimal(size);
                model.TotalPages = Convert.ToInt32(Math.Ceiling(totalTemp));
            }

            return model;
        }

        public void Dispose()
        {
            if (_completed) return;

            _completed = true;

            var parameters = new DynamicParameters();
            parameters.Add("@Resource", _resource);
            parameters.Add("@LockOwner", LockOwner);
            parameters.Add("@Result", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

            _connection.Execute(
                @"sp_releaseapplock",
                parameters,
                commandType: CommandType.StoredProcedure);

            var releaseResult = parameters.Get<int>("@Result");

            if (releaseResult < 0)
            {
                throw new SqlServerDistributedLockException(
                    String.Format(
                        "Could not release a lock on the resource '{0}': Server returned the '{1}' error.", 
                        _resource,
                        releaseResult));
            }
        }

        public void a_menu_is_good()
        {
            //Arrange
            string SqlQuery;
            //IEnumerable<SearchTime> fav;
            SearchTime fav;
            Stopwatch stopwatch = new Stopwatch();

            //act
            stopwatch.Start();
            using (DbConnection sqlConnection = GetOpenConnection(connection))
            {
                //sqlConnection.Open();

                SqlQuery = "Web_MainFormSearch";

                var p = new DynamicParameters();
                p.Add("@SearchTerm", "Ray Stantz");
                p.Add("@SearchGhost", true);
                p.Add("@SearchVehicles", true);
                //p.Add("@RetVal", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

                fav = sqlConnection.Query<SearchTime>(SqlQuery, p, commandType: CommandType.StoredProcedure).First();
            }

            stopwatch.Stop();
            int differ = (int)stopwatch.ElapsedMilliseconds;

            //Asserts
            Assert.Greater(fav.SearchName.Length, 1);
            Assert.Greater(fav.GroupType.Length, 1);
            //Assert.Greater(1200, differ);
        }

        // Add new team to database. Team object receieves a TeamID
        public void AddTeam(Team team)
        {
            using (SqlConnection cn = new SqlConnection(Settings.ConnectionString))
            {
                var p = new DynamicParameters();

                try
                {
                    p.Add("TeamName", team.TeamName);
                    p.Add("ManagerName", team.ManagerName);
                    p.Add("LeagueID", team.LeagueID);
                    p.Add("TeamID", DbType.Int32, direction: ParameterDirection.Output);

                    cn.Execute("CreateTeam", p, commandType: CommandType.StoredProcedure);

                    team.TeamID = p.Get<int>("TeamID");
                }
                //catch (Exception e)
                //{
                //    var ep = new DynamicParameters();

                //    ep.Add("ExceptionType", e.GetType());
                //    ep.Add("ExceptionMessage", e.Message);
                //    ep.Add("Input", String.Format("TeamName = {0}, ManagerName = {1}, LeagueID = {2}",
                //        team.TeamName, team.ManagerName, team.LeagueID));
                //    cn.Execute("AddError", ep, commandType: CommandType.StoredProcedure);
                //}
                finally
                {
                    cn.Close();
                }
            }
        }

        public SqlServerDistributedLock(string resource, SqlConnection connection)
        {
            if (String.IsNullOrEmpty(resource)) throw new ArgumentNullException("resource");
            if (connection == null) throw new ArgumentNullException("connection");

            _resource = resource;
            _connection = connection;

            var parameters = new DynamicParameters();
            parameters.Add("@Resource", _resource);
            parameters.Add("@LockMode", LockMode);
            parameters.Add("@LockOwner", LockOwner);
            parameters.Add("@LockTimeout", LockTimeout.TotalMilliseconds);
            parameters.Add("@Result", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

            connection.Execute(
                @"sp_getapplock", 
                parameters, 
                commandType: CommandType.StoredProcedure);

            var lockResult = parameters.Get<int>("@Result");

            if (lockResult < 0)
            {
                throw new SqlServerDistributedLockException(
                    String.Format(
                    "Could not place a lock on the resource '{0}': {1}.",
                    _resource,
                    LockErrorMessages.ContainsKey(lockResult) 
                        ? LockErrorMessages[lockResult]
                        : String.Format("Server returned the '{0}' error.", lockResult)));
            }
        }

        public bool CanAddPlaylist(int userId)
        {
            try
            {
                
                using (var smartTimer = new SmartTimer((x, u) => GatewayLoggerInfo("Exit CanAddPlaylist", userId, x.Elapsed)))
                {
                    GatewayLoggerInfo("CanAddPlaylist", userId);
                    using (var connection = _provider.Create())
                    {
                        var parameters = new DynamicParameters();
                        parameters.Add("@userId", userId);
                        parameters.Add("@canAdd", dbType: DbType.Boolean, direction: ParameterDirection.Output);

                        connection.Execute("user.CanAddPlaylist", parameters, commandType: CommandType.StoredProcedure);

                        return parameters.Get<bool>("@canAdd");
                    }
                }
            }
            catch (System.Exception ex)
            {
                logger.Error(ex);
                throw;
            }
        }

        public void UploadExcelFromSqlToDropBox(string savedQuery, string sqlscript, string targetpath, string filename)
        {
            using (var db2 = NewDataContext())
            {
                var accesstoken = db2.Setting("DropBoxAccessToken", ConfigurationManager.AppSettings["DropBoxAccessToken"]);
                var script = db2.Content(sqlscript, "");
                if (!script.HasValue())
                    throw new Exception("no sql script found");

                var p = new DynamicParameters();
                foreach (var kv in dictionary)
                    p.Add("@" + kv.Key, kv.Value);
                if (script.Contains("@qtagid"))
                {
                    int? qtagid = null;
                    if (savedQuery.HasValue())
                    {
                        var q = db2.PeopleQuery2(savedQuery);
                        var tag = db2.PopulateSpecialTag(q, DbUtil.TagTypeId_Query);
                        qtagid = tag.Id;
                    }
                    p.Add("@qtagid", qtagid);
                }
                var bytes = db2.Connection.ExecuteReader(script, p).ToExcelBytes(filename);

                var wc = new WebClient();
                wc.Headers.Add($"Authorization: Bearer {accesstoken}");
                wc.Headers.Add("Content-Type: application/octet-stream");
                wc.Headers.Add([email protected]"Dropbox-API-Arg: {{""path"":""{targetpath}/{filename}"",""mode"":""overwrite""}}");
                wc.UploadData("https://content.dropboxapi.com/2-beta-2/files/upload", bytes);
            }
        }

 public DynamicParameters AddUserParameters(User user)
 {
     DynamicParameters parameters = new DynamicParameters();
     parameters.Add("@Email", user.Email);
     parameters.Add("@Password", user.Password);
     return parameters;
 }

 public static Result GetPagedFragment(Page page, string sort)
 {
     var dp = new DynamicParameters();
     dp.Add("SKIP", page.Skip);
     dp.Add("PAGESIZE", page.PageSize);
     return new Result([email protected]"{GetSort(sort)} OFFSET @SKIP ROWS FETCH NEXT @PAGESIZE ROWS ONLY", dp);
 }

        public void TestSave()
        {
            var cedarContext = Cedar.CedarAppStore.Instance.GetContextOf("IGD");
            long uid = 0;

            if (cedarContext.IsSetupSchemaRequired)
            {
                cedarContext.SetupSchema(new ShardStartegyData() { StrategyType = Strategy.Sequential });

            }
            uid = cedarContext.CurrentShard;

            long? uId = 0;

            var worker = new Cedar.IdWorker(uid);
            uId = worker.GetUniqueId();

            using (ICedarSession cedarSession = new Cedar.AppContext("IGD").GetSession(uId.Value))
            {
                var parameters = new DynamicParameters();
                parameters.Add("firstName", "TArun");
                parameters.Add("lastName", "Kumar");
                cedarSession.Insert("usp_addemployee", parameters, commandType: Cedar.CommandType.StoredProcedure);
            }
        }

        public void EditUser(EditUserRequest editUser)
        {
            using (var cn = new SqlConnection(Settings.GetConnectionString()))
            {
                var p = new DynamicParameters();
                p.Add("@UserId", editUser.LmsUser.UserId);
                p.Add("@IsStudent", editUser.IsStudent ? 1 : 0);
                p.Add("@IsParent", editUser.IsParent ? 1 : 0);
                p.Add("@IsTeacher", editUser.IsTeacher ? 1 : 0);
                p.Add("@IsAdmin", editUser.IsAdmin ? 1 : 0);

                cn.Execute("spUpdateUserRoles", p, commandType: CommandType.StoredProcedure);

                var p2 = new DynamicParameters();
                p2.Add("@UserId", editUser.LmsUser.UserId);
                p2.Add("@LastName", editUser.LmsUser.LastName);
                p2.Add("@FirstName", editUser.LmsUser.FirstName);
                p2.Add("@Email", editUser.LmsUser.Email);
                p2.Add("@SuggestedRole", editUser.LmsUser.SuggestedRole);
                p2.Add("@GradeLevelId", editUser.LmsUser.GradeLevelId);

                cn.Execute("spUpdateUserDetails", p2, commandType: CommandType.StoredProcedure);

            }
        }

 public ILoginTableModal GetLogin(string procedureName)
 {
     var dynamic = new DynamicParameters();
     dynamic.Add(Constants.User, Modal.UserName);
     dynamic.Add(Constants.Pass, Modal.Password);
     return GetQueryiedFirst<LoginTableModal>(procedureName, dynamic);
 }