Where to find more resources of learning store procedure and NET?

[adonet]

Where to find more resources of learning store procedure and NET?

 

[JBaileys]

If you are looking for general information on stored procedures: I would just check out msdn at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da_0nxv.asp

 

[alvika]

What more do u want to know about SP and .NET? Let me see, if i can help u. Im currently working on that.

lavy

 

[adonet]

Thank you for help. What I want to learn is how to write more complicated SP and work with NET.

 

[MFritts253]

A Get book for this is Visual Basic.Net Database Programming by Evangelor Petroutsos

 

[alvika]

Hi adonet,

This is the part to call SP from .net

Dim ProviderString As String
ProviderString = ProviderString.Format(ConnStr, ds, dbUser, dbPass)ProviderString = "Data Source=GEMS;USER ID=scott;PASSWORD=tiger
Conn.ConnectionString = ProviderString

Dim Cmd As New Data.OracleClient.OracleCommand()
Cmd.Connection = conn
Cmd.CommandText = "PCK_TREEVIEW.TREEVIEW"
Cmd.CommandType = CommandType.StoredProcedure
Cmd.Parameters.Add(New Data.OracleClient.OracleParameter("TREEID", Data.OracleClient.OracleType.VarChar)).Value = TreeID
Cmd.Parameters.Add(New Data.OracleClient.OracleParameter("RET_CUR", Data.OracleClient.OracleType.Cursor)).Direction = ParameterDirection.Output
Dim MyDA As New Data.OracleClient.OracleDataAdapter(Cmd)
Try
MyDA.Fill(ds)
Return ds
Catch x
End Try


I have used oracle connection.
now for the SP part.

CREATE OR REPLACE PACKAGE PCK_TREEVIEW AS
TYPE REF_CUR IS REF CURSOR ;
PROCEDURE TREEVIEW(TREEID IN VARCHAR2 :='', FROMDATE IN VARCHAR2 :='' , TODATE IN VARCHAR2 :='', SCREEN IN VARCHAR2 :='', RET_CUR IN OUT REF_CUR);
END PCK_TREEVIEW;
/

CREATE OR REPLACE PACKAGE BODY PCK_TREEVIEW
IS

--- { SCREEN : PM FOR PORTFOLIO MASTER SCREEN AND HM FOR HIERARCHY MAINTENANCE SCREEN AND WS FOR WEBSERVICE .} ---

PROCEDURE TREEVIEW(TREEID IN VARCHAR2:='' , FROMDATE IN VARCHAR2:='' , TODATE IN VARCHAR2 :='', SCREEN IN VARCHAR2 :='', RET_CUR IN OUT REF_CUR)
IS
ROW_PROCESSED NUMBER:= 1; PARENTID VARCHAR2(8); OLDPARENT VARCHAR2(8):='';
TOTALROWS NUMBER:=1; BBTOTAL NUMBER:=0; SCREENMOD INTEGER; CIDUPDATE INTEGER; ROW_PRO NUMBER;

V_FLD_HIERARCHY_TREE_ID HIERARCHY.FLD_HIERARCHY_TREE_ID%TYPE;
V_FLD_PARENT_PORTF HIERARCHY.FLD_PARENT_PORTF%TYPE;
V_FLD_CHILD_PORTF HIERARCHY.FLD_CHILD_PORTF%TYPE;
V_FLD_START_DT HIERARCHY.FLD_START_DT%TYPE;
V_FLD_END_DT HIERARCHY.FLD_END_DT%TYPE;
V_FLD_LEVEL_ID HIERARCHY.FLD_LEVEL_ID%TYPE;
V_LEVELID INTEGER;

M_TREEID VARCHAR2(8);

OV_FLD_HIERARCHY_TREE_ID HIERARCHY.FLD_HIERARCHY_TREE_ID%TYPE;
OV_FLD_PARENT_PORTF HIERARCHY.FLD_PARENT_PORTF%TYPE;
OV_FLD_CHILD_PORTF HIERARCHY.FLD_CHILD_PORTF%TYPE;

CURSOR CWEBSER IS (SELECT DISTINCT FLD_HIERARCHY_TREE_ID FROM HIERARCHY
WHERE FLD_HIERARCHY_TREE_ID NOT IN
(SELECT FLD_HIERARCHY_TREE_ID FROM TREE_TEMPLATE WHERE FLD_TREE_DESIGNATION ='Building Block'));

CURSOR CPMPAR IS (SELECT DISTINCT FLD_PARENT_PORTF FROM HIERARCHY WHERE FLD_PARENT_PORTF NOT IN
(SELECT FLD_CHILD_PORTF FROM HIERARCHY
WHERE FLD_HIERARCHY_TREE_ID = M_TREEID) AND FLD_HIERARCHY_TREE_ID = M_TREEID);

CURSOR CHMPAR IS (SELECT DISTINCT FLD_PARENT_PORTF FROM HIERARCHY WHERE FLD_PARENT_PORTF NOT IN
(SELECT FLD_CHILD_PORTF FROM HIERARCHY
WHERE FLD_HIERARCHY_TREE_ID = M_TREEID) AND FLD_HIERARCHY_TREE_ID = M_TREEID AND
(TO_DATE(FLD_START_DT,'dd/MM/yyyy') BETWEEN TO_DATE(FROMDATE,'dd/MM/yyyy') AND TO_DATE(TODATE,'dd/MM/yyyy')
OR TO_DATE(FLD_END_DT,'dd/MM/yyyy') BETWEEN TO_DATE(FROMDATE,'dd/MM/yyyy') AND TO_DATE(TODATE,'dd/MM/yyyy')));

CURSOR CALL IS (SELECT FLD_HIERARCHY_TREE_ID , FLD_PARENT_PORTF, FLD_CHILD_PORTF, FLD_START_DT ,
FLD_END_DT , FLD_LEVEL_ID , LEVEL+1 LEVELID FROM HIERARCHY
WHERE FLD_HIERARCHY_TREE_ID = M_TREEID OR FLD_HIERARCHY_TREE_ID IN (SELECT FLD_HIERARCHY_TREE_ID FROM HIERARCHY
WHERE FLD_PARENT_PORTF IN (SELECT FLD_CHILD_PORTF FROM HIERARCHY
WHERE FLD_HIERARCHY_TREE_ID IN (M_TREEID)
CONNECT BY PRIOR FLD_CHILD_PORTF = FLD_PARENT_PORTF
START WITH FLD_PARENT_PORTF = PARENTID) AND FLD_HIERARCHY_TREE_ID IN
(SELECT FLD_HIERARCHY_TREE_ID FROM TREE_TEMPLATE WHERE FLD_TREE_DESIGNATION ='Building Block'))
CONNECT BY PRIOR FLD_CHILD_PORTF = FLD_PARENT_PORTF
START WITH FLD_PARENT_PORTF = PARENTID AND FLD_HIERARCHY_TREE_ID = M_TREEID);

BEGIN
DELETE FROM TREEVIEW_TMP;

CIDUPDATE := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(CIDUPDATE,'ALTER SESSION SET NLS_DATE_FORMAT =''dd/MM/yyyy''',DBMS_SQL.V7) ;
ROW_PRO := DBMS_SQL.EXECUTE(CIDUPDATE);
DBMS_SQL.CLOSE_CURSOR(CIDUPDATE);

IF SCREEN = 'PM' THEN
SCREENMOD := 1;
ELSIF SCREEN = 'HM' THEN
IF LTRIM(RTRIM(FROMDATE)) IS NULL THEN
SCREENMOD := 1;
ELSE
SCREENMOD := 2;
END IF;
ELSIF SCREEN = 'WS' THEN
SCREENMOD := 3;
END IF;

IF SCREENMOD = 1 THEN
M_TREEID := TREEID;
FOR REC_CUR IN CPMPAR
LOOP
IF CPMPAR%NOTFOUND THEN
EXIT;
END IF;

PARENTID := REC_CUR.FLD_PARENT_PORTF;
IF OLDPARENT = REC_CUR.FLD_PARENT_PORTF THEN
EXIT;
END IF;

INSERT INTO TREEVIEW_TMP (FLD_HIERARCHY_TREE_ID , FLD_PARENT_PORTF , FLD_CHILD_PORTF , FLD_START_DT ,
FLD_END_DT , FLD_LEVEL_ID , FLD_LEVEL , SLNO)
VALUES (M_TREEID ,M_TREEID, PARENTID, SYSDATE ,
SYSDATE , 0 ,1, TOTALROWS);
TOTALROWS := TOTALROWS + 1;
OPEN CALL;
LOOP
IF CALL%NOTFOUND THEN
EXIT;
END IF;

FETCH CALL INTO V_FLD_HIERARCHY_TREE_ID , V_FLD_PARENT_PORTF, V_FLD_CHILD_PORTF, V_FLD_START_DT ,
V_FLD_END_DT , V_FLD_LEVEL_ID , V_LEVELID;

IF OV_FLD_HIERARCHY_TREE_ID = V_FLD_HIERARCHY_TREE_ID AND OV_FLD_PARENT_PORTF = V_FLD_PARENT_PORTF AND OV_FLD_CHILD_PORTF = V_FLD_CHILD_PORTF THEN
EXIT;
END IF;

INSERT INTO TREEVIEW_TMP (FLD_HIERARCHY_TREE_ID , FLD_PARENT_PORTF , FLD_CHILD_PORTF , FLD_START_DT ,
FLD_END_DT , FLD_LEVEL_ID , FLD_LEVEL , SLNO)
VALUES (V_FLD_HIERARCHY_TREE_ID , V_FLD_PARENT_PORTF, V_FLD_CHILD_PORTF, V_FLD_START_DT ,
V_FLD_END_DT , V_FLD_LEVEL_ID , V_LEVELID, TOTALROWS);

OV_FLD_HIERARCHY_TREE_ID := V_FLD_HIERARCHY_TREE_ID;
OV_FLD_PARENT_PORTF := V_FLD_PARENT_PORTF;
OV_FLD_CHILD_PORTF := V_FLD_CHILD_PORTF;
TOTALROWS := TOTALROWS + 1;
END LOOP;

CLOSE CALL;

OLDPARENT:= PARENTID;
ROW_PROCESSED := ROW_PROCESSED + 1;
END LOOP;
ELSIF SCREENMOD = 2 THEN
M_TREEID := TREEID;
FOR REC_CUR IN CHMPAR
LOOP
IF CHMPAR%NOTFOUND THEN
EXIT;
END IF;

PARENTID := REC_CUR.FLD_PARENT_PORTF;
IF OLDPARENT = REC_CUR.FLD_PARENT_PORTF THEN
EXIT;
END IF;

INSERT INTO TREEVIEW_TMP (FLD_HIERARCHY_TREE_ID , FLD_PARENT_PORTF , FLD_CHILD_PORTF , FLD_START_DT ,
FLD_END_DT , FLD_LEVEL_ID , FLD_LEVEL , SLNO)
VALUES (M_TREEID ,M_TREEID, PARENTID, SYSDATE ,
SYSDATE , 0 ,1, TOTALROWS);
TOTALROWS := TOTALROWS + 1;
OPEN CALL;
LOOP
IF CALL%NOTFOUND THEN
EXIT;
END IF;

FETCH CALL INTO V_FLD_HIERARCHY_TREE_ID , V_FLD_PARENT_PORTF, V_FLD_CHILD_PORTF, V_FLD_START_DT ,
V_FLD_END_DT , V_FLD_LEVEL_ID , V_LEVELID;

IF OV_FLD_HIERARCHY_TREE_ID = V_FLD_HIERARCHY_TREE_ID AND OV_FLD_PARENT_PORTF = V_FLD_PARENT_PORTF AND OV_FLD_CHILD_PORTF = V_FLD_CHILD_PORTF THEN
EXIT;
END IF;

INSERT INTO TREEVIEW_TMP (FLD_HIERARCHY_TREE_ID , FLD_PARENT_PORTF , FLD_CHILD_PORTF , FLD_START_DT ,
FLD_END_DT , FLD_LEVEL_ID , FLD_LEVEL , SLNO)
VALUES (V_FLD_HIERARCHY_TREE_ID , V_FLD_PARENT_PORTF, V_FLD_CHILD_PORTF, V_FLD_START_DT ,
V_FLD_END_DT , V_FLD_LEVEL_ID , V_LEVELID, TOTALROWS);

