Problem : Retrive Data with ref table

[ahmed17]

hi ,
i make simple method that retrieve data from tables this is easy but my problem happen when i retrieve data from table that have relationship with other table, i make class to set value into properties when i read and bind it in Gridview.

i make for each table class that contains to properties and if i make all table in one class it work so i want to find solution to it with do this.

public class CatgBL
{
    int cid = 0;

    public int Cid
    {
        get { return cid; }
        set { cid = value; }
    }
    string catgName;

    public string CatgName
    {
        get { return catgName; }
        set { catgName = value; }

public class MyStudent
{
    int stid;

    public int Stid
    {
        get { return stid; }
        set { stid = value; }
    }
    string sname;

    public string Sname
    {
        get { return sname; }
        set { sname = value; }
    }
    int catgid;

    public int Catgid
    {
        get { return catgid; }
        set { catgid = value; }
    }
}

and here 's method

  SqlConnection myCon = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Documents and Settings\Administrator\My Documents\TestDb.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True");
    public ArrayList RetriveStdentInfo()
    {
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = myCon;
        cmd.CommandText = "dbo.StoredProcedure2";
        cmd.CommandType = CommandType.StoredProcedure;
        //cmd.Parameters=ParameterDirection.ReturnValue;
        myCon.Open();
        IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

        ArrayList sequence = new ArrayList();
        ArrayList seqCat = new ArrayList();
        ArrayList seqStudent = new ArrayList();
        while (reader.Read())
        {
            MyStudent std = new MyStudent();
            CatgBL cat = new CatgBL();
            std.Stid = reader.GetInt32(0);
            std.Sname = reader.GetString(reader.GetOrdinal("stname"));
            std.Catgid = reader.GetInt32(reader.GetOrdinal("Catgid"));
            cat.CatgName = reader.GetString(reader.GetOrdinal("CatgName"));
            seqCat.Add(cat);
            seqStudent.Add(std);
            sequence.AddRange(seqStudent);
            sequence.AddRange(seqCat);
        }
        return sequence;
    }

Stored procedure

create PROCEDURE dbo.StoredProcedure2 
AS
	SELECT     StudentInfo.*, Catgerories.Catgname
FROM         Catgerories INNER JOIN
                      StudentInfo ON Catgerories.CatgId = StudentInfo.CatgId
	RETURN

[TheCPUWizard]

There are NO tables anywhere in your posted code......

[ahmed17]

Tables :

Create Table Catgerories (
CatgId int PRIMARY KEY,
Catgname nvarchar(50)
)

Create Table StudentInfo(
stId int PRIMARY KEY,
Stname nvarchar(50),
CatgId int  REFERENCES Catgerories (CatgId)
)

[ahmed17]

?????????????

[honeyboy_20]

........................... ?

[vuyiswam]

look at this , They display Data from a Join

http://www.codeproject.com/KB/cs/N-Tier22.aspx

Hope it helps