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. This is done through the GetSchema method of the C1SalesforceConnection class, which optionally specifies specifiesthe schema name and restriction values.

In the following code example, the ReadOperation method demonstrates schema discovery using the ADO.NET provider for Salesforce. The GetSchema method is used to retrieve information about the tables in the Salesforce database. The code iterates over the rows of the databaseTables DataTable to display the names of the tables.

The ToCSV extension method is used to convert the dtColumnsSchema DataTable to a CSV file and save it to a specified path. This method writes the DataTable's contents to a StreamWriter, ensuring that the values are properly formatted and enclosed in quotes if necessary.

The CsvHelper static class defines the ToCSV extension method, which takes a DataTable and a file path as input and writes the DataTable's contents to a CSV file.

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 data model information based on your requirements and account permissions.

C#	Copy Code
class Program { static void Main(string[] args) { ReadOperation(); } static void ReadOperation() { 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(); } } }

Copy Code

class Program
{
    static void Main(string[] args)
    {
        ReadOperation();
    }
    static void ReadOperation()
    {
        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 the 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: Only the Salesforce API version 42 or above issupported for objects. To obtain a list of supported schemas for a specific version, refer to the Salesforce documentation and adjust the API version according to your requirements.