ASP.NET 使用 ADO.NET 連接 SQL Server (Connect SQL Server With ADO.NET)

2022-08-26

筆記在簡單的資料庫連線情境，如何使用 ADO.NET 而非 EntityFramework 來進行連線。

說明

.NET Framework Providers

• .NET Framework Data Provider for SQL Server
  • 使用 System.Data.SqlClient
• .NET Framework Data Provider for OLE DB
  • 使用 System.Data.OleDb
• .NET Framework Data Provider for ODBC
  • 使用 System.Data.Odbc
• EntityClient Provider
  • 使用 System.Data.EntityClient

連線範例 (SqlDataReader)

使用 Windows 驗證來連線資料庫的連線字串

"data source=.;initial catalog=DatabaseName;integrated security=True;"

使用 SQL Login 驗證來連線資料庫的連線字串

"Persist Security Info=False;User ID=*****;Password=*****;Initial Catalog=DatabaseName;Server=."

SqlDataReader 是連線型的資料庫連線方式，會保持住連線 (Connection)，直到作業完成與宣告 Close Connection，因此會有連線數影響資料庫效能的問題。

public ActionResult Connect()
{
    Stream stream = null;
    string name = "";
    using (var connection = new SqlConnection(
        "Persist Security Info=False;User ID=*****;Password=*****;Initial Catalog=DatabaseName;Server=."
    ))
    {
        connection.Open();


        using (var command = new SqlCommand())
        {
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.Text;
            command.CommandText = @"  
SELECT TOP (1) [file_stream], name
FROM [DB5].[dbo].[FileStreamTB]
WHERE stream_id = '382BFA5D-2525-ED11'";

            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                stream = reader.GetStream(0);
                name = reader.GetString(1);
            }
        }
    }

    return File(stream, "application/octet-stream", name);
}

Insert Data with SqlCommand

conn.Open();
var c = new SqlCommand("INSERT INTO Region VALUES(50, 'New Region', '')", conn);
c.ExecuteNonQuery();
conn.Close();

連線範例 (SqlDataAdapter)

SqlDataAdpater 是離線型的資料庫連線方式，資料會載入應用程式端的記憶體後結束 Connection，因此會有資料 ACID 的問題需要處理。

Model.cs

namespace ProejctName.Models
{
    public class DeviceRecord
    {
        public DateTime InsertDateTime { get; set; }
        public string DeviceName { get; set; }
        public int Value { get; set; }
    }
}

Controller.cs

DataTable table = new DataTable();
using (var connection = new SqlConnection(
    ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString))
{

    string sqlString = @"SELECT * FROM dbo.TableName";

    connection.Open();
    SqlDataAdapter adapter = new SqlDataAdapter(sqlString, connection);

    adapter.Fill(table);
}

var result = 
  (from r in table.AsEnumerable()
    select new DeviceInfo
    {
        InsertDateTime = DateTime.Parse(r[0].ToString()),
        DeviceName = r[1].ToString(),
        Value = Int32.Parse(r[2].ToString()),
    }).ToList();

return View(result);

在資料欄位的取得上，除了使用 Index 外，也可以使用明確的資料表欄位名稱：

var result = 
  (from r in table.AsEnumerable()
    select new DeviceInfo
    {
        InsertDateTime = DateTime.Parse(r["InsertDateTime"].ToString()),
        DeviceName = r["DeviceName"].ToString(),
        Value = Int32.Parse(r["Value"].ToString()),
    }).ToList();

如果要搭配 WHERE 條件進行參數化查詢，可以使用 Parameters 以及 AddWithValue 避免 SQL Injection：

var command = conn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = "SELECT TOP(5) * FROM Region WHERE RegionID = @id";

command.Parameters.AddWithValue("@id", id);

Insert Data with SqlDataAdapter

使用 SqlDataAdapter 進行資料 Insert 需藉由 Adapter 的 Insert Command 來進行，並且搭配 SqlCommandBuilder 根據提供 Adapter 的 SELECT Query 可以自動對應產生出 INSERT, UPDATE 及 DELETE 的 Command，若 SELECT 的欄位不包含要 INSERT 或 UPDATE 的欄位，則不會被接受。

DataTable table = new DataTable();
var connetionString = 
  ConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;

using (var conn = new SqlConnection(connetionString))
{
    var command = conn.CreateCommand();
    command.CommandType = CommandType.Text;
    command.CommandText = "SELECT * FROM Region";
    
    SqlDataAdapter adapter = new SqlDataAdapter(command);
    adapter.Fill(table);

    SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
    adapter.InsertCommand = adapter.InsertCommand = builder.GetInsertCommand();

    DataRow row = table.NewRow();
    row["RegionID"] = table.Rows.Count + 1;
    row["RegionDescription"] = "New Region";
    
    table.Rows.Add(row);
    adapter.Update(table);
    table.AcceptChanges();
}

參考資料

Connection String Syntax | learn.microsoft

相關連結

ADO.NET 學習指南