BindingSource in action
Posted by: rsusanto on: June 24, 2010
- In: .NET | C#
- Leave a Comment
In this part i will demonstrate use of Binding Source in purpose to filter data that linked with another data.
So lets go to the case learning.
The case is about a simple form to choose singer due their genre.
In this case i have 2 control in my form. The 1st Combo Box which contain datas about genres of music
date then 2nd Combo Box which contain datas about singer/band.
Im gonna use List for the Genre with this domain POP, ROCK, JAZZ
And for the singer i will use SQL Server 2000 with SQL Server Management Studio Express. I named the table singer :
So we must filter the data in the 2nd Combo Box due selected data in 1st Combo Box to match the singer to their genre.
This is how the form looks, quiet simple right 
:
Now we will step into the code.
1. Call the SQL Client so we can communicate with SQL Server Database through its data type and functions
Type this on the top of using System.Data.SqlClient;
2. Declare this global variables
- lststrGenre : datasource for cmbGenre.
- dtSinger : act as container for table that we call from MSSQL.
- conn : connection variable which represents the connection to MSSQL.
- da : SqlDataAdapter type as a bridge beetwen Dataset and SQL Server for retrieving and saving data. In this case da will fill dtSinger with data that we retrieve from SQL Server table, Singer.
- cmd : Represents a Transact-SQL statement or stored procedure to execute against a SQL Server database. In simple word, execute our query.
- bs : in this case we want to use BindingSource.Filter property that allowed us to filter data according cmbGenre.text property
3. Function to handle connection to SQL Server
private SqlConnection buildConn()
{
string strConn = @"Data Source = .\SQLEXPRESS;
Integrated Security = SSPI;
Initial Catalog = confuseisme;";
return new SqlConnection(strConn);
}- This time i used string strConn as parameter to instanciate SqlConnection
- Data Source : [your computer name]\[your SQL Server instance name]
- Integrated Security : When false, User ID and Password are specified in the connection. When true, the current Windows account credentials are used for authentication. sspi (strongly recommended), which is equivalent to true.
- Initial Catalog : The name of the database. Mine : confuseisme. Yours up to you
4. Function to fill the lststr
private void fillCmbGenre()
{
lststrGenre.Add("POP");
lststrGenre.Add("JAZZ");
lststrGenre.Add("ROCK");
cmbGenre.DataSource = lststrGenre;
}Use lststrGenre.Add(String param) function to add domain data. Then set lststrGenre as cmbGenre.DataSource value. So we dont dont to have loop then cmbGenre.Items.Add(string Param).
5. Function to fill DataTable dtSinger
private int fillDtSinger()
{
int intResp = -1;
try
{
if (conn.State.ToString() != "Open")
conn.Open();string strCmd = "SELECT * FROM T_Singer ORDER BY genre,singer;";
cmd = new SqlCommand(strCmd, conn);
da.SelectCommand = cmd;
da.Fill(dtSinger);
}
catch (SqlException e)
{
intResp = e.Number;
}
finally
{
cmd.Dispose();
da.Dispose();
conn.Close();
}
return intResp;
}
- intRespon : act as indicator that our syntax in try have success or not. I give its initial value -1, because if there’s an error the error code that will fill intResp must be more than 0. So if the intResp still -1, so we can assume there’s nothing wrong all the way
- if (conn.State.ToString() != “Open”) : by default connection still remain closed until we open it with conn.Open();
- Then we retrieve data from SQL Server with query and then fill dtSinger with da (SqlDataAdapter)
- catch (SqlException e) : catch any error from SQL if error happened. Then replace value of intResp with e.Number property
- Then with Dispose() release all resource and free memory load
6. Fill out cmbSinger
private void fillCmbSinger()
{
bs.RemoveFilter();
bs.Filter = "genre = '" + cmbGenre.Text + "'";
cmbSinger.Items.Clear();
foreach (DataRowView drv in bs)
{
cmbSinger.Items.Add(drv[1].ToString());
}
}
- bs.RemoveFilter() : remove filter so we dont filter data twice, ex: we have filtered the data for POP than we want to filter bs again for JAZZ, so all we got is 0 record. So in order to get the right result we have to remove filter to set data state to original state
- bs.Filter = “genre = ‘” + cmbGenre.Text + “‘” : use bs.Filter property like WHERE clause in SQL
- cmbSinger.Items.Clear() : we clear items so next time we add items will not be stacked with items we add before
- foreach (DataRowView drv in bs) : use this loop on purpose to use drv as parameter we use in cmbSinger.Items.Add(drv[1].ToString(), [1] means column ordinal which refered to singer. Remember BindingSource stored our record in DataRowView type
7. Add event SelectionIndexChanged to cmbGenre
We wan filter function working whenever we select another Genre so we put this
private void cmbGenre_SelectedIndexChanged(object sender, EventArgs e)
{
fillCmbSinger();
}
8. Intance the objects
Pheew, now we have complete all those step. Lest instanciate all of those object in Page_Load event
public F_Blog_BindingSource()
{
InitializeComponent();
conn = buildConn();
dtSinger = new DataTable();
lststrGenre = new List();
da = new SqlDataAdapter();
bs = new BindingSource();
dtSinger = new DataTable();
}May this post works and benefits you, let me know if you have some question about this case. And it will be nice if we you give me some advice, because seriously this is my first blog post. After all thanks
Credit for msdn.microsoft.com for definitions
