Wednesday, December 28, 2011

ORACLE DAL in C#

Hi All,

We can easily find the Data Access Layer for SQL, but here is one which we created for Oracle. Hope it would help you all.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

using Demo.Common;

namespace Demo.DataAccessLayer
{

////////////////////////////////////////////////////////////////////////////
/// <summary>
/// Defines common DataService routines for transaction management,
/// stored procedure execution, parameter creation, and null value
/// checking
/// </summary>
////////////////////////////////////////////////////////////////////////////
public class DataServiceBase
{

////////////////////////////////////////////////////////////////////////
// Fields
////////////////////////////////////////////////////////////////////////
private bool _isOwner = false; //True if service owns the transaction
private SqlTransaction _txn; //Reference to the current transaction

////////////////////////////////////////////////////////////////////////
// Properties
////////////////////////////////////////////////////////////////////////
public IDbTransaction Txn
{
get { return (IDbTransaction)_txn; }
set { _txn = (SqlTransaction)value; }
}


////////////////////////////////////////////////////////////////////////
// Constructors
////////////////////////////////////////////////////////////////////////

public DataServiceBase() : this(null) { }


public DataServiceBase(IDbTransaction txn)
{
if (txn == null)
_isOwner = true;
else
{
_txn = (SqlTransaction)txn;
_isOwner = false;
}
}


////////////////////////////////////////////////////////////////////////
// Connection and Transaction Methods
////////////////////////////////////////////////////////////////////////
protected static string GetConnectionString()
{
return ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
}


public static IDbTransaction BeginTransaction()
{
SqlConnection txnConnection =
new SqlConnection(GetConnectionString());
txnConnection.Open();
return txnConnection.BeginTransaction();
}


////////////////////////////////////////////////////////////////////////
// ExecuteDataSet Methods
////////////////////////////////////////////////////////////////////////
protected DataSet ExecuteDataSet(string procName,
params IDataParameter[] procParams)
{
SqlCommand cmd;
return ExecuteDataSet(out cmd, procName, procParams);
}


protected DataSet ExecuteDataSet(out SqlCommand cmd, string procName,
params IDataParameter[] procParams)
{
SqlConnection cnx = null;
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter();
cmd = null;

try
{
//Setup command object
cmd = new SqlCommand(procName);
cmd.CommandType = CommandType.StoredProcedure;
if (procParams != null)
for (int index = 0; index < procParams.Length; index++)
cmd.Parameters.Add(procParams[index]);

da.SelectCommand = (SqlCommand)cmd;

//Determine the transaction owner and process accordingly
if (_isOwner)
{
cnx = new SqlConnection(GetConnectionString());
cmd.Connection = cnx;
cnx.Open();
}
else
{
cmd.Connection = _txn.Connection;
cmd.Transaction = _txn;
}

//Fill the dataset
da.Fill(ds);
}
catch
{
throw;
}
finally
{
if(da!=null) da.Dispose();
if(cmd!=null) cmd.Dispose();
if (_isOwner)
cnx.Dispose(); //Implicitly calls cnx.Close()
}
return ds;
}


////////////////////////////////////////////////////////////////////////
// ExecuteNonQuery Methods
////////////////////////////////////////////////////////////////////////
protected void ExecuteNonQuery(string procName,
params IDataParameter[] procParams)
{
SqlCommand cmd;
ExecuteNonQuery(out cmd, procName, procParams);
}


protected void ExecuteNonQuery(out SqlCommand cmd, string procName,
params IDataParameter[] procParams)
{
//Method variables
SqlConnection cnx = null;
cmd = null; //Avoids "Use of unassigned variable" compiler error

try
{
//Setup command object
cmd = new SqlCommand(procName);
cmd.CommandType = CommandType.StoredProcedure;
for (int index = 0; index < procParams.Length; index++)
cmd.Parameters.Add(procParams[index]);

//Determine the transaction owner and process accordingly
if (_isOwner)
{
cnx = new SqlConnection(GetConnectionString());
cmd.Connection = cnx;
cnx.Open();
}
else
{
cmd.Connection = _txn.Connection;
cmd.Transaction = _txn;
}

//Execute the command
cmd.ExecuteNonQuery();
}
catch
{
throw;
}
finally
{
if (_isOwner)
cnx.Dispose(); //Implicitly calls cnx.Close()

if (cmd != null) cmd.Dispose();
}
}


////////////////////////////////////////////////////////////////////////
// CreateParameter Methods
////////////////////////////////////////////////////////////////////////
protected SqlParameter CreateParameter(string paramName,
SqlDbType paramType, object paramValue)
{
SqlParameter param = new SqlParameter(paramName, paramType);

if (paramValue != DBNull.Value)
{
switch (paramType)
{
case SqlDbType.VarChar:
case SqlDbType.NVarChar:
case SqlDbType.Char:
case SqlDbType.NChar:
case SqlDbType.Text:
paramValue = CheckParamValue((string)paramValue);
break;
case SqlDbType.DateTime:
paramValue = CheckParamValue((DateTime)paramValue);
break;
case SqlDbType.Int:
paramValue = CheckParamValue((int)paramValue);
break;
case SqlDbType.UniqueIdentifier:
paramValue = CheckParamValue(GetGuid(paramValue));
break;
case SqlDbType.Bit:
if (paramValue is bool) paramValue = (int)((bool)paramValue ? 1 : 0);
if ((int)paramValue < 0 || (int)paramValue > 1) paramValue = Constants.NullInt;
paramValue = CheckParamValue((int)paramValue);
break;
case SqlDbType.Float:
paramValue = CheckParamValue(Convert.ToSingle(paramValue));
break;
case SqlDbType.Decimal:
paramValue = CheckParamValue((decimal)paramValue);
break;
}
}
param.Value = paramValue;
return param;
}

protected SqlParameter CreateParameter(string paramName, SqlDbType paramType, ParameterDirection direction)
{
SqlParameter returnVal = CreateParameter(paramName, paramType, DBNull.Value);
returnVal.Direction = direction;
return returnVal;
}

protected SqlParameter CreateParameter(string paramName, SqlDbType paramType, object paramValue, ParameterDirection direction)
{
SqlParameter returnVal = CreateParameter(paramName, paramType, paramValue);
returnVal.Direction = direction;
return returnVal;
}

protected SqlParameter CreateParameter(string paramName, SqlDbType paramType, object paramValue, int size)
{
SqlParameter returnVal = CreateParameter(paramName, paramType, paramValue);
returnVal.Size = size;
return returnVal;
}

protected SqlParameter CreateParameter(string paramName, SqlDbType paramType, object paramValue, int size, ParameterDirection direction)
{
SqlParameter returnVal = CreateParameter(paramName, paramType, paramValue);
returnVal.Direction = direction;
returnVal.Size = size;
return returnVal;
}

protected SqlParameter CreateParameter(string paramName, SqlDbType paramType, object paramValue, int size, byte precision)
{
SqlParameter returnVal = CreateParameter(paramName, paramType, paramValue);
returnVal.Size = size;
((SqlParameter)returnVal).Precision = precision;
return returnVal;
}

protected SqlParameter CreateParameter(string paramName, SqlDbType paramType, object paramValue, int size, byte precision, ParameterDirection direction)
{
SqlParameter returnVal = CreateParameter(paramName, paramType, paramValue);
returnVal.Direction = direction;
returnVal.Size = size;
returnVal.Precision = precision;
return returnVal;
}


////////////////////////////////////////////////////////////////////////
// CheckParamValue Methods
////////////////////////////////////////////////////////////////////////
protected Guid GetGuid(object value)
{
Guid returnVal = Constants.NullGuid;
if (value is string)
returnVal = new Guid((string)value);
else if (value is Guid)
returnVal = (Guid)value;

return returnVal;
}

protected object CheckParamValue(string paramValue)
{
if (string.IsNullOrEmpty(paramValue))
return DBNull.Value;
else
return paramValue;
}

protected object CheckParamValue(Guid paramValue)
{
if (paramValue.Equals(Constants.NullGuid))
return DBNull.Value;
else
return paramValue;
}

protected object CheckParamValue(DateTime paramValue)
{
if (paramValue.Equals(Constants.NullDateTime))
return DBNull.Value;
else
return paramValue;
}

protected object CheckParamValue(double paramValue)
{
if (paramValue.Equals(Constants.NullDouble))
return DBNull.Value;
else
return paramValue;
}

protected object CheckParamValue(float paramValue)
{
if (paramValue.Equals(Constants.NullFloat))
return DBNull.Value;
else
return paramValue;
}

protected object CheckParamValue(Decimal paramValue)
{
if (paramValue.Equals(Constants.NullDecimal))
return DBNull.Value;
else
return paramValue;
}

protected object CheckParamValue(int paramValue)
{
if (paramValue.Equals(Constants.NullInt))
return DBNull.Value;
else
return paramValue;
}
}
}

Reading File Tags in VB.Net

Hi All,

There are times when we add the tags to our files. We can use the Shell32 folder to read the tags. I found a little problematic for the first timer, so adding the code to read the tags here :



Sub TestTags()
Dim fInfo As New FileInfo("Path of your File goes here")
Dim tags As String = ReadFileAttributes(fInfo)
End Sub

Public Function ReadFileAttributes(fInfo As FileInfo) As String

Dim shell As New Shell32.Shell
Dim objFolder As Shell32.Folder
Dim items As Shell32.FolderItems
Try
objFolder = shell.NameSpace(fInfo.Directory.FullName)
items = objFolder.Items
Return objFolder.GetDetailsOf(items.Item(fInfo.Name), 18)
Catch ex As Exception
Return "Caught Exception in Tags" 'exception..cannot find the tags
End Try

End Function

.

You have to add a reference to Microsoft Shell Controls and Automation from the COM tab of the Refences dialog.

HTH,
Vikas

Friday, September 2, 2011

How to Add a Reference in VBA Editor


Hi All!

Although VBA edit does provide loads of libraries inherently, still, we may need to use few libraries which are not included by default. For example, For using Scripting.Dictionary object, or FileSystem object to search the fileSystem in an organized way, we need to add a reference to Microsoft.Scripting.Runtime.

