Connection string in html

ASP and ASP.NET connection string examples for Microsoft SQL Server and MySQL

A connection string is sometimes a bit obscure. Do I need ODBC or OLE DB? When you need one and Google for it, you often find old ones that either don’t work at all anymore, or are not optimal. This article provides you with a couple of ASP.NET to SQL Server connection string examples, and as a bonus for ASP to MySQL too.

I put this up here, because I often forget which connection string to use when, when a client asks a question or encounters connection problems… Throughout the examples, replace ‘sql.example.com’, ‘mysql.example.com’, ‘db-user’, ‘P4ssword’ and ‘examplecom’ with your own information.

ASP.NET connection string for Microsoft SQL Server, using System.Data.SqlClient Namespace

// asp.net connection string for sql server string strConnection = "Data Source=sql.example.com;" + "User + "Password=P4ssword;" + "Initial Catalog=examplecom;"; SqlConnection objConnection = new SqlConnection(strConn); objConnection.Open()Code language: C# (cs)

Enable TLS/SSL in System.Data.SqlClient – encrypted SQL connection

Ideally all connections should be encrypted (using TLS/SSL), so that data transfers between a SQL Server instance and a client application are secure. To enable the use of TLS/SSL in your System.Data.SqlClient connection to encrypt and secure the connection, add Encrypt=True and TrustServerCertificate=True like:

Encrypt=True; TrustServerCertificate=True;Code language: HTML, XML (xml)

Microsoft’s documentation states:

Beginning in .NET Framework 4.5, when TrustServerCertificate is false and Encrypt is true, the server name (or IP address) in a SQL Server SSL certificate must exactly match the server name (or IP address) specified in the connection string. Otherwise, the connection attempt will fail.

SqlConnection.ConnectionString Property

ASP.NET Connector/NET to MySQL

A fully-managed ADO.NET driver for MySQL. Also see my MySql.Data.MySqlClient test script for reference and more information.

string strConn = "server=mysql.example.com;" + "user=db-user;database=examplecom;" + "port=3306;password=P4ssword; SslMode=REQUIRED;"; MySqlConnection conn = new MySqlConnection(strConn);Code language: C# (cs)

ASP.NET Connector/ODBC to MySQL

Connector/ODBC is a standardized database driver for Windows, Linux, Mac OS X, and Unix platforms. Also see my MySQL Connector/ODBC test script for reference and more information.

string strConn = "DRIVER=;" + "Provider=MSDASQL;" + "SERVER=mysql.example.com;" + "DATABASE=examplecom;" + "UID=db-user;" + "PASSWORD=P4ssword;" + "SslMode=REQUIRED;"; OdbcConnection MyConnection = new OdbcConnection(strConn); MyConnection.Open();Code language: C# (cs)

Classic ASP connection to SQL Server using ODBC

Dim strConn ' Microsoft SQL Server ODBC Driver Set strConn = Server.CreateObject("ADODB.Connection") Connstr = Connstr & "DRIVER=;" Connstr = Connstr & "DATABASE=examplecom;" Connstr = Connstr & "SERVER=sql.example.com;" ' open the connection here using: Connstr, "username", "password" SqlConn.Open Connstr, "db-user", "P4ssword"Code language: C# (cs)

Choose your DRIVER= string carefully. Use “” if ODBC Driver for SQL Server 18 is installed, otherwise use “”, “”, or ask your hosting company for instructions.

By not adding “db-user” and “P4ssword” to Connstr , you can safely print the connection string for debugging purposes using: Response.Write(Connstr) .

SQL connection string with encryption in ASP

Use the connection string properties Encrypt and trustServerCertificate (if needed) to allow applications to use Transport Layer Security (TLS) encryption. For example:

Set strConn = Server.CreateObject("ADODB.Connection") Connstr = Connstr & "DRIVER=;" Connstr = Connstr & "DATABASE=examplecom;" Connstr = Connstr & "SERVER=sql.example.com;" Connstr = Connstr & "Encrypt=Yes;" Connstr = Connstr & TrustServerCertificate=Yes;"Code language: VBScript (vbscript)

You may find more information in Microsofts article Using Encryption Without Validation.

Microsoft OLE DB Provider voor SQL Server (MSOLEDBSQL)

