Loading Data into DynamoDB

Summary of Video

A method that copies data from a SQL Server table to a DyanmoDB table.

Prerequisites

Video Link

Support Materials

(note – the AmazonDB reference is now version v2, so use using Amazon.DynamoDBv2 as reference)

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using Amazon;
using Amazon.DynamoDB;
using Amazon.DynamoDB.DocumentModel;
using Amazon.DynamoDB.DataModel;
using Amazon.SecurityToken;
using Amazon.Runtime;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
// Add using statements to access AWS SDK for .NET services.
// Both the Service and its Model namespace need to be added
// in order to gain access to a service. For example, to access
// the EC2 service, add:
// using Amazon.EC2;
// using Amazon.EC2.Model;
namespace AWS_App1
{
class Program
{
private static AmazonDynamoDBClient client;
private const int MAXROWS = 100;
public static void Main(string[] args)
{
try
{
AmazonDynamoDBConfig config = new AmazonDynamoDBConfig();
config.ServiceURL = “http://dynamodb.us-west-2.amazonaws.com”;

client = new AmazonDynamoDBClient(config);

//UploadData();
TransferData(“rawData”, “SERIALNO”, “HousingData”, “id”);
// Upload data (using the .NET SDK helper API to upload data)
Console.WriteLine(“Data uploaded… To continue, press Enter”);

}
catch (AmazonDynamoDBException e) { Console.WriteLine(“DynamoDB Message:” + e.Message); }
catch (AmazonServiceException e) { Console.WriteLine(“Service Exception:” + e.Message); }
catch (Exception e) { Console.WriteLine(“General Exception:” + e.Message); }
Console.ReadLine();
}
private static void TransferData(string SourceTable, string SourcePK, string DestinationTable, string DestinationPK)
{
// These open the local SQL Server
Console.WriteLine(“Reading Source Data”);
SqlConnection conn = new SqlConnection(@”Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=FloridaHousingDB;Data Source=A30609\EAGLIN”);
SqlDataAdapter adapter = new SqlDataAdapter(“SELECT TOP 100 * FROM ” + SourceTable, conn);
// Puts the Data into a DataSet
DataSet ds = new DataSet();
adapter.Fill(ds);
// Get Table from SQL Server DataSet
DataTable dt = ds.Tables[0];
Console.WriteLine(“Opening Desitnation table at AWS”);
// Get handle to DynamoDB table
Table destTable = Table.LoadTable(client, DestinationTable);
int r = 0; // Row Number
// Loop for each row in the table
foreach (DataRow dr in dt.Rows)
{
Console.WriteLine(“Starting Insert Row: ” + r.ToString());
var doc = new Document();
doc[DestinationPK] = Convert.ToString(dr[SourcePK]);
int c = 0; // Column Number
// Loop for each column in the row
foreach (DataColumn dc in dt.Columns)
{
// check not null and not Primary Key
if ((dt.Rows[r][c] != null) && (dc.ColumnName != SourcePK))
{
Console.WriteLine(dc.ColumnName + ” ” + Convert.ToString(dt.Rows[r][c]));
doc[dc.ColumnName] = Convert.ToString(dt.Rows[r][c]);
}
c++; // increment column
}
Console.WriteLine(“Completed Insert Row: ” + r.ToString());
destTable.PutItem(doc);
r++; // increment row

}
}
private static void UploadData()
{
Table sampleTable = Table.LoadTable(client, “SampleData”);
var d1 = new Document();
d1[“id”] = “1”;
d1[“Field1”] = “A field”;
d1[“Field2”] = “Another Field”;
sampleTable.PutItem(d1);
var d2 = new Document();
d2[“id”] = “2”;
d2[“Field1”] = “A field 2”;
d2[“Field2”] = “Another Field 2”;
sampleTable.PutItem(d2);
}
}
}

All Materials Copyright 2012 Dr. Ron Eaglin
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using Amazon;
using Amazon.DynamoDB;
using Amazon.DynamoDB.DocumentModel;
using Amazon.DynamoDB.DataModel;
using Amazon.SecurityToken;
using Amazon.Runtime;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
// Add using statements to access AWS SDK for .NET services.
// Both the Service and its Model namespace need to be added
// in order to gain access to a service. For example, to access
// the EC2 service, add:
// using Amazon.EC2;
// using Amazon.EC2.Model;
namespace AWS_App1
{
class Program
{
private static AmazonDynamoDBClient client;
private const int MAXROWS = 100;
public static void Main(string[] args)
{
try
{
AmazonDynamoDBConfig config = new AmazonDynamoDBConfig();
config.ServiceURL = “http://dynamodb.us-west-2.amazonaws.com”;

client = new AmazonDynamoDBClient(config);