Wpf and Windows Forms Controls Interop

Though WPF is a Wonderful technology, at times programmers get frustrated with the lack of certain controls. One such control is the DataGrid or GridView. Silverlight 2 infact has a DataGrid, hope in the next release of WPF it will be as feature rich as Windows Forms.

It is very much possible to use Windows Forms controls in WPF.

Start out by dragging a WindowsFormsHost into WPF Windows.


Create an object of System.Windows.Forms..DataGrid and set the Child Property of WindowsFormsHost to the newly created DataGrid object. WindowsFormsHost can have only one child as is obvious form the Child Property.

System.Windows.Forms.DataGrid dg = new System.Windows.Forms.DataGrid();
private void Window_Loaded(object sender, RoutedEventArgs e)
{
windowsFormsHost1.Child = dg;
}

Now you can work with the DataGrid just like you work with it in Windows Forms.

Here is the complete code for connecting the DataGrid to a DataSet.

using System.Data;
using System.Data.SqlClient;

using System.Windows.Forms;

System.Windows.Forms.DataGrid dg = new System.Windows.Forms.DataGrid();

SqlConnection cnn;
SqlDataAdapter da;
DataSet ds = new DataSet();
private void Window_Loaded(object sender, RoutedEventArgs e)
{
windowsFormsHost1.Child = dg;

cnn = new SqlConnection(@"Integrated Security=sspi;Initial Catalog=Shalvin;Data source=.\sqlexpress");
cnn.Open();
da = new SqlDataAdapter("select * from Categories", cnn);
da.Fill(ds, "Cat");
dg.DataSource = ds.Tables["Cat"];
}

.Net Windows Forms Database Connectivity with Ado .Net

Database connectivity refers to the ability of an application to connect to database system in order to store, retrieve, update or delete data.

ADO.Net is a data access technology of .Net. ADO.Net contains Managed Providers and DataSet.

Managed providers provide libraries to connect to various databases.

 Two important Managed Providers are SqlClient and OleDb.

SqlClient namespace contains classes required for interacting with SQL Server.

OleDb is used to connect to any RDBMS say Oracle, Access and even SQL Server. 

But it is recommended using SqlClient with Sql Server, since SqlClient is tailor made for Sql Server. 

SqlConnection class is used for establishing a live session between front end and back end. 

In the connection string we specify the connection options like Database instance, security mode, etc.

Connection string is passed as a constructor to the SqlConnection. 

In this example we are using Integrated Security, ie. using the windows credential for connecting to SQL Server. 

 SqlDataReader is a read-only forward only recordset. 

In the SqlCommand's constructor we are passing the SqlCommand.

 

 Inorder to create an SqlDataReader the ExecuteReader method of SqlCommand have to be used.

SqlDataReader Read() Method
After creating the SqlDataReader we use the Read method for iterating throug the SqlDataReader and filling the ListBox.

using System.Data.SqlClient;

using System.Data.SqlClient;

SqlConnection cnn; 
SqlCommand cmd; SqlDataReader dr; 
private void Form1_Load(object sender, EventArgs e)
{
    cnn = new SqlConnection("Integrated Security=sspi;Initial Catalog=Northwind");
    cnn.Open();
    cmd = new SqlCommand("select * from Categories", cnn);
    dr = cmd.ExecuteReader();
    while (dr.Read())
    {
        lstCategories.Items.Add(dr["CategoryName"].ToString());
    }
}

VB .Net

 Imports System.Data.SqlClient 
 Dim cnn As New SqlConnection 
