Learning Microsoft SQL Server and C # technical record 1: Connecting to a Database

Tools: Microsoft SQL Server 2008R2

Microsoft Visual Studio 2008

Learning Notes : connecting to a database

(1) Establishing a database

Use master;

IF db_id (' Edubasedemo ') is not NULL

BEGIN

ALTER DATABASE Edubasedemo

SET Single_user

With ROLLBACK IMMEDIATE;

DROP DATABASE Edubasedemo;

END

CREATE DATABASE Edubasedemo

On

(Name= ' datafile '

, filename= ' C:\EduBaseDemo\DataFile.mdf ')

LOG on

(Name= ' Logfile '

, filename= ' C:\EduBaseDemo\Logfile.ldf ');

(2) Reference namespace

Using System.Data.SqlClient

(3) Enter the appropriate code in the Connect button

3.1Windows Authentication

SqlConnection SqlConnection = new SqlConnection (); declaring and instantiating SQL connections;

SqlConnection.ConnectionString =

"Server= (Local);D atabase=edubase2017;integrated Security=sspi"; In A string variable, describe the server address, database name, and integrated security (that is, whether to use Windows authentication) required for the connection string ;

Sqlconnection.open (); Open The SQL connection;

MessageBox.Show (" database connection succeeded "); displayed in a message box;

Sqlconnection.close (); Close the SQL connection;

MessageBox.Show (" database off ");

3.2 SQL Server authentication Mode

SqlConnection SqlConnection = new SqlConnection (); declaring and instantiating SQL connections;

SqlConnection.ConnectionString =

"Server= (Local);D atabase=edubase2017; User Id=sa;password=sa "; In A string variable, describe the server address, database name, and integrated security (that is, whether to use Windows authentication) required for the connection string ;

Sqlconnection.open (); Open The SQL connection;

MessageBox.Show (" database connection succeeded "); displayed in a message box;

Sqlconnection.close (); Close the SQL connection;

MessageBox.Show (" database off ");

(4) Optimize the connection of the database

(4.1) Reuse of connection code snippets and post-modification convenience implementations-application configuration Files

Select the application configuration file in the new item

<?xml version= "1.0" encoding= "Utf-8"?>

<configuration>

<connectionStrings>

<add name= "SQL"

Connectionstring= "server= (local);D atabase=edubasedemo;integrated Security=sspi"

Providername= "System.Data.SqlClient"/>

</connectionStrings>

</configuration>

How to reference the config file:

Add a reference to system.configuration in a reference

Using System.Configuration-- reference namespaces

Specific usage code:

if (configurationmanager.connectionstrings["SQL"]! = NULL)// if Configuration Manager reads from the configuration file to The specified connection string;

{

SqlConnectionStringBuilder SqlConnectionStringBuilder = new SqlConnectionStringBuilder (); declares and instantiates the SQL Connection string constructor;

Sqlconnectionstringbuilder.connectionstring

=configurationmanager.connectionstrings["SQL"]. ConnectionString;

}

Learning Microsoft SQL Server and C # technical record 1: Connecting to a Database