C# - ComboBox + DataSet, ADO.NET

Source

In my last post I talked about the ADO.NET. Today I am going to demonstrate how fast you can build the application using the ADO.NET. 

My example will have two solution one is a class library (DLL) which will implement the database functionalities. Another solution will use that DLL to talk to the database and do some work with it.

I am using MySQL database. You can use any database instead of MySQL. My database (mydb) contains only two tables. These table follows -

---- info -----                    ---- detailed_info ----
    id(pk) | name                        id(fk) | email

Now first let us focus on database implementation. I took an abstract class which has two member. One is IdbConnection & another is String. It also has two virtual method open & fillDataSet. The code looks like below - 

    abstract public class DB {
        IDbConnection _connection;
        String _connStr;

        public IDbConnection Connection { set { this._connection = value; } get { return _connection; } }
        public String ConnStr { set { _connStr = value; } get { return _connStr; } }

        public abstract void open();
        public abstract void fillDataSet(ref DataSet dtSet, String query, String dataTableName);
    }

 This is our base class & we will inherit this class to make database specific implementation. I have took another class named MySQLDB and made it a child of DB class. As MySQL connection string is specific to MySQL database only. So that information is loaded to base classes ConnStr variable whenever we create an instance of MySQLDB class. Implementation of the class looks like this -

public class MySQLDB : DB
    {
        private String[] strMySQL = new String[] { "localhost", "root", "123", "mydb" };
        
        public MySQLDB()
        {
            this.ConnStr = String.Format("server={0};userid={1};password={2};Database={3};Character Set=utf8;", strMySQL[0], strMySQL[1], strMySQL[2], strMySQL[3]);
        }

        override public void open()
        {
            Debug.Assert(ConnStr != null);
            Connection = new MySql.Data.MySqlClient.MySqlConnection(ConnStr);
            Debug.Assert(Connection != null);
        }

        override public void fillDataSet(ref DataSet dtSet, String query, String dataTableName)
        {
            MySql.Data.MySqlClient.MySqlDataAdapter Adapter = new MySql.Data.MySqlClient.MySqlDataAdapter(query, (MySql.Data.MySqlClient.MySqlConnection)Connection);
            Debug.Assert(Adapter != null);
            Adapter.Fill(dtSet, dataTableName);
        }
    }

 As you can see that I have overrode the two virtual function declared in base class. In the open function the main task was to create the connection to the database using a connection string. In the fillDataSet function we took a dataadapter object and fill the given dataset with appropriate data by querying the database using the provided query.

Now we will introduce an interface which will contain the database function and a class which will actually implement the interface.

   public enum DBTYPE
    {
        MYSQL
    }

public interface DBFunctions
    {
        void getUserInfo(ref DataSet dtSet);
        void getUserDetailedInfo(ref DataSet dtSet);
    }

    public class DBAccessHandler: DBFunctions
    {
        DB dbase;
        DBTYPE dbType;
        public DBAccessHandler(DBTYPE dbtype)
        {
            this.dbType = dbtype;
            switch (this.dbType)
            {
                case DBTYPE.MYSQL:
                    dbase = new MySQLDB();
                    break;
                default:
                    // fall back to your default db
                    break;
            }
        }

        public void getUserInfo(ref DataSet dtSet)
        {
            String query = "SELECT id,name FROM info";
            dbase.fillDataSet(ref dtSet, query, "info");
        }

        public void getUserDetailedInfo(ref DataSet dtSet)
        {
            String query = "SELECT id,email FROM detail_info";
            dbase.fillDataSet(ref dtSet, query, "d_info");
        }
    }

These classes are so basic. There is not much to discuss. The two main points are the name of the datatable used in calling the fillDataSet. We will create same named datatable inside a dataset. Now we are done implementing our database implementation source file. Now we will move to the Windows form application.

I have created a very basic user interface just like the picture below -

Now we will make a dataset which will represent the database we are using. This dataset will be our transparent disconnected database. The dataset will look like below - 

As you can see that, the name of the datatable matches with the implemented interface function in DBAccessHandler class.

Now the codes -

public partial class Form1 : Form
    {
        public ComboBox ComboName { get { return _comboName; } }
        public ComboBox ComboEmail { get { return _comboEmail; } }
        private DBAccess.DBAccessHandler dbHandler = null;
        private DtSet dtSet = null;
        public Form1()
        {
            InitializeComponent();
            dbHandler = new DBAccessHandler(DBTYPE.MYSQL);
            dtSet = new DtSet();
            GetDatas();
            FillComboBox();
            SetupHandlers();
        }

        private void SetupHandlers()
        {
            ComboName.SelectedIndexChanged += new EventHandler(ComboName_SelectedIndexChanged);
        }

        void ComboName_SelectedIndexChanged(object sender, EventArgs e)
        {
            DataView tempView = dtSet.Tables[1].DefaultView;
            tempView.RowFilter = String.Format("id={0}", (int)((ComboBox)sender).SelectedValue);
            ComboEmail.DataSource = tempView;
            ComboEmail.DisplayMember = "email";
        }

        private void FillComboBox()
        {
            ComboName.DataSource = dtSet;
            ComboName.DisplayMember = "info.name";
            ComboName.ValueMember = "info.id";
        }

        private void GetDatas()
        {
            DataSet tempDtSet = (DataSet)dtSet;
            dbHandler.getUserInfo(ref tempDtSet);
            dbHandler.getUserDetailedInfo(ref tempDtSet);
        }

    }

 As you can see that inside the GetDatas function we called our database functions which handles the MySQL native query for this methods. After we populate data in the dataset we set ComboName combobox's datasource as that dataset. Now our dataset acts as the transparent database. We have access to detached database through it. 

The DisplayMember property tells which member of the datatable to be displayed & the ValueMember property let us map a value against the currently displaying DisplayMember. In this way we will get the associated id against a name easily.

Next we registered the selectedindexchanged event and add a filter based on the current selected value (i,e id of the name) and make a new dataview from the existing datatable. We then sets this dataview in the ComboEmail.

Hope this tutorial gives you an idea of how to use the ADO.NET. If you have any suggestion or query please leave a comment. 

Source Code.