About ADO. NET and ADO. NET

About ADO. NET and ADO. NET
What is ADO. NET?

Although we all know that ADO. NET is a database operation, it is not easy to tell the specific meaning of ADO. NET.
ADO. NET is short for ActiveX Data Objects. It is a COM component library used to access Data in microsoft technology. The name of ADO. NET should be Microsoft's own advertisement, hoping to give priority to this data access interface in the NET programming environment. The above paragraph basically comes from Baidu encyclopedia. To put it simply, ADO. NET is a data access interface that allows us to call the corresponding class library in the program to the database (usually SQL Server, or access and other databases) add, delete, modify, and query operations.

Components of ADO. NET

ADO. NET consists of five types of libraries:

Generally, the method to access the database from a program is:

The following explains the process in combination with the five major libraries of ADO. NET.

To use ADO. NET, you must reference System. Data. SqlClient in the program. This includes the data handler class for SQL Server operations:

• SqlConnection: connect to the database
• SqlCommand: Database Name object
• SqlCommandBuilder: Generate SQL commands
• SqlDataReader: data reader
• SqlDataAdapter: data adapter for filling DataSet
• SqlParameter: defines parameters for Stored Procedures
• SqlTransaction: Database Transaction

Establish a connection

First, to access the database, we need a media to connect the program to the database. This is the connection string. Its basic syntax is: Data Source (Data Source) + Initial Catalog (Database Name) + User ID (username) + Password (Password ).

String connectString = "Data Source = myServerAddress;Initial Catalog = myDataBase;User Id = myUserName; Password = myPassword;";

Or

String connectString = "Server =myServerAddress;Database = myDataBase; User Id = myUsername; Password = myPassword;";

 

Note: For SQL Server, it supports two authentication methods: windows Authentication and SQL Server Authentication. To use windows authentication, you must include the Integrated Security attribute in the connection string. The default value of this attribute is False. You must set it to True to use windows authentication.
In addition to the required fields, there are many optional attributes in the connection string. Here I will not list them one by one. I will list some relevant information for interested friends to check, what properties a connection string can contain (http://book.51cto.com/art/200812/99995.htm ).

Then, with the connection string, you can create a connection object.

SqlConnection connection = new SqlConnection(connecString); 

Alternatively, you can use a dedicated connection string generator:

SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder() { DataSource=”“, InitialCatalog=”“, UserID=”“, Password=”” }; SqlConnection connection = new SqlConnection(connectionStringBuilder.ToString());

 

Then, you can use the connection object to open or close the connection.
Connection. Open ();
Connection. Close ();

Execute Command

After the connection is opened, you can operate the database. here you need to use the SqlCommand object.
It has four main attributes, which will be assigned the default value during initialization:
CommandText: empty string ("")
CommandTimeout: 30
CommandType: CommandType. Text
Connection: Null

CREATE command object:
SqlCommand command = connection. CreateCommand ();
Or
SqlCommand command = new SqlCommand ();

SqlCommand contains several important attributes:
CommandText: An SQL statement, representation, or stored procedure used to obtain or set the row of the drug to the data source.
CommandType: Set the SQL statement type you run. There are three enumerations: Text (SQL Text command), StoredProcedure (stored procedure), and TableDirect (table name ).
Parameters: set the Parameters you need in your T-SQL.

Several important methods:
ExecuteNonQuery: returns the number of rows affected by SQL statement execution (int). It mainly performs addition, deletion, and modification operations.
ExecuteReader: executes SQL statements or stored procedures, and returns the SqlDataReader type, mainly used for query.
ExecuteScalar: returns the first column of the First row in the execution result set. If no data exists, NULL is returned.
CreateParameter: Create a SqlParameter instance.
Example:

Using System; using System. collections. generic; using System. linq; using System. text; using System. data; // required using System. data. sqlClient; // namespace Command {class Program {static void Main (string [] args) {SqlConnectionStringBuilder conStr = new SqlConnectionStringBuilder (); conSt. dataSource = @". \ SQLEXPRESS "; conStr. integratedSecurity = true; conStr. initialCatalog = "db_Test"; StringBuilder strSQL = new StringBuilder (); for (int I = 0; I <= 100; I ++) {strSQL. append ("insert into tb_Test"); strSQL. append ("values ('"); string name = "test" + I. toString (); strSQL. append (name);} using (SqlConnection con = new SqlConnection (conStr. connectionString) {con. open (); SqlCommand cmd = new SqlCommand (strSQL. toString (), con); int impactedNumber = cmd. executeNonQuery (); // returns the number of affected rows. object firstData = cmd. executeScalar (); // return the first column of the First row in the execution result. This method can be used to obtain the ID of the inserted data, (int lineNumber = (int) cmd. executeScalar ();)}}}}

SQL Parameters

If you want to pass parameters to the database in a program, you can use SqlParameter. This class has several important attributes:

• ParameterName: Set the parameter name.
• Value: Set the parameter Value.
• Size: sets the maximum length of the Parameter bytes.

• SqlDbType: Type of a parameter in SQL

  And several important methods:

• AddWithVlue

• Add

• AddRange

  Example:

SqlConnection connection = new SqlConnection ("") {SqlCommand cmd = connection. createCommand (); cmd. commandText = ""; cmd. parameters. add ("@ name", SqlDbType. NVarChar ). value = "deng"; // method 1 cmd. parameters. addWithValue (@ "name", "deng"); // method 2 SqlParameter [] parameters = new SqlParameter [] {new SqlParameter ("@ name", SqlDbType. nvarChar, 100) {Value = "deng"},}; cmd. parameters. addRange (parameters); // you can put an array of parameters that contain multiple parameters. Here, only one example is provided}

You can use cmd. Parameters [I]. Value to set and read values.

Data Reading

The data information obtained using the query statement needs to be operated through the data reader.
Example:

SqlConnetion con = new SqlConnection(""){  con.Open();  SqlCommand cmd = con.CreateCommand();  cmd.CommandText="";  SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)  {    While(dr.Read())    {     string str = dr.GetSqlString(0).ToString();    }  }}

Several common methods are introduced:

Common attributes include:

SqlDataReader is related to the connection. That is to say, the data in the database cannot be read once the connection to the database is disconnected. It means that the query results are not stored in the program, but in the database service.

Transactions

The SqlTransaction class is required. You must name the storage point in the specified location. All operations after the storage point will be rolled back.
Example:

SqlConnection con = new SqlConnection(strCon);con.Open();SqlTransaction transaction = con.BeginTransaction();SqlCommand cmd = con.CreateCommand();cmd.CommandText = ""cmd.Transaction = transaction;transaction.Save("transaction point");transaction.Rollback("transaction point");

Data adapter

The SqlDataAdapter class has four overload constructors:

• No Parameter
• SqlDataAdapter (SqlCommand)
• SqlDataAdapter (String, SqlConnection)
• SqlDataAdapter (String, ConnectionString)

Example of filling data:

DataSet dataSet = new DataSet();SqlConnection con = new SqlConnection("");con.Open();SqlCommand cmd = con.CreateCommand();cmd.CommandText="select xxx from tb_xxx";SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);dataAdapter.Fill(dataSet);

You can use SqlCommandBuilder to add, delete, modify, and query the filled data.

Example:

SqlConnection con = new SqlConnection(ConnectionString();con.Open();SqlDataAdapter da = new SqlDataAdapter("select xxx from tb_xx");DataSet ds =new DataSet();da.Fill(ds);SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(da);DataRow row = ds.Tables[0].NewRow();row[0]="a";row[1]="b";ds.Tables[0].Rows.Add(row);da.Update(ds);

SqlCommandBuilder can convert the data added to DataSet into SQL statements for updating the database. Then, call the Update method.

Summary

Because the frameworks or libraries used now have encapsulated ADO.. NET. in daily work, you may rarely write ADO from the beginning.. NET to connect to the database, but in. net interviews are still very common. I hope you can deepen your impression. In addition, I currently have a silver fern visa. I can apply for a job in New Zealand for 9 months. If I find a professional job, I can transfer a two-year work visa to facilitate immigration, 300 working holiday visas are issued globally each year. Although there are no working holiday visas, the annual competition is fierce. If you need to know more about the problem, you can leave a message for me. My first blog is a collection of materials. It is not entirely original. If any of the reasons is incorrect, I hope you can correct me more.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.