The DateReader class for the SQL Server database to read data and its related classes, serverdatereader

The DateReader class for the SQL Server database to read data and its related classes, serverdatereader

I learned SQL Server for a few days before, and now I am using C # code to connect to the database.

 

You need to use the C # code to connect to the database, read data, make some modifications, and write them into the database.

The classes involved include:

Class Name	Function	Remarks
ConfigurationManage	Used to read database information in the configuration file	Did not expect remarks
SqlConnection	Used to connect to the corresponding database	Open before use, Close as much as possible after use
SqlCommand	Stores the SQL statement to be executed and executes the SQL statement to obtain the SQLDataReader instance or an int value. (This value is the number of affected rows)	Assign a value to CommandText before each execution, That is, the SQL statement to be executed
SqlDataReader	Reading data table tuples from one row	When reading tuples, you can only read from the first tuples. Read one tuples at a time.

Statement for creating a database and a table:

Create database student; use student; create table student (sname nvarchar (20) not null, sno int not null, sage smallint not null, ssex char (2) not null ); alter table student add constraint PK_sno primary key (sno), constraint CK_sage check (sage between 8 and 40), constraint CK_ssex check (ssex = 'male' or ssex = 'female ') insert into student values ('zhang san', 100, 23, 'male'); insert into student values ('Li si', 101, 24, 'male '); insert into student values ('wang wu', 102, 25, 'male'); insert into student values ('zhao liu', 103, 26, 'male '); select * from student;

In the app. config Configuration file: (remember to add a reference to the reference to add a namespace, System. Configuration)

<? Xml version = "1.0" encoding = "UTF-8"?> <Configuration> <startup> <supportedRuntime version = "v4.0" sku = ". NETFramework, Version = v4.5.2 "/> </startup> <connectionStrings> <add name =" constr "connectionString =" Data Source = ANAN \ SQLEXPRESS; Initial Catalog = student; integrated Security = True; "providerName =" System. data. sqlClient "/> <! -- <Add name = "TestConnectionString" this is the key used to obtain database information in the C # code. The following are some information about the database: connectionString = "Data Source = your database instance name; Initial Catalog = database name; (this is not the table name, remember) User ID = sa; password = your own Password "providerName =" System. data. sqlClient "can also be logged on without a password account, or by using a Windows user/> --> <! -- Integrated Security = True; this is to connect to the database server through Windows authentication. The advantage of this method is that you do not need to write the user name and password in the connection string, which improves security to a certain extent. --> <! -- ProviderName = "System. data. sqlClient "indicates that MSSQLServer database providerName =" System. data. sqlLite "indicates that SQLLite database providerName =" System. data. oracleClient "indicates that Oracle Database providerName =" System. data. oracle. dataAccess. client "Same as above, Oracle Database providerName =" System. data. oleDb "indicates the Access database --> </connectionStrings> </configuration>

The method for connecting to the database is to print the data and do not perform any operations:

 

Private static void SqlConnPrint () {StringBuilder sb = new StringBuilder ("Name \ t student ID \ t age \ t gender \ n"); // Step 1: read the configuration file string str = ConfigurationManager. connectionStrings ["constr"]. toString (); // string str = @ "Data Source = ANAN \ SQLEXPRESS; Initial Catalog = student; Persist Security Info = True;"; // Step 2: create database connection object conn using (SqlConnection conn = new SqlConnection (str) {// Step 3: Open Database conn. open (); // Step 4: Create the cmd object using (SqlCommand cmd = conn. createCommand () {// Step 5: Build the SQL statement cmd to be executed. commandText = "select * from student"; // Step 6: Execute the SQL statement and obtain the reader object using (SqlDataReader reader = cmd. executeReader () {// Step 7: Read data while (reader. read () {sb. append (reader. getString (reader. getOrdinal ("sname") // The first way to obtain the column information (know the column name ). append ("\ t "). append (reader ["sno"]) // method 2 (know the column name ). append ("\ t "). append (reader [2]) // method 3 (the position of the column, that is, the column number ). append ("\ t "). append (reader. getString (reader. getOrdinal ("ssex "))). append ("\ n");} Console. writeLine (sb );}}}}

Printed result:

For more information about DataSet, see the next blog.