Visual C++ .NET/Database ADO.net/DataTable

Add columns to DataTable

<source lang="csharp">

1. include "stdafx.h"

using namespace System; using namespace System::Data; void main() {

   DataSet^ myDataSet = gcnew DataSet("Game Data");
   myDataSet->Tables->Add("Player");
   myDataSet->Tables->Add("Score");
   DataTable^ playerTable = myDataSet->Tables["Player"];
   DataColumn^ firstNameColumn1 = playerTable->Columns->Add("First Name",String::typeid);
   DataColumn^ lastNameColumn1 = playerTable->Columns->Add("Last Name",String::typeid);
   playerTable->Columns->Add("Email", String::typeid);

}

 </source>

Add foreign key to the DataTable

<source lang="csharp">

1. include "stdafx.h"

using namespace System; using namespace System::Data; void main() {

   DataSet^ myDataSet = gcnew DataSet("Game Data");
   myDataSet->Tables->Add("Player");
   myDataSet->Tables->Add("Score");
   DataTable^ playerTable = myDataSet->Tables["Player"];
   DataColumn^ firstNameColumn1 = playerTable->Columns->Add("First Name",String::typeid);
   DataColumn^ lastNameColumn1 = playerTable->Columns->Add("Last Name",String::typeid);
   playerTable->Columns->Add("Email", String::typeid);
   array<DataColumn^>^ primaryKey = {firstNameColumn1, lastNameColumn1};
   playerTable->Constraints->Add("PlayerPK", primaryKey, true);
   // Add columns to 2nd table
   DataTable^ ScoreTable = myDataSet->Tables["Score"];
   DataColumn^ firstNameColumn2 = ScoreTable->Columns->Add("First Name",String::typeid);
   DataColumn^ lastNameColumn2 = ScoreTable->Columns->Add("Last Name",String::typeid);
   ScoreTable->Columns->Add("Points", int::typeid);
   array<DataColumn^>^ foreignKey = {firstNameColumn2, lastNameColumn2};
   ScoreTable->Constraints->Add("PlayerFK", primaryKey, foreignKey);
   // (Same as establish a data relation)
   //myDataSet->Relations->Add("Player-Score", PrimaryKey, ForeignKey);

}

 </source>

Add primary key constraints to DataTable

<source lang="csharp">

1. include "stdafx.h"

using namespace System; using namespace System::Data; void main() {

   DataSet^ myDataSet = gcnew DataSet("Game Data");
   myDataSet->Tables->Add("Player");
   myDataSet->Tables->Add("Score");
   DataTable^ playerTable = myDataSet->Tables["Player"];
   DataColumn^ firstNameColumn1 = playerTable->Columns->Add("First Name",String::typeid);
   DataColumn^ lastNameColumn1 = playerTable->Columns->Add("Last Name",String::typeid);
   playerTable->Columns->Add("Email", String::typeid);
   array<DataColumn^>^ primaryKey = {firstNameColumn1, lastNameColumn1};
   playerTable->Constraints->Add("PlayerPK", primaryKey, true);
   // (Same as creating the composite primary key)
   //playerTable->PrimaryKey = PrimaryKey;

}

 </source>

Data Relations

<source lang="csharp">

1. include "stdafx.h"

using namespace System; using namespace System::Data; void main() {

   DataSet^ myDataSet = gcnew DataSet("Game Data");
   DataTable^ playerTable = myDataSet->Tables->Add("Player");
   DataTable^ scoreTable = myDataSet->Tables->Add("Score");
   DataColumn^ firstNameColumn1 = playerTable->Columns->Add("First Name",String::typeid);
   DataColumn^ lastNameColumn1 = playerTable->Columns->Add("Last Name",String::typeid);
   playerTable->Columns->Add("Email", String::typeid);
   array<DataColumn^>^ primaryKey = {firstNameColumn1, lastNameColumn1};
   playerTable->PrimaryKey = primaryKey;
   DataColumn^ firstNameColumn2 = scoreTable->Columns->Add("First Name",String::typeid);
   DataColumn^ lastNameColumn2 = scoreTable->Columns->Add("Last Name",String::typeid);
   scoreTable->Columns->Add("Points", int::typeid);
   array<DataColumn^>^ foreignKey = {firstNameColumn2, lastNameColumn2};
   myDataSet->Relations->Add("Player-Score", primaryKey, foreignKey);
   myDataSet->Tables["Score"]->Constraints->Add("Unique",foreignKey, true);

}

 </source>

Table Mapping

<source lang="csharp">

1. include "stdafx.h"

using namespace System; using namespace System::Data; using namespace System::Data::Common; using namespace System::Data::OleDb; using namespace System::Data::SqlClient; void main() {

   OleDbConnection^ myConnection = nullptr;
   myConnection = gcnew OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Music.mdb");
   myConnection->Open();
   OleDbDataAdapter^ dataAdapter = gcnew OleDbDataAdapter("SELECT * FROM Music", myConnection);
   DataSet^ myDataSet = gcnew DataSet();
   DataTable^ newMusicTable = myDataSet->Tables->Add("New Music");
   newMusicTable->Columns->Add("Title");
   newMusicTable->Columns->Add("Duration");
   DataTableMapping^ myMapping = dataAdapter->TableMappings->Add("Music", "New Music");
   myMapping->ColumnMappings->Add("Title", "Title");
   myMapping->ColumnMappings->Add("Duration", "Duration");
   dataAdapter->MissingSchemaAction = MissingSchemaAction::Ignore;
   dataAdapter->Fill(myDataSet, "Music");
   for ( int i = 0; i < newMusicTable->Rows->Count; i++ ){
       Console::WriteLine(newMusicTable->Rows[i]["Title"]);
       Console::WriteLine(newMusicTable->Rows[i]["Duration"]);
   }
   myConnection->Close();

}

 </source>