Adding a reference purely means to instruct the VBA Compiler/Editor to include the functionality of a particular library so that we can use the functions available in the added library.

If you try to create a FileSystemObject, and write the following code:
Dim dictionary_ as Scripting.Dictionary



If we try to compile or run the code with out adding the reference it will show us the following error :



Add the reference to Microsoft.Scripting.Runtime as shown in the picture below. Go to Tools --> Reference --> Look for Microsoft.Scripting.Runtime. If you are unable to find it in the options, then you will need to browse for scrrun.dll.




Thanks,
Vikas

Tuesday, August 30, 2011

Ever Wondered for Obfuscating VSTO .net Solutions? Here is the solution by Michael Zlatkovsky

Hi All,

I tried to obfuscate my VSTO COM code lots of time, but it never used to register the DLLs and Excel was never able to call my DLL successfully after Obfuscating. Although I have not tried it yet, but it seems we have got the solution :)

This is posted from a MSDN Forum thread. It was posted by Michael Zlatkovsky and is very very useful. Please see what he had posted :



Just to report back: after a few weeks of trying out various obfuscators, I finally did manage to obfuscate my VSTO project, both cheaply (only $179) and (now that I've got everything configured properly) painlessly.

The obfuscator that I settled on is called .NET Reactor (http://www.eziriz.com/dotnet_reactor.htm). It is VERY well-engineered piece of software, and costs remarkably little, $179 (compared to, say, Preemptive's Dotfuscator, which even after I emailed them and explained that I'm a freelance developer with little money, the best they could do is drop the price from ~$2000 to a "mere" $995). Unlike some of the other obfuscators, .NET Reactor works with VSTO solutions, and, also very importantly, it integrates with Visual Studio to make the obfuscation process just part of the build/publish one-click process. The importance of this integration is something you'll only discover when you need to obfuscate and sign assemblies manually -- which is a very tedious, 10-minute process of copying a file, obfuscating it, copying it back, re-signing the .dll.deploy file with MAGE, re-signing the .vsto file with MAGE, etc.

.NET Reactor's chief developer, Denis Mierzwiak, was VERY helpful and available when I needed some help in configuring .NET Reactor for VSTO. Initially my project was not lending itself to obfuscation, but Denis spent several hours with me on Skype to get things working, and kept sending me new releases by email as we continued to work out the kinks in the system. The Obfuscation now works 100%, integrates beautifully into Visual Studio, and is completely painless -- I actually can't recall a time when I was more pleased with a software product or with the level of technical support!

For those who'll be using .NET Reactor for VSTO, just a few hints:
1) It might be that the latest changes to the .NET Reactor (introduced for better VSTO support and integration) are still not in the "official" release -- you might want to email support@eziriz.com to ask about obfuscating an Office solution and to get the latest version.

2) It IS possible to fully obfuscate a VSTO solution (including renaming public types); since you are obfuscating a .dll file, however, that option is disabled by default. To enable it, go to the Settings tab, option #2 (Protection Settings), Obfuscation, and select "obfuscate public types".

