public static void SaveEmail(string to, string subject, string body) { string sql = @" INSERT INTO EmailList (EmailTo, EmailSubject, EmailBody) VALUES (@To, @Subject, @Body);"; try { var dbArgs = new Dapper.DynamicParameters(); dbArgs.Add("@To", to); dbArgs.Add("@Subject", subject); dbArgs.Add("@Body", body); var cs = ConfigurationManager.ConnectionStrings["Log"].ConnectionString; using (IDbConnection db = new SqlConnection(cs)) { db.Execute(sql, dbArgs); } } catch (Exception ex) { // if we fail to save an email to the production server, // let's save it to the backup DB server. new ErrorLog(ex, sql); } }
public SearchListViewModel QuerySearchList(SearchInfoViewModel searchInfo) { //組立SQL字串並連接資料庫 #region 參數告宣 SearchListViewModel result = new SearchListViewModel(); #endregion #region 流程 StringBuilder _sqlStr = new StringBuilder(); StringBuilder _sqlCountStr = new StringBuilder(); _sqlCountStr.Append("select count(*) from appraisal"); _sqlStr.Append("select * from appraisal ORDER BY " + (searchInfo.iSortCol_0 + 1).ToString() + " " + searchInfo.sSortDir_0 + " OFFSET @iDisplayStart ROWS FETCH NEXT @iDisplayLength ROWS ONLY"); _sqlParams = new Dapper.DynamicParameters(); _sqlParams.Add("iDisplayStart", searchInfo.iDisplayStart); _sqlParams.Add("iDisplayLength", searchInfo.iDisplayLength); result.data = new List <SearchItemViewModel>(); result.draw = (searchInfo.sEcho + 1); using (var cn = new SqlConnection(_dbConnPPP)) //連接資料庫 { cn.Open(); result.data = cn.Query <SearchItemViewModel>(_sqlStr.ToString(), _sqlParams).ToList(); result.recordsTotal = cn.Query <int>(_sqlCountStr.ToString()).First(); result.recordsFiltered = result.recordsTotal; } return(result); #endregion }
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 static void SaveEmail(string to, string subject, string body) { string sql = @" INSERT INTO EmailList (EmailTo, EmailSubject, EmailBody) VALUES (@To, @Subject, @Body);"; try { var dbArgs = new Dapper.DynamicParameters(); dbArgs.Add("@To", to); dbArgs.Add("@Subject", subject); dbArgs.Add("@Body", body); using (IDbConnection db = new SqlConnection(Program.Get_ConnStr(Program.LOG))) { db.Execute(sql, dbArgs); } } catch (Exception ex) { new ErrorLog(ex, sql); } }
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; }
internal void DeleteProductList(ProductModel model) { //組立SQL字串並連接資料庫 StringBuilder _sqlStr = new StringBuilder(); _sqlStr.Append("UPDATE Product SET ModifiedDate = @ModifiedDate ,Status = @Status WHERE ID = @ID "); _sqlParams = new Dapper.DynamicParameters(); _sqlParams.Add("ID", model.ID); _sqlParams.Add("ModifiedDate", model.ModifiedDate); _sqlParams.Add("Status", model.Status); try { using (var cn = new SqlConnection(_dbConnPPP))//連接資料庫 { cn.Open(); var ExecResult = cn.Execute(_sqlStr.ToString(), _sqlParams); } } catch (Exception ex) { throw ex; } }
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($@"{GetSort(sort)} OFFSET @SKIP ROWS FETCH NEXT @PAGESIZE ROWS ONLY", dp); }
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); }
/// <summary> /// Given a model and a list of column names, get the parameters used for a dynamic query in dapper /// </summary> /// <param name="model"></param> /// <param name="columnNames"></param> /// <returns></returns> private Dapper.DynamicParameters GetQueryParams(ModelType model, IEnumerable <string> columnNames) { var queryParams = new Dapper.DynamicParameters(); var type = model.GetType(); foreach (var columnName in columnNames) { var propertyName = columnName.Substring(0, 1).ToUpper() + columnName.Substring(1); System.Reflection.FieldInfo field; System.Reflection.PropertyInfo property; if ((field = type.GetField(propertyName)) != null) { queryParams.Add(columnName, field.GetValue(model)); } else if ((property = type.GetProperty(propertyName)) != null) { queryParams.Add(columnName, property.GetValue(model)); } else { throw new Exception($"Unable to find property '{propertyName}' on type {type.FullName}"); } } return(queryParams); }
public int SaveTagging(string record_entity, int record_pid, string o51ids, int only_one_o53id = 0) { record_entity = record_entity.Substring(0, 3); if (only_one_o53id == 0) { _db.RunSql("DELETE FROM o52TagBinding WHERE o52RecordPid=@pid AND o52RecordEntity=@entity", new { pid = record_pid, entity = record_entity }); if (String.IsNullOrEmpty(o51ids) == false) { _db.RunSql("INSERT INTO o52TagBinding(o52RecordEntity,o52RecordPid,o51ID) SELECT @entity,@pid,o51ID FROM o51Tag WHERE o51ID IN (" + o51ids + ")", new { pid = record_pid, entity = record_entity }); } } else { _db.RunSql("DELETE FROM o52TagBinding WHERE o52RecordPid=@pid AND o52RecordEntity=@entity AND o51ID IN (select o51ID FROM o51Tag WHERE o53ID=@o53id)", new { pid = record_pid, entity = record_entity, o53id = only_one_o53id }); if (String.IsNullOrEmpty(o51ids) == false) { _db.RunSql("INSERT INTO o52TagBinding(o52RecordEntity,o52RecordPid,o51ID) SELECT @entity,@pid,o51ID FROM o51Tag WHERE o53ID=@o53id AND o51ID IN (" + o51ids + ")", new { pid = record_pid, entity = record_entity, o53id = only_one_o53id }); } } var pars = new Dapper.DynamicParameters(); pars.Add("userid", _db.CurrentUser.pid); pars.Add("record_entity", record_entity, System.Data.DbType.String); pars.Add("record_pid", record_pid, System.Data.DbType.Int32); pars.Add("err_ret", "", System.Data.DbType.String, System.Data.ParameterDirection.Output); _db.RunSp("o51_tagging_after_save", ref pars); return(1); }
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; }
/// <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 })); } }
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 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 DynamicParameters AddUserParameters(User user) { DynamicParameters parameters = new DynamicParameters(); parameters.Add("@Email", user.Email); parameters.Add("@Password", user.Password); return parameters; }
// 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 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; }
public UserView Validate(string email, string password) { string query = String.Empty; if (password.IsYammer()) { query = "ValidateYammerUser"; } else { query = "ValidateFormsUser"; } try { using (SqlConnection conn = SqlHelper.GetOpenConnection()) { var par = new Dapper.DynamicParameters(); par.Add("@Email", email); par.Add("@Password", password); var user = conn.Query <UserView>(query, par, commandType: CommandType.StoredProcedure).FirstOrDefault(); log.Info("Validated user '" + user.DisplayName.Trim() + "' with email: '" + email + "'. Authentication method: '" + query + "'", false); return(user); } } catch (Exception ex) { log.Error("Validation of user: '******' failed", ex, false); throw; } }
private bool ValidateUpdate(Update update, out string validation) { var valid = true; validation = "Validated Successfully"; //validate the project name if (update.TableName.ToLower().Equals("project") && update.ColumnName.ToLower().Equals("name")) { using (SqlConnection conn = SqlHelper.GetOpenConnection()) { var par = new Dapper.DynamicParameters(); par.Add("@ProjectId", update.ProjectId); par.Add("@ProjectName", update.Value); valid = conn.Query <bool>("ValidateProjectName", par, commandType: CommandType.StoredProcedure).FirstOrDefault(); if (valid == false) { validation = "There is already a project with same name and organization"; } } } return(valid); }
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); }
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 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 int Update(ASSETKKF_MODEL.Data.Mssql.Mcis.msWorkStationGrp_Spare d, string userID = "", SqlTransaction transac = null) { var param = new Dapper.DynamicParameters(); param.Add("@WorkStationGrpCd", d.WorkStationGrpCd.GetValue()); param.Add("@WorkStationGrpCd_Spare", d.WorkStationGrpCd_Spare.GetValue()); param.Add("@User_Id", d.User_Id.GetValue()); param.Add("@User_date", d.User_date); string cmd = ""; /* * cmd = UPDATE mcis.dbo.msWorkStationGrp_Spare "+ * "SET WorkStationGrpCd = @.WorkStationGrpCd "+ * " , WorkStationGrpCd_Spare = @.WorkStationGrpCd_Spare "+ * " , User_Id = @.User_Id "+ * " , User_date = @.User_date "+ * "WHERE WorkStationGrpCd = @.WorkStationGrpCd "+ * " AND WorkStationGrpCd_Spare = @.WorkStationGrpCd_Spare "+ * " AND User_Id = @.User_Id "+ * " AND User_date = @.User_date "+ * " "; */ var res = ExecuteNonQuery(transac, cmd, param); return(res); }
public IEnumerable <BO.j72TheGridTemplate> GetList(string strEntity, int intJ03ID, string strMasterEntity) { string s; var p = new Dapper.DynamicParameters(); p.Add("j03id", intJ03ID); p.Add("j04id", _mother.CurrentUser.j04ID); if (strEntity == null && strMasterEntity == null) { s = string.Format("SELECT a.*,{0} FROM j72TheGridTemplate a WHERE a.j03ID=@j03id OR a.j72IsPublic=1 OR a.j72ID IN (select j72ID FROM j74TheGridReceiver WHERE j04ID=@j04id)", _db.GetSQL1_Ocas("j72")); return(_db.GetList <BO.j72TheGridTemplate>(s + " ORDER BY a.j72IsSystem DESC", p)); } p.Add("entity", strEntity); if (string.IsNullOrEmpty(strMasterEntity) == true) { s = string.Format("SELECT a.*,{0} FROM j72TheGridTemplate a WHERE a.j72Entity=@entity AND a.j72MasterEntity IS NULL", _db.GetSQL1_Ocas("j72")); } else { s = string.Format("SELECT a.*,{0} FROM j72TheGridTemplate a WHERE a.j72Entity=@entity AND a.j72MasterEntity = @masterentity", _db.GetSQL1_Ocas("j72")); p.Add("masterentity", strMasterEntity); } s += " AND (a.j03ID=@j03id OR a.j72IsPublic=1 OR a.j72ID IN (select j72ID FROM j74TheGridReceiver WHERE j04ID=@j04id))"; return(_db.GetList <BO.j72TheGridTemplate>(s + " ORDER BY a.j72IsSystem DESC", p)); }
public void EditGradebook(GradeBookView.StudentGrade gbUpdate) { //this approach makes two calls to the database there is probably a way to do this all in one SQL Stored Procedure using (var cn = new SqlConnection(Config.GetConnectionString())) { var p = new DynamicParameters(); p.Add("@RosterId", gbUpdate.RosterId); p.Add("@AssignmentId", gbUpdate.AssignmentId); p.Add("@Points",gbUpdate.Points); p.Add("@Score", gbUpdate.Score); if (//checks to see if grade exists cn.Query<AssignmentGrade>("AssignmentGrade_GetByRosterIdAndAssignmentId", p, commandType: CommandType.StoredProcedure) .Any()) { cn.Execute("AssignmentGrade_UpdateScore", p, commandType: CommandType.StoredProcedure); //updates existing grade } else { cn.Execute("AssignmentGrade_Insert", p, commandType: CommandType.StoredProcedure); //creates new grade } } }
public IEnumerable <Template> GetTemplates(string section, string format) { try { using (SqlConnection conn = SqlHelper.GetOpenConnection()) { var par = new Dapper.DynamicParameters(); par.Add("@Section", section); var query = @"SELECT * FROM Template WHERE Section = @Section"; if (!string.IsNullOrEmpty(format)) { query = " AND Format = @Format"; par.Add("@Format", format); } return(conn.Query <Template>(query, par)); } } catch (Exception ex) { log.Error("Error while quering templates", ex); throw; } }
public bool InsertCommonData(CommonData cd) { int res = 0; try { var sql = "[dbo].[InsertCommonData]"; DynamicParameters param = new DynamicParameters(); param.Add("type", cd.Type); param.Add("name", cd.Name); param.Add("value", cd.Value); param.Add("descrption", cd.Descrption); cn.Open(); res = cn.Execute(sql, param, commandType: CommandType.StoredProcedure); cn.Close(); } catch (Exception ex) { } finally { cn.Close(); } return Convert.ToBoolean(res); }
public int ChangePassword(PasswordChange obj) { try { using (SqlConnection conn = SqlHelper.GetOpenConnection()) { var par = new Dapper.DynamicParameters(); par.Add("@Name", obj.Name); par.Add("@Password", obj.Password); par.Add("@ChangePasswordRequest", obj.Key); int result = conn.Execute("ChangePassword", par, commandType: CommandType.StoredProcedure); if (result == -1) { string msg = string.Format("ChangePassword with key: '{0}' failed. The request has expired!", obj.Key); log.Warning(msg); //throw new Exception(msg); } return(result); } } catch (Exception ex) { log.Error("ChangePassword with key: '" + obj.Key + "' failed", 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($@"Dropbox-API-Arg: {{""path"":""{targetpath}/{filename}"",""mode"":""overwrite""}}"); wc.UploadData("https://content.dropboxapi.com/2-beta-2/files/upload", bytes); } }
private void WriteLog(string type, string message, bool identity) { int userId = 0; if (identity) { if (IdentityContext.IsInitialized) { if (IdentityContext.Current != null) { if (IdentityContext.Current.User != null) { userId = IdentityContext.Current.User.Id; } } } } var par = new Dapper.DynamicParameters(); par.Add("@UserId", userId); par.Add("@Type", type); par.Add("@Message", message); using (SqlConnection conn = SqlHelper.GetOpenConnection()) { conn.Execute("INSERT INTO [Log] (UserId, [Type], [Message]) VALUES (@UserId, @Type, @Message)", par); } }
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 IEnumerable <UserView> GetProgramAdministrators(int programTypeId, int projectRoleId, int organizationId) { using (SqlConnection conn = SqlHelper.GetOpenConnection()) { var par = new Dapper.DynamicParameters(); par.Add("@ProgramTypeId", programTypeId); par.Add("@ProjectRoleId", projectRoleId); par.Add("@organizationId ", organizationId); var query = @""; if (programTypeId > 1) { query = @"SELECT uv.* FROM UserView uv INNER JOIN ProgramRoleAdministration pra ON uv.Id = pra.UserId WHERE pra.ProgramTypeId = @ProgramTypeId AND pra.ProjectRoleId = @ProjectRoleId AND pra.OrganizationId = @organizationId " ; } else { query = @"SELECT uv.* FROM UserView uv INNER JOIN ProgramRoleAdministration pra ON uv.Id = pra.UserId WHERE pra.ProgramTypeId = @ProgramTypeId AND pra.ProjectRoleId = @ProjectRoleId" ; } return(conn.Query <UserView>(query, par)); } }
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 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 bool AspNetUser_Update(ApplicationUser user) { var p = new DynamicParameters(); user.Member = true; p.Add("Id", user.Id); p.Add("Member", user.Member); return _dbConnection.Connection.Execute("AspNetUsers_Update", p, commandType: CommandType.StoredProcedure) > 0; }
public void AddNewPostTag(int TagId, int PostId) { var p = new DynamicParameters(); p.Add("TagId", TagId); p.Add("PostId", PostId); _cn.Execute("AddNewPostTags", p, commandType: CommandType.StoredProcedure); }
private void run_p41_after_save(int p41id) { var pars = new Dapper.DynamicParameters(); pars.Add("userid", _db.CurrentUser.pid); pars.Add("pid", p41id, System.Data.DbType.Int32); pars.Add("err_ret", "", System.Data.DbType.String, System.Data.ParameterDirection.Output); _db.RunSp("p41_after_save", ref pars); }
public DynamicParameters AddReviewParameters(Review review) { DynamicParameters parameters = new DynamicParameters(); parameters.Add("@ReviewText", review.ReviewText); parameters.Add("@IsVerified", review.IsVerified); parameters.Add("@BootcampID", review.BootcampID); parameters.Add("@UserID", review.UserID); return parameters; }
public void RecoveryPlanInTask(int p41id) { var pars = new Dapper.DynamicParameters(); pars.Add("userid", _db.CurrentUser.pid); pars.Add("pid", p41id, System.Data.DbType.Int32); pars.Add("err_ret", "", System.Data.DbType.String, System.Data.ParameterDirection.Output); _db.RunSp("p41_recovery_plan", ref pars); }
public void Insert(CardModifier cardModifier) { using (var connection = new SqlConnection(ConfigurationSettings.GetConnectionString())) { DynamicParameters p = new DynamicParameters(); p.Add("@CardID", cardModifier.CardID); p.Add("@ModifierTypeID", cardModifier.ModifierTypeID); } }
public DynamicParameters AddBootcampSessionParameters(BootcampSession session) { DynamicParameters parameters = new DynamicParameters(); parameters.Add("@LocationID", session.LocationID); parameters.Add("@BootcampID", session.BootcampID); parameters.Add("@TechnologyID", session.TechnologyID); parameters.Add("@StartDate", session.StartDate); parameters.Add("@EndDate", session.EndDate); return parameters; }
public void DeleteStudentFromCourse(int studentId, int courseId) { using (SqlConnection cn = new SqlConnection(Settings.GetConnectionString())) { var p = new DynamicParameters(); p.Add("@StudentId", studentId); p.Add("@CourseId", courseId); cn.Execute("spRemoveStudentFromCourse", p, commandType: CommandType.StoredProcedure); } }
public void UpdateDepartment(Departments department) { using (SqlConnection cn = new SqlConnection(Settings.ConnectionString)) { var p = new DynamicParameters(); p.Add("dptName", department.DepartmentName); p.Add("dptId", department.DepartmentID); cn.Query("UpdateDepartment", p, commandType: CommandType.StoredProcedure); } }
private static void InsertRow(SqlConnection conn, string name, string surname, string ssn, DateTime birthDate) { var p = new DynamicParameters(); p.Add("@firstName", name, DbType.String, ParameterDirection.Input, 100); p.Add("@lastName", surname, DbType.String, ParameterDirection.Input, 100); p.Add("@ssn", ssn, DbType.String, ParameterDirection.Input, 100); p.Add("@birthDate", birthDate, DbType.DateTime2, ParameterDirection.Input, 7); conn.Execute("insert into dbo.Customers (first_name, last_name, ssn, birth_date) values (@firstName, @lastName, @ssn, @birthDate)", p); }
public DynamicParameters AddLinkParameters(Link link) { DynamicParameters parameters = new DynamicParameters(); parameters.Add("@URL", link.URL); parameters.Add("@LinkText", link.LinkText); parameters.Add("@SiteID", link.SiteID); parameters.Add("@Date", link.Date); parameters.Add("@BootcampID", link.BootcampID); return parameters; }
public void UpdateProjectFollowUpActivityTotal(int projectFollowUpId, decimal total) { using (SqlConnection conn = SqlHelper.GetOpenConnection()) { var par = new Dapper.DynamicParameters(); par.Add("@ActivityTotal", total); par.Add("@Id", projectFollowUpId); conn.Execute("UPDATE ProjectFollowUp SET ActivityTotal = @ActivityTotal WHERE Id = @Id", par); } }
/// <summary> /// 获取用户邀请注册人的数量(被邀请的人必须实行认证且绑定银行卡 2015-4-30号以后注册) /// </summary> /// <param name="extenderkey"></param> /// <param name="date"></param> /// <returns></returns> private int GetInvitedUserCount(string extenderkey, DateTime date) { string sql = "select count(0) from UserBasicInfo with(nolock) where ExtenderKey=@key and IsValidateIdentity = 1 and BankAccountNo is not null and AddDate > @date"; var para = new Dapper.DynamicParameters(); para.Add("@key", extenderkey); para.Add("@date", date); return(TuanDai.DB.TuanDaiDB.QueryFirstOrDefault <int>(TdConfig.DBRead, sql, ref para)); }
/// <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; } }
private Customer AddOrUpdateCustomer(Customer toAddOrUpdate) { Customer toReturn = null; try { using (var msSql = DBController.GetDBConnection()) { DynamicParameters p = new DynamicParameters(); p.Add("CustomerID", toAddOrUpdate.CustomerID); p.Add("FirstName", toAddOrUpdate.FirstName); p.Add("LastName", toAddOrUpdate.LastName); p.Add("MiddleName", toAddOrUpdate.MiddleName); p.Add("EmailAddress", toAddOrUpdate.EmailAddress); p.Add("Street", toAddOrUpdate.Street); p.Add("Housenumber", toAddOrUpdate.Housenumber); p.Add("PostalCode", toAddOrUpdate.PostalCode); p.Add("City", toAddOrUpdate.City); toReturn = msSql.Query<Customer>("up_AddOrUpdateCustomer", param: p, commandType: CommandType.StoredProcedure).FirstOrDefault(); } } catch (Exception ex) { logger.Error("Error retrieving DB customer", ex); } return toReturn; }
public void CreateUser(string email, string name, out string password, out RegisterUserStatus status) { password = string.Empty; using (SqlConnection conn = SqlHelper.GetOpenConnection()) { //check if user already exists int id = conn.Query <int>("SELECT Id FROM [User] WHERE Email = @Email", new { Email = email.Trim() }).FirstOrDefault(); //if user does not exists if (id > 0) { status = RegisterUserStatus.AlreadyExists; log.Warning("Cannot create user, email: '" + email.Trim() + "' already exists", false); //throw new Exception("Cannot create user, a user with email: '" + email.Trim() + "' already exists"); } else { //check if the email is external var isInternalQuery = @" SELECT TOP (1) CASE WHEN o.Domain IS NULL THEN 0 ELSE 1 END as [OrganizationState] FROM Organization o WHERE o.Domain = SUBSTRING(@Email, CHARINDEX('@', @Email) + 1, len(@Email))"; int isInternal = conn.Query <int>(isInternalQuery, new { Email = email.Trim() }).FirstOrDefault(); if (isInternal > 0) { try { var par = new Dapper.DynamicParameters(); par.Add("@Name", name.Trim()); par.Add("@Email", email.Trim()); password = conn.Query <string>("[CreateUser]", par, commandType: CommandType.StoredProcedure).FirstOrDefault(); status = RegisterUserStatus.Ok; log.Info("Created user: '******' with email: '" + email.Trim() + "'", false); } catch (Exception ex) { log.Error("Cannot create user with email: '" + email.Trim() + "' Message: " + ex.Message, false); status = RegisterUserStatus.Error; } } else { status = RegisterUserStatus.NoDomainMatch; log.Warning("Cannot create user, the email domain does not match any domain in the database"); } } } }
public int UpgradeSharedPyramid(SharingContext context) { var executeSqlString = @"spUpgradeSharedPyramid"; using (var database = this.databaseFactory.GenerateDatabase(isWriteOnly: true)) { var parameters = new Dapper.DynamicParameters(); parameters.Add("@sharedByOpenId", context.SharedBy.OpenId, System.Data.DbType.String); parameters.Add("@currentOpenId", context.Current.OpenId, System.Data.DbType.String); parameters.Add("@appid", context.Current.AppId, System.Data.DbType.String); return(database.Execute(executeSqlString, parameters, System.Data.CommandType.StoredProcedure, true)); } }
public int Save(BO.o51Tag rec) { if (rec.o51Name.Contains(",")) { _mother.CurrentUser.AddMessage("Název položky kategorie nesmí obsahovat čárku."); return(0); } if (rec.o51Name.Length > 30) { _mother.CurrentUser.AddMessage("V názvu položky kategorie může být maximálně 30 znaků."); return(0); } if (GetList(new BO.myQuery("o51Tag")).Where(p => p.pid != rec.pid && p.o51Name.ToLower() == rec.o51Name.Trim().ToLower()).Count() > 0) { _mother.CurrentUser.AddMessage("Položka kategorie s tímto názvem již existuje."); return(0); } var p = new DL.Params4Dapper(); p.AddInt("pid", rec.o51ID); if (rec.j02ID_Owner == 0) { rec.j02ID_Owner = _db.CurrentUser.j02ID; } p.AddInt("j02ID_Owner", rec.j02ID_Owner, true); p.AddInt("o53ID", rec.o53ID, true); p.AddString("o51Name", rec.o51Name); p.AddString("o51Code", rec.o51Code); p.AddInt("o51Ordinary", rec.o51Ordinary); p.AddBool("o51IsColor", rec.o51IsColor); if (rec.o51IsColor == false) { rec.o51ForeColor = ""; rec.o51BackColor = ""; } p.AddString("o51ForeColor", rec.o51ForeColor); p.AddString("o51BackColor", rec.o51BackColor); int intPID = _db.SaveRecord("o51Tag", p.getDynamicDapperPars(), rec); var pars = new Dapper.DynamicParameters(); pars.Add("userid", _db.CurrentUser.pid); pars.Add("pid", intPID, System.Data.DbType.Int32); pars.Add("err_ret", "", System.Data.DbType.String, System.Data.ParameterDirection.Output); _db.RunSp("o51_after_save", ref pars); return(intPID); }
public int Insert(ASSETKKF_MODEL.Data.Mssql.Mcis.MsCauseAppv d, string userID = "") { var param = new Dapper.DynamicParameters(); param.Add("@CauseID", d.CauseID); param.Add("@CauseName", d.CauseName); string cmd = "INSERT INTO mcis.dbo.MsCauseAppv " + $" (CauseID, CauseName) " + $"VALUES(@CauseID, @CauseName); " + $"SELECT SCOPE_IDENTITY();"; return(ExecuteScalar <int>(cmd, param)); }
public ActionResult AddOrEdit(EmployeeModel emp) { Dapper.DynamicParameters param = new Dapper.DynamicParameters(); param.Add("@EmployeeID", emp.EmployeeId); param.Add("@Name", emp.Name); param.Add("@Position", emp.Position); param.Add("@Age", emp.Age); param.Add("@Salary", emp.Salary); DapperORM.ExecuteWithoutReturn("EmployeeAddOrEdit", param); return(RedirectToAction("Index")); }
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 IEnumerable <UserView> SearchUsers(string search) { using (SqlConnection conn = SqlHelper.GetOpenConnection()) { //search = search.Replace("%", "[%]").Replace("[", "[[]").Replace("]", "[]]"); //search = "%" + search + "%"; var par = new Dapper.DynamicParameters(); par.Add("@Search", search); par.Add("@ProjectId", IdentityContext.CurrentProjectId); return(conn.Query <UserView>("SearchUsers", par, commandType: CommandType.StoredProcedure)); } }
internal void AddProductList(ProductModel model) { //組立SQL字串並連接資料庫 StringBuilder _sqlStr = new StringBuilder(); _sqlStr.Append("INSERT INTO Product (Name ,Qty ,Description ,CategoryId ,Price ,CreationDate ,ModifiedDate ,Status ) "); _sqlStr.Append(" VALUES "); _sqlStr.Append("(@Name ,@Qty ,@Description ,@CategoryId ,@Price ,@CreationDate ,@ModifiedDate ,@Status )"); _sqlParams = new Dapper.DynamicParameters(); _sqlParams.Add("Name", model.Name); _sqlParams.Add("Qty", model.Qty); _sqlParams.Add("Description", model.Description); _sqlParams.Add("CategoryId", model.CategoryId); _sqlParams.Add("Price", model.Price); _sqlParams.Add("CreationDate", model.CreationDate); _sqlParams.Add("ModifiedDate", model.ModifiedDate); _sqlParams.Add("Status", model.Status); try { using (var cn = new SqlConnection(_dbConnPPP))//連接資料庫 { cn.Open(); var ExecResult = cn.Execute(_sqlStr.ToString(), _sqlParams); } } catch (Exception ex) { throw ex; } }
public int Insert(ASSETKKF_MODEL.Data.Mssql.Mcis.lnItemGroup d, string userID = "", SqlTransaction transac = null) { var param = new Dapper.DynamicParameters(); param.Add("@Seq_Itemgroup", d.seq_itemgroup); param.Add("@Item_Group", d.itemgroup); param.Add("@Description", d.description); string cmd = "INSERT INTO mcis.dbo.lnItemGroup " + $" (Seq_Itemgroup, Item_Group, Description) " + $"VALUES(@Seq_Itemgroup, @Item_Group, @Description); " + $"SELECT SCOPE_IDENTITY();"; return(ExecuteScalar <int>(cmd, param)); }
/// <summary> /// 检查历史库是否存在数据 /// </summary> /// <returns></returns> protected bool IsExistsInDBHistory(DateTime bTime, DateTime eTime, Guid userId) { string sql = @"SELECT count(0) FROM UserPrize WITH(NOLOCK) WHERE (TypeId IN (4,11,13,14) AND IsReceive=1 and ReceiveDate between @btime and @etime or TypeId=3 and IsUsed=1 and UseDate between @btime and @etime) AND UserId=@UserID"; var para = new Dapper.DynamicParameters(); para.Add("@btime", bTime); para.Add("@etime", eTime); para.Add("@UserID", userId); return (TuanDai.DB.TuanDaiDB.QueryFirstOrDefault <int>(TdConfig.ApplicationName, TdConfig.DBHistoryRead, sql, ref para) > 0); }
public int Insert(ASSETKKF_MODEL.Data.Mssql.Mcis.zValue d, string userID = "") { var param = new Dapper.DynamicParameters(); param.Add("@ValueName", d.ValueName); param.Add("@ValueData", d.ValueData); param.Add("@ValueDes", d.ValueDes); string cmd = "INSERT INTO mcis.dbo.zValue " + $" (ValueName, ValueData, ValueDes) " + $"VALUES(@ValueName, @ValueData, @ValueDes); " + $"SELECT SCOPE_IDENTITY();"; return(ExecuteScalar <int>(cmd, param)); }