Dapper ORM for .Net

Dapper is opensource micro ORM framework that will help you access data from database in .Net projects. The project is maintained by stackoverflow community and hosted on GitHub

Usually database query return table which contains rows of information. In the object based programming world require data as class/objects. ORM help us to convert row into an object. It can also perform all CURD operations too.

The ORM can be utilized in Model-View-Model View pattern and it will help minimize the complexity of the code.

Dapper is not a database specific package. It can be used with any database (SQL Server,MySQL, MS Access etc). It uses a IDbConnection for performing operations.

It does not replacing anything at all, a simple example will explain the concept.

Dapper Object List example

using Dapper;
​
class Group
    {
        public int ID { get; set; }
        public string g_name { get; set; }
         
        public double g_dr_loc { get; set; }
        public static List<Group> GetAll()
        {
            using (IDbConnection db = new System.Data.OleDb.OleDbConnection(System.Configuration.ConfigurationManager.ConnectionStrings["cstring"].ConnectionString))
            {
                return db.Query<Group>("select id,g_name from groups").ToList();
            }
        }
​
    }
​
//calling 
var groups=Group.GetAll()

Here in the example, the Group model’s GetAll() static method will query all the rows into List of Group(model) using the Dapper. Without dapper you have to use Linq or a loop to store the row as a list of objects.

You can see how Linq can be used to achieve this in the following posts

Linq – suming rows,create list of custom fields,create model class object list from data table - Linq - suming rows,create list of custom fieds,create model class list from data table

Create Checked List Box in WPF C#

Checked List box is the combination of Text and Check box. In the C# tool box you have Listbox and Combo box , not the checked listbox.

The Data template become the savior for you. We can create a Data Template for our List box.

In fact Data template can be used to customize the look and feel of the listbox items, what ever you wish.

Let’s add a Simple ListBox with Check box using XAML code which bind the Product List

<ListBox Style="{DynamicResource prent_and_groups_List}"   Background="LightGreen" SelectedItem="SlectedAccount" ItemsSource="{Binding Products}"   Grid.Row="1" Grid.Column="1"  Grid.RowSpan="2" >
            <ListBox.ItemTemplate>
                <DataTemplate>
                    <CheckBox IsChecked="{Binding Ischecked}" Content="{Binding Name}" />
                </DataTemplate>
            </ListBox.ItemTemplate>
            
        </ListBox>

Nullable date with Linq and Model class in C#

In C# Model class you may have experienced a nullable Error while fetching the rows with nullable column from database to your Model objects.

To treat the nullable in proper way , you have to tell your Model class and Linq this is nullable field.

Model class

*The nullable is applied to Value Type .This is not required for string

In the Model class I have a field edate which is in database is a nullable field. I want to make this field nullable in my model

 class StocKBatchModel
    {
        public int B_Id { get; set; }        
        public string Name { get; set; }
        public DateTime? Edate { get; set; }
}

By placing ? after the type of my class property , it becomes nullable. Say the DateTime becomes nullable , I can use the same in Linq in my LInq query as follows

 
var blist = (from m in batch_table.AsEnumerable()                                 
                                 select new
                                 {
                                     obj = new Models.StocKBatchModel()
                                     {
                                         B_Id = m.Field<int>("b_id"),
                                         Name = m.Field<string>("name"),
                                         Edate = m.Field<DateTime?>("exp_date"),                                       
                                 }.obj).ToList();

Hope this will helpful for someone.

How to find sum from obervablecollection list in C#

This example shows how you can simply find aggregate functions like sum, average etc from a Obervablecollection list.

ObservableCollection and Model-View-ViewModel is a ideal for a typical WPF application, these are programmers two of the best tools.

In this example I am using a model class , and want to find sum of inventory qty from batch list.

double qty=_batches.Sum((b) => b.qty));

Here _batches is representing the ObservableCollection of Model batch.

Add a alternate color to ListView rows in WPF using XAML

Using Alteration count you can add Datagrid like alternate background for ListViewItems. There is no alternate background property for ListView control in WPF, instead we can made this possible with style triggers.

Style triggers work with property and we can track the change with trigger value value.

Styles

