Tuesday, 30 December 2008

Using Spring.NET to load data to DataTable

public class Program
{
  static void Main(string[] args)
  {
    IDbProvider provider = new DbPath("server", "database").DbProvider;
    TestDao dao = new TestDao(provider);
    DataTable dt = dao.Select();
    foreach(DataRow row in dt.Rows)
    {
      Console.WriteLine(string.Format("{0}: {1}",row["Id"],row["Name"]));
    }
    Console.ReadLine();
  }
}
class DbPath
{
  private const string connectionString = @"Persist Security Info=False; Integrated Security=SSPI;
    Server={0}; Database={1}; Connect Timeout={2}";
      
  private readonly string server;
  private readonly string database;
  private int timeout = 30;
      
  public DbPath(string server, string database)
  {
    this.server = server;
    this.database = database;
  }
  
  public string ConnectionString
  {
    get { return string.Format(connectionString,server,database,timeout); }
  }
  public IDbProvider DbProvider
  {
    get
    {
      IDbProvider dbProvider = DbProviderFactory.GetDbProvider("System.Data.SqlClient");
      dbProvider.ConnectionString = ConnectionString;
      return dbProvider;
    }
  }
}
        
class TestDao : AdoDaoSupport
{
  private readonly string selectAllSql = @"SELECT Id, Name FROM Table1";
  public TestDao(IDbProvider dbProvider)
  {
    this.DbProvider = dbProvider;
  }
  public DataTable Select()
  {
    return AdoTemplate.DataTableCreate(CommandType.Text, selectAllSql);
  }
} 
class TestUpdateDao : AdoDaoSupport
{
  private readonly string deleteSql = @"DELETE FROM Table1 WHERE Id = @Id";
  public TestUpdateDao(IDbProvider dbProvider)
  {
    this.DbProvider = dbProvider;
  }
  public void delete(int id)
  {
    IDbParameters parameters = CreateDbParameters();
    parameters.Add("Id", DbType.Int32).Value = id;
    AdoTemplate.ExecuteNonQuery(CommandType.Text, deleteSql, parameters);
  }
} 

Friday, 19 December 2008

Disable C# Form resizing

form.FormBorderStyle = System.Windows.Forms.FormBorderStyle.FixedDialog;

Thursday, 18 December 2008

Open Excel from C# as an idependent process

System.Diagnostics.Process excel = new System.Diagnostics.Process();

excel.StartInfo.FileName = "excel.exe";
//Open as readonly
excel.StartInfo.Arguments = string.Format("/r \"{0}\"",@"c:\temp\test.xls");
excel.Start();

Reading XML file in C# using XPath expressions

An example how to read XML file using XPath expression to access elements and attributes. Sample XML file

<?xml version="1.0" encoding="utf-8"?>
<bookstore>
    <book genre="autobiography" publicationdate="1981-03-22" ISBN="1-861003-11-0">
        <title1>The Autobiography of Benjamin Franklin</title1>
        <author>
            <first-name>Benjamin</first-name>
            <last-name>Franklin</last-name>
        </author>
        <price>8.99</price>
    </book>
    <book genre="novel" publicationdate="1967-11-17" ISBN="0-201-63361-2">
        <title1>The Confidence Man</title1>
        <author>
            <first-name>Herman</first-name>
            <last-name>Melville</last-name>
        </author>
        <price>11.99</price>
    </book>
</bookstore>

Reader code

XPathDocument doc = new XPathDocument(@"c:\temp\books.xml");
XPathNavigator nav = doc.CreateNavigator();
XPathNodeIterator nodeIterator = nav.Select("/bookstore/book");
foreach (XPathNavigator node in nodeIterator)
{
    Console.WriteLine(node.Name + ": " + node.GetAttribute("genre", ""));
    foreach (XPathNavigator subNode in node.Select("author"))
    {
        Console.WriteLine("  "+subNode.Name);
    }
}

There is an interesting problem when you have namespace defined as below, the code above doesn't like it.

