IDbTableDataManager.GetData Method

IIS 7.0

Returns table data as a query result.

Namespace:  Microsoft.Web.Management.DatabaseManager
Assembly:  Microsoft.Web.Management.DatabaseManager (in Microsoft.Web.Management.DatabaseManager.dll)

QueryResult GetData(
	string connectionString,
	string tableName,
	string schema
)

Parameters

connectionString
Type: System.String
The connection string for the database.
tableName
Type: System.String
The name of the table.
schema
Type: System.String
The schema name for the table.
Note   If schema is empty, the default schema name will be used.

Return Value

Type: Microsoft.Web.Management.DatabaseManager.QueryResult
A QueryResult object that contains the table data.

All database providers that implement the IDbTableDataManager interface must also implement the GetData method. The database manager will use this method to retrieve the data for a table in a database.

Notes for Implementers

If your provider does not support retrieving data, you can use the following code sample to raise a not-implemented exception:

public QueryResult GetData(string connectionString, string tableName, string schema)

{

   throw new NotImplementedException();

}

NoteNote:

See the SELECT Clause (Transact-SQL) topic for more information about the SELECT SQL statement.

The following code sample implements the GetData method to retrieve the data in a database table from an OLEDB data source.

NoteNote:

This example also provides helper methods that perform additional database tasks.



        // Retrieve data from a database.
        public QueryResult GetData(string connectionString, string tableName, string schema)
        {
            // Create a new query result object.
            QueryResult queryResult = new QueryResult();

            // Create a connection to the database.
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                // Open the database connection.
                connection.Open();

                List<Column> columns = new List<Column>();
                GetColumns(connection, tableName, schema, columns);
                IList<QueryColumnMetadata> metadata = new List<QueryColumnMetadata>();

                foreach (Column c in columns)
                {
                    QueryColumnMetadata m = new QueryColumnMetadata();
                    m.Name = c.Name;
                    m.ColumnWidth = c.Length;
                    m.IsNullable = c.AllowNulls;
                    m.TypeInfo = c.TypeInfo;
                    metadata.Add(m);
                }

                StringBuilder fields = new StringBuilder();

                if (metadata.Count == 0)
                {
                    // When the table is not found, adding an asterix returns an error message.
                    fields.Append("*");
                }
                else
                {
                    int fieldCount = metadata.Count;

                    bool isFirst = true;
                    for (int i = 0; i < fieldCount; i++)
                    {
                        QueryColumnMetadata m = metadata[i];
                        if (isFirst == false)
                        {
                            fields.Append(',');
                        }
                        isFirst = false;
                        fields.Append(EscapeName(m.Name));
                        queryResult.ColumnMetadata.Add(m);
                    }
                }

                // Create a new query object.
                Query query = new Query();

                // Create a SQL query statement.
                query.Statement = "SELECT " + fields.ToString() + " FROM " + EscapeName(tableName);

                // retrieve the query results.
                QueryResult[] results = ExecuteQuery(connectionString, query);

                // Add the query results to the list.
                queryResult.QueryResults.AddRange(results[0].QueryResults);
            }

            // Return the query result.
            return queryResult;
        }



...



        internal static string EscapeName(string name)
        {
            // Test if the value is already escaped.
            if (name.StartsWith("[", StringComparison.Ordinal) && name.EndsWith("]", StringComparison.Ordinal))
            {
                // Return the string with no changes.
                return name;
            }
            // Create a new string builder for the escaped string.
            StringBuilder escapedName = new StringBuilder();
            // Prepend the string with an opening bracket.
            escapedName.Append('[');
            // Loop through the string.
            for (int i = 0; i < name.Length; i++)
            {
                char c = name[i];
                if (c == ']')
                {
                    escapedName.Append(']');
                }
                escapedName.Append(c);
            }
            // Append the string with an opening bracket.
            escapedName.Append(']');
            // Return the escaped string.
            return escapedName.ToString();
        }



Show: