| 
  • If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

  • You already know Dokkio is an AI-powered assistant to organize & manage your digital files & messages. Very soon, Dokkio will support Outlook as well as One Drive. Check it out today!

View
 

Lecture - Loading Data into DynamoDB

Page history last edited by Dr. Ron Eaglin 8 years, 9 months ago

Loading Data into DynamoDB

 

Summary of Video

 

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

 

Prerequisites

 

Topic - NOSQL using DynamoDB

 

Video Link

 

http://online1.daytonastate.edu/player2.php?id=2ef248c04979d6b3a7b283ec3dc32cca 

 

 

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

Comments (0)

You don't have permission to comment on this page.