IDbTableDataManager.EditRow Method

IIS 7.0

Modifies the data inside a table row.

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

void EditRow(
	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 modify.

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

Notes for Implementers

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

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

   throw new NotImplementedException();

}

NoteNote:

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

The following code sample implements the EditRow method to modify the data in a database table in an OLEDB data source.

NoteNote:

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



public void EditRow(string connectionString, string tableName, string schema, IList<ColumnDataInfo> columnDataInfoList)
{
    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        using (OleDbCommand command = new OleDbCommand())
        {
            command.Connection = connection;
            command.CommandType = CommandType.Text;

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

            command.CommandText = String.Format("UPDATE {0} SET {1} WHERE {2}", EscapeName(tableName), setClause, whereClause);

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



...



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



...



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



...



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

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

                if (info.IsEdited)
                {
                    setClause.Append(EscapeName(info.ColumnName));
                    setClause.Append("=?, ");
                    OleDbParameter param = command.CreateParameter();

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

            setClause.Remove(setClause.Length - 2, 2);

            return setClause.ToString();
        }



Show: