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