Wed 31 Dec 2008

Today I need to implement transaction in .NET. Front end language was VB.NET and database was Oracle.

This transaction is being implemented using Oracle Data Access Provider - ODP.NET.

Simple implementation is that create and open a connection, begin transaction using that connection, create command using that connection, call stored procedures or statments using command(s), if every thing gone fine and success then call commit of that transaction else rollback, and in last close and dispose connection and transaction.

Be sure not to use commit, rollback or statement that causes transaction invalidate inside the procedure that is being called within .NET transaction, otherwise that .NET transaction scope will no longer valid as within that connection commit or rollback have been called.

Sample code that make my work done is :-

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

Private Sub Save()
Dim conn As New OracleConnection("ConnString")
Dim trans As OracleTransaction
Dim success as Boolean = False

Try
conn.Open()
 trans = conn.BeginTransaction
success =  saveThingOne(conn)
If success Then
success = saveThingTwo(conn)
End If

If success  Then
 trans.Commit()
else
trans.Rollback()
End If

Catch ex As Exception
 trans.Rollback()
Finally
trans.Dispose()
conn.Close()
conn.Dispose()
End Try
End Sub



Private Sub saveThingOne(ByVal conn As OracleConnection) As Boolean
Dim success as Boolean = False
Using comm As New OracleCommand("Save_Thing_One_Stored_Procedure", conn)
   comm.CommandType = CommandType.StoredProcedure
   comm.Parameters.Add("IN_ID", OracleType.Number).Value = intID
   comm.Parameters.Add("IN_THING_ONE", OracleType.VarChar, 60).Value = strThingONe
   comm.Parameters.Add("OUT_RESULT", OracleType.Number).Direction = ParameterDirection.Output
   comm.ExecuteNonQuery()
‘Here if that procedure successfully perform action then will return 0 in case of success and 1 in case of failure
success = Iif(comm.Parameters(“OUT_RESULT”).Value.ToString().equals(“0”), True, False)
Return success
End Using
End Sub



Private Sub saveThingTwo(ByVal conn As OracleConnection)
Dim success as Boolean = False
Using comm As New OracleCommand("Save_Thing_Two_Stored_Procedure", conn)
   comm.CommandType = CommandType.StoredProcedure
   comm.Parameters.Add("IN_ID", OracleType.Number).Value = intID
   comm.Parameters.Add("IN_THING_TWO", OracleType.VarChar, 60).Value = strThingTwo
   comm.Parameters.Add("OUT_RESULT", OracleType.Number).Direction = ParameterDirection.Output
   comm.ExecuteNonQuery()
‘Here if that procedure successfully perform action then will return 0 in case of success and 1 in case of failure
success = Iif(comm.Parameters(“OUT_RESULT”).Value.ToString().equals(“0”), True, False)
Return success

End Using
End Sub

 

 

Comments here
Fri 20 Jun 2008

Today I tried to find out which table is taking much space in DB or to find the numbers of columns and rows in all tables of DB

Following query will return the required result

=========

USE DatabaseName
GO
CREATE TABLE #temp (
                table_name sysname ,
                row_count INT,
                reserved_size VARCHAR(50),
                data_size VARCHAR(50),
                index_size VARCHAR(50),
                unused_size VARCHAR(50))
    SET NOCOUNT ON
INSERT     #temp
    EXEC       sp_msforeachtable 'sp_spaceused "?"'
SELECT     a.table_name,
            a.row_count,
            COUNT(*) AS col_count,
            a.data_size
    FROM       #temp a
            INNER JOIN information_schema.columns b
            ON a.table_name collate database_default
    = b.table_name collate database_default
    GROUP BY   a.table_name, a.row_count, a.data_size
    ORDER BY   CAST(REPLACE(a.data_size, 'KB', '') AS integer) DESC
DROP TABLE #temp

 

 

 

Comments here
Thu 22 May 2008

By Jane Wakefield
Technology reporter, BBC News

 

 

 

 

Plans for a super-database containing the details of all phone calls and e-mails sent in the UK have been heavily criticised by experts.

 

The government is considering the changes as part of its ongoing fight against serious crime and terrorism.

Assistant Information Commissioner Jonathan Bamford has warned that the UK could be "sleepwalking into a surveillance society".

Others have questioned how such a database could be made secure.

 

Public confidence

"While the public is "sleepwalking" into a surveillance society, the government seems to have its eyes wide open although, unfortunately, to everything except security," said Jamie Cowper, data protection expert at data protection firm PGP Corporation.

"The bottom line is - information of this nature should only be held if - and only if - it can be demonstrated that an appropriate system of checks and balances is in place and the security of the information being stored is of paramount concern," he added.

Public confidence in the governments' ability to look after data has been dented in recent months with high profile failures, including the loss of a CD carrying all the personal details of every child benefit claimant.

 

The latest plans being mulled by the Home Office will form part of the proposed Communications Bill, which is due to be considered by MPs later this year.

It is, said a Home Office spokesman, crucial "to ensure that public authorities have access to communications data essential for counter-terrorism and investigation of crime purposes."

 

Risks

 

The more people who have access to it the more risks there would be

Chris Mayers, Citrix Systems

It would extend the powers of RIPA (the Regulation of Investigatory Powers Act) which currently allows hundreds of government agencies access to communications data.

Some believe such legislation, which requires government authorities to request information from communication providers, is more than adequate for law enforcement purposes.

"The fight against terrorism doesn't require a centralised database," said Chris Mayers, chief security architect at Citrix Systems, an applications delivery firm.

"Such a database would face threats from both outside and inside. The more people who have access to it the more risks there would be," he said.

 

Big Brother

The Internet Service Providers' Association said it was seeking more information about the proposals.

"In particular we want to know more about the Government's intentions regarding "modifying the procedures for acquiring communications data," said a spokesman.

In the run-up to RIPA being approved by parliament, human rights campaigner Privacy International argued that such an act would be a dangerous first step towards a "Big Brother" society.

According to Gus Hosein, a senior fellow at Privacy International, the latest proposals could be even more controversial.

"The idea that ISPs need to collect data and send it en masse to central government is, without doubt, illegal," he said.

 

 

uk database.JPG

Categories : Knowledge / Amazing
Comments here