public void expect_to_execute_sql() { var executeSqlFile = new ExecuteSqlFile { BuildEngine = _buildEngineMock, DbLogin = "******", DbPassword = "******", DbServer = "tscore-dev-06", DbName = "TEST_DB", SqlFile = "test.sql" }; var cs = new SqlConnectionStringBuilder { DataSource = "tscore-dev-06", UserID = "BPMOnlineBuild", Password = "******" }; using (var connection = new SqlConnection(cs.ConnectionString)) { try { connection.Open(); try { connection.Execute("CREATE DATABASE TEST_DB"); } catch (SqlException ex) { if (ex.Number != 1801) { throw; } } executeSqlFile.Execute(); var tableExist = connection.ExecuteScalar<int>("SELECT COUNT (*) FROM sys.databases d WHERE d.name = @Name ", new { Name = "TestTable" }) == 1; Assert.IsTrue(tableExist); } finally { connection.ExecuteScalar<int>(@" ALTER DATABASE [TEST_DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;DROP DATABASE [TEST_DB]"); } } }
public void expect_to_backup_db() { var backup = new BackupDb { BuildEngine = _buildEngineMock, BackupFolder = @"\\V-ARTEMCHUK\tmp\", DbLogin = "******", DbPassword = "******", DbServer = "tscore-dev-06", DatabaseName = "TEST_DB" }; var cs = new SqlConnectionStringBuilder { DataSource = "tscore-dev-06", UserID = "BPMOnlineBuild", Password = "******" }; using (var connection = new SqlConnection(cs.ConnectionString)) { try { connection.Open(); connection.Execute("CREATE DATABASE TEST_DB"); backup.Execute(); Assert.IsTrue(File.Exists(backup.BackupPath.ToString())); Assert.IsTrue((new FileInfo(backup.BackupPath.ToString())).Length > 10); } finally { connection.ExecuteScalar<int>(string.Format("DROP DATABASE [{0}]", backup.DatabaseName)); } } }
public void Start() { _stopWatch.Start(); // load input version using (var cn = new SqlConnection(_context.Process.Connections.First(c => c.Name == _context.Entity.Connection).GetConnectionString())) { cn.Open(); _context.Entity.MaxVersion = _context.Entity.Version == string.Empty ? null : cn.ExecuteScalar(_context.SqlGetInputMaxVersion()); } // load control batch using (var cn = new SqlConnection(_context.Connection.GetConnectionString())) { cn.Open(); _context.Entity.BatchId = GetBatchId(cn); cn.Execute(_context.SqlControlStartBatch(), new { _context.Entity.BatchId, Entity = _context.Entity.Alias }); // load output version if (_context.Entity.Version == string.Empty) return; var field = _context.Entity.GetVersionField(); if (field == null) return; _context.Entity.MinVersion = cn.ExecuteScalar(_context.SqlGetOutputMaxVersion(field)); } }
public int CreateIndividualConsignee(IndividualConsigneeBindingModel individualConsigneeModel) { int? createdConsigneeId = null; var individualConsignee = new IndividualConsignee(); individualConsignee.FirstName = individualConsigneeModel.FirstName; individualConsignee.LastName = individualConsigneeModel.LastName; individualConsignee.TRN = individualConsigneeModel.TRN; individualConsignee.CustomerCode = individualConsigneeModel.CustomerCode; individualConsignee.Address = individualConsigneeModel.Address; using (var sqlConnection = new SqlConnection(_DBConnectionString)) { try { sqlConnection.Open(); createdConsigneeId = sqlConnection.ExecuteScalar<int>( @"INSERT INTO IndividualConsignee (FirstName,LastName,TRN,Address,CustomerCode) OUTPUT Inserted.Id Values (@indivdualConsignee)", individualConsignee, commandType: CommandType.Text, commandTimeout: 60); } catch (Exception ex) { ex.ToString(); //log exceptions here } } return createdConsigneeId.Value; }
public T_Property Save(SqlConnection db) { // Does not exist, inserting if (this.Property_Id == 0) this.Property_Id = db.ExecuteScalar<int>("INSERT INTO Property(Property_Term_Id, Property_Key, Property_Value) VALUES (@Property_Term_Id, @Property_Key, @Property_Value); SELECT CAST(SCOPE_IDENTITY() as int);", this); else // Exist, Updating db.Execute("UPDATE Property SET Property_Term_Id = @Property_Term_Id, Property_Key = @Property_Key, Property_Value = @Property_Value WHERE Property_Id = @Property_Id", this); // Method Chain return this; }
public static bool IsValueExists(string tableName, string columnName, string value) { using (var conn = new System.Data.SqlClient.SqlConnection(WebInfo.Conn)) { string sql = $@" SELECT IsNull(Sum(1), 0) count FROM {tableName} WHERE {columnName} = @Value "; Dictionary <string, object> param = new Dictionary <string, object>(); param.Add("@Value", value); int retValue = conn.ExecuteScalar <int>(sql, param); return(retValue > 0); } }
public void ExecuteScalar() { const string sql = @"SELECT 1 As IntColumn WHERE @Fizz = 1"; var dict = new Dictionary<string, object> {{"@Fizz", 1}}; // Examples using (var conn = new SqlConnection(My.Config.ConnectionString.UnitTest.ConnectionString)) { conn.Open(); object result = conn.ExecuteScalar(sql, dict.ToSqlParameters()); // UnitTest Assert.AreEqual(1, result); } }
public int AddBrand(Brand brand) { var insertStmt = @"INSERT Products.Brands (BrandName, BrandCode, BrandDescription, BrandDisplayName, InCommBrandIdentifier, BrandImageUrl, IsActive, CreatedOn, CreatedBy, ModifiedOn, ModifiedBy) VALUES (@BrandName, @BrandCode, @BrandDescription, @BrandDisplayName, @InCommBrandIdentifier, @BrandImageUrl, @IsActive, @CreatedOn, @CreatedBy, @ModifiedOn, @ModifiedBy); SELECT scope_identity();"; object id = null; using (var conn = new SqlConnection(_connectionString)) { conn.Open(); id = conn.ExecuteScalar(insertStmt, brand); conn.Close(); } return id != null ? Convert.ToInt32(id) : 0; }
public ActionResult Breadcrumb(string id) { using (_db = new SqlConnection(_connString)) { _db.Open(); var trailData = _db.ExecuteScalar<string>("EXEC mw_Get_Breadcrumb_Trail @ID", new { ID = id }); var links = trailData.Split('|') .Select(x => x.Split('^')) .Select(x => new Tuple<string, string>(x[0], x[1])) .ToList(); return View(new BreadcrumbViewModel { Links = links }); } }
public static User Login(string userName, string password) { using (var context = new PrincipalContext(ContextType.Domain, "erccollections.com")) { var usr = UserPrincipal.FindByIdentity(context, userName); if (usr != null) { var verified = context.ValidateCredentials(userName, password); if (verified) { using (var conn = new SqlConnection(ConnectionString)) { conn.Open(); var user = conn.Query<User>( "select * from [collect2000].[ERCTasks].[Users] WHERE UserName = @userName", new { userName = userName }).FirstOrDefault(); if (user == null) { var userId = conn.ExecuteScalar<int>( "INSERT INTO [collect2000].[ERCTasks].[Users] (UserName, DisplayName, Email) VALUES (@userName, @dispName, @email); SELECT SCOPE_IDENTITY();", new { userName = userName, dispName = usr.DisplayName, email = usr.EmailAddress }); user = new User() { DisplayName = usr.DisplayName, UserName = userName, UserRole = "user", UserId = userId, Email = usr.EmailAddress }; } return user; } } } } return null; }
public void Example1_Dapper() { IEnumerable<Dog> dog; var guid = Guid.NewGuid(); using (var connection = new SqlConnection(s_ConnectionString)) { dog = connection.Query<Dog>("select Age = @Age, Id = @Id", new { Age = (int?)null, Id = guid }); } Assert.AreEqual(1, dog.Count()); Assert.IsNull(dog.First().Age); Assert.AreEqual(guid, dog.First().Id); //Make it more realistic by actually inserting a record var originalDog = new Dog() { Age = 2, Name = "Fido", Weight = 2.5f }; Guid key; using (var connection = new SqlConnection(s_ConnectionString)) { const string insertSql = "INSERT INTO Dog (Age, Name, Weight) OUTPUT Inserted.Id VALUES (@Age, @Name, @Weight);"; key = connection.ExecuteScalar<Guid>(insertSql, originalDog); } //And then re-read it back Dog fetchedDog; using (var connection = new SqlConnection(s_ConnectionString)) { const string selectSql = "SELECT Age, Name, Weight FROM Dog WHERE Id = @Id;"; fetchedDog = connection.Query<Dog>(selectSql, new { Id = key }).Single(); } Assert.AreEqual(originalDog.Age, fetchedDog.Age); Assert.AreEqual(originalDog.Name, fetchedDog.Name); Assert.AreEqual(originalDog.Weight, fetchedDog.Weight); }
public void RemovePlayerByIDTest() { int playerID = 0; using (SqlConnection cn = new SqlConnection(Settings.ConnectionString)) { playerID = (int)cn.ExecuteScalar("select count(*) from Players"); } Assert.AreEqual(true, _ops.RemovePlayerByID(playerID).Success); }
public Stream GetConfiguration(string nodeName) { // Initialize service if uninitialized. Initialize(); // Check for the required parameters. if (string.IsNullOrEmpty(nodeName)) throw new ArgumentNullException("nodeName"); if (string.IsNullOrEmpty(m_connectionString)) throw new ArgumentNullException("ConnectionString"); SqlConnection database = null; DataSet configuration = new DataSet("Iaon"); ; MemoryStream output = new MemoryStream(); try { // Open connection to the database. database = new SqlConnection(m_connectionString); database.Open(); // Lookup ID of the specified node. object nodeID = database.ExecuteScalar(string.Format("SELECT ID FROM Node WHERE [Name] = '{0}'", nodeName)); if (nodeID == null) throw new ArgumentException("nodeName is not valid."); // Add configuration entities table to the output. DataTable entities, entity; entities = database.RetrieveData("SELECT * FROM ConfigurationEntity WHERE Enabled <> 0 ORDER BY LoadOrder"); entities.TableName = "ConfigurationEntity"; configuration.Tables.Add(entities.Copy()); // Add each configuration entity to the output. foreach (DataRow row in entities.Rows) { // Load configuration entity data filtered by node ID. entity = database.RetrieveData(string.Format("SELECT * FROM {0} WHERE NodeID = '{1}'", row["SourceName"].ToString(), nodeID.ToString())); entity.TableName = row["RuntimeName"].ToString(); // Remove redundant node ID column from the data. entity.Columns.Remove("NodeID"); // Add entity configuration data to the output. configuration.Tables.Add(entity.Copy()); } // Extract prepared data in XML format. configuration.WriteXml(output); // Return formatted output to requestor. output.Position = 0; WebOperationContext.Current.OutgoingResponse.ContentType = "application/xml"; WebOperationContext.Current.OutgoingResponse.ContentLength = output.Length; return output; } finally { // Release used memory. if (database != null) database.Dispose(); if (configuration != null) configuration.Dispose(); } }
public ActionResult Move(string id, string parentID) { var sql = "EXEC mw_Move_Document @ID, @ParentID, @Username"; var data = new { ID = id, ParentID = parentID, Username = User.Identity.Name }; using (_db = new SqlConnection(_connString)) { _db.Open(); var location = _db.ExecuteScalar<string>(sql, data); return Json(new { location = location }); } }
public static decimal? PingDb(string connectionString) { try { var csb = new SqlConnectionStringBuilder(connectionString); var db = new {DbName = csb.InitialCatalog}; csb.InitialCatalog = null; using (var connection = new SqlConnection(connectionString)) { var query = "select 1"; var command = new SqlCommand(query, connection); connection.Open(); command.ExecuteScalar(); } using (var c2 = new SqlConnection(csb.ConnectionString)) { return c2.ExecuteScalar<decimal>(@"SELECT CAST(SUM(size) * 8. / 1024 AS DECIMAL(8,2)) FROM sys.master_files WITH(NOWAIT) WHERE database_id = DB_ID(@DbName)", db); } } catch { return null; } }
public ActionResult Create(DocumentCreateViewModel model) { if (!ModelState.IsValid) return View(model); var sql = "EXEC mw_Create_Document @ID, @ParentID, @Title, @Body, @Slug, @Username, @TOC"; var data = new { ID = Guid.NewGuid().ToString().ToLower(), ParentID = model.ParentID, Title = model.Title, Body = model.Body, Slug = WikiHelpers.CreateSlug(model.Title), Username = User.Identity.Name, TOC = model.TOC }; using (_db = new SqlConnection(_connString)) { _db.Open(); var location = _db.ExecuteScalar<string>(sql, data); return Redirect(location); } }
// Import data to [Collect2000].[ERCTasks].[Tasks] and reference table, [Collect2000].[ERCTasks].[TaskUsers], [Collect2000].[ERCTasks].[Users] // Involved table for data migration [Collect2000].[dbo].[erc_ff_FileProcessing], public static void ImportDataDailyRecurrenceTask() { //[Collect2000].[dbo].erc_ff_FileProcessingData.Frequency should map with [Collect2000].[ERCTasks].[Tasks].RecurrencePattern //[Collect2000].[dbo].erc_ff_FileProcessingData.Recurrance should map with [Collect2000].[ERCTasks].[Tasks].RecurrencePattern.RecurrenceBusinessDayStep //Last 4 characters of [Collect2000].[dbo].erc_ff_FileProcessingData.ProcessorSLA should map with [Collect2000].[ERCTasks].[Tasks].TaskDueMinutes //Left characters of (Total length of [Collect2000].[dbo].erc_ff_FileProcessingData.ProcessorSLA - 4) should map with [Collect2000].[ERCTasks].[Tasks].TaskDueHour //[Collect2000].[ERCTasks].[Tasks].TaskDueDate should be null in case of daily recurrence. //We think the [Collect2000].[ERCTasks].[Tasks].TaskName should come from [Collect2000].[dbo].erc_ff_FpProcessorType on behalf of [Collect2000].[dbo].[erc_ff_FileProcessing].ProcessorType. // We think the [Collect2000].[ERCTasks].[Tasks].TaskDesc will remain blank because we don't get the matched column in [Collect2000].[dbo].[erc_ff_FileProcessing]. //Create User [Collect2000].[ERCTasks].[Users]. We assume that user is [Collect2000].[dbo].[erc_ff_FileProcessing].Processor. //We verify user using UserManager.GetUserDetails //For task assignment, UserId & TaskId from above steps to be inserted into [Collect2000].[ERCTasks].[TaskUsers]. // [Collect2000].[dbo].erc_ff_FileProcessingData is the child table of [Collect2000].[dbo].[erc_ff_FileProcessing], //We don't think data require from this [Collect2000].[dbo].erc_ff_FileProcessingData. //Following condition should meet // TaskDueDate = NULL, TaskDueHour > 0, TaskDueMinutes > 0(conditional) and belong to login user //Temporary using TaskDesc to store the username //We assume that need to assign the task to admin by default, if it does not require then remove this part var Dailytasks = new List<Task>(); int userId = 0;//to store recent inserted userId, TaskDueHour > 0 using (var db = new SqlConnection(ConnectionString)) { var q = @"SELECT DISTINCT top 2 ff.Processor TaskName, NULL TaskDueDate, CASE WHEN LEN(tt.ProcessorSLA) > 4 THEN LEFT(tt.ProcessorSLA, LEN(tt.ProcessorSLA) - 4) ELSE NULL END TaskDueHour, CASE WHEN LEN(tt.ProcessorSLA) > 4 THEN LEFT(RIGHT(tt.ProcessorSLA,4),2) ELSE NULL END TaskDueMinutes, tt.Frequency RecurrencePattern, tt.Recurrance RecurrenceBusinessDayStep, tt.Processor TaskDesc, tt.Customer FROM [Collect2000].[dbo].[erc_ff_FileProcessing] tt JOIN [Collect2000].[dbo].erc_ff_FpProcessorType ff WITH(NOLOCK) ON ff.pid = tt.ProcessorType WHERE tt.Frequency = 'Daily'"; Dailytasks.AddRange(db.Query<Task>(q)); foreach (var dr in Dailytasks) { userId = 0; var q1 = @"INSERT INTO [collect2000].[ERCTasks].[Tasks] (TaskDesc, TaskDueDate, TaskDueHour, TaskDueMinutes, RecurrencePattern, RecurrenceBusinessDayStep, TaskName, Customer) VALUES (@TaskName, @duedate, @hour, @minutes, @pattern, @daystep, @TaskName, @Customer);SELECT SCOPE_IDENTITY();"; var Tid = db.Query<int>(q1, new { duedate = dr.TaskDueDate, hour = dr.TaskDueHour, minutes = dr.TaskDueMinutes, pattern = dr.RecurrencePattern, daystep = dr.RecurrenceBusinessDayStep, TaskName = dr.TaskName, Customer = dr.Customer }).FirstOrDefault<int>(); User usr = UserManager.GetUserDetails(dr.TaskDesc);//dr.TaskDesc = [Collect2000].[dbo].[erc_ff_FileProcessing].Processor. We don't have variable for username in Task class so utilized the un-used TaskDesc temporary. //create user by [Collect2000].[dbo].[erc_ff_FileProcessing].Processor if (usr.UserName != null && usr.Email != null) { userId = db.ExecuteScalar<int>( "IF NOT EXISTS(SELECT * FROM [collect2000].[ERCTasks].[Users] WHERE UserName = @userName) BEGIN INSERT INTO [collect2000].[ERCTasks].[Users] (UserName, DisplayName, Email) VALUES (@userName, @dispName, @email); SELECT SCOPE_IDENTITY(); END IF EXISTS(SELECT * FROM [collect2000].[ERCTasks].[Users] WHERE UserName = @userName) SELECT UserId FROM [collect2000].[ERCTasks].[Users] WHERE UserName = @userName", new { userName = usr.UserName, dispName = usr.DisplayName, email = usr.Email }); } //assignment of current created task //in above query we pay attention on if user exist then return the exist userId otherwise new inserted userId will return //if UserName and Email is null thenin this case UserId will zero because code will not execute. //We have provided the below codition to avoid assignment if UseId is zero. if (userId > 0) AssignedToUser(userId, Tid); //default assign to admin //commented the auto assign to admin for task "Modify ImportDataDailyRecurrenceTask #15" //in future if we need auto assign to admin then simply uncomment the code // userId = db.ExecuteScalar<int>( // "SELECT TOP 1 UserId FROM [collect2000].[ERCTasks].[Users] WHERE UserRole = 'admin'", // new { userName = usr.UserName, dispName = usr.DisplayName, email = usr.Email }); // AssignedToUser(userId, Tid); } } }
public int CreateCompanyConsignee(CompanyConsigneeBindingModel companyConsigneeModel) { int? createdConsigneeId = null; var companyCosignee = new CompanyConsignee(); companyCosignee.CompanyName = companyConsigneeModel.CompanyName; companyCosignee.CompanyTRN = companyConsigneeModel.CompanyTRN; companyCosignee.CompanyAddress = companyConsigneeModel.CompanyAddress; companyCosignee.CompanyEmail = companyConsigneeModel.CompanyEmail; companyCosignee.CompanyPhone = companyCosignee.CompanyPhone; companyCosignee.CustomerCode = companyCosignee.CustomerCode; using (var sqlConnection = new SqlConnection(_DBConnectionString)) { try { sqlConnection.Open(); createdConsigneeId = sqlConnection.ExecuteScalar<int>( @"INSERT INTO CompanyConsignee (CompanyName,CompanyTRN,CompanyAddress,CompanyEmail,CompanyPhoneNumber,CustomerCode) OUTPUT Inserted.Id Values (@companyConsignee)",companyCosignee, commandType: CommandType.Text, commandTimeout: 60); } catch (Exception ex) { ex.ToString(); //log exceptions here } } return createdConsigneeId.Value; }
public void GetAllPlayersOnAllTeamsTest() { int count = 0; using (SqlConnection cn = new SqlConnection(Settings.ConnectionString)) { count = (int)cn.ExecuteScalar("select count(*) from players"); } Assert.AreEqual(count, _ops.GetAllPlayerOnAllTeams().Players.Count()); }
public void expect_to_restore_db() { var restore = new RestoreDb { BuildEngine = _buildEngineMock, BackupPath = @"\\V-ARTEMCHUK\tmp\testDatabase.bak", DbLogin = "******", DbPassword = "******", DbServer = "tscore-dev-06" }; restore.Execute(); var cs = new SqlConnectionStringBuilder { DataSource = "tscore-dev-06", UserID = "BPMOnlineBuild", Password = "******" }; using (var connection = new SqlConnection(cs.ConnectionString)) { connection.Open(); var dbs = connection.ExecuteScalar<int>("SELECT COUNT (*) FROM sys.databases d WHERE d.name = @Name ", new { Name = restore.DbName.ToString() }); Assert.AreEqual(1, dbs); connection.ExecuteScalar<int>(string.Format("DROP DATABASE [{0}]",restore.DbName)); } }
public int Insert(Employee employee) { const string sql = @"INSERT INTO HR.Employee (FirstName, MiddleName, LastName, Title, ManagerKey, OfficePhone, CellPhone ) VALUES (@FirstName, @MiddleName, @LastName, @Title, @ManagerKey, @OfficePhone, @CellPhone ); SELECT SCOPE_IDENTITY() "; using (var con = new SqlConnection(m_ConnectionString)) { con.Open(); return con.ExecuteScalar<int>(sql, employee); } }
/// <summary> /// Checks whether a decision has already been saved in the database /// </summary> /// <param name="decisionText">The decision text.</param> /// <returns></returns> public static bool DecisionExists(string decisionText) { using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["GrittingDecisionReader"].ConnectionString)) { var decisionCount = (int)conn.ExecuteScalar("usp_Decision_Exists", new { decisionText }, commandType: CommandType.StoredProcedure); return (decisionCount > 0); } }