Thursday, May 29, 2014

A Few SQLite Tips

 SQLite is an open source database that is written in C.  It is similar to say an access .mdb file or a Microsoft SQL Server CE file only databases in that it is a file and runs in the same process (well, some caviots here with newer mods to SQLite but I'm not familiar enough there to comment) as your deployed program.  It is used by Firefox, Chrome, a lot of phone app developers, and now by me.  SQL Server is overkill for many of the little web applications I write and although I like SQL Server Compact Edition it looks like it is dead in the water [LINK].

1) Once you have created a table in SQLite, you cannot add a new column to that table with the alter command that has a default value [LINK].

2) It seems like time for SQLite is defaulted to GMT, which in my opinion is a good thing.  For default dates in tables, use CURRENT_TIMESTAMP.

3) SQLite-Net looks really cool, but I haven't been able to hack it to work for web development.  If you are using Windows 8 and developing APPS or Desktop applications, take a look at it. [LINK].

4) A really great and free GUI manager for SQLite is a plugin for Firefox called SQLite Manager. Look for it in the Firefox Plugins.  More info [LINK].

5) It looks like the main .Net SQLite Data Connection enabler is System.Data.Sqlite.  The full Nuget package contains Linq and Entity Framework support.  More info [LINK].

6) There is a version of SQLite written in managed .net code, but it looks like it is no longer being revised.  Maybe Microsoft will pick it up.  Info [LINK].  You might be able to hack SQLite-net to use this version, as it looks like it was originally intended to use it.  I tried for a few hours but gave up.  Maybe I'll take another stab at it.

7) There are no stored procs with SQLite (although I believe there is a fork in development that supports that, but I'm not sure how stable it is).  You can however create user defined functions.

8) When to use SQLite [LINK].

9) Interestingly, if you try to connect to a SQLite DB that doesn't exist the SQLite Connection object will create a new blank DB file for you with the name that you were looking for.  This actually caused me some confusion as has an extra character in my connection string and suddenly I couldn't query any of my tables...duh, they didn't exist because I was querying a new database.

10) If you are familiar with Transact SQL and Microsoft SQL Server (or MySQL, Postgress, Oracle) I don't think you will have any problems using SQLite.

11) Data types on columns are more recommendations than set and fast.  I believe if you try you can save strings in int fields and mix and match.  This might have changed with the 3.x version but from what I have been reading this at lest used to be very true.  Who knows in some cases this could be a good thing, but in most if you aren't careful this might cause havoc.

12) For bulk inserts you want to use SQLite transactions else SQLite will be very slow [LINK]

13) SQLite select queries are CASE SENSITIVE by default.  You can get around this by using COLLATE NOCASE after your where condition like select * from mytable where colum1 = @somevalue COLLATE NOCASE.  A post explaining the particulars can be found [HERE].

14) There is no "Select top * or Select top 10" in SQLite.  Instead use limit. Example: "Select blah from mytable where blah order by blah limit 10"

I got my start in web development using classic ASP and Access MDB databases.  I could crank stuff out FAST.  I think now for some of my projects using MVC (thought without most of the MVC, just using Razor pages almost like development with WebMatrix) and SQLite might be a sweet spot for me as far as making development fun and productive.  I plan to update this page as I find more little quirks using SQLite with ASP.Net.  I also will be posting some source code examples.

Tuesday, May 27, 2014

Fixing Telerik's RadToolTip After Latest Releases Broke It


Ok, so I had the following scenario.  I was using a Telerik Radgrid with a gridtemplate column that displayed user comments.  Often the comments were long so I would truncate the comment text that would be displayed, and then I add a RadToolTip with the target of the comment label in the grid that on hover would display the full comment text in a tool tip.  But then something happened...the latest changes broke the tool tip so that if I did anything on the grid like sort, update a row, whatever often the comment tool tip would become blank.  I read that it has something to do with how the radtooltip now stores it's content in the viewstate...well that was the excuse, but IMHO it is broke as it worked before and it doesn't work now.

The Fix

Step 1) Add a RadToolTipManager to your page, outside of your update pannels.  Here is an example

   <telerik:RadToolTipManager ID="RadToolTipManager1" runat="server" OnAjaxUpdate="RadToolTipManager1_AjaxUpdate"  
    RelativeTo="Element" Position="TopLeft" Width="450" ManualClose="True" ShowDelay="600"  

