Wednesday, 29 February 2012

Custom LINQ to SQL Query with dynamic result

[TestFixture]
public class TestDynamicLinq2Sql
{
    [Test]
    public void TestDynamicLinq()
    {
        var ctx = new MyDataContext("<connection string>");
        var result = ctx.ExecuteQuery("SELECT USER [User], SUSER_SNAME() [SysUser]");
        foreach (dynamic row in result)
        {
            Console.WriteLine("{0},{1}", row.User, row.SysUser);
        }
    }
    [Test]
    public void TestDynamicRow()
    {
        dynamic d = new DynamicRow();
        d.FirstName = "Albert";
        d["LastName"] = "Einstein";
        Console.WriteLine(d.FirstName);
        Console.WriteLine(d.LastName);
    }
}
public static class LinqExtensions
{
    public static IEnumerable<dynamic> ExecuteQuery(this DataContext ctx, string query)
    {            
        using (DbCommand cmd = ctx.Connection.CreateCommand())
        {
            cmd.CommandText = query;
            ctx.Connection.Open();
            using (DbDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                while (rdr.Read())
                {
                    dynamic row = new DynamicRow();                        
                    for (int i = 0; i < rdr.FieldCount; i++)
                    {                      
                        row[rdr.GetName(i)] = rdr[i];
                    }
                    yield return row;
                }
            }
        }
    }
}
public class DynamicRow : DynamicObject
{
    private readonly Dictionary<string,object> _data = new Dictionary<string, object>();
    public object this[string propertyName]
    {
        get
        {
            object result = null;
            TryGetMember(propertyName, out result);
            return result;
        }
        set { TrySetMember(propertyName, value); }
    }
    public override bool TryGetMember(GetMemberBinder binder, out object result)
    {
        return TryGetMember(binder.Name, out result);
    }
    private bool TryGetMember(string propertyName, out object result)
    {
        return _data.TryGetValue(propertyName.ToLower(), out result); 
    }
    public override bool TrySetMember(SetMemberBinder binder, object value)
    {
        return TrySetMember(binder.Name, value);
    }
    private bool TrySetMember(string propertyName, object value)
    {
        _data[propertyName.ToLower()] = value;
        return true;
    }
}

Tuesday, 21 February 2012

Create chart in C# Windows Forms and save it to file

static class Program
{
    [STAThread]
    static void Main()
    {
        Application.EnableVisualStyles();
        Application.SetCompatibleTextRenderingDefault(false);
        using (var chartForm = new ChartForm())
        {
            FillData(chartForm.Chart);
            chartForm.Show();
            //save
            chartForm.Chart.SaveImage(@"Chart.png", ChartImageFormat.Png);
            chartForm.Close();
        }
    }
    public static void FillData(Chart chart)
    {
        chart.Titles[0].Text = "Price/Volume Chart";
        chart.Series[0].Name = "Price";
        chart.Series[1].Name = "Volume";
        var random = new Random();
        for (int i = 0; i < 10; i++)
        {
            var date = DateTime.Today.AddDays(i);
            chart.Series[0].Points.Add(new DataPoint(date.ToOADate(), random.NextDouble() * 1e3));
            chart.Series[1].Points.Add(new DataPoint(date.ToOADate(), random.Next() / 1e3));
        }
    }
}
chart
//Designer Code        
private void InitializeComponent()
{
    System.Windows.Forms.DataVisualization.Charting.ChartArea chartArea1 = new System.Windows.Forms.DataVisualization.Charting.ChartArea();
    System.Windows.Forms.DataVisualization.Charting.ChartArea chartArea2 = new System.Windows.Forms.DataVisualization.Charting.ChartArea();
    System.Windows.Forms.DataVisualization.Charting.Legend legend1 = new System.Windows.Forms.DataVisualization.Charting.Legend();
    System.Windows.Forms.DataVisualization.Charting.Series series1 = new System.Windows.Forms.DataVisualization.Charting.Series();
    System.Windows.Forms.DataVisualization.Charting.Series series2 = new System.Windows.Forms.DataVisualization.Charting.Series();
    System.Windows.Forms.DataVisualization.Charting.Title title1 = new System.Windows.Forms.DataVisualization.Charting.Title();
    this.chart1 = new System.Windows.Forms.DataVisualization.Charting.Chart();
    ((System.ComponentModel.ISupportInitialize)(this.chart1)).BeginInit();
    this.SuspendLayout();
    // 
    // chart1
    // 
    chartArea1.AxisX.LabelStyle.Enabled = false;
    chartArea1.Name = "ChartArea1";
    chartArea2.AlignWithChartArea = "ChartArea1";
    chartArea2.Name = "ChartArea2";
    this.chart1.ChartAreas.Add(chartArea1);
    this.chart1.ChartAreas.Add(chartArea2);
    this.chart1.Dock = System.Windows.Forms.DockStyle.Fill;
    legend1.Alignment = System.Drawing.StringAlignment.Center;
    legend1.Docking = System.Windows.Forms.DataVisualization.Charting.Docking.Top;
    legend1.Name = "Legend1";
    this.chart1.Legends.Add(legend1);
    this.chart1.Location = new System.Drawing.Point(0, 0);
    this.chart1.Name = "chart1";
    series1.BorderWidth = 2;
    series1.ChartArea = "ChartArea1";
    series1.ChartType = System.Windows.Forms.DataVisualization.Charting.SeriesChartType.Line;
    series1.Color = System.Drawing.Color.Red;
    series1.Legend = "Legend1";
    series1.Name = "Series1";
    series1.XValueType = System.Windows.Forms.DataVisualization.Charting.ChartValueType.Date;
    series2.ChartArea = "ChartArea2";
    series2.Color = System.Drawing.Color.Blue;
    series2.Legend = "Legend1";
    series2.Name = "Series2";
    series2.XValueType = System.Windows.Forms.DataVisualization.Charting.ChartValueType.Date;
    this.chart1.Series.Add(series1);
    this.chart1.Series.Add(series2);
    this.chart1.Size = new System.Drawing.Size(466, 248);
    this.chart1.TabIndex = 0;
    this.chart1.Text = "chart1";
    title1.Name = "Title1";
    title1.Text = "<TITLE>";
    this.chart1.Titles.Add(title1);
    // 
    // ChartControl
    // 
    this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
    this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
    this.Controls.Add(this.chart1);
    this.Name = "ChartControl";
    this.Size = new System.Drawing.Size(466, 248);
    ((System.ComponentModel.ISupportInitialize)(this.chart1)).EndInit();
    this.ResumeLayout(false);
}

