update table periodically

[jconway]

I am working on some code in vb.net that will run periodically to update a sql2005 database table.

table structure is very simple.

tblserver
Servername (Keyfield)
item1
item2
etc.

anyway. I need to run an update to the database and insert records if the server does not exists in the table
I can do a loop

for each input server 
if input server = tbleserver.servername
tblserver.servername.next
you get the point.

that seems pretty inefficent, given that I would have to check each of the 400 + servers against the current database

Any recommendations?

[jmcilhinney]

You need to use an If statement somewhere because you need to know If the server already exists. You can do it in your VB code or your SQL code. If you want to do it in VB then you can populate a DataTable with all the records, then for each server you want to ensure is in the table you do this:

If myDataTable.Select(String.Format("Servername = '{0}'", serverName)).Length = 0 Then
    'There are no matching rows so add one.
End If

and then at the end save the whole DataTable. If you did it in SQL the principle would be exactly the same.

[jconway]

Thanks for your assistance.

[szlamany]

Where are you getting the names of the 400 servers?

Usually I would suggest loading all the "potential new servers" into a staging table or temp table. Fastest way to do this is with some kind of BULK INSERT (from a file or using methods that do this from memory).

Then you simply say:

Insert into tblServer
    Select WT.* From WorkTable WT
    Left Join tblServer TS on TS.ServerName=WT.ServerName
    Where TS.ServerName is null

The WHERE clause determines the fact of the servername not being in tblServer.

[jconway]

I am getting the list from mfcom we have a very large citrix farm and I am working on some managment reports. Enumeration at runtime while an options has a large cost, and server names do not change that often.

So I load to a sql table and this process runs 2x a month automatically.

[szlamany]

Then - as I said - if you can get the servernames into a staging table - bulk insert will load them relatively instantly (hundreds of thousands of rows in seconds usually)...

Then that INSERT/SELECT I showed will run in less then seconds.

[jconway]

Then - as I said - if you can get the servernames into a staging table - bulk insert will load them relatively instantly (hundreds of thousands of rows in seconds usually)...

Then that INSERT/SELECT I showed will run in less then seconds.

I think I understand the concepts now. thanks for helping with that.

Now, being new to the .net environment, I am using the Dataset wizard to create the base code for me, which is pretty cool.

How can I modify the insert code in the query builder to deal with checking to see if the data exists?
here is what I have so far.

Public Sub Loadservers()
        Dim Worktable As New DataTable("wt")
        Worktable.Columns.Add("servername", GetType(System.String))
        Worktable.Columns.Add("Load_eval", GetType(System.String))
        Worktable.Columns.Add("Zone", GetType(System.String))
        Worktable.Columns.Add("dateadded", GetType(Date))
        Dim servertableadapter As New serverupdatedsTableAdapters.serverTableAdapter
        Dim serverlist As serverupdateds.serverDataTable
        serverlist = servertableadapter.GetData
        thefarm = New MetaFrameFarm
        thefarm.Initialize(MetaFrameObjectType.MetaFrameWinFarmObject)
        Dim aserver As New MetaFrameServer
        Dim theloadobject As New MetaFrameLoadEvaluator
        Dim worktablerow As DataRow
        For Each aserver In thefarm.Servers
            worktablerow = Worktable.NewRow
            Dim theservername As String = aserver.ServerName
            Dim theloadeval As String
            theloadobject = aserver.AttachedLE
            theloadobject.LoadData(1)
            theloadeval = theloadobject.LEName.ToString
            Dim thezone As String = aserver.ZoneName
            Dim thetimeadded As Date = Date.Now()
            worktablerow(0) = theservername
            worktablerow(1) = theloadeval
            worktablerow(2) = thezone
            worktablerow(3) = thetimeadded
            Worktable.Rows.Add(worktablerow)
'I know I need to do something here.  but what?>>>>
            'servertableadapter.Insert(theservername, theloadeval, thezone, thetimeadded)

        Next

    End Sub

[jconway]

ok, I have more information. maybe I just don't get the datatable stuff yet.

here is my updated code

Public Sub Loadservers()
        Dim Worktable As New DataTable("wt")
        Worktable.Columns.Add("servername", GetType(System.String))
        Worktable.Columns.Add("Load_eval", GetType(System.String))
        Worktable.Columns.Add("Zone", GetType(System.String))
        Worktable.Columns.Add("dateadded", GetType(Date))
        Dim servertableadapter As New serverupdatedsTableAdapters.serverTableAdapter
        Dim serverlist As serverupdateds.serverDataTable
        serverlist = servertableadapter.GetserverData
        thefarm = New MetaFrameFarm
        thefarm.Initialize(MetaFrameObjectType.MetaFrameWinFarmObject)
        Dim aserver As New MetaFrameServer
        Dim theloadobject As New MetaFrameLoadEvaluator
        Dim worktablerow As DataRow
        For Each aserver In thefarm.Servers
            worktablerow = Worktable.NewRow
            Dim theservername As String = aserver.ServerName
            Dim theloadeval As String
            theloadobject = aserver.AttachedLE
            theloadobject.LoadData(1)
            theloadeval = theloadobject.LEName.ToString
            Dim thezone As String = aserver.ZoneName
            Dim thetimeadded As Date = Date.Now()
            worktablerow(0) = theservername
            worktablerow(1) = theloadeval
            worktablerow(2) = thezone
            worktablerow(3) = thetimeadded
            Worktable.Rows.Add(worktablerow)
        Next
        servertableadapter.Insertserverswithoutdupblicates()

    End Sub

Insertserverswithoutdupblicates sql query is this

INSERT INTO server
                      (ServerName, Load_eval, Zone, dateadded)
SELECT     Worktable.ServerName, Worktable.Load_eval, Worktable.Zone, Worktable.dateadded
FROM         server AS server_1 LEFT OUTER JOIN
                      Worktable ON server_1.ServerName = Worktable.ServerName
WHERE     (server_1.Servername IS NULL)

I get the following exception.

System.Data.SqlClient.SqlException was unhandled
Class=16
ErrorCode=-2146232060
LineNumber=1
Message="Invalid object name 'Worktable'."

[jconway]

Solved my own problem, I was making it way to hard.

I was able to accomplish this with the datatable.clone and merge methods.

 Public Sub Loadservers()
        Dim servertableadapter As New serverupdatedsTableAdapters.serverTableAdapter
        Dim serverlist As serverupdateds.serverDataTable
        serverlist = servertableadapter.GetserverData
        thefarm = New MetaFrameFarm
        thefarm.Initialize(MetaFrameObjectType.MetaFrameWinFarmObject)
        Dim aserver As New MetaFrameServer
        Dim theloadobject As New MetaFrameLoadEvaluator
        Dim Worktable As New DataTable()
        Worktable = serverlist.Clone
        Dim worktablerow As DataRow
        For Each aserver In thefarm.Servers
            worktablerow = Worktable.NewRow
            Dim theservername As String = aserver.ServerName
            Dim theloadeval As String
            theloadobject = aserver.AttachedLE
            theloadobject.LoadData(1)
            theloadeval = theloadobject.LEName.ToString
            Dim thezone As String = aserver.ZoneName
            Dim thetimeadded As Date = Date.Now()
            worktablerow(0) = theservername
            worktablerow(1) = theloadeval
            worktablerow(2) = thezone
            worktablerow(3) = thetimeadded
            Worktable.Rows.Add(worktablerow)
        Next
        serverlist.Merge(Worktable, False, MissingSchemaAction.Error)
        servertableadapter.Update(serverlist)

End Sub