Schema Details

In This Topic

The ADO.NET provider for Google Analytics supports schema discovery using ADO.NET classes or SQL statements to the system tables. This is done through the GetSchema method of the C1GoogleAnalyticsConnection 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 Google Analytics. 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 }). The method also fetches foreign key information using con.GetSchema("foreignkeys", new string[] { tableName }) and populates the "References" column in the dtColumnsSchema DataTable. Additionally, the ToCSV extension method is provided to write the DataTable 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 { const string KeyFile = "*******"; const string ViewId = "******"; static void Main(string[] args) { ReadOperation(); } static void ReadOperation() { string gaConnection = string.Format("Key File={0};View Id={1}", KeyFile, ViewId); using (var con = new C1GoogleAnalyticsConnection(gaConnection)) { 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"; //Populate data for column References DataTable dtForignKeys = con.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"]}"; } //Add the path to the folder where the table files will be saved dtColumnsSchema.ToCSV($@"E:\temp\GoogleAnalytics\{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 KeyFile = "*********";
    const string ViewId = "********";

    static void Main(string[] args)
    {
        ReadOperation();
    }
    static void ReadOperation()
    {
        string gaConnection = string.Format("Key File={0};View Id={1}", KeyFile, ViewId);

        using (var con = new C1GoogleAnalyticsConnection(gaConnection))
        {
            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";

                //Populate data for column References
                DataTable dtForignKeys = con.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"]}";
                }
                //Add the path to the folder where the table files will be saved
                dtColumnsSchema.ToCSV($@"E:\temp\GoogleAnalytics\{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();
        }
    }
}

Alternatively to the GetSchema method, the GetSchemaTable method of the DbDataReader class can be used, which returns a DataTable that defines the column metadata.

Static Schema Details

The ADO.NET provider models the data in Google Analytics into a list of tables that can be queried using standard SQL statements. Following is the list of ADO.NET Provider for Google Analytics Tables.

Table Name	Description
Accounts	Create, update, and query Google Analytics Accounts.
Goals	A Goals resource that describes a goal for a user's profile.
Profiles	All the profiles to which the user has access.
Segments	All the segments to which the user has access.
Web Properties	All the Web Properties to which the user has access.

The ADO.NET provider models the Google Analytics entities in views as well. These Views are provided based on the Google Analytics reports. They comprise columns and pseudo columns and do not support updates. Here is the list of ADO.NET Provider for Google Analytics Views.

View Name	Description
AdSense	The view that retrieves the AdSense data.
AdWords	The view that retrieves the Adwords data.
Ecommerce	The view that retrieves the Ecommerce data.
Events	The view that retrieves data of the Event.
GoalCompletions	The view that retrieves data of Goal Completion.
SiteContent	The view that retrieves the internal site content data.
SiteSearch	The view that retrieves the internal site search data.
SiteSpeed	The view that retrieves internal site speed data.
Traffic	The view that retrieves all the traffic data.