Thursday, September 27, 2012

WPF Datagrid (Prevent DataGrid last row from being sorted on column header clicked!)

Here's the WPF version of prevent total rows from being sorted.
Images:
1. Form Load, no column header is clicked (unsorted records)

2. Name header is clicked (sorting by column). The names are sorted alphabetically.

Below are the methods used:
Code:
      /// <summary>  
      /// column header clicked(sorting)  
      /// </summary>  
      private void dgProducts_Sorting(object sender, DataGridSortingEventArgs e)  
      {  
        DataRowView rv = (DataRowView)dgProducts.Items[dgProducts.Items.Count - 1];  
        if (rv[0].ToString().Contains("Total:"))  
        {  
          dvCopy = dgProducts.Items.SourceCollection as DataView;  
          rv.Delete();  
        }  
        sorted_aborted = e.Handled;  
      }  

      /// <summary>  
      /// layout is updated  
      /// </summary>  
      private void dgProducts_LayoutUpdated(object sender, EventArgs e)  
      {  
        if (!sorted_aborted)  
        {          
          //method to add totals computation  
          ShowProductSorted();  
          sorted_aborted = true;  
        }  
        else  
        {  
          DisableLastRow();  
        }  
      }    

      /// <summary>  
      /// show sorted products  
      /// </summary>  
      private void ShowProductSorted()  
      {  
        Total = 0;  
        DataTable dt = new DataTable();  
        dt = dvCopy.ToTable();  
        dvCopy = null;  
        dgProducts.ItemsSource = null;  
        foreach (DataRow row in dt.Rows)  
        {  
          Total = Total + Convert.ToDouble(row[1].ToString());  
        }  
        DataRow dr1 = dt.NewRow();  
        dr1[1] = Total;  
        dt.Rows.Add(dr1);  
        yourdatagrid.ItemsSource = dt.AsDataView();        
      }  

Sunday, September 23, 2012

Add checkbox in WPF Datagrid DatagridTemplateColumnHeader(for checkall)

Here's how to add checkbox in WPF Datagrid DatagridTemplateColumn to simulate checkall. I have included the TSQL script, XAML and C# codes. The source code is available for download here: WPF DataGrid Check/Uncheck All Functionality

Perform these steps below
1. Create an MSSQL database with the following Product Table schema below:
Table: Products
Fields:
-    ProductID (int, autoincrement)
-    ProductName (varchar)
-    UnitPrice (decimal)
-    QuantityPerUnit (varchar)
-    Discontinue (bit) 
SQL Script:
 USE [Your_Database_Name]  
 GO  
 /****** Object: Table [dbo].[Products]  Script Date: 05/27/2013 14:07:39 ******/  
 SET ANSI_NULLS ON  
 GO  
 SET QUOTED_IDENTIFIER ON  
 GO  
 SET ANSI_PADDING ON  
 GO  
 CREATE TABLE [dbo].[Products](  
      [ProductID] [int] NOT NULL,  
      [ProductName] [varchar](50) NULL,  
      [UnitPrice] [decimal](18, 2) NULL,  
      [QuantityPerUnit] [varchar](50) NULL,  
      [Discontinue] [bit] NULL,  
  CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED   
 (  
      [productID] ASC  
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]  
 ) ON [PRIMARY]  
 GO  
 SET ANSI_PADDING OFF  
 GO  
Note: You may add values after creating the table.

2. Change the App.config with your database settings.
 <?xml version="1.0"?>   
  <configuration>   
  <connectionStrings>   
   <add name="products"   
    connectionString="data source=testPC\SQLEXPRESS;Initial Catalog=your_test_database;User ID=your_user_id;Password=your_password;"   
    providerName="System.Data.SqlClient" />   
  </connectionStrings>   
  <startup><supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/></startup></configuration>   

