1 2 Previous Next 19 Replies Latest reply on Apr 17, 2009 11:38 AM by 696827 Go to original post
      • 15. Re: How to convert .Net Guid to Oracle ???
        651546
        Hereby is the code which can assist you to accomplish what you require.


        //Oracle Stored Procedure

        create or replace PROCEDURE "USERS_CREATEUSER"
        (

        P_USERID IN RAW,
        P_RECORDSET OUT SYS_REFCURSOR

        )
        as
        Begin

        Open P_RECORDSET for

        SELECT username, vorname, nachname, abteilung, abkuerzung FROM UserProfiles WHERE UserId = P_USERID;

        END;

        //Oracle Stored Procedure End

        Run the code whatever i've given here exactly in your Oracle database (change the name of the Stored procedure, if you want). This stored procedure will return a dataset, since it has RefCursor as the output. When once you get the Dataset, you can use it with the DetailsView to format and display as you desire.

        Here is the C# code for the accessing of the Stored Procedure.

        OracleParameter[] Params = new OracleParameter[2];
        Params[0] = new OracleParameter();
        Params[0].ParameterName = "P_USERID";
        Params[0].OracleType = System.Data.OracleClient.OracleType.Raw;
        Params[0].Direction = ParameterDirection.Input;
        Params[0].Value = AspnetUserId.ToByteArray();

        Params[1] = new OracleParameter();
        Params[1].ParameterName = "P_RECORDSET";
        Params[1].Direction = ParameterDirection.Output;
        Params[1].OracleType = System.Data.OracleClient.OracleType.Cursor;

        The above code declares two parameters one for input and one for output, the output parameter gives you a dataset which contains the data. After declaring, attach these parameters to the Storedprocedure and call it from the C# Code, which i think you know it how to.....
        • 16. Re: How to convert .Net Guid to Oracle ???
          654407
          Hi Ravi,
          Many thanks for your support. I am in this area still beginners. that is why I have another question, my code is attached:

          Oracle Procedure:

          CREATE OR REPLACE
          PROCEDURE GET_USERPROFILE
          (P_userid in RAW, P_recordset out SYS_refcursor)

          AS
          BEGIN
          Open P_REcordset for

          select username, vorname, nachname abteilung, abkuerzung from userprofiles where userid = P_userid;
          END GET_USERPROFILE;

          C# Code:

          MembershipUser currentuser = Membership.GetUser();
          // Get a reference to the currently logged on user
          //MembershipUser currentUser = Membership.GetUser();
          Guid currentUserId = new Guid("{" + currentuser.ProviderUserKey.ToString() + "}");
          /*String SQL = " SELECT username, vorname, nachname, abteilung, abkuerzung FROM UserProfiles WHERE UserId = '" + currentUserId.ToByteArray();
          String SQL2 = SQL + "'";
          UserProfileDataSource.SelectCommand = SQL2;*/

          OracleConnection con = new OracleConnection();
          string connString = WebConfigurationManager.ConnectionStrings["aspnet_connect"].ToString();
          con.ConnectionString = connString;
          con.Open();
          OracleCommand select_user = new OracleCommand("GET_USERPROFILE", con);
          select_user.CommandType = CommandType.StoredProcedure;
          OracleParameter[] Params = new OracleParameter[2];
          Params[0] = new OracleParameter();
          Params[0].ParameterName = "P_userid";
          Params[0].OracleDbType = OracleDbType.Raw;
          Params[0].Direction = ParameterDirection.Input;
          Params[0].Value = currentUserId.ToByteArray();

          Params[1] = new OracleParameter();
          Params[1].ParameterName = "P_recordset";
          Params[1].Direction = ParameterDirection.Output;
          Params[1].OracleDbType = OracleDbType.RefCursor;



          select_user.ExecuteNonQuery();
          con.Close();

          My Question : How to bind the SQLDATASOURCE with this Procedure in C#
          • 17. Re: How to convert .Net Guid to Oracle ???
            651546
            Hi,

            But you told me that you are working with on Oracle right? Then, how do you require a Sql Binding in this context??
            • 18. Re: How to convert .Net Guid to Oracle ???
              682328
              Hi Ravi,

              At first, I am a newbee in field of using oracle with asp.net

              I am also facing the problem to convert userid and to store in another member table.

              I have tried the way, which you mentioned, but that doesn't helped me.

              If possible, please provide some more clear structure
              so that I can use it.


              Thanks,
              • 19. Re: How to convert .Net Guid to Oracle ???
                696827
                Hi ,


                i tried to extend membership user id in custom table,when i convert guid to bytearray ,while inserting i am getting

                ORA-06502: PL/SQL: numeric or value error: character to number conversion error

                my code is

                ================================

                if (oMembershipCreateStatus == MembershipCreateStatus.Success)
                {
                try
                {
                Roles.AddUserToRole(oMembershipUser.UserName, "Admin");
                NewAccount.UserID = oMembershipUser.ProviderUserKey;
                OracleCommand oORACLECommand = new OracleCommand();
                MembershipUser ocurrentUser = Membership.GetUser(NewAccount.UserID);
                Guid currentUserId = new Guid("{" + ocurrentUser.ProviderUserKey.ToString() + "}");

                //AddParamToORACLECmd(oORACLECommand, "ReturnValue", OracleDbType.Int32, 0, ParameterDirection.ReturnValue, null);
                AddParamToORACLECmd(oORACLECommand, "USERID", OracleDbType.Raw, 16, ParameterDirection.Input,currentUserId.ToByteArray() );
                AddParamToORACLECmd(oORACLECommand, "FIRSTNAME", OracleDbType.Varchar2, 100, ParameterDirection.Input, NewAccount.FirstName);
                AddParamToORACLECmd(oORACLECommand, "LASTNAME", OracleDbType.Varchar2, 100, ParameterDirection.Input, NewAccount.LastName);
                //AddParamToORACLECmd(oORACLECommand, "ORGANIZATION", OracleDbType.Char, 5, ParameterDirection.Input, NewAccount.Organization);
                AddParamToORACLECmd(oORACLECommand, "ACCOUNTTYPE", OracleDbType.Varchar2, 100, ParameterDirection.Input, NewAccount.AccountType);
                AddParamToORACLECmd(oORACLECommand, "ADDRESS", OracleDbType.Varchar2, 100, ParameterDirection.Input, NewAccount.Address);
                AddParamToORACLECmd(oORACLECommand, "ADDRESS2", OracleDbType.Varchar2, 50, ParameterDirection.Input, NewAccount.Address2);
                AddParamToORACLECmd(oORACLECommand, "CITY", OracleDbType.Varchar2, 100, ParameterDirection.Input, NewAccount.City);
                AddParamToORACLECmd(oORACLECommand, "STATE", OracleDbType.Varchar2, 50, ParameterDirection.Input, NewAccount.State);
                AddParamToORACLECmd(oORACLECommand, "ZIPCODE", OracleDbType.Varchar2, 15, ParameterDirection.Input, NewAccount.ZipCode);
                AddParamToORACLECmd(oORACLECommand, "COUNTRY", OracleDbType.Varchar2, 100, ParameterDirection.Input, NewAccount.Country);
                AddParamToORACLECmd(oORACLECommand, "TELEPHONE", OracleDbType.Varchar2, 20, ParameterDirection.Input, NewAccount.Telephone);
                AddParamToORACLECmd(oORACLECommand, "FAX", OracleDbType.Varchar2, 20, ParameterDirection.Input, NewAccount.Fax);
                AddParamToORACLECmd(oORACLECommand, "EMAIL", OracleDbType.Varchar2, 100, ParameterDirection.Input, NewAccount.EMail);
                AddParamToORACLECmd(oORACLECommand, "USERNAME", OracleDbType.Varchar2, 50, ParameterDirection.Input, NewAccount.UserName);
                AddParamToORACLECmd(oORACLECommand, "PASSWORD", OracleDbType.Varchar2, 100, ParameterDirection.Input, NewAccount.Password);
                AddParamToORACLECmd(oORACLECommand, "STATUS", OracleDbType.Int32, 0, ParameterDirection.Input, 1);
                AddParamToORACLECmd(oORACLECommand, "REFERRALTYPE", OracleDbType.Varchar2, 200, ParameterDirection.Input, 1);

                SetCommandType(oORACLECommand, CommandType.StoredProcedure, SP_MA_CREATE_ACCOUNT);
                ExecuteScalarCmd(oORACLECommand);
                return oMembershipCreateStatus;
                }
                catch (Exception ex)
                {

                }
                }

                Edited by: neezhas@gmail.com on Apr 17, 2009 4:38 AM
                1 2 Previous Next