none
Foreign key binding RRS feed

  • Question

  • My data includes 3 tables, a users table with userName and userId, a permissions table with permissionName and permissionId and a PermissionMembership table that has userid and permissionid that are foreign keys into the above tables.  I would like to setup a combo box where you can select a user and then have a datagridview display the permissionnames.  Is there a way to accomplish this with databinding and bindingsources?  Currently I have something like this...

     

      _usersBinding.DataSource = _dataSet;
      _usersBinding.DataMember = _dataSet.Users.TableName;
    
      _uesrsComboBox.DataSource = _usersBinding;
      _uesrsComboBox.DisplayMember = "UserName";
      _uesrsComboBox.ValueMember = "UserId";
    
      _permissionsBinding.DataSource = _usersBinding;
      _permissionsBinding.DataMember = "FK_UserId"];
    
      _permissionsNameBinding.DataSource = _permissionsBinding;
      _permissionsNameBinding.DataMember = "FK_PermissionId";
    
      _dataGridView.DataSource = _permissionsNameBinding;
    

     

    Can this be accomplished? Essentially I would like to go from a parent table to another parent table through a child table with foreign keys into both. The line above setting the _permissionNameBinding.DataMember to "FK_PermissionId" will throw and expection, but represents what I am trying to do.

    Thanks


    Wednesday, September 1, 2010 5:09 AM

Answers

  •      public SqlConnection GetSqlConn()
         {
           string M_str_sqlcon = "Data Source=(local);Database=Test;user Id=sa;PWD="; 
           SqlConnection mycon = new SqlConnection(M_str_sqlcon);
           mycon.Open();
           return mycon;
         }
    
        public DataSet GetDataSet(string sqlComm, string tableName)
        {
          SqlConnection sqlconn = this.GetSqlConn();
          SqlDataAdapter sqlda = new SqlDataAdapter(sqlComm, sqlconn);
          DataSet ds = new DataSet();
          sqlda.Fill(ds, tableName);
          return ds;
        }
    
        public void cboxBind(string sqlComm, string tableName, string tableColumn, ComboBox cbox)
        {
          DataSet ds = this.GetDataSet(sqlComm, tableName);
          DataRow dr = ds.Tables[0].NewRow();
          ds.Tables[0].Rows.InsertAt(dr, 0);
          cbox.DataSource = ds.Tables[tableName];
          cbox.DisplayMember = tableColumn;
        }
        
        public SqlDataReader GetReader(string sqlComm)
        {
          SqlConnection sqlconn = this.GetSqlConn();
          SqlCommand sqlcomm = new SqlCommand(sqlComm, sqlconn);
          SqlDataReader sqlreader = sqlcomm.ExecuteReader(CommandBehavior.CloseConnection);
          return sqlreader;
        }    
    
        private void Form4_Load(object sender, EventArgs e)
        {
          this.cboxBind("SELECT userId FROM Users", "Users", "userId", comboBox1);
        }
        
         private void comboBox1_TextChanged(object sender, EventArgs e)
        {
    
          if (comboBox1.Text.Trim() != "System.Data.DataRowView" && comboBox1.Text.Trim()!="")
          {
            SqlDataReader dr = this.GetReader("SELECT U.userName,P.permissionName FROM Users AS U,Permissions AS P,PermissionMembership AS PM WHERE PM.userId = '" + comboBox1.Text.Trim() + "' AND U.userId = PM.userId AND P.permissionId = PM.permissionid");
    
            dr.Read();
            if (dr.HasRows)
            {
              dataGridView1.ColumnCount = dr.FieldCount;
    
              string a = dr[0].ToString();
              string b = dr[1].ToString();
    
              for (int i = 0; i < dr.FieldCount; i++)
              {
                dataGridView1[i, 0].Value = dr[i].ToString();            
              }
            }
            dr.Close();
          }
        }    
    

    If this is helpful
      { Please Mark as Answered }
    else if it is not helpful
      { Un-Mark as Answered }


    Best Regards
    Huan Li, Code Blog: Small is New Big In C#
    • Marked as answer by liurong luo Monday, September 6, 2010 7:11 AM
    Wednesday, September 1, 2010 6:06 AM
  • This can be done with DataRelations on your DataSet and using those relationships as your BindingSources. I can get together an example if you'd like (I started messing with one, but haven't quite had the time to finish it)


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by liurong luo Monday, September 6, 2010 7:11 AM
    Wednesday, September 1, 2010 11:10 PM

