Saving Changes

Saving Changes

DBEdit allows saving changes to the underlying tables automatically.

For example, if a view or stored procedure has an SQL definition like "SELECT * FROM dbo.accounts", DBEdit saves changes to the dbo.accounts table.

You have to grant the VIEW DEFINITION permission on such views and stored procedures to allow DBEdit parsing the definition to find the underlying tables.

If you cannot give such permissions, or DBEdit cannot parse the SQL definition, you may specify the target table manually in the xls.objects table.

For example, the s02.usp_cashbook procedure has the target table s02.cashbook:

To save data into multiple underlying tables, you can use stored procedures executed for new, updated, and deleted rows.

In a simple case, create procedures with the _insert, _update, and _delete suffixes.

For example, a database has the following procedure used to select data:

CREATE PROCEDURE [s02].[usp_cashbook2]
AS
BEGIN
SET NOCOUNT ON
SELECT
    t.id
    , t.[date]
    , t.account_id
    , t.item_id
    , t.company_id
    , t.debit
    , t.credit
FROM
    s02.cashbook t
END

Also, the database contains the following procedures to insert, update, and delete records:

CREATE PROCEDURE [s02].[usp_cashbook2_insert]
    @date date = NULL
    , @account_id int = NULL
    , @item_id int = NULL
    , @company_id int = NULL
    , @debit money = NULL
    , @credit money = NULL
AS
BEGIN
INSERT INTO s02.cashbook ([date], account_id, item_id, company_id, debit, credit)
    VALUES (@date, @account_id, @item_id, @company_id, @debit, @credit)
END

CREATE PROCEDURE [s02].[usp_cashbook2_update]
    @id int = NULL
    , @date date = NULL
    , @account_id int = NULL
    , @item_id int = NULL
    , @company_id int = NULL
    , @debit money = NULL
    , @credit money = NULL
AS
BEGIN
UPDATE s02.cashbook
SET
    [date] = @date, account_id = @account_id,
    item_id = @item_id, company_id = @company_id, debit = @debit, credit = @credit
WHERE
    id = @id

END

CREATE PROCEDURE [s02].[usp_cashbook2_delete]
    @id int = NULL
AS
BEGIN
DELETE FROM s02.cashbook WHERE id = @id
END

In this case, DBEdit detects such procedures automatically using name suffixes.

You may assign such procedures to any object using the xls.objects table.

For example, the s02.usp_cashbook5 procedure uses the procedures shown above:

Note that you may use SQL codes in the INSERT_OBJECT, UPDATE_OBJECT, and DELETE_OBJECT fields.

This is extremely useful for SQLite and SQL Server Compact applications that do not support stored procedures.

Also, you may define SQL-based objects used to select data. Use the CODE type in the TABLE_TYPE field, and place the SQL code into the TABLE_CODE field.

Naming Conventions

In the example above, the stored procedures have parameters with the table column names.

So, the @date parameter gets the date column value, @account_id gets the account_id value, and so on.

DBEdit escapes column name characters that not supported in parameter names.

For example, to get the "account id" column value, use the parameter name @account_x0020_id.

Use the following SQL Server function to get a proper parameter name for any underlying column name:

CREATE FUNCTION [xls].[get_escaped_parameter_name]
(
    @name nvarchar(128) = NULL
)
RETURNS nvarchar(255)
AS
BEGIN

RETURN
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
    REPLACE(REPLACE(@name
    , ' ', '_x0020_'), '!', '_x0021_'), '"', '_x0022_'), '#', '_x0023_'), '$', '_x0024_')
    , '%', '_x0025_'), '&', '_x0026_'), '''', '_x0027_'), '(', '_x0028_'), ')', '_x0029_')
    , '*', '_x002A_'), '+', '_x002B_'), ',', '_x002C_'), '-', '_x002D_'), '.', '_x002E_')
    , '/', '_x002F_'), ':', '_x003A_'), ';', '_x003B_'), '<', '_x003C_'), '=', '_x003D_')
    , '>', '_x003E_'), '?', '_x003F_'), '@', '_x0040_'), '[', '_x005B_'), '\', '_x005C_')
    , ']', '_x005D_'), '^', '_x005E_'), '`', '_x0060_'), '{', '_x007B_'), '|', '_x007C_')
    , '}', '_x007D_'), '~', '_x007E_')

END
This website is using cookies. By continuing to browse, you give us your consent to our use of cookies as explained in our Cookie Policy.