Showing posts with label c#. Show all posts
Showing posts with label c#. Show all posts

Thursday, November 28, 2013

Get, Set variable methods to make code easily readable

1. Intead of declaring variables in Script task, when you always forget to declare them and then have errors during runtime - forget declaring, use easy and nice way
// No need to declare READ, WRITE variable and short form of accessing
private T Get<T>(string varName)
{
    Variables lockedVariables = null;
    T var;
    Dts.VariableDispenser.LockOneForRead(varName, ref lockedVariables);
    var = (T)lockedVariables[varName].Value;
    lockedVariables.Unlock();
    return var;
}

private void Set<T>(string varName, T value)
{
    Variables lockedVariables = null;
    Dts.VariableDispenser.LockOneForWrite(varName, ref lockedVariables);
    lockedVariables[varName].Value = value;
    lockedVariables.Unlock();
}
2. Here is example code to use variables
string someName = Get<string>("User::someName");
int someInt = Get<int>("User::someInt");
Set("User::content", "some text");
// OR 
Set<string>("User::content", "some text");

Friday, May 31, 2013

SP run helper

// Get Result from specific SP
int id   = 1;
int type = 0;
DataTable attributes = GetSpResults(conn
    , "[dbo].[sp_GetSomeAttributes]"
    , new SqlParameter("@id", (Int32)id)
    , new SqlParameter("@type", (Int32)type)
    );
   ...
// General request for DataTable
private DataTable GetSpResults(SqlConnection conn, string spName, params SqlParameter[] parms)
{
    SqlCommand cmd = new SqlCommand();
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = spName;
    foreach(SqlParameter parm in parms)
        cmd.Parameters.Add(parm);
    cmd.Connection = conn;

    SqlDataAdapter sqlAdapter = new SqlDataAdapter(cmd);
    DataTable dtable = new DataTable();
    sqlAdapter.Fill(dtable);

    return dtable;
}

Monday, May 27, 2013

run once block

string some_text;
string file = "test.txt";
// Run once and break where necessary
do
{
    // If no file then SKIP
    if (!File.Exists(file))
        break;
        
    if (any_condition_to_skip)
        break;

    // Sample code to execute
    using (StreamReader sr = new StreamReader(file))
    {
        some_text = sr.ReadToEnd();
    }

// Run once
} while (false);            

run external package with custom listener

Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
CustEventListener eventListener = new CustEventListener(Dts);
// Load package
string packageName = "test.dtsx";
Package childPackage = app.LoadPackage(packageName, eventListener);
// Execute
DTSExecResult dtsResult = childPackage.Execute(null, null, eventListener, null, null);
if (dtsResult == DTSExecResult.Success)
{
    Log("Success running child package {0}", packageName);
}
  ...

public class CustEventListener : Microsoft.SqlServer.Dts.Runtime.DefaultEvents
{
    Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel dts;
    public CustEventListener(Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel dts)
    {
        this.dts = dts;
    }

    public override bool OnError(DtsObject source, int errorCode,string subComponent, string description, string helpFile,int helpContext, string idofInterfaceWithError)
    {
        dts.Events.FireError(errorCode, subComponent, description, helpFile, helpContext);
        return false;    
    }

    public override void OnInformation(DtsObject source, int informationCode,string subComponent, string description, string helpFile,int helpContext, string idofInterfaceWithError, ref bool fireAgain)
    {
        dts.Events.FireInformation(informationCode, subComponent, description, helpFile, helpContext, ref fireAgain);

    }

    public override void OnWarning(DtsObject source, int warningCode,string subComponent, string description, string helpFile,int helpContext, string idofInterfaceWithError)
    {
        dts.Events.FireWarning(warningCode, subComponent, description, helpFile, helpContext);
    }
}

logging methods for script component

Log("Some log text {0}, {1}, {2}", 1, 2, 3);
   or
Log("Error-{0}; Description-{1}\n", Get("System::ErrorCode"), Get("System::ErrorDescription"));

 ...

private void Log() { Log(new string('*', 20)); }
private void Log(string str, params object[] pars)
{
    bool fireAgain = false;
    Dts.Events.FireInformation(1, "custom", string.Format(str, pars), "", 0, ref fireAgain);
       or
    string logFile = "d:\\SSIS.log";
    File.AppendAllText(logFile, string.Format(str, pars));
}

Friday, May 24, 2013

helper methods to get, set package variables during long running script

Set package variable in Script component without long locking
Set("User::number", GetInt("User::number") + 1);
...
// Helper methods to be added to Script code
private int GetInt(string varName)
{
    Variables lockedVariables = null;
    int var;
    Dts.VariableDispenser.LockOneForRead(varName, ref lockedVariables);
    var = (int)lockedVariables[varName].Value;
    lockedVariables.Unlock();
    return var;
}
private string GetStr(string varName)
{
    Variables lockedVariables = null;
    string var;
    Dts.VariableDispenser.LockOneForRead(varName, ref lockedVariables);
    var = lockedVariables[varName].Value.ToString();
    lockedVariables.Unlock();
    return var;
}
private void Set(string varName, int value)
{
    Variables lockedVariables = null;
    Dts.VariableDispenser.LockOneForWrite(varName, ref lockedVariables);
    lockedVariables[varName].Value = value;
    lockedVariables.Unlock();
}
private void Set(string varName, string value)
{
    Variables lockedVariables = null;
    Dts.VariableDispenser.LockOneForWrite(varName, ref lockedVariables);
    lockedVariables[varName].Value = value;
    lockedVariables.Unlock();
}

Wednesday, May 22, 2013

some file related

1. drop empty files from folder
string[] files = Directory.GetFiles(dirName);
foreach (string file in files)
{
    FileInfo fi = new FileInfo(file);
    if (fi.Length == 0)
        fi.Delete();
}
2. append text to file
string msg = "some text";
bool   append = True;

using (TextWriter tw = new StreamWriter(file, append))
    tw.WriteLine(msg);
  .. or ..
using (StreamWriter sw = File.AppendText(file))
    sw.WriteLine(msg);
  .. or ..
File.AppendAllText(file, msg + "\n");

check if folder exists and can be read

using System.IO;
using System.Security.AccessControl;
...
bool dirExists = Directory.Exists(fileDir);
bool accessToDir = ReadAccess(fileDir);
if (dirExists && accessToDir)
    Console.Write("there is access");
    ...
public bool ReadAccess(string dir)
{
    bool allow = false;
    bool deny = false;
    DirectorySecurity dirSecurity = Directory.GetAccessControl(dir);
    if (dirSecurity == null)
        return false;
    AuthorizationRuleCollection rules = dirSecurity.GetAccessRules(true, true
            ,typeof(System.Security.Principal.SecurityIdentifier));
    if (rules == null)
        return false;
    foreach (FileSystemAccessRule rule in rules)
    {
        if ((FileSystemRights.Read & rule.FileSystemRights) == FileSystemRights.Read)
        {
            if (rule.AccessControlType == AccessControlType.Allow)
                allow = true;
            else if (rule.AccessControlType == AccessControlType.Deny)
                deny = true;
        }
    }
    return allow && !deny;
}

read last line from text file

use filestream to get last line from big text file

using System;
using System.IO;
using System.Text;

class Program
{
    static void Main(string[] args)
    {
        string[] files = Directory.GetFiles("d:\\", "*.txt");
        foreach (string file in files)
        {
            string lastLine = Util.ReadLastLine(file);
            Console.WriteLine("File [{0}] has next last line:\n\t - \"{1}\"", file, lastLine);
        }
        Console.ReadKey();
    }
}
class Util
{
    public static string ReadLastLine(string fileName)
    {
        string lastLine = null;
        int buffsize = 1000; // Should be set to regular Line Size * 2-3

        if (!File.Exists(fileName))
            return lastLine;

        using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
        {
            if (fs.Length == 0)
                return lastLine;

            // create buffer
            buffsize = (int)Math.Min(buffsize, fs.Length);
            byte[] buffer = new byte[buffsize]; 

            // Read last 1000 bytes from the end
            fs.Seek(-buffsize, SeekOrigin.End);
            fs.Read(buffer, 0, buffer.Length);
            // Convert to ASCII string
            string strBuffer = Encoding.ASCII.GetString(buffer, 0, buffer.Length);

            // get the lines
            string[] strArr = strBuffer.Split('\n');
            // get last line
            lastLine = strArr[strArr.Length - 1];
        }

        return lastLine;
    }
}