Oracle and SqlDataSource two way data binding

Often you have to create a simple pages for your website, for example admin pages which allow you edit tables through web. When working with Ms Sql server it’s a very easy task which you can solve with SqlDataSource and DataGrid. But using Oracle it’s difficult to make your page work without exceptions and you have to know some tricks for using Oracle with SqlDataSource.

To develop webpage you can start with user interface. Put SqlDataSource and DataGrid on the page, and add css class if needed.

Than you need to add settings to SqlDataSource to select data from database using SelectCommand. And now you can have data inside of DataGrid.

To make Grid editable you have to define UpdateCommand which in this case contains the name of stored procedure which I am using to update table:

CREATE OR REPLACE PACKAGE HEATMAP.orm_web_support 
AS 
   PROCEDURE update_stream_lookup ( 
      stream_id_     public_stream_lookup.stream_id%TYPE, 
      stream_name_   public_stream_lookup.stream_name%TYPE, 
      is_visible_    public_stream_lookup.is_visible%TYPE 
   ); 
END orm_web_support; 
/ 

CREATE OR REPLACE PACKAGE BODY HEATMAP.orm_web_support 
AS 
   PROCEDURE update_stream_lookup ( 
      stream_id_     public_stream_lookup.stream_id%TYPE, 
      stream_name_   public_stream_lookup.stream_name%TYPE, 
      is_visible_    public_stream_lookup.is_visible%TYPE 
   ) 
   AS 
   BEGIN 
      UPDATE public_stream_lookup 
         SET stream_name = stream_name_, 
             is_visible = is_visible_, 
             last_modified_date = SYSDATE 
       WHERE stream_id = stream_id_; 
   END; 
END orm_web_support; 
/ 

It’s a simple Sql to update table, but I was not able to make page work without stored procedure.

You need to provide parameters for UpdateCommand inside of SqlDataSource:

<UpdateParameters> 
    <asp:Parameter Name="STREAM_ID" Type="Int32" /> 
    <asp:Parameter Name="STREAM_NAME" Type="String" /> 
    <asp:Parameter Name="IS_VISIBLE" Type="String" /> 
</UpdateParameters> 

Now if you try to run application you will get Exception:

PLS-00306: wrong number or types of arguments in call to 'UPDATE_STREAM_LOOKUP'

To make UpdateCommand work in OnUpdating event for DataSource you have to initialize parameters if you need, or add some parameters, usually it’s username who make changes or something like this. The number and the names of parameters must be the same as in Oracle stored procedure, so you have to add underscore at the and of the parameters.

Why do you need to add underscore? The thing is, in UpdateParameter you need to have the same names as in stored procedure, but you can’t change parameter names inside of your SqlDataSource, because they will not be initialized, so you have to change names inside of OnUpdating event. And the easiest way is use underscore for new parameter names.

protected void SqlStreams_OnUpdating(object sender, SqlDataSourceCommandEventArgs e) 
{ 
    foreach (DbParameter param in e.Command.Parameters) 
    { 
        param.ParameterName = param.ParameterName + "_"; 
    } 
} 

And now you can change data in table

If not all the columns in the table should be editable, for example Id, you have use asp:TemplateField instead of BoundField and Bind method instead of Eval. Why you have to use Bind, read here

To delete line from table you have to make the more simple steps as for edit. You need to define DeleteCommand which is simple sql statement. DeleteParameters in this case is not defined, so you have to create new parameter in your c# code and add it to command in OnDeleting event:

protected void SqlStreams_OnDeleting(object sender, SqlDataSourceCommandEventArgs e) 
{ 
    int appId; 
    if (Int32.TryParse(deleteKey, out appId)) 
    { 
        OracleParameter parameter = new OracleParameter("STREAM_ID", appId); 
        e.Command.Parameters.Add(parameter); 
    } 
} 

But before you need to know id of the row to delete. And you can save it in OnRowDeleting event for DataGrid:

protected void Grid_OnRowDeleting(object sender, GridViewDeleteEventArgs e) 
{ 
    GridView grid = sender as GridView; 
    if (null != grid) 
    { 
        deleteKey = e.Values["STREAM_ID"].ToString(); 
    } 
} 

And now it should work, and you will be able to edit rows inside of DataGrid and delete rows from DataGrid.

Below is source code for both .aspx and .cs pages:

