IDbTableDataManager.InsertRow Method

IIS 7.0

Inserts a row of data into a table.

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

Object[] InsertRow(
	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 insert.

Return Value

Type: System.Object[]
An object array that contains the data that was inserted and any computed values, such as identities.

All database providers that implement the IDbTableDataManager interface must also implement the InsertRow method. The database manager will use this method to insert data into a table of a database.

Notes for Implementers

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

public object[] InsertRow(string connectionString, string tableName, string schema, IList<ColumnDataInfo> columnDataInfoList)

{

   throw new NotImplementedException();

}

NoteNote:

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

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

NoteNote:

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



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



Show: