ADO.NET 概觀

ADO.NET是微軟公司開發用來操作資料庫的函式庫,ADO.NET 可讓您以一致的方式存取資料來源 (例如 SQL Server 與 XML)。 資料共用的消費者應用程式可使用 ADO.NET 來連接至這些資料來源,並且擷取、處理及更新其中所含的資料。
•微軟在.NET Framework中負責資料存取的類別庫集
•可以讓.NET上的任何程式語言能夠連接並存取關聯式資料庫與非資料庫型資料來源
•連線資料來源以及離線資料模型兩個部份構成,這兩個部份是相輔相成的

ODBC > OLEDB > ADO > ADO.Net> Entity Framework

ODBC(Open DatabaseConnectivity)是一種標準,而標準其實是介面(ODBC驅動)
OLEDB(ObjectLinkingand Embedding,Database)能處理關聯式資料庫, 也能處理非關聯式資料(如:Excel)
ADO(ActiveX Data Objects)建立在ODBC和OLEDB之上的又一次封裝
ADO.Net引入了離線型資料模型的概念
Entity Framework將每個資料庫物件轉換成應用程式物件,資料欄位轉換為屬性

Connection(用於建立與資料庫的連線)

Command(用於執行SQL語句)

DataReader(用於讀取資料)

DataAdapter(用於填充把資料填充到DataSet)

DataSet(資料集,用於程式中)

是從資料來源中抓取之資料的記憶體內部快取,是ADO.NET 架構的主要元件
是由DataTable物件的集合所組成
使用DataAdapter,以資料來源中的資料建立和填入DataSet中的每個DataTable
Clear() (移除所有資料表中的資料列,以清除任何資料的DataSet)
Copy (複製這個DataSet的結構和資料)
GetXml() (傳回儲存於DataSet的資料之XML 表示)
Merge() (將指定的DataSet(DataTable、DataRow) 及其結構描述合併到目前的DataSet)

DataTable

可以儲存的最大資料列數目是16777216

DO.NET 查詢、新增、修改、刪除 範本

Web.config

<?xml version="1.0"?>

<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->

<configuration>

    <system.web>
      <compilation debug="true" targetFramework="4.5.2" />
      <httpRuntime targetFramework="4.5.2" />
    </system.web>

  <connectionStrings>
    <add name="TEST" connectionString="Data Source=.;Initial Catalog=TEST;Persist Security Info=True;User ID=IRSYS;Password=irsysdba" providerName="System.Data.SqlClient"/>
  </connectionStrings>
  
</configuration>

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <fieldset>
        <legend>ADO.NET連線方式</legend>
        <asp:Label ID="Label1" runat="server" Text="Label">輸入值</asp:Label>
        Title:<asp:TextBox ID="Title" runat="server"></asp:TextBox>
        Value:<asp:TextBox ID="Value" runat="server"></asp:TextBox>
        <asp:Button ID="Select_btn" runat="server" Text="查詢" OnClick="Select_btn_Click"/>
        <asp:Button ID="Inster_btn" runat="server" Text="新增" OnClick="Inster_btn_Click" />
        <asp:Button ID="Update_btn" runat="server" Text="修改" OnClick="Update_btn_Click" />
        <asp:Button ID="Delete_btn" runat="server" Text="刪除" OnClick="Delete_btn_Click" />
        <asp:Button ID="Select_All_btn" runat="server" Text="查詢全部" OnClick="Select_All_btn_Click"/>
        <asp:GridView ID="GridView1" runat="server"></asp:GridView>
    </fieldset>
    </div>
    </form>
</body>
</html>

Default.aspx.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;

public partial class _Default : System.Web.UI.Page
{


    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            string SQLString = "Select * from Test..Test1 Where 1=1 ";
            SqlConnection sqlconnection = new SqlConnection(WebConfigurationManager.ConnectionStrings["Test"].ConnectionString);
            DataSet dataset = new DataSet();
            SqlCommand sqlcommand = new SqlCommand(SQLString);

            sqlcommand.Connection = sqlconnection;
            sqlcommand.Parameters.Clear();

