Read from SQL server

[lcfjoertoft]

I've managed to put together some code to retrieve data from a sql file. I plan to connect to a sql server that are operated by a database program, that update the registry every 10 sec.

Before I try to connect my program to the sql database, I need to know if my program will prohibit other progrograms to read and write to the database.

Anyone see any problems?

My program:

Imports System.Data.SqlClient

Public Class Form1

    Dim SQLRead As SqlDataReader
    Dim connStr As String = "Server=.\SQLExpress;AttachDbFileName=C:\tmp\DB\MyDB.mdf;integrated security=true;database=MyDB"
    Dim sqlcon As New SqlConnection(connStr)
    Dim QueryString As String


    Private Sub Parts()

        QueryString = "SELECT DATE, PCRID, STAT, PROVNR, CODE FROM dbo.PARTS"
        Dim PartsCmd As New SqlCommand(QueryString, sqlcon)
        sqlcon.Open()

        SQLRead = PartsCmd.ExecuteReader()

        While SQLRead.Read()
            'Some Code

        End While

        SQLRead.Close()
        sqlcon.Close()

    End Sub

    Private Sub Prov()
        QueryString = "SELECT LOT, ART, ANATYPE, RUN, PROVNR dbo.PROV"
        Dim ProvCmd As New SqlCommand(QueryString, sqlcon)
        sqlcon.Open()

        SQLRead = ProvCmd.ExecuteReader()

        While SQLRead.Read()
            'Some Code
        End While

        SQLRead.Close()
        sqlcon.Close()

    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


    End Sub


End Class

[lolafuertes]

In order to quicly free resources, I would prefer to fill some table in a memory dataset, then close and dispose the connection to the server (freeing resources). after that you can cicle over the data in memory.

Between fills, give some time to the system calling the System.Threading.Thread.Sleep for 50 miliseconds.

Hope this helps.

[lcfjoertoft]

My plan is to import the data in arrays, and sort out the posts for various tasks and displayed. Then loop it ever so often to update the information.

So I was hoping that the code that I have written would not interfer with other programs reading and writing to the database. Sort of opening the db file for reading without confiscating the use for other programs.

[lolafuertes]

When you do any action agtainst a database, the DBMS uses to 'mark' the database being used by you. Also 'marks' the tables, and also each record you access, to be sure that, if any other user want to access to the same info, can access (or not) depending on the actions pending.
This process is called Locking. Locking is done writing in a special table the info of what is being locked.
By default, locking is done at 'page' level. Depending on the size of the each record in the table, many can be locked. If a page is locked for shared read, it can't be modified until the lock is released. If is locked for update (insert, update or delete) can't be read until the write operation is finished.
The duration of the locking depends on how much time your connection / sqlcommand needs to finish the work and to release the objects locked. Then the DBMS needs to free the locks in the DB. This also consumes resources.

Hope this helps

[lcfjoertoft]

Sweet! Since I'm only going to read, and be in and out in a flash, this will not be a problem. Thankyou lolafuertes for the help.