Esempio n. 1
0
        public List <Salesperson> SearchSalesperson(string Name, string Surname)
        {
            List <Salesperson> list = new List <Salesperson>();

            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@Name", Name),
                new SqlParameter("@Surname", Surname)
            };

            using (DataTable table = DBHelper.ExecuteParamerizedSelectCommand("sp_SearchSalesperson", CommandType.StoredProcedure, parameters))
            {
                if (table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        Salesperson salesperson = new Salesperson();
                        salesperson.SalespersonID = Convert.ToInt32(row["SalespersonID"].ToString());
                        salesperson.Name          = row["Name"].ToString();
                        salesperson.Surname       = row["Surname"].ToString();
                        salesperson.Username      = row["Username"].ToString();
                        salesperson.Password      = row["Password"].ToString();
                        salesperson.Privileges    = row["Privileges"].ToString();
                        salesperson.EmployeeType  = row["Employee Type"].ToString();
                        list.Add(salesperson);
                    }
                }
            }
            return(list);
        }
Esempio n. 2
0
        public int GetSalesPersonID(string salesPFName, string salesPLName)
        {
            int salesID = 0;

            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@Name", salesPFName),
                new SqlParameter("@Surname", salesPLName)
            };

            using (DataTable table = DBHelper.ExecuteParamerizedSelectCommand("sp_SearchSalesperson", CommandType.StoredProcedure, parameters))
            {
                if (table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        Salesperson SP = new Salesperson();
                        SP.SalespersonID = Convert.ToInt32(row["SalespersonID"].ToString());
                        salesID          = SP.SalespersonID;
                    }
                }
            }

            return(salesID);
        }
        public Salesperson GetById(int id)
        {
            string query =
                "SELECT * FROM Salespersons WHERE id = @id";
            Salesperson found = null;

            try
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand(query, connection))
                    {
                        command.Parameters.Add("@id", SqlDbType.Int).Value = id;
                        SqlDataReader reader = command.ExecuteReader();
                        if (reader.Read())
                        {
                            found = BuildSalesperson(reader);
                        }
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Error:" + e.Message);
            }
            return(found);
        }
        public IEnumerable <Salesperson> GetAll()
        {
            string query =
                "SELECT * FROM Salespersons";
            List <Salesperson> found = new List <Salesperson>();

            try
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand(query, connection))
                    {
                        SqlDataReader reader = command.ExecuteReader();
                        while (reader.Read())
                        {
                            Salesperson salesperson = BuildSalesperson(reader);
                            found.Add(salesperson);
                        }
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Error:" + e.Message);
            }
            return(found);
        }
        public int Insert(Salesperson salesperson)
        {
            int    lastId = 0;
            string query  =
                "INSERT INTO Salespersons(name)" +
                "Values (@name);" +
                "SELECT CAST(scope_identity() as int)";

            try
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand(query, connection))
                    {
                        command.Parameters.Add("@name", SqlDbType.NVarChar, 50).Value = salesperson.Name;
                        Object newId = command.ExecuteScalar();
                        lastId = Convert.ToInt32(newId);
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Error:" + e.Message);
            }
            return(lastId);
        }
        public int Update(Salesperson salesperson)
        {
            int    rowsAffected = 0;
            string query        =
                "UPDATE Salespersons SET name = @name WHERE id = @id";

            try
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand(query, connection))
                    {
                        command.Parameters.Add("@name", SqlDbType.NVarChar, 50).Value = salesperson.Name;
                        command.Parameters.Add("@id", SqlDbType.Int).Value            = salesperson.Id;
                        rowsAffected = command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Error:" + e.Message);
            }
            return(rowsAffected);
        }
Esempio n. 7
0
 public bool AddSalesperson(Salesperson salesperson)
 {
     SqlParameter[] parameters = new SqlParameter[]
     {
         new SqlParameter("@Name", salesperson.Name),
         new SqlParameter("@Surname", salesperson.Surname),
         new SqlParameter("@Username", salesperson.Username),
         new SqlParameter("@Password", salesperson.Password),
         new SqlParameter("@Privileges", salesperson.Privileges),
         new SqlParameter("@EmployeeType", salesperson.EmployeeType)
     };
     return(DBHelper.ExecuteNonQuery("sp_AddSalesperson", CommandType.StoredProcedure, parameters));
 }