<?xml version="1.0" encoding="utf-8"?>
<bookstore xmlns="http://www.contoso.com/books">
    <book genre="autobiography" publicationdate="1981-03-22" ISBN="1-861003-11-0">
    ...

In this situation you need to use XmlNamespaceManager to define a namespace prefix (can be anything) and then use it in your XPath expressions.

XPathDocument doc = new XPathDocument(@"c:\temp\books1.xml");
XPathNavigator nav = doc.CreateNavigator();
XmlNamespaceManager manager = new XmlNamespaceManager(nav.NameTable);         
manager.AddNamespace("ns", "http://www.contoso.com/books");
XPathNodeIterator nodeIterator = nav.Select("/ns:bookstore/ns:book", manager);
foreach (XPathNavigator node in nodeIterator)
{
    Console.WriteLine(node.Name + ": " + node.GetAttribute("genre", ""));
    foreach (XPathNavigator subNode in node.Select("ns:author",manager))
    {
        Console.WriteLine("  " + subNode.Name);
    }
}

Monday, 15 December 2008

Check if database table, view, stored procedure exists

-- check if table exists
if exists (select * from dbo.sysobjects where id = object_id(N'[TableName]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
  -- do something
GO
-- check if view exists
if exists (select * from dbo.sysobjects where id = object_id(N'[ViewName]') and OBJECTPROPERTY(id, N'IsView') = 1)
  -- do something
GO
-- check if stored procedure exists
if exists (select * from dbo.sysobjects where id = object_id(N'[StoredProcedureName]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
  -- do something
GO

SQL Server show user permissions for an object

EXEC sp_helprotect Table

SQL Server show database roles

EXEC sp_helprolemember

SQL Copy data from one table to another using join

UPDATE @SampleTable
SET Name = t.Name
FROM @SampleTable s
JOIN Table1 t
on t.Id = s.Id

SQL Table variable

DECLARE @SampleTable TABLE(
 Id VARCHAR(50) NOT NULL PRIMARY KEY,
 name VARCHAR(50)
)

Excel FaceID icons

This code shows toolbar with FaceID icons from 1 to 500. Updated the FOR loop to show the rest. There are few thousands of them.
Sub CreateFaceIDMenu()
    Dim MyBar As CommandBar
    Dim MyPopup As CommandBarPopup
    Dim MyButton As CommandBarButton
    Dim i

    DeleteFaceIDMenu

    Set MyBar = CommandBars.Add(Name:="FaceID Icons", _
        Position:=msoBarFloating, temporary:=True)

    With MyBar
        For i = 1 To 500
            Set MyButton = .Controls.Add(Type:=msoControlButton)
            With MyButton
                .Style = msoButtonIcon
                .FaceId = i
                .Caption = i
                .Visible = True
            End With
        Next

        .Width = Application.Width
        .Top = 4 / 3 * (Application.Top + Application.Height) - .Height - 50
        .Left = 4 / 3 * Application.Left + (4 / 3 * Application.Width - .Width) / 2 '850
        .Visible = True
End With
    
End Sub

Sub DeleteFaceIDMenu()
    On Error Resume Next
    CommandBars("FaceID Icons").Delete
    On Error GoTo 0
End Sub

Create custom menu in Excel VBA

Public Sub CreateMenu()

    Dim MenuObject As CommandBarPopup
    Dim MenuItem As Object
    Dim SubMenuItem As Object

    On Error Resume Next
    DeleteMenu

    Set MenuObject = Application.CommandBars(1). _
        Controls.Add(Type:=msoControlPopup, _
        Before:=10, _
        Temporary:=True)
    MenuObject.Caption = "&SampleMenu"

' Add menu Item with Icon (FaceId=424)    
    Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
    MenuItem.OnAction = "RefreshOnly"
    MenuItem.Caption = "&Menu Item 1"
    MenuItem.FaceId = 424
        
' Add another Menu Item after divider
    Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
    MenuItem.OnAction = "RefreshBondsVsRepos"
    MenuItem.Caption = "M&enu Item 2"
    MenuItem.FaceId = 425
    MenuItem.BeginGroup = True

End Sub

Public Sub DeleteMenu()

    On Error Resume Next
    Application.CommandBars(1).Controls("SampleMenu").Delete

End Sub
To get a list of FaceId icons see this

Saturday, 13 December 2008

Convert datetime to varchar as yyyyMMdd in SQL Server

SELECT 
  CONVERT(CHAR(8), [datetimecolumn], 112)
FROM [table]
More info about CONVERT

Friday, 12 December 2008

INKSCAPE - Open Source vector graphics editor

Quite impressive open source vector graphics editor similiar to Adobe Illustrator. www.inkscape.org Make sure you check the Tutorials section under Help menu.

Thursday, 11 December 2008

Autosys Reference

* Listing Job/Box Information
autorep -J [job/box name]     //summary
autorep -J [job/box name] -d  //details
autorep -J [job/box name] -q  //definition (jil)
autorep -r -2 -J [job/box name] -d  //historical run info (-2 means 2 runs before)
use % as wildcard for job/box name listing
ex: autorep -J myjob1
autorep -J myjob%
* Starting/Stopping Jobs
sendevent -E [event] -J [job_name]
Events
- FORCE_STARTJOB
- KILLJOB
- JOB_ON_ICE
- JOB_OFF_ICE
- JOB_ON_HOLD
- JOB_OFF_HOLD
* Change Job Status
sendevent -E CHANGE_STATUS -s SUCCESS -J
Statues
- SU - SUCCESS
- RU - RUNNING
- AC - ACTIVE
- IN - INACTIVE
- FA - FAILED
- TE - TERMINATED
- OI - ON ICE
* Run JIL file
cat [filename].jil | jil
* Check if machine has autosys agent installed
autoping -m <machine-name> -D
* Tracks and reports changes to autosys DB
autotrack -J [job/box name] -v
autotrack -U [username] –v
You can find more info here Official Autosys Cheatsheet

Wednesday, 10 December 2008

C# Updating GUI from different thread

void MyEventHandler(object sender, EventArgs e)
{
if (this.InvokeRequired)
{
this.Invoke(new EventHandler(MyEventHandler), new object[] { sender, e }
);
}else{
//update GUI here
}
}
void MyEventHandler(object sender, MyEventArgs e)
{
if (this.InvokeRequired)
{
this.BeginInvoke(new EventHandler<MyEventArgs>(MyEventHandler), new object[] { sender, e });
}
else
{
//update GUI here
}
}

Events in C#


public class Messenger
{
public delegate void MessageEventHandler(object source, MessageEvent e);

public event MessageEventHandler changed;

public void Run()
{
fireMessageEvent("Message");
}

private void fireMessageEvent(string text)
{
log.Info(text);
if (changed != null)
{
changed(this, new MessageEvent(text));
}
}
}

public class MessageEvent : EventArgs
{

private readonly string message;

public MessageEvent(string message)
{
this.message = message;
}

public string Message
{
get { return this.message; }
}
}

public class Client
{
Messenger messenger = new Messenger();

public Client(){
messenger.changed += new Messenger.MessengerEventHandler(messenger_changed);
}

void messenger_changed(object source, MessageEvent e)
{
Console.WriteLine(e.Message);
}
}

Autoscroll in C# RichTextBox

using System.Runtime.InteropServices;
#region WinAPI
//WinAPI-Declaration for SendMessage
[DllImport("user32.dll")]
public static extern IntPtr SendMessage(
IntPtr window, int message, int wparam, int lparam);
const int WM_VSCROLL = 0x115;
const int SB_BOTTOM = 7;
#endregion
SendMessage(richTextBox1.Handle, WM_VSCROLL, SB_BOTTOM, 0);

Welcome!

Welcome to IT Development Tips & Tricks!