public void expect_to_execute_sql()
 {
     var executeSqlFile = new ExecuteSqlFile {
         BuildEngine = _buildEngineMock,
         DbLogin = "BPMOnlineBuild",
         DbPassword = "Terrasoft+",
         DbServer = "tscore-dev-06",
         DbName = "TEST_DB",
         SqlFile = "test.sql"
     };
     var cs = new SqlConnectionStringBuilder {
         DataSource = "tscore-dev-06",
         UserID = "BPMOnlineBuild",
         Password = "Terrasoft+"
     };
     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 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 void expect_to_backup_db()
 {
     var backup = new BackupDb {
         BuildEngine = _buildEngineMock,
         BackupFolder = @"\\V-ARTEMCHUK\tmp\",
         DbLogin = "BPMOnlineBuild",
         DbPassword = "Terrasoft+",
         DbServer = "tscore-dev-06",
         DatabaseName = "TEST_DB"
     };
     var cs = new SqlConnectionStringBuilder {
         DataSource = "tscore-dev-06",
         UserID = "BPMOnlineBuild",
         Password = "Terrasoft+"
     };
     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 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;
        }
Example #5
0
        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 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 });
            }
        }
Example #9
0
 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;
 }
Example #10
0
        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 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);
            }
        }
        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 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 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 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 });
            }
        }
Example #17
0
        // 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);
                }

            }
        }
 /// <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);
     }
 }
        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 expect_to_restore_db()
 {
     var restore = new RestoreDb {
         BuildEngine = _buildEngineMock,
         BackupPath = @"\\V-ARTEMCHUK\tmp\testDatabase.bak",
         DbLogin = "BPMOnlineBuild",
         DbPassword = "Terrasoft+",
         DbServer = "tscore-dev-06"
     };
     restore.Execute();
     var cs = new SqlConnectionStringBuilder {
         DataSource = "tscore-dev-06",
         UserID = "BPMOnlineBuild",
         Password = "Terrasoft+"
     };
     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 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);
            }
        }