public void AddMenu() { string sysId = Guid.NewGuid().ToString(); using (IDbConnection connection = ConnectionFactory.CreateOracleConnection()) { var p = new DynamicParameters(); p.Add("@SysId", sysId); p.Add("@MenuParentId", "4300916d-b838-4126-9bf3-abcc6615d908"); p.Add("@MenuOrder", 504); p.Add("@MenuName", "用户管理"); p.Add("@MenuLink", "/User/RoleAssign"); p.Add("@MenuIcon", "icon-nav"); p.Add("@IsVisible", true); p.Add("@IsLeaf", false); p.Add("@RecordStatus", "修改时间" + DateTime.Now + ",修改人:JF"); connection.Execute("Sys_Menu_ADD", p, commandType: CommandType.StoredProcedure); } //using (IDbConnection connection = ConnectionFactory.CreateMsSqlConnection()) //{ // var p = new DynamicParameters(); // p.Add("@SysId", Guid.NewGuid().ToString()); // p.Add("@PrivilegeMaster", PrivilegeMaster.Role); // p.Add("@PrivilegeMasterKey", "cf9d52cc-0500-4829-9611-fd0056961488"); // p.Add("@PrivilegeAccess", PrivilegeAccess.Menu); // p.Add("@PrivilegeAccessKey", sysId); // p.Add("@PrivilegeOperation", true); // p.Add("@RecordStatus", "创建时间:" + DateTime.Now + ",创建人:JF"); // connection.Execute("Sys_Privilege_ADD", p, commandType: CommandType.StoredProcedure); //} }
public void QueryDynamic_WithDynamicParams_Executes() { dynamic result; using (var c = _dataAndSchema.GetConnection()) { c.Open(); var args = new DynamicParameters(new { val = _dataAndSchema.Value.ToString() }); result = c.Query<dynamic>( SqlDataAndSchema.SprocNameDapperSingleInParam, args, commandType: CommandType.StoredProcedure).SingleOrDefault(); } Assert.IsNotNull(result); }
public SqlBuilderState() { FieldNames = new List<string>(); Parameters = new DynamicParameters(); OrderBy = new List<string>(); Hints = new List<string>(); Where = new StringBuilder(); Distinct = false; }
public IEnumerable<SysUser> GetUsers(string roleId) { var p = new DynamicParameters(); p.Add("RoleId", roleId.Trim()); return UserRepository.GetUserIncludeUserInfo( Constant.SqlTableUserAndRoleIncludeUserInfoJoin, Constant.SqlFieldsUserAndRoleIncludeUserInfoJoin, string.Format("ur.{0} ={1}{0}", "RoleId", Constant.SqlReplaceParameterPrefix), p); }
public IEnumerable<SysPrivilege> GetPrivilege(string menuId) { var p = new DynamicParameters(); p.Add(Constant.ColumnSysPrivilegePrivilegeAccess, (int)PrivilegeAccess.Menu); p.Add(Constant.ColumnSysId, menuId.Trim()); return MenuRepository.GetListByTable<SysPrivilege>(Constant.SqlTableMenuPrivilegeJoin, Constant.SqlFieldsPrivilegeJoin, string.Format("u.{1}={0}{1},u.{2}={0}{2}", Constant.SqlReplaceParameterPrefix, Constant.ColumnSysPrivilegePrivilegeAccess, Constant.ColumnSysId), p); //return MenuRepository.GetListByTable<SysPrivilege>(Constant.SqlTableMenuPrivilegeJoin, Constant.SqlFieldsPrivilegeJoin, string.Format("p.PrivilegeAccess={0} and m.SysId = '{1}'", (int)PrivilegeAccess.Menu, menuId)); }
public IEnumerable<SysPrivilege> GetPrivilege(string roleId) { var p = new DynamicParameters(); p.Add(Constant.ColumnSysId, roleId.Trim()); p.Add(Constant.ColumnSysPrivilegePrivilegeMaster, (int)PrivilegeMaster.Role); return RoleRepository.GetListByTable<SysPrivilege>( Constant.SqlTableRolePrivilegeJoin, Constant.SqlFieldsPrivilegeJoin, string.Format("p.{0} = {2}{0} and r.{1}={2}{1}", Constant.ColumnSysPrivilegePrivilegeMaster, Constant.ColumnSysId, Constant.SqlReplaceParameterPrefix), p); }
public void SqlTest() { string sql = "select @Fields from @Table where 2=2"; using (var connection = ConnectionFactory.CreateMsSqlConnection()) { var p = new DynamicParameters(); p.Add("@Table", "Sys_User", DbType.String, ParameterDirection.Input, 1000); p.Add("@Fields", "*", DbType.String, ParameterDirection.Input, 2000); p.Add("@Where", "1=1", DbType.String, ParameterDirection.Input, 1000); var lst = connection.Query<SysUser>(sql, p, commandType: CommandType.Text); } }
public void AddButton() { string sysId = Util.NewId(); using (IDbConnection connection = ConnectionFactory.CreateMsSqlConnection()) { var p = new DynamicParameters(); p.Add("@SysId", sysId); p.Add("@MenuId", "69d8eeaa-5c4b-4a27-b148-f46df54baee0"); p.Add("@BtnName", "保存"); p.Add("@BtnIcon", "icon-nav"); p.Add("@BtnOrder", 0); p.Add("@RecordStatus", "修改时间" + DateTime.Now + ",修改人:JF"); connection.Execute("Sys_Button_ADD", p, commandType: CommandType.StoredProcedure); } }
public SysUser CheckUser(string name, string pwd) { if (string.IsNullOrWhiteSpace(name) || string.IsNullOrWhiteSpace(pwd)) return null; var p = new DynamicParameters(); p.Add(Constant.ColumnSysUserUserName, name.Trim()); p.Add(Constant.ColumnSysUserUserPwd, Crypto.Encrypt(pwd.Trim())); var lstResult = UserRepository.GetList("", string.Format( "{0}={1}{0} and {2}={1}{2}", Constant.ColumnSysUserUserName, Constant.SqlReplaceParameterPrefix, Constant.ColumnSysUserUserPwd ), p); return lstResult.FirstOrDefault(); }
public bool ForecastVehicleSave(ForecastVehicle obj) { bool retVal = true; string procName = (obj.Id == 0 ? "dbo.ForecastVehicle_New" : "dbo.ForecastVehicle_Edit"); using (IDbConnection conn = DbHelper.GetDBConnection()) { try { var para = new DynamicParameters(); para.Add("@p_ForecastVehicleId", obj.ForecastVehicleId, dbType: DbType.Int32); para.Add("@p_ProgrammeId", obj.ProgrammeId, dbType: DbType.Int32); para.Add("@p_VehicleId", obj.VehicleId, dbType: DbType.Int32); para.Add("@p_GatewayId", obj.GatewayId, dbType: DbType.Int32); if (obj.Id == 0) { para.Add("@p_Id", dbType: DbType.Int32, direction: ParameterDirection.Output); } else { para.Add("@p_Id", obj.Id, dbType: DbType.Int32); } conn.Execute(procName, para, commandType: CommandType.StoredProcedure); if (obj.Id == 0) { obj.Id = para.Get<int>("@p_Id"); } } catch (Exception ex) { AppHelper.LogError("ForecastVehicleDataStore.ForecastVehicleSave", ex.Message, CurrentCDSID); retVal = false; } } return retVal; }
public ForecastVehicle ForecastVehicleGet(int id) { ForecastVehicle retVal = null; using (IDbConnection conn = DbHelper.GetDBConnection()) { try { var para = new DynamicParameters(); para.Add("@p_Id", id, dbType: DbType.Int32); retVal = conn.Query<ForecastVehicle>("dbo.ForecastVehicle_Get", para, commandType: CommandType.StoredProcedure).FirstOrDefault(); } catch (Exception ex) { AppHelper.LogError("ForecastVehicleDataStore.ForecastVehicleGet", ex.Message, CurrentCDSID); } } return retVal; }
private void EvaluateExpression() { if ( _evaluated ) return; var state = new SqlBuilderState(); var expression = _expression; while ( expression != null ) { if ( expression is ConstantExpression ) { var constantExpression = expression as ConstantExpression; expression = null; // end of the line // should be an IQueryable<T> where T is the resulting table state.ElementType = constantExpression.Type.GetGenericArguments()[ 0 ]; } else if ( expression is MethodCallExpression ) { var methodCallExpression = expression as MethodCallExpression; if ( methodCallExpression.Arguments.Count == 0 ) throw new NotSupportedException( "Method call expression must have at least one argument" ); // up the stack of expressions expression = methodCallExpression.Arguments[ 0 ]; // process each method LinqProcessor.Process( methodCallExpression.Method.Name ).Interpret( methodCallExpression, state ); } else throw new NotImplementedException( "Expression is not handled" ); } // NICE: put the field list into a concurrent shared dictionary _sql = BuildSqlStatement( state ); if ( state.HasParameters ) _parameters = state.Parameters; _evaluated = true; }
public IEnumerable<ForecastVehicle> ForecastVehicleGetMany ( ) { IEnumerable<ForecastVehicle> retVal = null; using (IDbConnection conn = DbHelper.GetDBConnection()) { try { var para = new DynamicParameters(); retVal = conn.Query<ForecastVehicle>("dbo.ForecastVehicle_GetMany", para, commandType: CommandType.StoredProcedure); } catch (Exception ex) { AppHelper.LogError("ForecastVehicleDataStore.ForecastVehicleGetMany", ex.Message, CurrentCDSID); } } return retVal; }
public JsonResult GetCurrentUserButtonsPrivilegeByMenuName(string menuName) { var p = new DynamicParameters(); p.Add(Constant.ColumnSysMenuMenuName, menuName); var result = menuService.MenuRepository.GetList(Constant.ColumnSysId, string.Format("{0}={1}{0}",Constant.ColumnSysMenuMenuName,Constant.SqlReplaceParameterPrefix),p); if (result == null) { return Json(null); } return GetCurrentUserButtonsPrivilege(result.FirstOrDefault().SysId); }
public void AddMenuToPrivilege() { using (IDbConnection connection = ConnectionFactory.CreateMsSqlConnection()) { var p = new DynamicParameters(); p.Add("@SysId", Guid.NewGuid().ToString()); p.Add("@PrivilegeMaster", PrivilegeMaster.Role); p.Add("@PrivilegeMasterKey", "cf9d52cc-0500-4829-9611-fd0056961488"); p.Add("@PrivilegeAccess", PrivilegeAccess.Menu); p.Add("@PrivilegeAccessKey", "f9199270-abd9-4b83-9020-e0c88e5c8b52"); p.Add("@PrivilegeOperation", true); p.Add("@RecordStatus", "创建时间:"+DateTime.Now+",创建人:JF"); connection.Execute("Sys_Privilege_ADD", p, commandType: CommandType.StoredProcedure); } }
public int Insert(string query, DynamicParameters dynamicParameters, string fieldName) { using (var con = new SqlConnection(conString)) return((int)((IDictionary <string, object>)con.QuerySingle(query, dynamicParameters, commandType: CommandType.StoredProcedure, commandTimeout: 300))[fieldName]); }
public void TestProcedureGetModel() { using (IDbConnection connection = ConnectionFactory.CreateMsSqlConnection()) { var p = new DynamicParameters(); p.Add("@BtnId", "00000000-0000-0000-0000-000000000000"); SysButton SysButton = connection.Query<SysButton>("Sys_Button_GetModel", p, commandType: CommandType.StoredProcedure) .First(); Console.WriteLine(SysButton.BtnIcon); } }
public void TestProcedureGetList() { using (IDbConnection connection = ConnectionFactory.CreateMsSqlConnection()) { var p = new DynamicParameters(); connection.Query("Sys_Button_GetList", commandType: CommandType.StoredProcedure); } }
public void TestProcedureExists() { using (IDbConnection connection = ConnectionFactory.CreateMsSqlConnection()) { var p = new DynamicParameters(); p.Add("@BtnId", "00000000-0000-0000-0000-000000000000"); connection.Execute("Sys_Button_Exists", p, commandType: CommandType.StoredProcedure); } }
public void TestProcedureAdd() { using (IDbConnection connection = ConnectionFactory.CreateMsSqlConnection()) { var p = new DynamicParameters(); p.Add("@SysId", new Guid()); p.Add("@MenuId", "1"); p.Add("@MenuNo", "1"); p.Add("@BtnName", "按钮1"); p.Add("@BtnNo", "1"); p.Add("@BtnIcon", "src='1.png'"); p.Add("@BtnOrder", 1); p.Add("@RecordStatus", "RecordStatus"); connection.Execute("Sys_Button_Add", p, commandType: CommandType.StoredProcedure); } }
public List <T> Query <T>(string query, DynamicParameters dynamicParameters) { using (var con = new SqlConnection(conString)) return(con.Query <T>(query, dynamicParameters, commandType: CommandType.StoredProcedure, commandTimeout: 300).ToList()); }
public IEnumerable<SysRole> GetRoles(string userId) { var p = new DynamicParameters(); p.Add("UserId", userId.Trim()); return UserRoleRepository.GetListByTable<SysRole>(Constant.SqlTableUserAndRoleJoin, "r.SysId,r.RoleDesc,r.RoleName,r.RecordStatus", string.Format("ur.{1}={0}{1}", Constant.SqlReplaceParameterPrefix, "UserId"), p); }
public async Task TestSupportForDynamicParametersOutputExpressions_QueryMultipleAsync() { var bob = new Person { Name = "bob", PersonId = 1, Address = new Address { PersonId = 2 } }; var p = new DynamicParameters(bob); p.Output(bob, b => b.PersonId); p.Output(bob, b => b.Occupation); p.Output(bob, b => b.NumberOfLegs); p.Output(bob, b => b.Address.Name); p.Output(bob, b => b.Address.PersonId); int x, y; using (var multi = await connection.QueryMultipleAsync(@" SET @Occupation = 'grillmaster' SET @PersonId = @PersonId + 1 SET @NumberOfLegs = @NumberOfLegs - 1 SET @AddressName = 'bobs burgers' select 42 select 17 SET @AddressPersonId = @PersonId", p)) { x = multi.ReadAsync<int>().Result.Single(); y = multi.ReadAsync<int>().Result.Single(); } bob.Occupation.IsEqualTo("grillmaster"); bob.PersonId.IsEqualTo(2); bob.NumberOfLegs.IsEqualTo(1); bob.Address.Name.IsEqualTo("bobs burgers"); bob.Address.PersonId.IsEqualTo(2); x.IsEqualTo(42); y.IsEqualTo(17); }
public async Task TestSupportForDynamicParametersOutputExpressions_ScalarAsync() { var bob = new Person { Name = "bob", PersonId = 1, Address = new Address { PersonId = 2 } }; var p = new DynamicParameters(bob); p.Output(bob, b => b.PersonId); p.Output(bob, b => b.Occupation); p.Output(bob, b => b.NumberOfLegs); p.Output(bob, b => b.Address.Name); p.Output(bob, b => b.Address.PersonId); var result = (int)(await connection.ExecuteScalarAsync(@" SET @Occupation = 'grillmaster' SET @PersonId = @PersonId + 1 SET @NumberOfLegs = @NumberOfLegs - 1 SET @AddressName = 'bobs burgers' SET @AddressPersonId = @PersonId select 42", p)); bob.Occupation.IsEqualTo("grillmaster"); bob.PersonId.IsEqualTo(2); bob.NumberOfLegs.IsEqualTo(1); bob.Address.Name.IsEqualTo("bobs burgers"); bob.Address.PersonId.IsEqualTo(2); result.IsEqualTo(42); }
public void Write(LoggingLevel level, string message, Exception exception, Type source) { var connectionKey = ConnectionKey; var insertCommand = InsertCommand; var parameters = new DynamicParameters(new Dictionary<string, object>() { { "@date", DateTime.Now }, { "@utcdate", DateTime.UtcNow }, { "@level", EnumMapper.GetName(level) }, { "@message", message }, { "@exception", exception != null ? exception.ToString() : null }, { "@source", source != null ? source.FullName : null }, { "@thread", Thread.CurrentThread.ManagedThreadId } }); lock (sync) queue.Enqueue(parameters); signal.Set(); }
private async Task LiteralReplacementDynamic(IDbConnection conn) { var args = new DynamicParameters(); args.Add("id", 123); try { await conn.ExecuteAsync("drop table literal2"); } catch { } await conn.ExecuteAsync("create table literal2 (id int not null)"); await conn.ExecuteAsync("insert literal2 (id) values ({=id})", args); args = new DynamicParameters(); args.Add("foo", 123); var count = (await conn.QueryAsync<int>("select count(1) from literal2 where id={=foo}", args)).Single(); count.IsEqualTo(1); }
public IEnumerable<SysPrivilege> GetPrivilege(string buttonId) { var p = new DynamicParameters(); p.Add(Constant.ColumnSysId, buttonId.Trim()); p.Add(Constant.ColumnSysPrivilegePrivilegeAccess, (int)PrivilegeAccess.Button); return ButtonRepository.GetListByTable<SysPrivilege>(Constant.SqlTableButtonPrivilegeJoin, Constant.SqlFieldsPrivilegeJoin, string.Format("p.{0}={2}{0} and b.{1} = {2}{1}", Constant.ColumnSysPrivilegePrivilegeAccess, Constant.ColumnSysId, Constant.SqlReplaceParameterPrefix),p); }
public async Task TestSupportForDynamicParametersOutputExpressions_Query_NonBufferedAsync() { var bob = new Person { Name = "bob", PersonId = 1, Address = new Address { PersonId = 2 } }; var p = new DynamicParameters(bob); p.Output(bob, b => b.PersonId); p.Output(bob, b => b.Occupation); p.Output(bob, b => b.NumberOfLegs); p.Output(bob, b => b.Address.Name); p.Output(bob, b => b.Address.PersonId); var result = (await connection.QueryAsync<int>(new CommandDefinition(@" SET @Occupation = 'grillmaster' SET @PersonId = @PersonId + 1 SET @NumberOfLegs = @NumberOfLegs - 1 SET @AddressName = 'bobs burgers' SET @AddressPersonId = @PersonId select 42", p, flags: CommandFlags.None))).Single(); bob.Occupation.IsEqualTo("grillmaster"); bob.PersonId.IsEqualTo(2); bob.NumberOfLegs.IsEqualTo(1); bob.Address.Name.IsEqualTo("bobs burgers"); bob.Address.PersonId.IsEqualTo(2); result.IsEqualTo(42); }
public void AddUserToRole() { using (IDbConnection connection = ConnectionFactory.CreateMsSqlConnection()) { var p = new DynamicParameters(); p.Add("@SysId", Guid.NewGuid().ToString()); p.Add("@UserId", "cf9d52cc-0500-4829-9611-fd0056961468"); p.Add("@RoleId", "cf9d52cc-0500-4829-9611-fd0056961488"); connection.Execute("Sys_UserRole_ADD", p, commandType: CommandType.StoredProcedure); } }
/// <summary> /// Logs the error to SQL /// If the rollup conditions are met, then the matching error will have a DuplicateCount += @DuplicateCount (usually 1, unless in retry) rather than a distinct new row for the error /// </summary> /// <param name="error">The error to log</param> protected override void LogError(Error error) { using (var c = GetConnection()) { if (RollupThreshold.HasValue && error.ErrorHash.HasValue) { var queryParams = new DynamicParameters(new { error.DuplicateCount, error.ErrorHash, ApplicationName, minDate = DateTime.UtcNow.Add(RollupThreshold.Value.Negate()) }); queryParams.Add("@newGUID", dbType: DbType.Guid, direction: ParameterDirection.Output); var count = c.Execute(@" Update Exceptions Set DuplicateCount = DuplicateCount + @DuplicateCount, @newGUID = GUID Where Id In (Select Top 1 Id From Exceptions Where ErrorHash = @ErrorHash And ApplicationName = @ApplicationName And DeletionDate Is Null And CreationDate >= @minDate)", queryParams); // if we found an error that's a duplicate, jump out if (count > 0) { error.GUID = queryParams.Get<Guid>("@newGUID"); return; } } error.FullJson = error.ToJson(); c.Execute(@" Insert Into Exceptions (GUID, ApplicationName, MachineName, CreationDate, Type, IsProtected, Host, Url, HTTPMethod, IPAddress, Source, Message, Detail, StatusCode, SQL, FullJson, ErrorHash, DuplicateCount) Values (@GUID, @ApplicationName, @MachineName, @CreationDate, @Type, @IsProtected, @Host, @Url, @HTTPMethod, @IPAddress, @Source, @Message, @Detail, @StatusCode, @SQL, @FullJson, @ErrorHash, @DuplicateCount)", new { error.GUID, ApplicationName = error.ApplicationName.Truncate(50), MachineName = error.MachineName.Truncate(50), error.CreationDate, Type = error.Type.Truncate(100), error.IsProtected, Host = error.Host.Truncate(100), Url = error.Url.Truncate(500), HTTPMethod = error.HTTPMethod.Truncate(10), // this feels silly, but you never know when someone will up and go crazy with HTTP 1.2! error.IPAddress, Source = error.Source.Truncate(100), Message = error.Message.Truncate(1000), error.Detail, error.StatusCode, error.SQL, error.FullJson, error.ErrorHash, error.DuplicateCount }); } }
static void ParametrizedQueries() { using (var con = new SqlConnection(ConString)) { //Insert statement with parameters var sql = "Insert into department (departmentid, name) values (@depId, @name)"; var parameters = new DynamicParameters(); parameters.Add("@depId", 1); parameters.Add("@name", "HR"); parameters.Add("@name", "HR"); var rowsAffected = con.Execute(sql, parameters); Console.WriteLine("\nExpected - {0}, Got - {1}\n", 1, rowsAffected); for (var i = 1; i < 5; i++) { sql = "Insert into Employee (id, name, department, salary ) values (@id, @name, @depId, @salary)"; parameters = new DynamicParameters(); parameters.Add("@id", i); switch (i) { case 1: parameters.Add("@name", "sfk shan"); break; case 2: parameters.Add("@name", "Hari"); break; case 3: parameters.Add("@name", "Jothi"); break; case 4: case 5: parameters.Add("@name", "Priya"); break; } parameters.Add("@depId", 1); parameters.Add("@salary", 5000 + i * 100); rowsAffected = con.Execute(sql, parameters); Console.WriteLine("Expected - {0}, Got - {1}", 1, rowsAffected); } var ctr = 0; sql = @"select * from Employee where Name = @name"; parameters = new DynamicParameters(); parameters.Add("@name", "sfk shan"); //Select statement with params //No need to specify if commandType: CommandType.Text coz its the default con.ExecuteReader(sql, reader => { while (reader.HasRows) { while (reader.Read()) { //For test purpose only one row is taken for ctr is used //In actual implementaion it wont be there //For money the specified cast is decimal if (ctr == 0) Console.WriteLine("\nExpected - {0}, got {1}", 5100, reader.Get<int>("Salary")); ctr++; } reader.NextResult(); } ctr = 0; }, parameters, commandType: CommandType.Text); //Update statement with parameters sql = "update Employee set salary = @salary where id >= 1 "; parameters = new DynamicParameters(); parameters.Add("@salary", 5000); var rowsAffected2 = con.Execute(sql, parameters); Console.WriteLine("\nExpected - {0}, Got - {1}", 4, rowsAffected2); } }
public string QueryString(string query, DynamicParameters dynamicParameters) { using (var con = new SqlConnection(conString)) return(con.QuerySingle <string>(query, dynamicParameters, commandType: CommandType.StoredProcedure, commandTimeout: 300)); }
private static void TableValuedParms() { //HERE DATATABLE NAME SHOULD BE THE TABLEVALUE TYPE NAME OTHERWISE WILL GET AN ERROR TYPENAME IS FOUND var dt = new DataTable { TableName = "DepartmentType" }; dt.Columns.Add("DepartmentId", typeof(int)); dt.Columns.Add("Name", typeof(string)); var arrayName = new[] { "HR", "Dev", "DevOps", "Admin" }; for (var i = 0; i <= 3; i++) { var row = dt.NewRow(); row["DepartmentId"] = i + 2; row["Name"] = arrayName[i]; dt.Rows.Add(row); } using (var con = new SqlConnection(ConString)) { var parameters = new DynamicParameters(); parameters.Add("@DepartmentDetails", dt, DbType.Structured, ParameterDirection.Input); parameters.Add("@ReturnValue", 0, direction: ParameterDirection.ReturnValue); con.Execute("spBulkInsert", parameters, CommandType.StoredProcedure); Console.WriteLine("\nExpected - {0}, Got - {1}", 4, parameters.Get<int>("@ReturnValue")); } }