public bool CreateUser(string firstName, string lastName, string email, string phonenumber, string password)
{
var query = String.Format(@"Insert into Customers (FirstName, LastName, Email, ContactNbr, Password)
Values ('{0}', '{1}', '{2}', '{3}', '{4}');
Select @@Identity as 'Identity'", firstName, lastName, email, phonenumber, password);
using (var cmd = new SqlCommand(query, new SqlConnection(WingtipTicketApp.ConstructConnection(WingtipTicketApp.Config.PrimaryDatabaseServer, WingtipTicketApp.Config.TenantDbName))))
{
using (var sdAdapter = new SqlDataAdapter(cmd))
{
var dsUser = new DataSet();
sdAdapter.Fill(dsUser);
if (dsUser.Tables.Count > 0 && dsUser.Tables[0].Rows.Count > 0)
{
var newUser = new Customer
{
FirstName = firstName,
LastName = lastName,
Email = email,
PhoneNumber = phonenumber,
CustomerId = Convert.ToInt32(dsUser.Tables[0].Rows[0]["Identity"])
};
HttpContext.Current.Session["SessionUser"] = newUser;
Startup.SessionUsers.Add(newUser);
}
}
}
VenuesDbContext.LogAction("Added new user - " + firstName + " " + lastName);
return(true);
}
public List <Customer> GetUsers()
{
var customers = new List <Customer>();
var query = String.Format(@"Select CustomerId, FirstName, LastName, Email, ContactNbr from Customers Where Email <> '*****@*****.**'");
using (var cmd = new SqlCommand(query, new SqlConnection(WingtipTicketApp.ConstructConnection(WingtipTicketApp.Config.PrimaryDatabaseServer, WingtipTicketApp.Config.TenantDbName))))
{
using (var sdAdapter = new SqlDataAdapter(cmd))
{
var dsUser = new DataSet();
sdAdapter.Fill(dsUser);
if (dsUser.Tables.Count > 0)
{
foreach (DataRow row in dsUser.Tables[0].Rows)
{
customers.Add(new Customer()
{
CustomerId = Convert.ToInt32(row["CustomerId"]),
FirstName = row["FirstName"].ToString(),
LastName = row["LastName"].ToString(),
Email = row["Email"].ToString(),
PhoneNumber = row["ContactNbr"].ToString()
});
}
}
}
}
return(customers);
}
public bool Login(string email, string password)
{
var query = String.Format(@"Select FirstName, LastName, CustomerId from Customers Where Email='{0}' and Password='******'", email, password);
using (var cmd = new SqlCommand(query, new SqlConnection(WingtipTicketApp.ConstructConnection(WingtipTicketApp.Config.PrimaryDatabaseServer, WingtipTicketApp.Config.TenantDbName))))
{
using (var sdAdapter = new SqlDataAdapter(cmd))
{
var dsUser = new DataSet();
sdAdapter.Fill(dsUser);
if (dsUser.Tables.Count > 0 && dsUser.Tables[0].Rows.Count > 0)
{
var newUser = new Customer
{
FirstName = dsUser.Tables[0].Rows[0]["FirstName"].ToString(),
LastName = dsUser.Tables[0].Rows[0]["LastName"].ToString(),
Email = email,
CustomerId = Convert.ToInt32(dsUser.Tables[0].Rows[0]["CustomerId"])
};
HttpContext.Current.Session["SessionUser"] = newUser;
if (Startup.SessionUsers.Any(a => a.Email != null && a.Email.ToUpper() == email.ToUpper()))
{
Startup.SessionUsers.Remove(Startup.SessionUsers.First(a => a.Email.ToUpper() == email.ToUpper()));
}
Startup.SessionUsers.Add(newUser);
}
}
}
return(true);
}
public bool UserExists(string email)
{
using (var conn = new SqlConnection(WingtipTicketApp.ConstructConnection(WingtipTicketApp.Config.PrimaryDatabaseServer, WingtipTicketApp.Config.TenantDbName)))
{
using (var dbReader = new SqlCommand(String.Format(@"Select CustomerId From Customers Where Email={0}", email), conn).ExecuteReader())
{
return(dbReader.HasRows);
}
}
}
public bool RefreshConcerts(bool fullReset)
{
#region Full Reset - Trim Extra Concerts
if (fullReset)
{
var trimSql = ReadSqlFromFile(HttpContext.Current.Server.MapPath("~/SqlScripts/TrimConcerts.sql"));
if (!string.IsNullOrEmpty(trimSql))
{
using (var conn = new SqlConnection(WingtipTicketApp.ConstructConnection(WingtipTicketApp.Config.PrimaryDatabaseServer, WingtipTicketApp.Config.TenantDbName)))
{
conn.Open();
using (var cmd = new SqlCommand(trimSql, conn))
{
cmd.ExecuteNonQuery();
}
}
}
}
#endregion Full Reset - Trim Extra Concerts
#region Push Concert Dates to Future
var resetDatesSql = ReadSqlFromFile(HttpContext.Current.Server.MapPath("~/SqlScripts/ResetConcertDates.sql"));
if (!string.IsNullOrEmpty(resetDatesSql))
{
using (var conn = new SqlConnection(WingtipTicketApp.ConstructConnection(WingtipTicketApp.Config.PrimaryDatabaseServer, WingtipTicketApp.Config.TenantDbName)))
{
conn.Open();
using (var cmd = new SqlCommand(resetDatesSql, conn))
{
cmd.ExecuteNonQuery();
}
}
}
#endregion Push Concert Dates to Future
return(true);
}
private static void CreateIndexer(SearchServiceClient searchServiceClient)
{
if (!searchServiceClient.DataSources.List().Any(d => d.Name == "concertssql"))
{
int test2 = searchServiceClient.DataSources.List().Count();
Debug.WriteLine(test2);
bool test3 = searchServiceClient.DataSources.List().Any(d => d.Name == "concertssql");
Debug.WriteLine(test3);
string connectionString = WingtipTicketApp.ConstructConnection(WingtipTicketApp.Config.PrimaryDatabaseServer,
WingtipTicketApp.Config.TenantDbName);
searchServiceClient.DataSources.CreateOrUpdate(new DataSource
{
Name = "concertssql",
Type = "azuresql",
Container = new DataContainer {
Name = "ConcertSearch"
},
Credentials = new DataSourceCredentials {
ConnectionString = connectionString
},
DataChangeDetectionPolicy = new HighWaterMarkChangeDetectionPolicy("RowVersion")
});
}
if (!searchServiceClient.Indexers.List().Any(i => i.Name == "fromsql"))
{
searchServiceClient.Indexers.CreateOrUpdate(new Indexer
{
Name = "fromsql",
DataSourceName = "concertssql",
TargetIndexName = "concerts",
Schedule = new IndexingSchedule {
Interval = TimeSpan.FromMinutes(5), StartTime = DateTimeOffset.Now
}
});
}
}
private string ConstructTicketsDbConnnectString()
{
return(WingtipTicketApp.ConstructConnection(WingtipTicketApp.Config.PrimaryDatabaseServer, WingtipTicketApp.Config.TenantDbName));
}
