【C#】63. 异步操作数据库 localDB

这篇不难，都是些基础性的异步操作，所以直接上代码。

可以参考里面的语法包括：

1）找到当前的bin文件夹：

string outputFolder = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);

2）调用数据库的自带procedure（这里是detach）并且传参：

Console.WriteLine("Detaching the database...");
var detachCommand = new SqlCommand("sp_detach_db", connection);
detachCommand.CommandType = CommandType.StoredProcedure;
detachCommand.Parameters.AddWithValue("@dbname", dbName); 
await detachCommand.ExecuteNonQueryAsync();

3）异步读取查询结果：

cmd = new SqlCommand(@"SELECT * FROM [dbo].[CustomTable]", connection);
using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
{
	while (await reader.ReadAsync())
	{
		var id = reader.GetFieldValue<int>(0);
		var name = reader.GetFieldValue<string>(1);
		Console.WriteLine("Table row: Id {0}, Name {1}", id, name);
	}
}

完整代码：

using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Reflection;
using System.Threading.Tasks;

namespace Chapter9.Recipe3
{
	class Program
	{
		static void Main(string[] args)
		{
			const string dataBaseName = "CustomDatabase";
			var t = ProcessAsynchronousIO(dataBaseName);
			t.GetAwaiter().GetResult();
			Console.WriteLine("Press Enter to exit");
			Console.ReadLine();
		}

		async static Task ProcessAsynchronousIO(string dbName)
		{
			try
			{
				const string connectionString = @"Data Source=(LocalDB)\v11.0;Initial Catalog=master;Integrated Security=True";
				string outputFolder = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
				string dbFileName = Path.Combine(outputFolder, string.Format(@".\{0}.mdf", dbName));
				string dbLogFileName = Path.Combine(outputFolder, string.Format(@".\{0}_log.ldf", dbName));
				string dbConnectionString = 
					string.Format(@"Data Source=(LocalDB)\v11.0;AttachDBFileName={1};Initial Catalog={0};Integrated Security=True;", dbName, dbFileName);

                #region 创建数据库 CustomDatabase
                using (var connection = new SqlConnection(connectionString))
				{
                    //阻塞等待连接完成
					await connection.OpenAsync();

                    //如果纯在数据库，则删除他。
					if (File.Exists(dbFileName))
					{
						Console.WriteLine("Detaching the 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 was detached succesfully.");
						Console.WriteLine("Deleteing the database...");

						if(File.Exists(dbLogFileName)) File.Delete(dbLogFileName);
						File.Delete(dbFileName);

						Console.WriteLine("The database was deleted succesfully.");
					}

                    //创建数据库
					Console.WriteLine("Creating the database...");
					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("The database was created succesfully");
				}
                #endregion

                #region 建立数据表 dbo.CustomTable，并且执行 Select * from dbo.CustomTable
                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("Table was created succesfully.");

					cmd = new SqlCommand(@"INSERT INTO [dbo].[CustomTable] (Name) VALUES ('John');
INSERT INTO [dbo].[CustomTable] (Name) VALUES ('Peter');
INSERT INTO [dbo].[CustomTable] (Name) VALUES ('James');
INSERT INTO [dbo].[CustomTable] (Name) VALUES ('Eugene');", connection);
					await cmd.ExecuteNonQueryAsync();

					Console.WriteLine("Inserted data succesfully");
					Console.WriteLine("Reading data from table...");

					cmd = new SqlCommand(@"SELECT * FROM [dbo].[CustomTable]", connection);
					using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
					{
						while (await reader.ReadAsync())
						{
							var id = reader.GetFieldValue<int>(0);
							var name = reader.GetFieldValue<string>(1);

							Console.WriteLine("Table row: Id {0}, Name {1}", id, name);
						}
					}
				}

                #endregion 

            }
            catch (Exception ex)
			{
				Console.WriteLine("Error: {0}", ex.Message);
			}
		}
	}
}