s21_05.cpp

本书分为五个部分

// 这是使用应用程序向导生成的 VC++ 
// 应用程序项目的主项目文件。

#include "stdafx.h"

#using <mscorlib.dll>
#include <tchar.h>

// 显示引用命名空间
#using <System.dll>
#using <System.Data.dll>
#using <System.Xml.dll>
using namespace System;
using namespace System::Data;
using namespace System::Data::SqlClient;
using namespace System::Xml;

// 显示当前DataSet对象中的数据
void DisplayDataSet(DataSet __gc* catDS)
{
    // 获取DataSet中表的字段（列）名称
    Console::WriteLine( S"{0}\t{1}", 
        catDS->Tables->get_Item(S"Categories")->
        Columns->get_Item(0)->ColumnName,
        catDS->Tables->get_Item(S"Categories")->
        Columns->get_Item(1)->ColumnName );
    // 遍历表的字段值
    DataRow __gc* catRow;
    for( int i=0; 
        i<catDS->Tables->get_Item(S"Categories")->Rows->Count; i++ )
    {
        catRow = catDS->Tables->get_Item("Categories")->Rows->get_Item(i);
        Console::WriteLine(S"{0}\t{1}", 
            catRow->get_Item(0), catRow->get_Item(1));
    }
}

// 这是此应用程序的入口点
int _tmain(void)
{
    // 创建一个Connection对象
    SqlConnection __gc* nwindConn = new SqlConnection(
        S"Data Source=localhost;" \
        S"Initial Catalog=northwind;" \
        S"Integrated Security=SSPI;");

    // 创建DataAdapter对象
    SqlDataAdapter __gc* catDA = new SqlDataAdapter();

    // -- 设置SelectCommand属性 --
    SqlCommand __gc* selectCMD = new SqlCommand( 
        S"SELECT CategoryID, CategoryName FROM Categories", 
        nwindConn);
    catDA->SelectCommand = selectCMD;
    Console::WriteLine("\nCreating SelectCommand...OK! ");

    // 打开连接对象，以建立到数据源的连接
    nwindConn->Open();
    // 创建DataSet对象，并使用DataAdapter填充
    DataSet __gc* catDS = new DataSet();
    catDA->Fill(catDS, S"Categories");
    // 显示DataSet中的数据
    ::DisplayDataSet(catDS);

    // -- 设置InsertCommand属性 --
    SqlCommand __gc* InsertCmd = new SqlCommand( 
        S"InsertCategory", nwindConn);
    InsertCmd->CommandType = CommandType::StoredProcedure;
    // 设置参数
    InsertCmd->Parameters->Add(S"@CategoryName", 
        SqlDbType::NVarChar, 15, S"CategoryName");
    // 设置标识输出参数
    SqlParameter __gc* insParm = new SqlParameter( 
        S"@Identity", SqlDbType::Int, 4, S"CategoryID");
    insParm->Direction = ParameterDirection::Output;
    InsertCmd->Parameters->Add(insParm);
    // 设置InsertCommand属性
    catDA->InsertCommand = InsertCmd;
    Console::WriteLine(S"\nCreating InsertCommand...OK! ");

    // 创建一个新行（记录）
    DataRow __gc* newRow = 
        catDS->Tables->get_Item(S"Categories")->NewRow();
    newRow->set_Item(S"CategoryName", S"Mojiarou"); // 膜夹肉
    // 添加前的记录状态
    Console::WriteLine(S"New row CREATED using NewRow(), RowState is: {0}",  
        __box(newRow->RowState)->ToString());
    //__box DataRowState* State = __box(newRow->get_RowState());
    // __box关键字是对__value类型进行“装箱”。
    // 这将使任何 __value 类对象可以在适用于任何托管对象的一般例程中使用，
    // 因为托管对象是间接从 System::Object 继承的（由于 
    // System::ValueType 是从 System::Object 继承的）。
    catDS->Tables->get_Item(S"Categories")->Rows->Add(newRow);
    // 添加后的记录状态
    Console::WriteLine(S"New row ADDED to table, RowState is: {0}", 
        __box(newRow->RowState)->ToString() );

    catDA->Update(catDS, S"Categories");
    // 清除DataSet对象的数据
    catDS->Clear();
    // 重新填充DataSet
    catDA->Fill(catDS, "Categories");
    ::DisplayDataSet(catDS);


    // -- 设置UpdateCommand属性 --
    SqlCommand __gc* updateCmd = new SqlCommand( 
        S"UPDATE Categories SET CategoryName = @CategoryName "\
        S"WHERE CategoryID = @CategoryID", nwindConn);
    updateCmd->Parameters->Add(S"@CategoryName", 
        SqlDbType::NVarChar, 15, S"CategoryName");
    // 设置参数
    SqlParameter __gc* workParm = 
        updateCmd->Parameters->Add(S"@CategoryID", SqlDbType::Int);
    workParm->SourceColumn = S"CategoryID";
    workParm->SourceVersion = DataRowVersion::Original;
    // 设置DataAdapter对象的UpdateCommand属性
    catDA->UpdateCommand = updateCmd;
    Console::WriteLine(S"\nCreating UpdateCommand...OK! ");

    // 修改符合条件的记录(也就是刚刚添加的记录)
    int i;
    String* oldName;
    DataRow __gc* catRow;
    for( i=0; 
        i<catDS->Tables->get_Item(S"Categories")->Rows->Count;
        i++ )
    {
        catRow = catDS->Tables->get_Item(S"Categories")->Rows->get_Item(i);
        oldName = catRow->get_Item(S"CategoryName")->ToString()->Trim();
        if( oldName->Equals(S"Mojiarou") )
        {
            catRow->set_Item(S"CategoryName", S"Roujiamo"); // 肉夹膜
        }
    }
    // 更新数据源
    catDA->Update(catDS, S"Categories");
    catDS->Clear();
    catDA->Fill(catDS, S"Categories");
    ::DisplayDataSet(catDS);


    // -- 设置DeleteCommand属性 -- 
    SqlCommand __gc* deleteCmd = new SqlCommand( 
        S"DELETE Categories WHERE CategoryID = @CategoryID", 
        nwindConn);
    // 设置命令参数
    SqlParameter __gc* delParm = new SqlParameter(S"@CategoryID", 
        SqlDbType::Int, 4);
    delParm->SourceColumn = S"CategoryID";
    delParm->SourceVersion = DataRowVersion::Current;
    deleteCmd->Parameters->Add(delParm);
    // 设置DeleteCommand属性
    catDA->DeleteCommand = deleteCmd;
    Console::WriteLine("\nCreating DeleteCommand...OK! ");
    // 删除新添加的记录
    newRow->Delete();
    
    catDA->Update(catDS, S"Categories");
    catDS->Clear();
    catDA->Fill(catDS, S"Categories");
    ::DisplayDataSet(catDS);


    nwindConn->Close();

    return 0;
}