public static IDataReader Departments()
{
SqlDatabase db = new SqlDatabase(connString);
DbCommand command = db.GetSqlStringCommand("SELECT DeptId, Name FROM AllDepartments D WHERE D.Active=1 AND D.NormBillsTime=1 ORDER BY Name");
command.CommandType = CommandType.Text;
return db.ExecuteReader(command);
}
public static IDataReader GetIDataReader(string connectionString, string sqlQuery)
{
SqlDatabase sqlServerDB = new SqlDatabase(connectionString);
DbCommand cmd = sqlServerDB.GetSqlStringCommand(sqlQuery);
//return an IDataReader.
return sqlServerDB.ExecuteReader(cmd);
}
public static DataTable GetResources(int jobId, int deptId)
{
SqlDatabase db = new SqlDatabase(connString);
string sql = @" SELECT U.UserId, U.FirstName + ' ' + U.LastName AS FullName,
t.TitleName as Title
FROM AllocableUsers U LEFT JOIN JobTitles AS t ON U.currentTitleID=t.TitleID
WHERE U.Active=1 AND U.UserId NOT IN (
SELECT UserId FROM Assignments WHERE JobId=@job_id
AND (EndDate IS NULL OR EndDate>DATEADD(s, 1, CURRENT_TIMESTAMP)))
AND U.DeptId=@dept_id AND realPerson='Y'
AND UserId NOT IN (
SELECT UserId
FROM timeEntry
WHERE JobId=@job_id AND UserId=U.UserId AND (TimeSpan IS NULL OR TimeSpan > 0)
)
ORDER BY FullName";
DbCommand command = db.GetSqlStringCommand(sql);
db.AddInParameter(command, "@job_id", DbType.Int32, jobId);
db.AddInParameter(command, "@dept_id", DbType.Int32, deptId);
DataTable t = new DataTable();
t = db.ExecuteDataSet(command).Tables[0].Copy();
t.TableName = "Resources";
command.Dispose();
return t;
}
public static UserAuthResult Authenticate(string userName, string password, string providerKey)
{
string Auth_GetUserByCredentials =
@"SELECT u.ID,u.Name,u.Surname,u.Email,u.Password,u.About,u.BirthDate,u.DateCreated,u.LastLogin,u.DateUpdated,ul.LoginProvider
FROM User AS u
INNER JOIN UserLogin AS ul
ON u.ID = ul.UserID
WHERE u.Email = '{1}'
WHERE ul.Providerkey = '{1}'";
string connStr = ConfigurationManager.AppSettings["MasterSQLConnection"];
SqlDatabase db = new SqlDatabase(connStr);
UserAuthResult result = new UserAuthResult();
result.AuthSuccess = false;
User user = new User();
string dbPassword = string.Empty;
try
{
string query = String.Format(Auth_GetUserByCredentials, userName);
using (DbCommand command = db.GetSqlStringCommand(query))
{
using (IDataReader reader = db.ExecuteReader(command))
{
if (reader.Read())
{
//Users.ID,Users.Name,Surname,IsAdmin,IsSuperAdmin,LoginType,Users.ActiveDirectoryDomain,Password
user.ID = int.Parse(reader["ID"].ToString());
user.Password = reader["Password"].ToString();
user.Name = reader["Name"].ToString();
user.Surname = reader["Surname"].ToString();
}
else
{
result.AuthSuccess = false;
result.ErrorMsg = "Username or password is wrong";
}
}
}
}
finally
{
}
if (!string.IsNullOrEmpty(password) && user.ID > 0 && password.Equals(user.Password))
{
result.User = user;
result.AuthSuccess = true;
}
else
{
result.ErrorMsg = "Username or password is wrong";
}
return result;
}
public static IDataReader Employees(int deptId)
{
SqlDatabase db = new SqlDatabase(connString);
DbCommand command = db.GetSqlStringCommand("SELECT FirstName + ' ' + LastName AS FullName, UserId FROM AllocableUsers WHERE DeptId=" + deptId + " AND Active=1 ORDER BY FullName");
command.CommandType = CommandType.Text;
return db.ExecuteReader(command);
}
public static void UpdateRole(Role role)
{
string sqlQuery = "UPDATE ROLE SET Name=@Name WHERE RoleID=" + role.RoleID;
Database db = new SqlDatabase(DBHelper.GetConnectionString());
DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery);
db.AddInParameter(dbCommand, "Name", DbType.String, role.Name);
db.ExecuteNonQuery(dbCommand);
}
//To write to DB
public static int WriteToDb(string connectionString, string sqlQuery)
{
SqlDatabase db = new SqlDatabase(connectionString);
DbCommand cmd = db.GetSqlStringCommand(sqlQuery);
//return the number of rows affected
return db.ExecuteNonQuery(cmd);
}
public static void AddEmployeeToRole(int employeeID, int roleID)
{
string sqlQuery = "INSERT INTO EMPLOYEEROLES(EmployeeID, RoleID) Values (@EmployeeID, @RoleID)";
Database db = new SqlDatabase(DBHelper.GetConnectionString());
DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery);
db.AddInParameter(dbCommand, "EmployeeID", DbType.Int32, employeeID);
db.AddInParameter(dbCommand, "RoleID", DbType.Int32, roleID);
db.ExecuteNonQuery(dbCommand);
}
public static DateTime GetJobEndDate(int jobId)
{
SqlDatabase db = new SqlDatabase(connString);
DbCommand command = db.GetSqlStringCommand("SELECT currentEndDate FROM AllOpenJobs WHERE JobId=" + jobId);
DateTime retval = DateTime.MinValue;
object obj = db.ExecuteScalar(command);
if (obj != DBNull.Value)
retval = Convert.ToDateTime(obj);
return retval;
}
public static IDataReader AllJobs(int clientId)
{
SqlDatabase db = new SqlDatabase(connString);
DbCommand command = db.GetSqlStringCommand(@"
SELECT JobId, Name FROM AllOpenJobs J
WHERE Active=1 AND ClientId=@clientId
ORDER BY Name");
db.AddInParameter(command, "@clientId", DbType.Int32, clientId);
return db.ExecuteReader(command);
}
public static void UpdateCity(City city)
{
string sqlQuery = "UPDATE City SET Name=@Name WHERE CityID=" + city.CityID;
Database db = new SqlDatabase(DBHelper.GetConnectionString());
DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery);
db.AddInParameter(dbCommand, "Name", DbType.String, city);
db.ExecuteNonQuery(dbCommand);
}
public static bool DeleteEmployee(int employeeID)
{
string sqlQuery = "UPDATE EMPLOYEE SET Active=0 WHERE EmployeeID = " + employeeID.ToString();
Database db = new SqlDatabase(DBHelper.GetConnectionString());
DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery);
if (db.ExecuteNonQuery(dbCommand) > 0)
return true;
else
return false;
}
public void TestInitialize()
{
string partitionName = "Partition1";
backingStore = CreateBackingStore(instanceName, partitionName);
Data.Database db = new SqlDatabase(@"server=(local)\SQLEXPRESS;database=Caching;Integrated Security=true");
DbCommand wrapper = db.GetSqlStringCommand("delete from CacheData");
db.ExecuteNonQuery(wrapper);
}
public static int GetDeptIdForUser(int userId)
{
int retval = -1;
SqlDatabase db = new SqlDatabase(connString);
DbCommand command = db.GetSqlStringCommand("SELECT DeptId FROM AllocableUsers WHERE UserId=" + userId);
command.CommandType = CommandType.Text;
object obj = db.ExecuteScalar(command);
if (obj != DBNull.Value)
retval = Convert.ToInt32(obj);
return retval;
}
public static int GetClientIdForJob(int jobId)
{
int retval = -1;
SqlDatabase db = new SqlDatabase(connString);
DbCommand command = db.GetSqlStringCommand("SELECT ClientId FROM AllOpenJobs WHERE JobId=" + jobId);
command.CommandType = CommandType.Text;
object obj = db.ExecuteScalar(command);
if (obj != DBNull.Value)
retval = Convert.ToInt32(obj);
return retval;
}
public static City InsertCity(City city)
{
string sqlQuery = "INSERT INTO City(Name) " +
" VALUES(@Name);SELECT @@Identity";
Database db = new SqlDatabase(DBHelper.GetConnectionString());
DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery);
db.AddInParameter(dbCommand, "Name", DbType.String, city.Name);
city.CityID = Convert.ToInt32(db.ExecuteScalar(dbCommand));
return city;
}
public static Role InsertRole(Role role)
{
string sqlQuery = "INSERT INTO ROLE(Name) VALUES(@Name);SELECT @@Identity";
Database db = new SqlDatabase(DBHelper.GetConnectionString());
DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery);
db.AddInParameter(dbCommand, "Name", DbType.String, role.Name);
role.RoleID = Convert.ToInt32(db.ExecuteScalar(dbCommand));
return role;
}
public static void UpdateContact(Contact contact)
{
string sqlQuery = "UPDATE Contact SET FirstName=@FirstName,LastName=@LastName,Email=@Email,Phone=@Phone WHERE ContactID=" + contact.ContactID;
Database db = new SqlDatabase(DBHelper.GetConnectionString());
DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery);
db.AddInParameter(dbCommand, "FirstName", DbType.String, contact.FirstName);
db.AddInParameter(dbCommand, "LastName", DbType.String, contact.LastName);
db.AddInParameter(dbCommand, "Email", DbType.String, contact.Email);
db.AddInParameter(dbCommand, "Phone", DbType.String, contact.Phone);
db.ExecuteNonQuery(dbCommand);
}
public static void UpdateTerritory(Territory territory)
{
string sqlQuery = "UPDATE Territory SET ParentTerritoryID=@ParentTerritoryID, FullDescription=@FullDescription, Name=@Name WHERE TerritoryID=" + territory.TerritoryID;
Database db = new SqlDatabase(DBHelper.GetConnectionString());
DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery);
db.AddInParameter(dbCommand, "ParentTerritoryID", DbType.Int32, territory.ParentTerritoryID);
db.AddInParameter(dbCommand, "FullDescription", DbType.String, territory.FullDescription);
db.AddInParameter(dbCommand, "Name", DbType.String, territory.Name);
db.ExecuteNonQuery(dbCommand);
}
private static Role GetRoleFromQuery(string sqlQuery)
{
Role role = null;
Database db = new SqlDatabase(DBHelper.GetConnectionString());
DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
while (dataReader.Read())
{
role = GetRoleFromReader(dataReader);
}
}
return role;
}
private static Address GetAddressFromQuery(string sqlQuery)
{
Address address = null;
Database db = new SqlDatabase(DBHelper.GetConnectionString());
DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
while (dataReader.Read())
{
address = GetAddressFromReader(dataReader);
}
}
return address;
}
private static City GetCityFromQuery(string sqlQuery)
{
City city = null;
Database db = new SqlDatabase(DBHelper.GetConnectionString());
DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery);
using (IDataReader dataReader = db.ExecuteReader(dbCommand))
{
while (dataReader.Read())
{
city = GetCityFromReader(dataReader);
}
}
return city;
}
public static Territory InsertTerritory(Territory territory)
{
string sqlQuery = "INSERT INTO Territory(ParentTerritoryID,FullDescription,Name) " +
" VALUES(@ParentTerritoryID,@FullDescription,@Name);SELECT @@Identity";
Database db = new SqlDatabase(DBHelper.GetConnectionString());
DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery);
db.AddInParameter(dbCommand, "ParentTerritoryID", DbType.Int32, territory.ParentTerritoryID);
db.AddInParameter(dbCommand, "FullDescription", DbType.String, territory.FullDescription);
db.AddInParameter(dbCommand, "Name", DbType.String, territory.Name);
territory.TerritoryID = Convert.ToInt32(db.ExecuteScalar(dbCommand));
return territory;
}
public static void UpdateAddress(Address address)
{
string sqlQuery = "UPDATE Address SET CountryID=@CountryID, CityID=@CityID, Street=@Street, ZipCode=@ZipCode, HouseNr=@HouseNr, ApartmentNr=@AparmentNr WHERE AddressID=" + address.AddressID;
Database db = new SqlDatabase(DBHelper.GetConnectionString());
DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery);
db.AddInParameter(dbCommand, "CountryID", DbType.Int32, address.CountryID);
db.AddInParameter(dbCommand, "CityID", DbType.String, address.City.CityID);
db.AddInParameter(dbCommand, "Street", DbType.String, address.Street);
db.AddInParameter(dbCommand, "ZipCode", DbType.String, address.ZipCode);
db.AddInParameter(dbCommand, "HouseNr", DbType.String, address.HouseNr);
db.AddInParameter(dbCommand, "ApartmentNr", DbType.String, address.ApartmentNr);
db.AddInParameter(dbCommand, "AddressID", DbType.Int32, address.AddressID);
db.ExecuteNonQuery(dbCommand);
}
public static Contact InsertContact(Contact contact)
{
string sqlQuery = "INSERT INTO Contact(FirstName,LastName,Email,Phone) " +
" VALUES(@FirstName,@LastName,@Email,@Phone);SELECT @@Identity";
Database db = new SqlDatabase(DBHelper.GetConnectionString());
DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery);
db.AddInParameter(dbCommand, "FirstName", DbType.String, contact.FirstName);
db.AddInParameter(dbCommand, "LastName", DbType.String, contact.LastName);
db.AddInParameter(dbCommand, "Email", DbType.String, contact.Email);
db.AddInParameter(dbCommand, "Phone", DbType.String, contact.Phone);
contact.ContactID = Convert.ToInt32(db.ExecuteScalar(dbCommand));
return contact;
}
public static DataTable ClientTeams(int clientId)
{
SqlDatabase db = new SqlDatabase(connString);
DbCommand command = db.GetSqlStringCommand(@"
SELECT TeamID, Name, Description
FROM ALOC_Teams
WHERE ClientID=@client_id
ORDER BY Name
");
command.CommandType = CommandType.Text;
db.AddInParameter(command, "@client_id", DbType.Int32, clientId);
DataTable t = new DataTable();
t = db.ExecuteDataSet(command).Tables[0].Copy();
t.TableName = "Teams";
command.Dispose();
return t;
}
public static Address InsertAddress(Address address)
{
string sqlQuery = "INSERT INTO Address(CountryID,CityID,Street,ZipCode,HouseNr,ApartmentNr) " +
" VALUES(@CountryID,@CityID,@Street,@ZipCode,@HouseNr,@ApartmentNr);SELECT @@Identity";
Database db = new SqlDatabase(DBHelper.GetConnectionString());
DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery);
db.AddInParameter(dbCommand, "CountryID", DbType.Int32, address.CountryID);
db.AddInParameter(dbCommand, "CityID", DbType.Int32, address.CityID);
db.AddInParameter(dbCommand, "Street", DbType.String, address.Street);
db.AddInParameter(dbCommand, "ZipCode", DbType.String, address.ZipCode);
db.AddInParameter(dbCommand, "HouseNr", DbType.String, address.HouseNr);
db.AddInParameter(dbCommand, "ApartmentNr", DbType.String, address.ApartmentNr);
address.AddressID = Convert.ToInt32(db.ExecuteScalar(dbCommand));
return address;
}
public void ItemRemovedFromCacheCompletelyIfAddFails()
{
cache.Add("foo", new SerializableClass());
try
{
cache.Add("foo", new NonSerializableClass());
Assert.Fail("should have thrown exception in Cache.Add");
}
catch (Exception)
{
Assert.IsFalse(cache.Contains("foo"));
string isItInDatabaseQuery = "select count(*) from CacheData";
Data.Database db = new SqlDatabase(@"server=(local)\SQLEXPRESS;database=Caching;Integrated Security=true");
DbCommand wrapper = db.GetSqlStringCommand(isItInDatabaseQuery);
int count = (int)db.ExecuteScalar(wrapper);
Assert.AreEqual(0, count);
}
}
public static DataTable GetAssigned(int jobId)
{
SqlDatabase db = new SqlDatabase(connString);
string sql = @" SELECT U.UserId, U.FirstName + ' ' + U.LastName AS FullName,
D.Name AS DeptName, TimeBilled =
CASE
WHEN A.EndDate IS NULL OR A.EndDate>DATEADD(s, 2, CURRENT_TIMESTAMP) THEN 0
ELSE 1
END
FROM AllocableUsers U INNER JOIN AllDepartments D ON U.DeptId=D.DeptId
INNER JOIN Assignments A ON (U.UserId=A.UserId AND A.JobId=@job_id)
WHERE realPerson='Y'
ORDER BY Fullname
";
DbCommand cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "@job_id", DbType.Int32, jobId);
DataTable t = new DataTable();
t = db.ExecuteDataSet(cmd).Tables[0].Copy();
t.TableName = "Assigned";
cmd.Dispose();
return t;
}
public static Employee InsertEmployee(Employee employee)
{
string sqlQuery = "INSERT INTO EMPLOYEE(TerritoryID,AddressID,ManagerID,ContactID,Login,PasswordHash,PasswordSalt,Active,CreationDate,LastLoginDate,LastActivityDate) " +
" VALUES(@TerritoryID,@AddressID,@ManagerID,@ContactID,@Login,@PasswordHash,@PasswordSalt,@Active,@CreationDate,@LastLoginDate,@LastActivityDate);SELECT @@Identity";
Database db = new SqlDatabase(DBHelper.GetConnectionString());
DbCommand dbCommand = db.GetSqlStringCommand(sqlQuery);
db.AddInParameter(dbCommand, "TerritoryID", DbType.Int32, employee.TerritoryID);
db.AddInParameter(dbCommand, "AddressID", DbType.Int32, employee.AddressID);
db.AddInParameter(dbCommand, "ManagerID", DbType.Int32, employee.ManagerID);
db.AddInParameter(dbCommand, "ContactID", DbType.Int32, employee.ContactID);
db.AddInParameter(dbCommand, "Login", DbType.String, employee.Login);
db.AddInParameter(dbCommand, "PasswordHash", DbType.String, employee.PasswordHash);
db.AddInParameter(dbCommand, "PasswordSalt", DbType.String, employee.PasswordSalt);
db.AddInParameter(dbCommand, "Active", DbType.Boolean, true);
db.AddInParameter(dbCommand, "CreationDate", DbType.DateTime, employee.CreationDate);
db.AddInParameter(dbCommand, "LastLoginDate", DbType.DateTime, employee.LastLoginDate);
db.AddInParameter(dbCommand, "LastActivityDate", DbType.DateTime, employee.LastActivityDate);
employee.EmployeeID = Convert.ToInt32(db.ExecuteScalar(dbCommand));
return employee;
}
