スキーマの詳細

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

C#	コードのコピー
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) { //テーブル名を表示します。 string tableName = row["TableName"].ToString(); Console.WriteLine(tableName);//Table Name //テーブル内の列名を取得します。 DataTable dtColumnsSchema = conn.GetSchema("Columns", new string[] { tableName }); //列 DbType を削除します。 dtColumnsSchema.Columns.Remove("DbType"); //列参照を追加します。 var fkCol = dtColumnsSchema.Columns.Add(); fkCol.ColumnName = "References"; //列参照のデータを入力します。 DataTable dtForignKeys = conn.GetSchema("foreignkeys", new string[] { tableName }); foreach (DataRow fkRow in dtForignKeys.Rows) { //外部キーである列の名前。 var fkColName = fkRow["ColumnName"]; // dtColumnsSchema で対応するデータ行を検索します。 var dtRow = dtColumnsSchema.Rows.Cast<DataRow>().Where(r => r["ColumnName"].ToString().Equals(fkColName)).Select(r => r).First(); //値を埋めます。 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)) { //ヘッダ 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
{
    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)
            {
                //テーブル名を表示します。
                string tableName = row["TableName"].ToString();
                Console.WriteLine(tableName);//Table Name

                //テーブル内の列名を取得します。
                DataTable dtColumnsSchema = conn.GetSchema("Columns", new string[] { tableName });

                //列 DbType を削除します。
                dtColumnsSchema.Columns.Remove("DbType");

                //列参照を追加します。
                var fkCol = dtColumnsSchema.Columns.Add();
                fkCol.ColumnName = "References";

                //列参照のデータを入力します。
                DataTable dtForignKeys = conn.GetSchema("foreignkeys", new string[] { tableName });

                foreach (DataRow fkRow in dtForignKeys.Rows)
                {
                    //外部キーである列の名前。
                    var fkColName = fkRow["ColumnName"];

                    // dtColumnsSchema で対応するデータ行を検索します。
                    var dtRow = dtColumnsSchema.Rows.Cast<DataRow>().Where(r => r["ColumnName"].ToString().Equals(fkColName)).Select(r => r).First();

                    //値を埋めます。
                    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))
        {
            //ヘッダ   
            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: Only the Salesforce API version 42 or above are supported 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.