Table of contents
String class and StringBuilder
Common properties of StringBuilder class
Common methods of Stringbuilder class
Main properties and main methods of the DataReader object
Query data using the DataReader object
DataAdapter objects and DataSet s
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(); } } }