Wednesday, December 7, 2011

Exporting nvarchar fields from SQL Server to a csv file

I am posting this more so for a reminder for myself to acknowledge myself that there is something called observation.

I am working on this data migration project(like I didnt say that earlier!) and I was exporting firstname and lastname (nvarchar fields) to a csv file and I was having issues on the final export. I was getting the following error:



I searched on the internet to find a solution to it, but couldn't find one. I was feeling frustated. Then I thought there must be something that should solve it.

I went all over again through the sql server export screens and found that little tiny checkbox that my eyes weren't able to catch before:


So I marked the "Unicode" checkbox, and I was able to export all my records successfully!

Moral of the story: Keenly observe the details before banging your head on the monitor!

Tuesday, November 29, 2011

Discarding changes in LINQ memory

I was looking for a solution just like what I used to do with ADO.NET back in the days when LINQ was non-existent.

My issue was that when I received an exception while inserting a row in SQL db (due to duplicate value already present), I wanted to discard that insertion from the memory and carry out with the next insertion. The problem was that the last insertion error was not removed from memory.

Many people suggested recreation of the DataContext object, but that was not an option for me. After some "Googling", I found the link below which was helpful:

http://graemehill.ca/discard-changes-in-linq-to-sql-datacontext

Though I did something like the one below in the catch block, where "spmap" was the new object created for insertion.
datacontext.tablename.DeleteOnSubmit(spmap);
Works for me! :)

Wednesday, November 2, 2011

Handling NULL values in SQL case statements while inserting or Updating Records

I am working on this massive data import project. Reminds me of those old days, when lots of stuff was done at the SQL level and and .NET took care of the UI Side. LINQ spoiled me to do all the SQL stuff using LINQ to SQL.

Anyway, working on SQL console is a lot of fun. Especially for big data imports and exports, that's the way to go. Feels like you are more so of a DBA!

NULL values are always tricky to work with. I came across this problem where I had to write a case statement where if the value is NULL, update the 'remarks' field, else append the value to the field:

Here's what I was doing:
update [tablename]
set remarks=case remarks when null then 'source:ms.csv'
else (remarks + '; source:ms.csv') end
where email='xyz@abc.com'
Apparently, the field was'nt updated since the remarks field was blank for that record. Looks strange!

After doing a little research and trying a few conditions, here's how it worked:
update [tablename]
set remarks=case isnull(remarks,'true') when 'true' then 'source:ms.csv'
else (remarks + ' source:ms.csv') end
where email='xyz@abc.com'

Note that the second parameter should be implicitly convertible to the first parameter of isnull.

UPDATE: April 2012
For numeric/decimal/int columns, this would be it:
update [tablename]
set totalcount=case isnull(totalcount,0) when 0 then 1
else (totalcount + 1) end
where email='xyz@abc.com'

Check the microsft documentation here for details.

Thats how it goes! You can take care of bigger chunks of data, but its the little things like those NULL values that drive you crazy!

Tuesday, September 27, 2011

Using Linq to SQL to fetch the records that non alphabetic

I came across an issue where I had to fetch all the email addresses that do not begin with an alphabet. I learnt something new and interesting today.

On the sql server side, query the database table with something like this:
select * from [yourtable] where [columnname] like '[^a-zA-Z]%'
This interestingly fetches all the desired email address that either begin with a number, or have any other special character.

How to achieve this in Linq? Well that's a little tricky. This was my initial query:
DataClassesDataContext db = new DataClassesDataContext();
     var therecords =    from dh in db.customers
                         where dh.email.StartsWith("[^a-zA-Z]%")
                         select dh)