OV_FLD_HIERARCHY_TREE_ID := V_FLD_HIERARCHY_TREE_ID;
OV_FLD_PARENT_PORTF := V_FLD_PARENT_PORTF;
OV_FLD_CHILD_PORTF := V_FLD_CHILD_PORTF;
TOTALROWS := TOTALROWS + 1;
END LOOP;

CLOSE CALL;

OLDPARENT:= PARENTID;
ROW_PROCESSED := ROW_PROCESSED + 1;
END LOOP;
ELSIF SCREENMOD = 3 THEN
FOR REC_TREE IN CWEBSER
LOOP
M_TREEID := REC_TREE.FLD_HIERARCHY_TREE_ID;

FOR REC_CUR IN CPMPAR
LOOP
IF CPMPAR%NOTFOUND THEN
EXIT;
END IF;

PARENTID := REC_CUR.FLD_PARENT_PORTF;
IF OLDPARENT = REC_CUR.FLD_PARENT_PORTF THEN
EXIT;
END IF;

INSERT INTO TREEVIEW_TMP (FLD_HIERARCHY_TREE_ID , FLD_PARENT_PORTF , FLD_CHILD_PORTF , FLD_START_DT ,
FLD_END_DT , FLD_LEVEL_ID , FLD_LEVEL , SLNO)
VALUES (M_TREEID ,M_TREEID, PARENTID, SYSDATE ,
SYSDATE , 0 ,1, TOTALROWS);
TOTALROWS := TOTALROWS + 1;
OPEN CALL;
LOOP
IF CALL%NOTFOUND THEN
EXIT;
END IF;

FETCH CALL INTO V_FLD_HIERARCHY_TREE_ID , V_FLD_PARENT_PORTF, V_FLD_CHILD_PORTF, V_FLD_START_DT ,
V_FLD_END_DT , V_FLD_LEVEL_ID , V_LEVELID;

IF OV_FLD_HIERARCHY_TREE_ID = V_FLD_HIERARCHY_TREE_ID AND OV_FLD_PARENT_PORTF = V_FLD_PARENT_PORTF AND OV_FLD_CHILD_PORTF = V_FLD_CHILD_PORTF THEN
EXIT;
END IF;

INSERT INTO TREEVIEW_TMP (FLD_HIERARCHY_TREE_ID , FLD_PARENT_PORTF , FLD_CHILD_PORTF , FLD_START_DT ,
FLD_END_DT , FLD_LEVEL_ID , FLD_LEVEL , SLNO)
VALUES (M_TREEID , V_FLD_PARENT_PORTF, V_FLD_CHILD_PORTF, V_FLD_START_DT ,
V_FLD_END_DT , V_FLD_LEVEL_ID , V_LEVELID, TOTALROWS);

OV_FLD_HIERARCHY_TREE_ID := V_FLD_HIERARCHY_TREE_ID;
OV_FLD_PARENT_PORTF := V_FLD_PARENT_PORTF;
OV_FLD_CHILD_PORTF := V_FLD_CHILD_PORTF;
TOTALROWS := TOTALROWS + 1;
END LOOP;

CLOSE CALL;

OLDPARENT:= PARENTID;
ROW_PROCESSED := ROW_PROCESSED + 1;
END LOOP;
END LOOP;
END IF;
COMMIT;

OPEN RET_CUR FOR
SELECT FLD_HIERARCHY_TREE_ID , FLD_PARENT_PORTF , FLD_CHILD_PORTF , FLD_START_DT ,
FLD_END_DT , FLD_LEVEL_ID , FLD_LEVEL , SLNO FROM TREEVIEW_TMP ORDER BY SLNO;

COMMIT;
END TREEVIEW;
END PCK_TREEVIEW;
/


pralvika@yahoo.com is my personal id. If u have any clarifications, Pls dont hesitate to contact me.

Friendly
Lavy