Ejemplo n.º 1
0
        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);
        }
Ejemplo n.º 2
0
        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);
        }
Ejemplo n.º 3
0
        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);
        }
Ejemplo n.º 4
0
        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);
        }
Ejemplo n.º 5
0
        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);
        }
Ejemplo n.º 6
0
        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);
        }
Ejemplo n.º 7
0
        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);
        }
Ejemplo n.º 8
0
        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);
        }
Ejemplo n.º 9
0
        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);
        }
Ejemplo n.º 10
0
        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);
        }
Ejemplo n.º 11
0
        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();
        }
Ejemplo n.º 12
0
        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);
        }
Ejemplo n.º 13
0
        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);
        }
Ejemplo n.º 14
0
        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);
        }
Ejemplo n.º 15
0
 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);
Ejemplo n.º 16
0
 public static int DeleteRoute(Route r) => SqlHelper.ExecuteNonQuery(DbProps.GetCs(), "DeleteRoute", r.IDRoute);