3) Once you obfuscate public types, you will need to add a regular expression to EXCLUDE the several classes that VSTO expects (under .NET Reactor's settings -> Protections Settings -> Obfuscation -> Exclusions -> Regular Expressions). In the case of an Excel spreadsheet, those would be ExcelSampleWorkbook\.Sheet1;ExcelSampleWorkbook\.Sheet2;ExcelSampleWorkbook\.Sheet3;ExcelSampleWorkbook\.ThisWorkbook

4) Finally, if you obfuscate public types but exclude the required VSTO objects from obfuscation, be aware that code in those files (Sheet1, ThisWorkbook, etc) will obviously NOT be obfuscated. In my case, I created a "HelperDelegate" for each of those classes (i.e., ThisWorkbookDelegate) to hide the code behind the delegate. All that my Excel objects carry are event handlers (things like BeforeDoubleClick) and a reference to the delegate (so I can call Globals.ThisWorkbook.helper.myFunctionName())

Hope my experience is useful!

- Michael Zlatkovsky



HTH,
Vikas

Friday, August 26, 2011

Removing Duplicates with in the worksheet

Hello All,

I had asked by someone to create a code for removing a duplicate from worksheets.

The requirement was such that one keyword should only appears once in Column B in all the worksheets. So if a value (for example "Data-1234") appears in sheet 1 in column B, then it cannot appear again in Column B, in any of the worksheets. If it appears, then move it to Duplicate sheet.

So I used the following code. Thought it might be a help so posting it here. To make it work, you will need to add a reference to Microsoft Scripting Runtime.



Const ColumnToSearch As String = "B"
Const ColumnToSearch_i As Integer = 2
Private duplicates As Scripting.Dictionary

Sub MoveDuplicates()

Dim blankCounter As Integer
Dim rowCounter As Long
Dim duplicate As Worksheet
Dim sht As Worksheet
Dim runLoop As Boolean
Dim value As String
Dim added As Boolean
Dim lastRow As Long

runLoop = True


Set duplicates = New Scripting.Dictionary


Set duplicate = GetWorksheet(ActiveWorkbook, "DuplicateEntries")

If (duplicate Is Nothing) Then
Set duplicate = ActiveWorkbook.Worksheets.Add
duplicate.name = "DuplicateEntries"
End If


'get duplicate sheet

For Each sht In ActiveWorkbook.Worksheets

blankCounter = 1
rowCounter = 2
runLoop = True




If (sht.name <> "DuplicateEntries") Then

lastRow = GetLastRow(duplicate, 1)

If (lastRow > 1) Then
lastRow = lastRow + 2
End If

'enter the log

duplicate.Range("A" & lastRow).value = "Duplicate Entries Entered on : " & Now & " from Worksheet " & sht.name
duplicate.Range("A" & lastRow + 1).value = "'-"
duplicate.Range("A" & lastRow + 2).value = "'-"




While runLoop
If (blankCounter > 10) Then
runLoop = False
Else
'check blank

value = Trim(sht.Cells(rowCounter, ColumnToSearch_i).value)


If (value = "") Then
blankCounter = blankCounter + 1
Else
blankCounter = 1
added = AddSuccess(value)
lastRow = GetLastRow(duplicate, 1) + 1
If (added) Then
'dont do anything. not a duplicate
Else
'Duplicate item, Move
MoveRow sht.Cells(rowCounter, ColumnToSearch_i), duplicate.Cells(lastRow, 1)
rowCounter = rowCounter - 1
End If

End If


End If

rowCounter = rowCounter + 1

Wend
End If
Next sht


End Sub

Private Function GetLastRow(sht As Worksheet, Optional Col As Integer = 1) As Long
Dim rng As Range

GetLastRow = sht.Cells(sht.Rows.Count - 1, Col).End(xlUp).Row

End Function

Private Function GetWorksheet(wkb As Workbook, shtName As String) As Worksheet
Dim sht As Worksheet

On Error Resume Next
Set GetWorksheet = wkb.Worksheets(shtName)
Err.Clear

End Function

Private Sub MoveRow(rngToMove As Range, moveAt As Range)
rngToMove.EntireRow.Copy moveAt
rngToMove.EntireRow.Delete
End Sub

Private Function AddSuccess(name As String) As Boolean
Err.Clear
On Error Resume Next
duplicates.Add name, name

If (Err.Description <> "") Then
AddSuccess = False
Else
AddSuccess = True
End If

Err.Clear
End Function




HTH,
Vikas