Esempio n. 8
0
        public District BuildDistrict(DataSet dataSet)
        {
            District districtObject = null;

            foreach (DataRow distRow in dataSet.Tables["Districts"].Rows)
            {
                int    id   = (int)distRow["id"];
                string name = (string)distRow["name"];
                districtObject = new District {
                    Id = id, Name = name
                };

                var primarySP        = new Salesperson();
                var primarySPRowData = dataSet.Tables["Salespersons"].Select("id = " + (int)distRow["primary_salesperson_id"]);
                primarySP.Id   = (int)primarySPRowData.FirstOrDefault()["id"];
                primarySP.Name = (string)primarySPRowData.FirstOrDefault()["name"];
                districtObject.PrimarySalesperson = primarySP;

                var tempStoreList = new List <Store>();
                foreach (DataRow distStoreRow in distRow.GetChildRows(dataSet.Relations["DistStore"]))
                {
                    int    storeId   = (int)distStoreRow["id"];
                    string storeName = (string)distStoreRow["name"];
                    var    tempStore = new Store {
                        Id = storeId, Name = storeName
                    };
                    tempStoreList.Add(tempStore);
                }
                districtObject.Stores = tempStoreList;

                var tempSPList = new List <Salesperson>();
                foreach (DataRow distSalesRow in distRow.GetChildRows(dataSet.Relations["DistrictJunc"]))
                {
                    int    spId            = (int)distSalesRow.GetParentRow(dataSet.Relations["SalespersonJunc"])["id"];
                    string spName          = (string)distSalesRow.GetParentRow(dataSet.Relations["SalespersonJunc"])["name"];
                    var    tempSalesperson = new Salesperson {
                        Id = spId, Name = spName
                    };
                    tempSPList.Add(tempSalesperson);
                }
                districtObject.Salespersons = tempSPList;
            }
            return(districtObject);
        }
Esempio n. 9
0
 public void Login(Salesperson salesperson)
 {
     SqlParameter[] parameters = new SqlParameter[]
     {
         new SqlParameter("@username", salesperson.Username),
         new SqlParameter("@password", salesperson.Password)
     };
     using (DataTable table = DBHelper.ExecuteParamerizedSelectCommand("sp_Login", CommandType.StoredProcedure, parameters))
     {
         if (table.Rows.Count == 1)
         {
             DataRow row = table.Rows[0];
             salesperson.SalespersonID = Convert.ToInt32(row["SalespersonID"]);
             salesperson.Name          = row["Name"].ToString();
             salesperson.Surname       = row["Surname"].ToString();
             salesperson.Privileges    = row["Privileges"].ToString();
             salesperson.EmployeeType  = row["EmployeeType"].ToString();
         }
     }
 }
Esempio n. 10
0
        public List <Salesperson> GetAllSalespersons()
        {
            List <Salesperson> list = new List <Salesperson>();

            using (DataTable table = DBHelper.ExecuteSelectCommand("sp_GetAllSalespersons", CommandType.StoredProcedure))
            {
                if (table.Rows.Count > 0)
                {
                    foreach (DataRow row in table.Rows)
                    {
                        Salesperson salesperson = new Salesperson();
                        salesperson.SalespersonID = Convert.ToInt32(row["SalespersonID"].ToString());
                        salesperson.Name          = row["Name"].ToString();
                        salesperson.Surname       = row["Surname"].ToString();
                        salesperson.Username      = row["Username"].ToString();
                        salesperson.Password      = row["Password"].ToString();
                        salesperson.Privileges    = row["Privileges"].ToString();
                        salesperson.EmployeeType  = row["Employee Type"].ToString();
                        list.Add(salesperson);
                    }
                }
            }
            return(list);
        }
Esempio n. 11
0
        private Salesperson BuildSalesperson(SqlDataReader reader)
        {
            Salesperson salesperson = new Salesperson();
            int         idOrdinal   = reader.GetOrdinal("id");
            int         nameOrdinal = reader.GetOrdinal("name");

            try
            {
                if (!reader.IsDBNull(idOrdinal) && !reader.IsDBNull(nameOrdinal))
                {
                    salesperson.Id   = reader.GetInt32(idOrdinal);
                    salesperson.Name = reader.GetString(nameOrdinal);
                }
                else
                {
                    return(null);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Error in BuildSalesperson: " + e.Message);
            }
            return(salesperson);
        }
Esempio n. 12
0
        public int Delete(Salesperson salesperson)
        {
            int    rowsAffected = 0;
            string query        =
                "DELETE FROM Salespersons WHERE id = @id";

            try
            {
                using (SqlConnection connection = new SqlConnection(ConnectionString))
                {
                    connection.Open();
                    using (SqlCommand command = new SqlCommand(query, connection))
                    {
                        command.Parameters.Add("@id", SqlDbType.Int).Value = salesperson.Id;
                        rowsAffected = command.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception e)
            {
                Console.WriteLine("Error:" + e.Message);
            }
            return(rowsAffected);
        }