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

1. 說明
   1. 連線範例 (SqlDataReader)
      1. Insert Data with SqlCommand
   2. 連線範例 (SqlDataAdapter)
      1. Insert Data with SqlDataAdapter
2. 參考資料
3. 相關連結

---

筆記在簡單的資料庫連線情境，如何使用 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 學習指南