C# DynamicParameters.Get方法代码示例

        public FineUploaderResult AccountsImport(FineUpload upload)
        {
            try
            {
                List<Account> list;

                using (var csv = new CsvReader(new StreamReader(upload.InputStream)))
                {
                    csv.Configuration.ClassMapping<AccountMap, Account>();
                    list = csv.GetRecords<Account>().ToList();
                }

                //save to \\userdata01\Localuser\Users\HTMLEngager
                var folder = ConfigurationManager.AppSettings["WebDriver/AccountImport/Folder"];
                var fileName = string.Format("{0}\\{1:yyyyMMddHHmmssfff}.csv", folder, DateTime.UtcNow);
                using (var csv = new CsvWriter(new StreamWriter(fileName)))
                {
                    csv.Configuration.ClassMapping<AccountMap, Account>();
                    csv.WriteRecords(list);
                }

                //call mark sporc and wait for results
                var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["WebDriverEngager"].ConnectionString);
               
                var p = new DynamicParameters();
                p.Add("@SourceFilename", fileName);
                p.Add("@Updated", dbType: DbType.Int32, direction: ParameterDirection.Output);
                p.Add("@Inserted", dbType: DbType.Int32, direction: ParameterDirection.Output);
                conn.Open();
                conn.Execute("HTMLEngagerUpsert", p, commandType: CommandType.StoredProcedure);
                conn.Close();

                //delete file after import
                System.IO.File.Delete(fileName);

                return new FineUploaderResult(true, new
                                                    {
                                                        accountCount = list.Count(),
                                                        insertCount = p.Get<int>("@Inserted"),
                                                        updateCount = p.Get<int>("@Updated")
                                                    });
            }
            catch (Exception ex)
            {
                return new FineUploaderResult(false, error: ex.Message);
            }
        }

        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;
            }
        }

 /// <summary>
 /// 论坛版块添加
 /// </summary>
 /// <param name="model"></param>
 /// <returns></returns>
 public static ForumType ForumType_ADD(ForumType model)
 {
     try
     {
         using (var conn = DbHelper.CCService())
         {
             var p = new DynamicParameters();
             p.Add("@ForumTypeID", dbType: DbType.Int32, direction: ParameterDirection.Output);
             p.Add("@OCID", model.OCID);
             p.Add("@CourseID", model.CourseID);
             p.Add("@Title", model.Title);
             p.Add("@IsEssence", model.IsEssence);
             p.Add("@Brief", model.Brief);
             p.Add("@TeachingClassID", model.TeachingClassID);
             p.Add("@IsPublic", model.IsPublic);
             p.Add("@UserID", model.UserID);
             conn.Execute("ForumType_ADD", p, commandType: CommandType.StoredProcedure);
             model.ForumTypeID = p.Get<int>("@ForumTypeID");
             return model;
         }
     }
     catch (Exception e)
     {
         return null;
     }
 }

 public static ScoreManageInfo ScoreManageInfo_Add(ScoreManageInfo model)
 {
     try
     {
         using (var conn = DbHelper.CCService())
         {
             var p = new DynamicParameters();
             p.Add("@output", dbType: DbType.Int32, direction: ParameterDirection.Output);
             p.Add("@OCID", model.OCID);
             p.Add("@UserID", model.UserID);
             p.Add("@UserName", model.UserName);
             p.Add("@CourseID", model.CourseID);
             p.Add("@StartDate", model.StartDate);
             p.Add("@EndDate", model.EndDate);
             p.Add("@Name", model.Name);
             p.Add("@ScoreTypeID", model.ScoreTypeID);
             conn.Execute("Score_Test_ADD", p, commandType: CommandType.StoredProcedure);
             model.TestID = p.Get<int>("output");
             return model;
         }
     }
     catch (Exception e)
     {
         return null;
     }
 }

 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 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 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 Policy AddNewPolicy(Policy newPolicy)
        {
            using (SqlConnection cn = new SqlConnection(Settings.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = cn;
                cn.Open();
                //if new category, check to see if category exists via search then add it

                //Add Policy in existing category
                var p = new DynamicParameters();
                p.Add("PolicyTitle", newPolicy.Title);
                p.Add("CategoryID", newPolicy.Category.CategoryId);
                p.Add("DateCreated", newPolicy.DateCreated);
                p.Add("ContentText", newPolicy.ContentText);
                p.Add("PolicyID", DbType.Int32, direction: ParameterDirection.Output);

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

                newPolicy.PolicyId = p.Get<int>("PolicyID");

                var p1 = new DynamicParameters();
                p1.Add("CategoryID", newPolicy.Category.CategoryId);
                var category =
                    cn.Query<PolicyCategory>("SELECT * FROM Categories WHERE CategoryID = @CategoryID", p1)
                        .FirstOrDefault();
                if (category != null)
                {
                   newPolicy.Category.CategoryTitle = category.CategoryTitle;
                }

            }
            return newPolicy;
        }

        // 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();
                }
            }
        }

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int AddCrowdInfo(CrowdInfoModel model)
        {
            const string sql = @"INSERT INTO activity_crow_info
                                (innerid, title, subtitle, enrollstarttime, enrollendtime, secrettime,uppertotal,uppereach, prize, status, type,flagcode, qrcode, remark, extend, createrid, createdtime, modifierid, modifiedtime)
                                VALUES
                                (@innerid, @title, @subtitle, @enrollstarttime, @enrollendtime, @secrettime,@uppertotal,@uppereach, @prize, @status, @type,@flagcode, @qrcode, @remark, @extend, @createrid, @createdtime, @modifierid, @modifiedtime);";

            using (var conn = Helper.GetConnection())
            {
                int result;
                try
                {
                    //生成编号
                    var obj = new
                    {
                        p_tablename = "activity_crow_info",
                        p_columnname = "flagcode",
                        p_prefix = "A",
                        p_length = 4,
                        p_hasdate = 0
                    };

                    var args = new DynamicParameters(obj);
                    args.Add("p_value", dbType: DbType.String, direction: ParameterDirection.Output);
                    args.Add("p_errmessage", dbType: DbType.String, direction: ParameterDirection.Output);

                    using (conn.QueryMultiple("sp_automaticnumbering", args, commandType: CommandType.StoredProcedure)) { }

                    model.Flagcode = args.Get<string>("p_value");

                    if (string.IsNullOrWhiteSpace(model.Flagcode))
                    {
                        var msg = args.Get<string>("p_errmessage");
                        LoggerFactories.CreateLogger().Write("活动码生成失败：" + msg, TraceEventType.Error);
                        return -1;
                    }
                    result = conn.Execute(sql, model);
                }
                catch (Exception ex)
                {
                    LoggerFactories.CreateLogger().Write("AddCrowdInfo异常：", TraceEventType.Error, ex);
                    result = 0;
                }

                return result;
            }
        }

 public void TestExecuteAsyncCommandWithHybridParameters()
 {
     var p = new DynamicParameters(new { a = 1, b = 2 });
     p.Add("c", dbType: DbType.Int32, direction: ParameterDirection.Output);
     using (var connection = Program.GetOpenConnection())
     {
         connection.ExecuteAsync(@"set @c = @a + @b", p).Wait();
         p.Get<int>("@c").IsEqualTo(3);
     }
 }

 public Technology AddTechnology(Technology technology)
 {
     using (SqlConnection connection = new SqlConnection(Settings.GetConnectionString()))
     {
         DynamicParameters param = new DynamicParameters();
         param.Add("@Name", technology.Name);
         param.Add("@TechnologyID", dbType: DbType.Int32, direction: ParameterDirection.Output);
         connection.Execute("TechnologyAdd", param, commandType: CommandType.StoredProcedure);
         technology.TechnologyID = param.Get<int>("@TechnologyID");
     }
     return technology;
 }

        /// <summary>
        /// Creates a new project.
        /// </summary>
        /// <param name="name">The name of the project.</param>
        /// <returns>The database identifier of the newly created project.</returns>
        public Guid Execute(string name)
        {
            using (var connection = GetConnection())
            {
                var parameters = new DynamicParameters();
                parameters.Add("name", name);
                parameters.Add("id", dbType: DbType.Guid, direction: ParameterDirection.Output);

                connection.Execute(Sql, parameters);

                return parameters.Get<Guid>("id");
            }
        }

 public BootcampLocation AddBootcampLocation(BootcampLocation location)
 {
     using (SqlConnection connection = new SqlConnection(Settings.GetConnectionString()))
     {
         DynamicParameters param = new DynamicParameters();
         param.Add("@BootcampID", location.BootcampID);
         param.Add("@LocationID", location.LocationID);
         param.Add("@BootcampLocationID", dbType: DbType.Int32, direction: ParameterDirection.Output);
         connection.Execute("BootcampLocationsAdd", param, commandType: CommandType.StoredProcedure);
         location.BootcampLocationID = param.Get<int>("@BootcampLocationID");
     }
     return location;
 }

 public BootcampTechnology AddBootcampTechnology(BootcampTechnology obj)
 {
     using (SqlConnection connection = new SqlConnection(Settings.GetConnectionString()))
     {
         DynamicParameters param = new DynamicParameters();
         param.Add("@TechnologyID", obj.TechnologyID);
         param.Add("@BootcampID", obj.BootcampID);
         param.Add("@BootcampTechnologyID", dbType: DbType.Int32, direction: ParameterDirection.Output);
         connection.Execute("BootcampTechnologyAdd", commandType: CommandType.StoredProcedure);
         obj.BootcampTechnologyID = param.Get<int>("@BootcampTechnologyID");
     }
     return obj;
 }