Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
Here in following code i have two different forms and one class that class i am using for establish and release connection with database.
My questions are as follows:
1) how can i minimized cn variable declaration.
2) how to minimized cmd and other variable declaration(not very much needed but if possible)
'first class coding>
Imports System.Data.SqlClient
Public Class FrontDesk_approval_form
    Dim cn As Class1 = Class1.getInstance
 
    Dim cmd As SqlCommand
    Dim dr As SqlDataReader
    Dim da As SqlDataAdapter
    Dim ds, ds1 As New DataSet
    Dim str As String
    Dim temp_rowNo As String
    Dim temp_No As Integer
    Dim frm As New FrontDesk_approval_sub_form
    Public which_form As String = ""
   
    Private Sub bt_select_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bt_select.Click
 
        str = "select * from Asset_Service_Master where CaseId=@ temp_No "
        cmd = New SqlCommand(str, cn.getConnection())
        cmd.Parameters.Add(New SqlParameter("@temp_No",temp_No)
 
        dr = cmd.ExecuteReader()
 
        If dr.Read Then
            frm.tb_caseid.Text = dr.Item("CaseId")
            frm.temp_asset = dr.Item("Assets")
            frm.temp_brandname = dr.Item("BrandName")
            frm.temp_asset_type = dr.Item("AssetTypes")
            frm.temp_modelno = dr.Item("ModleNo")
            frm.temp_SerialNo = dr.Item("SerialNo")
            frm.temp_service_charge = dr.Item("ServiceCharge")
            frm.temp_service_Tex = dr.Item("ServiceTex")
            frm.which_form = which_form
            Me.Hide()
            frm.ShowDialog()
 
            Me.Dispose()
            Me.Close()
        End If
        cn.closingConnection()
 End Sub
end class
 
second class coding:
Imports System.Data.SqlClient
Public Class Front_Main
 
    Public maintain_user_name As String = ""
    Public maintain_password As String = ""
    Public maintain_department As String = ""
 
    Dim cmd As SqlCommand
    Dim dr As SqlDataReader
    Dim da As SqlDataAdapter
    Dim ds, ds1 As New DataSet
    Dim str As String
    Dim cn As Class1 = Class1.getInstance
    
 
    Private Sub bt_NewCaseId_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bt_NewCaseId.Click
        Dim f1 As New Front_New_Case
        f1.ShowDialog()
    End Sub
 
    Private Sub MainFormFrontDesk_Activated(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Activated
 

        str = "select Count(*)  from Asset_Service_Master where CaseStatus=  @temp "
        cmd = New SqlCommand(str, cn.getConnection())
        cmd.Parameters.Add(New SqlParameter("@temp", "pending for client approval"))
 
        tb_PendingForClientApproval.Text = cmd.ExecuteScalar().ToString
 
        str = "select count(*)  from Asset_Service_Master where CallClosed= @tp  AND BillingComplete=@tmp   "
        cmd = New SqlCommand(str, cn.getConnection())
 

        cmd.Parameters.Add(New SqlParameter("@tp", "yes"))
        cmd.Parameters.Add(New SqlParameter("@tmp", ""))
 
        tb_CalledClosed.Text = cmd.ExecuteScalar().ToString
 
        cn.closingConnection()
 
    End Sub
 
Class coding is:
Imports System.Data.SqlClient
 
Public Class Class1
    Private objcon As SqlConnection
    Private Shared inst As Class1
 
    Public Shared Function getInstance() As Class1
        'the following code identify that connection object having null or reference

        If inst Is Nothing Then
            inst = New Class1
 
        End If
        Return inst
 
    End Function
 
    Public Function getConnection() As SqlConnection
        'the following code identify that connection object having null or reference

        If objcon Is Nothing Then
            objcon = New SqlConnection("Data Source=.\sqlexpress;Initial Catalog=PROJ;Integrated Security=True")
            objcon.Open()
        End If
        If objcon.State = ConnectionState.Broken Or objcon.State = ConnectionState.Closed Then
 
            objcon.Open()
 
        End If
        Return objcon
    End Function
 
    Public Sub closingConnection()
        If objcon.State <> ConnectionState.Broken Then
 
            objcon.Close()
 
        End If
    End Sub
 
End Class
Posted 19-Sep-12 19:55pm

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi,
 
Why to write repeated code, when we have already implemented and tested code with us.
 
You can use SQLHelper class[^] to create your connection and execute your command. This class will handle everything for you.
 
Now you need to concentrate on other features.
 
Best luck.
  Permalink  
Comments
neldesai at 20-Sep-12 1:04am
   
can this helper class will do all my different queries and all other things..
And if it works ..then how can i used it in different form(how can i called them in minimum code..)
@AmitGajjar at 20-Sep-12 1:09am
   
You just need to add this class in your project and use all your desired functions. go though this class once. this will be helpfull to execute every kind of database interaction.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web01 | 2.8.1411023.1 | Last Updated 20 Sep 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100