Dim strConn Set strConn = Server.CreateObject("ADODB.Connection") Connstr = "Provider=MSOLEDBSQL;" Connstr = Connstr & "DATABASE=examplecom;" Connstr = Connstr & "SERVER=sql.example.com;" ' open the connection here using: Connstr, "db-user", "P4ssword" SqlConn.Open Connstr, "db-user", "P4ssword"Code language: VBScript (vbscript)

Deprecated: Microsoft OLE DB Provider for SQL Server (SQLOLEDB)

Only for archival purposes. Do not use in production!

Dim oldSqlConn Set oldSqlConn = Server.CreateObject("ADODB.Connection") Connstr = "Provider=SQLOLEDB;" Connstr = Connstr & "DATABASE=examplecom;" Connstr = Connstr & "SERVER=sql.example.com;" ' open the connection here using: Connstr, "username", "password" oldSqlConn.Open Connstr, "db-user", "P4ssword"Code language: VBScript (vbscript)

ASP connection string to MySQL databases using Connector/ODBC

Dim strConn Set sCstrConnonn = Server.CreateObject("ADODB.Connection") strConn.Open "Provider=MSDASQL;" & _ "DRIVER=;" &_ "Server=mysql.example.com;" & _ "Database=examplecom;" & _ "UID=db-user;" & _ "PWD=P4ssword;" &_ "SSLMODE=REQUIRED;"Code language: VBScript (vbscript)

UTF-8 with MySQL and classic ASP

Internally, VBScript runs with UCS2 character encoding, not UTF-8. UCS2 stands for 2-byte Universal Character Set and is a character encoding standard in which characters are represented by a fixed-length 16 bits (2 bytes).

If you want to fully support UTF-8 in ASP, for example with MySQL utf8mb4, then you have to define UCS2 as your connection’s charset:

This makes the ASP connection string for UTF-8 support:

strConn.Open "Provider=MSDASQL;" & _ "DRIVER=;" &_ "Server=mysql.example.com;" & _ "Database=examplecom;" & _ "UID=db-user;" & _ "PWD=P4ssword;" &_ "SSLMODE=REQUIRED;" &_ "charset=ucs2;"Code language: VBScript (vbscript)

To save UTF-8 encoded values (é, ë, á, ö, etc) in your MySQL database table, you must set ASP’s codepage to 65001 too:

%@language="VBScript" codepage="65001"%>Code language: HTML, XML (xml)

Related Posts

• How to install Microsoft’s SQL Server Driver for PHP Windows Server
• How to: Test MySQL database connectivity in ASP.NET, PHP,… Code base
• ASP.NET performance: what to keep in mind Windows Server
• How to: Determine which .NET Framework versions are… Windows Server
• MySQL InnoDB performance improvement: InnoDB buffer pool… MySQL
• How to send authenticated SMTP over a TLS encrypted… Code base

Источник

Creating a Connection String and Working with SQL Server LocalDB

An updated version of this tutorial is available here using the latest version of Visual Studio. The new tutorial uses ASP.NET Core MVC, which provides many improvements over this tutorial.

This tutorial teaches ASP.NET Core MVC with controllers and views. Razor Pages is a new alternative in ASP.NET Core, a page-based programming model that makes building web UI easier and more productive. We recommend you try the Razor Pages tutorial before the MVC version. The Razor Pages tutorial:

• Is easier to follow.
• Covers more features.
• Is the preferred approach for new app development.

Creating a Connection String and Working with SQL Server LocalDB

The MovieDBContext class you created handles the task of connecting to the database and mapping Movie objects to database records. One question you might ask, though, is how to specify which database it will connect to. You don’t actually have to specify which database to use, Entity Framework will default to using LocalDB. In this section we’ll explicitly add a connection string in the Web.config file of the application.

SQL Server Express LocalDB

LocalDB is a lightweight version of the SQL Server Express Database Engine that starts on demand and runs in user mode. LocalDB runs in a special execution mode of SQL Server Express that enables you to work with databases as .mdf files. Typically, LocalDB database files are kept in the App_Data folder of a web project.

SQL Server Express is not recommended for use in production web applications. LocalDB in particular should not be used for production with a web application because it is not designed to work with IIS. However, a LocalDB database can be easily migrated to SQL Server or SQL Azure.

In Visual Studio 2017, LocalDB is installed by default with Visual Studio.

By default, the Entity Framework looks for a connection string named the same as the object context class ( MovieDBContext for this project). For more information see SQL Server Connection Strings for ASP.NET Web Applications.

Open the application root Web.config file shown below. (Not the Web.config file in the Views folder.)

Add the following connection string to the element in the Web.config file.

The following example shows a portion of the Web.config file with the new connection string added:

The two connection strings are very similar. The first connection string is named DefaultConnection and is used for the membership database to control who can access the application. The connection string you’ve added specifies a LocalDB database named Movie.mdf located in the App_Data folder. We won’t use the membership database in this tutorial, for more information on membership, authentication and security, see my tutorial Create an ASP.NET MVC app with auth and SQL DB and deploy to Azure App Service.

The name of the connection string must match the name of the DbContext class.

using System; using System.Data.Entity; namespace MvcMovie.Models < public class Movie < public int ID < get; set; >public string Title < get; set; >public DateTime ReleaseDate < get; set; >public string Genre < get; set; >public decimal Price < get; set; >> public class MovieDBContext : DbContext < public DbSetMovies < get; set; >> > 

You don’t actually need to add the MovieDBContext connection string. If you don’t specify a connection string, Entity Framework will create a LocalDB database in the users directory with the fully qualified name of the DbContext class (in this case MvcMovie.Models.MovieDBContext ). You can name the database anything you like, as long as it has the .MDF suffix. For example, we could name the database MyFilms.mdf.

Next, you’ll build a new MoviesController class that you can use to display the movie data and allow users to create new movie listings.

Источник

Connection Strings

NOTE: Replace myServerName, myDatabaseName, myUsername, and myPassword with the correct details for your database.

How to create connection object in your code using the above connection string:

System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient(System.Configuration.ConfigurationManager.ConnectionStrings

How to override default ASPNet Membership connection string

NOTE: Replace myServerName, myDatabaseName, myUsername, and myPassword with the correct details for your database. For overriding the default ASPNet Membership connection string, it is important that the connection string name be left as «LocalSQLServer».

MySQL

MySQL connection string example:

NOTE: This example requires the following library from mysql.com to be referenced in your project (also ensure that ‘CopyLocal’ is set to ‘True’ on the reference):

NOTE: Replace myServerName, myDatabaseName, myUsername, and myPassword with the correct details for your database.

How to create connection object in your code using the above connection string:

MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection( System.Configuration.ConfigurationManager.ConnectionStrings["MySQLConnection"].ConnectionString );

Classic ASP

Connection string examples for classic ASP.

Access Database Connection String Examples

Using DSN-less method

Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\home\mysite.com\db\myfile.mdb;"

Using DSN

Conn.Open "MyDSN" Set Conn = CreateObject("ADODB.Connection") Conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\home\mysite.com\db\myfile.mdb;"

MySQL Connection String (ASP)

For newer versions of MySQL, use the below code.

Dim conDB Dim rs '--Set the connection Set conDB = CreateObject("ADODB.Connection") '--You may need to use OPTION=3 instead of 16834 conDB.Open "DRIVER=MySQL ODBC 5.1 Driver;SERVER=mysql10.hostek.com;OPTION=16834;USER=db_un;Password=db_pw;DATABASE=db_name" '--Set the recordset Set rs = CreateObject("ADODB.Recordset")

For older MySQL versions, you will want to use the below code.

Dim conDB Dim rs '--Set the connection Set conDB = CreateObject("ADODB.Connection") '--You may need to use OPTION=3 instead of 16834 conDB.Open "DRIVER=MySQL ODBC 3.51 Driver;SERVER=mysql10.hostek.com;OPTION=16834;USER=db_un;Password=db_pw;DATABASE=db_name" '--Set the recordset Set rs = CreateObject("ADODB.Recordset")

MSSQL Connection String (ASP)

ConnStr="DRIVER=;SERVER=sql1.hostek.com;UID=db_username;PWD=db_password;DATABASE=db_name" Conn.open ConnStr

Replace the username, password, and database name with the information for your database.

Источник