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