Notice there is an OnAjaxUpdate method.  You will have to add that in your code behind, but let's worry about that in a second.

Step 2: Add a or update an existing RadGrid ItemDataBound event so we can add a tool tip on to each comment field.

 if ( e.Item.OwnerTableView.Name == "MasterTableName" && ( e.Item.ItemType == GridItemType.Item || e.Item.ItemType == GridItemType.AlternatingItem ) )  
     Control cntl = e.Item.FindControl ( "mylabelintemplatecontrolidwithtruncatedtext" );  
     if ( cntl != null )  
      if ( this.RadToolTipManager1 != null )  
       this.RadToolTipManager1.TargetControls.Add ( cntl.ClientID, true );  

Note: I'm doing the e.Item.OwnerTableView.Name check because I'm using a grid with detail tables. If you don't have any detail tables you can skip that part of the check.  You might also add in a check to make sure the Radgrid is in read only mode, I didn't but I might go back and do that just to make it more efficient.

Step 3) Add your RadToolTipManager Ajax update method.

   protected void RadToolTipManager1_AjaxUpdate ( object sender, Telerik.Web.UI.ToolTipUpdateEventArgs e )  
    for ( int i = 0 ; i < MyGridView.Items.Count ; i++ )  
     if ( MyGridView.Items[ i ].OwnerTableView.Name == "MasterTableName" && ( MyGridView.Items[ i ].ItemType == GridItemType.Item || MyGridView.Items[ i ].ItemType == GridItemType.AlternatingItem ) )  
      /* unfortunately only get client ID back on ajax method, so we have to loop to find matching client ID */  
      Control cntl = MyGridView.Items[ i ].FindControl ( "mylabelintemplatecontrolidwithtruncatedtext" );  
      if ( cntl != null && cntl.ClientID == e.TargetControlID )  
       /* get row ID number */  
       int myID = Convert.ToInt32(MyGridView.Items[ i ].GetDataKeyValue ( "myID" ));  
        /* now get full comment text out of the database */  
        var rv = ( from myrow in ( (DataView)MyDataSet.Select ( DataSourceSelectArguments.Empty ) ).ToTable ().AsEnumerable ()  
              where ( myrow.Field<int> ( "myID" ) == myID )  
              select myrow ).FirstOrDefault ();  
       Label lblInsideToolTip = new Label ();  
       lblInsideToolTip.Text = rv[ "mycommenttextfield" ].ToString ();  
       e.UpdatePanel.ContentTemplateContainer.Controls.Add ( lblInsideToolTip );  

Ok, a few comments on Step 3.  I could only get the ClientID of the target control from the Telerik.Web.UI.ToolTipUpdateEventArgs object, so I had to loop through all of my RadGrid items and then try match client ID's of the column I was looking for.  Kind of a bummer. I elected to grab the comment text from the Dataset itself.  There are other ways of doing this.  Also this is just demo code, you might want to add this in a big try catch or at least check for more nulls.


This seems to work.  I can sort, edit, insert rows and now the comment field's tool tip is always current.  I hope this code helps. Also be sure to add the using System.Linq at the top of the page in your code behind if it isn't there.

Tuesday, May 13, 2014

Web API 2 CRUD Basic Example

Here is a basic Microsoft Web API 2 CRUD example.  The Source code is [HERE] (this gives you the full directory and list of files, just File->Download to download the full zipped project).  I used Visual Studio 2013 and local DB.  I'm not using MVC or any ORM / Entity Framework stuff...this is a very basic example to help get you going.

You will need to change the database path in the web.config file.

Happy Coding!

Monday, May 12, 2014

SQL Dates in Where Clauses

I always forget how to do this, so I'm making a post more for my quick reference than anyone else's.  Anyway, this format should work not matter what culture you format your dates in...using Microsoft SQL Server variants anyway...

WHERE datetime_column BETWEEN '20081220 00:00:00.000'
                          AND '20081220 23:59:59.997'

'YYYYMMDD HH:MM:SS:XXX' xxx being milliseconds.

More information here-