Three common methods for Operating SQL Server databases

Source: Internet
Author: User
-Connection string: server computer name or ip Instance name, database name, uidsa, pwd password, server computer name or ip Instance name, and database name; integratedsecuritytrue;

-Connection string: 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;-commonly used class library SqlConnection connects to the database and uses SqlCommand to execute the SQL statement SqlDataReade

-> Connection string, two common methods:

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

Use SqlConnection to connect to the database

SqlCommand

The object read by SqlDataReader (this method of reading data is a bit like StreamReader)

SqlAdapter puts data in the database into DataSet

DataSet memory-level offline database (a bit like FileStream. Read)

Comparison Model

(The image is from the Internet. If the source author sees the image, provide the link address. It takes a long time .......)

-> Database connection steps

-> Write the connection string

-> Create a connection object and use a connection string

-> Open: Open the database.

-> Shut down and release the database. You can use using.

2. Execute SQL statements

-> Use SqlCommand object

-> Steps:

-> First, create an SQL statement string (SQL) and the connection channel SqlConnection (conn)

1 string SQL = "insert into tbl (id, name) values (1, '123 '); "2 SqlConnection conn = new SqlConnection (@" server = computer name or ip \ Instance name; database = database name; uid = sa; pwd = password ;");

-> Create a SqlCommand object and hand over SQL and conn to it (through constructors or attributes)

 SqlCommand cmd = new SqlCommand(sql, conn);

-> In the case of Open, call the method (the method of the SqlCommand object)

1 conn. Open (); 2 cmd. Method ();

-> Three common methods

1 ExecuteNonQuery () specifically executes non-query statements (add, delete, modify, etc.), returns the number of affected rows 2 ExecuteScalar () for query, the returned data is the data (object) 3 ExecuteReader () in the first row of the result, which is used to execute a query to obtain data from multiple rows and multiple columns.

3. Create a table and insert data.

Notes

-> If an SQL statement is executed in SSMS, no problem occurs in ADO. Net.

-> About primary keys and automatic Growth

-> Name of the connection string Database

-> Constraints

Exception Handling

1 try 2 {3 // code with possible exceptions 4} 5 6 catch (Exception ex) 7 8 {9 // code 10 after an exception} 11 12 finally13 {14 // used to close the connection 15}

4. ExecuteScalar ()

-> Read data to obtain the first row and the first column

-> The execution process is the same except for SQL statements and execution methods.

5.-> concatenate and modify the parameters in the SQL statement.

-> Whenever a parameter is used, the variable is written as a variable.

-> Assign a value to the variable when using it.

-> Implementation steps

-> When writing SQL statements, use "@ alias" to replace the pitfalls

1 select COUNT (*) from tblLogin where logUid = '{0}' and logPwd = '{1}'; easy SQL injection vulnerability attack 2 select COUNT (*) from tblLogin where logUid = @ logUid and logPwd = @ logPwd;

-> Before executing an SQL statement in ADO. Net (cmd. Method () before calling)

-> Create an object, SqlParameter

1 SqlParameter p = new SqlParameter (parameter alias, parameter value); 2 SqlParameter p1 = new SqlParameter ("@ logUid", txtUid. text. trim (); 3 SqlParameter p2 = new SqlParameter ("@ logPwd", txtPwd. text );

-> Add parameters to the cmd object

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.