DataConnector | ComponentOne
ADO.NET provider for Salesforce / Schema Details
In This Topic
    Schema Details
    In This Topic

    The ADO.NET provider for Salesforce supports schema discovery using ADO.NET classes or SQL statements to the system tables. The ADO.NET classes enable access to schema information of database, connection property and columns returned.

    GetSchema method of C1SalesforceConnection class is used to retrieve schema of the Database and DataTables. 

    In the below code example GetSchema method is called which returns Tables in the Database. In the second call, the method returns the columns in a specific data table. In addition, the code also returns the references for a table.

    Note: The data model information used in the following code is fetched from our account based on the available permissions. Similarly, you can write the code to get your own data model information based on your requirements and account permissions.
    C#
    Copy Code
    class ProgramNew
    {
        const string GCSalesforceServerConnectionString = @"Username=*********;Password=********;
                                                            Security Token=*******;
                                                            OAuth Client Id=*********; 
                                                            OAuth Client Secret=***********; 
                                                            OAuth Token Endpoint=https://ap16.salesforce.com/services/oauth2/token; 
                                                            Url=https://ap16.salesforce.com/services/data/v45.0";
    
        static void Main(string[] args)
        {
            LoadSchemaTable("Order");
        }
    
        static void LoadSchemaTable(string tableName)
        {
            Console.WriteLine($"LoadSchemaTable '{tableName}' started !!!");
            using (C1SalesforceConnection conn = new C1SalesforceConnection(GCSalesforceServerConnectionString))
            {
                conn.Open();
                DataTable databaseTables = conn.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 = conn.GetSchema("Columns", new string[] { tableName });
    
                    //Remove column DbType
                    dtColumnsSchema.Columns.Remove("DbType");
    
                    //Add column References
                    var fkCol = dtColumnsSchema.Columns.Add();
                    fkCol.ColumnName = "References";
    
                    //Populate data for column References
                    DataTable dtForignKeys = conn.GetSchema("foreignkeys", new string[] { tableName });
    
                    foreach (DataRow fkRow in dtForignKeys.Rows)
                    {
                        //Name of the column that is a foreign key
                        var fkColName = fkRow["ColumnName"];
    
                        //Find corresponding data row in dtColumnsSchema
                        var dtRow = dtColumnsSchema.Rows.Cast<DataRow>().Where(r => r["ColumnName"].ToString().Equals(fkColName)).Select(r => r).First();
    
                        //Fill value
                        dtRow["References"] = $"{fkRow["ForeignKeyTableName"]}.{fkRow["ForeignKeyColumnName"]}";
                    }
                    dtColumnsSchema.ToCSV($@"E:\temp\Salesforce\{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();
            }
        }
    }
    

    Similar to the GetSchema method, you can also use GetSchemaTable method of the C1SalesforceDataReader class. The GetSchemaTable method returns a DataTable that defines the column metadata.

    Static Schema Details

    The ADO.NET provider models the data in Salesforce into a list of tables that can be queried using standard SQL statements. We support various ADO.NET Providers for Salesforce Tables for which you can refer to the Salesforce documentation.

    Note that we only support the Salesforce API version 42 or above for objects. For this, you can always refer to the Salesforce documentation and change the API version based on your requirements for getting a list of supported schema for that version.