[x]
Posted via EE Mobile

Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.
Question
[x]
Attachment Details
[x]
The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

  • The Grade of the Solution
  • The Zone Rank of the Expert Providing the Solution
  • The Number of Author and Expert Comments
  • The Number of Experts Contributing
  • The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!
7.6

reuse sql connection and sqlcommand in vb.net windows application

Asked by johnnyg123 in .NET, Microsoft Visual Basic.Net

Tags: Vb.Net Windows Application

I have inherited maintenace of a Vb.Net Windows application that performs many sqlcommands against various tables in a single sql server 200 database.  I noticed that creation and closing of multiple connections to the same database and also the creation of multiple sqlcommands.
(Note: the connection info is retrieved from the app.config file)

(I have included some samples in the code window below)

This seems very inefficient to me.  Is there a way to create a single connection that can be reused as needed and then closed right before the windows application is closed?  Also, is it possible to have a single sqlcommand creation and just change the appropriate properties as needed?  Any examples would be great! 
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:
159:
160:
161:
162:
163:
164:
165:
166:
167:
168:
169:
170:
171:
172:
173:
174:
175:
176:
177:
178:
179:
180:
181:
182:
183:
184:
185:
186:
187:
188:
189:
190:
191:
192:
193:
194:
195:
196:
197:
198:
199:
200:
201:
202:
203:
204:
205:
206:
207:
208:
209:
210:
211:
212:
213:
214:
215:
216:
217:
218:
219:
220:
221:
222:
223:
224:
225:
226:
227:
228:
229:
230:
231:
232:
233:
234:
235:
236:
237:
238:
239:
240:
241:
242:
243:
244:
245:
246:
247:
248:
249:
250:
251:
252:
253:
254:
255:
256:
257:
258:
259:
260:
261:
262:
263:
264:
265:
266:
267:
268:
269:
270:
271:
272:
273:
274:
275:
276:
277:
278:
279:
280:
281:
282:
283:
284:
285:
286:
287:
288:
289:
290:
291:
292:
293:
294:
295:
296:
297:
298:
299:
300:
301:
302:
303:
304:
305:
306:

Here is some declaration of variables:
 
Public Class Form1
        Inherits System.Windows.Forms.Form
        Dim logfilepath, MgtAnalysis As String
        Dim datafilepath As String
        Dim errorfilepath As String
        Dim logdate, logfilename, logmsg, prcsflag, feedday As String
        Dim logwriter As StreamWriter
        Dim errorflag As Boolean
        Dim connstrGRP As String
        Dim strsqlselect As String
        Dim BegMoSeq, EndYr, EndMo, EndMoSeq, FinalMoSeq As Integer
        Dim scBegMoSeq, scEndYr, scEndMo, scEndMoSeq, scFinalMoSeq, scUpdEndYrMo, scPA, scPA1 As New SqlCommand
        Dim mySqlConn As New SqlClient.SqlConnection
        Dim sdrPA1 As SqlClient.SqlDataReader
        Dim beginmonthseq, endmonthseq As Integer
        Dim rankcalcdone As Boolean   'IT0801
 
 
 
  Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            '********************************************************************************
            '***       Create/Write Log File Name                                         ***                                                                     
            '********************************************************************************
            Dim processerror As Boolean
 
            errorflag = False
            processerror = False
            rankcalcdone = False                                 'IT0801
            ' Get application config parameters
            Try
                logfilepath = System.Configuration.ConfigurationSettings.AppSettings("logfilepath").ToString
                datafilepath = System.Configuration.ConfigurationSettings.AppSettings("Datafilepath").ToString
                errorfilepath = System.Configuration.ConfigurationSettings.AppSettings("errorfilepath").ToString
                connstrGRP = System.Configuration.ConfigurationSettings.AppSettings("ConnectionString.connstrGRP").ToString()
                MgtAnalysis = System.Configuration.ConfigurationSettings.AppSettings("MgtAnalysis").ToString
 
                logdate = Date.Now.Year.ToString + Date.Now.Month.ToString + Date.Now.Day.ToString + Date.Now.Hour.ToString + Date.Now.Minute.ToString
                logfilename = logfilepath + "\" + "PresidentAwardMonth" + logdate + ".txt"
                logwriter = New StreamWriter(logfilename)
                logmsg = " ***** President Award Monthly Analysis Process Begins ***** " + Now.ToString & vbCrLf
                logging(logmsg, logwriter)
                logmsg = " " & vbCrLf
                logging(logmsg, logwriter)
            Catch ex As Exception
                errorflag = True
                logmsg = " "
                logmsg = "     Error in Reading App.config file  " + ex.Message + Date.Now.ToString & vbCrLf
                logging(logmsg, logwriter)
                processerror = True
            End Try
 
            If errorflag = True Then
                ' processerror()
            End If
        End Sub
 
