Connecting to the SQL Azure database using ADO. Net

How to connect to the SQL Azure database using ADO. Net

In the. Net environment, the most common method to connect to a database is to use ADO. Net. In the SQL Azure environment, ADO. Net

Can continue to use, so that developers in the. Net environment can easily learn new knowledge and skills.

Is connected to the SQL Azure database, which is also in line with the cloud computing strategy that Microsoft has been promoting, reducing the Independent Learning cloud

The cost of application development.

To connect to SQL Azure using ADO. Net, you can use either of the following methods:

The other method is hard writing in the program. Next we will introduce the second method.

To use a program to connect to SQL Azure, you need to use

System. Data. SqlClient. SqlConnectionStringBuilder class. The instance code is as follows:

Private string GetDbConnection ()

{

SqlConnectionStringBuilder connBuilder =

New SqlConnectionStringBuilder ();

String server = "unjikh4kim.database.windows.net ";

ConnBuilder. DataSource = server;

ConnBuilder. InitialCatalog = "TestDB ";

ConnBuilder. Encrypt = true;

ConnBuilder. TrustServerCertificate = true;

ConnBuilder. UserID = "sqladmin ";

ConnBuilder. Password = "pass @ word ";

Return connBuilder. ToString ();

}

Use a connection string to execute an SQL statement

Private void ExcuteSQL ()

{

Using (SqlConnection conn = new SqlConnection (GetDbConnection ()))

{

Using (SqlCommand command = conn. CreateCommand ())

{

Conn. Open ();

// Create a table

Command. CommandText =

"Create table MyTable1 (Column1 int primary key clustered," +

"Column2 varchar (50), Column3 datetime )";

Command. ExecuteNonQuery ();

// Insert data

Command. CommandText = String. Format

("Insert into MyTable1 (Column1, Column2, Column3)" +

"Values ({0}, '{1}', '{2}')", 1, "TestData", DateTime. Now. ToString ("s "));

Int rowsAdded = command. ExecuteNonQuery ();

DisplayResults (command );

}

}

}

Now let's take a look at what this code has done.

1. First, the Code uses the SqlConnectionStringBuilder object to connect to the master number of SQL Azure Database

Create a database in the name of the sampleDatabaseName string.

2. Use another SqlConnectionStringBuilder object to connect to the database created in step 1.

3. Once we connect to the Database just created in SQL Azure Database, we use the second

SqlConnectionStringBuilder is used to create database tables and some sample data operations.

4. Finally, the Code returns data to the console before and after data modification.

For more information about Connecting to SQL Azure, see Connecting to a Data

Source (ADO. NET)

1. Use vs to create a console Program

2. Replace <ProvideUserName> in the above Code with the logon name of SQL Azure Database. The format is as follows:

Login @ server. For more information about accounts, see Managing Databases and

Logins in SQL Azure.

3. Replace <ProvidePassword> with your account password.

4. Replace <ProvideServerName> with your SQL Azure server name, as shown in figure

Servername.database.windows.net, which is the same as the post part of the '@' symbol in the Login Format.

5. <ProvideDatabaseName> is the name of the database you want to create using your code (which does not exist ).

Supported Transact-SQL features:
Constant
Data restrictions
Data cursor
Database index management and index Reconstruction
Local Database temporary tables
Reserved Words
Pre-stored program
Database statistics management
Database transactions
Trigger Program
Database tables, data table joins, and table Variables
Transact-SQL language elements, such as creating, modifying, and deleting databases, tables, users, and logon.
User-Defined Functions
View table
Unsupported Transact-SQL features:
SQL CLR
Database file configuration
Database ing
Distributed Query
Distributed Transaction
File Group Management
Global temporary tables
Sparse data and Index
SQL Server Configuration Options
SQL Server Service Broker
System Table
Tracking flag.

Note:

Through the above example, we have some basic knowledge about connecting to SQL Azure. Now let's discuss

Some minor issues that need attention

To avoid injection attacks, we use the SqlConnectionStringBuilder class, which is also the. net framework

Built-in framework class
We need to well protect our connection string information. If we let others know, they can use our data.

And all other devastating losses, such as tampering.
Since we do not know how many route nodes will be passed between the communication process and SQL Azure, in order to protect our connection

The connection string is not stolen by others, so we 'd better set ADO. NET Encrypt and TrustServerCertificate.

Connection parameters, as shown in the code, Encrypt = True, TrustServerCertificate = False

Ensure that our connection string is encrypted. If any person who intercepts the communication gets your connection string

Useless.