Search This Blog

Loading...

Saturday, December 8, 2012

SQL Server execute a storedprocedure with out parameters in SQL Management Studio

SYNTAX :
-----------

declare @param1 varchar(10)
declare @param2 varchar(15)
declare @param3 int

exec my_proc @param1,@param2,@param3 output

select '@param3 = ' + convert(varchar,@param3)

EXPLANATION:
-------------------

for this example :-
1. my_proc is the procedure which accepts 2 input params (  @param1,@param2 )
2. It returns an out param ( @param3)
3. So while running this proc from SQL mgmt studio, we will suffix the out-param with 'output' keyword

** I have added a final select stmt to verify the value returned

Monday, August 13, 2012

How to install Oracle 11g r2 standard edition

**Note if you are installing Oracle on your office laptop and there is already an Oracle Server then you need to install Oracle client.

**However, if you are learning Oracle on your own and want to install Oracle on your own laptop then you will need to install Oracle Server (As you don't already have a Server to connect to.). In this case, you need not install the Client

**Note Oracle database and online documents are available for free download at oracle.com. However, you will need to register at oracle.com and create a new account. It is a simple process and you only need a valid email id to get registered. Another benefit of registering is that you can ask for help in oracle discussion forums at oracle.com

Follow these steps for installing Oracle Server:- (I was able to install this on my laptop Windows 7 Ultimate 32-bit)

1. Download the two .zip files from Oracle website found at the following path:- (file1=1.5 GB;  file2=600MB)
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html
2. After both the files are downloaded to your hard drive, extract both files to a same folder say D:\Oracle
3. After both the files are finished extracting you will find 'Setup' in the following folder 'D:\Oracle\database. 4. Double click on Setup to start the installation.
5. Keep clicking 'Next' and follow the instructions. At some steps you will get 'User Accounts Warning, Allow this program to make changes Yes/No'. (Select yes when prompted)
6. During the 'Typical installation step' you are required to enter Administrator password. This password will be used to login to 'Oracle Enterprise Manager'
7. Installer will ask you whether to create database or install without database create.. Choose 'create database option.
8. In the last step you will be prompted to configure passwords. But you can do this later from 'Oracle Enterprise Manager'
9. Once your installation is finished, you can go to Start>Programs>oracle-OraDB11g_home>Database Control - orcl
10. This will open a page in your browser where you can do all 'Administrative Tasks' (Like SQL Server Management Studio). Login with userid=sys and password=(the administrative password you specified at the time of installation)
11. Now follow the steps in this page to unlock any oracle user accounts (most of the accounts will be locked after install. Some online books use a particular built in account, based on which books you are referring, choose to unlock that account)
http://docs.oracle.com/cd/E11882_01/server.112/e10575/tdpsg_user_accounts.htm
12. If you are a .NET programmer and just getting started with Oracle then you can refer the following book '2-Day .NET Developers guide' available at Oracle and start trying the example projects over there :-
http://www.oracle.com/pls/db112/portal.all_books
http://docs.oracle.com/cd/E11882_01/appdev.112/e10767.pdf

13. To follow the examples in the book you will have to download and install ODAC. In case, you have installed Oracle Server (11g r2 enterprise, 2 files, total 2GB downloaded from Oracle.com) then you need to install 'ODTwithODAC112012' (11.2.0.1.2) as this will be compatible with your Server software already installed in your machine. While installing you can install directly into your existing Oracle Home directory (the Oracle home on my machine looks something like this :-
C:\app\thoshiba\product\11.2.0\dbhome_1)
ODT with ODAC can be downloaded from here :-
http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html 

Tuesday, July 3, 2012

C# equivalent of IsDate and DateDiff

// DateDiff equivalent in C#

int sec = ((TimeSpan)( (DateTime)dra3["dtp"]- stime)).Seconds;

//Check if obj is date in C#

 if (DateTime.TryParse(dratt["Outtm"].ToString(),out outtm))
{
    //obj is a date
}
else
{
    //not a date
}

** here 'dra3' & 'dratt' are data rows
**outtm is datetime obj

Monday, June 25, 2012

ASP.NET DataGridView with 'Databound Dropdownlist'

This can be implemented using 'TemplateField' and 'EditItemTemplate' in the .aspx and using the below code inside the 'RowDataBound' eventhandler (code-behind).

Important thing to notice is the 'IsNot Nothing' check that has been done. First I failed with this code and later on added the 'Nohting' check and it worked like charm :-)

Protected Sub grdEmp_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles grdEmp.RowDataBound
        '    Dim ddl As DropDownList = CType(e.Row.FindControl("ctl00_ContentPlaceHolder1_grdEmp_ctl02_ddlDeptID"), DropDownList)
        Dim ddl As DropDownList = CType(e.Row.FindControl("ddlDeptID"), DropDownList)
        If ddl IsNot Nothing Then
            objData = New clsData
            ddl.DataSource = objData.fn_getDataSet("select * from Department").Tables(0)
            ddl.DataTextField = "Department"
            ddl.DataValueField = "Dept_Id"
            ddl.DataBind()
        End If

    End Sub

Sunday, May 27, 2012

select query to get most recent transaction time and transaction type with conditional check

select top 1 max(dtpunched),inout from loginfo where nfingerprintid=1117 and nverifyresult=1 group by nfingerprintid,inout having datediff("h",max(dtpunched),#27/May/2012 11:44:49 AM#)<18  and datediff("s",max(dtpunched),#27/May/2012 11:49:49 AM#)>0 order by 1 desc

**Note the use of 'order by 1 desc' and 'top1' to get the most recent record when we have grouped by additional columns