public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
{
totalRecords = 0;
MembershipUserCollection users = new MembershipUserCollection();
using (SqlStatement sql = CreateSqlStatement(MembershipProviderSqlStatement.CountAllUsers))
{
totalRecords = Convert.ToInt32(sql.ExecuteScalar());
if (totalRecords <= 0)
{
return(users);
}
}
using (SqlStatement sql = CreateSqlStatement(MembershipProviderSqlStatement.GetAllUsers))
{
int counter = 0;
int startIndex = (pageSize * pageIndex);
int endIndex = ((startIndex + pageSize)
- 1);
while (sql.Read())
{
if (counter >= startIndex)
{
users.Add(GetUser(sql));
}
if (counter >= endIndex)
{
break;
}
counter++;
}
}
return(users);
}
public override MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize, out int totalRecords)
{
MembershipUserCollection users = new MembershipUserCollection();
using (SqlStatement sql = CreateSqlStatement(MembershipProviderSqlStatement.CountUsersByEmail))
{
sql.AssignParameter("UserName", emailToMatch);
totalRecords = Convert.ToInt32(sql.ExecuteScalar());
}
if (totalRecords > 0)
{
int counter = 0;
int startIndex = (pageSize * pageIndex);
int endIndex = ((startIndex + pageSize)
- 1);
using (SqlStatement sql = CreateSqlStatement(MembershipProviderSqlStatement.FindUsersByEmail))
{
sql.AssignParameter("Email", emailToMatch);
while (sql.Read())
{
if (counter >= startIndex)
{
users.Add(GetUser(sql));
}
if (counter >= endIndex)
{
break;
}
counter++;
}
}
}
return(users);
}
public override string GetUserNameByEmail(string email)
{
using (SqlStatement sql = CreateSqlStatement(MembershipProviderSqlStatement.GetUserNameByEmail))
{
sql.AssignParameter("Email", email);
return(Convert.ToString(sql.ExecuteScalar()));
}
}
public override bool RoleExists(string rolename)
{
using (SqlStatement sql = CreateSqlStatement(RoleProviderSqlStatement.RoleExists))
{
sql.AssignParameter("RoleName", rolename);
return(Convert.ToInt32(sql.ExecuteScalar()) > 0);
}
}
public override LastInsertId ExecuteInsert(SqlStatement sql, string idKey)
{
sql.Sql += ";SELECT LAST_INSERT_ID()";
using (sql)
{
var rez = sql.ExecuteScalar();
return new LastInsertId(rez);
}
}
public override LastInsertId ExecuteInsert(SqlStatement sql, string idKey)
{
sql.Sql += ";SELECT LAST_INSERT_ID()";
using (sql)
{
var rez = sql.ExecuteScalar();
return(new LastInsertId(rez));
}
}
public override bool IsUserInRole(string username, string rolename)
{
using (SqlStatement sql = CreateSqlStatement(RoleProviderSqlStatement.IsUserInRole))
{
sql.AssignParameter("UserName", username);
sql.AssignParameter("RoleName", rolename);
return(Convert.ToInt32(sql.ExecuteScalar()) > 0);
}
}
public override LastInsertId ExecuteInsert(SqlStatement sql, string idKey)
{
sql.Sql += ";Select SCOPE_IDENTITY() as id";
using (sql)
{
var rez = sql.ExecuteScalar();
return new LastInsertId(rez);
}
}
public override LastInsertId ExecuteInsert(SqlStatement sql, string idKey)
{
sql.Sql += ";Select SCOPE_IDENTITY() as id";
using (sql)
{
var rez = sql.ExecuteScalar();
return(new LastInsertId(rez));
}
}
public override LastInsertId ExecuteInsert(SqlStatement sql, string idKey)
{
if (!string.IsNullOrEmpty(idKey))
{
sql.Sql += (" returning " + EscapeName(idKey));
}
using (sql)
{
return(new LastInsertId(sql.ExecuteScalar()));
}
}
public override LastInsertId ExecuteInsert(SqlStatement sql, string idKey)
{
if (!string.IsNullOrEmpty(idKey))
{
sql.Sql += (" returning "+EscapeName(idKey));
}
using(sql)
{
return new LastInsertId(sql.ExecuteScalar());
}
}
public override LastInsertId ExecuteInsert(SqlStatement sql, string idKey)
{
if (idKey != null)
{
sql.Sql += ";SELECT last_insert_rowid()";
using (sql)
{
var rez = sql.ExecuteScalar();
return new LastInsertId(rez);
}
}
sql.Execute();
return LastInsertId.Empty;
}
public int Enqueue(string taskType, string payload)
{
const string sql =
"INSERT INTO TaskQueue (Action, Payload) OUTPUT INSERTED.TaskQueueId VALUES (@task, @payload)";
int taskId;
using (var cmd = new SqlStatement(sql, Config.Settings.ConnectionString))
{
cmd.AddParameter("@task", taskType);
cmd.AddParameter("@payload", payload);
taskId = (int)cmd.ExecuteScalar();
}
return(taskId);
}
public override LastInsertId ExecuteInsert(SqlStatement sql, string idKey)
{
if (idKey != null)
{
sql.Sql += ";SELECT last_insert_rowid()";
using (sql)
{
var rez = sql.ExecuteScalar();
return(new LastInsertId(rez));
}
}
sql.Execute();
return(LastInsertId.Empty);
}
public override LastInsertId ExecuteInsert(SqlStatement sql, string idKey)
{
//sql.Sql += ";Select @@IDENTITY as id";
using (sql)
{
sql.Execute();
using (var idquery = new SqlStatement(sql.Db))
{
idquery.SetSql("select @@IDENTITY as id");
var rez = idquery.ExecuteScalar();
return(new LastInsertId(rez));
}
}
}
public override LastInsertId ExecuteInsert(SqlStatement sql, string idKey)
{
//sql.Sql += ";Select @@IDENTITY as id";
using (sql)
{
sql.Execute();
using (var idquery = new SqlStatement(sql.Db))
{
idquery.SetSql("select @@IDENTITY as id");
var rez = idquery.ExecuteScalar();
return new LastInsertId(rez);
}
}
}
public string GetUserInstitution(string username)
{
var connectionString = Config.Settings.ExternalDependency.UserDatabaseConnectionString;
const string sql = "SELECT " +
"usi.name " +
"FROM " +
"US_Institution usi, " +
"US_Users usu " +
"WHERE " +
"usi.PK_InstitutionID = usu.FK_PrimaryInstitutionID " +
"AND " +
"usu.Username = @username";
using (var sqlCommand = new SqlStatement(sql, connectionString))
{
sqlCommand.AddParameter("@username", username);
return((string)sqlCommand.ExecuteScalar());
}
}
