Database basics and exception handling in ADO and C #

First, the database base

1. Start database service--sqlserver (MSSQLSERVER)

Mode 1:

Start--Program--microsoft SQL Server r2--Configuration tool--sql Server Configuration Manager--sqlserver Service

Mode 2:

Go to Control Panel--management tools--services--Find SQL Server (MSSQLSERVER)

Mode 3:

Start-run-enter services.msc return and enter into the list of services, locate SQL Server (MSSQLSERVER)

Mode 4:

Start-run-input net start MSSQLServer and return

2. Open the database and log in

2.1 Open

Mode 1:

Start--Program--microsoft SQL Server r2--sql server Management Studio

Mode 2:

Start-run-Enter ssms and click OK

2.2 Login

2 different ways:

Windows authentication modifies the password security of the SA--Login name--sa

SQL Server Authentication (recommended) default Administrator sa password you set it yourself

Server name: Enter a point number

* * Connect to other database servers on the network?

Enter "IP Address" in the server name

Enter the login name and password provided

3. New and Configuration database

4. Database Separation and addition

5. Simple T-SQL statements

5.1 Add insert into table name (field list) values (value);

Note: * Number of values and data type must be the same as the number of field names and data types

* The identity column cannot be inserted manually, and the data for that column is automatically generated

* The value of the foreign key (the value from the parent table's primary key)

* Whether check (check) constraints are violated

*datetime type format correct year/month/day year-month-Sun moon/day/year

5.2 Delete
Delete from table name where condition

Attention:
* When deleting data, consider the relationship of the primary foreign key
* Delete the foreign key first, then delete the data

5.3 Modifications
Update table name
Set column Name 1 = updated value,
... Column name n= updated value
Where condition

5.4 Queries
Select Column name from table name

* Multiple columns, separated by commas

* Query all columns in the table, replace all column names with *

* Need to display several columns, query several columns,

II. introduction of ADO

1. What is ADO?
Microsoft. NET platform, a technology for connecting and accessing databases

2, ADO. NET's 2 largest components
Data Set--dataset
. NET Framework Data Provider (contains 4 large objects)

3. The. NET Framework Data Provider has 4 large objects
1.Connection (Connection object): Responsible for connecting to database
2.Command (Command object): Send SQL statements, perform additions and deletions, and change operations
3.DataReader (Data read object): Responsible for reading data
4.DataAdapter (Adapter object): Responsible for reading data, populating data into DataSet object dataset

Second, write code to connect SQL Server database with ADO
Step 1: Reference namespaces
Using System.Data.SqlClient;

Step 2: Write the database connection string
Way One: Handwriting
String url= "server= server name (can be used. instead);
database= database name;
uid= login name; pwd= login Password "

Mode two: auto generate "reference P294 page 14.3"

Step 3: Create the SqlConnection object, associate the connection string
SqlConnection Object name = new SqlConnection (database connection string);

Step 4: Open the database connection,
Call the Open () method of the SqlConnection object
The name of the object. Open ();

Third, exception handling in C #

1, exception ≠ syntax error, program operation in the event of an accident

2. How exceptions are handled in C #

Try {There may be code for the exception

Open a database connection

} catch (Exception type) {

Code to process the exception

} finally {

Regardless of whether there is an exception, the code here will always execute//close the database connection

}

3. Use exception handling to connect to the database
Connection string
Creating a Connection object, associating a connection string

1 Try2     {3 The Connection object. Open ();4     }5     Catch(Exception ex)6     {7Console.WriteLine ("Exception:"+Ex. Message);8     }9     finallyTen     { One The Connection object. Close (); A}

Four, ADO. NET implementation queries a single value

1. The SELECT statement returns a unique 1 value

1. In the SELECT statement, use the aggregate function Select Aggregate function (column name) from table name where condition

Common aggregate functions: Count, Max, MIN, avg

2.select 1 Column name from table name where primary key column = value

Step 1: Connect and open the database (4 lines of code)

Step 2: Write a SELECT statement that returns a single value

Step 3: Create a Command object, associate "SELECT statement" and "Connection object"

Step 4: Invoke the Command object's method ExecuteScalar (), execute the "SELECT statement"

The return type of ExecuteScalar () is object//following this line of code, which requires the type of the conversion data type variable name = Command object. ExecuteScalar ();

Step 5: Close the database connection

Database basics and exception handling in ADO and C #