Open Windows Form from nunit test

[Test]
public void Test()
{
    var form = new Form1();
    Application.Run(form);
}

Create excel spreadsheet with C#

* Download EPPlus library http://epplus.codeplex.com/

[Test]
public void Test()
{
    var file = @"Sample.xlsx";
    if (File.Exists(file)) File.Delete(file);
    using (var excel = new ExcelPackage(new FileInfo(file)))
    {
        var ws = excel.Workbook.Worksheets.Add("Sheet1");
        ws.Cells[1, 1].Value = "Date";
        ws.Cells[1, 2].Value = "Price";
        ws.Cells[1, 3].Value = "Volume";
        var random = new Random();
        for (int i = 0; i < 10; i++)
        {
            ws.Cells[i + 2, 1].Value = DateTime.Today.AddDays(i);
            ws.Cells[i + 2, 2].Value = random.NextDouble() * 1e3;
            ws.Cells[i + 2, 3].Value = random.Next() / 1e3;
        }
                    
        ws.Cells[2, 1, 11, 1].Style.Numberformat.Format = "dd/MM/yyyy";
        ws.Cells[2, 2, 11, 2].Style.Numberformat.Format = "#,##0.000000";                
        ws.Cells[2, 3, 11, 3].Style.Numberformat.Format = "#,##0";                
        ws.Column(1).AutoFit();
        ws.Column(2).AutoFit();
        ws.Column(3).AutoFit();
        excel.Save();
    }
}

excel

Thursday, 9 February 2012

SQL Server GROUPING SETS

DECLARE @Products TABLE (
  Product varchar(50),
  Category varchar(50),
  Price float
)
INSERT INTO @Products
SELECT 'Sugar','Groceries',2.35
UNION SELECT 'Bread','Groceries',1.09
UNION SELECT 'Ecomonist','Magazines',4.99
UNION SELECT 'FT','Magazines',2.99
SELECT Product,Category, SUM(Price) AS Price, COUNT(*) AS [Count]
FROM @Products
GROUP BY GROUPING SETS((Product),(Category),())
ORDER BY Product DESC, Category DESC, Price DESC, [Count] DESC
SELECT Product,[Range], SUM(Price) AS Price, AVG(Price) AS AvgPrice
FROM (
 SELECT 
	Product, 
	Price,
	[Range] = 
		CASE WHEN Price BETWEEN 0 AND 1 THEN '1$'
			 WHEN Price BETWEEN 1 AND 2 THEN '2$'
			 WHEN Price BETWEEN 2 AND 3 THEN '3$'
		     WHEN Price BETWEEN 3 AND 4 THEN '4$'
		     WHEN Price BETWEEN 4 AND 5 THEN '5$'	
		END
 FROM @Products 
) a
GROUP BY GROUPING SETS((Product),([Range]))
HAVING [Range] IS NOT NULL
ORDER BY Product DESC, [Range] DESC

image

Tuesday, 7 February 2012

C# Compare default(T) values

[TestFixture]
public class TestDefaultValue
{
	[Test]
	public void Test()
	{
		Assert.IsTrue(Helper<string>.IsDefault(default(string)));            
		Assert.IsTrue(Helper<string>.IsDefault(null));
		Assert.IsFalse(Helper<string>.IsDefault(""));
		Assert.IsFalse(Helper<string>.IsDefault("hello"));
	}        
}
public class Helper<T>
{
	public static bool IsDefault(T value)
	{
		if (EqualityComparer<T>.Default.Equals(value, default(T)))
		{
			return true;
		}
		return false;
	}
}