Blog Archives
Winforms Databinding
Snippets below have been condensed from their original sources for brevity. See references for original articles.
Dataset usage:
using System;
using System.Data;
using System.Data.SqlClient;
namespace Microsoft.AdoNet.DataSetDemo
{
class NorthwindDataSet
{
static void Main()
{
string connectionString = GetConnectionString();
ConnectToData(connectionString);
}
private static void ConnectToData(string connectionString)
{
//Create a SqlConnection to the Northwind database.
using (SqlConnection connection =
new SqlConnection(connectionString))
{
//Create a SqlDataAdapter for the Suppliers table.
SqlDataAdapter adapter = new SqlDataAdapter();
// A table mapping names the DataTable.
adapter.TableMappings.Add("Table", "Suppliers");
// Open the connection.
connection.Open();
Console.WriteLine("The SqlConnection is open.");
// Create a SqlCommand to retrieve Suppliers data.
SqlCommand command = new SqlCommand(
"SELECT SupplierID, CompanyName FROM dbo.Suppliers;",
connection);
command.CommandType = CommandType.Text;
// Set the SqlDataAdapter's SelectCommand.
adapter.SelectCommand = command;
// Fill the DataSet.
DataSet dataSet = new DataSet("Suppliers");
adapter.Fill(dataSet);
// Create a second Adapter and Command to get
// the Products table, a child table of Suppliers.
SqlDataAdapter productsAdapter = new SqlDataAdapter();
productsAdapter.TableMappings.Add("Table", "Products");
SqlCommand productsCommand = new SqlCommand(
"SELECT ProductID, SupplierID FROM dbo.Products;",
connection);
productsAdapter.SelectCommand = productsCommand;
// Fill the DataSet.
productsAdapter.Fill(dataSet);
// Close the connection.
connection.Close();
Console.WriteLine("The SqlConnection is closed.");
// Create a DataRelation to link the two tables
// based on the SupplierID.
DataColumn parentColumn =
dataSet.Tables["Suppliers"].Columns["SupplierID"];
DataColumn childColumn =
dataSet.Tables["Products"].Columns["SupplierID"];
DataRelation relation =
new System.Data.DataRelation("SuppliersProducts",
parentColumn, childColumn);
dataSet.Relations.Add(relation);
Console.WriteLine(
"The {0} DataRelation has been created.",
relation.RelationName);
}
}
static private string GetConnectionString()
{
// To avoid storing the connection string in your code,
// you can retrieve it from a configuration file.
return "Data Source=(local);Initial Catalog=Northwind;"
+ "Integrated Security=SSPI";
}
}
}
Binding DataGridView:
private void GetData(string selectCommand)
{
try
{
// Specify a connection string. Replace the given value with a
// valid connection string for a Northwind SQL Server sample
// database accessible to your system.
String connectionString =
"Integrated Security=SSPI;Persist Security Info=False;" +
"Initial Catalog=Northwind;Data Source=localhost";
// Create a new data adapter based on the specified query.
dataAdapter = new SqlDataAdapter(selectCommand, connectionString);
// Create a command builder to generate SQL update, insert, and
// delete commands based on selectCommand. These are used to
// update the database.
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
// Populate a new data table and bind it to the BindingSource.
DataTable table = new DataTable();
table.Locale = System.Globalization.CultureInfo.InvariantCulture;
dataAdapter.Fill(table);
bindingSource1.DataSource = table;
// Resize the DataGridView columns to fit the newly loaded content.
dataGridView1.AutoResizeColumns(
DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
}
catch (SqlException)
{
MessageBox.Show("To run this example, replace the value of the " +
"connectionString variable with a connection string that is " +
"valid for your system.");
}
}
References:
MSDN, “DataSet Class”, http://msdn.microsoft.com/en-us/library/system.data.dataset.aspx
MSDN, “How to: Bind Data to the Windows Forms DataGridView Control”, http://msdn.microsoft.com/en-us/library/fbk67b6z.aspx
Bind Control to an Object in Windows Forms
Simple solution for basic binding in Windows Forms app. This would NOT be recommended if you are using ASP .Net, Silverlight, WCF, RIA or any other services to retrieve the data as these project types have much better support for binding to controls.
C#:
static class dataaccess {
static mytype object;
}
//on app load
object = new mytype();
//on form load
tbField1.DataBindings.Add("Text", dataaccess.object.property, "Field1")
Lambda Functions in .Net
Lambda expressions in .Net are denoted by the ‘=>’ symbol.
These powerful operators can be used to create sleek, optimized loops that perform much faster.
The usage below condenses the equivalent of at least 20 lines of two loops.
(Sorry kiddos, no time for VB conversion…C# only this time. ;))
Edit (20101229): Lambda rocks! Simply amazing! Greatest ever!
Ex. 1:
PaymentMethodCollection visiblePaymentMethods = new PaymentMethodCollection();
DataSet myPaymentMethods = GlobalDataLoads.PaymentMethods();
List<DataRow> pmids = myPaymentMethods.Tables[0].Select("Visible = 1").ToList();
List<int> ids = pmids.Select(i => i["PaymentMethodId"]).Cast<int>().ToList();
//.Cast<int>().ToList();
List<PaymentMethod> pm = availablePaymentMethods.FindAll(i => ids.Contains(i.PaymentMethodId));
availablePaymentMethods.Clear();
availablePaymentMethods.AddRange(pm);
Ex. 2:
USER user = tol_dc.USERs.Single(u => u.USER_ID == (int)userid);
Ex. 3:
//DataContext tol_dc;
List<int> courseids = registrationList.Select(i=>i.MODULE_ID).ToList();
List<MODULE> modulenames = tol_dc.MODULEs.Where(i=>courseids.Contains(i.MODULE_ID)).Cast<MODULE>().ToList();
Ex. 4: (Get the value of data item inside the child repeater of a parent repeater if the UID is known)
//RepeaterCommandEventArgs e
if ((stringvalueineed==null) || (stringvalueineed=="")) {
int uid= (int)((System.Data.DataTable)rptCourses.DataSource).Rows[e.Item.ItemIndex]["UID"];
stringvalueineed= ((System.Data.DataRow[])(((Repeater)rptOne.Items[e.Item.ItemIndex].FindControl("rptTwo")).DataSource)).Single(i => (int)i["UID"] == uid)["COLUMNNAME"].ToString();
}
Ex. 5a: (filter a list of available products by removing all excluded products from the list, such as those out of stock)
private bool Load_AvailableProducts()
{
string region = Session["region"];
lbAvailableProducts.Items.Clear();
LinqData.DataClassesDataContext dc = new LinqData.DataClassesDataContext();
List<LinqData.GET_PRODUCT_LISTResult> allproducts = (from p in dc.GET_PRODUCT_LIST(region)
select p).ToList();
List<int> excproducts = (from ListItem m in lbExcludedProducts.Items
select Convert.ToInt32(m.Value)).ToList();
List <LinqData.GET_PRODUCT_LISTResult> availproducts = allproducts.Where(p => !excproducts .Contains(p.ProductID)).ToList();
lbAvailableProducts.DataSource = availproducts;
lbAvailableProducts.DataTextField = "ProductName";
lbAvailableProducts.DataValueField = "ProductID";
lbAvailableMarkets.DataBind();
return true;
}
Ex. 5b: (same as above.in this version code is smaller and more load is placed on SQL server rather application (IIS). analyze with SQL profiler and you’ll see the difference)
private bool Load_AvailableProducts()
{
string region = Session["Region"];
lbAvailableProducts.Items.Clear();
LinqData.DataClassesDataContext dc = new LinqData.DataClassesDataContext();
List<LinqData.GET_PRODUCT_LISTResult> availproducts = (from i in dc.GET_PRODUCT_LIST(region)
select i).Where(m => !(from ListItem l in lbExcludedProducts.Items
select Convert.ToInt32(l.Value)).ToList().Contains(m.ProductID)).ToList();
lbAvailableProducts.DataSource = availproducts;
lbAvailableProducts.DataTextField = "ProductName";
lbAvailableProducts.DataValueField = "ProductID";
lbAvailableProducts.DataBind();
return true;
}
The lambda one-liner indicated in the snippet below essentially nests at least two “for/foreach” loops. See my related article here for full source.
Ex. 6:
protected void LoadFactories()
{
ClearLists();
if (ddlSegment.SelectedIndex > 0)
{
using (AndroneticsDataContext db = new AndroneticsDataContext(
ConfigurationManager.ConnectionStrings["AndroneticsConnection1"].ConnectionString))
{
List<UP_GET_FactoriesResult> allFactories = db.UP_GET_Factories(Convert.ToInt32(ddlRegion.SelectedValue), null).ToList();
////this would be approximately how you would approach traditionally
//foreach (UP_GET_FactoriesResult r in allFactories)
//{
// if (GetUnregisteredRobots(r.FACTORY_ID).Count != 0)
// {
// //etc, would need another loop here
// }
//}
//now for the lambda way
var Factories = allFactories.Where(
r => GetUnregisteredRobots(r.FACTORY_ID).Where(
ra => !lstDecommissionRobots.Items.Contains(new ListItem(ra.ROBOT_NAME,ra.ROBOT_ID.ToString()))).Count() != 0);
ddlFactories.DataSource = Factories;
ddlFactories.DataTextField = "FACOTRY_TITLE";
ddlFactories.DataValueField = "FACTORY_ID";
ddlFactories.DataBind();
ListItem li = new ListItem("", "");
ddlFactories.Items.Insert(0, li);
}
}
}
private List<UP_LOAD_COURSE_RobotsResult> GetUnregisteredRobots(int courseid)
{
using (AndroneticsDataContext db = new AndroneticsDataContext(
ConfigurationManager.ConnectionStrings["AndroneticsConnection1"].ConnectionString))
{
List<UP_LOAD_COURSE_RobotsResult> allRobots = db.UP_LOAD_COURSE_Robots(courseid).ToList();
int userID = int.Parse(Request.QueryString["ID"]);
List<string> excregisteredRobots = (from m in db.UP_GET_REGISTERED_Robots(userID) select m.MODULE_NAME).ToList();
return allRobots.Where(m => !excregisteredRobots.Contains(m.MODULE_NAME)).ToList();
}
}
Ex. 7 (foreach)
//make sure list first
List<mydatatype> data = ReturnSomeData().ToList();
//technically long way but helps understand what is happening
Func<mydatatype, string> f = (a) => a.somepropertyname = "somevalue";
data.ForEach(i => f(i));
//or simply
data.ForEach(c => c.somepropertyname = "somevalue";
Ex. 8 Lambda Cast to Custom Object (list of objects from one type to another)
//DAL.StoreProcedures is simply a static class with functions that instantiate and dispose of datacontexts and return object collections from data calls
List<geocode> geocodes = DAL.StoredProcedures.LoadGeoCodes_Zip(market, datestart).Select(g=> new geocode {lat=g.Latitude.ToString(),lng=g.Longitude.ToString()}).Cast<geocode>().ToList();
References:
MSDN, “Lambda Expressions (C# Programming Guide)”, http://msdn.microsoft.com/en-us/library/bb397687.aspx
MSDN C# Developer Center, “101 Linq Samples”, http://msdn.microsoft.com/en-us/vcsharp/aa336746
Fraction of The Blogosphere, https://ronniediaz.com/2010/12/21/lambda_functions_in_-net/
StackOverflow, http://stackoverflow.com/questions/1909268/convert-a-list-of-objects-from-one-type-to-another-using-lambda-expression
