IDbTableDataManager.GetData Method
Returns table data as a query result.
Namespace: Microsoft.Web.Management.DatabaseManager
Assembly: Microsoft.Web.Management.DatabaseManager (in Microsoft.Web.Management.DatabaseManager.dll)
Syntax
'Declaration
Function GetData ( _
connectionString As String, _
tableName As String, _
schema As String _
) As QueryResult
'Usage
Dim instance As IDbTableDataManager
Dim connectionString As String
Dim tableName As String
Dim schema As String
Dim returnValue As QueryResult
returnValue = instance.GetData(connectionString, _
tableName, schema)
QueryResult GetData(
string connectionString,
string tableName,
string schema
)
QueryResult^ GetData(
String^ connectionString,
String^ tableName,
String^ schema
)
function GetData(
connectionString : String,
tableName : String,
schema : String
) : QueryResult
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.
Remarks
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();
}
Note
See the SELECT Clause (Transact-SQL) topic for more information about the SELECT SQL statement.
Examples
The following code sample implements the GetData method to retrieve the data in a database table from an OLEDB data source.
Note
This example also provides helper methods that perform additional database tasks.
' Retrieve data from a database.
Public Function GetData( _
ByVal connectionString As String, _
ByVal tableName As String, _
ByVal schema As String) _
As Microsoft.Web.Management.DatabaseManager.QueryResult _
Implements Microsoft.Web.Management.DatabaseManager.IDbTableDataManager.GetData
' Create a new query result object.
Dim queryResult As QueryResult = New QueryResult
' Create a connection to the database.
Dim connection As OleDbConnection = New OleDbConnection(connectionString)
' Open the database connection.
connection.Open()
Dim columns As List(Of Column) = New List(Of Column)
GetColumns(connection, tableName, schema, columns)
Dim metadata As IList(Of QueryColumnMetadata) = New List(Of QueryColumnMetadata)
For Each c As Column In columns
Dim m As QueryColumnMetadata = New QueryColumnMetadata
m.Name = c.Name
m.ColumnWidth = c.Length
m.IsNullable = c.AllowNulls
m.TypeInfo = c.TypeInfo
metadata.Add(m)
Next
Dim fields As StringBuilder = New StringBuilder
If (metadata.Count = 0) Then
' When the table is not found, adding an asterix returns an error message.
fields.Append("*")
Else
Dim fieldCount As Integer = metadata.Count
Dim isFirst As Boolean = True
Dim i As Integer = 0
For i = 0 To (fieldCount - 1)
Dim m As QueryColumnMetadata = metadata(i)
If (isFirst = False) Then
fields.Append(",")
End If
isFirst = False
fields.Append(EscapeName(m.Name))
queryResult.ColumnMetadata.Add(m)
Next
End If
' Create a new query object.
Dim query As Query = New Query
' Create a SQL query statement.
query.Statement = "SELECT " + fields.ToString + " FROM " + EscapeName(tableName)
' retrieve the query results.
Dim results() As QueryResult = ExecuteQuery(connectionString, query)
' Add the query results to the list.
queryResult.QueryResults.AddRange(results(0).QueryResults)
' Return the query result.
Return queryResult
End Function
...
Friend Shared Function EscapeName(ByVal name As String) As String
' Test if the value is already escaped.
If (name.StartsWith("[", StringComparison.Ordinal) AndAlso name.EndsWith("]", StringComparison.Ordinal)) Then
' Return the string with no changes.
Return name
End If
' Create a new string builder for the escaped string.
Dim escapedName As StringBuilder = New StringBuilder
' Prepend the string with an opening bracket.
escapedName.Append(Microsoft.VisualBasic.ChrW(91))
' Loop through the string.
Dim i As Integer = 0
Do While (i < name.Length)
Dim c As Char = name(i)
If (c = Microsoft.VisualBasic.ChrW(93)) Then
escapedName.Append(Microsoft.VisualBasic.ChrW(93))
End If
escapedName.Append(c)
i = (i + 1)
Loop
' Append the string with an opening bracket.
escapedName.Append(Microsoft.VisualBasic.ChrW(93))
' Return the escaped string.
Return escapedName.ToString
End Function
// 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();
}
Permissions
- Full trust for the immediate caller. This member cannot be used by partially trusted code. For more information, see Using Libraries from Partially Trusted Code.