Hi all,
I am trying to update updated data in a gridview but the update button seem to
do nothing as i retreive data using REF CURSOR.
Let me describe the architecture of my application first. I'm trying to
implement best practice whenever possible. I am following the data access
tutorial published in www.asp.net , the only difference is that i have an Oracle
(10g) database. So I split my application into three layers, data access,
business logic, and presentation layer. I'm also writing all queries in an
Oracle package.
So here is the code i am using:
Oracle Package: All the CRUD functions. Notice the first procedure (READ)
returns a REF Cursor
1 /*=================== TABLE: TF_REF_TYPES
===============================*/
2
/*=======================================================================*/3
PROCEDURE get_ref_type(ref_type_cursor OUT T_CURSOR) AS
4 BEGIN
5 6 OPEN ref_type_cursor FOR
7 SELECT ref_type_id, ref_type
8 FROM tf_ref_types;
9 10 END get_ref_type;
11 12 PROCEDURE create_ref_type(p_ref_type IN VARCHAR2, p_created_by IN
VARCHAR2) AS
13 BEGIN
14 15 INSERT INTO tf_ref_types (ref_type, created_by, created_date)
16 VALUES (p_ref_type, p_created_by, SYSDATE);
17 18 END create_ref_type;
19 20 PROCEDURE update_ref_type(p_ref_type IN VARCHAR2, p_ref_type_id IN
NUMBER, p_updated_by IN VARCHAR2) AS
21 BEGIN
22 23 UPDATE tf_ref_types
24 SET ref_type = p_ref_type,
25 updated_by = p_updated_by,
26 updated_date = SYSDATE
27 WHERE ref_type_id = p_ref_type_id;
28 29 END update_ref_type;
30 31 PROCEDURE delete_ref_type(p_ref_type_id IN NUMBER) AS32
ref_type_count NUMBER;
33 BEGIN34 35 -- Verify that no references are using the reference type.
36 SELECTCOUNT(ref_id) INTO ref_type_count
37 FROM tf_references
38 WHERE ref_type_id = p_ref_type_id;
39 40 IF ref_type_count > 0 THEN41
raise_application_error(-20018, 'The record cannot be deleted because there is
table including related records.$');
42 ELSE
43 DELETE FROM tf_ref_types
44 WHERE ref_type_id = p_ref_type_id;
45 END IF;
46 47 END delete_ref_type;
The I have xsd file that define a tableAdapter that uses the procedures defined
above.
Next I have a .cs file where my business logic will be and calls the function
from the xsd file. And I am assuming that this is where i need to make the
change to allow row updates.
1 [System.ComponentModel.DataObject]
2 public class ReferencesTypeBLL
3 {
4 private REF_TYPETableAdapter _refTable = null;
5 6 protected REF_TYPETableAdapter Adapter
7 {
8 get9 {
10 if (_refTable == null)
11 _refTable = new REF_TYPETableAdapter();
12 13 return _refTable;
14 }
15 }
16 17
[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjec
tMethodType.Select, true)]
18 public References.REF_TYPEDataTable GetReferenceTypes()
19 {
20 Object x;
21 return Adapter.GetRefType(out x);
22 }
23 24
[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjec
tMethodType.Select, false)]
25 public References.REF_TYPEDataTable GetReferenceTypeByID(int typeID)
26 {
27 Object x;
28 if (typeID == -1)
29 return GetReferenceTypes();
30 else
31 return Adapter.GetRefTypeByID(typeID, out x);
32 }
33 34
[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjec
tMethodType.Insert, true)]
35 public bool AddReferenceType(string REF_TYPE)
36 {
37 int rowsAffected = Adapter.Insert(REF_TYPE, "Admin");
38 return rowsAffected == 1;
39 }
40 41
[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjec
tMethodType.Update, true)]
42 public bool UpdateReferenceType(int REF_TYPE_ID, string REF_TYPE)
43 {
44 References.REF_TYPEDataTable refType =
GetReferenceTypeByID(REF_TYPE_ID);
45 46 if (refType.Count == 0)
47 return false;
48 49 /*References.REF_TYPERow typeItem = refType[0];
50 51 typeItem.REF_TYPE_ID = typeID;
52 typeItem.REF_TYPE = value;*/53 54 int rowsAffected =
Adapter.Update(REF_TYPE, REF_TYPE_ID, "Admin");
55 56 return rowsAffected == 1;
57 }
58 59 }
Presentation Layer:
1 <asp:DetailsView ID="DetailsView1" runat="server"
AllowPaging="True" AutoGenerateRows="False"2
DataKeyNames="REF_TYPE_ID" DataSourceID="ObjectDataSource1"
Height="50px" Width="125px">
3 <Fields>
4 <asp:BoundField DataField="REF_TYPE_ID"
HeaderText="REF_TYPE_ID" ReadOnly="True"5
SortExpression="REF_TYPE_ID" />
6 <asp:BoundField DataField="REF_TYPE"
HeaderText="REF_TYPE" SortExpression="REF_TYPE" />
7 <asp:CommandField ShowEditButton="True"
ShowInsertButton="True" />
8 </Fields>
9 </asp:DetailsView>
10 <br />
11 <asp:ObjectDataSource ID="ObjectDataSource1"
runat="server"12
OldValuesParameterFormatString="original_"13
SelectMethod="GetReferenceTypes"14
TypeName="ReferencesTypeBLL"15
InsertMethod="AddReferenceType"16
UpdateMethod="UpdateReferenceType">
17 <UpdateParameters>
18 <asp:Parameter Name="REF_TYPE_ID"
Type="Int32" />
19 <asp:Parameter Name="REF_TYPE"
Type="String" />
20 </UpdateParameters>
21 <InsertParameters>
22 <asp:Parameter Name="REF_TYPE"
Type="String" />
23 </InsertParameters>
24 </asp:ObjectDataSource>
In a nutshell, I am just trying to update records retrieved using REF CURSOR.
Your help is very much appreciated. Please let me know if further details are
required.
Cheers,
|