All replies

  •      public SqlConnection GetSqlConn()
         {
           string M_str_sqlcon = "Data Source=(local);Database=Test;user Id=sa;PWD="; 
           SqlConnection mycon = new SqlConnection(M_str_sqlcon);
           mycon.Open();
           return mycon;
         }
    
        public DataSet GetDataSet(string sqlComm, string tableName)
        {
          SqlConnection sqlconn = this.GetSqlConn();
          SqlDataAdapter sqlda = new SqlDataAdapter(sqlComm, sqlconn);
          DataSet ds = new DataSet();
          sqlda.Fill(ds, tableName);
          return ds;
        }
    
        public void cboxBind(string sqlComm, string tableName, string tableColumn, ComboBox cbox)
        {
          DataSet ds = this.GetDataSet(sqlComm, tableName);
          DataRow dr = ds.Tables[0].NewRow();
          ds.Tables[0].Rows.InsertAt(dr, 0);
          cbox.DataSource = ds.Tables[tableName];
          cbox.DisplayMember = tableColumn;
        }
        
        public SqlDataReader GetReader(string sqlComm)
        {
          SqlConnection sqlconn = this.GetSqlConn();
          SqlCommand sqlcomm = new SqlCommand(sqlComm, sqlconn);
          SqlDataReader sqlreader = sqlcomm.ExecuteReader(CommandBehavior.CloseConnection);
          return sqlreader;
        }    
    
        private void Form4_Load(object sender, EventArgs e)
        {
          this.cboxBind("SELECT userId FROM Users", "Users", "userId", comboBox1);
        }
        
         private void comboBox1_TextChanged(object sender, EventArgs e)
        {
    
          if (comboBox1.Text.Trim() != "System.Data.DataRowView" && comboBox1.Text.Trim()!="")
          {
            SqlDataReader dr = this.GetReader("SELECT U.userName,P.permissionName FROM Users AS U,Permissions AS P,PermissionMembership AS PM WHERE PM.userId = '" + comboBox1.Text.Trim() + "' AND U.userId = PM.userId AND P.permissionId = PM.permissionid");
    
            dr.Read();
            if (dr.HasRows)
            {
              dataGridView1.ColumnCount = dr.FieldCount;
    
              string a = dr[0].ToString();
              string b = dr[1].ToString();
    
              for (int i = 0; i < dr.FieldCount; i++)
              {
                dataGridView1[i, 0].Value = dr[i].ToString();            
              }
            }
            dr.Close();
          }
        }    
    

    If this is helpful
      { Please Mark as Answered }
    else if it is not helpful
      { Un-Mark as Answered }


    Best Regards
    Huan Li, Code Blog: Small is New Big In C#
    • Marked as answer by liurong luo Monday, September 6, 2010 7:11 AM
    Wednesday, September 1, 2010 6:06 AM
  • I agree that it could all be resolved by handling various update types, but was hoping the synchronization could be handled by the binding source.  Is this not possible?
    Kevin
    Wednesday, September 1, 2010 12:09 PM
  • This can be done with DataRelations on your DataSet and using those relationships as your BindingSources. I can get together an example if you'd like (I started messing with one, but haven't quite had the time to finish it)


    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    • Marked as answer by liurong luo Monday, September 6, 2010 7:11 AM
    Wednesday, September 1, 2010 11:10 PM