none
indirect connect database RRS feed

  • Question

  •  

    hi some body

    i want too create connect to database and show some fields from some tables,

    i have 3 tables in database

    s----> sid,sname,sfamily

    c----> cid,cname,units

    sc---> sid,cid

    sc.sid is forien key for s.sid

    and

    sc.cid is forien key for c.cid

    i wrote some code for this:

     

    private void button1_Click(object sender, EventArgs e)

    {

    SqlConnection cn = new SqlConnection();

    cn.ConnectionString = "server=.;initial catalog=university;integrated security=true";

     

    DataSet ds = new DataSet();

    SqlCommand cmd = cn.CreateCommand();

     

    cmd.CommandText = "select * from s";

    SqlDataAdapter da_s = new SqlDataAdapter(cmd);

    da_s.Fill(ds, "dts");

     

    cmd.CommandText = "select * from c";

    SqlDataAdapter da_c = new SqlDataAdapter(cmd);

    da_c.Fill(ds, "dtc");

     

    cmd.CommandText = "select * from sc";

    SqlDataAdapter da_sc = new SqlDataAdapter(cmd);

    da_sc.Fill(ds, "dtsc");

     

    DataRelation dr1 = new DataRelation("rels_scs", ds.Tables["dts"].Columns["sid"], ds.Tables["dtsc"].Columns["sid"]);

    DataRelation dr2 = new DataRelation("rels_scc", ds.Tables["dtc"].Columns["cid"], ds.Tables["dtsc"].Columns["cid"]);

     

    foreach (DataRow dr in ds.Tables["dts"].Rows)

    if (dr["family"].ToString().Trim() == "ahmadi")

    for (int i = 0; i < dr.GetChildRows("rels_scs").Length; i++)

    listBox1.Items.Add(dr.GetChildRows("rels_scs")[.i]["cid"]);

    }

     

    but i don't get answer from it, every time that i click at button1 it doesn't any thing.

    i don't know which code i should write,

    would you help me?

    Saturday, March 15, 2008 7:59 PM

Answers

  • Debug a bit to see what is happening.

     

    For example I like to dump dataset to xml like so:

     

    cmd.CommandText = "select * from s";

    SqlDataAdapter da_s = new SqlDataAdapter(cmd);

    da_s.Fill(ds, "dts");

    MessageBox.Show(ds.GetXml());

     

    cmd.CommandText = "select * from c";

    SqlDataAdapter da_c = new SqlDataAdapter(cmd);

    da_c.Fill(ds, "dtc");

    MessageBox.Show(ds.GetXml());

     

    cmd.CommandText = "select * from sc";

    SqlDataAdapter da_sc = new SqlDataAdapter(cmd);

    da_sc.Fill(ds, "dtsc");

    MessageBox.Show(ds.GetXml());

     

     

     

    Monday, March 17, 2008 5:40 PM

All replies

  • A couple of things.

    1) put a break point and make sure you actually have rows in ds.Tables["dts"].Rows.

    2) This may be a problem, change:

     

    dr["family"].ToString().Trim() == "ahmadi"

     

    to

     

    dr["family"].ToString().ToLower().Trim() == "ahmadi"

     

     

     

    Here are a couple general suggestions:

     

    1) split your program up into three layers A GUI layer, a business layer (have classes representing Family, Person, etc), and a Data Access layer to fill and persist the business classes. Make a different project in the solution for each layer. Don't ever directly call the data layer from the presentation layer.

    2) Don't use underscores in your variable names. Be more verbose in your variable names.

    Saturday, March 15, 2008 10:54 PM
  • You have created database connection, but you did not assign that connection to the commands, so they have no idea about where to take data from. Your code should look lile

     

    private void button1_Click(object sender, EventArgs e)

    {

    SqlConnection cn = new SqlConnection();

    cn.ConnectionString = "server=.;initial catalog=university;integrated security=true";

     

    DataSet ds = new DataSet();

    SqlCommand cmd = cn.CreateCommand();

    cmd.Connection = cn;

     

    .......

    }

    Monday, March 17, 2008 9:56 AM
    Moderator
  • It's not necessary to set the connection on the command, because the CreateCommand method already associates the created command with the connection.

     

    I did notice that the connection was never being opened. I think that should throw an exception though, which the poster did not indicate was happening.

    Monday, March 17, 2008 5:30 PM
  • Debug a bit to see what is happening.

     

    For example I like to dump dataset to xml like so:

     

    cmd.CommandText = "select * from s";

    SqlDataAdapter da_s = new SqlDataAdapter(cmd);

    da_s.Fill(ds, "dts");

    MessageBox.Show(ds.GetXml());

     

    cmd.CommandText = "select * from c";

    SqlDataAdapter da_c = new SqlDataAdapter(cmd);

    da_c.Fill(ds, "dtc");

    MessageBox.Show(ds.GetXml());

     

    cmd.CommandText = "select * from sc";

    SqlDataAdapter da_sc = new SqlDataAdapter(cmd);

    da_sc.Fill(ds, "dtsc");

    MessageBox.Show(ds.GetXml());

     

     

     

    Monday, March 17, 2008 5:40 PM