绑定完请刷新页面
取消
刷新

分享好友

×
取消 复制
ADO.net DataTable 和Amazon SimpleDB的相互转换
2022-04-12 16:13:52

Amazon SimpleDB是灵活的,高可用性的基于云的数据库。他和我们常见的SQL Server,Oracle等等不同,是非关系型的,也就是每一行不一定有同样的列数。另外还有个特点就是他把所有的值都按照字符串来保存,并且在查询时也是按照字典来排序的,所以在保存数字,时间和负数时,好先处理一下,AWS SDK中提供了对应的Encode和decode的工具。这篇文章介绍了如何把一个DataTable上传到SimpleDB,并且把SimpleDB下载到一个DataTable中。由于SimpleDB只按字符串保存,但为了再还原为DataTable时还能保存原先的数据类型,我的方法是把数据类型放在attribute的Name里,数据类型和数据字段名用冒号分开,比如 System.Int32:ID。 下面就是相互转换的代码。

 

Amazon SimpleDB is a highly available and flexible cloud based database. It is different with common used database like SQL Server or MySQL, it is a non-relational data store, the data can be organized into domains and can run queries across all of the data stored in a particular domain. Domains are collections of items that are described by attribute-value pairs. But we already have many data in relational database, how to move the data up to cloud?  I created a simple tool, to upload DataTable of ADO.net to Amazon SimpleDB, and vise versa, that means to retrieve data from SimpleDB and save into a DataTable.

Since Amazon SimpleDB is a schema-less data store and everything is stored as a UTF-8 string value, so I need a way to maintain the schema(data type) of DataTable so that it can be used when creating DataTable from SimpleDB. My way is to save the data type information into the attribute name, with a colon(:) as separator between data type and field name.

This is a test DataTable:

Here is a screen-shot when I querying the corresponding SimpleDB with AWS Explorer of Visual Studio, which is part of AWS SDK for .net.

For the common SimpleDB domain like below:

I need to convert it to a DataTable as below, since there is no data type information in this domain, I will leave the data type as string in DataTable:

In SimpleDB, everything is stored as a UTF-8 string value, all comparisons are performed lexicographically. As a result, we need to use negative number offsets, zero padding, and store dates in an appropriate format.

Here is  the code:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using Amazon;

using Amazon.SimpleDB;

using Amazon.SimpleDB.Model;

using Amazon.SimpleDB.Util;

 

namespace DataTable2SimpleDb

{

  /*

   * Sample useage:

   *

   * DataTableSimpleDbConverter dtSdbConverter = new DataTableSimpleDbConverter();

   *   

   * //DataTable dt = CreateTestDataTable();

   * //Upload DataTable to SimpleDB

   * dtSdbConverter.Datatable2SimpleDb(dt, "cloudTable");

   *

   * //CreateTestSimpleDB();

   * //Retrive data from SimpleDB, save into DataTable

   * DataTable dt2 = dtSdbConverter.SimpleDb2Datatable("MyStore");

   *

  */

 

  public class DataTableSimpleDbConverter

  {

    private const int MAX_NUM_DIGITS = 10;

    private const int OFFSET_VALUE = int.MaxValue;

 

    private const string EMPTY_VALUE_PLACEHOLDER = "";

    private static AmazonSimpleDB sdb;

 

    public DataTableSimpleDbConverter()

    {

      if (sdb == null)

      {

        sdb = AWSClientFactory.CreateAmazonSimpleDBClient(

          "<your AWSAccessKey>",

          "<your AWSSecretKey>");

      }

 

    }

    public void Datatable2SimpleDb(DataTable dt, string domainName = "")

    {

      // if domainName is omitted, use the datatable name as domain name

      if (string.IsNullOrEmpty(domainName))

      {

        domainName = dt.TableName;

      }

 

      CreateDomian(domainName);

 

      int rowCount = 0;

      foreach (DataRow row in dt.Rows)

      {

        string itemName = string.Format("Item{0}", rowCount++);

        PutAttributesRequest putAttrReq = new PutAttributesRequest()

          .WithDomainName(domainName).WithItemName(itemName);

 

        foreach (DataColumn col in dt.Columns)

        {

          string fieldName = col.ColumnName;

          //add data type, example: System.Int32:ID

          fieldName = string.Format("{0}:{1}",col.DataType.ToString(), fieldName);

 

          object value = row[col];

 

          //value is save as string in SimpleDB,

          //encoding/padding needed for number, datatime

          string stringValue = string.Empty;

          if (col.DataType == System.Type.GetType("System.Int32")

            || col.DataType == System.Type.GetType("System.Int16")

            || col.DataType == System.Type.GetType("System.Int64"))

          {

            stringValue = AmazonSimpleDBUtil.EncodeZeroPadding(

              Convert.ToInt32(value),

              MAX_NUM_DIGITS);

          }

          else if (col.DataType == System.Type.GetType("System.Decimal")

            || col.DataType == System.Type.GetType("System.Double")

            || col.DataType == System.Type.GetType("System.Single"))

          {

 

            stringValue = AmazonSimpleDBUtil.EncodeRealNumberRange(

              Convert.ToSingle(value),

              MAX_NUM_DIGITS,

              MAX_NUM_DIGITS,

              OFFSET_VALUE);

          }

          else if (col.DataType == System.Type.GetType("System.DateTime"))

          {

            stringValue = AmazonSimpleDBUtil.EncodeDate((DateTime)value);

          }

          else

          {

            stringValue = value.ToString();

          }

 

          List<ReplaceableAttribute> attributes = putAttrReq.Attribute;

          attributes.Add(new ReplaceableAttribute()

            .WithName(fieldName).WithValue(stringValue));

 

        }

        sdb.PutAttributes(putAttrReq);

      }

    }

 

