Database Connection String in VB2008

[vineet]

Dear Friends,
May I request for a small help regarding VB-MySQL !
I have ERP solution in VFP for medium scale businesses.
I am now converting it to VB-MySQL.
In this, different users need to login with their ID's.
So, the connection string can't be defined while writing code. It will
be different for each session (to be entered from login screen)
Secondly, there are more that 100 VB.NET forms, each requiring
connection string to MySQL.
If we store conn string in app.config of a particular form, how other
forms can retrieve it ?
My query is that, where & how to store the connection string sothat --
1) it will be available to all different VB.NET forms, and
2) connection string to MySQL will be different for every session.
You are requested to throw some light on the above-mentioned task.
Thanks,
Vineet.

[Vineet]

This is editing by the author himself: (since google groups doesn't
have edit facility)
============================================
The VB forms are in different projects. That's why I am wondering how
to store & retrieve database connection string in app.config
---Vineet
=============================================

[The_Fruitman]

"It will be different for each session (to be entered from login

screen) " I would think that the connection string would be different
for each user not each session.

Here's one way I can think of doing this (its early here and I may not
be thinking this through in the best way).


I'm not sure why the VB forms are in different projects, but it
doesn't matter for this. I would recommend creating a DLL (Class
Library project) that both projects would reference. This DLL would
facilitate communication to and from the database as well as any other
code that you tend to use repeatedly (Its nice to have code in one
location if you need to make a change rather than having identical
code in many locations that requires changing). Since most of your
connection string remains the same, have it coded inside the a class
in the DLL project. The DLL Project can have an app config that you
can store configurables such as the IP address or server name and the
database name. Then create a function that builds your connection
string. The username and password variables come from the login
form.

(Note: the following code was typed in here and is most likely missing
some important syntax - I haven't coded in VB for over a year).

Public String CreateConnectionString(UserName as String, Password as
String)
Return Server=(from app.config);Database=(from
app.config);Uid=UserName;Pwd=Password;

End Sub

> > Vineet.- Hide quoted text -
>
> - Show quoted text -

[Processor Devil]

ok, but I still think using sessions is much more effective :P. I used to code cms systems in PHP. User logged in (or sent some information), those were processed and saved into sessions. It is simple, fast and quite secured.

2009/7/3 The_Fruitman <evilfrui...@gmail.com>

[Processor Devil]

I am not sure if this will help... So it is an asp.net application? If it is, then you can use Sessions. Every session is unique for each user

2009/7/3 Vineet <vineet....@gmail.com>

[Kira Qian]

You'd better use Web Service or remoting, the client app won't store connection. It should be store on the server that offer web service.

Take this for your reference, hope this helps. If you want any detail help, please tell us.
http://www.codeproject.com/KB/webservices/myservice.aspx

--
Sincerely,
Kira Qian
E-mail: kira...@gmail.com

[☺Aνιηαѕн ∂єѕαι™☺]

Hello Vineet let me know that this is ryt or wrong

1.You want to connect to dynamic Database?
2. You want to Build Connection String Dynamically?
3.You want to access 1st Connection String from app.config file??

Answer these and i will try to help..

--
Thanks & Regards
Avinash Desai
*SAVE Nature SAVE Tree SAVE*

Think Before you print: Please consider our environment before printing this e-mail
Fred Allen  - "California is a fine place to live - if you happen to be an orange."

[The_Fruitman]

I would agree with you that using sessions is much more effective IF
this is a web application. Since the OP stated that they were using
VB.NET Forms I assumed that the application was a WinForms application
and crafted my response as such. If the OP wants correct information
they must state that they are working with Winforms or webforms.

On Jul 3, 8:23 am, Processor Devil <processor.de...@gmail.com> wrote:
> ok, but I still think using sessions is much more effective :P. I used to
> code cms systems in PHP. User logged in (or sent some information), those
> were processed and saved into sessions. It is simple, fast and quite
> secured.
>

> 2009/7/3 The_Fruitman <evilfruitsmas...@gmail.com>

> > > > Secondly, there are more that 100 VB.NET <http://vb.net/> forms, each

> > requiring
> > > > connection string to MySQL.
> > > > If we store conn string in app.config of a particular form, how other
> > > > forms can retrieve it ?
> > > > My query is that, where & how to store the connection string sothat --

> > > > 1) it will be available to all different VB.NET <http://vb.net/>forms, and

