WANT TO CONFIGURE SQL SERVER CONNECTION IN MY SOFTWARE

Question

Dear All

I am working on an erp. in that erp i am making a form with
servername
databasename
username
password

now what i need is whenever i run erp .. it first check connection details from that form (frmsqlconnection) and if connected then should work in every form . means every form must take connection settings from that form .
for now i wrote a code and using that code in every form

  Public Sub connectdatabase()
        Dim databasename As String = mysqldbo.txtdbo.Text
        Dim server As String = mysqldbo.txtserver.Text
        Dim username As String = mysqldbo.txtuser.Text
        Dim password As String = mysqldbo.txtpass.Text

        If Not connx Is Nothing Then connx.Close()
        connx.ConnectionString = "server=" & server & ";user id=" & username & ";" &
            "password=" & password & ";database=" & databasename & ""
        Try
            connx.Open()
            MsgBox("Connection Activated")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        connx.Close()
    End Sub

is it possible that i make a class or module using above code and use only one line code where i need to open or close connections.

please guide with your best experience.

Thank you

Answer 1

Consider something like this:

Public Function connectdatabase() as SqlConnection
    Dim databasename As String = mysqldbo.txtdbo.Text
    Dim server As String = mysqldbo.txtserver.Text
    Dim username As String = mysqldbo.txtuser.Text
    Dim password As String = mysqldbo.txtpass.Text

    Dim connection_string = "server=" & server & ";user id=" & username & ";" &
             "password=" & password & ";database=" & databasename
    Dim conn = new SqlConnection(connection_string)

    conn.Open( )

    Return conn
End Function

The usage:

Using conn = connectdatabase( )
   . . . SQL operations . . .
End Using

The connection will be closed automatically. Add the corresponding Try-Catch to treat the errors.

Answer 2

Hi @Omer ahmed ,
You can use the following classes to encapsulate the database connection.

Public Class connectdatabase  
Public connx As SqlConnection  

Public Sub New(databasename As String, server As String, username As String, password As String)  
    connx.ConnectionString = "server=" & server & ";user id=" & username & ";" & "password=" & password & ";database=" & databasename & ""  
End Sub  

Public Function Open() As SqlConnection  
    connx.Open()  
    Return connx  
End Function  
Public Sub Close()  
    connx.Close()  
End Sub  
End Class  

Create a connectdatabase object in form (frmsqlconnection).

    Public conn As New connectdatabase(mysqldbo.txtdbo.Text, mysqldbo.txtserver.Text, mysqldbo.txtuser.Text, mysqldbo.txtpass.Text)  

Then call it in your project.

    Using frmsqlconnection.conn.Open()  

    End Using  

Hope the code above could be helpful.
Best Regards.
Jiachen Li

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread

Answer 3

Hi,
try this demo:

Imports System.Data.SqlClient

Public Class Form28

  Private dgv As New DataGridView With {.Dock = DockStyle.Fill}
  Private Sub Form28_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Me.Controls.Add(dgv)
    ' How to use Module '
    Using da As New SqlDataAdapter("SELECT * FROM Tab1", DataBase.Connection())
      Dim dt As New DataTable
      da.Fill(dt)
      dgv.DataSource = dt.DefaultView
    End Using
  End Sub
End Class

''' <summary>
''' Module to open SQL Server Connection
''' </summary> '
Public Module DataBase

  Private Property mysqldbo As New SqlParams
  Private cn As SqlConnection

  Public Function Connection(Optional newconnection As Boolean? = Nothing) As SqlConnection
    If cn Is Nothing OrElse (newconnection.HasValue And newconnection.Value) Then
      cn?.Close
      cn?.Dispose
      mysqldbo.ShowDialog()
      Dim connection_string = $"server={mysqldbo.server};user id={mysqldbo.username};password={mysqldbo.password};database={mysqldbo.databasename}"
      cn = New SqlConnection(connection_string)
      cn.Open()
    End If
    Return cn
  End Function

  Public Class SqlParams
    Inherits Form
    Public Property databasename As String
    Public Property server As String
    Public Property username As String
    Public Property password As String

    Private lbldbo As New Label With {.Text = "Server Name", .Top = 10, .Left = 10, .Width = 100}
    Public txtdbo As New TextBox With {.Top = 10, .Left = 120, .Width = 200}
    Private lblserver As New Label With {.Text = "Database Name", .Top = 40, .Left = 10}
    Public txtserver As New TextBox With {.Top = 40, .Left = 120, .Width = 200}
    Private lbluser As New Label With {.Text = "UserName", .Top = 70, .Left = 10}
    Public txtuser As New TextBox With {.Top = 70, .Left = 120, .Width = 200}
    Private lblpass As New Label With {.Text = "Password", .Top = 100, .Left = 10}
    Public txtpass As New TextBox With {.Top = 100, .Left = 120, .Width = 200}
    Private WithEvents btn As New Button With {.Text = "Save", .Top = 130, .Left = 200, .Width = 100}

    Private Sub SqlParams_Load(sender As Object, e As EventArgs) Handles MyBase.Load
      Me.Width = 350
      Me.Controls.AddRange(New Control() {lbldbo, txtdbo, lblserver, txtserver, lbluser, txtuser, lblpass, txtpass, btn})
      txtdbo.DataBindings.Add("Text", Me, "databasename")
      txtserver.DataBindings.Add("Text", Me, "server")
      txtuser.DataBindings.Add("Text", Me, "username")
      txtpass.DataBindings.Add("Text", Me, "password")
    End Sub

    Private Sub btn_Click(sender As Object, e As EventArgs) Handles btn.Click
      Me.Close()
    End Sub

  End Class

End Module