Visual Studio 2010

I am installing visual studio 2010 right now. I’m excited to try the new database features. I heard directly from a Microsoft source that you won’t need to use SQL management studio anymore. I hope it’s true. I’ve also read that VB.NET can now do EVERYTHING that C# can do. They are now a fully merged language. I’m also excited about no longer having to put an underscore (_) at the end of your code line to do line continuation, instead the system recognizes that your line is continuing automatically. That should be really cool. Now I have even more reasons to brag about how VB is better then C#. I’ve always hated semicolons but the C# people would win the argument by saying that line continuation was a hassle in VB, well take that C#, I don’t need a semicolon or a underscore, my language is smarter then yours!

SQL Date trick.

You ever have to write a query where you need to show a range of dates, but some of the dates are missing? Most message boards I have read want you to create some massive date table to keep track of every day and then you can use it in a join to get the missing days. Well I came up with my own solution to this, I’ve seen variations of it out there but none quite like mine. I created a function that you pass in a start date and an end date, and it returns all the dates in the middle. It also uses a SQL while loop much like you’d use a ‘FOR LOOP’ in code, which I think is pretty nifty. I also included a line to get rid of weekend dates, depending on the data you are using you may or may not want to do this. In the actual function it takes in the parameters for the dates, I wrote it this way so you can just copy and paste it into a SQL Query tool and see the magic.

declare @StartDate datetime;
declare @EndDate datetime;

DECLARE @TMP_DATES AS TABLE

(
	[Date] datetime
)

SET @StartDate = GETDATE()
SET @EndDate = @StartDate+14

 declare @i int
 SET @i = 0;
 while (@i<(DATEDIFF (d, @StartDate, @EndDate)))
 begin
 insert into @TMP_DATES values (CONVERT(CHAR(10),@StartDate+@i,101))
 SET @i=@i+1
 end

DELETE from @TMP_DATES where DATEPART(weekday,[Date]) = 1 OR DATEPART(weekday,[Date]) = 7

SELECT * from @TMP_DATES

Advanced SQL Concept: Temp Tables

Often in an advanced SQL stored procedure you need to create a temporary table in the database to dump data into. There are two methods available to do this. One is using the # symbol and giving it any name, like so:

CREATE TABLE #TempTable (
  TempID int,
  TempName varchar(30)
)

The problem with this method is that the temp table stays around in memory until you call drop table #TempTable

I suggest you use a variable to create the table like so:

DECLARE @TABLE AS TABLE
(
  TempID int,
  TempName varchar(30)
)

By doing it this way your table is only scoped within your stored procedure and so it will disappear once your SP stops executing and you don’t need to worry about dropping it.

Once you’ve created your table you use it like any other table you just need to remember to keep the @ or # at the beginning of the table name, depending on which method above you used.

Here is one way that you could use it:

DECLARE @TempTable AS TABLE
(
	idUser bigint,
	UserName varchar(50)
)

INSERT INTO @TempTable
SELECT idUser, UserName from tblUser

DECLARE @USR varchar(50)

SELECT @USR =UserName from @TempTable where idUser = 15

I usually use it when you have a super complicated Query and then you need to filter it down and adding your new filter to your SQL statement would drive you insane.

Here is some real code I created using one:

DECLARE @TABLE AS TABLE
(
idUser bigint,
Value int,
CurVal int
)

INSERT INTO @TABLE (Value, CurVal, idUser)
select DISTINCT Value, (SELECT UserValue from tblUserDetails where idUserDetailsField = 11 and idUser =(
(SELECT idUser from tblUser where id =
(SELECT DISTINCT Value from tblImportData where Field = 'System ID' and Row = d.Row and idImport = d.idImport))))
As CurVal,
(SELECT idUser from tblUser where id =
(SELECT DISTINCT Value from tblImportData where Field = 'System ID' and Row = d.Row and idImport = d.idImport))
As idUser
from tblImportData as d where Field = '% UserValue' and idImport = @idImport
Order by idUser

SELECT idUser from @TABLE where Value <> CurVal

I needed to add that compare, Value <> CurVal, to the sql query, but it boggled my mind so I just created a temp table, maybe a SQL Guru could have done it better.

Stupid LINQ Error

Ok in the end this was my fault, I’m not sure how I messed it up, but I just had to pull my hair out trying to figure out why I was getting this error using LINQ.