> > > > 2) connection string to MySQL will be different for every session.
> > > > You are requested to throw some light on the above-mentioned task.
> > > > Thanks,
> > > > Vineet.- Hide quoted text -
>

> > > - Show quoted text -- Hide quoted text -

[Vineet]

---------------------------------------------------------------------------------------------------------------
1) My application is Win Forms application. Not a web app.

2) Conn Strings will be built dynamically.
(In short, each user will login with his/her ID and password to mysql
through VB.NET WinForms, doint some work like adding records, fetching
reports, ets., then logging off.) So connection string will be
different for different users.

3) The reason as to WHY I maintain separate projects for separate
forms---
This is to simplify client-updates. I distribute only .exe files to my
clients.
If there is any bug in my code, I can send only a small .exe file
pertaining to that Form. This way, customer servicing is very easy.

What I Tried after my initial post --
1)Created a DLL project with public string property with user scope in
Preferences--Settings.
2)This DLL is referenced in Login form project.
3) From Login form, this string property is modified to hold user's
connection string.
4) This DLL is referred in all other projects requiring conn string.

.......and this works.......
I hope there will not be any security threat for capturing login info
by any malicious lifeform !!

Any ideas for the betterment of the Solution?

Thanks,
Vineet.
---------------------------------------------------------------------------------------------------------------

[Cerebrus]

Gosh! That's a horrible solution to a common enough problem. Do you
realize the scalability implications of this kind of design ?

On Jul 3, 9:37 pm, Vineet <vineet.deod...@gmail.com> wrote:
> ---------------------------------------------------------------------------­------------------------------------

[☺Aνιηαѕн ∂єѕαι™☺]

Hi
Vineet 

Here's the Solution you can try with this
If any error please let me know.. Even i am using the same if any variables or something missing let me know



Just i have Declared Some Global Variables here

Module GlbVariables

    Public glb_DBUserid As String
    Public glb_DBPassword As String
    Public glb_DBName As String
    Public glb_ServerName As String

End Module

App.congif file connection String

    <connectionStrings>
          <add name="DefaultConnectionStringToMasterDatabase" connectionString="Data Source=ServerName;Initial Catalog=Master;integrated security=True;uid=UserName;pwd=Password"
            providerName="System.Data.SqlClient" />
    </connectionStrings>





'SQL DATA Manager Class

This Below Class uses the Master database Connection String and then Connects to the Databse which is Stored in the Global Variable with UserId and Password..

If any error in the code please let me know

Imports System.Data.SqlClient
Imports System.Configuration

Namespace DataManager

   Public Class SqlServerDataManager

#Region "Private Attributes"
        Private m_ConnectionString As String
        Public m_Connection As SqlConnection
        Private m_Command As SqlCommand
        Private m_DataAdapter As SqlDataAdapter
        Private m_CommandBuilder As SqlCommandBuilder
        Private StrSql As String
#End Region

 
        'Constructor
        Public Sub New()
        'In Below Line i have connected to the Master database this is mandatory and after that we can build connectionstring Dynamically as shown below
            m_ConnectionString = ConfigurationManager.ConnectionStrings("DefaultConnectionStringToMasterDatabase").ConnectionString

          Dim ConnStrBuiler As New SqlConnectionStringBuilder(m_ConnectionString)
            Dim PrevYearConnStr    Builer As New SqlConnectionStringBuilder(m_ConnectionString)

            Try
                If Len(glb_DBName) <= 0 Then
                    m_Connection = New Data.SqlClient.SqlConnection(m_ConnectionString)
                Else
                    ConnStrBuiler.InitialCatalog = glb_DBName
                    m_Connection = New Data.SqlClient.SqlConnection(ConnStrBuiler.ToString())
                End If

                glb_DBUserid = ConnStrBuiler.UserID
                glb_DBPassword = ConnStrBuiler.Password
                glb_ServerName = ConnStrBuiler.DataSource
                glb_strConnString = ConnStrBuiler.ToString()

                m_Command = New Data.SqlClient.SqlCommand
                m_Command.Connection = m_Connection
                m_Command.CommandType = CommandType.Text

              
            Catch ex As System.InvalidOperationException
                MessageBox.Show(ex.Message.ToString)

            Catch ex As Exception
                MessageBox.Show(ex.Message.ToString)
            End Try
        End Sub

