//delete dock
public static Dock DelDock(int ID)
{
SqlConnection conn = new SqlConnection();
Dock DockObj = new Dock();
try
{
conn = MariaDB.GetConnection();
string sql = "DELETE FROM [dbo].[Dock]" +
" WHERE [ID]=@ID ";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@ID", ID);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("Error :" + ex.ToString());
}
finally
{
conn.Close();
}
return(DockObj);
}
public static Dock GetDock(int ID)
{
SqlConnection conn = new SqlConnection();
Dock DockObj = new Dock();
try
{
conn = MariaDB.GetConnection();
string sql = "SELECT [ID],[Name],[WaterService],[ElectricalService] FROM [dbo].[Dock]" +
" WHERE [ID]=@ID ";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
cmd.Parameters.AddWithValue("@ID", ID);
while (dr.Read())
{
DockObj.ID = Convert.ToInt32(dr["ID"]);
DockObj.Name = dr["Name"].ToString();
DockObj.WaterService = Convert.ToBoolean(dr["WaterService"]);
DockObj.ElectricalService = Convert.ToBoolean(dr["ElectricalService"]);
}
}
catch (Exception ex)
{
MessageBox.Show("Error :" + ex.ToString());
}
finally
{
conn.Close();
}
return(DockObj);
}
public static DataTable GetSlipByDockID(int DockID)
{
SqlConnection conn = new SqlConnection();
DataTable dt = new DataTable();
try
{
conn = MariaDB.GetConnection();
string sql = "SELECT [Slip].[ID] AS SlipID,[Slip].[Width],[Slip].[Length],[Slip].[DockID],[Dock].[Name] AS DockName" +
" FROM [dbo].[Slip]" +
" JOIN [dbo].[Dock] ON [dbo].[Dock].[ID] = [dbo].[Slip].[DockID]" +
" WHERE [Slip].[ID] NOT IN(SELECT[Lease].[SlipID] FROM [dbo].[Lease] WHERE [Lease].[SlipID] = [Slip].[ID])" +
" AND [Slip].[DockID] != @DockID";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@DockID", DockID);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
}
catch (Exception ex)
{
MessageBox.Show("Error : " + ex.ToString());
}
finally
{
conn.Close();
}
return(dt);
}
public static Customer GetCustomer(int ID)
{
SqlConnection conn = new SqlConnection();
Customer CustomerObj = new Customer();
try
{
conn = MariaDB.GetConnection();
string sql = "SELECT [ID],[FirstName],[LastName],[Phone],[City],[UserName],[Password] FROM [dbo].[Customer]" +
" WHERE [ID]= " + ID;
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (dr.Read())
{
CustomerObj.ID = Convert.ToInt32(dr["ID"]);
CustomerObj.FirstName = dr["FirstName"].ToString();
CustomerObj.LastName = Convert.ToString(dr["LastName"]);
CustomerObj.Phone = dr["Phone"].ToString();
CustomerObj.City = dr["City"].ToString();
CustomerObj.UserName = dr["UserName"].ToString();
CustomerObj.Password = dr["Password"].ToString();
}
}
catch (Exception ex)
{
MessageBox.Show("Error :" + ex.ToString());
}
finally
{
conn.Close();
}
return(CustomerObj);
}
public static DataSet GetAllLease(int inCustomerID)
{
SqlConnection conn = new SqlConnection();
DataSet ds = new DataSet();
try
{
conn = MariaDB.GetConnection();
string sql = "SELECT [Lease].[ID],[Slip].[Width],[Slip].[Length],[Slip].[DockID],[SlipID],[CustomerID],Customer.FirstName + ' ' + Customer.LastName AS FullName, [Dock].Name" +
" FROM [dbo].[Lease] " +
" JOIN [dbo].[Customer] ON [dbo].[Customer].ID = [dbo].[Lease].CustomerID " +
" JOIN [dbo].[Slip] ON [dbo].[Slip].ID = [dbo].[Lease].SlipID " +
" JOIN [dbo].[Dock] ON [dbo].[Dock].ID = [dbo].[Slip].DockID " +
" WHERE [CustomerID] = @CustomerID ";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@CustomerID", inCustomerID);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(ds);
}
catch (Exception ex) {
MessageBox.Show("Error : " + ex.ToString());
}
finally
{
conn.Close();
}
return(ds);
}
//Update dock
public static void UpdateDock(int intid, string name, bool waterService, bool electricalService)
{
Int32 inID = 0;
SqlConnection conn = new SqlConnection();
try
{
conn = MariaDB.GetConnection();
string sql = "UPDATE [dbo].[Dock]" +
" ( [Name],[WaterService],[ElectricalService]) " +
" VALUES(@Name,@WaterService,@ElectricalService) WHERE ID=@intId ";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@intId", intid);
cmd.Parameters.AddWithValue("@Name", name);
cmd.Parameters.AddWithValue("@WaterService", waterService);
cmd.Parameters.AddWithValue("@ElectricalService", electricalService);
inID = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("Error :" + ex.ToString());
}
finally
{
conn.Close();
}
}
public static Int32 AddCustomer(Customer objCustomer)
{
Int32 inCustomerId = 0;
SqlConnection conn = new SqlConnection();
try
{
conn = MariaDB.GetConnection();
string sql = "INSERT INTO [dbo].[Customer]" +
" ([FirstName],[LastName],[Phone],[City],[UserName],[Password]) " +
" VALUES(@FirstName,@LastName,@Phone,@City,@UserName,@Password)";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@FirstName", objCustomer.FirstName);
cmd.Parameters.AddWithValue("@LastName", objCustomer.LastName);
cmd.Parameters.AddWithValue("@Phone", objCustomer.Phone);
cmd.Parameters.AddWithValue("@City", objCustomer.City);
cmd.Parameters.AddWithValue("@UserName", objCustomer.UserName);
cmd.Parameters.AddWithValue("@Password", objCustomer.Password);
inCustomerId = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("Error : " + ex.ToString());
}
finally
{
conn.Close();
}
return(inCustomerId);
}
public static Int32 UpdateCustomer(Customer objCustomer)
{
Int32 inSupplierId = 0;
SqlConnection conn = new SqlConnection();
try
{
conn = MariaDB.GetConnection();
string sql = "UPDATE [dbo].[Customer] SET" +
" FirstName = @FirstName" +
" LastName = @LastName" +
" Phone = @Phone" +
" City = @City" +
" WHERE ID = @ID";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@ID", objCustomer.ID);
cmd.Parameters.AddWithValue("@FirstName", objCustomer.FirstName);
cmd.Parameters.AddWithValue("@LastName", objCustomer.LastName);
cmd.Parameters.AddWithValue("@Phone", objCustomer.Phone);
cmd.Parameters.AddWithValue("@City", objCustomer.City);
inSupplierId = cmd.ExecuteNonQuery();
}
catch (Exception ex) { }
finally
{
conn.Close();
}
return(inSupplierId);
}
public static Int32 AddSlip(int ID, string Width, string Length, string DockID)
{
Int32 inID = 0;
SqlConnection conn = new SqlConnection();
try
{
conn = MariaDB.GetConnection();
string sql = "INSERT INTO [dbo].[Slip]" +
" ([ID],[Width],[Length],[DockID]) " +
" VALUES(@ID,@Width,@Length,@DockID)";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@ID", ID);
cmd.Parameters.AddWithValue("@Width", Width);
cmd.Parameters.AddWithValue("@Length", Length);
cmd.Parameters.AddWithValue("@DockID", DockID);
inID = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("Error : " + ex.ToString());
}
finally
{
conn.Close();
}
return(inID);
}
/*
* public static DataSet GetAllDock()
* {
* SqlConnection conn = new SqlConnection();
* DataSet ds = new DataSet();
* try
* {
* conn = MariaDB.GetConnection();
* string sql = "SELECT [ID],[Name],[WaterService],[ElectricalService] FROM [dbo].[Dock]";
* SqlCommand cmd = new SqlCommand(sql, conn);
* SqlDataAdapter sda = new SqlDataAdapter(cmd);
* sda.Fill(ds);
* }
* catch (Exception ex) { }
* finally
* {
* conn.Close();
* }
* return ds;
* }
*/
public static List <Dock> GetAllDock()
{
List <Dock> dockList = new List <Dock>();
string sql = " SELECT [ID], [Name], [WaterService], [ElectricalService] FROM [dbo].[Dock] ";
using (SqlConnection con = new SqlConnection(MariaDB.GetConnectionString()))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
Dock dock;
while (dr.Read())
{
dock = new Dock();
dock.ID = Convert.ToInt32(dr["ID"]);
dock.Name = dr["Name"].ToString();
dock.WaterService = Convert.ToBoolean(dr["WaterService"]);
dock.ElectricalService = Convert.ToBoolean(dr["ElectricalService"]);
dockList.Add(dock);
}
dr.Close();
}
}
return(dockList);
}
public static Slip GetSlip(int ID)
{
SqlConnection conn = new SqlConnection();
Slip SlipObj = new Slip();
try
{
conn = MariaDB.GetConnection();
string sql = "SELECT [ID],[Width],[Length],[DockID] FROM [dbo].[Slip]" +
" WHERE [ID]= " + ID;
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (dr.Read())
{
SlipObj.ID = Convert.ToInt32(dr["ID"]);
SlipObj.Width = Convert.ToInt32(dr["Width"].ToString());
SlipObj.Length = Convert.ToInt32(dr["Length"]);
SlipObj.DockID = Convert.ToInt32(dr["DockID"]);
}
}
catch (Exception ex)
{
MessageBox.Show("Error : " + ex.ToString());
}
finally
{
conn.Close();
}
return(SlipObj);
}
public static List <SlipAndLease> GetAllHistoryLease()
{
//get customerID from session
int inCustomerID = Convert.ToInt32(HttpContext.Current.Session["CustomerID"]);
SqlConnection conn = new SqlConnection();
//DataSet ds = new DataSet();
List <SlipAndLease> lstSlipLease = new List <SlipAndLease>();
SlipAndLease SlipLeaseObj;
try
{
conn = MariaDB.GetConnection();
string sql = "SELECT [Lease].[ID] as LeaseID,[Slip].[Width],[Slip].[Length],[Slip].[DockID],[Dock].Name as Dockname,[Dock].WaterService as DockWaterService, [Dock].ElectricalService as DockElectricalService, [SlipID],[CustomerID],Customer.FirstName + ' ' + Customer.LastName AS FullName " +
" FROM [dbo].[Lease] " +
" JOIN [dbo].[Customer] ON [dbo].[Customer].ID = [dbo].[Lease].CustomerID " +
" JOIN [dbo].[Slip] ON [dbo].[Slip].ID = [dbo].[Lease].SlipID " +
" JOIN [dbo].[Dock] ON [dbo].[Dock].ID = [dbo].[Slip].DockID " +
" WHERE [CustomerID] = @CustomerID ";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@CustomerID", inCustomerID);
SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (dr.Read())
{
SlipLeaseObj = new SlipAndLease();
SlipLeaseObj.LeaseID = Convert.ToInt32(dr["ID"]);
SlipLeaseObj.Width = Convert.ToInt32(dr["Width"]);
SlipLeaseObj.Length = Convert.ToInt32(dr["Length"]);
SlipLeaseObj.DockID = Convert.ToInt32(dr["DockID"]);
SlipLeaseObj.DockName = Convert.ToString(dr["DockName"]);
SlipLeaseObj.WaterService = Convert.ToBoolean(dr["DockWaterService"]);
SlipLeaseObj.ElectricalService = Convert.ToBoolean(dr["DockElectricalService"]);
SlipLeaseObj.SlipID = Convert.ToInt32(dr["SlipID"]);
SlipLeaseObj.CustomerID = Convert.ToInt32(dr["CustomerID"]);
SlipLeaseObj.FullName = Convert.ToString(dr["FullName"]);
lstSlipLease.Add(SlipLeaseObj);
}
//SqlDataAdapter sda = new SqlDataAdapter(cmd);
//sda.Fill(ds);
}
catch (Exception ex)
{
MessageBox.Show("Error : " + ex.ToString());
}
finally
{
conn.Close();
}
return(lstSlipLease);
}
//get customerID and CustomerFullName based on User.Identity.Name
public static List <Customer> GetCustomerIDFullName()
{
List <Customer> lstCust = new List <Customer>();
//string Username = User.Identity.Name;
string username = Convert.ToString(HttpContext.Current.Session["UserName"]);
MessageBox.Show("username = "******"SELECT ID, FirstName, LastName FROM [dbo].[Customer]" +
" WHERE UserName = @UserName ";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@UserName", username.ToString());
SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (dr.Read())
{
CustomerObj.ID = Convert.ToInt32(dr["ID"]);
CustomerObj.FirstName = dr["FirstName"].ToString();
CustomerObj.LastName = Convert.ToString(dr["LastName"]);
/*
* CustomerObj.Phone = dr["Phone"].ToString();
* CustomerObj.City = dr["City"].ToString();
* CustomerObj.UserName = dr["UserName"].ToString();
* CustomerObj.Password = dr["Password"].ToString();
*/
lstCust.Add(CustomerObj);
}
}
catch (Exception ex)
{
MessageBox.Show("Error: " + ex.ToString());
}
finally
{
conn.Close();
}
}
return(lstCust);
}
public static Int32 AddCustomer(int ID, string FirstName, string LastName, string Phone, string City, string UserName, string Password)
{
/*
* MessageBox.Show("FirstName=" + FirstName);
* MessageBox.Show("LastName=" + LastName);
* MessageBox.Show("Phone=" + Phone);
* MessageBox.Show("City=" + City);
* MessageBox.Show("UserName="******"Password="******"INSERT INTO [dbo].[Customer]" +
" ([FirstName],[LastName],[Phone],[City],[UserName],[Password]) " +
" VALUES( @FirstName1, @LastName1 , @Phone1, @City1, @UserName1, @Password1) ";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@FirstName1", FirstName.ToString());
cmd.Parameters.AddWithValue("@LastName1", LastName.ToString());
cmd.Parameters.AddWithValue("@Phone1", Phone.ToString());
cmd.Parameters.AddWithValue("@City1", City.ToString());
cmd.Parameters.AddWithValue("@UserName1", UserName.ToString());
cmd.Parameters.AddWithValue("@Password1", PasswordHashed(Password).ToString());
inSupplierID = cmd.ExecuteNonQuery();
//if succeeded, it should set a CustomerID SESSION for other programs to use
if (inSupplierID >= 1)
{
string sql1 = "select ID from [dbo].[Customer] where UserName = @UserName1 and Password = @Password1 ";
SqlCommand cmd1 = new SqlCommand(sql1, conn);
cmd.Parameters.AddWithValue("@UserName1", UserName.ToString());
cmd.Parameters.AddWithValue("@Password1", PasswordHashed(Password).ToString());
int CustomerId = (Int32)cmd1.ExecuteScalar();
System.Web.HttpContext.Current.Session["CustomerID"] = Convert.ToString(CustomerId);
System.Web.HttpContext.Current.Session["CustomerFirstname"] = FirstName;
System.Web.HttpContext.Current.Session["CustomerLastName"] = LastName;
System.Web.HttpContext.Current.Session["CustomerFullName"] = FirstName + " " + LastName;
}
}
catch (Exception ex) {
MessageBox.Show("Error : " + ex.ToString());
}
finally
{
conn.Close();
}
return(inSupplierID);
}
public static List <SlipDock> GetAllHoldLease()
{
//get CustomerID vaia SESSION
SqlConnection conn = new SqlConnection();
List <SlipDock> lstSlip = new List <SlipDock>();
SlipDock SlipDockObj;
//DataSet ds = new DataSet();
try
{
conn = MariaDB.GetConnection();
string sql = "SELECT [Slip].[ID] AS SlipID,[Slip].[Width],[Slip].[Length],[Slip].[DockID] ,[Dock].[Name] AS DockName, [Dock].[WaterService] as DockWaterService, [Dock].[ElectricalService] as DockElectricalService " +
" FROM [dbo].[Slip]" +
" JOIN [dbo].[Dock] ON [dbo].[Dock].[ID] = [dbo].[Slip].[DockID]" +
" WHERE [Slip].[ID] NOT IN(SELECT[Lease].[SlipID] FROM [dbo].[Lease] WHERE [Lease].[SlipID] = [Slip].[ID])";
SqlCommand cmd = new SqlCommand(sql, conn);
//cmd.Parameters.AddWithValue("@CustomerID", inCustomerID);
SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (dr.Read())
{
SlipDockObj = new SlipDock();
SlipDockObj.ID = Convert.ToInt32(dr["SlipID"]);
SlipDockObj.Width = Convert.ToInt32(dr["Width"]);
SlipDockObj.Length = Convert.ToInt32(dr["Length"]);
SlipDockObj.DockID = Convert.ToInt32(dr["DockID"]);
SlipDockObj.DockName = Convert.ToString(dr["DockName"]);
SlipDockObj.WaterService = Convert.ToBoolean(dr["DockWaterService"]);
SlipDockObj.ElectricalService = Convert.ToBoolean(dr["DockElectricalService"]);
lstSlip.Add(SlipDockObj);
}
//SqlDataAdapter sda = new SqlDataAdapter(cmd);
//sda.Fill(ds);
}
catch (Exception ex)
{
MessageBox.Show("Error : " + ex.ToString());
}
finally
{
conn.Close();
}
return(lstSlip);
}
public static SlipAndLease GetSlipAndLeaseByID(string SlipID)
{
//write one record into hold table.
WriteSlipAndLeaseByID(SlipID);
SlipAndLease product = null;
string sql = "SELECT [Slip].[ID] as SlipID,[Slip].[Width],[Slip].[Length],[Slip].[DockID],[Dock].Name as Dockname,[Dock].WaterService as DockWaterService, [Dock].ElectricalService as DockElectricalService " + //, [Customer].[ID] as CustomerID, Customer.FirstName + ' ' + Customer.LastName AS FullName " +
" FROM [dbo].[Slip] " +
//" JOIN [dbo].[Customer] ON [dbo].[Customer].ID = [dbo].[Lease].CustomerID " +
//" JOIN [dbo].[Slip] ON [dbo].[Slip].ID = [dbo].[Lease].SlipID " +
" JOIN [dbo].[Dock] ON [dbo].[Dock].ID = [dbo].[Slip].DockID " +
" WHERE [Slip].[ID] = @SlipID ";
using (SqlConnection con = new SqlConnection(MariaDB.GetConnectionString()))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
int SlipID1 = Convert.ToInt32(SlipID);
cmd.Parameters.AddWithValue("@SlipID", SlipID1);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
product = new SlipAndLease();
product.SlipID = Convert.ToInt32(dr["SlipID"]);
product.Width = Convert.ToInt32(dr["Width"]);
product.Length = Convert.ToInt32(dr["Length"]);
product.DockID = Convert.ToInt32(dr["DockID"]);
product.DockName = dr["DockName"].ToString();
product.WaterService = Convert.ToBoolean(dr["DockWaterService"]);
product.ElectricalService = Convert.ToBoolean(dr["DockElectricalService"]);
//product.CustomerID = Convert.ToInt32(dr["CustomerID"]);
//product.FullName = Convert.ToString(dr["FullName"]);
}
dr.Close();
}
}
return(product);
}
public static Int32 AddLease(Lease objLease)
{
Int32 inLeaseId = 0;
SqlConnection conn = new SqlConnection();
try
{
conn = MariaDB.GetConnection();
string sql = "INSERT INTO [dbo].[Lease]" +
" ([SlipID],[CustomerID]) " +
" VALUES(@SlipID,@CustomerID)";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@SlipID", objLease.SlipID);
cmd.Parameters.AddWithValue("@CustomerID", objLease.CustomerID);
inLeaseId = cmd.ExecuteNonQuery();
}
catch (Exception ex) { }
finally
{
conn.Close();
}
return inLeaseId;
}
public static Int32 DeleteCustomer(Int32 ID)
{
Int32 inSupplierId = 0;
SqlConnection conn = new SqlConnection();
try
{
conn = MariaDB.GetConnection();
string sql = "DELETE FROM [dbo].[Customer]" +
" WHERE ID = @ID";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@ID", ID);
inSupplierId = cmd.ExecuteNonQuery();
}
catch (Exception ex) { }
finally
{
conn.Close();
}
return(inSupplierId);
}
public static List <Slip> GetAllAvailableDock()
{
SqlConnection conn = new SqlConnection();
List <Slip> lstSlip = new List <Slip>();
Slip SlipObj;
try
{
conn = MariaDB.GetConnection();
string sql = "SELECT [Slip].[ID] AS SlipID,[Slip].[Width],[Slip].[Length],[Slip].[DockID] " + //,[Dock].[Name] AS DockName" +
" FROM [dbo].[Slip]" +
" JOIN [dbo].[Dock] ON [dbo].[Dock].[ID] = [dbo].[Slip].[DockID]" +
" WHERE [Slip].[ID] NOT IN(SELECT[Lease].[SlipID] FROM [dbo].[Lease] WHERE [Lease].[SlipID] = [Slip].[ID])";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (dr.Read())
{
SlipObj = new Slip();
SlipObj.ID = Convert.ToInt32(dr["SlipID"]);
SlipObj.Width = Convert.ToInt32(dr["Width"]);
SlipObj.Length = Convert.ToInt32(dr["Length"]);
SlipObj.DockID = Convert.ToInt32(dr["DockID"]);
//SlipObj.DockName = Convert.ToString(dr["DockName"]);
lstSlip.Add(SlipObj);
}
}
catch (Exception ex) {
MessageBox.Show("Error : " + ex.ToString());
}
finally
{
conn.Close();
}
return(lstSlip);
}
//copy data from AspNetUsers to Customer
public static void CopyDataFromAspNetUsersToCustomers()
{
//put Customer data into a List
List <Customer> customers = new List <Customer>();
Customer CustomerObj = new Customer();
SqlConnection conn = new SqlConnection();
try
{
conn = MariaDB.GetConnection();
string sql = "SELECT [ID],[FirstName],[LastName],[Phone],[City],[UserName],[Password] FROM [dbo].[Customer]";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (dr.Read())
{
CustomerObj.ID = Convert.ToInt32(dr["ID"]);
CustomerObj.FirstName = dr["FirstName"].ToString();
CustomerObj.LastName = Convert.ToString(dr["LastName"]);
CustomerObj.Phone = dr["Phone"].ToString();
CustomerObj.City = dr["City"].ToString();
CustomerObj.UserName = dr["UserName"].ToString();
CustomerObj.Password = dr["Password"].ToString();
customers.Add(CustomerObj);
}
}
catch (Exception ex) {
MessageBox.Show("Error : " + ex.ToString());
}
finally
{
conn.Close();
}
//put AspNetUsers data into a List
List <AspNetUser> aspnetUsers = new List <AspNetUser>();
AspNetUser aspnetuserobj = new AspNetUser();
conn = new SqlConnection();
try
{
conn = MariaDB.GetConnection();
string sql = "SELECT [Id],[FirstName],[LastName],[Phone],[City],[UserName],[Password] FROM [dbo].[AspNetUsers]";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (dr.Read())
{
aspnetuserobj.Id = Convert.ToInt32(dr["Id"]);
aspnetuserobj.FirstName = dr["FirstName"].ToString();
aspnetuserobj.LastName = Convert.ToString(dr["LastName"]);
aspnetuserobj.Phone = dr["Phone"].ToString();
aspnetuserobj.City = dr["City"].ToString();
aspnetuserobj.UserName = dr["UserName"].ToString();
aspnetuserobj.PasswordHash = dr["PasswordHash"].ToString();
aspnetUsers.Add(aspnetuserobj);
}
}
catch (Exception ex)
{
MessageBox.Show("Error : " + ex.ToString());
}
finally
{
conn.Close();
}
//compare two lists, if username and password are identical, then ignore, if not, add the record into Customers
for (int i = 0; i < aspnetUsers.Count; i++)
{
for (int j = 0; j < customers.Count; j++)
{
//if(customers[j][5] == )
}
}
}
public static SlipAndLease WriteSlipAndLeaseByID(string SlipID)
{
//read info from slip table
SlipAndLease product = null;
string sql = "SELECT [Slip].[ID] as SlipID,[Slip].[Width],[Slip].[Length],[Slip].[DockID],[Dock].Name as Dockname,[Dock].WaterService as DockWaterService, [Dock].ElectricalService as DockElectricalService " + //, [Customer].[ID] as CustomerID, Customer.FirstName + ' ' + Customer.LastName AS FullName " +
" FROM [dbo].[Slip] " +
//" JOIN [dbo].[Customer] ON [dbo].[Customer].ID = [dbo].[Lease].CustomerID " +
//" JOIN [dbo].[Slip] ON [dbo].[Slip].ID = [dbo].[Lease].SlipID " +
" JOIN [dbo].[Dock] ON [dbo].[Dock].ID = [dbo].[Slip].DockID " +
" WHERE [Slip].[ID] = @SlipID ";
using (SqlConnection con = new SqlConnection(MariaDB.GetConnectionString()))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
int SlipID1 = Convert.ToInt32(SlipID);
cmd.Parameters.AddWithValue("@SlipID", SlipID1);
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
{
product = new SlipAndLease();
product.SlipID = Convert.ToInt32(dr["SlipID"]);
product.Width = Convert.ToInt32(dr["Width"]);
product.Length = Convert.ToInt32(dr["Length"]);
product.DockID = Convert.ToInt32(dr["DockID"]);
product.DockName = dr["DockName"].ToString();
product.WaterService = Convert.ToBoolean(dr["DockWaterService"]);
product.ElectricalService = Convert.ToBoolean(dr["DockElectricalService"]);
//product.CustomerID = Convert.ToInt32(dr["CustomerID"]);
//product.FullName = Convert.ToString(dr["FullName"]);
}
dr.Close();
}
}
//get CustomerID and customer FullName from Session
int CustID = Convert.ToInt32(System.Web.HttpContext.Current.Session["CustomerID"]);
string CustFullName = Convert.ToString(System.Web.HttpContext.Current.Session["CustomerFullName"]);
if ((CustID <= 0) || (CustFullName == ""))
{
List <Customer> cust = new List <Customer>();
cust = CustomerDB.GetCustomerIDFullName();
foreach (var cus in cust)
{
CustID = cus.ID;
CustFullName = cus.FirstName + " " + cus.LastName;
}
}
//insert into table
string sql1 = "INSERT INTO [dbo].[SlipAndLease]" +
" ([SlipID],[Width],[Length],[DockID],[DockName],[LeaseID],[CustomerID],[FullName]) " +
" VALUES (@SlipID, @Width, @Length, @DockID, @DockName, @LeaseID, @CustomerID, @FullName) ";
return(product);
}