System.NotSupportedException: Method ‘System.Object CompareObjectEqual(System.Object, System.Object, Boolean)’ has no supported translation to SQL.

  Private Function TEMP_LINQ(ByVal SessionID, ByVal AdvisorID, ByVal StudentID) As Boolean

    Dim db As MyDB = New MyDB(Conn.ToString)

    Dim Query = (From t In db.tblUsers Where t.idAdvisor = AdvisorID And t.idStudent = StudentID And t.idSession = SessionID).FirstOrDefault

    If Query Is Nothing Then Return False

    Return True

  End Function

The problem was that for some reason I deleted all my types in my function setting this fixed the problem. This is not something  that I ever do, I just messed up a copy and paste process where I renamed the variables, in the process of which I accidentally forgot to give all my variables types, and this makes LINQ puke.

  Private Function TEMP_LINQ(ByVal SessionID As Integer, ByVal AdvisorID As Integer, ByVal StudentID As Integer) As Boolean

    Dim db As MyDB = New MyDB (Conn.ToString)

    Dim Query = (From t In db.tblUsers Where t.idAdvisor = AdvisorID And t.idStudent = StudentID And t.idSession = SessionID).FirstOrDefault

    If Query Is Nothing Then Return False

    Return True

  End Function

Being Good With SQL, Using SQL Cursors

If you want to make any money programming in VB.NET then you need to be in the business of writing good SQL server code. VB, business programming and Databases all come together in the same package.  And so from time to time I will be giving out some tips about advanced SQL Server concepts.  I don’t think of myself as a SQL Guru, but I do know a lot about it.  MS SQL is not the only database language you may need to learn, but the good news is that nearly all databases use a similar T-SQL Language and so if you get good at one of them, it’s generally pretty easy to use the others. Being good at designing table structure translates into EVERY database engine the same as well.


Today I wanted to talk for a moment about using SQL cursors, because they are very cool.  Have you ever wanted to do a loop inside a Stored procedure?  Well check out this code, you this will loop through all users that do not have a locker assigned (using a cool “NOT IN” SQL statement) and assign each of them the next available locker.

CREATE PROCEDURE [dbo].[spDEMO]

AS

BEGIN

SET NOCOUNT ON;

DECLARE @idUser as int

DECLARE @idLocker as int

DECLARE UserLocker_cursor CURSOR FOR

–Get all the users that don’t have lockers

SELECT idUser From tblUser where idUser NOT IN

(

SELECT idOwner from tblLocker where idOwner is not null

)

OPEN UserLocker_cursor

FETCH NEXT FROM UserLocker_cursor

INTO @idUser

WHILE @@FETCH_STATUS = 0

BEGIN

SET ROWCOUNT 1 –Return only one record

SELECT @idLocker=idLocker From tblLocker where idLocker is null

SET ROWCOUNT 0 –Return all records (important to reset this setting!)

–Now Assign a locker to each user

Update tblLocker set idOwner=@idUser Where idLocker = @idLocker

FETCH NEXT FROM UserLocker_cursor

INTO @idUser

END

CLOSE UserLocker_cursor

DEALLOCATE UserLocker_cursor

END

SQL CE Takes too much Memory

This is a bast form the past, I was thinking about how to promote my new blog so I signed into http://www.vbforums.com to answer a few questions, and I found this posting, something I figured out and posted about 5 years ago.  This was two posts, the first one describing my problem and the second one gives the solution.

—– (POST #1)

I have an MC 1000. At first I thought it was perfect, ultra low cost, and no extra features that were never used. At first they run perfectly, but after you insert a few hundred records into its CE Database it starts running slower and slower until it is almost unusable. I have the users reboot the device and it runs fine again for a while but does the same thing again. All my code is doing is adding records to the SQL CE DB.

Anyone have similar problems? I am thinking of switching over to a XML file instead of SQL CE.

I have converted as much storage memory into program memory and it has helped, but only for a while.

—– (POST #2)

I came across the following MS Article while researching this problem:

http://support.microsoft.com/?kbid=842191

It mentions:

Q17: Does setting my ADO Recordset to ‘nothing’ reclaim any memory?

A17: Generally, the answer is “yes.” However, Microsoft recommds that all your applications use set rs=nothing and set cn=nothing when you are finished using them. Database engines can take a fair amount of memory, and it is best not to leak memory.

Usually, Microsoft Visual Basic eventually releases the memory. However, there have been some cases where the memory is not released quickly enough and you can run out of memory on the device, especially if you have a particularly bad memory leak.

Made me think: am I setting all my objects to nothing? I went through my code and found that although I was setting all my Connections = Nothing I wasn’t setting the SqlCeCommand Objects to nothing when I was done with them. I added a few cmd = Nothing lines and now my App runs Faster then ever and doesn’t seem to be stopping for anything.

Whoo!