<%@ Page Title="" Language="C#" MasterPageFile="~/DevKitV5Content.master" 
    AutoEventWireup="true" CodeFile="PublicStreamsLookup.aspx.cs" 
    Inherits="PublicStreamsLookup" %> 
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" 
    runat="Server"> 
    <asp:SqlDataSource ID="SqlStreams" runat="server" SelectCommand="select STREAM_ID, stream_name, is_visible, last_modified_date from public_stream_lookup order by stream_id desc"
        DeleteCommand="delete from public_stream_lookup where stream_Id = :Id" 
        UpdateCommand="heatmap.orm_web_support.update_Stream_lookup" 
        UpdateCommandType="StoredProcedure" ProviderName="System.Data.OracleClient" 
        OnUpdating="SqlStreams_OnUpdating" OnDeleting="SqlStreams_OnDeleting"> 
        <UpdateParameters> 
            <asp:Parameter Name="STREAM_ID" Type="Int32" /> 
            <asp:Parameter Name="STREAM_NAME" Type="String" /> 
            <asp:Parameter Name="IS_VISIBLE" Type="String" /> 
        </UpdateParameters> 
        <DeleteParameters> 
            <asp:Parameter Name="STREAM_ID" Type="Int32" /> 
        </DeleteParameters> 
    </asp:SqlDataSource> 
    <div> 
        <asp:Label runat="server" ForeColor="Red" ID="LblError"></asp:Label> 
        <table class="dataGrid"> 
            <tr> 
                <th> 
                    Stream name 
                </th> 
                <th> 
                    Visible 
                </th> 
            </tr> 
            <tr> 
                <td> 
                    <asp:TextBox ID="TxtStreamName" runat="server" CssClass="selectable"></asp:TextBox> 
                </td> 
                <td> 
                    <asp:DropDownList ID="LstVisible" runat="server"> 
                        <asp:ListItem Text="Yes" Value="Y"></asp:ListItem> 
                        <asp:ListItem Text="No" Value="N"></asp:ListItem> 
                    </asp:DropDownList> 
                </td> 
            </tr> 
        </table> 
        <asp:Button ID="Button1" runat="server" Text="Add" Style="float: right;" 
            CssClass="buttonclass" OnClick="AddButton_OnClick" /> 
    </div> 
    <asp:GridView runat="server" DataSourceID="SqlStreams" AutoGenerateEditButton="true" 
        CssClass="dataGrid" GridLines="None" ID="GridStreams" AutoGenerateDeleteButton="true" 
        OnRowDeleting="Grid_OnRowDeleting" AutoGenerateColumns="false"> 
        <AlternatingRowStyle CssClass="odd" /> 
        <Columns> 
            <asp:TemplateField HeaderText="Id"> 
                <ItemTemplate> 
                    <asp:Label runat="server" ID="lblId" Text='<%#Bind("STREAM_ID") %>'></asp:Label> 
                </ItemTemplate> 
            </asp:TemplateField> 
            <asp:BoundField HeaderText="Stream name" DataField="stream_name" /> 
            <asp:BoundField HeaderText="Visible" DataField="is_visible" /> 
            <asp:TemplateField HeaderText="Last modified date"> 
                <ItemTemplate> 
                    <asp:Label runat="server" ID="lblDate" Text='<%#Eval("last_modified_date", "{0:dd/MM/yyyy}") %>'></asp:Label>
                </ItemTemplate> 
            </asp:TemplateField> 
        </Columns> 
    </asp:GridView> 
</asp:Content> 

using System; 
using System.Data.Common; 
using System.Data.OracleClient; 
using System.Web.UI.WebControls; 
using ORM_MS.DAL; 
using ORM_MS.Framwork; 
public partial class PublicStreamsLookup : PageBase 
{ 
    private string deleteKey; 
    protected override void OnInit(EventArgs e) 
    { 
        base.OnInit(e); 
        SqlStreams.ConnectionString = Settings.HeatmapConnectionString; 
        SqlStreams.DataBind(); 
    } 
    protected void Page_Load(object sender, EventArgs e) 
    { 
    } 
    protected void SqlStreams_OnUpdating(object sender, SqlDataSourceCommandEventArgs e) 
    { 
        foreach (DbParameter param in e.Command.Parameters) 
        { 
            param.ParameterName = param.ParameterName + "_"; 
        } 
    } 

    protected void SqlStreams_OnDeleting(object sender, SqlDataSourceCommandEventArgs e) 
    { 
        int appId; 
        if (Int32.TryParse(deleteKey, out appId)) 
        { 
            OracleParameter parameter = new OracleParameter("STREAM_ID", appId); 
            e.Command.Parameters.Add(parameter); 
        } 
    } 
    protected void Grid_OnRowDeleting(object sender, GridViewDeleteEventArgs e) 
    { 
        GridView grid = sender as GridView; 
        if (null != grid) 
        { 
            deleteKey = e.Values["STREAM_ID"].ToString(); 
        } 
    } 
    protected void AddButton_OnClick(object sender, EventArgs e) 
    { 
        try 
        { 
            new DbMaintanenceDao().AddInStreams(TxtStreamName.Text, 
                                                LstVisible.SelectedValue); 
            GridStreams.DataBind(); 
        } 
        catch (Exception ex) 
        { 
            LblError.Text = "It was an error while adding value. Error id = " + Logger.LogException(ex); 
            ; 
        } 
    } 
}