3. Update your xaml and classes as presented below.
Here is the DataGrid xaml:
 <DataGrid Name="dgProducts" AutoGenerateColumns="False" Grid.Row="1" Grid.RowSpan="3" Grid.ColumnSpan="2" CanUserAddRows="False">    
    <DataGrid.Columns>    
    <DataGridTextColumn Header="Name" Binding="{Binding Path=ProductName}" Width="215"/>    
    <DataGridTextColumn Header="Price" Binding="{Binding Path=UnitPrice}"/>    
    <DataGridTextColumn Header="Quantity Per Unit" Binding="{Binding Path=QuantityPerUnit}" Width="180"/>    
    <DataGridTemplateColumn>    
     <DataGridTemplateColumn.Header>    
     <CheckBox Content="Discontinue All" Click="CheckBox_Click" />    
     </DataGridTemplateColumn.Header>    
     <DataGridTemplateColumn.CellTemplate>    
     <DataTemplate>    
      <CheckBox Name="chkDiscontinue" IsChecked="{Binding Path=Discontinue,Mode=TwoWay}" Margin="45 2 0 0" Click="chkDiscontinue_Click" />    
     </DataTemplate>    
     </DataGridTemplateColumn.CellTemplate>    
    </DataGridTemplateColumn>    
    </DataGrid.Columns>    
   </DataGrid>    
Main Window class:
public partial class MainWindow : Window
{
 public MainWindow()
 {
  InitializeComponent();
 }

 private void Window_Loaded(object sender, RoutedEventArgs e)
 {
  dgProducts.ItemsSource = Connections.GetProduct().AsDataView();
 }

 /// <summary>
 /// check all checkbox
 /// </summary>
 /// <param name="sender"></param>
 /// <param name="e"></param>
 private void CheckBox_Click(object sender, RoutedEventArgs e)
 {
  var row = GetDataGridRows(dgProducts);

  if (((CheckBox)sender).IsChecked == true)
  {
   SetCheckbox(row, true);
  }
  else
  {
   SetCheckbox(row, false);
  }
 }

 //individual checking
 private void chkDiscontinue_Click(object sender, RoutedEventArgs e)
 {
  object a = e.Source;
  CheckBox chk = (CheckBox)sender;

  DataGridRow row = FindAncestor<DataGridRow>(chk);
  if (row != null)
  {
   DataRowView rv = (DataRowView)row.Item;
   
   //LINQ or Database Method to Update Product discontinue status
   Connections.UpdateProductDiscontinue((bool)chk.IsChecked, rv["ProductName"].ToString());
  }
 }
 
 private void SetCheckbox(IEnumerable<DataGridRow> row, bool value)
 {
  //loop through datagrid rows
  foreach (DataGridRow r in row)
  {
   DataRowView rv = (DataRowView)r.Item;
   foreach (DataGridColumn column in dgProducts.Columns)
   {
    if (column.GetType().Equals(typeof(DataGridTemplateColumn)))
    {
     rv.Row["Discontinue"] = value;

     //LINQ or Database Method to Update Product discontinue status
     Connections.UpdateProductDiscontinue(value, rv.Row["productname"].ToString());                                                
    }
   }
  }
 }

 public IEnumerable<DataGridRow> GetDataGridRows(DataGrid grid)
 {
  var itemsSource = grid.ItemsSource as IEnumerable;
  if (null == itemsSource) yield return null;
  foreach (var item in itemsSource)
  {
   var row = grid.ItemContainerGenerator.ContainerFromItem(item) as DataGridRow;
   if (null != row) yield return row;
  }
 }

 #region visual tree helpers

 /// <summary>
 /// Returns the first ancester of specified type
 /// </summary>
 public static T FindAncestor<T>(DependencyObject current) where T : DependencyObject
 {
  current = VisualTreeHelper.GetParent(current);
  while (current != null)
  {
   if (current is T)
   {
    return (T)current;
   }

   current = VisualTreeHelper.GetParent(current);
  };
  return null;
 }

 private childItem FindVisualChild<childItem>(DependencyObject obj) where childItem : DependencyObject
 {
  for (int i = 0; i < VisualTreeHelper.GetChildrenCount(obj); i++)
  {
   DependencyObject child = VisualTreeHelper.GetChild(obj, i);
   if (child != null && child is childItem)
    return (childItem)child;
   else
   {
    childItem childOfChild = FindVisualChild<childItem>(child);
    if (childOfChild != null)
     return childOfChild;
   }
  }
  return null;
 }       

 #endregion
}
Database Manipulation class:
class Connections
{
 public static DataTable GetProduct()
 {
  DataSet ds = new DataSet();
  string query = "Select * from Products;";                    
   
  try
  {
   using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["products"].ConnectionString.ToString()))
   {
    SqlCommand cmd = new SqlCommand(query, conn);
    conn.Open();
    SqlDataAdapter da = new SqlDataAdapter(cmd);                    
    da.Fill(ds);
    conn.Close();
   }
  }
  catch (Exception ex)
  {
   throw ex;   
  }            
  return ds.Tables[0];
 }

 public static int UpdateProductDiscontinue(bool value, string product_name)
 {
  int result = 0;
  string query = String.Format("Update Products set discontinue = '{0}' where productname = '{1}' ;",value,product_name);

  try
  {
   using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["products"].ConnectionString.ToString()))
   {
    SqlCommand cmd = new SqlCommand(query, conn);
    conn.Open();
    result = cmd.ExecuteNonQuery();
    conn.Close();
   }
  }
  catch (Exception ex)
  {
   throw ex;
  }
  return result;
 }
}

Sample Output:
a. On form load (some of the checkbox are set to true, others are false)

b. Checkbox in column header is selected

c. Checkbox in column header is deselected

Thursday, September 20, 2012

Prevent DataGridView Last Row From Being Sorted On Column Click

Hello,
You might have a row in the DataGridView typically the last one that computes total and the grid control is unbound to a datasource. And then if a sorting event occurs, you dont' wanna include that row during sort event. So given that your application has a form and a DataGridView control, the code to perform databinding is handled in the Form Load Event.
DataGridViewRow dgRowTotalCount;
DataTable dataTable;

private void Form1_Load(object sender, EventArgs e)
{
 DataTable dt = new DataTable("tblEntTable");
 dt.Columns.Add("ID", typeof(string));
 dt.Columns.Add("Amount", typeof(decimal));
 dt.Rows.Add(new object[] { "1", 100.51 });
 dt.Rows.Add(new object[] { "2", 200.52 });
 dt.Rows.Add(new object[] { "6", 500.24 });
 dt.Rows.Add(new object[] { "8", 1000.11 });
 dt.Rows.Add(new object[] { "4", 400.31 });
 dt.Rows.Add(new object[] { "5", 600.88 });
 dt.Rows.Add(new object[] { "3", 700.11 });
 dt.Rows.Add(new object[] { "7", 700.12 });
 dt.Rows.Add(new object[] { "9", 300.12 });

 foreach (DataRow row in dt.Rows)
 {
  string id = row[0].ToString();
  decimal amount = Convert.ToDecimal(row[1].ToString());
  datagrid.Rows.Add(new object[]{ id, amount });
 }
}
To exclude the totals row from being sorted, the solution is pretty straightforward.  Clone and copy cell content values of the totals row to a DataGridRow object in the Cell Click Event of the grid. Then remove that row from the control.
private void datagrid_CellClick(object sender, DataGridViewCellEventArgs e)
{
 if (e.RowIndex == -1)
 {
  dgRowTotalCount = (DataGridViewRow)datagrid.Rows[((DataGridView)sender).Rows.Count - 1].Clone();
  for (Int32 index = 0; index < ((DataGridView)sender).Rows[((DataGridView)sender).Rows.Count - 1].Cells.Count; index++)
  {
   dgRowTotalCount.Cells[index].Value = ((DataGridView)sender).Rows[((DataGridView)sender).Rows.Count - 1].Cells[index].Value;
  }
  ((DataGridView)sender).Rows.RemoveAt(((DataGridView)sender).Rows.Count - 1);
 }
}
In the sorted event of the grid control, re-insert the row that was cloned and removed from the cell click event.
private void datagrid_Sorted(object sender, EventArgs e)
{
 datagrid.Rows.Insert(((DataGridView)sender).Rows.Count, dgRowTotalCount); 
}

