public DynamicQueryTests() {
if(File.Exists(dbFile)) {
File.Delete(dbFile);
}
new SqlCeEngine("Data Source=" + dbFile).CreateDatabase();
db = Database.OpenConnectionString("Data Source="+dbFile);
db.Execute("CREATE TABLE Users ([id] INT IDENTITY(1000,1) PRIMARY KEY, [firstName] NVARCHAR(128), [lastName] NVARCHAR(128))");
db.Execute("INSERT Users ([firstName],[lastName]) VALUES (@0,@1)", "Alice", "Allison");
db.Execute("INSERT Users ([firstName],[lastName]) VALUES (@0,@1)", "Bob", "Bobson");
db.Execute("INSERT Users ([firstName],[lastName]) VALUES (@0,@1)", "Cecil", "Cyrus");
db.Execute("INSERT Users ([firstName],[lastName]) VALUES (@0,@1)", "Donald", "Donaldson");
db.Execute("CREATE TABLE UsersWithFromField ([id] INT IDENTITY(1000,1) PRIMARY KEY, [from] NVARCHAR(128))");
}
public static void UnregisterMember(string clientID, string username, WebMatrix.Data.Database db)
{
db.Execute(@"DELETE FROM [master].ClientMembers WHERE [Client]=@0 AND [Member]=@1", clientID, username);
}
public static void RegisterMember(string clientID, string username, WebMatrix.Data.Database db)
{
db.Execute(@"INSERT INTO [master].ClientMembers VALUES (@0, @1)", clientID, username);
}
public void InsertTest ()
{
string newPath = dbPath + ".tmp";
File.Copy (dbPath, newPath, true);
database = Database.OpenConnectionString ("Data Source="+newPath+";Version=3;", "Mono.Data.Sqlite");
database.Execute ("insert into memos values ('foo', @0);", 42);
Assert.AreEqual (42, database.QueryValue ("select Priority from memos where Text='foo'"));
Assert.AreEqual (6, database.GetLastInsertId ());
File.Delete (newPath);
}
public int Execute(string commandText, params object[] args)
{
return(db.Execute(commandText, args));
}
private bool CheckPassword(Database db, int userId, string password) {
string hashedPassword = GetHashedPassword(db, userId);
bool verificationSucceeded = (hashedPassword != null && Crypto.VerifyHashedPassword(hashedPassword, password));
if (verificationSucceeded) {
// Reset password failure count on successful credential check
db.Execute(@"UPDATE " + MembershipTableName + " SET PasswordFailuresSinceLastSuccess = 0 WHERE (UserId = @0)", userId);
}
else {
int failures = GetPasswordFailuresSinceLastSuccess(db, userId);
if (failures != -1) {
db.Execute(@"UPDATE " + MembershipTableName + " SET PasswordFailuresSinceLastSuccess = @1, LastPasswordFailureDate = @2 WHERE (UserId = @0)", userId, failures + 1, DateTime.UtcNow);
}
}
return verificationSucceeded;
}
private static bool SetPassword(Database db, int userId, string newPassword) {
string hashedPassword = Crypto.HashPassword(newPassword);
if (hashedPassword.Length > 128)
throw new ArgumentException(WebDataResources.SimpleMembership_PasswordTooLong);
// Update new password
int result = db.Execute(@"UPDATE " + MembershipTableName + " SET Password=@0, PasswordSalt=@1, PasswordChangedDate=@2 WHERE UserId = @3", hashedPassword, String.Empty /* salt column is unused */, DateTime.UtcNow, userId);
return result > 0;
}
private void CreateUserRow(Database db, string userName, IDictionary<string, object> values) {
// Make sure user doesn't exist
int userId = GetUserId(db, SafeUserTableName, SafeUserNameColumn, SafeUserIdColumn, userName);
if (userId != -1) {
throw new MembershipCreateUserException(MembershipCreateStatus.DuplicateUserName);
}
StringBuilder columnString = new StringBuilder();
columnString.Append(SafeUserNameColumn);
StringBuilder argsString = new StringBuilder();
argsString.Append("@0");
List<object> argsArray = new List<object>();
argsArray.Add(userName);
if (values != null) {
int index = 1;
foreach (string key in values.Keys) {
// Skip the user name column since we always generate that
if (String.Equals(UserNameColumn, key, StringComparison.OrdinalIgnoreCase)) continue;
columnString.Append(",").Append(key);
argsString.Append(",@").Append(index++);
object value = values[key];
if (value == null) {
value = DBNull.Value;
}
argsArray.Add(value);
}
}
int rows = db.Execute("INSERT INTO " + SafeUserTableName + " (" + columnString.ToString() + ") VALUES (" + argsString.ToString() + ")", argsArray.ToArray());
if (rows != 1) {
throw new MembershipCreateUserException(MembershipCreateStatus.ProviderError);
}
}
private static void DeleteTags(int id, Database db)
{
var sql = "DELETE FROM PostsTagsMap WHERE PostId=@0 ";
db.Execute(sql, id);
}
private static void AddTags(int postId,IEnumerable<int> tags, Database db)
{
if (!tags.Any())
{
return;
}
var sql = "INSERT INTO PostsTagsMap (PostId,TagId) VALUES (@0,@1) ";
foreach (var tag in tags)
{
db.Execute(sql, postId, tag);
}
}
