C# Dapper.DynamicParameters类代码示例

        /// <summary>
        /// 添加 Customer
        /// </summary>
        public bool CustomerInsert(Customer model)
        {
            string sql = @"
                            INSERT INTO Customer
                            (
	                            Id,CustomerName,AddTime,IsDelete,CustomerLevel,
	                            PhoneNo,Token,VipPrice,Remark
                            )
                            VALUES
                            (
	                            @Id,@CustomerName,@AddTime,@IsDelete,@CustomerLevel,
	                            @PhoneNo,@Token,@VipPrice,@Remark
                            )
                            ";

            DynamicParameters param = new DynamicParameters();
            param.Add("@Id", model.Id, DbType.Guid);
            param.Add("@CustomerName", model.CustomerName, DbType.String);
            param.Add("@AddTime", model.AddTime, DbType.DateTime);
            param.Add("@IsDelete", model.IsDelete, DbType.Boolean);
            param.Add("@CustomerLevel", model.CustomerLevel, DbType.Byte);
            param.Add("@PhoneNo", model.PhoneNo, DbType.String);
            param.Add("@Token", model.Token, DbType.String);
            param.Add("@VipPrice", model.VipPrice, DbType.Double);
            param.Add("@Remark", model.Remark, DbType.String);


            var result = DataBaseAccessCommand.ExecuteCommand(sql, param);

            return result < 0 ? false : true;
        }

        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 static async Task<long> Insert(Device device)
        {
            const string commandText = @" Insert into Devices (Id, ClientId, RequestDeviceId, IsActived, DeviceKey, DeviceSecret, SerialNumber, IMEI, Manufacturer, Model, Platform, PlatformVersion, DeviceName, DeviceDescription) 
                                         values (@id, @clientId, @requestDeviceId, @isActived, @deviceKey, @deviceSecret, @serialNumber, @iMEI, @manufacturer, @model, @platform, @platformVersion, @deviceName, @deviceDescription)";
            var parameters = new DynamicParameters();
            if (device.Id > 0)
                parameters.Add("id", device.Id);
            else
                parameters.Add("id",0);
            parameters.Add("clientId", device.ClientId);
            parameters.Add("requestDeviceId", device.RequestDeviceId);
            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 void DeletePost(int postID)
        {
            using (SqlConnection cn = new SqlConnection(Settings.ConnectionString))
            {
                var p = new DynamicParameters();

                try
                {
                    p.Add("PostID", postID);

                    cn.Execute("DeletePost", 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 IEnumerable<dynamic> QuerySql(string sql, object p1, Dictionary<string, string> d)
        {
            var cn = GetReadonlyConnection();
            var parameters = new DynamicParameters();
            parameters.Add("@p1", p1 ?? "");
            if (d != null)
                foreach (var kv in d)
                    parameters.Add("@" + kv.Key, kv.Value);

            if (sql.Contains("@UserPeopleId"))
                parameters.Add("@UserPeopleId", data.PeopleId ?? Util.UserPeopleId);
            if (sql.Contains("@CurrentOrgId"))
                parameters.Add("@CurrentOrgId", data.OrgId ?? db.CurrentOrgId0);

            if (sql.Contains("@BlueToolbarTagId"))
                if (dictionary.ContainsKey("BlueToolbarGuid"))
                {
                    var guid = (dictionary["BlueToolbarGuid"] as string).ToGuid();
                    if (!guid.HasValue)
                        throw new Exception("missing BlueToolbar Information");
                    var j = db.PeopleQuery(guid.Value).Select(vv => vv.PeopleId).Take(1000);
                    var tag = db.PopulateTemporaryTag(j);
                    parameters.Add("@BlueToolbarTagId", tag.Id);
                }

            return cn.Query(sql, parameters, commandTimeout: 300);
        }

        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 SqlServerDistributedLock(string resource, TimeSpan timeout, IDbConnection 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("@DbPrincipal", "public");
            parameters.Add("@LockMode", LockMode);
            parameters.Add("@LockOwner", LockOwner);
            parameters.Add("@LockTimeout", timeout.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 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 int Insert(GroupSaleVehicle poco, IDbConnection connection)
 {
     var dynamicParams = new DynamicParameters(mapper.Map(poco));
     dynamicParams.Add("@VehicleID", dbType: DbType.Int32, direction: ParameterDirection.Output);
     connection.Execute("InsertSaleVehicle7", dynamicParams ,commandType: CommandType.StoredProcedure);
     return dynamicParams.Get<int>("@VehicleID");
 }

        public Movie Find(int id)
        {
            var dbArgs = new DynamicParameters();
            dbArgs.Add("id", id);

            return this.db.Query<Movie>("select * from movies where [email protected]", dbArgs).First();
        }

        public static SurveyQuestion SurveyQuestion_Edit(SurveyQuestion model)
        {
            try
            {
                using (var conn = DbHelper.CCService())
                {
                    var p = new DynamicParameters();
                   p.Add("@QuestionID", model.QuestionID);
                    p.Add("@SurveyID", model.SurveyID);
                    p.Add("@Type", model.Type);
                    p.Add("@Conten", model.Conten);
                    p.Add("@IsMust", model.IsMust);
                    p.Add("@IsRandom", model.IsRandom);
                    p.Add("@MinSel", model.MinSel);
                    p.Add("@MaxSel", model.MaxSel);
                    p.Add("@UserID", model.UserID);
                    p.Add("@IsSample", model.IsSample);
                    p.Add("@ChoiceItems", model.ChoiceItems);
                    p.Add("@ChildQuestions", model.ChildQuestions);
                    p.Add("@Orde",model.Orde);
                    conn.Execute("SurveyQuestion_Edit", p, commandType: CommandType.StoredProcedure);
                    //model.QuestionID = p.Get<Int32>("QuestionID");
                    return model;

                }
            }
            catch (Exception e)
            {
                return null;
            }
        }

        public IEnumerable<EventData> Get(Guid aggregateRootId)
        {
            var @params = new DynamicParameters();
            @params.Add("@AggregateRootId", aggregateRootId);

            return _context.Connection.Query<EventData>("[EventStore].[GetEvents]", @params, _databaseTransaction, commandType: CommandType.StoredProcedure);
        }

 public OverviewDataModel GetReleaseOverviewData(string includedReleasePathIDs)
 {
     var data = new OverviewDataModel { LastRefresh = DateTime.Now };
     string[] pathIDs = includedReleasePathIDs != null ? includedReleasePathIDs.Split(new[] { ',' }) : null;
     DynamicParameters queryArguments = new DynamicParameters();
     if (pathIDs != null)
     {
         for (int paramNo = 0; paramNo < pathIDs.Length; paramNo++)
         {
             queryArguments.Add(String.Format("p{0}", paramNo), pathIDs[paramNo]);
         }
     }
     using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ReleaseManagement"].ConnectionString))
     {
         var sql = GenerateOverviewSQL(pathIDs != null ? pathIDs.Length : 0);
         using (var multi = connection.QueryMultiple(sql, queryArguments))
         {
             data.ReleasePaths = multi.Read<ReleasePath>().ToList();
             data.Stages = multi.Read<OverviewStage>().ToList();
             data.StageDetails = multi.Read<OverviewStageDetails>().ToList();
             data.ReleaseComponents = multi.Read<Component>().ToList();
         }
     }
     return data;
 }

        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 Region Retrieve(int id)
        {
            var p = new DynamicParameters();
            p.Add("RegionID", id);

            return _cn.Query<Region>("SELECT * FROM Region WHERE RegionID = @RegionID", p).SingleOrDefault();
        }