Three common methods for manipulating SQL Server databases

1. ADO

Connection string, commonly used in two ways:

server= computer name or ip\ instance name; database= database name; uid=sa;pwd= password;

server= computer name or ip\ instance name; database= database name; integrated security=true;

--Common class libraries

SqlConnection connecting the database with

SqlCommand Executing SQL statements

SqlDataReader Read object (this kind of reading data is a bit like StreamReader)

Sqladapter putting data from a database into a dataset

DataSet memory-level offline database (somewhat like filestream.read)

Contrast model

(Pictures from the Web, if the original author see, please provide a link address, the time is very long ....) ）

Steps to connect to the database

Write connection string

Create a Connection object, using a connection string

--Open Database

-Close the release database, you can use the using

2. Execute SQL statements

, using the SqlCommand object

Steps:

--First Create SQL statement string (SQL) with Connection Channel SqlConnection (conn)

1 string " INSERT into TBL (ID, name) VALUES (1, ' 123 '); " 2 New SqlConnection (@ "server= computer name or ip\ instance name; database= database name; uid=sa;pwd= password; ");

Create a SqlCommand object, give SQL and Conn to it (by constructor or property)

New SqlCommand (SQL, conn);

In the case of open, call Method (method of SqlCommand object )

1 Conn. Open (); 2  cmd. method ();

Three common methods

1 ExecuteNonQuery ()   specifically executes non-query statements (add, delete, change, etc.), returns the number of rows affected 2 ExecuteScalar ()     executes the query, which returns the first column of data (object) 3 of the first row in the result  ExecuteReader ()     performs queries specifically to get multiple rows and columns of data

3, first create a table, insert data

Attention issues

SQL statement If you do not have a problem in SSMs, the problem does not occur in ADO

About primary keys and autogrow

The name of the connection string database

About constraints

Exception handling

1 Try 2  { 3         //code that may appear to be abnormal 4  } 5  6  Catch(Exception ex)7  8  { 9         //code After the exception occurredTen  } One  A  finally -   { -         //used to close the connection the}

4, ExecuteScalar ()

Read data, get first row and first column

Execute in addition to the SQL statements and execution methods are different processes are the same

5. Change the parameter stitching in SQL statement

Where the parameters are used, the variable is written

When used, assign a value to this variable

Implementation steps

When writing SQL statements, use "@ aliases" instead of digging holes.

1  Select COUNT(*) fromTblloginwhereLoguid='{0}'  andLogpwd='{1}'; Easy SQL Injection Vulnerability Attack2  Select COUNT(*) fromTblloginwhereLoguid=@logUid  andLogpwd=@logPwd;

---Before executing SQL statements in ADO (cmd. Method () before calling)

Create an object, SqlParameter

1 New SqlParameter (the alias of the parameter, the value of the parameter); 2 New SqlParameter ("@logUid", TxtUid.Text.Trim ()); 3 New SqlParameter ("@logPwd", Txtpwd.text);

Add parameters to the CMD object

Cmd. Parameters.Add (Parameter object); cmd. Parameters.Add (p1);

Three common ways to manipulate SQL Server databases