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