(Note: Sub Processfiles is the "main" sub.  It is called from the click event of the button the user clicks when ready to launch the processing)
 
Sub Processfiles()
 
            logmsg = "Preparing the SQL connection for getting the data - Begins at " + Now.ToString & vbCrLf
            logging(logmsg, logwriter)
            logmsg = " " & vbCrLf
            logging(logmsg, logwriter)
            datafilepath = Trim(datafilepath) + "\"
            errorfilepath = Trim(errorfilepath) + "\"
 
            PrcsCalScore()
 
            ' if rank calc completed successfully, then call stored proc for revised ranking, to incorporate sales component
            If rankcalcdone = True Then                                                                  'IT0801
                PrcsRevisedRank()                                                                             'IT0801
            End If                                                                                                       'IT0801
 
            mySqlConn.Close()
            FileClose(1)
            Me.Dispose(True)
            Application.Exit()
 
        End Sub
 
 
 Sub PrcsCalScore()
            Dim mySqlConn As SqlConnection = New SqlConnection(connstrGRP)
            Dim selectCMD As SqlCommand = New SqlCommand(strsqlselect, mySqlConn)
            selectCMD.CommandTimeout = 1240
 
            mySqlConn.Open()
 
 
            ' TRUNCATE TABLE MONTH PRESIDENT AWARD RESULT
            selectCMD.CommandText = "Truncate Table MonthPresidentAwardResult "
           
            Dim custDA As SqlDataAdapter = New SqlDataAdapter
            custDA.SelectCommand = selectCMD
 
            Try
                selectCMD.ExecuteNonQuery()
                logmsg = " "
                logmsg = "     Truncated table MonthPresidentAwardResult  " + Date.Now.ToString & vbCrLf
                logging(logmsg, logwriter)
            Catch ex As Exception
                errorflag = True
                logmsg = " "
                logmsg = "     Error in truncating table MonthPresidentAwardResult  " + ex.Message + Date.Now.ToString & vbCrLf
                logging(logmsg, logwriter)
            End Try
 
            'Added 6/12/2009 to see if need to clear and Load PresidentAwardSales table with shopm2sale data
            'This will allow for one off processing
            'Be sure to comment this out if changes were made 
 
            Dim Answer As Integer
            Answer = MsgBox("Reload PresidentAwardSales Table?", vbYesNo + vbExclamation, "Load PresidentAwardSales")
 
            If Answer = 6 Then
                'Yes   
                scPA.CommandType = CommandType.StoredProcedure
                scPA.CommandText = "sp_Load_PresidentAwardSalesTable"
                Try
                    scPA.ExecuteNonQuery()
                    logmsg = " "
                    logmsg = "     Executed stored procedure sp_Load_PresidentAwardSalesTable  " + Date.Now.ToString & vbCrLf
                    logging(logmsg, logwriter)
                Catch ex As Exception
                    errorflag = True
                    logmsg = " "
                    logmsg = "     Error in Executing stored sp_Load_PresidentAwardSalesTable  " + ex.Message + Date.Now.ToString & vbCrLf
                    logging(logmsg, logwriter)
                End Try
 
            End If
 
            If Answer = 7 Then
                'No   
            End If
 
            BegMoSeq = beginmonthseq
            EndMoSeq = endmonthseq
 
            ' WRITE A RECORD FOR ALL SHOPS FROM SHOPINFO WITH NULL AS VALUE FOR SCORES, RANK
            scPA.CommandText = "Insert Into MonthPresidentAwardResult Select 0, 0, ShopNumber, Null, Null, " & _
             "Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, Null, 1750 From ShopInfo"
            strsqlselect = scPA.CommandText
            Dim selectCMD2 As SqlCommand = New SqlCommand(strsqlselect, mySqlConn)
            selectCMD2.CommandTimeout = 30
            custDA.SelectCommand = selectCMD2
 
            Try
                selectCMD2.ExecuteNonQuery()
                logmsg = " "
                logmsg = "     Inserted shopinfo records into table MonthPresidentAwardResult  " + Date.Now.ToString & vbCrLf
                logging(logmsg, logwriter)
            Catch ex As Exception
                errorflag = True
                logmsg = " "
                logmsg = "     Error in Inserting shopinfo records into table MonthPresidentAwardResult CMD2  " + ex.Message + Date.Now.ToString & vbCrLf
                logging(logmsg, logwriter)
            End Try
 
            selectCMD2.CommandType = CommandType.StoredProcedure
            If MgtAnalysis = "Y" Then
                selectCMD2.CommandText = "sp_Build_PresidentAward_Data_cosmic"
 
            Else
                selectCMD2.CommandText = "sp_Build_PresidentAward_Data"
            End If
 
 
            selectCMD2.Parameters.Add("@BegMoSeq", SqlDbType.Int)
            selectCMD2.Parameters("@BegMoSeq").Value = BegMoSeq
            selectCMD2.Parameters("@BegMoSeq").Direction = ParameterDirection.Input
            selectCMD2.Parameters.Add("@EndMoSeq", SqlDbType.Int)
            selectCMD2.Parameters("@EndMoSeq").Value = EndMoSeq
            selectCMD2.Parameters("@EndMoSeq").Direction = ParameterDirection.Input
 
            Try
                selectCMD2.ExecuteNonQuery()
                logmsg = " "
                If MgtAnalysis = "Y" Then
                    logmsg = "     Executed stored procedure sp_build_presidentaward_data_cosmic  " + Date.Now.ToString & vbCrLf
                Else
                    logmsg = "     Executed stored procedure sp_build_presidentaward_data  " + Date.Now.ToString & vbCrLf
                End If
                logging(logmsg, logwriter)
            Catch ex As Exception
                errorflag = True
                logmsg = " "
                logmsg = "     Error in Executing stored procedure sp_build_presidentaward_data  " + ex.Message + Date.Now.ToString & vbCrLf
                logging(logmsg, logwriter)
            End Try
 
            ' CALCULATE RANK FOR EACH CRITERIA
 
            selectCMD.CommandText = "Select * From PresidentAwardItem"
            sdrPA1 = selectCMD.ExecuteReader
            If sdrPA1.HasRows Then
                While sdrPA1.Read
                    PrcsCreatePreAwdItemRank(sdrPA1("PreAwdItem"), sdrPA1("ScoreColName"), sdrPA1("MoCntColName"), _
                    sdrPA1("RankColName"), sdrPA1("RankSeq"))
                End While
                sdrPA1.Close()
                'GET OVERALL RANKING
                PrcsCreateOverallRank()
            End If
        End Sub
 
