private static IList <Vehicle> GetAllVehicles()
{
IList <Vehicle> lista = new List <Vehicle>();
string cs = DbProps.GetCs();
string query = "Select * from Vehicles";
using (SqlConnection conn = new SqlConnection(cs))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = query;
cmd.CommandType = System.Data.CommandType.Text;
using (SqlDataReader r = cmd.ExecuteReader())
{
if (r.HasRows)
{
while (r.Read())
{
Vehicle v = new Vehicle();
v.IDVehicle = (int)r["IDVehicle"];
v.Maker = r["Maker"].ToString();
v.Miles = (int)r["miles"];
v.Type = r["Type"].ToString();
v.YearOfMaking = (int)r["YearOfMaking"];
lista.Add(v);
}
}
}
}
conn.Close();
}
return(lista);
}
public static string ExportXmlRoutes(int warrantid)
{
// TODO: Remove ID from exported
string toRet;
using (SqlConnection conn = new SqlConnection(DbProps.GetCs()))
{
using (SqlDataAdapter adapter = new SqlDataAdapter())
{
adapter.TableMappings.Add("Routes", "Routes");
conn.Open();
SqlCommand cmd = new SqlCommand("Select * from Routes where WarrantID = @warrantid", conn)
{
CommandType = CommandType.Text
};
cmd.Parameters.AddWithValue("@warrantid", warrantid);
adapter.SelectCommand = cmd;
using (DataSet ds = new DataSet("RoutesDataSet"))
{
adapter.Fill(ds);
toRet = ds.GetXml();
}
}
}
return(toRet);
}
public static IList <Driver> GetDrivers()
{
IList <Driver> lista = new List <Driver>();
string cs = DbProps.GetCs();
string query = "Select * from Drivers";
using (SqlConnection conn = new SqlConnection(cs))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = query;
cmd.CommandType = System.Data.CommandType.Text;
using (SqlDataReader r = cmd.ExecuteReader())
{
if (r.HasRows)
{
while (r.Read())
{
Driver d = new Driver();
d.Id = (int)r["IDDriver"];
d.Name = r["Name"].ToString();
d.Surname = r["Surname"].ToString();
d.PhoneNumber = r["phoneNumber"].ToString();
d.DriversLicenseID = r["driversLicenseID"].ToString();
lista.Add(d);
}
}
}
}
conn.Close();
}
return(lista);
}
public static int UpdateDriver(Driver d, int id)
{
string pname = "@name";
string psurname = "@surname";
string pphone = "@phone";
string plicense = "@license";
string pid = "@id";
String cs = DbProps.GetCs();
string delim = ",";
string query = "UPDATE Drivers SET Name = " + pname + ", " +
"Surname = " + psurname + ", " +
"phoneNumber = " + pphone + ", " +
"driversLicenseID = " + plicense + " WHERE IDDriver = " + pid;
int v;
using (SqlConnection conn = new SqlConnection(cs))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.Parameters.AddWithValue(pid, id);
cmd.Parameters.AddWithValue(pname, d.Name);
cmd.Parameters.AddWithValue(psurname, d.Surname);
cmd.Parameters.AddWithValue(pphone, d.PhoneNumber);
cmd.Parameters.AddWithValue(plicense, d.DriversLicenseID);
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
v = cmd.ExecuteNonQuery();
}
conn.Close();
}
return(v);
}
public static IList <Route> GetRoutesForWarrant(TravelWarrant travelWarrant)
{
if (travelWarrant == null)
{
throw new ArgumentNullException("travelWarrant", "Atr travelWarrant must not be null");
}
IList <Route> routes = new List <Route>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(DbProps.GetCs(), "GetRoutesForWarrant", travelWarrant.IDWarrant))
{
while (reader.Read())
{
routes.Add(new Route
{
IDRoute = (int)reader["IDRoute"],
WarrantID = (int)reader["WarrantID"],
DateStart = DateTime.Parse(reader["DateFrom"].ToString()),
DateEnd = DateTime.Parse(reader["DateTo"].ToString()),
CoordA = reader["CoordA"].ToString(),
CoordB = reader["CoordB"].ToString(),
DistanceKm = (int)reader["DistanceInKm"],
AvgSpeed = (int)reader["AvgV"],
FuelSpent = (int)reader["FuelSpent"]
});
}
}
return(routes);
}
public static int DeleteWarrant(int id)
{
int v = 0;
using (SqlConnection conn = new SqlConnection(DbProps.GetCs()))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "DeleteWarrant";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@warrantid", id);
v = cmd.ExecuteNonQuery();
}
}
return(v);
}
public static int DeleteDriver(int id)
{
string query = "DELETE FROM Drivers WHERE IDDriver=@pid";
int v;
using (SqlConnection conn = new SqlConnection(DbProps.GetCs()))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.Parameters.AddWithValue("@pid", id);
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
v = cmd.ExecuteNonQuery();
}
}
return(v);
}
internal static int DeleteRoute(int idRoute)
{
string prouteid = "@idroute";
string query = "DELETE FROM Routes where IDRoute = " + prouteid;
int v;
using (SqlConnection conn = new SqlConnection(DbProps.GetCs()))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = query;
cmd.Parameters.AddWithValue(prouteid, idRoute);
v = cmd.ExecuteNonQuery();
}
conn.Close();
}
return(v);
}
public static int SaveWarrant(TravelWarrant tv)
{
int v = 0;
using (SqlConnection conn = new SqlConnection(DbProps.GetCs()))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "SaveWarrant";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@driverid", tv.DriverID);
cmd.Parameters.AddWithValue("@vehicleid", tv.VehicleID);
cmd.Parameters.AddWithValue("@warrantstateid", tv.WarrantStateID);
v = cmd.ExecuteNonQuery();
}
}
return(v);
}
private static int SaveDataSetToRoutes(DataSet ds)
{
int v = 0;
using (SqlConnection conn = new SqlConnection(DbProps.GetCs()))
{
string selectQuery = "SELECT * FROM Routes";
string insertQuery = "INSERT INTO Routes" +
"(WarrantID, DateFrom, DateTo, CoordA, CoordB, DistanceInKm, AvgV, FuelSpent) VALUES " +
"(@warrantid, @datefrom, @dateto, @cooA, @cooB, @disInKm, @avgV, @fuel)";
string updateQuery = "UPDATE Routes SET WarrantId = @warrantid, " +
"DateFrom = @datefrom, DateTo = @dateto, CoordA = @cooA, CoordB = @cooB, " +
"DistanceInKm = @disInKm, AvgV = @avgV, FuelSpent = @fuel " +
"where IDRoute = @routeId";
string deleteQuery = "DELETE FROM Routes WHERE IDRoute = @routeId";
SqlCommand selectCommand = new SqlCommand(selectQuery, conn);
SqlCommand insertCommand = new SqlCommand(insertQuery, conn);
SqlCommand updateCommand = new SqlCommand(updateQuery, conn);
SqlCommand deleteCommand = new SqlCommand(deleteQuery, conn);
using (SqlDataAdapter adapter = new SqlDataAdapter(selectCommand))
{
DataSet dataSet = new DataSet();
adapter.Fill(dataSet);
insertCommand.Parameters.Add("@warrantid", SqlDbType.Int, 4, dataSet.Tables[0].Columns[1].ColumnName);
insertCommand.Parameters.Add("@datefrom", SqlDbType.DateTime, 4, dataSet.Tables[0].Columns[2].ColumnName);
insertCommand.Parameters.Add("@dateto", SqlDbType.DateTime, 4, dataSet.Tables[0].Columns[3].ColumnName);
insertCommand.Parameters.Add("@cooA", SqlDbType.NVarChar, 20, dataSet.Tables[0].Columns[4].ColumnName);
insertCommand.Parameters.Add("@cooB", SqlDbType.NVarChar, 20, dataSet.Tables[0].Columns[5].ColumnName);
insertCommand.Parameters.Add("@disInKm", SqlDbType.Int, 4, dataSet.Tables[0].Columns[6].ColumnName);
insertCommand.Parameters.Add("@avgV", SqlDbType.Int, 4, dataSet.Tables[0].Columns[7].ColumnName);
insertCommand.Parameters.Add("@fuel", SqlDbType.Int, 4, dataSet.Tables[0].Columns[8].ColumnName);
adapter.InsertCommand = insertCommand;
dataSet.Merge(ds, true, MissingSchemaAction.Ignore);
v = adapter.Update(dataSet);
}
}
return(v);
}
public static void SaveAllDataFromXml(string filepath)
{
SqlCommand sqlComm = new SqlCommand();
SqlCommand sqlIdent = new SqlCommand();
SqlCommand sqlIdentBack = new SqlCommand();
DataSet ds = new DataSet(DSName);
using (FileStream fs = File.OpenRead(filepath))
{
using (XmlReader reader = XmlReader.Create(fs))
{
ds.ReadXml(reader);
}
}
using (var conn = new SqlConnection(DbProps.GetCs()))
{
conn.Open();
foreach (DataTable table in ds.Tables)
{
StringBuilder sb = new StringBuilder();
using (XmlWriter writer = XmlWriter.Create(sb))
{
ds.WriteXml(writer);
}
sqlComm.Connection = conn;
sqlIdent.Connection = conn;
sqlIdentBack.Connection = conn;
sqlComm.CommandText = $"INSERT {table.TableName} VALUES (@xml)";
sqlComm.Parameters.Add("@xml", SqlDbType.Xml, sb.ToString().Length).Value = sb.ToString();
sqlIdent.CommandText = $"SET IDENTITY_INSERT {table.TableName} ON;";
sqlIdentBack.CommandText = $"SET IDENTITY_INSERT {table.TableName} OFF;";
sqlIdent.ExecuteNonQuery();
sqlComm.ExecuteNonQuery();
sqlIdentBack.ExecuteNonQuery();
}
}
ds.Dispose();
sqlComm.Dispose();
sqlIdent.Dispose();
sqlIdentBack.Dispose();
}
public static int SaveDriver(Driver d)
{
string cs = DbProps.GetCs();
string query = "INSERT INTO Drivers(Name,Surname,phoneNumber,driversLicenseID) " +
"VALUES(@param1,@param2,@param3,@param4)";
int v;
using (SqlConnection conn = new SqlConnection(cs))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.Add("@param1", SqlDbType.VarChar).Value = d.Name;
cmd.Parameters.Add("@param2", SqlDbType.VarChar).Value = d.Surname;
cmd.Parameters.Add("@param3", SqlDbType.VarChar).Value = d.PhoneNumber;
cmd.Parameters.Add("@param4", SqlDbType.VarChar).Value = d.DriversLicenseID;
cmd.CommandType = CommandType.Text;
v = cmd.ExecuteNonQuery();
}
conn.Close();
}
return(v);
}
public static IList <Route> GetRoutes(int warrantId)
{
IList <Route> routes = new List <Route>();
string query = "SELECT * FROM Routes Where WarrantID = @wid";
using (SqlConnection conn = new SqlConnection(DbProps.GetCs()))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.Parameters.AddWithValue("@wid", warrantId);
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
using (SqlDataReader re = cmd.ExecuteReader())
{
if (re.HasRows)
{
while (re.Read())
{
Route r = new Route();
r.IDRoute = (int)re["IDRoute"];
r.WarrantID = (int)re["WarrantID"];
r.DateStart = DateTime.Parse(re["DateFrom"].ToString());
r.DateEnd = DateTime.Parse(re["DateTo"].ToString());
r.CoordA = re["CoordA"].ToString();
r.CoordB = re["CoordB"].ToString();
r.DistanceKm = (int)re["DistanceInKm"];
r.AvgSpeed = (int)re["AvgV"];
r.FuelSpent = (int)re["FuelSpent"];
routes.Add(r);
}
}
}
}
}
return(routes);
}
private static IList <TravelWarrant> GetAllWarrants(IList <Vehicle> vehicles, IList <Driver> drivers)
{
IList <TravelWarrant> warrants = new List <TravelWarrant>();
using (SqlConnection conn = new SqlConnection(DbProps.GetCs()))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "GetAllTravelWarrants";
cmd.CommandType = CommandType.StoredProcedure;
using (SqlDataReader r = cmd.ExecuteReader())
{
if (r.HasRows)
{
while (r.Read())
{
warrants.Add(new TravelWarrant
{
IDWarrant = (int)r["IDTWarrant"],
VehicleID = (int)r["VehicleID"],
DriverID = (int)r["DriverID"],
WarrantStateID = (int)r["WarrantStateEnumId"],
Driver = drivers.Where(x => x.Id == (int)r["DriverID"]).FirstOrDefault(),
Vehicle = vehicles.Where(x => x.IDVehicle == (int)r["VehicleID"]).FirstOrDefault(),
WarrantState = (TravelWarrantStateEnum)(int)r["WarrantStateEnumId"]
});
}
}
}
}
conn.Close();
}
return(warrants);
}
public static int SaveRoute(Route r) => SqlHelper.ExecuteNonQuery(DbProps.GetCs(), "SaveRoute", 0, r.WarrantID, r.DateStart, r.DateEnd, r.CoordA, r.CoordB, r.DistanceKm, r.AvgSpeed, r.FuelSpent);
public static int DeleteRoute(Route r) => SqlHelper.ExecuteNonQuery(DbProps.GetCs(), "DeleteRoute", r.IDRoute);