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.
| 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(); } } } | |
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.
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.
