Schema Details

In This Topic

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 specifies 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#	Copy Code
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) { //Display table names string tableName = row["TableName"].ToString(); Console.WriteLine(tableName);//Table Name //Get column names in a table DataTable dtColumnsSchema = con.GetSchema("Columns", new string[] { tableName }); //Remove column DbType dtColumnsSchema.Columns.Remove("DataType"); //Add column References var fkCol = dtColumnsSchema.Columns.Add(); fkCol.ColumnName = "References"; //Add the path to the folder where the table files will be saved dtColumnsSchema.ToCSV($@"{tableName}.csv"); } } } } static class CsvHelper { public static void ToCSV(this DataTable dtDataTable, string strFilePath) { using (StreamWriter sw = new StreamWriter(strFilePath, false)) { //headers 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(); } } }

Copy Code

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)
              {
                  //Display table names
                  string tableName = row["TableName"].ToString();
                  Console.WriteLine(tableName);//Table Name
  
                  //Get column names in a table
                  DataTable dtColumnsSchema = con.GetSchema("Columns", new string[] { tableName });
  
                  //Remove column DbType
                  dtColumnsSchema.Columns.Remove("DataType");
  
                  //Add column References
                  var fkCol = dtColumnsSchema.Columns.Add();
                  fkCol.ColumnName = "References";
  
                  //Add the path to the folder where the table files will be saved
                  dtColumnsSchema.ToCSV($@"{tableName}.csv");
              }
          }
      }
}

static class CsvHelper
{
    public static void ToCSV(this DataTable dtDataTable, string strFilePath)
    {
        using (StreamWriter sw = new StreamWriter(strFilePath, false))
        {
            //headers   
            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 is 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 Shipments to which the user has access.
Creditmemo	Loads a specified credit memo.
Transactions	Lists transactions that match specified search criteria.
Categories	Retrieve a list of categories.
Customers	Retrieve customers who match specified criteria.
CustomerGroups	Retrieve customer groups to which the user has access.
CustomerDefaultGroups	Get the default customer group.
BundleProductOption	Get an option for bundle products.
BundleProductChildren	Get all children for Bundle products.
SalesRule	Retrieve sales rules that match specified criteria.
ProductAttributes	Retrieve all attributes for the entity type.

The tables below can be accessed as a Guest. No authentication is required.

Table Name	Description
GuestCartsItems	List items that are assigned to a specified cart.
GuestCarts	Enable a guest user to return information for a specified cart.
ProductsRenderInfo	Collect and retrieve the list of product render info. This info contains raw prices and formatted prices, product name, stock status, store_id, etc.
ApplePay	Returns details required to be able to submit a payment with ApplePay.
Currency	Get currency information for the store.
Countries	Get all countries and regions information for the store.