Tuesday, 24 March 2009

DataTable not updating when clicked on CheckBox in DataGridView

When you have DataGridView with checkbox column and you click on the checkbox the underlying DataTable is not updated correctly. DataTable is only updated if you move to other row. Not sure why it's like that, but this is a workaround.

public void Save(){
DataTable dataTable1 = ((DataTable)dataGridView.DataSource).GetChanges();
//dataTable1 is NULL

//revalidate form (fixes the problem)
this.ValidateChildren();

DataTable dataTable2 = ((DataTable)dataGridView.DataSource).GetChanges();
//dataTable2 has correct values now
}

Open webpage from C# GUI

private void button_Click(object sender, EventArgs e)
{
try
{
string target = "http://itdevspace.blogspot.com/";
System.Diagnostics.Process.Start(target);
}
catch (Exception ex)
{
MessageBox.Show("Failed to open webpage");
}
}

Monday, 23 March 2009

Windows Live Writer and Code Snippet plugin - extra BR lines problem with Blogger

To get rid of extra BR lines when posting to blogger with Windows Live Writer and Code Snippet plugin make sure you set the Markup Type to HTML (rather then XHTML) in Advanced Setting for your blog in Windows Live Writer.

Friday, 20 March 2009

Transaction management in SQL Server 2005

This is an example of how to use try/catch functionality in SQL Server 2005 to manage transactions.

USE [MyDatabase]

PRINT 'Moving Data'
GO

BEGIN TRY
  BEGIN TRAN

    PRINT 'Inserting..'
    INSERT INTO table2(Id,ParentId,Name)
    SELECT t1.Id, t1.ParentId, t1.Name
    FROM Table1 t1
    WHERE t1.ParentId = 1

    PRINT 'Deleting..'
    DELETE FROM [Table1] i
    WHERE t1.ParentId = 1    

  COMMIT TRAN

END TRY
BEGIN CATCH
 
  ROLLBACK TRAN 
 
END CATCH 
GO

SQL Contraints in CREATE TABLE statement

CREATE TABLE [dbo].[Table1](
  [Id] [int] NOT NULL,
  [ParentId] [int] NOT NULL,
  [Name] [datetime] NULL,
  [Date] [datetime] NULL
  CONSTRAINT [DF_Table1_Date] DEFAULT getdate(), --default
  CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED  -- primary key
  (
    [Id] ASC
  ),
  CONSTRAINT [UQ_Table1_Name] UNIQUE NONCLUSTERED --unique key
  (
    [Name] ASC
  ),
  CONSTRAINT [FK_Table1_Table2_ParentId] FOREIGN KEY --foreign key
  (
    [ParentId]
  ) REFERENCES [Table2](
    [Id]
  )
)

Wednesday, 18 March 2009

Define custom application configuration section in C#

App.config section configuration

<configuration>
  <configSections>
    <sectionGroup name="section1">
      <section name="nvpconfig" type="MyCompany.NameValueConfiguration,MyCompany"/>
      <section name="customconfig" type="MyCompany.CustomConfiguration,MyCompany" />
    </sectionGroup> 
  </configSections>
  <section1>
    <nvpconfig>
      <properties>
        <add name="id" value="1" />
        <add name="name" value="One" />
        <add name="age" value="18" />
      </properties>
    </nvpconfig>
    <customconfig>
      <list>
        <add id="1" name="One" age="18"/>
        <add id="2" name="Two" age="21"/>
      </list>
    </customconfig>
  </section1>
</configuration>  

Simple name value pair section

namespace MyCompany
{
  public class NameValueConfiguration : System.Configuration.ConfigurationSection
  {
    [ConfigurationProperty("properties")]
    public NameValueConfigurationCollection properties
    {
      get
      {
        return (NameValueConfigurationCollection)this["properties"] ?? new NameValueConfigurationCollection();
      }
    }
  }
}

Custom section definition

namespace MyCompany
{
  public class CustomConfiguration : ConfigurationSection
  {
    [ConfigurationProperty("list")]
    public CustomConfigurationCollection jobs
    {
      get
      {
        return (CustomConfigurationCollection)this["list"] ?? new CustomConfigurationCollection();
      }
    }       
  }
  
  public class CustomConfigurationCollection : ConfigurationElementCollection
  {
    public CustomConfigurationElement this[int index]
    {
      get
      {
        return base.BaseGet(index) as CustomConfigurationElement;
      }
      set
      {
        if (base.BaseGet(index) != null)
        {
          base.BaseRemoveAt(index);
        }
          this.BaseAdd(index, value);
      }
    }
    protected override ConfigurationElement CreateNewElement()
    {
      return new CustomConfigurationElement();
    }
    protected override object GetElementKey(ConfigurationElement element)
    {
      return ((CustomConfigurationElement) element);
    }
  } 
  
  public class CustomConfigurationElement : ConfigurationElement
  {
    [ConfigurationProperty("id")]
    public string Id
    {
      get { return (string)this["id"]; }
      set { this["id"] = value; }
    }
    [ConfigurationProperty("name")]
    public string Name
    {
      get { return (string)this["name"]; }
      set { this["name"] = value; }
    }
    [ConfigurationProperty("age")]
    public string Age
    {
      get { return (string)this["age"]; }
      set { this["age"] = value; }
    }
  }  
}

Reading/Writing C# Application Settings in runtime

This is example how to access defined application settings in runtime
public static string MyStringSetting
{
  get { return Properties.Settings.Default.MyStringSetting; }
  set
  {
    Properties.Settings.Default.MyStringSetting = value;
    Properties.Settings.Default.Save();
  }
}
public static List<string> MyStringCollectionSetting
{
  get { return toStringList(Properties.Settings.Default.MyStringCollectionSetting); }
  set
  {
    Properties.Settings.Default.MyStringCollectionSetting = fromStringList(value);
    Properties.Settings.Default.Save();
  }
}
private static List<string> toStringList(StringCollection sc)
{
  List<string> list = new List<string>();
  if (sc != null)
  {
    foreach (string s in sc)
    {
      list.Add(s);
    }
  }
  return list;
}
private static StringCollection fromStringList(List<string> list)
{
  StringCollection sc = new StringCollection();
  if (list != null)
  {
    foreach (string s in list)
    {
      sc.Add(s.ToString());
    }
  }
  return sc;
}

Read/Write text file in C#

//read file
using (TextReader tr = new StreamReader("sample.txt"))
{
  string line = tr.ReadLine();
}
//write file
using (TextWriter tw = new StreamWriter("sample.txt"))
{
  tw.WriteLine("text");
}

Using ComboBox with DataTable in C#

Define the ComboBox with DataTable

//define DataTable
DataTable dt = new DataTable("dataTable");
dt.Columns.Add("Id", typeof(int));
dt.Columns.Add("Name", typeof(string));
//add DataRow
DataRow row = dt.NewRow();
row["Id"] = 1;
row["Name"] = "One";
dt.Rows.Add(row);
//assign to ComboBox
comboBox.DataSource = dt;
comboBox.DisplayMember = "Name";
comboBox.ValueMember = "Id";

Get Selected data from ComboBox

DataRow row = ((DataTable)comboBox.DataSource).Rows[comboBox.SelectedIndex];
int Id = (int)row["Id"];
string Name = (string)row["Name"];

Using GridView with DataTable in C#

Define the GridView with DataTable

//define DataTable
DataTable dt = new DataTable("dataTable");
dt.Columns.Add("Id", typeof(int));
dt.Columns.Add("Name", typeof(string));
//add DataRow
DataRow row = dt.NewRow();
row["Id"] = 1;
row["Name"] = "One";
dt.Rows.Add(row);
//assign to gridView
gridView.DataSource = dt;

Get data from selected Row in GridView

//get data from selected row
private void gridView_SelectionChanged(object sender, EventArgs e)
{
  if (gridView.SelectedCells.Count > 0)
  {
    int seletedRow = gridView.SelectedCells[0].RowIndex;
    DataRow row = ((DataTable) gridView.DataSource).Rows[seletedRow];
    int id = (int)row["Id"];
    string name = (string)row["Name"];
  }
}

Change Column colours

private void gridView_CellFormatting(object sender, DataGridViewCellFormattingEventArgs e)
{            
  DataGridViewRow row = gridView.Rows[e.RowIndex];
  if (e.RowIndex < ((DataTable)gridView.DataSource).Rows.Count)
  {
    if (((DataTable)gridView.DataSource).Rows[e.RowIndex].RowState != DataRowState.Unchanged)
    {
      row.DefaultCellStyle.BackColor = Color.LightYellow;
    }
  }else{
    row.DefaultCellStyle.BackColor = Color.LightYellow;
  }
}

Start/Stop windows service from C#

using System.ServiceProcess;
public void Test()
{
  ServiceController controller = new ServiceController();
  controller.MachineName = ".";
  controller.ServiceName = "MyService";
  
  controller.Stop();            
  //wait until stopped
  controller.Start();
  //wait until started
  controller.Refresh();   
  Console.WriteLine(controller.Status.ToString());
}

Zip/Unzip files in C#

DotNetZip is an OpenSource Zip library for C#