When this query was executed, it yielded zero results. This was how sql was interpreting the LINQ to Sql query:
exec sp_executesql N'SELECT *
FROM [dbo].[customers] AS [t0]
WHERE [t0].[email] LIKE @p0 ESCAPE ''~''',N'@p0 varchar(12)',@p0='~[~^a-zA-Z]%'

I had to get rid of the Escape characters. Modifying the Linq query as below gave me the desired result:
DataClassesDataContext db = new DataClassesDataContext();
     var therecords =    from dh in db.customers
                         where System.Data.Linq.SqlClient.SqlMethods.Like(dh.email, "[^a-zA-Z]%")                      
                         select dh)

Now I know there is one more way of Linq to get close to Sql!

Thursday, April 7, 2011

Generating Dynamic query for LinqDataSource (Where clause) in code behind

Consider this scenario.

I have a GridView which is binded to a LinqDataSource and it binds the data on the Page_Load Event. Now I have a Search Box, where you would enter the text to be searched in the GridView. The value entered into the search box is searched across x number of columns in the Database table to find a match.

Initially what I did was to generate a new query within the click event handler of the search Button. But when I tried to delete the record after search, it gave me an error:

The GridView 'grdView' fired event RowDeleting which wasn't handled

Apparently this GridView is Binded to the LinqDataSource and upon binding it to a different datasource from Code Behind, there is some data corruption from the results not being properly fetched.

Instead of going further and digging into a solution, I thought about having the where clauses applied for the LinqDataSource right in code behind. Upon doing some research and getting ideas from this blog, I implemented it into my code:


LinqDataSourceSearch.Where = "name.Contains(\"" + txtSearchSponsor.Text.Trim() + "\")";


Pretty easy enough. Right?

Friday, March 18, 2011

Add Google Analytics Tracking to iOS Apps

Hmmm... Look now someone is talking about iOS development. Well, I guess you have to go with the "Flow" of the software industry. Right?

So now on, I will be posting on iOS development that I find interesting/important and hopefully we can benefit from it.

I have always loved the way Google Analytics displays the powerful information about the content of your website. These guys have done a wonderful job. Now, they have GA for mobile Apps too. Cool!

I recently integrated GA into one of my Apps and would like to forward a few links for bookmarking:

http://code.google.com/mobile/analytics/docs/iphone/#gettingStarted

This guy on the link below does a pretty decent job of explaining how it works:

http://www.icodeblog.com/2010/04/22/how-to-integrate-google-analytics-tracking-into-your-apps-in-7-minutes/

Have a great discovery of data for your Apps!

Thursday, March 17, 2011

Commenting a .net server control

Pretty much basic stuff, but you really need it. The title of this post is self explanatory. Here's how you do it!


<%--

--%>


And There you are. Your old code safe and sound!

Monday, March 7, 2011

Fetching mutilple columns from Different tables using LinqDataSource and GridView

So after writing about Binding 2 columns of a Table to the DataTextField of Dropdown, I wanted to achieve even further with fetching column values from different tables

So I have a gridview showing Lectures information, which binds the "Title" of the Lecture and "show_id" (foreign key to "shows" table) . What I want to display is the Title of the show rather than just displaying the "show_id".

Lectures
Nameshows
SharePoint 1015
XCode 1012


to

Nameshows
SharePoint 101SPTechCon
XCode 101iPhonedevCon


On thing to be kept in mind for this is that you should have a property called "shows" to be able to reference to a foreign table. In short, your DataContext class should have inherited the foreign key reference applied between the shows and the lectures, with shows being the primary table and lectures containing the foreign key.







<%# Eval("Show.show_title") %>



OrderBy="name" TableName="showclasses">




Now when you run the code and there you are. It automatically kind of figures it the mapping between the tables with the "Show.show_title"

Wednesday, March 2, 2011

LinqDataSource with mutlple columns of a Table assigned to DataTextField

I have been working on LinqDataSource lateley. To me, it seem to save me some time and avoids code clutter in code behind. It can take care of all those basic master tables that you would want to populate to a dropdown list, gridview or listview.

One thing that I was looking into today was to bind multiple columns to the DataTextField of DropDownList. Here's how I got it:






Pretty much like writing query in code behind! I like it!

Tuesday, February 1, 2011

First dive into jQuery

Alright so learning new stuff never ends, and it is always exiting to learn cool animation stuff using AJAX. Well, this time around its jQuery.

What I wanted was an animation to pop up a "content box" at a specific scroll position. And jQuery did this thing very well.

Ok for beginners like me, this is where you can get all the documentation for learning jQuery:

http://jquery.com/

Easy enough.

I am using version 1.4.4 but you can download the latest version (v 1.5) from the website. There are various CDNs that you can point to if you dont like to have a local copy.

Google CDN

Microsoft CDN

Now here's the thing. I am using the $(window).scroll event which triggers when the scroll position is changed. You can have this event on a window or any element with style applied as "overflow:scroll" or "overflow:auto" where the height of the element is less than its content.

The code goes here:





It comes out to be that browsers with webkits(Chrome, safari) have a different behavior with scrollTop, so please keep in mind that you make it compatible with all browsers.


Way to go jQuery. Hope to do a lot of good stuff with you in the future