ADO.NET data access technology

Learning objectives

1. Master the basic methods of C# accessing database
2. Master various methods of database query
3. Focus on understanding and mastering the application of OOP principles in data access

ADO.NET component and database connection

Understand ADO NET

• ActiveX Data Objects(ADO)
• Yes NET platform, a set of object-oriented class libraries for interaction between applications and data sources
• Simple understanding: data access component

Main components

 ADO for accessing and manipulating data The two main components of. Net are NET Framework data providers and ‎DataSet‎‎. ‎

Framework.net data provider type

• . NET Framework data provider
  • SQL Server Database - > system Data. SqlClient namespace
  • Access, Excel or SQL server data source - > system Data. OLEDB namespace
  • Oracle Database - > system Data. Oracleclient namespace (reference needs to be added)
  • ODBC public database - > system Data. ODBC namespace
• Data provider provided by the third party: MySQL Net data provider

Preparation for connecting to the database

• Viewing and modifying SQL Server server ports

1. Enable TCP/IP
2. Modify port number
3. Effective after service restart

How to connect to the database correctly

• Four conditions are required
  • Server IP address
  • Database name
  • Login account
  • Login password
• Use of account
  • The sa account has all permissions to access the database and is used in the learning, development and testing stages

Connection object

• Function: establish point-to-point connection between application and database

• Property: connectionstring

  • Encapsulates four conditions for connecting to the database

  • Server = server name or IP address; DataBase = DataBase name; User ID = login account; Password = login password

  • Sample string for login using SQL Server User Authentication

    Server=192.168.1.2;DataBase=StudentManageDB;Uid=xiaoming;Pwd=password
    

  • Example of a login string using Windows integrated authentication (native only)

    Data Source=; Initial Catalog=StudentManageDB;Integrated Security=True
    

• method:

  • Open: open connection
  • Close(): close the connection

Compilation of database addition, deletion and modification methods

Command object

• Function: send SQL statements to the database
  • Encapsulates the connection object and the SQL statement to be executed
  • Perform specific operations on the database and provide the methods of "add, delete, modify and query"
• attribute
  • CommandText: name of SQL statement or stored procedure to be encapsulated
  • Connection: the database connection object used by the Command object
• method:
  • ExecuteNonQuery(): add, delete and modify
  • ExecuteScalar(): returns a single result query
  • ExecuteReader(): a query that returns a read-only data list
• Key points of using ExecuteNonQuery() method
  • Execute insert, update and delete statements
  • After execution, the number of affected rows is returned, which is generally an integer greater than 0. If it is equal to 0, it indicates that there is no impact, - 1 indicates that there is an error in execution

using System;
// Introduce namespace
using System.Data;
using System.Data.SqlClient;


namespace ADOConnectSql
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // Define connection string
            // string connStrings = "Server=.;Database=StudentsManageDB;UID=sa;PWD=sqlpassword";
            string connStrings = "Data Source=; Initial Catalog=StudentManageDB;Integrated Security=True";

            // Create connection object
            SqlConnection sqlConnection = new SqlConnection(connStrings);
            // open a connection
            try
            {
                sqlConnection.Open();
                if (ConnectionState.Open == sqlConnection.State)
                {
                    // Console.WriteLine("Connection is opened");
                    // Connect successfully, execute SQL statement
                    string sqlString = @"insert into Students(StudentName,Gender,Birthday,StudentIdNo,Age,PhoneNumber,StudentAddress,ClassId)";
                    sqlString += "values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')";
                    sqlString = string.Format(sqlString, "Zhang San", "male", "1990-09-01", 413302198730289090, 20, "12972782727", "Beijing", 1);
                    // Create Command object
                    /*SqlCommand sqlCommand = new SqlCommand();
                    sqlCommand.CommandText = sqlString;*/
                    SqlCommand sqlCommand = new SqlCommand(sqlString, sqlConnection);

                    int result = sqlCommand.ExecuteNonQuery();

                    Console.WriteLine(result);
                }
                else
                {
                    Console.WriteLine("Connection is filed");
                }
            }
            catch (Exception ex)
            {

                Console.WriteLine(ex.Message);
            }
            
            // Close connection
            sqlConnection.Close();
            if (ConnectionState.Closed == sqlConnection.State)
            {
                Console.WriteLine("Connection is Closed");
            }
            Console.ReadLine();
        }
    }
}

Modify entity

// Modify entity
string updateString = @"update Students set StudentName='{0}' where StudentId={1}";
updateString = string.Format(updateString,"Qian Qi", 100005);
result = new SqlCommand(updateString, sqlConnection).ExecuteNonQuery();

Gets the value that identifies the column

• Problem elicitation

  • Add a new student object in the Students table and return the student number of the new student
  • Tip: the student number is automatically identified, that is, after inserting a new record, return to the identification column of the record

• Problem solving:

  • Add a select @@identity query after the insert statement

  • Execute ExecuteScalar() method: execute insert and select at the same time

    insert insert into Students(StudentName,Gender,Birthday,StudentIdNo,Age,PhoneNumber,StudentAddress,ClassId)
    values('Zhang San','male','1990-01-20',123302198730289090,24,'010-1231231','Shenzhen Sea World ',1);select @@identity
    

  • explain

    • @@identity is a global variable in the database, which holds the value of the last generated identification column

  string sqlString = @"insert into Students(StudentName,Gender,Birthday,StudentIdNo,Age,PhoneNumber,StudentAddress,ClassId)";
  sqlString += "values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}');select @@identity";
  sqlString = string.Format(sqlString, "Zhao Si", "male", "1990-09-01", 012307998730289090, 20, "13972782727", "Beijing", 1);
  SqlCommand sqlCommand = new SqlCommand(sqlString, sqlConnection);
  object res = sqlCommand.ExecuteScalar();
  
  Console.WriteLine(res);
  

Data query method

Returns a single result query

• Question: how to execute the following query?

  • 1. Query the total number of all students
    2. Query the names of students with student number equal to 100004

• Use the ExecuteScalar() method

  • Single result: single row and single column
  • Return object type

• Code writing analysis: query the total number of students

  // Create sql statement
  string sql = "select count(*) from Students";
  // Create Command object, query
  object result = new SqlCommand(sql, sqlConnection).ExecuteScalar();
  Console.WriteLine(result);
  

Summary of single result query steps

1. Create Connection object
2. Combined SQL statement: select single result query
3. Create a Command object and encapsulate Connection and SQL statements
4. open a connection
5. Execute the ExecuteScalar() method to return a single result (object type)
6. Close connection

A query that returns a read-only dataset

• Basic steps for using the ExecuteReader() method

  1. Create Connection object
  2. Combine SQL statements of type Select
  3. Build Command objects and encapsulate Connection and SQL statements
  4. open a connection
  5. Execute the ExecuteReader() method and return the DataReader object
  6. Read query results row by row
  7. Close the reader
  8. Close connection

• How DataReader objects read data

• Particular attention

  • The DataReader checks the data of the object and reads it in a loop
  • The connection to the database will remain open until the read is complete
  • Before closing the connection, the reader must be closed, and both must be closed

string sql = "select StudentId,StudentName from Students;select ClassId,ClassName from StudentClass;";
SqlDataReader result = new SqlCommand(sql, sqlConnection).ExecuteReader();
// Read the first result set
while (result.Read())
{
    Console.WriteLine(result["StudentId"].ToString()+ result["StudentName"].ToString());
}
Console.WriteLine("---------------------------");
// Determine whether to the next result set
if (result.NextResult())
{
    while (result.Read())
    {
        Console.WriteLine(result["ClassId"].ToString() + result["ClassName"].ToString());

    }
}

Optimizing database access based on OOP principles

Realize code reuse

• The basic form of code reuse: writing a general method
• Application requirements of code reuse technology
  • Principle: extract the unchanged and encapsulate the changed
  • Technique: the constant is used as the "method body", and the variable is used as the parameter

Application of entity class in development

Problems and solutions of method parameters

