Tuesday, 25 June 2013

How to return resultset from stored procedure In ORACLE

Hey some people thinking about its simple retreive data from  query rather than stored procedure.
But is more simple and safe to so do changes if required.

For that we need to follow steps as follows :
[Note : Creating stored proc. and package only through sqlplus]
1)   We need to package of oracle to create it.

Packages in oracle syntax :
 
   CREATE OR REPLACE PACKAGE SELECT_EMP_HISTORY AS
    TYPE T_CURSOR IS REF CURSOR;
    PROCEDURE Sp_Test_Emp_Data
    (
            p_year IN VARCHAR2,
           cur_JobHistory OUT T_CURSOR
    );
    END SELECT_EMP_HISTORY;

   /


 Create  Package Body :

    CREATE OR REPLACE PACKAGE BODY SELECT_EMP_HISTORY AS
     PROCEDURE Sp_Test_Emp_Data
    (
       p_year IN VARCHAR2,
       cur_JobHistory OUT T_CURSOR
     )
        IS
        BEGIN
       OPEN cur_JobHistory FOR
       SELECT * FROM Table1  WHERE num=@p_year ;
       END Sp_Test_It_Projection_Data;
      END SELECT_EMP_HISTORY;
     /


[ Your Stored Procedure over view created , now you can edit your procedure according to requierment ]

C# :
         // Open Oracle connection and "conn" is connection variable in sample prog.
           OracleCommand cmd = new OracleCommand();
            cmd.Connection = conn;
            cmd.CommandText = "SELECT_EMP_HISTORY.Sp_Test_It_Projection_Data";
            cmd.CommandType = CommandType.StoredProcedure;
            // add the parameters for the stored procedure including the REF CURSOR
            // to retrieve the result set
            cmd.Parameters.Add("p_finYear", OracleDbType.Varchar2).Value = "100";
            cmd.Parameters.Add("cur_JobHistory", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
            // open the connection and create the DataReader
            conn.Open();
            OracleDataReader dr = cmd.ExecuteReader();
            // output the results and close the connection.
            DataTable dt = new DataTable();
            dt.Load(dr);
  
  

Tuesday, 4 June 2013

Difference between AddRange and RemoveRange in C#

AddRange : 

List<T>.AddRange Method 

Adds the elements of the specified collection to the end of the List<T>.

If you are retrieving records from database and want to add to current array list , then it will help to add range to current record array.

Example :

           string[] oldinput= { "Nihar",
                                         "technical",
                                         "Funda" };


            string[] newInput = { "Blog",
                                             "spot",
                                             ".com" };

 
            List<string> oldList = new List<string>();
            oldList.AddRange(oldinput);

            foreach (string ip in oldList)
            {
                Console.WriteLine(ip);
            }

            oldList.AddRange(newInput);
            Console.WriteLine("\n ###  New members added ####");
            foreach (string ip in oldList)
            {
                Console.WriteLine(ip);
            }



Output : 

Nihar
technical
Funda
 ###  New members added ####
Nihar
technical
Funda
Blog
spot
.com


RemoveRange : 

Removes a range of elements from the List<T>.

Syntax :
public void RemoveRange(int index,int count)

Take reference of above output and continue it....

            oldList.RemoveRange(2, 2);
            Console.WriteLine("\n ###  Remove members  ####");
            foreach (string ip in oldList)
            {
                Console.WriteLine(ip);
            } 



Output :

 ###  Remove members  ####
Nihar
technical
spot
.com











 

Saturday, 1 June 2013

Paging in ASP.Net MVC

Here , you wil get simplest solution for " How to do paging in MVC ? "

Step 1: 

Download following .dll from here.

*.dll build in .net 3.5 (VS2008)


Step 2:

Add as reference to your solution.

Right Click to solution ->  Add reference -> Browse downloaded DLL -> Select it


Step 3:

 In Controller of Project ,

 Add line on top of page " using  MvcPagingByNihar  "

  public ActionResult Index(int? page)
        {

            page = page - 1;
            if (page < 0)
                page = 0;

           Ienumerable<tableName> mymaterial= /*Your query*/

            ViewData["numpages"] = mycount / 9;
            ViewData["curpage"] = page;

            return View(new PagedList<TableName>(myMaterial, page??0, pagesize,mycount));

      }


Step 4:  Index (View Page)

Current Page
    <%=Convert.ToInt32(ViewData["curpage"]) + 1 %>
    of
    <%=ViewData["numpages"] %><br />
    <br />
    <div id="paging">
        <table>
            <tr>
                <%
                    for (int i = 1; i <= Convert.ToInt32(ViewData["numpages"]); i++)
                    {
                %>
                <td style="border: 2px">
                    <%--style="background-color: Aqua; border-width: thin"--%>
                    <span><b style="color: Gray">
                        <%= Html.ActionLink(i.ToString(),"Index","ControllerName",new{page=i},null) %>
                    </b></span>
                </td>
                <%
                    }%>
            </tr>
        </table>


 Enjoy !!...

If any  problem arise with dll .Please comment here.