The alternate color we want to apply is for ListViewItems , so we have to create a Itemcontainer style for ListView as follows.

    <Style TargetType="ListViewItem" x:Key="gridview_itemcontainer1" >
        <Setter Property="Foreground" Value="DarkBlue"/>
        <Setter Property="FontSize" Value="13.5"/>       
    </Style>

You can define the style as resource file or can define as ListView.Resources in your xaml portion of UI.

As ListViewResource

<ListView AlternationCount=2 >
  <ListView.Resources>
   <Style TargetType="ListViewItem" >
        <Setter Property="Foreground" Value="DarkBlue"/>
        <Setter Property="FontSize" Value="13.5"/>       
    </Style>
  </ListView.Resources>

<ListView/>

In our style we are define background and font using Setter. Likewise we can add some Style Triggers. We want to change the color of ListViewItems according to the AlternationIndex value.

Style.Triggers

Add the following style trigger section in our style.

<Style.Triggers>
    <Trigger Property="ItemsControl.AlternationIndex" Value="0">
       <Setter Property="Background" Value="LightBlue"></Setter>
    </Trigger>
    <Trigger Property="ItemsControl.AlternationIndex" Value="1">
       <Setter Property="Background" Value="LightYellow"></Setter>
    </Trigger>
 </Style.Triggers>

We had added two trigger for checking the alternationIndex value and a associated setter for setting background property of ListViewItem.

Our style is finished and purpose is served.

If you are use resource file you can assign the style to Item container as ItemContainerStyle=”{DynamicResource <RESOURCEKEY>}”. Replace the RESOURCE KEY with your own style key.

Change ComboBoxitem color using xaml in WPF

You can reuse styles to customize appearance of components s in WPF . To change the color of Combo items ,you have to create a style for itemcontainer

This can be done as follows

ItemContainer Style

<Style x:Key="Combo+" TargetType="ComboBoxItem">
        <Setter Property="FocusVisualStyle" Value="{x:Null}" />
        <Setter Property="Background" Value="AliceBlue" />
        <Style.Triggers>
            <Trigger Property="IsSelected" Value="True">
                <Setter Property="Foreground" Value="Red"/>
                <Setter Property="Background" Value="GreenYellow"/>
            </Trigger>
        </Style.Triggers>
    </Style>

I have also another style for combo which set font, color etc . You can call the container style directly to Listbox or call from another style which is I am doing right here

Within another style

 <Style x:Key="Combo" TargetType="ComboBox">
        <Setter Property="FontSize" Value="18"/>
        <Setter Property="ItemContainerStyle" Value="{DynamicResource Combo+}"/>         
    </Style>

Within List [Direct way]

 <ComboBox ItemContainerStyle=""="{DynamicResource Combo+}" KeyUp="_TabPress"  TabIndex="10"   x:Name="lstb_base" Grid.Column="1" Grid.Row="10" Margin="0.2,31.6,0,2" Grid.RowSpan="2" Width="99" HorizontalAlignment="Left" >
 <ComboBox Style="{DynamicResource Combo}" KeyUp="_TabPress"  TabIndex="10"   x:Name="lstb_base" Grid.Column="1" Grid.Row="10" Margin="0.2,31.6,0,2" Grid.RowSpan="2" Width="99" HorizontalAlignment="Left" >

In the XAML

In my xaml I just calling the style using

   <ComboBox Style="{DynamicResource Combo}" KeyUp="_TabPress"  TabIndex="10"   x:Name="lstb_base" Grid.Column="1" Grid.Row="10" Margin="0.2,31.6,0,2" Grid.RowSpan="2" Width="99" HorizontalAlignment="Left" >

listebox selecteditem at runtime in WPF

Listbox’s selected item property used to get/set values at runtime. In WPF you can used ViewModel to get selected item automatically to the ViewModel, that is for another article. Let’s learn how to set selected item at runtime,

• First we need to identify the element in List Item collection
• Set the property value

Getting the Item

var itt = (ListBoxItem)lstb_base.Items.Cast<ListBoxItem>().Where((s, b) => s.Content.Equals(r.M_Base)).SingleOrDefault();
                             

Set property of SelectedItem

lstb_base.SelectedItem = itt;