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);
}
}
/// <summary>
/// 获取客户需求
/// </summary>
/// <param name="Cusid">需求ID</param>
/// <returns></returns>
public string GetCus(int Cusid)
{
try
{
var param = new Dapper.DynamicParameters();
param.Add("@Agentid", User.userid);
param.Add("@Cusid", Cusid);
param.Add("@state", 0, DbType.Int32, ParameterDirection.Output);
param.Add("@msg", 0, DbType.String, ParameterDirection.Output, size: 100);
var res2 = shhouseconn.Execute("exchange_GetCus", param, null, null, CommandType.StoredProcedure);
int _state = param.Get <int>("@state");
string msg = param.Get <string>("@msg");
return(JsonConvert.SerializeObject(new
{
state = _state,
msg = msg
}));
}
catch (Exception e)
{
return(JsonConvert.SerializeObject(new repmsg {
state = 2, msg = "网络异常，请稍后再试!", data = e
}));
}
}
protected void LoadData()
{
ProjectBLL bll = new ProjectBLL();
var dyParams = new Dapper.DynamicParameters();
dyParams.Add("@SubscribeId", TransferId);
dyParams.Add("@projectId", null, DbType.Guid, ParameterDirection.Output, 36);
dyParams.Add("@addDate", null, DbType.DateTime, ParameterDirection.Output, 0);
dyParams.Add("@refundedMonths", null, DbType.Int32, ParameterDirection.Output, 0);
string strSQL = @"select @projectId=ProjectId, @addDate=AddDate,@refundedMonths= RefundedMonths FROM dbo.Subscribe WITH(NOLOCK) where Id=@SubscribeId";
PublicConn.ExecuteTD(PublicConn.DBWriteType.Read, strSQL, ref dyParams);
Guid projectId = dyParams.Get <Guid>("@projectId");
//获取项目信息
model = bll.GetProjectDetailInfo(projectId);
DateTime investDate = dyParams.Get <DateTime>("@addDate");
int refundedMonths = dyParams.Get <Int32>("@refundedMonths");
string sql = string.Empty;
if (model.RepaymentType.Value == 1)//到期还本息
{
sql = "SELECT dateDiff(day,@BeginDate,getdate())-1";
}
else
{
sql = "SELECT dateDiff(day,dbo.f_GetRepaymentAdvance_Date(@BeginDate,@Month),getdate())";
}
dyParams = new Dapper.DynamicParameters();
dyParams.Add("@BeginDate", investDate);
dyParams.Add("@Month", refundedMonths);
DiffDay = PublicConn.QuerySingle <int>(sql, ref dyParams);
WebSettingBLL websetbll = new WebSettingBLL();
WebSettingEntity = websetbll.GetWebSettingInfo("588B23C6-56EC-40C1-80A6-09B19C6F21E1");
if (WebSettingEntity == null)
{
WebSettingEntity = new WebSettingInfo();
}
zqzrRateSet = websetbll.GetWebSettingInfo("63C10EF7-3961-4CA3-B277-147AAD8E7D42");
if (zqzrRateSet.Param1Value == "1" && DateTime.Parse(zqzrRateSet.Param2Value) < DateTime.Now && DateTime.Now < DateTime.Parse(zqzrRateSet.Param3Value))
{
IsOpenRateLimit = true;
}
}
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)));
}
}
/// <summary>
/// sql查询 分页集合对象
/// </summary>
/// <param name="sql"></param>
/// <param name="param"></param>
/// <param name="commandTimeout"></param>
/// <param name="commandType"></param>
/// <returns></returns>
public virtual async Task <PagedResult <TEntity> > QueryPageAsync(PagedRequest request, object param = null, int?commandTimeout = null, CommandType commandType = CommandType.StoredProcedure)
{
var type = typeof(TEntity);
var para = new Dapper.DynamicParameters();
para.Add("TableName", Reflector.GetTableName(type), DbType.String, ParameterDirection.Input);
para.Add("FieldsStr", "*", DbType.String, ParameterDirection.Input);
para.Add("OrderString", request.Order, DbType.String, ParameterDirection.Input);
para.Add("PageIndex", request.PageIndex, DbType.Int16, ParameterDirection.Input);
para.Add("PageSize", request.PageSize, DbType.Int16, ParameterDirection.Input);
para.Add("TotalRecord", request.PageSize, DbType.Int64, ParameterDirection.Output);
para.AddDynamicParams(param);
var DataList = await _context._connection.QueryAsync <TEntity>("proc_PageList", para, _context._transaction, commandTimeout, CommandType.StoredProcedure);
var TotalRecord = para.Get <long>("TotalRecord");
PagedResult <TEntity> entities = new PagedResult <TEntity>
{
Data = DataList.ToList(),
TotalRecords = TotalRecord,
PageIndex = request.PageIndex,
PageSize = request.PageSize,
TotalPages = ((int)TotalRecord + request.PageSize - 1) / request.PageSize
};
return(entities);
}
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 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 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;
}
}
// 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 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 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;
}
/// <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 void AddNewStaticPage(StaticPage newStaticPage)
{
var p = new DynamicParameters();
newStaticPage.StaticPageDate = DateTime.Today;
p.Add("StaticPageDate", newStaticPage.StaticPageDate);
p.Add("StaticPageTitle", newStaticPage.StaticPageTitle);
p.Add("StaticPageContent", newStaticPage.StaticPageContent);
p.Add("StaticPageID", dbType: DbType.Int32, direction: ParameterDirection.Output);
_cn.Execute("AddNewStaticPage", p, commandType: CommandType.StoredProcedure);
p.Get<int>("StaticPageID");
}
public Site AddSite(Site site)
{
using (SqlConnection connection = new SqlConnection(Settings.GetConnectionString()))
{
DynamicParameters param = new DynamicParameters();
param.Add("@SiteName", site.SiteName);
param.Add("@SiteLogoURL", site.SiteLogoURL);
param.Add("@SiteID", dbType: DbType.Int32, direction: ParameterDirection.Output);
connection.Execute("SitesAdd", param, commandType: CommandType.StoredProcedure);
site.SiteID = param.Get<int>("@SiteID");
}
return site;
}
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;
}
public int GetBidCountForChallenge(long ChallengeID)
{
using (SqlConnection db = new SqlConnection(connStr))
{
db.Open();
DynamicParameters p = new DynamicParameters();
p.Add("@ChallengeID", ChallengeID, DbType.Int64, ParameterDirection.Input);
p.Add("@BidCount", dbType: DbType.Int32, direction: ParameterDirection.Output);
db.Execute("spBidCountForChallenge", p, commandType: CommandType.StoredProcedure);
return p.Get<int>("@BidCount");
}
}
public TradeStates?UpgradeTradeState(string tradeId, TradeStates state)
{
var executeSqlString = @"spUpgradeTradeState";
using (var database = this.databaseFactory.GenerateDatabase(isWriteOnly: false)) {
var parameters = new Dapper.DynamicParameters();
parameters.Add("@tradeId", tradeId, System.Data.DbType.String);
parameters.Add("@tradeState", (int)state, System.Data.DbType.Int32);
parameters.Add("@o_state", null, System.Data.DbType.Int32, System.Data.ParameterDirection.Output);
database.Execute(executeSqlString, parameters, System.Data.CommandType.StoredProcedure, true);
return(parameters.Get <TradeStates>("@o_state"));
}
}
public void AddToRoster(RosterAddRequest request)
{
using (var cn = new SqlConnection(Config.GetConnectionString()))
{
var p = new DynamicParameters();
p.Add("@RosterId", dbType: DbType.Int32, direction: ParameterDirection.Output);
p.Add("@UserId", request.UserId);
p.Add("@ClassId", request.ClassId);
cn.Execute("RosterInsert", p, commandType: CommandType.StoredProcedure);
request.RosterId = p.Get<int>("@RosterId");
}
}
public Ingredient AddIngredient(Ingredient ingredient)
{
using (var connection = new SqlConnection(Settings.GetConnectionString()))
{
var p = new DynamicParameters();
p.Add("Name", ingredient.Name);
p.Add("IsLiquor", ingredient.IsLiquor);
p.Add("IngredientId", dbType: DbType.Int32, direction: ParameterDirection.Output);
connection.Execute("IngredientAdd", p, commandType: CommandType.StoredProcedure);
ingredient.IngredientId = p.Get<int>("IngredientId");
return ingredient;
}
}
internal int GetCountByOutParam()
{
using (var cn = DbConnectionUtil.GetConnection())
{
cn.Open();
// 基本的にはGetCountByReturn()のケースと同様。
// outputパラメータで値を受け取る場合は引数directionにSystem.Data.ParameterDirection.Outputを設定。
var param = new DynamicParameters(new { baseAge = 24 });
param.Add("count", 0, direction: System.Data.ParameterDirection.Output);
cn.Execute("GetCountOlderByOutputParamProc", param, commandType: System.Data.CommandType.StoredProcedure);
return param.Get<int>("count");
}
}
public ContactForm AddContactForm(ContactForm form)
{
using (SqlConnection connection = new SqlConnection(Settings.GetConnectionString()))
{
DynamicParameters param = new DynamicParameters();
param.Add("@ContactFormID", dbType: DbType.Int32, direction: ParameterDirection.Output);
param.Add("@Name", form.Name);
param.Add("@Email", form.Email);
param.Add("@Message", form.Message);
connection.Execute("ContactFormAdd", param, commandType: CommandType.StoredProcedure);
form.ContactFormID = param.Get<int>("@ContactFormID");
}
return form;
}
public static void RetrieveOutputParameter()
{
using (SqlConnection cn = new SqlConnection(Settings.ConnectionString))
{
var p = new DynamicParameters();
p.Add("RegionDescription", "New Region");
p.Add("RegionId", dbType: DbType.Int32, direction: ParameterDirection.Output);
cn.Execute("RegionInsert", p, commandType: CommandType.StoredProcedure);
int regionID = p.Get<int>("RegionId");
Console.WriteLine("Id = {0}", regionID);
}
}
public void GetDataByStoredProcedure()
{
// Get Repository
IRepository<Customer, CustomerEnum> repo = UnitOfWork.GetRepository<Customer, CustomerEnum>();
// Executing stored procedure
var param = new DynamicParameters();
param.Add("@startIndex", 10);
param.Add("@endIndex", 20);
param.Add("@count", dbType: DbType.Int32, direction: ParameterDirection.Output);
//Example for string return / out param
//param.Add("@errorMsg", dbType: DbType.String, size: 4000, direction: ParameterDirection.ReturnValue);
IEnumerable<Customer> customers = repo.Exec<Customer>(CustomerEnum.GetCustomerByPage, param);
int count = param.Get<int>("@count");
}
//获取复投宝的待收本息
public Tuple <decimal, decimal> GetFTBDueInAmountInterest(Guid userId)
{
var dyParams = new Dapper.DynamicParameters();
dyParams.Add("@UserId", userId);
dyParams.Add("@totalAmount", 0, DbType.Decimal, ParameterDirection.Output, 18, null, 2);
dyParams.Add("@totalInterest", 0, DbType.Decimal, ParameterDirection.Output, 18, null, 2);
string strSQL = "select @totalAmount=RecoverBorrowOut, @totalInterest=DueComeInterest from We_FundAccountInfo with(nolock) where UserId=@UserId";
PublicConn.ExecuteTD(PublicConn.DBWriteType.Read, strSQL, ref dyParams);
Tuple <decimal, decimal> result = new Tuple <decimal, decimal>(dyParams.Get <decimal?>("@totalAmount") ?? 0, dyParams.Get <decimal?>("@totalInterest") ?? 0);
return(result);
}
/// <summary>
/// 执行存储过程-2
/// Execute StroedProcedure and get result from return value
/// </summary>
/// <param name="sqlConnnectionString"></param>
public static void ExecuteStoredProcedureWithParms(string sqlConnnectionString)
{
DynamicParameters p = new DynamicParameters();
p.Add("@UserName", "cooper");
p.Add("@Password", "123456");
p.Add("@LoginActionType", null, DbType.Int32, ParameterDirection.ReturnValue);
using (IDbConnection cnn = GetSqlConnection(sqlConnnectionString))
{
cnn.Execute("dbo.p_validateUser", p, null, null, CommandType.StoredProcedure);
int result = p.Get<int>("@LoginActionType");
Console.WriteLine(result);
}
Console.ReadLine();
}
public CocktailIngredient AddCocktailIngredient(CocktailIngredient ci)
{
using (var connection = new SqlConnection(Settings.GetConnectionString()))
{
var p = new DynamicParameters();
p.Add("CocktailId", ci.CocktailId);
p.Add("IngredientId", ci.IngredientId);
p.Add("Amount", ci.Amount);
p.Add("CocktailIngredientId", dbType: DbType.Int32, direction: ParameterDirection.Output);
connection.Execute("CocktailIngredientAdd", p, commandType: CommandType.StoredProcedure);
ci.CocktailIngredientId = p.Get<int>("CocktailIngredientId");
return ci;
}
}
public Cocktail AddCocktail(Cocktail cocktial)
{
using (var connection = new SqlConnection(Settings.GetConnectionString()))
{
var p = new DynamicParameters();
p.Add("Name", cocktial.Name);
p.Add("ImgUrl", cocktial.ImgUrl);
p.Add("CocktailId", dbType: DbType.Int32, direction: ParameterDirection.Output);
connection.Execute("CocktailAdd", p, commandType: CommandType.StoredProcedure);
cocktial.CocktailId = p.Get<int>("CocktailId");
}
return cocktial;
}
public string RunSp(string strProcName, ref Dapper.DynamicParameters pars)
{
using (SqlConnection con = new SqlConnection(_conString))
{
try
{
con.Query(strProcName, pars, null, true, null, System.Data.CommandType.StoredProcedure);
if (pars.Get <string>("err_ret") != "")
{
this.CurrentUser.AddMessage(pars.Get <string>("err_ret"));
return(pars.Get <string>("err_ret"));
}
else
{
return("1");
}
}
catch (Exception e)
{
log_error(e, strProcName, pars);
return(e.Message);
}
}
}
public void CreateBlogPostDB(BlogPost blogPost)
{
using (var cn = new SqlConnection(Settings.ConnectionString))
{
var p = new DynamicParameters();
p.Add("@UserID", blogPost.User.UserID);
p.Add("@Title", blogPost.Title);
p.Add("@Body", blogPost.Mce.Body);
p.Add("@CategoryID", blogPost.Category.CategoryID);
p.Add("@PostDate", DateTime.Today);
p.Add("@Status", blogPost.Status);
p.Add("@BlogPostID", DbType.Int32, direction: ParameterDirection.Output);
cn.Execute("BlogPostInsert", p, commandType: CommandType.StoredProcedure);
var blogPostID = p.Get<int>("BlogPostID");
_hashTags = GetAllHashTags();
foreach (var hashTag in blogPost.HashTags) //new user hashtags
{
var ht = _hashTags.FirstOrDefault(h => h.HashTagName == hashTag.HashTagName);
var hashTagID = 0;
if (ht == null)
{
var p2 = new DynamicParameters();
p2.Add("@HashTagName", hashTag.HashTagName);
p2.Add("@HashTagID", DbType.Int32, direction: ParameterDirection.Output);
cn.Execute("HashTagInsert", p2, commandType: CommandType.StoredProcedure);
hashTagID = p2.Get<int>("HashTagID");
_hashTags.Add(new HashTag() { HashTagID = hashTagID, HashTagName = hashTag.HashTagName });
}
else
{
hashTagID = ht.HashTagID;
}
var p4 = new DynamicParameters();
p4.Add("@HashTagID", hashTagID);
p4.Add("@BlogPostID", blogPostID);
cn.Execute("HashTagPostInsert", p4, commandType: CommandType.StoredProcedure);
}
}
}
public void CreateAssignment(Assignment assignment)
{
using (SqlConnection cn = new SqlConnection(Config.GetConnectionString()))
{
var p = new DynamicParameters();
p.Add("@ClassId", assignment.ClassId);
p.Add("@Name", assignment.Name);
p.Add("@PossiblePoints", assignment.PossiblePoints);
p.Add("@DueDate", assignment.DueDate);
p.Add("@Description", assignment.Description);
p.Add("@AssignmentId", dbType: DbType.Int32, direction: ParameterDirection.Output);
cn.Execute("AssignmentInsert", p, commandType: CommandType.StoredProcedure);
assignment.AssignmentId = p.Get<int>("@AssignmentId");
}
}
public Team AddTeam(Team team)
{
using (SqlConnection cn = new SqlConnection(Settings.ConnectionString))
{
var pnsm = new DynamicParameters();
pnsm.Add("@TeamName", team.TeamName);
pnsm.Add("@Manager", team.Manager);
pnsm.Add("@LeagueID", team.League.LeagueID);
pnsm.Add("@TeamID", DbType.Int32, direction: ParameterDirection.Output);
cn.Execute("InsertTeams", pnsm, commandType: CommandType.StoredProcedure);
var teamID = pnsm.Get<int>("TeamID");
return GetTeamByID(teamID);
}
}
//获取用户信用档案
private void GetBorrowerData()
{
userCreditInfo = bll.WXGetUserCreditInfo(model.UserId.Value);
//特殊处理
Guid tempBorrowUserId = Guid.Parse("73810E55-E2D4-4F40-8FF9-F0A23F909A75");
if (borrowerUserInfo.Id == tempBorrowUserId)
{
Guid tempProjectId = Guid.Parse("EA7A69C1-38B4-4073-A69D-CFBE2944B4E3");
ProjectDetailInfo tempProjectModel = bll.GetProjectDetailInfo(tempProjectId);
userCreditInfo.DueOutPAndI = userCreditInfo.DueOutPAndI - ((tempProjectModel.TotalAmount ?? 0) + (tempProjectModel.TotalInterest ?? 0) - (tempProjectModel.RefundInterest ?? 0) - (tempProjectModel.RefundCaptital ?? 0));
//LogHelper.WriteLog("待还本息特殊处理", "", tempProjectModel.TotalAmount.Value.ToString());
// SysLogHelper.WriteTraceLog("待还本息特殊处理", tempProjectModel.TotalAmount.Value.ToString());
}
string sql = @" SELECT @prepaymentTime=count(CASE WHEN IsRepayAdvance=1 THEN 1 ELSE NULL END ),
@sumTime=count(CASE WHEN Status=6 THEN 1 ELSE NULL END )
FROM Project (NOLOCK) where Type=6 and UserId=@userId ;
SELECT @OverdueCount=COUNT(0) FROM (SELECT ProjectId,periods FROM dbo.OverDueRecord with(nolock) WHERE PublisherUserId=@userId and isnull(IsHide,0)=0 GROUP BY ProjectId,periods)t;
select @OverdueAdvanceTime=COUNT(0) from (SELECT DISTINCT m_Period,m_ProjectId FROM dbo.t_AdvanceDetail(NOLOCK) where m_BorrowUserid=@UserId ) A;
SELECT @TotalOverdue=(ISNULL(SUM(ActualCost),0)+ISNULL(SUM(ActualInterest),0)) FROM dbo.OverDueRecord(NOLOCK) WHERE PublisherUserId=@userId AND IsBorrow=0 and isnull(IsHide,0)=0 ";
var dyParams = new Dapper.DynamicParameters();
dyParams.Add("@userId", model.UserId);
dyParams.Add("@prepaymentTime", 0, DbType.Int32, ParameterDirection.Output, 20);
dyParams.Add("@sumTime", 0, DbType.Int32, ParameterDirection.Output, 20);
dyParams.Add("@OverdueCount", 0, DbType.Int32, ParameterDirection.Output, 20);
dyParams.Add("@OverdueAdvanceTime", 0, DbType.Int32, ParameterDirection.Output, 20);
dyParams.Add("@TotalOverdue", 0, DbType.Decimal, ParameterDirection.Output, 20);
PublicConn.ExecuteTD(PublicConn.DBWriteType.Read, sql, ref dyParams);
PrepaymentTime = dyParams.Get <int>("@prepaymentTime");
int sumTime = dyParams.Get <int>("@sumTime");
OnTimepayTime = sumTime - PrepaymentTime;
overdueNum = dyParams.Get <int>("@OverdueCount");
OverdueAdvanceTime = dyParams.Get <int>("@OverdueAdvanceTime");
TotalOverdue = dyParams.Get <decimal>("@TotalOverdue");
//垫付次数大于逾期次数，显示逾期次数，否则是逾期垫付次数。
if (overdueNum < OverdueAdvanceTime)
{
OverdueAdvanceTime = overdueNum;
}
#region 会员等级 2016-04-16
string strSQL = @"declare @PreNetWorth numeric(18, 2)=0
SELECT @PreNetWorth=ISNULL(PreNetWorth,0) FROM dbo.MUserVipInfo WITH(NOLOCK) WHERE UserId=@UserId
SELECT Level FROM dbo.MVipLevel WITH(NOLOCK) WHERE MinNetWorth<=@PreNetWorth AND MaxNetWorth>@PreNetWorth ";
string level = string.Empty;
DynamicParameters whereParams = new DynamicParameters();
whereParams.Add("@UserId", this.model.UserId);
level = PublicConn.QueryVipSingle <string>(strSQL, ref whereParams);
if (DateTime.Now < DateTime.Parse("2018-01-01 02:00:00") && level == "9")
{
level = "8";
}
VipLevel = "V" + (level ?? "1");//会员等级
#endregion
string sqlText = @"select a.FullName,a.image from UserInEnterpriseProject_ZC u left join AssureOrganization a
on u.EnterpriseUserId=a.UserId where ProjectId=@projectId";
var para = new DynamicParameters();
para.Add("@projectId", model.Id);
assureModel = PublicConn.QuerySingle <AssureOrganizationInfo>(sqlText, ref para);
}
public List <string> Save(UserAccess ua)
{
List <InspType> inspTypes = InspType.GetCachedInspectionTypes();
List <string> errors = this.Validate(ua.current_access, inspTypes);
if (errors.Count > 0)
{
return(errors);
}
int IRID = this.AddIRID();
string InitialComment = $"Inspection Request scheduled for {this.SchecDateTime.Date.ToShortDateString()}.";
var dbArgs = new Dapper.DynamicParameters();
dbArgs.Add("@PermitNo", this.PermitNo);
dbArgs.Add("@InspCd", this.InspectionCd);
dbArgs.Add("@SelectedDate", this.SchecDateTime.Date);
dbArgs.Add("@Username", ua.user_name.Trim(), dbType: DbType.String, size: 7);
dbArgs.Add("@DisplayName", ua.display_name);
dbArgs.Add("@IRID", (IRID == -1) ? null : IRID.ToString());
dbArgs.Add("@InitialComment", InitialComment);
dbArgs.Add("@Comment", Comment);
dbArgs.Add("@SavedInspectionID", -1, dbType: DbType.Int32, direction: ParameterDirection.Output, size: 8);
string sql = $@"
USE WATSC;
WITH last_completed_inspection_of_this_type AS (
SELECT TOP 1
BaseId,
inspreqid,
ResultADC
FROM bpINS_REQUEST
where inspectionCode = @InspCd
AND PermitNo = @PermitNo
order by inspreqid desc
)
INSERT INTO bpINS_REQUEST
(PermitNo,
InspectionCode,
Inspector,
SchecDateTime,
ReqDateTime,
BaseId,
ReceivedBy,
PrivProvIRId,
ReinspectionId)
SELECT TOP 1
@PermitNo,
@InspCd,
CASE WHEN @IRId IS NOT NULL THEN 'PPI' ELSE NULL END inspector,
CAST(@SelectedDate AS DATE),
GETDATE(),
B.BaseId,
@Username,
@IRID,
CASE WHEN L.ResultADC IN ('D','N') THEN L.InspReqID ELSE NULL END ReinspectionId
FROM bpBASE_PERMIT B
LEFT OUTER JOIN bpMASTER_PERMIT M ON M.BaseID = B.BaseID
LEFT OUTER JOIN bpASSOC_PERMIT A ON B.BaseID = A.BaseID
LEFT OUTER JOIN last_completed_inspection_of_this_type L ON L.BaseId = B.BaseID
WHERE (A.PermitNo = @PermitNo OR M.PermitNo = @PermitNo)
SET @SavedInspectionID = SCOPE_IDENTITY();
EXEC add_inspection_comment @DisplayName, @SavedInspectionID, @InitialComment, @Comment;";
try
{
//bool isFinal = (from it in inspTypes
// where it.InspCd == this.InspectionCd
// select it.Final).First();
//Console.WriteLine("isFinal:", isFinal);
var i = Constants.Exec_Query(sql, dbArgs);
if (i > -1)
{
int SavedInspectionId = dbArgs.Get <int>("@SavedInspectionID");
string inspDesc = (from it in inspTypes
where it.InspCd == this.InspectionCd
select it.InsDesc).First();
errors.Add(inspDesc + " inspection has been scheduled for permit #" + this.PermitNo + ", on " + this.SchecDateTime.ToShortDateString() + ". This was saved with request id " + SavedInspectionId + ".");
}
else
{
errors.Add("No Record Saved, Please Try again. Contact the Building department if issues persist.");
}
}
catch (Exception ex)
{
Constants.Log(ex, sql);
errors.Add("No Record Saved, Please Try again. Contact the Building department if issues persist.");
}
return(errors);
}
/// <summary>
/// <para>Inserts a row into the database, using ONLY the properties defined by TEntity</para>
/// <para>By default inserts into the table matching the class name</para>
/// <para>-Table name can be overridden by adding an attribute on your class [Table("YourTableName")]</para>
/// <para>Insert filters out Id column and any columns with the [Key] attribute</para>
/// <para>Properties marked with attribute [Editable(false)] and complex types are ignored</para>
/// <para>Supports transaction and command timeout</para>
/// <para>Returns the ID (primary key) of the newly inserted record if it is identity using the defined type, otherwise null</para>
/// </summary>
/// <param name="connection"></param>
/// <param name="entityToInsert"></param>
/// <param name="transaction"></param>
/// <param name="commandTimeout"></param>
/// <returns>The ID (primary key) of the newly inserted record if it is identity using the defined type, otherwise null</returns>
public static async Task <TKey> InsertAsync <TKey, TEntity>(this IDbConnection connection, TEntity entityToInsert, IDbTransaction transaction = null, int?commandTimeout = null)
{
var idProps = GetIdProperties(entityToInsert).ToList();
if (!idProps.Any())
{
throw new ArgumentException("Insert<T> only supports an entity with a [Key] or Id property");
}
var keyHasPredefinedValue = false;
var baseType = typeof(TKey);
var underlyingType = Nullable.GetUnderlyingType(baseType);
var keytype = underlyingType ?? baseType;
if (keytype != typeof(int) && keytype != typeof(uint) && keytype != typeof(long) && keytype != typeof(ulong) && keytype != typeof(short) && keytype != typeof(ushort) && keytype != typeof(Guid) && keytype != typeof(string))
{
throw new Exception("Invalid return type");
}
var name = GetTableName(entityToInsert);
var sb = new StringBuilder();
sb.AppendFormat("insert into {0}", name);
sb.Append(" (");
BuildInsertParameters <TEntity>(sb);
sb.Append(") ");
sb.Append("values");
sb.Append(" (");
BuildInsertValues <TEntity>(sb);
sb.Append(")");
if (keytype == typeof(Guid))
{
var guidvalue = (Guid)idProps.First().GetValue(entityToInsert, null);
if (guidvalue == Guid.Empty)
{
var newguid = SequentialGuid();
idProps.First().SetValue(entityToInsert, newguid, null);
}
else
{
keyHasPredefinedValue = true;
}
}
if ((keytype == typeof(int) || keytype == typeof(long)) && Convert.ToInt64(idProps.First().GetValue(entityToInsert, null)) == 0)
{
if (_dialect != Dialect.Oracle)
{
sb.Append(";" + _getIdentitySql);
}
else
{
sb.AppendFormat(_getIdentitySql, GetColumnName(idProps.First()));
}
}
else
{
keyHasPredefinedValue = true;
}
if (Debugger.IsAttached)
{
Trace.WriteLine(String.Format("Insert: {0}", sb));
}
if (_dialect == Dialect.Oracle)
{
if (keytype == typeof(Guid))
{
throw new Exception("Invalid return type");
}
var param = new DynamicParameters(entityToInsert);
if (keyHasPredefinedValue)
{
var xr = await connection.ExecuteAsync(sb.ToString(), param, transaction, commandTimeout);
return((TKey)idProps.First().GetValue(entityToInsert, null));
}
else
{
param.Add(GetColumnName(idProps.First()), null, DbType.Int64, ParameterDirection.ReturnValue);
var xr = await connection.ExecuteAsync(sb.ToString(), param, transaction, commandTimeout);
var q = param.Get <dynamic>(GetColumnName(idProps.First()));
return((TKey)q);
}
}
else
{
if (keytype == typeof(Guid) || keyHasPredefinedValue)
{
await connection.ExecuteAsync(sb.ToString(), entityToInsert, transaction, commandTimeout);
return((TKey)idProps.First().GetValue(entityToInsert, null));
}
var r = await connection.QueryAsync(sb.ToString(), entityToInsert, transaction, commandTimeout);
return((TKey)r.First().id);
}
}