Dim cmd As SqlCommand 
Dim dr As SqlDataReader
 Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 cnn = New SqlConnection("integrated security=sspi;initial catalog=Northwind; 
data source=.\sqlExpress") 
cnn.Open() 
cmd = New SqlCommand("select* from categories", cnn) 
dr = cmd.ExecuteReader() 
While (dr.Read()) 
lstCategories.Items.Add(dr("categoryName")) 
End While 
End Sub

'

Inserting Records from Front End 

For inserting records use the ExecuteNonQuery() method of SqlCommand class. 

  
 using System.Data.SqlClient; 

SqlConnection cnn;
SqlCommand cmd;
private void Form1_Load(object sender, EventArgs e)
{ 
cnn = new SqlConnection("Integrated Security=sspi;Initial Catalog=Shalvin"); 
cnn.Open(); 
} 
 private void btnInsert_Click(object sender, EventArgs e)
{ 
cmd = new SqlCommand("insert into Categories values ('Condiments')", cnn);
cmd.ExecuteNonQuery(); 
MessageBox.Show("Record saved"); 
}

 

VB .Net

Imports System.Data.SqlClient

Dim cnn as SqlConnection Dim cmd as SqlCommand

Private Sub Form_Load(.. cnn = new SqlConnection("Integrated Security=sspi;Initial Catalog=Shalvin") cnn.Open() End Sub

Private Sub btnInsert_Click(sender as object, e as EventArgs) .. cmd = new SqlCommand("insert into Categories values ('Condiments')", cnn)

cmd.ExecuteNonQuery() MessageBox.Show("Record saved") End Sub

Connecting to Sql Server 2005

cnn = new SqlConnection("Data Source=.\\sqlexpress;Integrated Security=sspi;Initial Catalog=Shalvin");

Inserting values of TextBox

The previous example cannot be used in real time since on clicking the button the same data will be inserted to the table. In this example we are constructing an Sql statement by concatenating the contents of TexBox as shown in the highlighted code. After inserting the values we blanking the control and bringing the focus back to the control.

using System.Data.SqlClient;

SqlConnection cnn;
SqlCommand cmd;

private void Form1_Load(object sender, EventArgs e)
{
cnn = new SqlConnection("Integrated Security=sspi;Initial Catalog=Shalvin");
cnn.Open();
}

private void btnInsert_Click(object sender, EventArgs e)
{
string strSql = "insert into Categories values ('" + txtCategoryName.Text + "')";
cmd = new SqlCommand(strSql, cnn);
cmd.ExecuteNonQuery();
MessageBox.Show("Record saved");
BlankControls();
}
private void BlankControls()
{
txtCategoryName.Text = "";
txtCategoryName.Focus();
}

DataSet DataGrid

DataSet is an offline disconnected data store. SqlDataAdapter is an intemediary between the RDBMS and DataSet. The methods of SqlDataAdapter are Fill and Update. Fill Method is used for filling a DataSet. Update Method is used synchronizing the RDBMS with changes in DataSet.

using System.Data.SqlClient;

SqlConnection cnn;
SqlDataAdapter da;
DataSet ds = new DataSet();

private void Form1_Load(object sender, EventArgs e)
{
	cnn = new SqlConnection("Integrated Security=sspi;Initial Catalog=Shalvin");

	da = new SqlDataAdapter("select * from Categories", cnn);
	da.Fill(ds, "Shalvin");
	dataGridView1.DataSource = ds.Tables["Shalvin"];
}

VB .Net

Imports System.Data Imports System.Data.SqlClient Public Class frmGrid Dim cnn As SqlConnection Dim ds As New DataSet Dim da As SqlDataAdapter Private Sub frmGrid_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load cnn = New SqlConnection("Integrated Security=sspi;Initial Catalog=Northwind") cnn.Open() da = New SqlDataAdapter("select * from Categories", cnn) da.Fill(ds, "Cat") DataGridView1.DataSource = ds.Tables("Cat") End Sub End Class

DataSet with Multiple DataTables A DataSet is a collection of DataTables along with other objects just like Database is a collection of Tables (views, stored procedures, etc.). This example requires DataGrid insted of GridView Control.

using System.Data.SqlClient; SqlConnection cnn; SqlDataAdapter da; DataSet ds = new DataSet(); private void Form1_Load(object sender, EventArgs e) { cnn = new SqlConnection("Integrated security=sspi;Initial Catalog=Northwind"); da = new SqlDataAdapter("select CategoryId, CategoryName, Description from categories", cnn); da.Fill(ds, "Cat"); da = new SqlDataAdapter("select ProductId, ProductName, UnitPrice from Products", cnn); da.Fill(ds, "Prod"); dataGrid1.DataSource = ds; }

VB .Net

Imports System.Data Imports System.Data.SqlClient Public Class frmGrid Dim cnn As SqlConnection Dim ds As New DataSet Dim da As SqlDataAdapter Private Sub frmGrid_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load cnn = New SqlConnection("Integrated Security=sspi;Initial Catalog=Northwind") cnn.Open() da = New SqlDataAdapter("select CategoryId, CategoryName, Description from Categories", cnn) da.Fill(ds, "Cat") da = New SqlDataAdapter("select ProductId, ProductName, UnitPrice from Products", cnn) da.Fill(ds, "Prod") DataGrid1.DataSource = ds End Sub

DataTable DataColumn and DataRow Visit http://shalvinpd.blogspot.com/2008/03/datatable-datacolumn-and-datarow-aspnet.html for Asp.net code of DataTable DataColum and DataRow. DataSet is an offline disconnected Data Store. DataSet is a collection of DataTables. A DataTable Contains DataRow and DataColumns. The following code illustrating creating DataTable, DataRow and DataColumn. This approached can be used for creating a temporary storage of structured data. DataTable dt = new DataTable("Purchase"); DataColumn dc; DataRow dr; private void Form1_Load(object sender, EventArgs e) { dc = new DataColumn("ProductName"); //Adding the column to the Columns colluction of DataTable dt.Columns.Add(dc); dc = new DataColumn("Price"); //Adding the column to the Columns colluction of DataTable dt.Columns.Add(dc); //Adding a new row to the table dr = dt.NewRow(); dr["ProductName"] = "Microsoft Mouse"; dr["Price"] = 400; dt.Rows.Add(dr); dgPurchase.DataSource = dt; } Creating AutoIncrement Colum for DatTable Just like Sql Server is having an identity column, we can have an auto increment column for out datatable. Set the AutoIncrement property of column to true you can optionally set the AutoIncrementSeed and AutoIncrementStep. DataTable dt = new DataTable("Purchase"); DataColumn dc; DataRow dr; private void Form1_Load(object sender, EventArgs e) { dc = new DataColumn(("Product ID"), System.Type.GetType("System.Int32")); dc.AutoIncrement = true; dc.AutoIncrementSeed = 100; dc.AutoIncrementStep = 1; dt.Columns.Add(dc); dc = new DataColumn("ProductName"); dt.Columns.Add(dc); dc = new DataColumn("Price"); dt.Columns.Add(dc); dr = dt.NewRow(); dr["ProductName"] = "Microsoft Mouse"; dr["Price"] = 400; dt.Rows.Add(dr); dr = dt.NewRow(); dr["ProductName"] = "Shalvin's C# Tutorial Video"; dr["Price"] = 4000; dt.Rows.Add(dr); dgPurchase.DataSource = dt; }

DataSet and ComboBox's DisplayMember Property

Combox's DisplayMember property is used to specify the column to display in a ComboBox.

using System.Data.SqlClient; SqlConnection cnn; SqlDataAdapter da; DataSet ds = new DataSet(); private void Form1_Load(object sender, EventArgs e) { cnn = new SqlConnection("Integrated Security=sspi;Initial Catalog=Northwind"); da = new SqlDataAdapter("select CategoryId, CategoryName, Description from Categories", cnn); da.Fill(ds, "Cat"); cboCategories.DataSource = ds.Tables["Cat"]; cboCategories.DisplayMember = "CategoryName"; }

Login Module in Windows Forms Table and Stored Procedure create table UserTable (UserId int identity(1,1) primary key, RoleId int, UserName varchar(20), Password varchar(20)) create procedure spusertable(@pMode char(1), @pUserId int = null, @pRoleId int = null, @pUserName varchar(20) = null,@ppassword varchar(20) = null) as if @pMode = 'S' select * from Usertable else if @pMode = 'I' insert into usertable values(@pRoleid,@pUsername,@ppassword) else if @pmode='D'delete from usertable where userid=@puserid go Windows Forms SqlConnection cnn; SqlCommand cmd; SqlDataReader dr; int count; private void btnLogin_Click(object sender, EventArgs e) { cmd = new SqlCommand("select count(*) from usertable where UserName = '" + txtUserName.Text + "'and password = '" + txtPassword.Text + "'", cnn); count = Int32.Parse(cmd.ExecuteScalar().ToString()); if (count != 0) { frmMain fm = new frmMain(); fm.Show(); this.Hide(); } else MessageBox.Show("Invalid User"); } }

DataTable DataColumn and DataRow in .Net

DataSet is an offline disconnected Data Store.
DataSet is a collection of DataTables.
A DataTable Contains DataRow and DataColumns.

The following code illustrating creating DataTable, DataRow and DataColumn. This approached can be used for creating a temporary storage of structured data.

using System.Data;  
DataTable dt = new DataTable("Purchase");  
DataColumn dc;  
DataRow dr;  
protected void Page_Load(object sender, EventArgs e)  
{  
 dc = new DataColumn("ProductName");  
 dt.Columns.Add(dc);  
 dc = new DataColumn("Price");  
 dt.Columns.Add(dc);  
 
 dr = dt.NewRow();  
 dr["ProductName"] = "Microsoft Mouse";  
 dr["Price"] = 400;  
 dt.Rows.Add(dr);  
 
 grvPurchase.DataSource = dt;  
 DataBind();  
}

Figure 1






VB .Net

Imports System.Data

Dim dt As New DataTable("Purchase")
Dim dc As DataColumn
Dim dr As DataRow

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    dc = New DataColumn("ProductName")

    'Adding the column to the Columns colluction of DataTable
    dt.Columns.Add(dc)
    dc = New DataColumn("Price")

    'Adding the column to the Columns colluction of DataTable
    dt.Columns.Add(dc)

    'Adding a new row to the table
    dr = dt.NewRow()
    dr("ProductName") = "Microsoft Mouse"
    dr("Price") = 400
    dt.Rows.Add(dr)

    grvPurchase.DataSource = dt
    DataBind()
End Sub

Windows Forms
DataBind() method is not required in the case with Windows Forms. Here I am using DataGrid and not DataGridView. The code work perfectly fine with DataGridView.
















Figure 2


DataSet and DataGrid

DataGrid is better suited to display the relationship between DataSet and DataTable.

DataSet ds = new DataSet();
DataTable dt = new DataTable("Purchase");
DataColumn dc;
DataRow dr;

private void Form1_Load(object sender, EventArgs e)
{
dc = new DataColumn("ProductName");
//Adding the column to the Columns colluction of DataTable
dt.Columns.Add(dc);
dc = new DataColumn("Price");

//Adding the column to the Columns colluction of DataTable
dt.Columns.Add(dc);
//Adding a new row to the table
dr = dt.NewRow();
dr["ProductName"] = "Microsoft Mouse";
dr["Price"] = 400;
dt.Rows.Add(dr);

ds.Tables.Add(dt);
dataGrid1.DataSource = ds;
}















Figure 3

















Figure 4

















Figure 5













Specifying the DataTable of DataSet in DataGridView

ds.Tables.Add(dt);
dataGridView1.DataSource = ds.Tables["Purchase"];

Now the result will be similar to that of Figure 2.




Building Invoice Type Application

By Invoice Type Application I mean those applications used in invoicing module wherein as the user submits temporary data it won't be committed to database. Instead it would be stored temporarily and calculations like totaling done on the local data.

In the example below as the user enters the product details its sub total will be displayed below the grid.






















Here I am using the simplified code for creating DataColums and DataRows.


DataTable dt = new DataTable("Purchase");

private void Form1_Load(object sender, EventArgs e)
{

dt.Columns.Add("ProductName", typeof(string ));
dt.Columns.Add("Price", typeof(int));
}

private void btnAdd_Click(object sender, EventArgs e)
{
int intTotal = 0;
dt.Rows.Add(txtProductName.Text, txtUnitPrice.Text );
ShowData();

foreach (DataRow dr in dt.Rows)
intTotal += Int32.Parse(dr["Price"].ToString());

lblTotal.Text = intTotal.ToString();
}

private void ShowData()
{
dataGridView1.DataSource = dt;
txtProductName.Text = "";
txtUnitPrice.Text = "";
txtProductName.Focus();
}



























Related Blogs
Filling Windows Forms ListView with DataTable
Xml with .Net