public LcDatabase(LcDatabase db)
{
// Reuse internal connection directly
// (if null is passed in, the other method overload manage it :-)
this.db = db.db;
isShared = true;
}
static DynamicDataReader()
{
Db = Database.OpenConnectionString(ConfigurationManager.ConnectionStrings["UserCenter"].ConnectionString, "System.Data.SqlClient");
Database.ConnectionOpened += (sender, args) =>
{
Diagnostics.Debug.WriteLine(args.Connection);
};
}
public LcDatabase(Database db = null)
{
isShared = db != null;
if (isShared)
{
this.db = db;
}
else
{
this.db = Database.Open("sqlloco");
}
}
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 LcDatabase(LcDatabase db)
{
// Reuse internal connection directly
isShared = db != null;
if (isShared)
{
this.db = db.db;
}
else
{
this.db = Database.Open("sqlloco");
}
}
IPagedEnumerable<dynamic> IPagedDynamicQuery.ExecuteIn(Database db) {
int count;
if(FieldSelectionPattern.IsMatch(baseQuery)) {
var countQuery = BuildCountQuery();
try {
count = db.QueryValue(countQuery.Statement, countQuery.Parameters);
} catch(Exception e) {
throw new DynamicQueryException("Exception when executing COUNT() query '" + countQuery.Statement + "'", e);
}
} else {
count = -1;
}
var skip = Math.Max(0, page - 1)*pageSize.Value;
var items = ExecuteIn(db).Skip(skip).Take(pageSize.Value);
return new PagedEnumerable<dynamic>(items, count, pageSize.Value, page);
}
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);
}
}
public AccountAdminModelBuilder(IDataService dataService)
: base(dataService)
{
db = Database.Open("ConnectDB");
sqlGetAllusers = "SELECT UserId, UserName, Discount FROM UserProfile";
}
public IEnumerable<dynamic> ExecuteIn(Database db) {
var query = pageSize.HasValue
? BuildPagedItemQuery()
: BuildItemQuery();
try {
return db.Query(query.Statement, query.Parameters);
} catch(Exception e) {
throw new DynamicQueryException("Exception when executing query '" + query.Statement + "'", e);
}
}
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);
}
}
private static int FindRoleId(Database db, string roleName) {
var result = db.QuerySingle(@"SELECT RoleId FROM " + RoleTableName + " WHERE (RoleName = @0)", roleName);
if (result == null) {
return -1;
}
return (int)result[0];
}
IPagedEnumerable<dynamic> IPagedDynamicQuery.ExecuteCursorIn(Database db) {
var query = BuildItemQuery();
return new ServerSideSqlCursor(db.Connection, query.Statement, pageSize.Value, page, query.Parameters);
}
public static void RegisterMember(string clientID, string username, WebMatrix.Data.Database db)
{
db.Execute(@"INSERT INTO [master].ClientMembers VALUES (@0, @1)", clientID, username);
}
private List<int> GetUserIdsFromNames(Database db, string[] usernames) {
List<int> userIds = new List<int>(usernames.Length);
foreach (string username in usernames) {
int id = SimpleMembershipProvider.GetUserId(db, SafeUserTableName, SafeUserNameColumn, SafeUserIdColumn, username);
if (id == -1) {
throw new InvalidOperationException(String.Format(CultureInfo.CurrentCulture, WebDataResources.Security_NoUserFound, username));
}
userIds.Add(id);
}
return userIds;
}
public TagRepository()
{
_database = Database.Open("PhotoGallery");
}
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);
}
internal static int GetUserId(Database db, string userTableName, string userNameColumn, string userIdColumn, string userName) {
var result = db.QueryValue(@"SELECT " + userIdColumn + " FROM " + userTableName + " WHERE (UPPER(" + userNameColumn + ") = @0)", userName.ToUpperInvariant());
if (result != null) {
return (int)result;
}
return -1;
}
internal static bool CheckTableExists(Database db, string tableName) {
var query = db.QuerySingle(@"SELECT * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = @0", tableName);
return query != null;
}
public DatabaseProxy(Database database)
{
Condition.Requires(database, "database").IsNotNull();
this.database = database;
}
public AccountRepository()
{
_database = Database.Open("PhotoGallery");
}
public static void releaseDBConnecttion(WebMatrix.Data.Database db)
{
try{ db.Close(); }
catch {}
}
static void TriggerConnectionOpened (Database self, DbConnection connection)
{
EventHandler<ConnectionEventArgs> evt = ConnectionOpened;
if (evt != null)
evt (self, new ConnectionEventArgs (connection));
}
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;
}
public void Setup ()
{
string path = Path.Combine ("Test", "testsqlite.db");
database = Database.OpenConnectionString ("Data Source="+path+";Version=3;", "Mono.Data.Sqlite");
}
private static int GetPasswordFailuresSinceLastSuccess(Database db, int userId) {
var failure = db.QueryValue(@"SELECT PasswordFailuresSinceLastSuccess FROM " + MembershipTableName + " WHERE (UserId = @0)", userId);
if (failure != null) {
return failure;
}
return -1;
}
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);
}
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 List<int> GetRoleIdsFromNames(Database db, string[] roleNames) {
List<int> roleIds = new List<int>(roleNames.Length);
foreach (string role in roleNames) {
int id = FindRoleId(db, role);
if (id == -1) {
throw new InvalidOperationException(String.Format(CultureInfo.CurrentCulture, WebDataResources.SimpleRoleProvider_NoRoleFound, role));
}
roleIds.Add(id);
}
return roleIds;
}
private string GetHashedPassword(Database db, int userId) {
var pwdQuery = db.Query(@"SELECT m.[Password] " +
@"FROM " + MembershipTableName + " m, " + SafeUserTableName + " u " +
@"WHERE m.UserId = " + userId + " AND m.UserId = u." + SafeUserIdColumn).ToList();
// REVIEW: Should get exactly one match, should we throw if we get > 1?
if (pwdQuery.Count != 1) {
return null;
}
return pwdQuery[0].Password;
}
public DatabaseWrapper(Database database)
{
_database = database;
}
// Ensures the user exists in the accounts table
private int VerifyUserNameHasConfirmedAccount(Database db, string username, bool throwException) {
int userId = GetUserId(db, SafeUserTableName, SafeUserNameColumn, SafeUserIdColumn, username);
if (userId == -1) {
if (throwException) {
throw new InvalidOperationException(String.Format(CultureInfo.CurrentCulture, WebDataResources.Security_NoUserFound, username));
}
else {
return -1;
}
}
int result = db.QueryValue(@"SELECT COUNT(*) FROM " + MembershipTableName + " WHERE (UserId = @0 AND IsConfirmed = 1)", userId);
if (result == 0) {
if (throwException) {
throw new InvalidOperationException(String.Format(CultureInfo.CurrentCulture, WebDataResources.Security_NoAccountFound, username));
}
else {
return -1;
}
}
return userId;
}
