Multithread programming learning notes-asynchronous database operations and multithread programming learning notes

Multithread programming learning notes-asynchronous database operations and multithread programming learning notes

Multi-thread programming learning notes-use Asynchronous IO

Multi-thread programming learning notes-write an asynchronous HTTP server and client

 

Iii. asynchronous database operations

This example demonstrates how to create a database, asynchronously operate data, and read data.

1. The program code is as follows.

Using System; using System. collections. generic; using System. data; using System. data. sqlClient; using System. IO; using System. linq; using System. net; using System. net. http; using System. reflection; using System. text; using System. threading. tasks; namespace ThreadIODemo {class Program {static void Main (string [] args) {Console. writeLine ("-- create a simple asynchronous database operation example --"); const string dbName = "CustomDB"; var t = GetDBAsync (dbName); t. getAwaiter (). getResult (); Console. read ();} static async Task GetDBAsync (string dbName) {try {const string connectionString = @ "Data Source =. \ SQLEXPRESS;
Initial Catalog = master; Integrated Security = SSPI "; string outFolder = Path. getDirectoryName (Assembly. getExecutingAssembly (). location); string dbFileName = Path. combine (outFolder, string. format (@ "{0 }. mdf ", dbName); string dbLogFileName = Path. combine (outFolder, string. format (@ "{0} _ log. ldf ", dbName); string dbConnectionString = string. format (@ "Data Source =. \ SQLEXPRESS; AttachDBFileName = {1 };
Initial Catalog = {0}; Integrated Security = SSPI ", dbName, dbFileName); using (var connection = new SqlConnection (connectionString) {await connection. openAsync (); if (File. exists (dbFileName) {Console. writeLine ("detaching a database... "); var detachCommand = new SqlCommand (" sp_detach_db ", connection); detachCommand. commandType = CommandType. storedProcedure;
DetachCommand. Parameters. AddWithValue ("@ dbname", dbName); await detachCommand. ExecuteNonQueryAsync (); Console. WriteLine ("the database has been detached !! "); Console. writeLine ("deleting database files ..... "); if (File. exists (dbLogFileName) File. delete (dbLogFileName); File. delete (dbFileName); Console. writeLine ("the database file is deleted successfully !! ");} Console. WriteLine (" Create a Database file... "); string createCommand = String. Format (" Create Database {0} on
(Name = n' {0} ', FILENAME =' {1} ') ", dbName, dbFileName); var cmd = new SqlCommand (createCommand, connection); await cmd. executeNonQueryAsync (); Console. writeLine ("database created successfully !! ");} Using (var connection = new SqlConnection (dbConnectionString) {await connection. openAsync (); var cmd = new SqlCommand ("select newid ()", connection); var result = await cmd. executeScalarAsync (); Console. writeLine ("New GUID from database: {0}", result); cmd = new SqlCommand (@ "Create Table [dbo]. [CustomTable] (
[ID] [int] IDENTITY (1, 1) not null, [Name] [nvarchar] (50) not null,
CONSTRAINT [PK_ID] primary key clustered ([ID] ASC) ON [PRIMARY]) ON [PRIMARY] ", connection); await cmd. executeNonQueryAsync (); Console. writeLine ("CustomTable table created successfully! "); Cmd = new SqlCommand (@" insert into [dbo]. [CustomTable] (Name) values ('john ');
Insert into [dbo]. [CustomTable] (Name) values ('zhang san ');
Insert into [dbo]. [CustomTable] (Name) values ('Li si ');
Insert into [dbo]. [CustomTable] (Name) values ('wang wu'); ", connection); await cmd. executeNonQueryAsync (); Console. writeLine ("Table mtmtable data inserted successfully! "); Console. WriteLine (" querying CustomTable data .... "); Cmd = new SqlCommand (@" select * from [dbo]. [CustomTable] ", connection); using (SqlDataReader dr = await cmd. executeReaderAsync () {while (await dr. readAsync () {var id = dr. getFieldValue <int> (0); var name = dr. getFieldValue <string> (1); Console. writeLine ("CustomTable data: Id {0}, Name {1}", id, name) ;}}} catch (Exception ex) {Console. writeLine ("error message: {0}", ex. message );}}}}

2. The program running result is as follows.

 

 

Run the program. If the database already exists, delete and recreate the program. I/O asynchronous operations are used when you open the connection and run the SQL command using the OpenAsync and ExecuteNonQueryAsync methods separately.

After this task is completed, we have created a new table and inserted some data. In addition to the method mentioned above, we also use ExceuteScalarAsync to asynchronously obtain a scalar value from the database engine and use SqlDataReader. the ReadAsync method is used to asynchronously read data rows from database tables.