Output (Records sorted by ID in ascending and descending order)
Cheers! :)

Monday, September 17, 2012

Regex Word Boundary (Not working if using a variable)

In this scenario, I have to match an exact state abbreviation (QLD) that is Queensland. I declared an array containing state constant values. Normally this would work without using a string variable in C#:
Code:
Regex.IsMatch(address, @"\bQLD\b")
However, this won't work:
Code:
if (Regex.IsMatch(address, @"\b" + str + "\b"))
The solution is to put an @ sign on both "\b" of the expression:
Code:
  string[] states = new string[]{"ACT","NSW","QLD"};  
  foreach (string str in states)  
  {  
    if (Regex.IsMatch(address, @"\b" + str + @"\b"))  
    {  
       state= str;  
       address = address.Replace(str, "").Trim();  
       break;  
    }  
  } 

Thursday, September 13, 2012

ASP.NET MVP Design Pattern (Simple)

Out of boredom, I decided to read some ASP.NET Articles and came stumbling to Dino Esposito's MVP Flavors. After reading it, I decided to translate this into an ASP.NET application. I revised specifically the Presenter class since this has most of the interaction. Below are some snippets and screen shots: Image: 1. Solution Explorer for the Sample ASP.NET MVP Site
2. On Drop Down Selection of Customer
3. Button Expand Is Clicked, Show other records on other textbox controls.
Presenter Class:
Code:
  public class Presenter  
    {  
      private IDefaultView view;  
      public Presenter(IDefaultView viewObject)  
      {  
        view = viewObject;  
      }  
      public void InitializeView()  
      {  
:        view.AddCustomer("Alfreds Futterkiste", "ALFKI");  
        view.AddCustomer("Ana Trujillo Emparedados y helados", "ANATR");  
        view.AddCustomer("Antonio Moreno Taquería", "ANTON");  
      }  
      public void ExpandCustomer()  
      {  
        // Get input data  
        string id = view.SelectedCustomer.ToString();  
        // Perform the action (through the model)  
        northwindEntities rep = new northwindEntities();  
        CustomersInfo c = (from cus in rep.Customers  
                 where cus.CustomerID == id  
                 select cus).FirstOrDefault();  
        // Update the view  
        view.CustomerID = c.CustomerID;  
        view.CompanyName = c.CompanyName;  
        view.ContactName = c.ContactName;  
        view.Country = c.Country;  
      }  
    } 
CustomerInfo Class:
Code:
// model class to hold results from  
//data context  
public partial class CustomersInfo  
{  
   public string CustomersID { get; set; }  
   public string CompanysName { get; set; }  
   public string ContactsName { get; set; }  
   public string Countrys { get; set; }     
}    
Cheers!

Wednesday, September 12, 2012

Convert time from a specific timezone to another using CONVERT_TZ()

In this scenario, we have to convert a timezone from Central USA to Hongkong time. I took note of DST here from (-06:00) to (-05:00). Here's a sample query.
SELECT CONVERT_TZ('2012-09-12 04:35:00', '-05:00', '+08:00') as convert_to_ph;
Cheers!

Monday, September 10, 2012

Adding an Item to WPF Datagrid (C#)

Here's the c# .net version.
Code:
DataGridTextColumn c1 = new DataGridTextColumn();  
c1.Header = "Test";  
c1.Binding = new Binding("test");  
 c1.Width = 473;  
dataGrid.Columns.Add(c1);  
dataGrid.Items.Add(new Names() {test="just testing"});
Class property:
Code:
public string test { get; set; }  
XAML code:
 <DataGrid AutoGenerateColumns="False" Height="289" HorizontalAlignment="Left" Margin="10,10,0,0" Name="dataGrid"   
          VerticalAlignment="Top" Width="481" Grid.ColumnSpan="2" ItemsSource="{Binding }">  
     </DataGrid>  
:)

Add an item to WPF Datagrid (VB.NET)

