troubleshooting Question

Muliple User Database Application

Avatar of spen_lang
spen_lang asked on
Microsoft SQL Server.NET Programming
12 Comments1 Solution334 ViewsLast Modified:
Hi,

I am creating a database application where more that one user may be access it at the same time. For the backend I am using MSSQL 2008.

I have connected to the database and performed updates using dataAdatpers and datasets as a single user but please could you give me some advice on how I will need to change my application so that it can handle muliple users?

E.g. ensure that two users do not attempt to update the same record at the the same time, perform some sort of locks when a user has a record open for edit.

How often and how should I refresh my datasets so that the users always have the latest data?

I am currently using the following code to access the database and then binding the data to controls on my form.

Imports System.Data.SqlClient
Public Class dataUserAccess
    Private cnKPI As SqlConnection
    Private daUsers As SqlDataAdapter
    Private cmdSelectUsersInfo As SqlCommand
    Private cmdUpdateUsersInfo As SqlCommand
    Private cmdInsertUsersInfo As SqlCommand
    Private cmdDeleteUsersInfo As SqlCommand
    Private dsUsers As DataSet

    Public Sub New()

        'Connection
        cnKPI = New SqlConnection("")

        'select command
        cmdSelectUsersInfo = New SqlCommand
        cmdSelectUsersInfo.Connection = cnKPI
        cmdSelectUsersInfo.CommandType = CommandType.StoredProcedure
        cmdSelectUsersInfo.CommandText = "proc_UserAccess_All"

        'update command
        cmdUpdateUsersInfo = New SqlCommand
        cmdUpdateUsersInfo.Connection = cnKPI
        cmdUpdateUsersInfo.CommandType = CommandType.StoredProcedure
        cmdUpdateUsersInfo.CommandText = "proc_UserAccess_Update"

        cmdUpdateUsersInfo.Parameters.Add("@UserID", SqlDbType.VarChar, 50, "UserID")
        cmdUpdateUsersInfo.Parameters.Add("@Username", SqlDbType.VarChar, 50, "Username")
        cmdUpdateUsersInfo.Parameters.Add("@InvoiceAccuracy", SqlDbType.Bit, 1, "InvoiceAccuracy")
        cmdUpdateUsersInfo.Parameters.Add("@EmployeeAccidentRate", SqlDbType.Bit, 1, "EmployeeAccidentRate")
        cmdUpdateUsersInfo.Parameters.Add("@Settings", SqlDbType.Bit, 1, "Settings")
        cmdUpdateUsersInfo.Parameters.Add("@Users", SqlDbType.Bit, 1, "Users")
        cmdUpdateUsersInfo.Parameters.Add("@Active", SqlDbType.Bit, 1, "Active")
        cmdUpdateUsersInfo.Parameters.Add("@UniqueKey", SqlDbType.Int, 1, "UniqueKey")

        'insert command
        cmdInsertUsersInfo = New SqlCommand
        cmdInsertUsersInfo.Connection = cnKPI
        cmdInsertUsersInfo.CommandType = CommandType.StoredProcedure
        cmdInsertUsersInfo.CommandText = "proc_UserAccess_Insert"

        cmdInsertUsersInfo.Parameters.Add("@UserID", SqlDbType.VarChar, 50, "UserID")
        cmdInsertUsersInfo.Parameters.Add("@Username", SqlDbType.VarChar, 50, "Username")
        cmdInsertUsersInfo.Parameters.Add("@InvoiceAccuracy", SqlDbType.Bit, 1, "InvoiceAccuracy")
        cmdInsertUsersInfo.Parameters.Add("@EmployeeAccidentRate", SqlDbType.Bit, 1, "EmployeeAccidentRate")
        cmdInsertUsersInfo.Parameters.Add("@Settings", SqlDbType.Bit, 1, "Settings")
        cmdInsertUsersInfo.Parameters.Add("@Users", SqlDbType.Bit, 1, "Users")
        cmdInsertUsersInfo.Parameters.Add("@Active", SqlDbType.Bit, 1, "Active")
        cmdInsertUsersInfo.Parameters.Add("@CreateDate", SqlDbType.DateTime, 1, "CreateDate")
        cmdInsertUsersInfo.Parameters.Add("@CreatedBy", SqlDbType.VarChar, 50, "CreatedBy")

        'Delete command
        cmdDeleteUsersInfo = New SqlCommand
        cmdDeleteUsersInfo.Connection = cnKPI
        cmdDeleteUsersInfo.CommandType = CommandType.StoredProcedure
        cmdDeleteUsersInfo.CommandText = "proc_UserAccess_Delete"
        cmdDeleteUsersInfo.Parameters.Add("@UniqueKey", SqlDbType.Int, 1, "UniqueKey")


        'DataApapter
        daUsers = New SqlDataAdapter
        daUsers.SelectCommand = cmdSelectUsersInfo
        daUsers.UpdateCommand = cmdUpdateUsersInfo
        daUsers.InsertCommand = cmdInsertUsersInfo
        daUsers.DeleteCommand = cmdDeleteUsersInfo

        'Dataset
        dsUsers = New DataSet()
    End Sub

    Public Function GetUsersInfo() As DataSet
        daUsers.Fill(dsUsers)
        Return (dsUsers)
    End Function

    Public Sub UpdateUsersInfo(ByVal dsChanges As DataSet)
        daUsers.Update(dsChanges)
    End Sub

End Class

Thanks, Greg
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 12 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros