Table of contents

String class and StringBuilder

string class

StringBuilder class

Common properties of StringBuilder class

Common methods of Stringbuilder class

DataReader object

Main properties and main methods of the DataReader object

Query data using the DataReader object

DataAdapter objects and DataSet s

3.1 Data Adapter DataAdapter

3.2 Temporary data warehouse DataSet

String class and StringBuilder

string class

String name = "Amy";
name += "zhang"；
Console.WriteLine(name);

StringBuilder class

The stringBuilder class represents a dynamic string, which is similar to the string buffer of the String class

StringBuilder object name = new StringBuilder([val]);

Where val is an optional parameter, indicating the initial value of the string, and can also be assigned using the methods of the Stringbuilder class

Common properties of StringBuilder class

Attributes	illustrate
Capacity	Gets or sets the maximum number of characters contained in the memory allocated by the current object
Length	Get or set the length of the current object

Common methods of Stringbuilder class

method	illustrate
StringBuilder Append(String values)	Append a string to the current string
Stringbuilder AppendFormat(string format,object arg(),object argl)	Append a string of a specific format
StringBuilder Insert(int index,string value)	Inserts a string at the specified position in the current string
StringBuilder Replace(string oldValue,string new value)	In the current string, replace all characters in another string with a certain character
String Tostring()	Returns the string currently cast to a String object

Use the Stringbuilder class to transform the code, the code is as follows

StringBuilder sb = new StringBuilder("Amy");
Sb.Append("zhang");
Console.WriteLine(sb.ToString());

Run the code and output "Amy,zhang".

DataReader object

The DataReader object can read a read-only, forward-only data stream from the data source, extracting only one record from the data source at a time

.NET data provider and corresponding DataReader class

.NET Data Provider	DataReader class	command space
SQL Server Data Provider	SqlDataReader	System.Data.SqlClient
OLE DB Data Provider	OleDbDataReader	System.Data.OleDb
ODBC Data Provider	OdbcDataReader	System.Data.Odbc
Oracle Data Provider	OracleDataReader	System.Data.OracleClient

Main properties and main methods of the DataReader object

Attributes	illustrate
FiedCount	Get the number of columns in the current row
IsClosed	Determines whether the current object is closed. If the value is true, the current object is closed; otherwise, the value is false

method	illustrate
Read()	Advance to the next line of records. Returns true if a record is read; otherwise, returns false
Close()	Close the DataReader object

Query data using the DataReader object

The data in the DataReader object is obtained through the ExecuteReader() method of the Command object, so Dat..., Reader objects are generally used with Command.

Use the DataReader object to query the book information in the book information table in the BookDB database

(1) Create a Command object The code is as follows

string connString = "Data Source = ; Initial Catalog = BookDB; User ID = sa PWD = 12345";
Sqlconnection conn = new SqlConnection(connString);
conn.Open();
string sql = "select BName,BPrice,Bauthor from BookInfo";
sqlCommand comm = new sqlCommand(Sql,conn);

(2) Call the ExecuteReader() method of the Command object to get the DataReader object, the code is as follows

sqlDataReader reader = comm.ExecuteReader();

(3) Call the Read() method provided by the DataReader object to read the records in the query result set line by line. The return value of the Read() method is a boolean type. If the record is read, it returns true, otherwise, it returns false. The code is as follows

reader.Read();

(4) Read the data of the specified column in the current row. Usually, the Read() method is used in combination with the loop structure. The value of a column can be read through the square bracket class in each loop body. The square brackets can be the index of the column (starting from 0) or the column name, the code is as follows

while(reader.Read()){
//Read the name of the book
string bName = reader["BName"];
//or
String bName = reader[0];
}

(5) Call the Close() method to close the DataReader object

reader.Close();

The operation of reading the book classification table data in the BookDB database is realized according to the above steps, and the output is displayed on the console according to the format. The code is as follows

class Program
{
  static void Main(String[] args)
    {
    string connString = "Data Source = ; Initial Catalog = BookDB; User ID = sa PWD = 12345";
    Sqlconnection conn = new SqlConnection(connString);
    conn.Open();
    string sql = "select BName,BPrice,Bauthor from BookInfo";
    sqlCommand comm = new sqlCommand(Sql,conn);
    sqlDataReader reader = comm.ExecuteReader();
    StringBuilder SbOutPut = new StringBuilder();
    while(reader.Read())
    {
      string categoryCode = reader["categoryCode"].ToString();
      string categoryName = reader["categoryName"].ToString();
      SboutPut.AppendFormat("Classification number:{0}, Category Name:{1}",categoryCode,categoryName);
      Console.WriteLine(SboutPut);
      SbOutPut.Length = 0;
   }
   reader.Close();
   conn.Close();
​
}
}