There was a question on visual basic forum on how to add an item to a wpf datagrid. Here's a simple example:
Code view:
Dim linkColumn As New DataGridTextColumn : Dim titleColumn As New DataGridTextColumn  
linkColumn.Header = "Links:" : titleColumn.Header = "Titles:"  
linkColumn.Width = dataGrid.Width / 2 : titleColumn.Width = dataGrid.Width / 2  
linkColumn.Binding = New System.Windows.Data.Binding("Link") : titleColumn.Binding =   
New System.Windows.Data.Binding("Title")  
dataGrid.Columns.Add(linkColumn) : dataGrid.Columns.Add(titleColumn)  
dataGrid.Items.Add(New DataItems() With {.Link = "http://testingme.com", .Title = "mytesting"})
XAML:
 <DataGrid AutoGenerateColumns="False" Height="289" HorizontalAlignment="Left" Margin="10,10,0,0" Name="dataGrid"   
          VerticalAlignment="Top" Width="481" Grid.ColumnSpan="2" ItemsSource="{Binding }">  
     </DataGrid>  
VB.NET Class
     Private _link As String  
         Public Property Link() As String  
           Get  
             Return _link  
           End Get  
          Set(ByVal l As String)  
             _link = l  
           End Set  
         End Property  
        Private _title As String  
        Public Property Title() As String  
          Get  
            Return _title  
          End Get  
          Set(ByVal t As String)  
            _title = t  
          End Set  
        End Property

That's it... :)

Sunday, September 9, 2012

CREATE DATABASE permission denied in database ‘master’.

I successfully installed MSSQL Server 2008 in my working pc. But when I tried creating a database
I encountered an error

CREATE DATABASE permission denied in database ‘master’. 


I recalled my username has no privilege as domain administrator. So, the solution was to 
1. login on my  working pc as domain administrator
2. change server authentication to mixed mode
3. change password of sa.
4. reconnect using sa account

That's it...

Tuesday, September 4, 2012

WPF TimePicker control

Based from these sources:
 a. (http://jobijoy.blogspot.com.au/2007/10/time-picker-user-control.html)
 b. Dipitimaya Patra's datepicker in Datagrid

I came up with a modified custom timepicker control embedded in WPF Datagrid with databinding features. I encountered bugs on migrating the present time picker custom control throughout development in .Visual Studio 2010 and I manage to fix them myself. The errors are specifically found on the keydown events and time updates (increment/decrement of values).

Here are the things that I did to make this work:

1. I added a dependency property to the user control
Code:
  public DateTime TimeValue  
      {  
        get { return (DateTime)GetValue(TimeProperty); }  
        set { SetValue(TimeProperty, value); }  
      }  
      public static readonly DependencyProperty TimeProperty =  
        DependencyProperty.Register("TimeValue", typeof(DateTime), typeof(TimeControlBinding),  
        new UIPropertyMetadata(DateTime.Now, new PropertyChangedCallback(OnTimeChangedTimeVal)));  
      private static void OnTimeChangedTimeVal(DependencyObject obj, DependencyPropertyChangedEventArgs e)  
      {  
        TimeControlBinding control = obj as CustomDatePickerTimeControlBinding;  
        control.Value = new TimeSpan(control.TimeValue.Hour, control.TimeValue.Minute, control.TimeValue.Second);  
      } 

2. Added a datagrid template column for the time field.
  <DataGridTemplateColumn Header="Hired Time" Width="100">   
       <DataGridTemplateColumn.CellTemplate>   
        <DataTemplate>   
         <TextBlock Text="{Binding HiredTime, StringFormat=hh:mm:ss}"/>   
        </DataTemplate>   
       </DataGridTemplateColumn.CellTemplate>   
       <DataGridTemplateColumn.CellEditingTemplate>   
        <DataTemplate>   
         <local:TimeControlBinding TimeValue="{Binding Path=HiredTime, Mode=TwoWay}" />   
        </DataTemplate>   
       </DataGridTemplateColumn.CellEditingTemplate>   
      </DataGridTemplateColumn>   
Here's how the control works:
a. on form load

b. on cell editing (used keydown or keyup to modify the values)
c. on cell leave (after modifying the time values), binding occurs.

Cheers!

Thanks to the original sources for the inputs of this control.. :)