Visual Basic

Polymorpher24-Jul-06 2:41

24-Jul-06 2:41

This should help you. Just pass your connection string to new. I have been working on this for a while now. If I am missing any references let me know. I think I made this one pretty much standalone. If you give me your email i will send you this entire component in source code and the dll.

#Region " Imports "
Imports System
Imports System.Data.SqlClient
#End Region

Public Class winConnectionManager

#Region " Variables "
Private m_Conn As SqlConnection
Private m_ConnStr As String
Private m_Valid As Boolean = True
Protected Shared m_ShowErrors As Boolean
#End Region

#Region " Constructor "
Public Sub New(Optional ByVal showErrors As Boolean = True)
Me.New(winAppConfig.GetSetting("SQLString"), showErrors)
End Sub

Public Sub New(ByVal conStr As String, Optional ByVal showErrors As Boolean = True)
Try
m_ShowErrors = showErrors
m_ConnStr = conStr
m_Conn = New SqlConnection(m_ConnStr)
m_Conn.Open()
Catch ex As Exception
m_Valid = False
If m_ShowErrors Then
Throw New Exception(ex.Message & vbCrLf & vbCrLf & " Occured in winConnectionManager::New")
End If
End Try
End Sub
#End Region

#Region " Propertys "
Public ReadOnly Property GetConnection() As SqlConnection
Get
Return m_Conn
End Get
End Property

Public ReadOnly Property GetConnectionString() As String
Get
Return m_ConnStr
End Get
End Property

Public ReadOnly Property Valid() As Boolean
Get
Return m_Valid
End Get
End Property
#End Region

#Region " Public Functions "
Public Function UpdateDataSet(ByVal ds As DataSet, Optional ByVal allowDelete As Boolean = True) As Boolean
Try
If Not m_Valid Then Return False

If Not winDSUtils.IsDSEmpty(ds) Then
If ds.HasChanges Then
Dim tbl As String = ds.Tables(0).TableName
Dim pkField As String = ds.Tables(0).PrimaryKey(0).ColumnName
Dim theInsert As String = Nothing
Dim tryedToDelete As Boolean = False

For Each r As DataRow In ds.GetChanges.Tables(0).Rows
Dim state As DataRowState = r.RowState
Select Case state
Case DataRowState.Added
If theInsert Is Nothing Then theInsert = winSQLUtils.BuildInsertSQL(ds.Tables(0))
r.AcceptChanges()
ExecuteNonQry(theInsert & winSQLUtils.GetInsertValues(r))
Case DataRowState.Deleted
r.RejectChanges()
If allowDelete Then
ExecuteNonQry("Delete From " & tbl & " Where " & pkField & " = " & r.Item(pkField))
r.Delete()
Else : tryedToDelete = True
End If
Case DataRowState.Modified
r.AcceptChanges()
ExecuteNonQry(winSQLUtils.GetUpdateSQL(r))
End Select
Next r
If tryedToDelete Then MsgBox("You are not alowed to delete these items!")
ds.AcceptChanges()
End If
End If
Catch ex As Exception
If m_ShowErrors Then
Throw New Exception(ex.Message & vbCrLf & vbCrLf & " Occured in winConnectionManager::UpdateDataSet")
End If
Return False
End Try
End Function

Public Function InsertDataRowReturnKey(ByVal r As DataRow) As Integer
Try
ExecuteNonQry(winSQLUtils.GetInsertSQL(r))
Return GetLastKey(r.Table.TableName)
Catch ex As Exception
If m_ShowErrors Then
Throw New Exception(ex.Message & vbCrLf & vbCrLf & " Occured in winConnectionManager::InsertDataRowReturnKey")
End If
Return -1
End Try
End Function

Public Function GetLastKey(ByVal tblName As String) As Integer
Try
Return IIf(m_Valid, CInt(ExecuteScaler("SELECT IDENT_CURRENT('" & tblName & "') As ID")), -1)
Catch ex As Exception
If m_ShowErrors Then
Throw New Exception(ex.Message & vbCrLf & vbCrLf & " Occured in winConnectionManager::GetLastKey")
End If
Return -1
End Try
End Function

Public Function ExecuteScaler(ByVal sqlStr As String) As Object
Try
If Not m_Valid Then Return Nothing

Return New SqlCommand(sqlStr, m_Conn).ExecuteScalar()
Catch ex As Exception
If m_ShowErrors Then
Throw New Exception(ex.Message & vbCrLf & vbCrLf & " Occured in winConnectionManager::ExecuteScaler")
End If
Return Nothing
End Try
End Function

Public Function Execute(ByVal sqlStr As String) As DataSet
Try
If Not m_Valid Then Return Nothing

Dim ds As New DataSet
With New SqlDataAdapter(New SqlCommand(sqlStr, m_Conn))
.Fill(ds)
End With

Return ds
Catch ex As Exception
If m_ShowErrors Then
Throw New Exception(ex.Message & vbCrLf & vbCrLf & " Occured in winConnectionManager::Execute")
End If
Return Nothing
End Try
End Function

Public Function ExecuteNonQry(ByVal sqlStr As String) As Integer
Try
IIf(m_Valid, New SqlCommand(sqlStr, m_Conn).ExecuteNonQuery, 0)
Catch ex As Exception
If m_ShowErrors Then
Throw New Exception(ex.Message & vbCrLf & vbCrLf & " Occured in winConnectionManager::ExecuteNonQuery")
End If
Return 0
End Try
End Function

