Ado. Net (connotation efficiency problem)

Ado. net

Why Learning ADO. Net?

Previously we learned to view data and operate data in the query analyzer. We cannot allow common users to learn SQL,

Therefore, we set up a Web winform interface to allow users to conveniently operate data in the database.

What is ADO. Net?

Ado. Net is a group of class libraries.ProgramTo access the database, just like class operations under system. Io.CompositionSame, system. data. this class is used to operate databases (not only MSSQL Server). It provides a unified programming interface that allows you to operate other databases (such as access and Oracle) in the same way as MSSQL Server.

 

 The three most important statements for connecting to a database:

Data Provider (common class)

ConnectionUsed to connect to the database

CommandUsed to execute SQL statements

DatareaderRead-only, only-in result set, read data one by one (streamreader and xmlreader are similar in usage in Microsoft class libraries)

 

In addition to the above three:

Dataadapter, an object that encapsulates the preceding three objects.

 

The next question is where the data we read is stored?

Dataset (Dataset), temporary database.

Disconnected Data Operations

 

Other common classes in ADO. net

Connectionstringbuilder// Automatically generate the connection string(Some basic information is required during the connection, and it is passed through)

Parameter // SQL statement with Parameters

Transaction // use transactions in ADO. net

Dataset-related classes:

Dataview // View class. The data in the able can be viewed from different perspectives.

Rows in datarowview // dataview.

Datatable // data table in Dataset

Rows in datarow // datatable

Datacolumn // columns in the datatable

Datarealation // relationship between datatable and datatable

Constraints created in constraint // datatable

 

I. Data Source

 

2. Understand the database connection process

What should I do if I can't remember it right away?

The aboveCodeIt's just for everyone to understand that there is no need to write so much in the future.

 

Iii. Simplified database connection

Step 1: declare a string

Step 2: Shut down the database and release its resources when it is used up.

 

The simplified code, dispose () does not need to be called every time. By default, using is used to help us tune the code.

 

Iv. Simplified code for connecting to the database

 

 

V. view the dispose method using the anti-Compiler

 

Vi. Notes for database connection

There is an event in the connection object: con. statechange uses this event to capture changes to the connection status every time. Let's take a look at its specific usage:

 

VII. Event con. statechange

How to set the connection string:

 

8. How to set the connection string attribute

Without this tool, how can we generate this string? Is to use sqlconnectionstringbuilder

Demo:

 

9. Use sqlconnectionstringbuilder to write the connection string attributes

Can I build a small generation tool for the connection string by myself? It is very easy to use this class and it will be done right away.

 

10. Generation Tool for connecting strings

We recommend that you write the regular string.

We have connected the database and have not performed any operations on it. Next we will talk about the second object command.

 

Connect to the database and insert a record to it:

Select a simple table in the database, create the winform interface, and click "insert data.

 

11. Insert a column into the database

Open it as soon as possible and close it early. When you use this resource, you cannot open it for less. There is a connection pool problem here. I will explain it in detail later.

 

12. Insert a data entry into another database

 

13. delete a record in the database

 

14. Update a piece of data in the database

  Summary: We added, deleted, modified, and code to the database just now. Only SQLStatement is different. Create connection objects, SQLStatement, create another object, open, execute, and close.

Next let's look at the query.

 

15. Code for querying data in the database

 

16. Process of querying data output results

We can try to use the software to practice what we just explained above. I made statistics on the areas that are prone to errors. Summary.

1. The problem that occurs when connecting strings: Write the computer name, as shown in the following figure.

 

17. view the computer name of the database connection

1. The database name is written into the table name.
2. An error is reported due to an SQL statement write error.

How to adjust it? Insert an error

 

18. insert an error.

The Code contains con. open ();

Int r = cmd. executenonquery ();

In principle, try catch should be added, and network disconnection may occur.

However, the error cannot be displayed. If it is added here, it will be thrown out later.

All in all, the following errors are common in ado.net:

1. Connection string Problems
2. SQL statement Problems

 

19. executescalar () method

Statementcompleted event, triggered after each SQL statement is executed.

When multiple statements are executed simultaneously (separated by semicolons), how does one obtain the number of lines affected by each statement?

The actual return value is the sum of the number of rows affected by each statement.

In addition to executing the above aggregate function, the executescalar () method inserts a statement to obtain the return value number of the statement just inserted.

 

20. Execute the query statement and return to the automatic numbering first Interface

 

21. Execute the query statement and return the automatically numbered code section.

Therefore, executescalar () is not only able to execute count (*) Aggregate functions, but can be used for any single statement returned.

