public void DoLotsOfConnectionFailures()
{
int numberOfEvents = 50;
using (WmiEventWatcher eventListener = new WmiEventWatcher(numberOfEvents))
{
SqlDatabase db = new SqlDatabase("BadConnectionString");
DataInstrumentationListener listener = new DataInstrumentationListener("foo", true, true, true);
DataInstrumentationListenerBinder binder = new DataInstrumentationListenerBinder();
binder.Bind(db.GetInstrumentationEventProvider(), listener);
for (int i = 0; i < numberOfEvents; i++)
{
try
{
db.ExecuteScalar(CommandType.Text, "Select count(*) from Region");
}
catch { }
}
eventListener.WaitForEvents();
Assert.AreEqual(numberOfEvents, eventListener.EventsReceived.Count);
Assert.AreEqual("ConnectionFailedEvent", eventListener.EventsReceived[0].ClassPath.ClassName);
Assert.AreEqual("foo", eventListener.EventsReceived[0].GetPropertyValue("InstanceName"));
Assert.AreEqual(db.ConnectionStringWithoutCredentials, eventListener.EventsReceived[0].GetPropertyValue("ConnectionString"));
}
}
public static int CurrectWeek()
{
SqlDatabase db = new SqlDatabase( connString );
DbCommand command = db.GetStoredProcCommand( "getWeekNr" );
command.CommandType = CommandType.StoredProcedure;
//the +1 below is to correct for an apparent off-by-one error in the stored procedure
return Convert.ToInt32( db.ExecuteScalar( command ) ) + 1;
}
public static DateTime CurrectWeekStartDate( int currentWeek )
{
SqlDatabase db = new SqlDatabase( connString );
DbCommand command = db.GetStoredProcCommand( "smWkNmStr" );
command.CommandType = CommandType.StoredProcedure;
db.AddInParameter( command, "@week_no", DbType.Int32, currentWeek );
return Convert.ToDateTime( db.ExecuteScalar( command ) );
}
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 override bool IsUserInRole(string username, string roleName)
{
SqlDatabase sqlDatabase = new SqlDatabase(_connectionString);
DbCommand dbCommand = sqlDatabase.GetStoredProcCommand("adm.SCISP_EstaElUsuarioEnElRol");
sqlDatabase.AddInParameter(dbCommand, "Aplicacion", DbType.String, _applicationName);
sqlDatabase.AddInParameter(dbCommand, "Login", DbType.String, username);
sqlDatabase.AddInParameter(dbCommand, "Rol", DbType.String, roleName);
return (bool) sqlDatabase.ExecuteScalar(dbCommand);
}
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 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 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 override int DeleteInactiveProfiles(ProfileAuthenticationOption authenticationOption,
DateTime userInactiveSinceDate)
{
SqlDatabase sqlDatabase = new SqlDatabase(_connectionString);
DbCommand dbCommand = sqlDatabase.GetStoredProcCommand("adm.NlayerSP_EliminarPerfilesInactivos");
sqlDatabase.AddInParameter(dbCommand, "Aplicacion", DbType.String, _applicationName);
sqlDatabase.AddInParameter(dbCommand, "UltimaActividad", DbType.DateTime, userInactiveSinceDate);
int deleteCount = (int) sqlDatabase.ExecuteScalar(dbCommand);
return deleteCount;
}
public static DateTime GetCurrentDateTime()
{
DateTime dt = DateTime.MinValue;
SqlDatabase objSqlDatabase = new SqlDatabase(_ConStr);
try
{
string commandText = "SELECT GETDATE() AS Today ";
dt = (DateTime)objSqlDatabase.ExecuteScalar(System.Data.CommandType.Text, commandText);
}
catch (SqlException)
{ }
return dt;
}
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 bool LoginUserDAL(LoginModel userData)
{
SqlDatabase travelMSysDB = new SqlDatabase(ConnString.DBConnectionString);// (@"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\TravelMS_Sep16.mdf;Integrated Security=True");
SqlCommand selectCmmnd = new SqlCommand("SELECT [Password] FROM EMPLOYEES WHERE [User_ID]=@User_ID");
selectCmmnd.CommandType = CommandType.Text;
selectCmmnd.Parameters.AddWithValue("@User_ID", userData.User_ID);
object pHash = travelMSysDB.ExecuteScalar(selectCmmnd);
if (!(pHash == null))
if (pHash.ToString().Equals(userData.Password))
return true;
return false;
}
public static bool LoginAgentDAL(LoginModel userData)
{
SqlDatabase travelMSysDB = new SqlDatabase(ConnString.DBConnectionString);
SqlCommand selectCmmnd = new SqlCommand("SELECT [Password] FROM AGENTS WHERE [Agent_ID]=@User_ID");
selectCmmnd.CommandType = CommandType.Text;
selectCmmnd.Parameters.AddWithValue("@User_ID", userData.User_ID);
object pHash = travelMSysDB.ExecuteScalar(selectCmmnd);
if (!(pHash == null))
if (pHash.ToString().Equals(userData.Password))
return true;
return false;
}
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 string nextClaimID()
{
SqlDatabase travelMSysDB = new SqlDatabase(@"Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\TravelMS_Sep16.mdf;Integrated Security=True");
SqlCommand selectCmmnd = new SqlCommand("SELECT IDENT_CURRENT('CLAIM_REQUESTS')+1");
selectCmmnd.CommandType = CommandType.Text;
object num = travelMSysDB.ExecuteScalar(selectCmmnd);
string res;
if (!(num == null))
{
res = ("000000" + num.ToString());
res = 'C' + res.Substring(res.Length - 6);
return res;
}
throw new Exception("Next Claim Request Failed.");
}
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 static bool ApproverIsFirstLevel(string appr)
{
if (string.IsNullOrWhiteSpace(appr))
return false;
SqlDatabase travelMSysDB = new SqlDatabase(ConnString.DBConnectionString);
SqlCommand queryCmmnd = new SqlCommand("SELECT Job_Level FROM EMPLOYEES WHERE User_ID=@User_ID");
queryCmmnd.CommandType = CommandType.Text;
queryCmmnd.Parameters.AddWithValue("@User_ID", appr);
var level = travelMSysDB.ExecuteScalar(queryCmmnd);
if (level == null)
return false;
if (level.ToString().Equals("1"))
return true;
return false;
}
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);
}
}
private static bool IsUser(string authcode, ref bool result)
{
try
{
SqlDatabase db = new SqlDatabase(Properties.Settings.Default.connString);
using (DbCommand cmd = db.GetStoredProcCommand("IsUser"))
{
db.AddInParameter(cmd, "authcode", DbType.String, authcode);
int? o = db.ExecuteScalar(cmd) as int?;
if (o == null)
result = false;
else
result = (o.Value == 1);
return true;
}
}
catch
{
return false;
}
}
public static int GetMaxTestID()
{
try
{
int result = 0;
SqlDatabase db = new SqlDatabase(Properties.Settings.Default.connString);
using (DbCommand cmd = db.GetStoredProcCommand("SelectMaxTest"))
{
int? o = db.ExecuteScalar(cmd) as int?;
if (o == null)
result = 0;
else
result = o.Value;
return result;
}
}
catch
{
return 0;
}
}
public static bool HasAllocations(int jobId, int userId)
{
bool retval = false;
SqlDatabase db = new SqlDatabase(connString);
DbCommand cmd = db.GetSqlStringCommand(@" SELECT COUNT(AllocationId)
FROM Allocations
WHERE UserId=@user_id AND JobId=@job_id AND WeekNumber>=dbo.fnGetWeekNumber()+1");
db.AddInParameter(cmd, "@job_id", DbType.Int32, jobId);
db.AddInParameter(cmd, "@user_id", DbType.Int32, userId);
object result = db.ExecuteScalar(cmd);
if (result != null && result != DBNull.Value)
{
if(Convert.ToInt32(result)>0)
retval = true;
}
cmd.Dispose();
return retval;
}
/// <summary>
///
/// </summary>
/// <param name="jobId"></param>
/// <param name="userId"></param>
/// <param name="existing"></param>
/// <returns>0 if user being unassigned has never billed time to the job,
/// 1 if user has billed time to the job</returns>
public static int Unassign(int jobId, int userId, string existing)
{
SqlDatabase db = new SqlDatabase(connString);
DbCommand cmd = null;
if (existing == "delete")
{
cmd = db.GetSqlStringCommand(@" DELETE FROM Allocations WHERE UserId=@user_id AND JobId=@job_id");
db.AddInParameter(cmd, "@user_id", DbType.Int32, userId);
db.AddInParameter(cmd, "@job_id", DbType.Int32, jobId);
db.ExecuteNonQuery(cmd);
}
else if (existing == "move")
{
MoveAllocations(userId, jobId);
}
int retval = 0;
cmd = db.GetStoredProcCommand("ALOC_Unassign");
db.AddInParameter(cmd, "@job_id", DbType.Int32, jobId);
db.AddInParameter(cmd, "@user_id", DbType.Int32, userId);
object result = db.ExecuteScalar(cmd);
if (result != null && result != DBNull.Value)
retval = Convert.ToInt32(result);
cmd.Dispose();
return retval;
}
private static void Transaction(SqlDatabase database, DbCommand command)
{
using (var scope = new TransactionScope(TransactionScopeOption.RequiresNew))
{
database.ExecuteNonQuery(CommandType.Text,
@"INSERT INTO [dbo].[Region] ([RegionID], [RegionDescription]) VALUES (5, N'Middle')");
database.ExecuteNonQuery(CommandType.Text, "DELETE [dbo].[Region] WHERE [RegionID]=5");
var count = database.ExecuteScalar(CommandType.Text, "SELECT COUNT(*) FROM Employees");
}
}
private static void MoveAllocations(int userId, int jobId)
{
SqlDatabase db = new SqlDatabase(connString);
DbCommand cmd = db.GetSqlStringCommand(@"SELECT UserId
FROM AllocableUsers
WHERE DeptId=(SELECT DeptId FROM AllocableUsers WHERE UserId=@user_id)
AND FirstName='TBD' AND realPerson='N'");
db.AddInParameter(cmd, "@user_id", DbType.Int32, userId);
object val = db.ExecuteScalar(cmd);
int tbdUserId = -1;
if (val != null && val != DBNull.Value)
tbdUserId = Convert.ToInt32(val);
if (tbdUserId < 0)
throw new Exception("Could not find TBD user in same department as user with userid " + userId);
cmd = db.GetSqlStringCommand(@" SELECT AllocationId, WeekNumber
FROM Allocations
WHERE JobId=@job_id AND UserId=@tbd_user AND WeekNumber>=dbo.fnGetWeekNumber()+1");
db.AddInParameter(cmd, "@job_id", DbType.Int32, jobId);
db.AddInParameter(cmd, "@tbd_user", DbType.Int32, tbdUserId);
DataTable tbdAllocs = db.ExecuteDataSet(cmd).Tables[0];
//
foreach (DataRow row in tbdAllocs.Rows)
{
//add the existing user's alloc'd minutes to these
//TODO: why would this ever try to put NULL into AnyMins???
cmd = db.GetSqlStringCommand(@" UPDATE Allocations
SET AnyMins=IsNull(AnyMins,0)+IsNull((SELECT IsNull(AnyMins,0) FROM Allocations
WHERE UserId=@user_id AND JobId=@job_id AND WeekNumber=@week_num),0)
WHERE AllocationId=@alloc_id");
db.AddInParameter(cmd, "@user_id", DbType.Int32, userId);
db.AddInParameter(cmd, "@job_id", DbType.Int32, jobId);
db.AddInParameter(cmd, "@week_num", DbType.Int32, row["WeekNumber"]);
db.AddInParameter(cmd, "@alloc_id", DbType.Int32, row["AllocationId"]);
db.ExecuteNonQuery(cmd);
//now delete the allocation for the user
cmd = db.GetSqlStringCommand(@" DELETE FROM Allocations WHERE UserId=@user_id AND JobId=@job_id AND WeekNumber=@week_num");
db.AddInParameter(cmd, "@user_id", DbType.Int32, userId);
db.AddInParameter(cmd, "@job_id", DbType.Int32, jobId);
db.AddInParameter(cmd, "@week_num", DbType.Int32, row["WeekNumber"]);
db.ExecuteNonQuery(cmd);
}
//we've now moved any colliding allocation records...do an update of the user-id on the rest
cmd = db.GetSqlStringCommand(@" UPDATE Allocations
SET UserId=@tbd_id, AllocNote=''
WHERE JobId=@job_id AND UserId=@user_id AND WeekNumber>=dbo.fnGetWeekNumber()+1");
db.AddInParameter(cmd, "@tbd_id", DbType.Int32, tbdUserId);
db.AddInParameter(cmd, "@job_id", DbType.Int32, jobId);
db.AddInParameter(cmd, "@user_id", DbType.Int32, userId);
db.ExecuteNonQuery(cmd);
}
/// <summary>
/// Executes the scalar.
/// </summary>
/// <param name="commandType">Type of the command.</param>
/// <param name="commandText">The command text.</param>
/// <returns></returns>
public override object ExecuteScalar(CommandType commandType, string commandText)
{
SqlDatabase database = new SqlDatabase(this._connectionString);
return database.ExecuteScalar(commandType, commandText);
}
/// <summary>
/// Executes the scalar.
/// </summary>
/// <param name="commandWrapper">The command wrapper.</param>
/// <returns></returns>
public override object ExecuteScalar(DbCommand commandWrapper)
{
SqlDatabase database = new SqlDatabase(this._connectionString);
return database.ExecuteScalar(commandWrapper);
}
/// <summary>
/// Executes the scalar.
/// </summary>
/// <param name="storedProcedureName">Name of the stored procedure.</param>
/// <param name="parameterValues">The parameter values.</param>
/// <returns></returns>
public override object ExecuteScalar(string storedProcedureName, params object[] parameterValues)
{
SqlDatabase database = new SqlDatabase(this._connectionString);
return database.ExecuteScalar(storedProcedureName, parameterValues);
}
public override string ResetPassword(string username, string answer)
{
if (!EnablePasswordReset)
{
throw new NotSupportedException("El reseteo de password no está disponible.");
}
if (answer == null && RequiresQuestionAndAnswer)
{
throw new ProviderException("La pregunta de seguridad es obligatoria.");
}
string newPassword = Membership.GeneratePassword(NewPasswordLength, _minRequiredNonAlphanumericCharacters);
ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPassword, true);
OnValidatingPassword(args);
if (args.Cancel)
{
if (args.FailureInformation != null)
{
throw args.FailureInformation;
}
throw new MembershipPasswordException(
"El reseteo del password ha sido cancelado debido a un error en la validación del usuario..");
}
SqlDatabase sqlDatabase = new SqlDatabase(_connectionString);
DbCommand dbCommand = sqlDatabase.GetStoredProcCommand("adm.SCISP_ResetearPasswordUsuario");
sqlDatabase.AddInParameter(dbCommand, "Aplicacion", DbType.String, _applicationName);
sqlDatabase.AddInParameter(dbCommand, "Login", DbType.String, username);
sqlDatabase.AddInParameter(dbCommand, "NuevoPassword", DbType.String, Encode(newPassword));
sqlDatabase.AddInParameter(dbCommand, "Respuesta", DbType.String, Encode(answer));
sqlDatabase.AddInParameter(dbCommand, "IntentosPassword", DbType.Int32, _passwordAttemptWindow);
sqlDatabase.AddInParameter(dbCommand, "MaximoIntentosFallidos", DbType.Int32, _maxInvalidPasswordAttempts);
int resultado = (int) sqlDatabase.ExecuteScalar(dbCommand);
switch (resultado)
{
case 0:
return newPassword;
case 1:
throw new MembershipPasswordException("El usuario no existe.");
case 2:
throw new MembershipPasswordException("El usuario se encuentra bloqueado.");
case 3:
throw new MembershipPasswordException("El usuario no tiene una respuesta establecida.");
case 4:
throw new MembershipPasswordException("Respuesta incorrecta.");
}
throw new MembershipPasswordException(
"Usuario no existe, o el usuario está bloqueado. El password no ha sido reseteado.");
}
public override int GetNumberOfUsersOnline()
{
TimeSpan onlineSpan = new TimeSpan(0, Membership.UserIsOnlineTimeWindow, 0);
DateTime compareTime = DateTime.Now.Subtract(onlineSpan);
SqlDatabase sqlDatabase = new SqlDatabase(_connectionString);
DbCommand dbCommand = sqlDatabase.GetStoredProcCommand("adm.SCISP_ObtenerTotalUsuariosEnLinea");
sqlDatabase.AddInParameter(dbCommand, "Aplicacion", DbType.String, _applicationName);
sqlDatabase.AddInParameter(dbCommand, "FechaComparacion", DbType.DateTime, compareTime);
int totalUsuariosEnLinea = (int) sqlDatabase.ExecuteScalar(dbCommand);
return totalUsuariosEnLinea;
}
