Using LINQ to SQL with SQLite database

In my previous short article we have discussed about how to use Ado.net Entity Framework with SQLite database. And we have witnessed that its almost as like as SQL server in context of ease and usability when you have the right provider.

Today we are going to see how we can use LINQ to SQL with SQLite database. We always looks for ways that would make our life easy and most important no more re-inventing the wheel. By default you can not drag and drop tables from server explorer or from data connections. Its because we don’t have any built in provider that support LINQ and if you try to do so you would certainly encounter with the following error.

Now, I know what am I going to show you wont please you but it certainly serve our purpose to use LINQ with SQLite. There is no magic or rocket engineering behind this, we have a DBLinq open source distribution that come with various sort of database support. For this particular case lets stick to SQLite. The binaries can be found here,  http://code.google.com/p/dblinq2007/, please download latest binaries from this site.

Now its time to generate the OurDatabase.dbml file. the steps are not that much complex, Located the DBLinq package that you downloaded from Google code, then unzip in a suitable folder location where you can access it easily.

Note that you need to copy the System.Data.SQLite dll in the DBLinq directory. 

Step 1: Create the dbml file

DbMetal /provider:Sqlite /conn "Data Source=File.db3" /dbml:File.dbml

Step 2: Create the code file for the dbml file

DbMetal /code:File.cs File.dbml

Copy those files to the files. That’s it you are good to go.

Make sure that every thing compiles okay by hitting build. Bellow I have put done a simple code snippet to demonstrate how to use this dbml to retrieve data.

 protected void Page_Load(object sender, EventArgs e)
        {
            string ConStr = "Data Source=" + HttpContext.Current.Server.MapPath("App_Data\\File.s3db") + ";Version=3;";
            var connection = new SQLiteConnection(
                    ConStr
                );
            connection.Open();
            var db = new Main(connection,new SqliteVendor());
            var users = db.User;
            var item = users.Take(10).ToList();
        }

I hope this above code is self explanatory. Best of luck and happy coding.

WPF ItemsControl with alternating item and hover effect

Introduction

In this short article, we are going to see a few tricks about WPF ItemsControl. ItemsControl is one of the simplest yet powerful controls in WPF. ItemsControl is just like the Repeater control in ASP.NET. It supports Binding and supports custom templates to display data. For a detail study about the power of ItemsControl, please read the MSDN documentation here. In this article, we are going to focus on how to display the alternating items in an ItemsControl and apply a few effects on the items of an ItemsControl.

Use Alternating Item Style

ItemsControl can be used to display data both via populating the ItemsControl.Items collection or by specifying a an ItemsSource in the ItemControl.ItemsSource property. In this example, we are going to use an ItemsSource to display our data. Let's assume we have a custom class called Country and we want to display the information about Country in an ItemsControl. Shown below is a simple code listing to get the total idea.

public partial class Window1 : Window
{
    public Window1()
    {
        loadDemoData();
        this.DataContext = this;
    }

    public static readonly DependencyProperty DataListProperty = 
        DependencyProperty.Register(
        "DataList", typeof(ObservableCollection), typeof(Window1));
    
    private void loadDemoData()
    {
        ObservableCollection data = new ObservableCollection();
        Country c1 = new Country { ID = "1", Name = "Bangladesh", 
            Capital = "Dhaka", Continent = "Asia" };
        Country c2 = new Country { ID = "2", Name = "India", 
            Capital = "Delhi", Continent = "Asia" };
        Country c3 = new Country { ID = "3", Name = "U.S.A", 
            Capital = "Washington", Continent = "North America" };
        Country c4 = new Country { ID = "4", Name = "Australia", 
            Capital = "Canbarra", Continent = "Australia" };
        Country c5 = new Country { ID = "5", Name = "Kenya", 
            Capital = "", Continent = "Africa" };
        data.Add(c1);
        data.Add(c2);
        data.Add(c3);
        data.Add(c4);
        data.Add(c5);
        this.SetValue(Window1.DataListProperty, data);
    }

    public class Country
    {
        public string ID { get; set; }
        public string Name { get; set; }
        public string Continent { get; set; }
        public string Capital { set; get; }
    }
}

In a few examples available in the online community, I have seen that ItemsControl is not directly used to display the idea of alternating items, rather a list box, or perhaps a more higher level control is shown to display alternating items. In this section, we will see how we can add styles in the items and alternating items of an ItemsControl just by using a simple DataTemplate. When we use the ItemsControl, the items of ItemsControl are generally rendered inside a ContentPresenter, which has only a few basic properties, and we can not assign the background or foreground.

Using a DataTemplate and the smart use of styles on a DataTemplate might just solve our problem. Below is a complete code listing of how to do the intended effect.

<Window x:Class="WPFDemo.Window1"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        Title="ItemsControl" MinHeight="350" 
        MinWidth="550" Background="#f5f5f5">
    <Window.Resources>
        <Style x:Key="alternatingWithTriggers" 
               TargetType="{x:Type ContentPresenter}">
            <Setter Property="Height" Value="25"></Setter>
        </Style>
        <DataTemplate x:Key="MyItemTemplate">
            <Border x:Name="yahoo">
                <StackPanel Orientation="Horizontal">
                    <Label Content="{Binding Name}"></Label>
                    <Label Content="is in"></Label>
                    <Label Content="{Binding Continent}"></Label>
                </StackPanel>
            </Border>
            <DataTemplate.Triggers>
                <Trigger Property="ItemsControl.AlternationIndex" Value="0">
                    <Setter Property="Background" Value="#e9e9e9" 
                            TargetName="yahoo"></Setter>
                </Trigger>
                <Trigger Property="ItemsControl.AlternationIndex" Value="1">
                    <Setter Property="Background" Value="#d9d9d9" 
                            TargetName="yahoo"></Setter>
                </Trigger>
            </DataTemplate.Triggers>
        </DataTemplate>
    </Window.Resources>
    <Grid MinHeight="350" MinWidth="550">
        <ItemsControl ItemsSource="{Binding DataList}" 
            ItemContainerStyle="{StaticResource alternatingWithTriggers}" 
            AlternationCount="2" 
            ItemTemplate="{StaticResource MyItemTemplate}"/>
    </Grid>
</Window>

In the above code listing, we have not done any style changes in the item container styles; rather, we override the item template and use the item templates style in such a way that it shows the alternate items style. The main engine to do this trick is the AlternatingItemIndex property of ItemsControl. In the data template, we used a trigger to check the index of the item and then apply the style to the DataTemplates of one of the inner elements.

Hover Effect

Applying a hover effect is now easy since we know how to apply styles using a trigger. But, in this case, we won't use AlternatingItemIndex. We want to apply a mouse over effect regardless of whether it is an item or alternating item. Below is a code snippet showing the hover effect on items.

<Style x:Key="onmouseover" TargetType="{x:Type StackPanel}"> 
    <Style.Triggers>
        <Trigger Property="IsMouseOver" Value="True">
            <Setter Property="Background" Value="Yellow">
            </Setter>
        </Trigger>
    </Style.Triggers>
</Style>
<DataTemplate x:Key="MyItemTemplate">
    <Border x:Name="yahoo">
        <StackPanel Orientation="Horizontal" 
                    Style="{StaticResource onmouseover}">
            <Label Content="{Binding Name}"></Label>
            <Label Content="is in"></Label>
            <Label Content="{Binding Continent}"></Label>
        </StackPanel>
    </Border>
    <DataTemplate.Triggers>
        <Trigger Property="ItemsControl.AlternationIndex" Value="0">
            <Setter Property="Background" Value="#e9e9e9" 
                    TargetName="yahoo"></Setter>
        </Trigger>
        <Trigger Property="ItemsControl.AlternationIndex" Value="1">
            <Setter Property="Background" Value="#d9d9d9" 
                    TargetName="yahoo"></Setter>
        </Trigger>
    </DataTemplate.Triggers>
</DataTemplate>

In the above code snippet, you can see that we have applied the style on a StackPanel. The style of hover on border on the parent control of the data template will not work, since we have already added styles using a trigger in the data template. So, we define a separate style for the StackPanel and apply to it. And, that concludes our mission.

Summary

In this short article, we saw how we can display alternating items using the ItemsControl. In this demonstration, we used AlternatingItemCount as 2; we can set it to greater than two, but to get the alternating item effect, it must be larger than 1. The styles must be modified to support AlternatingItemCount. For more details on ItemsControl, please visit the links in the References section below.

References

• http://msdn.microsoft.com/en-us/library/system.windows.controls.itemscontrol.aspx
  
