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);