Social Icons

Tuesday, 6 August 2013

Connection string parameters for Sql database and Oracle database with Example

Connection string parameters for SqlConnection ,Oralce Connection objects to Connect with Sql Server and Oracle databases

Introduction:

In this article you will learn what are the different connection string parameters need to set while connecting with either Sql Database or Oracle database.

Description:

In my previous article Introduction to Ado.Net part1 i mention that Creating connection is the first step while connecting with data sources.
         So to establish connection with Sql Server database we use SqlConnection object in the same manner OracleConnection object to connect with Oracle database.

Constructors are as follows

SqlConnection(string ConnectionString)
OralceConnection(string ConnectionString).

Here ConnectionString is a collection of attributes that are required while connecting with datasources.

Connection String Parameters:

Here this connection string attributes has some parameters exist. Some of these parameters are common to both SqlConnection and OracleConnetion objects. These are

                  - Provider
                  - Data Source
                  - User Id and Password
                  - Database or Initial catalog
                  - Trusted_Connection or Integrated Security
                  - DSN
we will see each these in detail now.

Provider:

As i mentioned in Ado.Net introduction , provider is required while connecting with datasource. Ado.Net provides different types of providers for connecting different types of data sources.

Here is the list Providers that Ado.Net Provides.

Oralce                                 Msdaora
SqlServer                            Sqloledb
Ms-Access or Ms-Excel     Microsoft.Jet.Oledb.4.0
Ms-Indexing Server             Msidxs

Data Source:

       It is the name of the machine on which the our required database exist, it is optional when our database exist on local machine, it is required when database is on remote machine.

 User Id and Password:

              As we know databases store secured information we need to provide User Id and Password for connecting with databases in secured mode.
                    Oracle             scott/tiger
                     Sql Server      sa/

Database or Initial Catalog:

                 These attributes are required only while connecting with Sql Server Database as it contains multiple databases. It is the database name to which we need to connect.

Trusted_Connection or Integrated Security:

               These attributes also required only while connecting with Sql Server Database  in Windows authentication mode only. In this case we don't need to provide User Id and Password attributes in the Connection String.
we provide Trusted_Connection = true or Integrated Security=SSPI

Here SSPI- Security Support Provider Interface.

DSN:

 This attribute is required while connecting with data sources using ODBC Drivers.

Examples for Sql Server and Oracle Connection Strings:

Oracle Connection String:

"Provider=Msdaora;User Id=Scott;Password=tiger [;Data Source=]"

Sql Server Connection String:

Sql Server Authentication :-

"Provider=SqlOledb;User Id=Sa;Password=;
Database=[;Data Source=]"

Windows Authentication:-

"Provider=SqlOledb;Trusted_Connection= true / Integrated Security = SSPI;
Database=[;Data Source=]"

Conclusion:

I hope you are Clear about Setting Connection string for Sql Server and Oracle databases.

Don't forget to comment and share if you like this post for our friends. Thank You.


No comments:

Post a comment

Please Give your Valuable Suggestions,Questions and Comments to improve my blog.