    public DataTable SimpleDb2Datatable(string domainName)

    {

      if (!DomainExist(domainName))

      {

        return null;

      }

 

      string selectExp = string.Format("select * from {0}", domainName);

      SelectRequest selectReq = new SelectRequest()

        .WithSelectExpression(selectExp);

      SelectResponse selectResp = sdb.Select(selectReq);

 

      if (!selectResp.IsSetSelectResult())

      {

        return null// no data

      }

 

      DataTable dt = new System.Data.DataTable(domainName);

      SelectResult selectResult = selectResp.SelectResult;

 

      List<string> collomnNames = new List<string>();

 

      //build the schema of DataTable

      foreach (Item item in selectResult.Item)

      {

        //not useful for datatable

        string itemName = item.IsSetName() ? item.Name : EMPTY_VALUE_PLACEHOLDER;

 

        //Get all  the attribute name as collomn of data table

        foreach (Amazon.SimpleDB.Model.Attribute attr in item.Attribute)

        {

          if (!collomnNames.Contains(attr.Name))

          {

            collomnNames.Add(attr.Name);

            string dataType = GetDataType(attr.Name);

            string colName = GetCollomnName(attr.Name);

            DataColumn col = new DataColumn(colName, Type.GetType(dataType));

            dt.Columns.Add(col);

          }

 

        }

      }

 

      //Fill record into DataTable

      foreach(Item item in selectResult.Item)

      {

        //fill data row

        DataRow row = dt.NewRow();

        foreach (string colNameWithType in collomnNames)

        {

          string value = string.Empty;

          List<Amazon.SimpleDB.Model.Attribute> attrs =

            item.Attribute.FindAll(att => att.Name == colNameWithType);

          foreach (Amazon.SimpleDB.Model.Attribute attr in attrs)

          {

            value += (attr != null) ? attr.Value : EMPTY_VALUE_PLACEHOLDER;

            value += ",";

          }

          //remove the last ","

          if (value.Length > 1)

          {

            value = value.Substring(0, value.Length - 1);

          }

 

          //decode value

          string dataType = GetDataType(colNameWithType);

          string colName = GetCollomnName(colNameWithType);

          switch (dataType)

          {

            case "System.Int":

            case "System.Int32":

            case "System.Int64":

 

              int intVal = AmazonSimpleDBUtil.DecodeZeroPaddingInt(value);

              row[colName] = intVal;

              break;

 

            case "System.Decimal":

            case "System.Double":

            case "System.Single":

 

              float fValue = AmazonSimpleDBUtil.DecodeRealNumberRangeFloat(

                value,

                MAX_NUM_DIGITS,

                OFFSET_VALUE);

              row[colName] = fValue;

              break;

 

            case "System.DateTime":

              DateTime dtValue = AmazonSimpleDBUtil.DecodeDate(value);

              row[colName] = dtValue;

              break;

 

            default:

              row[colName] = value;

              break;

          }

        }

        dt.Rows.Add(row);

      }

 

      return dt;

    }

 

    private static string GetCollomnName(string colNameWithType)

    {

      //no data type in collumn name

      if (!colNameWithType.Contains(":")) return colNameWithType;

 

      try

      {

        //valid data type in collumn name

        string type = colNameWithType.Split(':')[0];

        Type.GetType(type);

        return colNameWithType.Split(':')[1];

      }

      catch

      {

        //invlide data type, perhapes this simpleDB domain

        //is not uploaded from DataTable

        return colNameWithType;

 

      }

 

 

    }

 

    private static string GetDataType(string colNameWithType)

    {

      string defaultDataType = "System.String";

 

      if (!colNameWithType.Contains(":")) return defaultDataType;

      try

      {

        string type = colNameWithType.Split(':')[0];

        Type.GetType(type);

        return type;

      }

      catch

      {

        //invlide data type, perhapes this simpleDB domain is not uploaded from DataTable

        return defaultDataType;

 

      }

 

 

    }

 

 

    private void CreateDomian(string domainName)

    {

      CreateDomainRequest createDomainRequest = new CreateDomainRequest()

        .WithDomainName(domainName);

 

      if (!DomainExist(domainName))

      {

        sdb.CreateDomain(createDomainRequest);

      }

 

 

    }

 

    private bool DomainExist(string domainName)

    {

      ListDomainsRequest listDomainRequest = new ListDomainsRequest();

      ListDomainsResponse sdblistDomainResponse = sdb.ListDomains(listDomainRequest);

      if (sdblistDomainResponse.IsSetListDomainsResult())

      {

        ListDomainsResult listDomainResult = sdblistDomainResponse.ListDomainsResult;

 

        foreach (string domain in listDomainResult.DomainName)

        {

          if (domain == domainName)

          {

            return true;

          }

        }

      }

 

      return false;

 

    }

 

  }

}

 

Hope this helps.

分享好友

分享这个小栈给你的朋友们,一起进步吧。

Amazon SimpleDB
创建时间:2022-04-12 16:12:56
Amazon SimpleDB
展开
订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅:虚拟交易,一经交易不退款;若特殊情况,可3日内客服咨询

• 专区发布评论属默认订阅所评论专区(除付费小栈外)

技术专家

查看更多
  • LCR_
    专家
戳我,来吐槽~