スキーマの詳細

The ADO.NET provider for Magento supports schema discovery using ADO.NET classes or SQL statements to the system tables. This is done through the GetSchema method of the C1MagentoConnection class, which optionally specify the schema name and restriction values.

In the following code example, the ReadOperation method demonstrates schema discovery using the ADO.NET provider for Magento. The GetSchema method is used to retrieve information about tables and columns in the database. The code retrieves the list of tables in the database by calling con.GetSchema("Tables") and then iterates over the rows to display the table names. It further retrieves column names in each table by calling con.GetSchema("Columns", new string[] { tableName }).

Additionally, the ToCSV extension method is provided to write the DataTable to a CSV file.

C#	コードのコピー
class Program { const string Url = @"http://*.."; const string Username = @""; const string Password = @""; const string TokenType = @"***"; static string MagentoConnectionString = $@”Url={Url};UserName={Username};Password={Password};Token Type={TokenType}“; static void Main(string[] args) { ReadOperation(); } static void ReadOperation() { using (var con = new C1MagentoConnection(MagentoConnectionString)) { con.Open(); DataTable databaseTables = con.GetSchema("Tables"); Console.WriteLine("List of Tables in database: \\n"); foreach (DataRow row in databaseTables.Rows) { //テーブル名を表示します。 string tableName = row["TableName"].ToString(); Console.WriteLine(tableName);//Table Name //テーブル内の列名を取得します。 DataTable dtColumnsSchema = con.GetSchema("Columns", new string[] { tableName }); //列 DbType を削除します。 dtColumnsSchema.Columns.Remove("DataType"); //列参照を追加します。 var fkCol = dtColumnsSchema.Columns.Add(); fkCol.ColumnName = "References"; //テーブルファイルが保存されるフォルダーへのパスを追加します。 dtColumnsSchema.ToCSV($@"{tableName}.csv"); } } } } static class CsvHelper { public static void ToCSV(this DataTable dtDataTable, string strFilePath) { using (StreamWriter sw = new StreamWriter(strFilePath, false)) { //ヘッダ for (int i = 0; i < dtDataTable.Columns.Count; i++) { sw.Write(dtDataTable.Columns[i]); if (i < dtDataTable.Columns.Count - 1) { sw.Write(","); } } sw.Write(sw.NewLine); foreach (DataRow dr in dtDataTable.Rows) { for (int i = 0; i < dtDataTable.Columns.Count; i++) { if (!Convert.IsDBNull(dr[i])) { string value = dr[i].ToString(); if (value.Contains(',')) { value = String.Format("\"{0}\"", value); sw.Write(value); } else { sw.Write(dr[i].ToString()); } } if (i < dtDataTable.Columns.Count - 1) { sw.Write(","); } } sw.Write(sw.NewLine); } sw.Close(); } } }

コードのコピー

class Program
{    
    const string Url = @"http://***.***.***";
    const string Username = @"****";
    const string Password = @"****";
    const string TokenType = @"*****";
    
    static string MagentoConnectionString = $@”Url={Url};UserName={Username};Password={Password};Token Type={TokenType}“;
    
    static void Main(string[] args)
    {
        ReadOperation();
    }
    static void ReadOperation()
    {
          using (var con = new C1MagentoConnection(MagentoConnectionString))
          {
              con.Open();
  
              DataTable databaseTables = con.GetSchema("Tables");
              Console.WriteLine("List of Tables in database: \\n");
              foreach (DataRow row in databaseTables.Rows)
              {
                  //テーブル名を表示します。
                  string tableName = row["TableName"].ToString();
                  Console.WriteLine(tableName);//Table Name
  
                  //テーブル内の列名を取得します。
                  DataTable dtColumnsSchema = con.GetSchema("Columns", new string[] { tableName });
  
                  //列 DbType を削除します。
                  dtColumnsSchema.Columns.Remove("DataType");
  
                  //列参照を追加します。
                  var fkCol = dtColumnsSchema.Columns.Add();
                  fkCol.ColumnName = "References";
  
                  //テーブルファイルが保存されるフォルダーへのパスを追加します。
                  dtColumnsSchema.ToCSV($@"{tableName}.csv");
              }
          }
      }
}

static class CsvHelper
{
    public static void ToCSV(this DataTable dtDataTable, string strFilePath)
    {
        using (StreamWriter sw = new StreamWriter(strFilePath, false))
        {
            //ヘッダ   
            for (int i = 0; i < dtDataTable.Columns.Count; i++)
            {
                sw.Write(dtDataTable.Columns[i]);
                if (i < dtDataTable.Columns.Count - 1)
                {
                    sw.Write(",");
                }
            }
            sw.Write(sw.NewLine);

            foreach (DataRow dr in dtDataTable.Rows)
            {
                for (int i = 0; i < dtDataTable.Columns.Count; i++)
                {
                    if (!Convert.IsDBNull(dr[i]))
                    {
                        string value = dr[i].ToString();
                        if (value.Contains(','))
                        {
                            value = String.Format("\"{0}\"", value);
                            sw.Write(value);
                        }
                        else
                        {
                            sw.Write(dr[i].ToString());
                        }
                    }
                    if (i < dtDataTable.Columns.Count - 1)
                    {
                        sw.Write(",");
                    }
                }
                sw.Write(sw.NewLine);
            }
            sw.Close();
        }
    }
}

Static Schema Details

The ADO.NET provider models the data in Magento into a list of tables that can be queried using standard SQL statements. Following is the list of ADO.NET Provider for Magento Tables.

Table Name	Description
Products	This the main product table. It contains the id, sku, attribute set and a few other details.
ProductMedia	Images assigned to products.
Orders	Lists orders that match specified search criteria.
Invoice	All the invoices to which the user has access.
Shipments	All the Shipment to which the user has access
Creditmemo	Loads a specified credit memo.
Transactions	Lists transactions that match specified search criteria.
Categories	Retrieve list of categories
Customers	Retrieve customers which match a specified criteria.
CustomerGroups	Retrieve customer groups to which the user has access.
CustomerDefaultGroups	Get default customer group.
BundleProductOption	Get option for bundle product.
BundleProductChildren	Get all children for Bundle product.
SalesRule	Retrieve sales rules that match te specified criteria.
ProductAttributes	Retrieve all attributes for entity type.