Connecting to SQL Server in ASP.Net

Introduction

We can connect our application or Open() the connection to SQL Server database using .Net Data Provider, OLEDB.Net Data Provider or ODBC.Net Data Provider.

Connect using .Net Data Provider

To connect using .Net Data Provider we have to use

            string sqlConnectString1 = "Data Source=(local);" +
"Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            using (SqlConnection connection = new SqlConnection(sqlConnectString1))
            {
                connection.Open();

                // Return some information about the server.
                Console.WriteLine("---.NET data provider for SQL Server " +
                "with Windows Authentication mode---");
                Console.WriteLine("ConnectionString = {0}\n", sqlConnectString1);
                Console.WriteLine("State = {0}", connection.State);
                Console.WriteLine("DataSource = {0}", connection.DataSource);
                Console.WriteLine("ServerVersion = {0}", connection.ServerVersion);
            }

And with authentication we have to use

            string sqlConnectString2 = "Data Source=(local);" +
            "User Id=sa;Password=password;Initial Catalog=AdventureWorks;";

            using (SqlConnection connection = new SqlConnection(sqlConnectString2))
            {
                connection.Open();

                // Return some information about the server.
                Console.WriteLine("\n---.NET data provider for SQL Server " +
                "with SQL Server Authentication mode---");
                Console.WriteLine("ConnectionString = {0}\n", sqlConnectString2);
                Console.WriteLine("State = {0}", connection.State);
                Console.WriteLine("DataSource = {0}", connection.DataSource);
                Console.WriteLine("ServerVersion = {0}", connection.ServerVersion);
            }
        }

Connect using OLEDB .Net Data Provider

To connect using OLEDB .Net Data Provider we have to use

            string oledbConnectString = "Provider=SQLOLEDB;Data Source=(local);" +
            "Initial Catalog=AdventureWorks;User Id=sa;Password=password;";

            using (OleDbConnection connection =
            new OleDbConnection(oledbConnectString))
            {
                connection.Open();

                // Return some information about the server.
                Console.WriteLine("\n---.NET data provider for OLE DB---");
                Console.WriteLine("ConnectionString = {0}\n", oledbConnectString);
                Console.WriteLine("State = {0}", connection.State);
                Console.WriteLine("DataSource = {0}", connection.DataSource);
                Console.WriteLine("ServerVersion = {0}", connection.ServerVersion);
            }

Connect using ODBC .Net Data Provider

To connect using ODBC .Net Data Provider we have to use

            string odbcConnectString = "Driver={SQL Native Client};" +
"Server=(local);Database=AdventureWorks;uid=sa;pwd=password;";

            using (OdbcConnection connection =
            new OdbcConnection(odbcConnectString))
            {
                connection.Open();

                // Return some information about the server.
                Console.WriteLine("\n---.NET data provider for ODBC---");
                Console.WriteLine("ConnectionString = {0}\n", odbcConnectString);
                Console.WriteLine("State = {0}", connection.State);
                Console.WriteLine("DataSource = {0}", connection.DataSource);
                Console.WriteLine("ServerVersion = {0}", connection.ServerVersion);
            }


HAVE A HAPPY CODING!