As can be seen from the code, the AppendFormat() method of the StringBuilder object is called in the loop structure to specify the explicit format of categoryCode and categoryName. Similar to the Console.WriteLine(); method, the AppendFormat() method can specify two parameters, the first parameter is a string format specifier, and the second is a parameter list consisting of objects to be formatted.

In the above code, the Execute Reader() method of Command is called first to get data from the database. to the DataReader object; then use the while loop to call the Read() method to display the book information. In the actual development process, the code for operating the database and the code for processing the business are generally separated.

DataAdapter objects and DataSet s

3.1 Data Adapter DataAdapter

.NET Data Provider	DataAdapter class	Namespaces
SQL server data provider	SqlDataAdapter	System.Data.SqlClient
OLE DB Data Provider	OleDbDataAdapter	System.Data.OleDb
ODBC Data Provider	OdbcDataAdapter	System.Data.Odbc
Oracle Data Provider	OracleDataAdapter	System.Data.OracleClient

(1) Create a DataAdapter object, the code is as follows

string connString = "Data Source = ; Initial Catalog = BookDB; User ID = sa PWD = 12345";
  SqlConnection connection = new SqlConnection(connString);
  connection.Open();
  SqlDataAdapter adapter = new SqlDataAdapter(select....//calling code);

(2) Create a DataSet object, call the Fill() method provided by the DataAdapter object, and load the data from the data source into the DataSet. The code is as follows

DataSet ds = new DataSet();
adapter.Fill(ds);

3.2 Temporary data warehouse DataSet

A DataSet is an offline container of data, a warehouse for temporarily storing data, and it does not contain the concept of a database.

If you want to get the data table of the DataSet, you can get it through the Tables property.

Tables : Get the collection of data tables in the DataSet, you can get the specified data table by index (starting from 0) or data table name

1.Rows: Get the data set in the data table and get the value through the index

2.Columns: Get the data set in the data table, get the value by index or column name

Use the DataAdapter object combined with the DataSet to query the user information in the user information table in the BookDB database. The specific steps are as follows

(1) Create a Connection object and call the Open() method to open the connection, the code is as follows

string connString = "Data Source = ; Initial Catalog = BookDB; User ID = sa PWD = 12345";
  SqlConnection connection = new SqlConnection(connString);
  connection.Open();

(2) Create a DataAdapter object, the code is as follows

SqlDataAdapter adapter = new SqlDataAdapter(select....calling code, connString);

(3) Create a DataSet data set object, call the Fill() method provided by the DataAdapter object, and load the data from the data source into the DataSer. The code is as follows

DataSet ds = new DataSer();
adapter.Fill(ds);

(4) Use the properties provided by the DataSet to obtain the data table, and combine the properties of the data table to obtain the specific value, the code is as follows

foreach(DataRow dr in ds.Tables[0].Rows)
{
string userName = dr["UserName"].ToString();
string gender = dr["Gender"].ToString();
}

(5) Call the Close() method to close the Connection object connection, the code is as follows

connection.Close();

Use the DataAdapter object to query the user's name, book name and book category who have borrowed books. And call the Fill() method to fill the data into the DataSer, the code is as follows

class Program
{
  private const "string connString = "Data Source = ; Initial Catalog = BookDB; User ID = sa PWD = 123456";
  static void Main(string[] args)
  {
  SqlConnection conn = new SqlConnection(connString);
  tr{
     conn.Open();
     string sql = "select A.UserName,D.CategoryName,C.BName from UserInfo A inner join BorrowInfo B on A.UserId = B.UserId inner join BookInfo C on B.BId = C.BId inner join BookCategory D on C.CId = D.CategoryCode ";
     DataSet ds = new DataSet();
     SqlDataAdapter adapter = new SqlDataAdapter(sql,conn);
     adapter.Fill(ds);
     StringBuilder sbOutPut = new StringBuilder();
     foreach(DataRow dr in ds.Tables[0].Rows)
     {
     SbOutPut.AppendFormat("username:{0},Category Name:{1}，book title:{2}",
            dr["UserName"].ToString(),dr["CategoryName"].ToString(),
            dr[BName].ToString());
            Console.WriteLine(SbOutPut);
            SbOutPut.Length = 0;
     
     }
  }
  catch(Exception){
  Console.WriteLine("An exception occurs!");
  }
  finally
  {
  //close database connection
  conn.Close();
  }
  }
}