• http://msdn.microsoft.com/en-us/library/system.windows.controls.itemscontrol.alternationcount.aspx
  
• http://blogs.msdn.com/vinsibal/archive/2008/05/28/wpf-3-5-sp1-feature-alternating-rows.aspx

Tooltip service and tooltip facility of WPF

Introduction

Tooltip is a very important part of any modern software. It helps and suggests what to do with any user interaction item control or what a particular content or legends means. In this article, I am going to discuss about a small yet interesting feature of WPF, the “ToolTipService”. Using tooltip service, we can do a good amount of customization on how we can display tooltip.

Let us put down a small example how we can define a tooltip of any content in XAML markup.

<Button Width="100" Height="30" Content="Click Me!"
                ToolTip="Click here to do some thing"></Button>

WPF has two default events to help developers do some level of customization, the events are ToolTipOpening and ToolTipClosing.

Customizing the Tooltip

In almost all the technology that exists except WPF, tooltip is not that customizable. But in WPF, almost all the gates are opened. We can customize tooltip in such a way that we can put any content in a tooltip, event a data grid! Just kidding, no scenario can be that bizarre. But no kidding, we can put almost anything in a tooltip content. Enough talk, let's put an example and see actually how pretty handy it is.

<Button Width="100" Height="30" Content="Click Me!">
    <Button.ToolTip>
        <Border Margin="-4,0,-4,-3" Padding="10" Background="Silver">
            <Border.BitmapEffect>
                <OuterGlowBitmapEffect></OuterGlowBitmapEffect>
            </Border.BitmapEffect>
            <Label>This is a simple customization of tooltip</Label>
        </Border>
    </Button.ToolTip>
</Button>

In the above example, we have added a border as a content of the tooltip and also applied few changes on the look and feel of the border. And the effect after running the application looks like the following image:

Using the Tooltip Service

So we now know that tooltip is a very good level of customizable in WPF. Now let's explore a service that is evolved around tooltip - the “ToolTipService”. Normally a tooltip is displayed exactly where the mouse pointer is. If we want to display the tooltip always at a particular position, we can just modify the markup a little using ToolTipService and set the ToolTipService.Placement property to our desired location. The code below is given to show a tooltip always at the bottom of a button element:

<Button Width="100" Height="30" Content="Click Me!" 
        ToolTipService.Placement="Bottom">
    <Button.ToolTip>
        <Border Margin="-4,0,-4,-3" Padding="10" Background="Silver">
            <Border.BitmapEffect>
                <OuterGlowBitmapEffect></OuterGlowBitmapEffect>
            </Border.BitmapEffect>
            <Label>This is a simple customization of tooltip</Label>
        </Border>
    </Button.ToolTip>
</Button>

And the result looks like the following image:

Let's see another very interesting feature about the tooltipservice. If we have a disable element, the tooltip does not get shown when we move over on the element. But using ToolTipService.ShowOnDisabled property, we can display tooltip even on a disabled item. Below, the code is given:

<Button Width="100" Height="30" Content="Click Me!" 
    IsEnabled="False"
    ToolTipService.ShowOnDisabled="True"
    ToolTipService.Placement="Bottom">
    <Button.ToolTip>
        <Border Margin="-4,0,-4,-3" Padding="10" Background="Silver">
            <Border.BitmapEffect>
                <OuterGlowBitmapEffect></OuterGlowBitmapEffect>
            </Border.BitmapEffect>
            <Label>This is a simple customization of tooltip</Label>
        </Border>
    </Button.ToolTip>
</Button>

And the effect of the above code looks like this:

Let's put a little more interesting code here.

<Button Width="100" Height="30" Content="Click Me!" 
        ToolTipService.HasDropShadow="False"
        ToolTipService.InitialShowDelay="100"
        ToolTipService.ShowDuration="5000"
        ToolTipService.ShowOnDisabled="True"
        ToolTipService.Placement="Bottom">
    <Button.ToolTip>
        <Border Margin="-4,0,-4,-3" Padding="10" Background="Silver">
            <Border.BitmapEffect>
                <OuterGlowBitmapEffect></OuterGlowBitmapEffect>
            </Border.BitmapEffect>
            <Label>This is a simple customization of tooltip</Label>
        </Border>
    </Button.ToolTip>
</Button>

