C# 資料庫操作三大經典用法總結

近期頻繁與資料庫打交道，需使用 C# 搭配 SQL Server 2005 進行操作，特將常用操作整理總結。筆者首次使用 C# 操作資料庫，這三種典型用法對初學者頗有幫助。

以下是在 Visual Studio 2005 撰寫的類別（連接 SQL Server 2005），已測試通過。裡面有三個典型方法，原始碼如下：

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DatabaseOperate
{
    class SqlOperateInfo
    {
        // 假設 ServerName="aa", DatabaseName="bb", UserName="cc", Password="dd"
        private string sqlConnectionCommand = "Data Source=aa;Initial Catalog=bb;User ID=cc;Pwd=dd";
        // 此表包含兩欄：KeywordID int not null, KeyWordName varchar(100) not null
        private string dataTableName = "Basic_KeyWord_Test";
        private string storedProcedureName = "Sp_InsertToBasic_KeyWord_Test";
        private string sqlSelectCommand = "Select KeywordID, KeywordName From Basic_KeyWord_Test";
        // sqlUpdateCommand 可包含 insert、delete、update 操作
        private string sqlUpdateCommand = "Delete From Basic_Keyword_Test Where KeyWordID = 1";

        // 使用 SqlDataReader 讀取資料
        public void UseSqlReader()
        {
            SqlConnection sqlConnection = new SqlConnection(sqlConnectionCommand);
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandType = System.Data.CommandType.Text;
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandText = sqlSelectCommand;
            sqlConnection.Open();
            SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
            while (sqlDataReader.Read())
            {
                int keyWordId = (int)sqlDataReader[0];
                string keyWordName = (string)sqlDataReader[1];
                Console.WriteLine("KeywordID = " + keyWordId + " , KeywordName = " + keyWordName);
            }
            sqlDataReader.Close();
            sqlCommand.Dispose();
            sqlConnection.Close();
        }

        // 使用預存程序
        public void UseSqlStoredProcedure()
        {
            SqlConnection sqlConnection = new SqlConnection(sqlConnectionCommand);
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandText = storedProcedureName;
            sqlConnection.Open();
            sqlCommand.ExecuteNonQuery();
            sqlCommand.Dispose();
            sqlConnection.Close();
        }

        // 使用 DataSet 進行斷線式操作
        public void UseSqlDataSet()
        {
            SqlConnection sqlConnection = new SqlConnection(sqlConnectionCommand);
            SqlCommand sqlCommand = new SqlCommand();
            sqlCommand.CommandType = System.Data.CommandType.Text;
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandText = sqlSelectCommand;
            sqlConnection.Open();
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            sqlDataAdapter.SelectCommand = sqlCommand;
            DataSet dataSet = new DataSet();
            SqlCommandBuilder sqlCommandBuilder = new SqlCommandBuilder(sqlDataAdapter);
            sqlDataAdapter.Fill(dataSet, dataTableName);
            // 對 DataSet 操作後可更新回資料庫，這裡僅新增一列示範
            DataRow row = dataSet.Tables[0].NewRow();
            row[0] = 10000;
            row[1] = "new row";
            dataSet.Tables[0].Rows.Add(row);
            sqlDataAdapter.Update(dataSet, dataTableName);
            sqlCommand.Dispose();
            sqlDataAdapter.Dispose();
            sqlConnection.Close();
        }
    }
}

上述程式概括了最典型、最基本的用法。更多用法將陸續補充，歡迎來信 (jiangbiao0827@163.com) 或留言交流。