            using (sqlcommand.Connection)
            {
                sqlcommand.Connection.Open();
                SqlDataAdapter sqldataadapter = new SqlDataAdapter(sqlcommand);
                sqldataadapter.Fill(dataset);

            }
            GridView1.DataSource = dataset.Tables[0];
            GridView1.DataBind();

        }
        catch (Exception msg)
        {
            Console.WriteLine(msg);
        }
        finally
        {

        }
    }

    protected void Select_btn_Click(object sender, EventArgs e)
    {
        try
        {

            string SQLString = "Select * from Test..Test1 Where 1=1 and Title=@Title ";

            SqlCommand sqlcommand = new SqlCommand(SQLString);
            SqlConnection sqlconnection = new SqlConnection(WebConfigurationManager.ConnectionStrings["Test"].ConnectionString);
            DataSet dataset = new DataSet();

            sqlcommand.Connection = sqlconnection;
            sqlcommand.Parameters.Clear();
            string TitleTextTrim = Title.Text.Trim();
            sqlcommand.Parameters.Add("@Title", SqlDbType.NChar).Value = TitleTextTrim;

            using (sqlcommand.Connection)
            {
                sqlcommand.Connection.Open();
                SqlDataAdapter sqldataadapter = new SqlDataAdapter(sqlcommand);
                sqldataadapter.Fill(dataset);
            }

            GridView1.DataSource = dataset.Tables[0];
            GridView1.DataBind();
        }
        catch(Exception msg)
        {
            Console.WriteLine(msg);
        }
    }

    protected void Inster_btn_Click(object sender, EventArgs e)
    {
        try
        {
            string SQLString = "INSERT INTO dbo.Test1([Title], [Value])VALUES (@Title, @Value); ";

            SqlCommand sqlcommand = new SqlCommand(SQLString);

            SqlConnection sqlconnection = new SqlConnection(WebConfigurationManager.ConnectionStrings["Test"].ConnectionString);
            DataSet dataset = new DataSet();


            sqlcommand.Connection = sqlconnection;
            sqlcommand.CommandText = SQLString;
            sqlcommand.Parameters.Add("@Title", SqlDbType.NChar).Value = Title.Text.Trim();
            sqlcommand.Parameters.Add("@Value", SqlDbType.NChar).Value = Value.Text.Trim();

            using (sqlcommand.Connection)
            {
                sqlcommand.Connection.Open();
                sqlcommand.ExecuteNonQuery();
            }
        }
        catch (Exception msg)
        {

        }
       

    }

    protected void Update_btn_Click(object sender, EventArgs e)
    {
        try
        {
            string SQLString = "Update dbo.Test1 SET [Value] = @Value WHERE [Title] = @Title; ";

            SqlCommand sqlcommand = new SqlCommand(SQLString);

            SqlConnection sqlconnection = new SqlConnection(WebConfigurationManager.ConnectionStrings["Test"].ConnectionString);
            DataSet dataset = new DataSet();

            sqlcommand.Connection = sqlconnection;
            sqlcommand.CommandText = SQLString;
            sqlcommand.Parameters.Add("@Title", SqlDbType.NChar).Value = Title.Text.Trim();
            sqlcommand.Parameters.Add("@Value", SqlDbType.NChar).Value = Value.Text.Trim();

            using (sqlcommand.Connection)
            {
                sqlcommand.Connection.Open();
                sqlcommand.ExecuteNonQuery();
            }

        }
        catch (Exception msg)
        {

        }
    }

    protected void Delete_btn_Click(object sender, EventArgs e)
    {
        try
        {
            string SQLString = "DELETE FROM dbo.Test1 WHERE [Title] = @Title and [Value] = @Value;  ";

            SqlCommand sqlcommand = new SqlCommand(SQLString);

            SqlConnection sqlconnection = new SqlConnection(WebConfigurationManager.ConnectionStrings["Test"].ConnectionString);
            DataSet dataset = new DataSet();

            sqlcommand.Connection = sqlconnection;
            sqlcommand.CommandText = SQLString;
            sqlcommand.Parameters.Add("@Title", SqlDbType.NChar).Value = Title.Text.Trim();
            sqlcommand.Parameters.Add("@Value", SqlDbType.NChar).Value = Value.Text.Trim();

            using (sqlcommand.Connection)
            {
                sqlcommand.Connection.Open();
                sqlcommand.ExecuteNonQuery();
            }

        }
        catch(Exception msg)
        {

        }
    }

    protected void Select_All_btn_Click(object sender, EventArgs e)
    {
        try
        {
            string SQLString = "Select * from Test..Test1 Where 1=1 ";
            SqlConnection sqlconnection = new SqlConnection(WebConfigurationManager.ConnectionStrings["Test"].ConnectionString);
            DataSet dataset = new DataSet();
            SqlCommand sqlcommand = new SqlCommand(SQLString);

            sqlcommand.Connection = sqlconnection;
            sqlcommand.Parameters.Clear();

            using (sqlcommand.Connection)
            {
                sqlcommand.Connection.Open();
                SqlDataAdapter SDadapter = new SqlDataAdapter(sqlcommand);
                SDadapter.Fill(dataset);

            }
            GridView1.DataSource = dataset.Tables[0];
            GridView1.DataBind();
        }
        catch(Exception msg)
        {

        }
    }
}

資料庫 範本

範本結果

參考資料:

Microsoft ADO.NET 概觀 :https://docs.microsoft.com/zh-tw/dotnet/framework/data/adonet/ado-net-overview

發佈留言