多線程編程學習筆記——非同步操作資料庫

接上文 多線程編程學習筆記——使用非同步IO

接上文 多線程編程學習筆記——編寫一個非同步的HTTP伺服器和客戶端

三、   非同步操作資料庫

本示例演示了創建資料庫，非同步操作數據，讀取數據的過程。

1. 程式代碼如下。

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("--創建簡單非同步操作資料庫示例 -- ");
            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("分離資料庫...");

                        var detachCommand = new SqlCommand("sp_detach_db", connection);
                        detachCommand.CommandType = CommandType.StoredProcedure;                       
                        detachCommand.Parameters.AddWithValue("@dbname", dbName);
                        await detachCommand.ExecuteNonQueryAsync();
                        Console.WriteLine("分離資料庫 成功！！");
                        Console.WriteLine("刪除資料庫文件.....");
                        if (File.Exists(dbLogFileName))
                            File.Delete(dbLogFileName);
                        File.Delete(dbFileName);
                     Console.WriteLine("刪除資料庫文件成功！！"); 

                    }

                    Console.WriteLine("創建資料庫文.......");
                    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("創建資料庫成功！！");
                }
 

                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成功！");
 

                     cmd = new SqlCommand(@"INSERT INTO [dbo].[CustomTable](Name) values('John') ;
INSERT INTO [dbo].[CustomTable](Name) values('張三') ;
INSERT INTO [dbo].[CustomTable](Name) values('李四') ;
INSERT INTO [dbo].[CustomTable](Name) values('王五') ;", connection); 

                    await cmd.ExecuteNonQueryAsync();
                    Console.WriteLine("表CustomTable數據插入成功！");
                    Console.WriteLine("查詢表CustomTable數據。。。。"); 

                     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數據 : Id {0} ,Name {1}", id, name);

                        }
                    }
                }
            }
            catch(Exception ex)
            {

                Console.WriteLine("錯誤信息 :  {0} ",ex.Message); 
            }
        }
    } 

}

 

2.程式運行結果，如下。

        運行程式，如果資料庫已經存在，則刪除重建。當打開 連接以及單獨使用OpenAsync和ExecuteNonQueryAsync方法執行SQL命令時，我們使用了I/O非同步操作。

         在這個任務完成後，我們創建了一張新的表並插入了一些數據，除了之前提到的方法，我們還使用了ExceuteScalarAsync來非同步地從資料庫引擎中得到一個標量值，並且使用SqlDataReader.ReadAsync方法來從資料庫表中非同步地讀取數據行。