Sub PrcsCreateOverallRank()
            Dim scRankData As New SqlCommand
            Dim scUpdResultTable As New SqlCommand
            Dim ShopNum, MoCnt, RankCnt1, RankCnt2 As Integer
            Dim Score, ScoreHolder As Double
            Dim sdrRankData As SqlDataReader
            Dim custDA As SqlDataAdapter = New SqlDataAdapter
            Dim mySqlConn2 As SqlConnection = New SqlConnection(connstrGRP)
            Dim mySqlConn9 As SqlConnection = New SqlConnection(connstrGRP)
            mySqlConn2.Open()
            mySqlConn9.Open()
 
            scUpdResultTable.CommandText = "Update MonthPresidentAwardResult Set RankAvg = " & _
            "([CSIRank] + [FOTCRank] + [CustRetentionRank] + [CustComplaintRank]) / 4 " & _
            "Where CSIRank Is Not Null and FOTCRank Is Not Null and " & _
            "CustRetentionRank Is Not Null and CustComplaintRank Is Not Null"
            Dim strsqlselect1 As String = scUpdResultTable.CommandText
            Dim selectCMD12 As SqlCommand = New SqlCommand(strsqlselect1, mySqlConn2)
            selectCMD12.CommandTimeout = 30
            custDA.SelectCommand = selectCMD12
            Try
                selectCMD12.ExecuteNonQuery()
 
                RankCnt1 = 1
                RankCnt2 = 0
                ScoreHolder = 10000000.0 'Pick a number which no score will equal to as start
 
                scRankData.CommandText = "Select ShopNum, RankAvg From MonthPresidentAwardResult " & _
                "Where RankAvg is not Null Order By RankAvg"
                strsqlselect1 = scRankData.CommandText
                Dim selectCMD13 As SqlCommand = New SqlCommand(strsqlselect1, mySqlConn2)
                selectCMD13.CommandTimeout = 30
                custDA.SelectCommand = selectCMD13
                Try
                    sdrRankData = selectCMD13.ExecuteReader
                    While sdrRankData.Read
                        If sdrRankData("RankAvg") = ScoreHolder Then
                            RankCnt2 += 1
                        Else
                            RankCnt1 += RankCnt2
                            RankCnt2 = 1
                        End If
 
                        scUpdResultTable.CommandText = "Update MonthPresidentAwardResult Set " & _
                        "OverallRank = " & RankCnt1 & ", QualifyForCompetition = 'Yes' " & _
                        "Where MonthPresidentAwardResult.ShopNum = " & sdrRankData("ShopNum")
                        strsqlselect1 = scUpdResultTable.CommandText
                        Dim selectCMD14 As SqlCommand = New SqlCommand(strsqlselect1, mySqlConn9)
                        selectCMD14.CommandTimeout = 30
                        custDA.SelectCommand = selectCMD14
                        Try
                            selectCMD14.ExecuteNonQuery()
                            ScoreHolder = sdrRankData("RankAvg")
                        Catch ex As Exception
                            errorflag = True
                            logmsg = " "
                            logmsg = "     Error in updating MonthPresidentAwardResultSet with overall rank CMD14  " + ex.Message + Date.Now.ToString & vbCrLf
                            logging(logmsg, logwriter)
                        End Try
 
                    End While
                    sdrRankData.Close()
                    logmsg = " "
                    logmsg = "     Successfully updated Overall Rank  " + Date.Now.ToString & vbCrLf
                    logging(logmsg, logwriter)
                Catch ex As Exception
                    errorflag = True
                    logmsg = " "
                    logmsg = "     Error in obtaining records from MonthPresidentAwardResult CMD 13 in PrcsCreateOverallRank  " + ex.Message + Date.Now.ToString & vbCrLf
                    logging(logmsg, logwriter)
                End Try
 
                scUpdResultTable.CommandText = "Update MonthPresidentAwardResult Set QualifyForCompetition = 'No' " & _
                "Where RankAvg Is Null"
                strsqlselect1 = scUpdResultTable.CommandText
                Dim selectCMD15 As SqlCommand = New SqlCommand(strsqlselect1, mySqlConn9)
                selectCMD15.CommandTimeout = 30
                custDA.SelectCommand = selectCMD15
                Try
                    selectCMD15.ExecuteNonQuery()
                    rankcalcdone = True                                                                                      'IT0801
                Catch ex As Exception
                    errorflag = True
                    logmsg = " "
                    logmsg = "     Error in updating MonthPresidentAwardResult set qualify for competition where rankavg is null CMD15 in PrcsCreateOverallRankroutine  " + ex.Message + Date.Now.ToString & vbCrLf
                    logging(logmsg, logwriter)
                End Try
            Catch ex As Exception
                errorflag = True
                logmsg = " "
                logmsg = "     Error in PrcsCreateOverallRankroutine CMD 12  " + ex.Message + Date.Now.ToString & vbCrLf
                logging(logmsg, logwriter)
 
            End Try
            mySqlConn2.Close()
            mySqlConn9.Close()
        End Sub