There are three common methods:

 Executenonquery ()Execute add, delete, modify, and delete operations on the database to return the affected number of rows. Suitable for: insert, delete, update(-1 is returned for other statements)

Executescalar ()Execute the query and return the first column of the first row.

Executereader ()Execute the query and return the datareader object.

 

Do you still remember the example of the query we just made above? The following figure shows the principle:

 

22. datareader

 

The following describes how to use datareader to obtain data in a column:

 

23. Reader [] and reader. getvalue

 

Twenty-four. Reader [] and reader. getvalue code

Many times we don't want to convert the type. What should we do at this time? Please refer to the following method.

 

25. Use reader to obtain strong data types

 

Notes for using sqldatareader

The types in the database are not the same as those in C #. float in the database must be obtained using getdouble () of C.

 

26. Processing and judgment of null values

 

27. obtain data from multiple result sets

Next we will use sqldatareader to make a small case.

 

28. login first page

TIPS:

 

Twenty-nine, Operation Skills, convert to a row

 

30. logon case code

When the user fails to log on, the system prompts whether the user name is wrong or the password is wrong. How can this problem be solved? Think about it.

Next, let's take a look at the problems related to the ado.net connection pool.

 

. Connection Pool not enabled

 

. Connection Pool Enabled

We found that the connection pool is quite fast after it is enabled.

Next we will use a tool to check what is done inside the database when it is enabled or not.

First, let us know that SQL has such a function:

 

33. Monitor internal conditions of the Connection Pool

 

In this case, another connection is enabled.

2. Under what circumstances will the connection object in the pool be used?

1> when a connection object calls the close () method, the connection will be put into the pool.

2> when you create a connection object again, the connection object in the pool is used only when the connection string used to create the connection object is exactly the same as the connection string of the existing connection object in the pool.

 

35. The pool can be configured through the connection string

3. You can set the number of connection objects in the connection pool by setting the connection string.

4. The destruction of connection objects in the pool is determined by the system.

Summary of using the ado.net connection pool:

1. A connection object will be created when the connection is opened for the first time.

2. When the connection is closed (when the close () method is called), the current connection object is put into the pool.

3. For the next connection object, if the connection string is exactly the same as the connection string of the existing connection object in the pool, the existing connection in the pool will be used instead of creating a new one.

4. only when close () is called by the object will it be put into the pool. If a connection object is still in use, create another connection object next time and no connection object exists in the pool, A new connection object will also be created.

Connection object in the pool. If not accessed for a period of time, it is automatically destroyed.

 

 

Under what circumstances do I need to disable the connection pool?

It is generally not disabled. In particular, Asp.net and other programs are frequently accessed by N users, but most users use the same connection string.

However, if an application has multiple clients, each client uses its own connection string for access.In this case, if the connection pool is used, although the speed of each open connection will be faster, but due to the "pool" problem, multiple open connection objects will be saved at the same time.

 

In addition, it should be noted that the database should not be accessed during recursion.

 

Next let's look at the login case we just made:

 

36. SQL injection attacks

How can we solve the problem of injection attacks?

The answer is the SQL statement with parameters.

 

37. SQL statements with Parameters

 

Author's recentArticleList:

C # basic tutorial (free of charge, the best gift for code lovers. Note: The authors share their carefully organized basic C # tutorials without any commercial purposes. I hope to share my experiences with more code lovers. Please give me more advice !!!)
Layer 3	Layer 3 (1)
	Layer-3 cases (and Common Errors)
	Layer-3 instances (SQL crud)
SQL database ado.net	Database Application diagram 1
	Database Application details 2
	Ado. Net (connotation efficiency problem)
Process-oriented, object-oriented, and advanced	Process-oriented, object-oriented in-depth understanding 1
	Process-oriented, object-oriented in-depth understanding 2
	Object-oriented deep understanding 3
Winform Basics	Winform Basics
	Commonly used controls in winform
Process-oriented	Comparison of three cycles
	Method (I) in C)
	Our common Array
Object-oriented	Thought Change
	C # super-class and easy-to-use classes
	The use of destructor and namespaces in C #
	C # super-class and easy-to-use strings
	How to quickly process strings in C #
	Value Type, reference type, and others
	Arraylist and hashtable
	Arraylist and hashtable
	File Management
	Polymorphism
	Section on other issues in C #
GDI +	The GDI + code I have collected over the years
	The GDI + Code 2 I collected over the years
HTML overview and CSS	HTML language that you cannot ignore
	You can't ignore HTML 2.
	HTML language 3 that you cannot ignore
	CSS basic content-special contribution to the Mid-Autumn Festival
	CSS basic content 2
Javascript Basics	Javascript basics 1
Jquery	Jquery (connotation: jquery selector)