In the above code, we have set the ToolTipService.InitialShowDelay to 100 ms which allows the tooltip to show almost just after we mouse over the button. Now we have made the tooltip stay open a little longer using ToolTipService.ShowDuration to 5000 ms. We can customize more using the other properties of ToolTipService.

Summary

In this sort of article, we have seen a few interesting tricks about tooltip using ToolTipService. For more information about how to customize the tooltip, please visit the references. Best of luck and happy programming.

References

1. http://msdn.microsoft.com/en-us/library/system.windows.controls.tooltipservice.aspx [About ToolTipService Class]
   
2. http://msdn.microsoft.com/en-us/library/ms752368.aspx [How to Position a ToolTip]
   
3. http://msdn.microsoft.com/en-us/library/system.windows.controls.tooltipservice_fields.aspx [Fields of ToolTip service]

Web browser control does not display data in WPF window if window's allow transparency is true

Windows from have web browser control since .net 2.0, WPF also introduced a browser control from .net 3.5 sp1, well the controls are very good as far as functionality is concern, i my self prefer windows forms browser since it have more control with the functionality.

Today i encountered with a strange problem, in one of my application i used a modal dialog to present data as light box effect, inside the modal i added a browser control and then populated appropriate data, when i run the application the modal showed up, but the browser was just blank, no data what so ever. Later after few trial and error i found that my modal windows allow transparency property is set to true,  when i turned it to false, browser's content showed up.

Its a small problem perhaps many of you already knew about it, but since it killed almost two hour of my time, i thought its worth sharing. Happy coding..

Using SQLite with Entity Framework in Visual Studio 2010

Introduction

In my previews blog post we have seen how using a simple “data provider” provided by http://sqlite.phxsoftware.com/ can make our life easier in context of SQLite in .net. In this short article we are going to see how we can use SQLite to generate Entity Framework Data model.

Choosing the Provider

First thing first create any type of project in Visual studio 2010. But I would prefer an web application or windows forms or perhaps a WPF application. Now click on project and select add new item to add a new item type. From Add new item wizard select “Ado.net Entity Data Model” and click on add this will brought up the Entity data model wizard.

• Step 1: Select Generate from Database
• Step 2: In next window select New connection
• Step 3: Connection properties window will popup and the select right kind of provider for SQLite.
• Step 4: After that the Connection properties will have browser or New db,

• Step 5: Now select existing db if you have using browser or New to create new db.
• Step 6: Continue with the wizard to select database table items to include in model.
• Step 7: Click finish to close the wizard and a data model will be generated for you.

If you had any relationship in the database those will be automatically be synchronized in data model, that’s it we are good to jump in the code to retrieve data and do all type of necessary stuff that we must do in a database. Best of luck and happy coding.

Edit SQLite database using Visual Studio 2010 or SQLite Administrator

Introduction

In my previous blog I have shared few useful information about SQLite Embedded database and how to use its provider to create an connection using System.Data.SQLite. We have also seen a simple tool named “SQLite Administrator” which is very handy to use with SQLite database. In this short article we will see that how we can create and manipulate  data using Visual Studio 2010 or SQLite Administrator.

Creating Table in Visual Studio 2010

Select the SQLite connection that you created and expend the Tables node, then right click and select “Add New Table” that’s it you are good to go to add a table just like the bellow figure. I am sure you guys have done this hundreds of time in SQL server, this time its just SQLite. you might see a warning when you first enter into edit of table editor.

Using SQLite Administrator

Double click on the SQLite database file and right click on tables and click "Create Table”, rest of the UI is fanatic to Create the table. Set field as primary key or assign auto increment to primary key all can be done via simple clicks. So no custom hand written SQL any more.

Creating Relations in Visual Studio 2010

Creating table relations are easy in visual studio, just click on relation icon, the icon beside primary key, in fact to be precise right side icon of primary key button. Or perhaps you can select a table and then click on relations, this will popup an widget to create relation.

Creating auto increment primary key is a not that much difficult also, now a days in every table we use auto increment key so we must discuss about it. at first glance we didn’t find any button or option to assign it directly. You can use SQLite Administrator to add it in few clicks, but to add in Visual Studio 2010 you got to bring the indexes window, just click on Indexes after that you will find option to add auto increment column.

I hope this information's will be helpful to us to work better with SQLite. Hope to write more information's about SQLite in near future. Happy coding.