A Customized User Control Combo (smartCombo) that can handle generic connection

A Customized User Control Combo (smartCombo) that can handle generic connection

Introduction

Welcome to my very first article in Code Project!

This is a very simple C# windows application that explains of how to create a custom control of a combox with data bindings.

I was working on a windows application where I needed to display a combo box that would load a data with ValueMember and DisplayMember . I needed to do this with out physically inserting Query than specify the property values. The values will be as per user customized. Now the connection will be a major point. Here I use a generic IDBConnection where it may take SQLConnection,OledbConnection, OdbcConnection etc. Using some modification it can be used any kind of data sources. This article is those who want to display data in a combo or want to make their own control. It will give a clear idea about this.

Background

I have created a very simple windows form with two combo boxes on it. One will be loaded when LoadData button will be clicked. Another will be loaded when the form will be loaded. Here I have used a class SmartCombo that inherit Combo class. There I have used Methods and Properties that can be used at design time as well as run time. Here I have used a combo(right side combo ) that load a data at design time support. And left side combo, I have used purely runtime load. Run time when user will press the LoadData button then the following code will be executed.

smartCombo1.SetConnection = cn; 
smartCombo1.ValueID = "AccountNo";
smartCombo1.ValueText = "AccountName";
smartCombo1.TableName = "tblAccountInfo";
smartCombo1.WhereCondition = "AccountNo = 
'0000000112'";
//here we can use like 
//smartCombo1.WhereCondition = 
//"AccountName Like '%HO%'";
smartCombo1.SetDataSource(ProviderType.SqlServer);

Other way we can make Design Time SmartCombo Property Settings:

After property setting you must add the following code for getting the certain data

//connection is generic. 
//You can use any 
//Connection(SQLConnection,Oledb,Odbc)

cn.Open();
smartCombo2.SetConnection = cn;
smartCombo2.SetDataSource(ProviderType.SqlServer);

Here TableName = a name of a table from where I want to load data

ValueID = a field name that will be treated as a hidden value like ID

ValueText = a field name that will be treated as a DisplayMember

Code

The main part of coding is given below. Where I have used a enumeration that will be used to select appropriate provider like Sql, Oledb, Odbc, Oracle and so on. Oracle is not implemented here. And some properties to demonstrate the customized table, and field.

using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Data;

namespace CustomComboDataBinding
{
 class SmartCombo:ComboBox
 {
   #region Private Variable collection
   private IDbConnection _iConnection;
   private string _TableName;
   private string _valueID;
   private string _valueText;
   private string _whereCond="";
   #endregion

   #region Property Collection
   /// <summary>
   /// Set generic Connection
   /// SQLConnection,OLEDBConnection,
   ///ODBCCOnnection accepted
   /// </summary>
   public IDbConnection SetConnection
   {
    get { return _iConnection; }
    set { _iConnection = value; }
   }
   /// <summary>
   /// Set or get the table name
   /// </summary>
   public string TableName
   {
    set { _TableName = value; }
    get { return _TableName; }
   }

   /// <summary>
   /// get or set
   /// The Item that will be not displayed 
   /// but treated as ID
   /// </summary>
   public string ValueID
   {
    set { _valueID = value; }
    get { return _valueID; }
   }

   /// <summary>
   /// get or set a item that will be 
   /// displayed on smart combo
   /// </summary>
   public string ValueText
   {
    get { return _valueText; }
    set { _valueText = value; }
   }

   /// <summary>
   /// get the item that is hidden 
   ///or treated as a ID or
   /// set from ValueID
   /// </summary>
   public string Value
   {
    get { return base.SelectedValue.ToString(); }
   }

   /// <summary>
   /// Get the display Item of the smart combo
   /// </summary>
   public string Text
   {
    get { return base.Text; }
   }

   public string WhereCondition
   {
    get { return _whereCond; }
    set { _whereCond = value; }
   }

   #endregion
   /// <summary>
   /// SetDatasource execute the final
   /// lines of code. It will load the
   /// data as per given attribute.
   /// </summary>
   public void SetDataSource(ProviderType pType)
   {
    IDbDataAdapter iadpt;
    IDbCommand iCmd;
    string strSQL;
    DataSet ds;        
    try
    {
     if (WhereCondition == "")
     {
      strSQL = "Select [" + ValueID + "], 
      [" + ValueText + 
      "] FROM [" + TableName + "] ";
     }
     else
     {
      strSQL = "Select [" + ValueID + "], 
     [" + ValueText +    "] FROM [" + 
      TableName + "] WHERE " + WhereCondition;
     }
     iCmd = GetCommand(pType);
     iCmd.Connection = SetConnection;
     iCmd.CommandText = strSQL;
     iadpt = GetAdapter(pType);
     iadpt.SelectCommand = iCmd;
     ds = new DataSet();
     iadpt.Fill(ds);
     DataTable dt = MakeTable();
     dt = ds.Tables[0];
     base.DisplayMember = ValueText;
     base.ValueMember = ValueID;
     base.DataSource = dt.DefaultView;
    }
    catch (Exception ex)
    {
     throw ex;
    }
   }


   #region Private Function

   private DataTable MakeTable()
   {
    DataTable dt;
    dt = new DataTable("Source");
    dt.Columns.Add(ValueID);
    dt.Columns.Add(ValueText);
    return dt;
   }

   private IDbDataAdapter GetAdapter(ProviderType pType)
   {
    if (pType == ProviderType.SqlServer)
    {
     return new System.Data.SqlClient.SqlDataAdapter();
    }
    else if(pType == ProviderType.Oledb)
    {
     return new System.Data.OleDb.OleDbDataAdapter();
    }
    else if(pType == ProviderType.Odbc)
    {
     return new System.Data.Odbc.OdbcDataAdapter();
    }
    return null;
   }

   private IDbCommand GetCommand(ProviderType pType)
   {
    if (pType == ProviderType.SqlServer)
    {
     return new System.Data.SqlClient.SqlCommand();
    }
    else if (pType == ProviderType.Oledb)
    {
     return new System.Data.OleDb.OleDbCommand();
    }
    else if (pType == ProviderType.Odbc)
    {
     return new System.Data.Odbc.OdbcCommand();
    }
    return null;
   }
   #endregion
  }

  /// <summary>
  /// ProviderType enum.
  /// </summary>
  public enum ProviderType
  {
   SqlServer,
   Oledb,
   Odbc
  }
}

• What does the code do?

Ans: This code demonstrates how to make a user control that work some customized way. Here I have implemented a combo box and make it a customized data binding as per developer is being used. I called it smartCombo control.

• How do I integrate it with my existing code or how do I use it?

Ans: After compiling the project just drag and drop the smartCombo control from toolbox and use as necessary.

Conclusion:

This is my first article on code project. So please give me proper suggestions

Details:

Md Arifuzzaman (Microsoft certified technology specialist)

Assist Database Report Developer 

Metatude Asia Ltd 

Dhaka, Bangladesh 

Email: arif_uap@yahoo.com