• Disadvantages of many method parameters
  • It is inconvenient to define and use, and it is easy to write parameters wrong
  • When the properties of the object change, the parameters of the method must change
  • The change of parameters causes the instability of object interface and reduces maintainability, scalability and security
  • The method has many parameters, which does not meet the requirements of "low coupling and high cohesion" in object-oriented
  • The compilation of background methods depends on the completion of the database
  • The implementation of foreground code depends on the completion of background methods
• Problem solving ideas
  • Provide a standard and stable object interface for class design
  • Different developers only need to develop synchronously according to the standard interface
• Problem solving
  • Use "entity class" as the method parameter to stabilize the external interface

Design of entity class

• Concept and form

  • A class that contains only properties and construction methods is called an entity class
  • Entity attributes correspond to database entity attributes one by one (field name and data type are consistent)

  Entity class attribute type	Database data type
  string	char,nchar,varchar,nvarchar
  int	int,smallint
  Date Time	datetime, smalldatetime
  float	float
  bool	byte
  decimal	decimal,money

  namespace ADOConnectSql.Models
  {
     /// <summary>
     ///Student entity class
     /// </summary>
     class Student
     {
         public int StudentId { get; set; }
         public string StudentName { get; set; }
         public string Gender { get; set; }
         public DateTime Birthday { get; set; }
         public decimal StudentIdNo { get; set; }
         public string PhoneNumber { get; set; }
         public int ClassId { get; set; }
     }
  }
  
  

Data query and object encapsulation

Encapsulating and parsing the meaning of objects

• Stabilize the data access interface and clarify the responsibilities
• Foreground developers and background developers can separate and realize synchronous development
  • Data access methods only care about how to encapsulate objects, not who uses them
  • The data display part of the interface only cares about how to parse objects, not how to query and encapsulate objects
  • The respective responsibilities of the foreground and background are completely separated, which fully reflects the color idea of object-oriented development (high cohesion and low coupling)

Encapsulation of multiple objects of the same type

public static List<Student> GetAllStudents()
{
    string sqlString = "select StudentName,Gender,Birthday,StudentIdNo,Age,PhoneNumber,StudentAddress,ClassId from Students";
    // Execute query
    SqlDataReader objReader = SQLHelper.GetReaderResult(sqlString);
    List<Student> stuList = new List<Student>();
    while (objReader.Read())
    {
        // Add object to collection
        stuList.Add(new Student(){
            StudentName = objReader["StudentName"].ToString(),
            Gender = objReader["Gender"].ToString(),
            Birthday = Convert.ToDateTime(objReader["Birthday"]),
            StudentIdNo = Convert.ToDecimal(objReader["StudentIdNo"]),
            Age = Convert.ToInt16(objReader["Age"]),
            PhoneNumber = objReader["PhoneNumber"].ToString(),
            StudentAddress = objReader["StudentAddress"].ToString(),
        });
    }
    // Close the reader
    objReader.Close();
    // Returns a collection of objects
    return stuList;
}

Call data access method

// Call the data access method to get the student object
List<Student> listStudent= StudentService.GetAllStudents();
if (listStudent.Count != 0)
{
    Console.WriteLine("full name" + "\t" + "class" + "\t" + "Age" + "\t" + "birthday" + "\t" + "Telephone");
    foreach (Student item in listStudent) // Parse object
    {
        Console.WriteLine(item.StudentName + "\t" + item.ClassId + "\t" + item.Age + "\t" + item.Birthday.ToShortDateString() + "\t" + item.PhoneNumber);
    }
}
else
{
    Console.WriteLine("No data to display!");
}

Application of combined extended entity

namespace ADOConnectSql
{
    /// <summary>
    ///Composite extended entity
    /// </summary>
    public class StudentExt
    {
        public StudentExt()
        {
            objStudent = new Student();
            objScore = new ScoreList();
            objClass = new StudentClass();
        }
        public Student objStudent { get; set; }
        public StudentClass objClass { get; set; }
        public ScoreList objScore { get; set; }
    }
}	

Object oriented query summary