ADO. NET Basic Knowledge Summary

  • 2021-07-09 07:50:48
  • OfStack

Although we all know that ADO. NET is an operation on a database, it is not easy to really tell the specific meaning of ADO. NET.
ADO. NET is the abbreviation of ActiveX Data Objects, which is an COM component library for accessing data in microsoft technology. The reason why it is called ADO. NET should be advertised by Microsoft itself, hoping to give priority to using this data access interface in NET programming environment. The above passage basically comes from Baidu Encyclopedia. To put it simply, ADO. NET is a kind of data access interface, which allows us to call the corresponding class library in the program to add, delete, modify and check the database (usually SQL, Server, access and other databases).

ADO. Several major components of NET
ADO. NET consists of five major class libraries, namely:

Connection (used to establish a connection to the database)
Command (used to execute SQL statements)
DataReader (for reading data)
DataAdapter (for populating to populate data into DataSet)
DataSet (dataset, used in programs)

Generally, the method of accessing the database from the program is:

Create a connection to the database
Open a database connection
Creating an ADO Recordset
Extract the required data from the recordset
Close RecordSet
Close the connection

The following is an explanation according to this process combined with the five major class libraries of ADO. NET.

To use ADO. NET, you need to reference System. Data. SqlClient in your program. It contains the data access classes that operate on Sql Server:

SqlConnection: Connecting to a database
SqlCommand: Database named objects
SqlCommandBuilder: Generate SQL commands
SqlDataReader: Data reader
SqlDataAdapter: Data adapter for populating DataSet
SqlParameter: Defining parameters for stored procedures
SqlTransaction: Database transactions

Establish a connection

First of all, to access the database, we need a medium to connect the program with the database. This is the connection string, and its basic syntax is: Data Source (data source) + Initial Catalog (database name) + User ID (user name) + 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, one is windows authentication and the other is Sql Server authentication. If you want to use windows authentication, you need to include the Integrated Security attribute in the connection string. This property defaults to False. It needs to be set to True before windows authentication can be used.
In addition to these necessary fields, there are many optional attributes in the connection string. I will not list them here, but list some related information for interested friends to consult. What attributes can a connection string contain (https://www. ofstack. com/article/67742. htm).

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

SqlConnection connection = new SqlConnection(connecString);
Or you can use a special connection string generator:


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

You can then use the connection object to open or close the connection.

connection.Open();
connection.Close();

Execute a command

After opening the connection, you can operate the database, and you need to use the SqlCommand command object here.
It has four main attributes, which are assigned default values at initialization time:
CommandText: Empty string ("")
CommandTimeout30
CommandType: CommandType. Text
Connection: Null

To create a command object:


SqlCommand command = connection.CreateCommand(); 

Or


SqlCommand command = new SqlCommand();

SqlCommand contains several important attributes:
CommandText: An SQL statement, indication, or stored procedure used to get or set a trip to a data source.
CommandType: Set the type of SQL statement you execute. There are three enumerations, namely 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 the execution of SQL statement (int), mainly performing addition, deletion and modification operations.
ExecuteReader: Executes an SQL or stored procedure that returns an SqlDataReader type, primarily for queries.
ExecuteScalar: Returns row 1, column 1 in the execution result set, or NULL if there is no data.
CreateParameter: Create an SqlParameter instance.

For example:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;// Must 
using System.Data.SqlClient;// Must 
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 rows affected 
    object firstData = cmd.ExecuteScalar();// Returns the first in the execution result 1 Line number 1 Column , This method can be used to get the ID,(int lineNumber =(int)cmd.ExecuteScalar();)
    }
  }
 }
}

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

ParameterName: Setting parameter names
Value: Setting Values for Parameters
Size: Set the maximum length of parameter bytes
SqlDbType: Category of parameters in SQL

And several important methods:

AddWithVlue

Add
AddRange

For 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);// Can be put 1 Array of parameters, containing multiple parameters, only listed here 1 Examples 
}

Values can be set and read through cmd. Parameters [i]. Value.

Data reading

The data information obtained by using query statements needs to be operated by data readers.
Examples:


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();
  }
 }
}

Introduce several commonly used methods:

GetOrdinal: You can get the sequence number of the specified column name, int name = dr. GetOrdinal ("name");
GetName: Corresponding to the above method, you can return the column name by column number.
IsDBNull: Determines whether the currently read data is Null.
NextResult: When the query is a batch query, use this method to get the next result set, and the return value is Bool; If there are multiple result sets, it is true;; Otherwise, it is false.
Read: Read data.

Common attributes are:

HasRow: Determine if data is available.
FieldCount: Gets the number of columns read.
IsClosed: Determines whether the read data stream is closed.
SqlDataReader is connection-related, that is to say, the data in the database cannot be read if the connection 1 is disconnected from the database, which shows that the query result is not placed in the program, but in the service of the database.

Affairs

The SqlTransaction class needs to be used, and the storage point needs to be named at the specified location, and all operations after the storage point will be rolled back.
Examples:


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 overloaded constructors:

No parametric
SqlDataAdapter(SqlCommand)
SqlDataAdapter(String,SqlConnection)
SqlDataAdapter(String, ConnectionString)
Examples of populating 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);

The filled data can be added, deleted and checked by SqlCommandBuilder.

Examples:


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 by DataSet into SQL statements to update the database. Then call the Update method.

Summarize

Because the framework or library used now has encapsulated ADO. NET, it may be rare to write ADO. NET from scratch to connect to the database in daily work, but it is still very common in. net interview, hoping to deepen my impression. In addition, I currently hold a Silver Fern visa, and I can apply for a job in New Zealand for 9 months. If I find a relevant professional job during this period, I can directly transfer to a 2-year work visa, which is very convenient for immigrants. There are 300 visas issued worldwide every year. Although working holiday visas are not well known to everyone, the competition is still fierce every year. If you need to know about relevant matters, you can leave me a message. My first blog belongs to sorting out information, not completely original. If there is any incorrect aspect, I hope the great god can correct me a lot.


Related articles: