Monday, February 6, 2012

Loop through all the files in C# 2010

Hi All,

I migrated from VBA to C# a while back. Earlier, with VBA, I used to create Re-Cursive function to loop through all inner folders in a given folder. For example, if I have to search whole files in C: Drive, I will probably need to loop through Drive, then folders and subfolders in VBA. But in C#, you can simply give a parameter to search through the subdirectories as well.

Code:

DirectoryInfo parent = new DirectoryInfo(@"C:\Vikas");
FileInfo[] files = parent.GetFiles("*.*", SearchOption.AllDirectories);


The files array will contain all the files within the Folder "Vikas" and it's subfolder.

Thanks,
Vikas B

Org Charts using Google docs

Hi All,

I was reading a thread in LinkedIn. Someone had asked if it is possible to create a ORG chart in Excel or not. I tried to google it and found a very interesting way to do it using Google Docs.

Create Org Chart using Google Docs/

Cheers!
Vikas B

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