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

Programmer Interviews

It is interesting to me how people evaluate programmers. I have applied for many programming jobs over my life time and I usually run into one of three scenarios:

1) I’ll start with my favorite, they have me write code.

This is my absolute favorite type of interview, every time I get this one I always get the job offer. They give me a problem and ask me to write code to solve it. Not on a white board, or in pseudo code, but on a PC with Visual Studio installed. The last one I did like this they wanted me to connect through a web service to a SQL database, download some information, save it locally on the PC, modify it, and send it back via the web service to be recored with the new value.

So not only did I write code to do it, I also wrote a design document, added complex error handling, and I did it on a PC without internet access (no help form the web). They hired me and later told me that none of the other applicants even finished the code.

2) The Programmer Talk Interview.

I like this one too, I sit down with one of the other developers on the team and we talk about design best practices, software development methodology, and applications that I have worked on before. This type of interview I usually do good with as well, but other times it has gone poorly when I’m not on the same page.

3) The Computer Class Questions. (Ugh!)

This is the type I hate. They ask you to write a linked list optimizer or an anagram solver while your on the phone or on a white board. This may be great for those of you out there that got a 4.0 in all your computer classes (which actually I did get a 4.0 in programming II), but I don’t see how it says anything about your actual ability to solve problems. When I write code, I solve problems. I have written code that connects to a semi-truck over a serial port found under the dash with no documentation on the communication protocol which 4 other programmers had tried and failed. I did it, and I even had to re-write a C++ driver in .NET and my .NET driver ran faster. I bet if the company I was working at at the time had given me a question about linked lists they would have never figured out how to get it to work.

When I interview programmers, I use method 1 above.

VB vs C#

So I thought that I would throw my opinion out into the C# vs VB.NET debate. Let me say that professionally I have had to program in both languages on a scale of 1 to 10 my skills in VB are a solid 10, but in C# I’d only consider myself a 6 or 7. The thing I usually say when people ask me if I like C# is “No, There are too many semicolons.” It’s funny to me and usually to whomever I’m talking to. But really I have yet to run into a scenario where there is something you can do in C# but you can’t in VB. There are a number of translators on the internet that will translate back and forth from C# to VB and vise versa. So whenever I have to write in C# if I get stuck I write the code out in VB and then translate it to C#.

Since it is easy to call a C# DLL from VB and vise versa, personally I think it does not matter what language you write in. If you have a good multi-tiered architecture you should be able to let one programmer write his tier in VB and another tier in C#, but I have yet to be at a company who thinks this is a good idea. I’m not sure why I guess it’s adding a layer of complexity that project managers do not understand or want to deal with.

So at the end of the day my opionion is that both languages are equal, and you should just write in whatever language you like. I have been writing in BASIC code my entire life, and switching over to C# just seems pointless to me, unless there is a paycheck in it.

Multi-Tiered Architecture

Muti-Tiered Architecture is a very important software concept. Whenever I am givien someone elses code to fix my brain explodes when I see All three of the major teirs in the same function. The three tiers are: Presentation, Application, and Database. In my opinion these three areas should never mix. Your UI should never call the database, and your Application Layer should never interact with the UI. Sometimes you will find yourself in a situation where you have to do some business logic in the UI or inside that database tier, but you should never, ever have to mix the Presentation with the database.

Personally I prefer what I call a 4 tier model. I break down the Development into 4 distinct Areas:

UI – (User Interface or Presentation)
BL – (Business Logic or Application, or I even prefer calling it the Logic Layer)
DL – (Data Layer)
DB – (Database)

I break out the database Layer into two parts, the Data Layer, and the Data Base, this is not the actual database that would be a different layer if anything. The purpose of the data Layer is to convert all database objects into your custom business objects. The database layer only returns datasets, data tables, and other database related objects, but not SQL objects. The Database Layer uses SQL objects or whatever type of database your are connecting to, but only passes back the common database objects like datatables and the sort. This allows TRUE database portability. If you want to change from SQL to mySQL or any other database technology you only have to change one small layer of your code. Your UI has NO idea that underneath it all there is a SQL database or a mySQL database, or an XML flat file, it does not know, nor does it care.

I allways create a common library that has the base objects that I use to transfer data. I use the namespace ‘common’. So I use somthing like ‘vbguru.Common.User’ as my common user object. This way all layers can have access to a common object and pass data back and forth using it. I also separate my tiers into at least different files, I don’t mix tiers in one code file, I even prefer to put them into their own projects for what I would call ‘ultimate portability.’ By breaking down your logic into small chunks it makes it easier to reuse. I love the idea of perfecting some logic and creating a DLL and never looking at it’s code again, just add it to a new project as the dll.

Here is an example of some good muti-tiered code. I am putting it all in one file so that it is easy to read here. Normally, I would put each piece in it’s own file.

