// a method to register a customer(adding to the database):
public static int InsertCustomers(string fname, string lname, string phone, string city)
{
int custID = 0;
// get connected to the database
SqlConnection con = InlandMarinaScriptDB.GetConnection();
// creating the proper sql query to extract data from MS SQL server
string Query = "INSERT INTO Customer (FirstName,LastName, Phone, City) OUTPUT inserted.[ID] " +
"VALUES(@FirstName, @LastName, @Phone, @City)";
// creating the proper command to run the query
SqlCommand comm = new SqlCommand(Query, con);
comm.Parameters.AddWithValue("@FirstName", fname);
comm.Parameters.AddWithValue("@LastName", lname);
comm.Parameters.AddWithValue("@Phone", phone);
comm.Parameters.AddWithValue("@City", city);
// try to run the command
try
{
con.Open();
custID = (int)comm.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return(custID);
}
// a method to get a list of leased slips for a specific customer from the database:
public static List <SlipDock> GetLeases(int Id)
{
// make an empty list of slip-dock
List <SlipDock> LeaseList = new List <SlipDock>();
// make an empty slip-dock object
SlipDock SlDk = new SlipDock();
// get connected to the database
SqlConnection con = InlandMarinaScriptDB.GetConnection();
// creating the proper sql query to extract data from MS SQL server
string Query = "SELECT Dock.ID AS DockID, Name, Slip.ID AS SlipID, Width, Length, WaterService, ElectricalService FROM Slip " +
"INNER JOIN Dock ON DockID = Dock.ID INNER JOIN Lease ON SlipID = Slip.ID WHERE CustomerID = @CustomerID";
// creating the proper command to run the query
SqlCommand comm = new SqlCommand(Query, con);
comm.Parameters.AddWithValue("@CustomerID", Id);
// try to run the command
try
{
// opening the connection
con.Open();
// creating a sql data reader and run it to read the data from the database
SqlDataReader dr = comm.ExecuteReader();
// read line by line as much as there is something to read
while (dr.Read())
{
// for each line of the returned rows of data from database,
//assign the column values to the properties of a new slip-dock object
SlDk = new SlipDock();
SlDk.DockId = (int)dr["DockID"];
SlDk.DockName = dr["Name"].ToString();
SlDk.SlipId = (int)dr["SlipID"];
SlDk.Width = (int)dr["Width"];
SlDk.Length = (int)dr["Length"];
SlDk.WaterService = (bool)dr["WaterService"];
SlDk.ElectricalService = (bool)dr["ElectricalService"];
// adding the new object to the list of objects
LeaseList.Add(SlDk);
}
dr.Close(); // closing the data reader
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return(LeaseList);
}
// a method to get a list of available slips for a selected docks from the database:
public static List <Slip> GetSlips(int Id)
{
// make an empty list of slip-dock
List <Slip> SlipsList = new List <Slip>();
// make an empty slip-dock object
Slip SL = new Slip();
// get connected to the database
SqlConnection con = InlandMarinaScriptDB.GetConnection();
// creating the proper sql query to extract data from MS SQL server
string Query = "SELECT ID, Width, Length, DockID FROM Slip WHERE DockID = @DockID";
// creating the proper command to run the query
SqlCommand comm = new SqlCommand(Query, con);
comm.Parameters.AddWithValue("@DockID", Id);
// try to run the command
try
{
// opening the connection
con.Open();
// creating a sql data reader and run it to read the data from the database
SqlDataReader dr = comm.ExecuteReader();
// read line by line as much as there is something to read
while (dr.Read())
{
// for each line of the returned rows of data from database,
//assign the column values to the properties of a new slip-dock object
SL = new Slip();
SL.SlipId = (int)dr["ID"];
SL.Width = (int)dr["Width"];
SL.Length = (int)dr["Length"];
SL.DockId = (int)dr["DockID"];
// adding the new object to the list of objects
SlipsList.Add(SL);
}
dr.Close(); // closing the data reader
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return(SlipsList);
}
// a method to get a list of customers from the database:
public static List <Customer> GetCustomers()
{
// make an empty list of customers
List <Customer> CustList = new List <Customer>();
// make an empty customer object
Customer cust;
// get connected to the database
SqlConnection con = InlandMarinaScriptDB.GetConnection();
// creating the proper sql query to extract data from MS SQL server
string Query = "SELECT * FROM Customer";
// creating the proper command to run the query
SqlCommand comm = new SqlCommand(Query, con);
// try to run the command
try
{
// opening the connection
con.Open();
// creating a sql data reader and run it to read the data from the database
SqlDataReader dr = comm.ExecuteReader();
// read line by line as much as there is something to read
while (dr.Read())
{
// for each line of the returned rows of data from database,
//assign the column values to the properties of a new customer object
cust = new Customer();
cust.CustomerID = (Int32)dr["Id"];
cust.FirstName = dr["FirstName"].ToString();
cust.LastName = dr["LastName"].ToString();
cust.Phone = dr["Phone"].ToString();
cust.City = dr["City"].ToString();
// adding the new object to the list of objects
CustList.Add(cust);
}
dr.Close(); // closing the data reader
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return(CustList);
}
// a method to get a list of docks from the database:
public static List <Dock> GetDocks()
{
// make an empty list of docks
List <Dock> DockList = new List <Dock>();
// make an empty slip-dock object
Dock Dk = new Dock();
// get connected to the database
SqlConnection con = InlandMarinaScriptDB.GetConnection();
// creating the proper sql query to extract data from MS SQL server
string Query = "SELECT ID, Name, WaterService, ElectricalService FROM Dock";
// creating the proper command to run the query
SqlCommand comm = new SqlCommand(Query, con);
// try to run the command
try
{
// opening the connection
con.Open();
// creating a sql data reader and run it to read the data from the database
SqlDataReader dr = comm.ExecuteReader();
// read line by line as much as there is something to read
while (dr.Read())
{
// for each line of the returned rows of data from database,
//assign the column values to the properties of a new slip-dock object
Dk = new Dock();
Dk.DockId = (int)dr["ID"];
Dk.DockName = dr["Name"].ToString();
Dk.WaterService = (bool)dr["WaterService"];
Dk.ElectricalService = (bool)dr["ElectricalService"];
// adding the new object to the list of objects
DockList.Add(Dk);
}
dr.Close(); // closing the data reader
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return(DockList);
}
// a method to lease a slip(adding to the database):
public static int InsertLease(int slipId, int customerId)
{
int LsID = 0;
// get connected to the database
SqlConnection con = InlandMarinaScriptDB.GetConnection();
// creating the proper sql query to extract data from MS SQL server
string Query = "INSERT INTO Lease (SlipID, CustomerID) OUTPUT inserted.[ID] " +
"VALUES(@SlipID, @CustomerID)";
// creating the proper command to run the query
SqlCommand comm = new SqlCommand(Query, con);
comm.Parameters.AddWithValue("@SlipID", slipId);
comm.Parameters.AddWithValue("@CustomerID", customerId);
// try to run the command
try
{
con.Open();
comm.ExecuteNonQuery();
string NewQuery = "SELECT IDENT_CURRENT('Lease') FROM Lease";
SqlCommand sqlCommand = new SqlCommand(NewQuery, con);
LsID = Convert.ToInt32(sqlCommand.ExecuteScalar());
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return(LsID);
}
