IDbTableDataManager.DeleteRow Method

IIS 7.0

Removes a row from a table.

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

void DeleteRow(
	string connectionString,
	string tableName,
	string schema,
	IList<ColumnDataInfo> columnDataInfoList
)

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.
columnDataInfoList
Type: System.Collections.Generic.IList<ColumnDataInfo>
An IList object that contains a list of ColumnDataInfo objects to delete.

All database providers that implement the IDbTableDataManager interface must also implement the DeleteRow method. The database manager will use this method to remove rows from a table in a database.

Notes for Implementers

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

public void DeleteRow(string connectionString, string tableName, string schema, IList<ColumnDataInfo> columnDataInfoList)

{

   throw new NotImplementedException();

}

NoteNote:

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

The following code sample implements the DeleteRow method to remove a row from an OLEDB data source.

NoteNote:

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



// Delete a row from a table.
public void DeleteRow(string connectionString, string tableName, string schema, IList<ColumnDataInfo> columnDataInfoList)
{
    // Create a new string builder for the SQL statement.
    StringBuilder whereText = new StringBuilder();
    // Create a connection to the database.
    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        // Create database command object.
        using (OleDbCommand command = new OleDbCommand())
        {
            // Specify the command type.
            command.Connection = connection;
            command.CommandType = CommandType.Text;

            string whereClause = CreateWhereClause(columnDataInfoList, command, false);

            // Construct the DELETE query.
            command.CommandText = String.Format("DELETE FROM {0} WHERE {1}", EscapeName(tableName), whereClause);

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




...



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



...



        // 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);
            }
        }



...



        private string CreateWhereClause(IList<ColumnDataInfo> list, OleDbCommand command, bool includeModifiedColumnsOnly)
        {
            // Create a new string builder for the SQL statement.
            StringBuilder whereClause = new StringBuilder();

            foreach (ColumnDataInfo info in list)
            {
                // Determine the OLEDB type for the column.
                OleDbType dbType = StringToOleDbType(info.TypeInfo.ToString());

                // Do not add binary fields.
                if (dbType != OleDbType.Binary)
                {
                    // Do not include modified columns if the flag is set true
                    if (!includeModifiedColumnsOnly || info.IsEdited)
                    {
                        whereClause.Append(EscapeName(info.ColumnName));

                        if (info.OriginalValue == null)
                        {
                            whereClause.Append(" IS NULL AND ");
                        }
                        else
                        {
                            whereClause.Append("=? AND ");

                            OleDbParameter param = command.CreateParameter();
                            param.OleDbType = dbType;
                            if (dbType == OleDbType.Guid)
                            {
                                // Convert the GUID string back to a GUID for the parameter.
                                param.Value = new Guid(info.OriginalValue.ToString());
                            }
                            else
                            {
                                // Add the parameter value.
                                param.Value = GetDataValue(info.OriginalValue);
                            }
                            command.Parameters.Add(param);
                        }
                    }
                }
            }

            whereClause.Remove(whereClause.Length - 5, 5);

            return whereClause.ToString();
        }



Show: