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);
            }

Comments

Post a Comment

Popular posts from this blog

Customize User's Profile in ASP.NET Identity System

Image
Note: You should read this post instead, I found below walkthrough will not work on ASP.NET Identity 2. I will re-work on this post soon. In this post you will learn how to customize User’s Profile and add some more fields like FirstName, LastName, EmailID etc with ASP.NET Identity System. In my last post I listed some of the great new features introduced with Identity system .
Read More »

Lambda two tables and three tables inner join code samples

In this blog post you will learn inner join using lambda queries. There will be two samples, in first sample you will see how you can join two tables and in second sample you will see how you can extend even further to join three tables and so on. I will sample in-memory data, but the same implementation will work with IQueryable (lazy query) too. Here's the code snippet:- namespace ConsoleApp1 {     class Program     {         static void Main()         {             var table1 = new List<Table1>();             table1.Add( new Table1 { Id = 1, Name = "Name 1" , Address = "Address 1" });             table1.Add( new Table1 { Id = 2, Name = "Name 2" , Address = "Address 2" });  ...
Read More »