Share via


ColumnDataInfo.TypeInfo Property

Retrieves the type information for the column data.

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

Syntax

'Declaration
Public Property TypeInfo As TypeInfo
'Usage
Dim instance As ColumnDataInfo
Dim value As TypeInfo

value = instance.TypeInfo
public TypeInfo TypeInfo { get; internal set; }
public:
property TypeInfo^ TypeInfo {
    TypeInfo^ get ();
    internal: void set (TypeInfo^ value);
}
function get TypeInfo () : TypeInfo
internal function set TypeInfo (value : TypeInfo)

Property Value

Type: Microsoft.Web.Management.DatabaseManager.TypeInfo
A TypeInfo class that contains the type information for the column data.

Examples

The following code sample implements the InsertRow method to insert data into a database table in an OLEDB data source.

Note

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


    Public Function InsertRow( _
            ByVal connectionString As String, _
            ByVal tableName As String, _
            ByVal schema As String, _
            ByVal columnDataInfoList _
            As System.Collections.Generic.IList( _
            Of Microsoft.Web.Management.DatabaseManager.ColumnDataInfo)) _
            As Object() _
            Implements Microsoft.Web.Management.DatabaseManager.IDbTableDataManager.InsertRow

        Dim insertFieldsText As StringBuilder = New StringBuilder
        Dim valuesText As StringBuilder = New StringBuilder
        Dim whereText As StringBuilder = New StringBuilder
        Dim selectFieldsText As StringBuilder = New StringBuilder

        Dim insertedRow() As Object = Nothing
        Dim connection As OleDbConnection = New OleDbConnection(connectionString)
        Dim command As OleDbCommand = New OleDbCommand
        command.Connection = connection
        command.CommandType = CommandType.Text

        For Each info As ColumnDataInfo In columnDataInfoList
            ' Append the column to the SELECT fields.
            selectFieldsText.Append(EscapeName(info.ColumnName))
            selectFieldsText.Append(",")
            ' Only update modified fields.
            If (info.IsEdited = True) Then
                ' Append the column to the INSERT fields.
                insertFieldsText.Append(EscapeName(info.ColumnName))
                insertFieldsText.Append(",")
                valuesText.Append("?,")
                ' Append the column to the WHERE statement.
                whereText.Append(String.Format("({0} = ?)", EscapeName(info.ColumnName)))
                whereText.Append(" AND ")
                ' Determine the OLEDB type for the column.
                Dim dbType As OleDbType = StringToOleDbType(info.TypeInfo.ToString)
                ' 
                Dim parameter As OleDbParameter = command.CreateParameter
                parameter.ParameterName = ("@" + info.ColumnName)
                parameter.OleDbType = dbType
                ' Test if the column is a GUID field.
                If (dbType = OleDbType.Guid) Then
                    ' Convert the GUID string back to a GUID for the parameter.
                    parameter.Value = New Guid(info.NewValue.ToString)
                Else
                    ' Add the parameter value.
                    parameter.Value = GetDataValue(info.NewValue)
                End If
                parameter.Direction = ParameterDirection.Input
                command.Parameters.Add(parameter)
            End If
        Next
        insertFieldsText.Remove((insertFieldsText.Length - 1), 1)
        valuesText.Remove((valuesText.Length - 1), 1)
        whereText.Remove((whereText.Length - 5), 5)
        selectFieldsText.Remove((selectFieldsText.Length - 1), 1)
        command.CommandText = String.Format("INSERT INTO {0} ({1}) VALUES({2})", EscapeName(tableName), insertFieldsText.ToString, valuesText.ToString)
        ' Open the database connection.
        connection.Open()
        ' Run the query.
        ExecuteNonQuery(connection, command)
        ' Close the database connection.
        connection.Close()
        Return insertedRow

    End Function



...


    Private Shared Function ConvertToSerializable(ByVal data As Object) As Object
        If (TypeOf data Is DBNull) Then
            Return Nothing
        ElseIf (TypeOf data Is Decimal) Then
            Return CType(CType(data, Decimal), Double)
        ElseIf (TypeOf data Is Guid) Then
            Return data.ToString
        Else
            Return data
        End If
    End Function



...



    ' Run a transactional query.
    Private Function ExecuteNonQuery( _
            ByVal connection As OleDbConnection, _
            ByVal command As OleDbCommand) As Integer

        ' Begin a transaction.
        Dim transaction As OleDbTransaction = connection.BeginTransaction
        ' Specify the transaction for the command.
        command.Transaction = transaction
        Try
            ' Run the query.
            Dim records As Integer = command.ExecuteNonQuery
            ' Test for the number of affected rows.
            If (records > 1) Then
                ' Throw an error if more than one row was affected.
                Dim e As InvalidOperationException = New InvalidOperationException
                e.Data("RowsAffected") = records
                Throw e
            End If
            ' Commit the transaction.
            transaction.Commit()
            ' Return the count of affected records.
            Return records
        Catch ex As Exception
            ' Roll back the transaction if en exception occurred
            transaction.Rollback()
            ' Raise an exception when an error occured.
            Throw New ProviderException(ex.Message)
        End Try

    End Function



...



    Private Shared Function GetDataValue(ByVal value As Object) As Object
        If (value Is Nothing) Then
            Return DBNull.Value
        Else
            Return value
        End If
    End Function


        public object[] InsertRow(string connectionString, string tableName, string schema, IList<ColumnDataInfo> columnDataInfoList)
        {
            StringBuilder insertFieldsText = new StringBuilder();
            StringBuilder valuesText = new StringBuilder();
            StringBuilder whereText = new StringBuilder();
            StringBuilder selectFieldsText = new StringBuilder();
            Object[] insertedRow = null;

            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                using (OleDbCommand command = new OleDbCommand())
                {
                    command.Connection = connection;
                    command.CommandType = CommandType.Text;

                    foreach (ColumnDataInfo info in columnDataInfoList)
                    {
                        // Append the column to the SELECT fields.
                        selectFieldsText.Append(EscapeName(info.ColumnName));
                        selectFieldsText.Append(",");

                        // Only update modified fields.
                        if (info.IsEdited == true)
                        {
                            // Append the column to the INSERT fields.
                            insertFieldsText.Append(EscapeName(info.ColumnName));
                            insertFieldsText.Append(",");

                            valuesText.Append("?,");

                            // Append the column to the WHERE statement.
                            whereText.Append(string.Format("({0} = ?)", EscapeName(info.ColumnName)));
                            whereText.Append(" AND ");

                            // Determine the OLEDB type for the column.
                            OleDbType dbType = StringToOleDbType(info.TypeInfo.ToString());

                            // 
                            OleDbParameter parameter = command.CreateParameter();
                            parameter.ParameterName = "@" + info.ColumnName;
                            parameter.OleDbType = dbType;

                            // Test if the column is a GUID field.
                            if (dbType == OleDbType.Guid)
                            {
                                // Convert the GUID string back to a GUID for the parameter.
                                parameter.Value = new Guid(info.NewValue.ToString());
                            }
                            else
                            {
                                // Add the parameter value.
                                parameter.Value = GetDataValue(info.NewValue);
                            }

                            parameter.Direction = ParameterDirection.Input;
                            command.Parameters.Add(parameter);
                        }
                    }

                    insertFieldsText.Remove(insertFieldsText.Length - 1, 1);
                    valuesText.Remove(valuesText.Length - 1, 1);
                    whereText.Remove(whereText.Length - 5, 5);
                    selectFieldsText.Remove(selectFieldsText.Length - 1, 1);

                    command.CommandText = String.Format("INSERT INTO {0} ({1}) VALUES({2})", EscapeName(tableName), insertFieldsText.ToString(), valuesText.ToString());

                    // Open the database connection.
                    connection.Open();
                    // Run the query.
                    ExecuteNonQuery(connection,command);
                    // Close the database connection.
                    connection.Close();
                }
            }

            return insertedRow;
        }



...



        private static object ConvertToSerializable(object data)
        {
            if (data is DBNull)
            {
                return null;
            }
            else if (data is Decimal)
            {
                return (double)((Decimal)data);
            }
            else if (data is Guid)
            {
                return data.ToString();
            }
            else
            {
                return data;
            }
        }



...



        // Run a transactional query.
        private int ExecuteNonQuery(OleDbConnection connection, OleDbCommand command)
        {
            // Begin a transaction.
            OleDbTransaction transaction = connection.BeginTransaction();
            // Specify the transaction for the command.
            command.Transaction = transaction;
            try
            {
                // Run the query.
                int records = command.ExecuteNonQuery();
                // Test for the number of affected rows.
                if (records > 1)
                {
                    // Throw an error if more than one row was affected.
                    InvalidOperationException e = new InvalidOperationException();
                    e.Data["RowsAffected"] = records;
                    throw e;
                }
                // Commit the transaction.
                transaction.Commit();
                // Return the count of affected records.
                return records;
            }
            catch (Exception ex)
            {
                // Roll back the transaction if en exception occurred
                transaction.Rollback();
                // Raise an exception when an error occured.
                throw new ProviderException(ex.Message);
            }
        }



...



        // Compute if a data value is null.
        private static object GetDataValue(object value)
        {
            return (value == null ? DBNull.Value : value);
        }

Permissions

See Also

Reference

ColumnDataInfo Class

Microsoft.Web.Management.DatabaseManager Namespace