'And a Test Query for the above code
'Wrote a function which Executes the Query sent as parameter and returns the value


 Public Function GetValue(ByVal SQL As String) As Object
            ' Function will return First-Row First-Column result of executing SQL Query over Access Database
            If m_Connection.State = ConnectionState.Open Then
                m_Connection.Close()
            End If

            If SQL.Length <= 0 Then
                Throw New InvalidExpressionException
            End If

            Dim oObject As Object
            m_Command.CommandText = SQL
            m_Command.CommandType = CommandType.Text

            Try
                m_Connection.Open()
                oObject = m_Command.ExecuteScalar()
                m_Connection.Close()
                Return oObject

            Catch ex As System.Data.SqlClient.SqlException
                ' Closing connection
                Call CloseConnectionObject()
                Throw New InvalidExpressionException(ex.Message.ToString)
            Catch ex As System.InvalidOperationException
                ' Closing connection
                Call CloseConnectionObject()
                Throw New InvalidOperationException(ex.Message.ToString)
            Catch ex As Exception
                ' Closing connection
                Call CloseConnectionObject()
                Throw New Data.DataException
            Finally
                ' Closing connection
                Call CloseConnectionObject()
            End Try

        End Function


Public Sub CloseConnectionObject()

            ' Closing connection
            If m_Connection.State = ConnectionState.Open Then
                m_Connection.Close()
            End If
            If Not IsNothing(m_DataAdapter) Then
                m_DataAdapter.Dispose()
            End If
            If Not IsNothing(m_CommandBuilder) Then
                m_CommandBuilder.Dispose()
            End If
        End Sub

    End Class

End Namespace


I think this will help you a lot ..

--
Thanks & Regards
Avinash Desai
*SAVE Nature SAVE Tree SAVE*

Think Before you print: Please consider our environment before printing this e-mail

Adrienne Gusoff  - "Opportunity knocked. My doorman threw him out."

[Vineet]

Dear Avinash,
Thanks for your code.
But I think that I haven't explained my need correctly.
It is like this ----
Users will go to login form(of course, it will be .exe).
User will type MySQL login ID and password and computer name hosting
MySQL.

Login ID & password will be different for each & every user.
If the ID & password are correct, then permission is granted and Main
Form opens.
Otherwise permission is denied.

Therefore, I can't hardcode connection string in app.config as you
say.

You code reads (in app.config) ---

<connectionStrings>
> <add name="DefaultConnectionStringToMasterDatabase"
> connectionString="Data Source=ServerName;Initial Catalog=Master;integrated
> security=True;uid=UserName;pwd=Password"
> providerName="System.Data.SqlClient" />
> </connectionStrings>

Here, we can't hardcode like "Data Source=Wheels;uid=Vineet;pwd=qwert
$1"

In this situation, what's your call?

---Vineet.

[Vineet]

===============================================
Cerebrus,
Can you pl. suggest an alternative to this problem?
I resorted to this design considering client updates (one project &
one exe for a particular task).
Any light on how can it threaten the scalability?

Thanks,
Vineet
================================================

> > pertaining to that Form. This way, customer servicing is very easy.- Hide quoted text -

[Cerebrus]

Research ClickOnce:

http://msdn.microsoft.com/en-us/library/wh45kb66.aspx

> > - Show quoted text -- Hide quoted text -