Thursday, February 24, 2011

Return SalesAgents byName Or byZone

http://www.flickr.com/photos/24834074@N04/5472432292/sizes/l/in/photostream/
http://www.flickr.com/photos/24834074@N04/5471839947/sizes/l/in/photostream/
http://www.flickr.com/photos/24834074@N04/5472432212/sizes/l/in/photostream/
http://www.flickr.com/photos/24834074@N04/5471839801/sizes/l/in/photostream/



public class SalesAgent
{
    private string _name     = string.Empty;
    private string _surname  = string.Empty;
    private string _userID   = string.Empty;
    private string _zoneID   = string.Empty;
    private string _zoneName = string.Empty;
    private string _password = string.Empty;
   
    public SalesAgent(){}

    public string Name
    {
        get { return _name;  }
        set { _name = value; }
    }

    public string Surname
    {
        get { return _surname;  }
        set { _surname = value; }
    }

    public string UserID
    {
        get { return _userID;  }
        set { _userID = value; }
    }

    public string ZoneID
    {
        get { return _zoneID;  }
        set { _zoneID = value; }
    }

    public string ZoneName
    {
        get { return _zoneName; }
        set { _zoneName = value; }
    }
   
    public string Password
    {
        get { return _password;  }
        set { _password = value; }
    }

}


[WebMethod(Description = "Method to obtain SalesAgents by Zone or by Name")]
public SalesAgent[] ReturnSalesAgents(string theZone, string theAgent)
{

    SqlConnection dbConn = new SqlConnection(connStr);
    dbConn.Open();
    string sqlSelect = " ";
    if ( theZone.Equals("") && theAgent.Equals("") ) {
    sqlSelect = " SELECT TOP (100) dbo.PersoanaFizica.Nume as Name,
                          dbo.PersoanaFizica.Prenume as Surname,  " +
                       " dbo.Utilizator.UtilizatorID as UserID, dbo.Utilizator.Parola as Password,
                         dbo.Utilizator.ZonaID as ZoneID, " +
                       " dbo.Zona.Denumire as ZoneName " +
                       " FROM dbo.PersoanaFizica , dbo.Zona , dbo.Utilizator  " +
                       " WHERE dbo.PersoanaFizica.PersoanaFizica_ID = dbo.Utilizator.UtilizatorID and " +
                       " dbo.Utilizator.ZonaID = dbo.Zona.Id " +
                       " ORDER BY dbo.PersoanaFizica.Nume, dbo.PersoanaFizica.Prenume ";
    }
    else if (theAgent.Equals(""))
    {
        sqlSelect = " SELECT TOP (100) dbo.PersoanaFizica.Nume as Name,
                            dbo.PersoanaFizica.Prenume as Surname,  " +
                           " dbo.Utilizator.UtilizatorID as UserID, dbo.Utilizator.Parola as Password,
                             dbo.Utilizator.ZonaID as ZoneID, " +
                           " dbo.Zona.Denumire as ZoneName" +
                           " FROM dbo.PersoanaFizica , dbo.Zona , dbo.Utilizator  " +
                           " WHERE dbo.PersoanaFizica.PersoanaFizica_ID = dbo.Utilizator.UtilizatorID and " +
                           " dbo.Utilizator.ZonaID = dbo.Zona.Id and " +
                           " dbo.Zona.Denumire LIKE" + "'%" + theZone + "%'" +
                           " ORDER BY dbo.PersoanaFizica.Nume, dbo.PersoanaFizica.Prenume ";
    }
    else {
        sqlSelect = " SELECT TOP (100) dbo.PersoanaFizica.Nume as Name,
                              dbo.PersoanaFizica.Prenume as Surname,  " +
                           " dbo.Utilizator.UtilizatorID as UserID, dbo.Utilizator.Parola as Password,
                              dbo.Utilizator.ZonaID as ZoneID, " +
                           " dbo.Zona.Denumire as ZoneName" +
                           " FROM dbo.PersoanaFizica , dbo.Zona , dbo.Utilizator  " +
                           " WHERE dbo.PersoanaFizica.PersoanaFizica_ID = dbo.Utilizator.UtilizatorID and " +
                           " dbo.Utilizator.ZonaID = dbo.Zona.Id and " +
                           " ( dbo.PersoanaFizica.Nume LIKE " + "'%" + theAgent + "%' or
                               dbo.PersoanaFizica.Prenume LIKE " + "'%" + theAgent + "%' )";
    }
   
   
    SqlDataAdapter da = new SqlDataAdapter(sqlSelect, dbConn);
    DataTable dt = new DataTable();
    SqlCommand dbCommand = new SqlCommand(sqlSelect, dbConn);
    da.Fill(dt);
    dbConn.Close();
    List list = new List();
    foreach (DataRow row in dt.Rows)
    {
        SalesAgent target = new SalesAgent();
        target.Name = row["Name"].ToString();
        target.Surname = row["Surname"].ToString();
        target.ZoneID = row["ZoneID"].ToString();
        target.UserID = row["UserID"].ToString();
        target.Password = row["Password"].ToString();
        target.ZoneName = row["ZoneName"].ToString();
        list.Add(target);
    }
    return list.ToArray();
}

No comments:

Post a Comment