Namespace vbGuru

  Public Class UI

 

    Public Sub UI_Function()

 

      Dim Obj As vbGuru.Common.MyData()

      Dim AL As vbGuru.Logic = New vbGuru.Logic

 

      Obj = AL.GetObj

 

      ‘Add UI Stuff

 

    End Sub

 

  End Class

End Namespace

 

Namespace vbGuru.Common

  Public Structure MyData

    Public id As Integer

    Public Name As String

    Public Value As String

  End Structure

End Namespace

 

Namespace vbGuru

  Public Class Logic

    Public Function GetObj() As vbGuru.Common.MyData()

      Dim Data As vbGuru.Data = New vbGuru.Data

 

      Dim Obj As vbGuru.Common.MyData() = Data.GetObj()

 

      ‘Add any logic that you need to manipulate the object

 

      Return Obj

 

    End Function

  End Class

End Namespace

 

Namespace vbGuru

  Public Class Data

    Public Function GetObj() As vbGuru.Common.MyData()

 

      Dim DB As vbGuru.DB = New vbGuru.DB

 

      Dim dt As DataTable = DB.GetObj()

 

      If dt Is Nothing Then Return Nothing

      ‘I don’t think this second line is needed

      ‘But I allways put it in just in case.

      If dt.Rows.Count = 0 Then Return Nothing

 

      Dim Que As Queue(Of vbGuru.Common.MyData) = New Queue(Of vbGuru.Common.MyData)

      ‘I like to use Queues to avoid for next loops and i’s

 

      For Each drow In dt.Rows

        Dim obj As vbGuru.Common.MyData

        obj.id = drow("id")

        obj.Name = drow("Name")

        obj.Value = drow("Value")

        Que.Enqueue(obj)

      Next

 

      Return Que.ToArray

 

    End Function

  End Class

 

End Namespace

 

Namespace vbGuru

  Public Class DB

    Public Function GetObj() As DataTable

 

      Dim cn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection("CNSTRING")

      Dim cmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand("SELECT * FROM TMP", cn)

      Dim ds As System.Data.SqlClient.SqlDataAdapter = New System.Data.SqlClient.SqlDataAdapter(cmd)

 

      Dim dt As DataTable = New DataTable

 

      ds.Fill(dt)

 

      Return dt

 

    End Function

  End Class

End Namespace

It may seem like a lot of steps just to do a simple function but by separating it all out it makes your code easier to maintain, easier to make a database change, and easily reusable.

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.

Shutting up the warnings

Ok ever have some code like this:

  Function GetMine(ByVal id As Integer) As ObjMine

    Dim Obj As ObjMine

    If id = 0 Then

      Obj = New ObjMine(id)

    End If

    Return Obj

  End Function

Visual studio will warn you:

Warning 2 Function ‘GetMine’ doesn’t return a value on all code paths. A null reference exception could occur at run time when the result is used.’

But your thinking “But I want it to return nothing if there is no object with that ID, so stop bugging me!”

Well here is my little trick to fix that:

  Function GetMine(ByVal id As Integer) As ObjMine

    Dim Obj As ObjMine = Nothing

    If id = 0 Then

      Obj = New ObjMine(id)

    End If

    Return Obj

  End Function

If Not obj is nothing

I happen to love “If Not” it even got me in trouble at work. I don’t think it was available in VB6 but I might have just not known about it. Back in my VB6 Days I would often write an if statement like so:

    If obj Is Nothing Then

      ‘Do Nothing

    Else

      Obj = New Object

    End If

This solves the issue of there not being an ‘isnot’ operator, but it looks bad. Well In .NET you can use “If Not” to solve this:

    If Not obj Is Nothing Then

      obj = New Object

    End If

I got into trouble at work when I used it this way:

    If Not i = 1 Then

    End If

My code reviewer made me rewrite it to:

    If i <> 1 Then

    End If

I think it was a fine line of code but I may have just been addicted to If Not, but I admit it is a little confusing.

Sending An Email using SSL & Comcast

Here is something I just figured out, it took me a while because I was trying to use the some older settings that I had found before. Port 587 is the one you want to use with Comcast & .net.

This code works with my comcast account.

  Private Sub SendEmail()

    Dim smtp As System.Net.Mail.SmtpClient
    smtp = New System.Net.Mail.SmtpClient("smtp.comcast.net", 587)
    smtp.UseDefaultCredentials = False
    'NOTE: USER_NAME does not include the @comcast.net part
    smtp.Credentials = New System.Net.NetworkCredential("USER_NAME", "PASSWORD")
    smtp.EnableSsl = True
    smtp.DeliveryMethod = Net.Mail.SmtpDeliveryMethod.Network

    smtp.Send("USER_NAME@COMCAST.NET", "USER@USER.COM", "TEST SUBJECT", "TEST MESSAGE")

  End Sub

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