[+][-]09/03/09 08:34 AM, ID: 25251760Expert Comment

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]09/03/09 08:44 AM, ID: 25251864Author Comment

Often, when Experts are collaborating with members who have asked questions, they will request additional information about the problem. Askers respond with an author comment like this one.

Start your 30-day free trial to view this Author Comment or ask the Experts your question.

 
[+][-]09/03/09 08:58 AM, ID: 25252003Accepted Solution

View this solution now by starting your 30-day free trial. Setting up your free trial is quick, easy, and secure. We will return you to this solution, unlocked, when you're done.

About this solution

Zones: .NET, Microsoft Visual Basic.Net
Tags: Vb.Net Windows Application
Sign Up Now!
Solution Provided By: ladarling
Participating Experts: 4
Solution Grade: A
 
[+][-]09/03/09 11:46 AM, ID: 25253654Expert Comment

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
[+][-]09/03/09 01:27 PM, ID: 25254609Expert Comment

At Experts Exchange, members can ask their questions to thousands of technology professionals, also known as Experts. Experts compete and collaborate to answer those questions by leaving comments like this one.

Start your 30-day free trial to view this Expert Comment or ask the Experts your question.

 
 
Loading Advertisement...
20091021-EE-VQP-81 - Hierarchy / EE_QW_3_20080625