"I've been maintaining a 'certain' application for several months now," Trent writes, "it exists in a wonderful state of being partially properly written code, but mostly legacy garbage. I've done my best to avoid anything in the database realm, but a change request forced me to journey down that dark path."
"When scrolling through the countless number of tables, I noticed something called
tblIsThere. This is what it looked like:"
"Curious, I did a quick search in the code base to find this:"
Public Shared Function TableExists(ByVal TableName As String) As Boolean Dim SQL As String = _ "IF NOT EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES" + " WHERE TABLE_NAME = 'tblIsThere')" + " CREATE TABLE [dbo].[tblIsThere] ([isthere] [bit] NOT NULL)" Dim theCMD As New SqlClient.SqlCommand(SQL, clsConnect.connect) Dim itDA As New SqlClient.SqlDataAdapter(_ "SELECT * FROM tblIsThere", clsConnect.connect) Dim itDT As New DataTable Dim itDV As New DataView(itDT) If theCMD.Connection.State <> ConnectionState.Open Then theCMD.Connection.Open() End If Try theCMD.ExecuteNonQuery() Catch ex As Exception MessageBox.Show(ex.ToString) End Try itDA.Fill(itDT) If itDV.Count = 0 Then SQL = "INSERT INTO tblIsThere (isthere) VALUES (0)" Else SQL = "UPDATE tblIsThere SET isthere = 0" End If theCMD.CommandText = SQL Try theCMD.ExecuteNonQuery() Catch ex As Exception MessageBox.Show(ex.ToString) End Try SQL = "IF EXISTS(SELECT TABLE_NAME " + "FROM INFORMATION_SCHEMA.TABLES " + "WHERE TABLE_NAME = '" & TableName & "') " + "UPDATE tblIsThere SET isthere = 1" theCMD.CommandText = SQL Try theCMD.ExecuteNonQuery() Catch ex As Exception MessageBox.Show(ex.ToString) End Try itDT.Clear() itDA.Fill(itDT) Return itDV.Item(0)("isthere") End Function
"Luckily, the method is only used in
clsUpgrade, which has since been replaced with a much nicer system that doesn't involve 20,000+ lines of SQL and dodgy logic hard coded into a single file."