public void ZipDir(string directoryPath, string zipFilePath)
{
  using(ZipFile zip = new ZipFile())
  {
    zip.AddDirectory(directoryPath);
    zip.Save(zipFilePath);
  }            
}
public void UnzipDir(string zipFilePath, string directoryPath)
{
  using(ZipFile zip = ZipFile.Read(zipFilePath))
  {                                
    zip.ExtractAll(directoryPath,true);                
  }            
}
public void UnzipFile(string zipFilePath, string directoryPath, string fileName)
{
  using (ZipFile zip = ZipFile.Read(zipFilePath))
  {
    zip.Extract(fileName, directoryPath, true);
  }
}

Monday, 16 March 2009

How to quickly compare 2 tables in SQL Server

This is the fastest way to compare 2 tables in SQL Server. Generate hash for both tables and see if it's the same.

SELECT checksum_agg(checksum (*)) FROM table1

Friday, 13 March 2009

List view/table/storedprocedure details including creation, modify date

This system tables allow you to see the list of views, tables, stored procedures in the database as well as information like creation, modify date.

SELECT * FROM MyDb.sys.views
SELECT * FROM MyDb.sys.tables
SELECT * FROM MyDb.sys.procedures

Thursday, 12 March 2009

Add/Remove privilages from Database

Grant/Revoke privileges on Tables

Privilages: Select, Insert, Update, Delete, References, Alter, Index References - Ability to create contraint that refers to table Alter - Ability to alter table definition Index - Ability to create index

Grant/Revoke privileges on Functions/Procedures

Privilages: Execute Examples:
GRANT Select, Update ON Table1 TO MyDbUser
REVOKE Select, Update ON Table1 FROM MyDbUser

GRANT Execute ON MyStoredProcedure TO MyDbUser
REVOKE Execute ON MyStoredProcedure FROM MyDbUser

Tuesday, 10 March 2009

Implementing Generic method in C# to print contents of List and Dictionary

public static string ToString<T>(List<T> list)
{
  StringBuilder builder = new StringBuilder();
  if (list != null)
  {
    foreach (T t in list)
    {
      builder.Append(t.ToString());
    }
  }
  return builder.ToString();
}
public static string ToString<T,K>(Dictionary<T,K> dictionary)
{
  StringBuilder builder = new StringBuilder();
  if (dictionary != null)
  {
    foreach (KeyValuePair<T, K> pair in list)
    {
      builder.Append(pair.ToString());
    }
  }
  return builder.ToString();
}

Add ToString() to Resharper Code/Generate menu

To autogenerate ToString() method with Resharper (Alt+Ins) download JetBrains PowerToys The installer will create directory structure in C:\Program Files\JetBrains\ReSharper\v3.0\ If you're using different version v3.x make sure to copy the contents of \v3.0\vs8.0\PowerToys and \v3.0\vs8.0\Bin\Plugins to \v3.x folder.

Monday, 9 March 2009

Load Assembly at runtime from another dll in C#

This example shows how to load a specialized version of Component implementation from from another dll during runtime. Define an interface in a Common project

namespace MyCompany.Common
{
  public interface Component
  {
    void Execute();
  }
}

Create specialized library that will be loaded at runtime and add reference to Common project.

using MyCompany.Common;
namespace MyCompany.Custom
{
  public class SampleComponent : Component
  {
    public void Execute()
    {
      Console.WriteLine("Sample Component");
    }
  }
}

Create your Console/Windows project and add a reference to Common project. Do not add reference to Custom one as this assembly will be loaded at runtime.

using MyCompany.Common;
namespace MyCompany.MyProgram
{
  static class Program
  {
    public static void Main()
    {
      string path = @"{your.dll.directory}\MyCompany.Custom.dll";
      Assembly a = Assembly.LoadFile(path);
      Component c = (Component)a.CreateInstance("MyCompany.Common.SampleComponent");
      c.Execute();
    }
  }
}

Friday, 6 March 2009

C# Define and execute bean from the spring configuration file

Create spring configuration file spring.xml with a sample bean definition

<?xml version="1.0" encoding="utf-8" ?>
<objects xmlns="http://www.springframework.net">
  <object id="MyClass" type="MyCompany.MyClass,MyCompany" >
    <property name="name" value="John"/>
  </object>
</objects>

Use XmlApplicationContext to access bean

namespace MyCompany
{
    [TestFixture]
    public class Test
    {
        public void Test1()
        {            
            IApplicationContext context = new XmlApplicationContext("spring.xml");
            IObjectFactory factory = context;
            MyClass obj = (MyClass)factory["MyClass"];            
            obj.Test();
        }
    }
    public class MyClass
    {
        private string name;
        public string Name
        {
            get { return name; }
            set { name = value; }
        }
        public void Test()
        {
            Console.WriteLine(string.Format("My name is {0}", name));
        }
    }
}