Public Function GetData(ByVal sqlStr As String, ByVal tblName As String) As DataSet
Try
If Not m_Valid Then Return Nothing

Dim ds As New DataSet
With New SqlDataAdapter(New SqlCommand(sqlStr, m_Conn))
.Fill(ds, tblName)
End With

Return ds
Catch ex As Exception
If m_ShowErrors Then
Throw New Exception(ex.Message & vbCrLf & vbCrLf & " Occured in winConnectionManager::GetData")
End If
Return Nothing
End Try
End Function

#Region " DataReader "
Public Function GetDataReader(ByVal sqlStr As String) As SqlDataReader
Try
If Not m_Valid Then Return Nothing

Return New SqlCommand(sqlStr, m_Conn).ExecuteReader
Catch ex As Exception
If m_ShowErrors Then
Throw New Exception(ex.Message & vbCrLf & vbCrLf & " Occured in winConnectionManager::GetDataReader")
End If
Return Nothing
End Try
End Function

Public Function RDR_Execute(ByVal sqlStr As String) As DataSet
Try
If Not m_Valid Then Return Nothing

Return winDataReaderUtils.DataReaderToDataset(GetDataReader(sqlStr))
Catch ex As Exception
If m_ShowErrors Then
Throw New Exception(ex.Message & vbCrLf & vbCrLf & " Occured in winConnectionManager::ExecuteDR")
End If
Return Nothing
End Try
End Function

Public Function RDR_GetData(ByVal sqlStr As String, ByVal tblName As String) As DataSet
Try
If Not m_Valid Then Return Nothing

Return winDataReaderUtils.DataReaderToDataset(GetDataReader(sqlStr), tblName, tblName)
Catch ex As Exception
If m_ShowErrors Then
Throw New Exception(ex.Message & vbCrLf & vbCrLf & " Occured in winConnectionManager::GetData")
End If
Return Nothing
End Try
End Function
#End Region

#End Region

#Region " Overrides "
Protected Overrides Sub Finalize()
Try
m_Conn.Close()
Catch
End Try

m_Conn = Nothing
MyBase.Finalize()
End Sub
#End Region

End Class

Pablo
www.aes4you.com

karansharma24-Jul-06 5:22

24-Jul-06 5:22

Thanx for this soon reply.
I am using a class file for connection string & other data access functions.

Now i am in a strange problem.Let me explain in detail ,When i have installed the setup on computers in my office, it is functioning properly but when i tried it at client side i have got an error message like this
"The specified module could not be found " .
My connection string is : " Datasource = (local); initial catalog = " My Database" ; userid = sa; "
& the sqlserver is installed as local at the client site.

The configuration of SQL SERVER in my office & client site are same.
Please help me.

Polymorpher24-Jul-06 7:49

24-Jul-06 7:49

karansharma wrote:
"The specified module could not be found "

It doesn't sound to me like the problem is your connection string. It sounds like you have something installed on your computer that your customer does not. I would recoment looking at your clients installed programs and making sure that they have what is nessessary to run your software. Are you using and special components etc? The only thing i can see with your connection string that could be a problem is that 'Datasource' should be 'data source' and 'userid' should be 'user id'.

Pablo
www.aes4you.com

karansharma24-Jul-06 18:44

24-Jul-06 18:44

Thanx Pablo for this very soon reply ,
I have the same feeling but what all i need to install my software is .net framework & sql server. Nothing else except these twos . And i have installed them sucessfully. its something like nightmare to me ,i dont know how i can get rid of it. It may be machie problem ( Registry) . today i m going to check their registry . if u have another option pls tell me .
waiting for ur reply.
The error massege is something like this :

" System.IO.FileNotFound Exception : The Specified module could not be found "

Polymorpher25-Jul-06 16:45

25-Jul-06 16:45

Sorry I have not replied sooner, I have been VERY bussy at the office the past few days. I'm not sure what else could be causing this problem. Mybe they have there SQL configured on a domain? If all else fails and they will alow it I would try backing up there current databases and re-installing SQL. Another posible way to find the solution would be to temporarily install visual studio on one of there computers and then debug your program while it is trying to startup...I'm sorry I am not more help to you. I will help as much as I can though.

Pablo
www.aes4you.com

karansharma25-Jul-06 18:50

25-Jul-06 18:50

Thanx Pablo,
Please dont feel sorry for any thing. I have tried all the things above mentioned but all in vain . At last I request the client to Provide other Pc , so i can check where is the problem ( in my software or in the Pc ) ,
& thank God ,he agree to provide me another .
This Option works and My software installed without any problem .
I dont know where is the problem in clients PC. But my this solution works.
Thanx for ur Valuable Advices .

Polymorpher26-Jul-06 2:49

26-Jul-06 2:49

GOOD!!! I hate when that happens...But at leaset you know that it was not your solution that was a problem. Other peoples computers are not your fault, and can't be blamed on you. If you ever need help again and I can be of use let me know and I will do my best. Good luck and happy hacking Wink | ;)

Pablo
www.aes4you.com

Rizwan Bashir21-Jul-06 5:40

Rizwan Bashir

21-Jul-06 5:40

Always use Application.config while for these type of Variables. If you will right click on your project and add application.configuration file then you can put connection string over there.

also put connection stirng in any text file and use it onload of your application and on client side change the textfile.

Rizwan Bashir

karansharma23-Jul-06 20:01