/*
* 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 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 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);
}