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

Wednesday, February 26, 2014

Microsoft.SqlServer.Management.SMO

Hi All,

I’ve been using SMO heavily in my OSR data migration piece of work, and I’d like to share that with you.  To put SMO into context, I consider it as a programmatic way to interact with SSMS.
For CRUD actions, SqlClient is still very good. Very flexible compared to entity framework.
However, when we need extra flexibility, e.g., create/backup database, create/alter tables, especially running SQL with GO statement, SMO is the way to go.   

I’ve attached a Util class that demonstrates how it can be wrapped, and the dynamic SQL that gets generated by my program and executed via the Util class.

If you would like to know more, plenty of articles on MSND:
SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server


Sqlserver nvarchar ntext difference

Thanks to Graeme for asking an interesting question about the difference between nvarchar and varchar. Since my first language can’t be stored in ACSII, I know nvarchar is the way to go.
Further reading on nvarchar(max) prompted us to think the differences between nvarchar and ntext, and we found the following in MSDN. 

ntext , text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

Don’t panic, further version doesn’t include SQLServer 2012.  Read more here:

.Net Entity Framework and DAL

This old post demonstrates some very interesting thoughts about DAL and Entity Framework, and discussed the design pattern (pros and cons) in an architecture point of view. At the last of this post, a ppt is embedded. http://www.wadewegner.com/2009/06/architecting-your-data-access-layer-with-the-entity-framework/
Here are some highlights and my understanding:
·         Use EF as DAL  This is still a good option to replace the CRUD. See my OpsLog comments below about EF.
·         Full Encapsulation of the Entity Framework.  This is the ideal solution but we probably won’t get to the complexity. It reduces coupling and make application testable.
·         Partial Encapsulation of the Entity Framework.  The author didn’t give an example, but I have some in EGMAN.  EGMAN uses MVC and MVVM (MVVM Model-View ViewModel is similar to MVC, Model-View Controller).  I used EF to map db to model (M in MVC), but sometimes lost the flexibility to add custom attribute and data access logic. I adopted MVVM, which use VM to replace C, and VM is the encapsulation of EF that extract data from the Model. 

Links of the month November

Links of the Month November

OpenXML: New and easiest way to do handle MS Office document on server (eg, mailmerge)
·         Example: TFS $ActewAGL.MailMerge
·         Download: http://www.microsoft.com/en-us/download/details.aspx?id=5124
·         Read More

Asp.NET MVC: A mindset change to build testable website
·         Office Website with very good tutorials
·         Sample website with 200+ pages of step by step guild on how to build the website, MVC Music Store
LINQ:  Jon Skeet re-implement LINQ in 45 posts. Download the ebook and learn.
Tools
·         Telerik Extensios for ASP.NET MVC: free open source controls for MVC. Every control come with full sample code and demo.
·         .Net Demon: Tool that build as you type
·         NCrunch similar to .Net Demon, but also do continuous testing.
·         Both tools Combine with LiveReload (and Chrome extension), No more F6 to build, and F5 to refresh
·         VS 2012 & TFS 2012 update 1 released.  Visual Studio 2012 Update 1 focuses on enhancements to SharePoint development, Windows development, continuous quality enablement, and tools for agile teams.
·         Together with update 1 release, MS also released VS2010 Application Lifecycle Management Virtual Machine and Hands-on-Labs.  It’s really time to upgrade!
Latest
·         I won’t tell you what this is, you so have to click to find out. http://www.microsoft.com/australia/ws2012xbox/
·         Announcing the Latest Release of Office Developer Tools
·         Video:  Bleeding edge ASP.NET: See what is next for MVC, Web API, SignalR and more…
·         Microsoft Releases SkyDrive SDKs for .NET, Windows Phone 8
·         HTML5 and Why Developers Need It (IE8 support 0% of its’ feature)
·       Finally: Windows (Phone/Tablet) 8 : Too many posts out there

scrum

     After three days of training on scrum, I'd like to share few tips and materials.
Scrum is simple to understand, hard to implement. It is the most popular agile methodology. Few things I like about it:

* Daily Scrum (also referred as daily stand-up, but what if Zhen broke his knee and can't stand):  is a good way to synchronise between team members. It is not a free form, and 3 questions are answered by every team member.  (was working, plan to work, any impediment)
* Retrospective: is the best part in my opinion. Every team member answer three questions, what went well, want went wrong, how to improve. The lecture said he used this to coach his kids soccer team :)

* Working in the order of priority, and deliver business value in each sprint. 

links of the month October

Links of the Month October

MEF
·         Managed Extensibility Framework (MEF)
·         MEF An interesting documentation with examples
·         MEF vs IoC

Working with Data
·         Overview of Data Development platform in .Net

Tools
·         LinqPad A combination of c# and SMSS
·         Database Comparison Tool:  DBComparer
·         TFS2012 Reasons to Upgrade
Latest
·         Team Foundation Service is Released   A version of Team Foundation Server hosted on Windows Azure. Free for MSDN subscribers!
·         When JavaScript is unmanageable: Introducing Typescript
·         Typescript - a real world story of adoption in TFS 160K line of JavaScript
·         Key Windows 8 Developer Tenets and Skills

.Net tools

  • ·         NuGet Package Manager: NuGet is a Visual Studio extension that makes it easy to install and update third-party libraries and tools in Visual Studio. You can install all of the library discussed here from within NuGet
  • ·         AjaxControlToolkit:  http://www.asp.net/ajaxlibrary/AjaxControlToolkitSampleSite/ An open-source project built on top of the Ms ASP.NET AJAX framework. It contains more than 30 controls that enable you to easily create rich, interactive web pages. Eg, Calendar, ModalPopup, AsyncFileUpload
  • ·         ELMAH: Error Logging Modules and Handlers is an application-wide error logging facility that is completely pluggable. Tutorial: http://www.asp.net/web-forms/tutorials/deployment/deploying-web-site-projects/Logging-Error-Details-with-ELMAH-cs
  • ·         JQuery, JQuery UI & plugins,: More than 55% of the world most visited 10000 website use JQuery. Why shouldn’t we use it?
  • ·         Json: JavaScript Object Notation is a text format, lightweight data-interchange format. A Fat-Free Alternative to XML.
  • ·         Json.NET: Json.NET is a popular high-performance JSON framework for .NET. see http://james.newtonking.com/pages/json-net.aspx for details.
  • ·         EF:

o   Microsoft product, we know what is means.
o   Highly Supported and plenty of resources online, with fairly strong communities online. Evolving very fast, fixes was coming out very fast and it was getting better day by day.
o   LINQ.  No more CRUD.
o   Lower learning curve than Nhibernate.
o   Database Frist has more features than Code First

o   Easy to code and maintain compared to Dataset

Links of the month September

Microsoft Developer Links of the Month


·         Debug production system: Visual Studio Ultimate 2012: Collecting and analyzing data in production
·         TFS put into good use: Automated Build-Deploy-Test using TFS 2012
·         Visual Studio New features: Visual Studio 2012: Did You Call Me?
·         Reason not to test? None: Testing for Continuous Development
·         ASP.NET improvements continue: Introducing ASP.NET FriendlyUrls - cleaner URLs, easier Routing, and Mobile Views for ASP.NET Web Forms and ASP.NET 4.5 ScriptManager Improvements in WebForms
·         Windows Azure new features: Meet the New Windows Azure and a follow up article Announcing: Great Improvements to Windows Azure Web Sites
·         Win8: Reasons Why You Should Develop a Windows 8 App
·         Webcast: How Google Tests Software & Getting Started with Windows Azure

Want something Free?

How to check a process or program is running via powershell script

Powershell script to check if a program is running or not

$Prog = "C:\Program Files (x86)\Mozilla Firefox\firefox.exe"
$Running = Get-Process firefox -ErrorAction SilentlyContinue
$Start = {([wmiclass]"win32_process").Create($Prog)}
if($Running -eq $null) # evaluating if the program is running
{& $Start} # the process is created on this line

Sunday, November 22, 2009

c# create and write to excel file

Following my previous post c# how to read write excel spreedsheet and C# quit excel appliction after using COM objectI wanted to continue the topic with a focus on write to excel spreedsheet.

Using Microsoft.Office.Interop.Excel is the easiest option and with Excel 2007 installed, you can get version 12 of this dll. It allows user to save files as Excel 2007 format, or the old 97-03 format.

Firstly, Let’s look at some sample code.

        private static void NAR( object o )
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject( o );
}
catch( Exception ex )
{
Console.WriteLine( ex.ToString( ) );
}
finally
{
o = null;
}
}

public override void save( FileWriter writer )
{
string filename = writer.FileName;

TimeSeries[] series = checkCompatibleTimeSeries( (Data[])data.ToArray( typeof( Data ) ) );
int count = series[0].itemCount( );
if( count == 0 )
throw new DataFileIOException( this, "Time series to save to " + filename + " are empty" );

// Instantiate Excel and start a new workbook.
Excel.Application objApp;
Excel._Workbook objBook;
Excel.Workbooks objBooks;
Excel.Sheets objSheets;
Excel._Worksheet objSheet;
Excel.Range range;

objApp = new Excel.Application( );
objBooks = objApp.Workbooks;
objBook = (Excel._Workbook)( objBooks.Add( Missing.Value ) );
objSheets = (Excel.Sheets)objBook.Worksheets;
objSheet = (Excel._Worksheet)( objSheets.get_Item( 1 ) );

if( objSheet == null )
{
throw new Exception( "Worksheet could not be created. Check that your office installation and project references are correct." );
}

try
{
( (Excel.Range) objSheet.Cells[ 1, 1 ] ).set_Value( Missing.Value, "Date" );
( (Excel.Range) objSheet.Cells[ 1, 2 ] ).set_Value( Missing.Value, filename );


for( int rowIndex = 0; rowIndex < count; rowIndex++ )
{
( (Excel.Range) objSheet.Cells[ rowIndex + 2, 1 ] ).set_Value( Missing.Value,
series[ 0 ].timeForItem( rowIndex ) );

for( int columnIndex = 0; columnIndex < data.Count; columnIndex++ )
{
( (Excel.Range) objSheet.Cells[ rowIndex + 2, columnIndex + 2 ] ).set_Value( Missing.Value,
series[ columnIndex
][ rowIndex ] );
}
}
objApp.DisplayAlerts = false;
objApp.AlertBeforeOverwriting = false;
objBook.SaveAs( filename, format , Missing.Value, Missing.Value, Missing.Value, Missing.Value, Excel.XlSaveAsAccessMode.xlShared, Excel.XlSaveConflictResolution.xlLocalSessionChanges, Missing.Value, Missing.Value, Missing.Value, Missing.Value );

objBooks.Close( );
objApp.Quit( );
}
catch( Exception theException )
{
throw theException;
}
finally
{

if( objBooks != null )
objBooks.Close( );
if( objApp != null )
objApp.Quit( );
NAR( objSheet );
NAR( objSheets );
NAR( objBooks );
NAR( objBook );
NAR( objApp );
}

GC.Collect( );
GC.WaitForPendingFinalizers( );

}



Some tricky part of the code is the SaveAs Method. In order to disable the pop up alert messagebox, you need to do few things. (I believe most people do want to disable it since this must have been handled in the GUI part). Excel.XlSaveAsAccessMode.xlShared is the essential key to solve this problem. If it is not used, then  Excel.XlSaveConflictResolution.xlLocalSessionChanges wouldn’t work. Both “objApp.DisplayAlerts = false;” and “ objApp.AlertBeforeOverwriting = false; ” are used in the Excel dll Version 11 and will be overwrite by the XlSaveConflictResolution.



The code is fairly straight forward, the please refer to my old post on how i clean up the resources before quit.

Monday, November 16, 2009

c# detect excel install location in registry and open file

public void OpenInExcel(string filename)
{
string dir = "";
RegistryKey key = Registry.LocalMachine;
RegistryKey excelKey = key.OpenSubKey(@"SOFTWARE\MicroSoft\Office");
if (excelKey != null)
{
foreach (string valuename in excelKey.GetSubKeyNames())
{
int version = 9;
double currentVersion=0;
if (Double.TryParse(valuename, out currentVersion) && currentVersion >= version)
{
RegistryKey rootdir = excelKey.OpenSubKey(currentVersion + @".0\Excel\InstallRoot");
if (rootdir != null)
{
dir = rootdir.GetValue(rootdir.GetValueNames()[0]).ToString();
break;
}
}
}
}
if (dir != "")
{
ProcessStartInfo startInfo = new ProcessStartInfo();

startInfo.FileName = dir + @"Excel.exe";
startInfo.Arguments = "\"" + fileName + "\"";
startInfo.UseShellExecute = false;

using (Process process = new Process())
{
process.StartInfo = startInfo;
try
{
process.Start();
}
catch (Exception ex)
{
Console.WriteLine("\n\nCould not start Excel process.");
Console.WriteLine(ex);
}
}
}
else
{
MessageBox.Show("Can't Open in excel because excel is not installed.");
}
}

c# revert a LinkedList

void ReverseLinkedList(LinkedList ls)
{
if ( ls == null || ls.Head == null )
return;
ListNode head = ls.Head;
ListNode current = head.Next;
while(current!=null)
{
ListNode tmp = current;
current = current.Next;
tmp.Next = head;
head = tmp;
}
ls.Head.Next = null;
ls.Head = head;
}

c# merge sort algorithm implementation

        private int[] MergeSort(int[] a)
{
if ( a.Length == 1)
return a;
int middle = a.Length / 2;
int[] left = new int[middle];
for (int i = 0 ; i < middle ; i ++)
{
left[ i ] = a[ i ];
}
int[] right = new int[a.Length - middle];
for( int i = 0; i < a.Length - middle; i++ )
{
right[i] = a[i+middle];
}
left = MergeSort( left );
right = MergeSort( right );

int leftptr = 0;
int rightptr = 0;

int[] sorted = new int[a.Length];
for(int k = 0 ; k < a.Length; k++)
{
if ( rightptr == right.Length || ((leftptr < left.Length ) && (left[leftptr] <= right[rightptr])))
{
sorted[ k ] = left[ leftptr ];
leftptr++;
}
else if( leftptr == left.Length || ((rightptr < right.Length ) && (right[rightptr] <= left[leftptr] )))
{
sorted[k] = right[rightptr];
rightptr++;
}
}
return sorted;
}

c# Quick Sort Algorithm Implementation

private void QuickSort( int[] a, int left, int right ) 
        { 
            if( a == null ) 
                return; 
            int i = left; 
            int j = right; 
            int pivot = a[( left + right ) / 2]; 
            while( i <= j ) 
            { 
                while( a[i] < pivot ) 
                    i++; 
                while( a[j] > pivot ) 
                    j--; 
                if( i <= j ) 
                { 
                    int tmp = a[i]; 
                    a[i++] = a[j]; 
                    a[j--] = tmp; 
                } 
            } 
            if( j > left ) 
            { 
                QuickSort( a, left, j ); 
            } 
            if( i < right ) 
            { 
                QuickSort( a, i, right ); 
            } 
        }

c# insert sort algorithm implementation

        private void insert_sort( int[] s )
{
for( int k = 1; k < s.Length ; k++ )
{
int value = s[k];
for( int i = 0; i <= k - 1; i++ )
{
if( value < s[i] )
{
for( int j = k - 1; j >= i; j-- )
s[j + 1] = s[j];
s[i] = value;
break;
}
}
}
}

Thursday, October 8, 2009

c# copy excel data and paste into datagridview table

This post is about managing the memory data object after ctrl+c is used in Excel. You will learn how data are organised in memory and should be able to apply it anywhere. The code example tries to read the data in DataObject out and paste it in datagridview.            


DataObject o = (DataObject) Clipboard.GetDataObject(); 
if (o.GetDataPresent(DataFormats.Text))
{
int rowOfInterest = DataGridView.CurrentCell.RowIndex;
string[] selectedRows = Regex.Split(o.GetData(DataFormats.Text).ToString( ).TrimEnd( "\r\n".ToCharArray() ), "\r\n");

if (selectedRows == null || selectedRows.Length == 0)
return;

foreach (string row in selectedRows)
{
if (rowOfInterest >= DataGridView.Rows.Count)
break;

try
{
string[] data = Regex.Split(row, "\t");
int col = DataGridView.CurrentCell.ColumnIndex;

foreach (string ob in data)
{
if (col >= DataGridView.Columns.Count)
break;
if (ob!=null)
DataGridView[col, rowOfInterest].Value = Convert.ChangeType( ob, DataGridView[col,rowOfInterest].ValueType );
col++;
}
}
catch (Exception enterException)
{
//do something here
}
rowOfInterest++;
}
}

Thursday, September 3, 2009

Calculate GUID from object or string

private Guid CalculateGUID(object m)
{
    ASCIIEncoding enc = new ASCIIEncoding();
    string code = m.GetHashCode( ).ToString();
    string key;
    if (code.Length < 16)
        key = code + new string( '*', 16 - code.Length );
    else if (code.Length == 16)
    {
        key = code;
    }
    else
    {
        key = code.Substring( 0, 16 );
    }

    return new Guid(enc.GetBytes(key)) ;
}

Monday, August 17, 2009

c# using reflection to get current class name

If you are inside the class method, and don’t have access to an instance of the object, use:

MethodBase.GetCurrentMethod().DeclaringType.FullName;

If you hava access to an instance of the object, then:

public static class SomeClass
{
public static string FullName
  {
    get { return typeof(SomeClass).FullName; }
  }
}

Saturday, July 18, 2009

c# Override GetHashCode when using dictionary in .Net

In .Net, Dictionary is implemented as HashTable. As you might know, Hashtable rely on hash algorithm to perform. Last week, I was involved in a performance enhancement task. One of the key performance bottleneck was due to an unimplemented hash algorithm. The task was trying to insert object in dictionary and trying to retrieve it when necessary.
Originally since the object is quite uncertain, and even some of the objects had override the GetHashCode method, it only returns 0, which then every object will get the same hash code. When ContainsKey called, the result is the dictionary get iterated, the algorithm checks so many Equals method. It is quite hard for us to write a genric hash algorithm for object, so I simply change the algorithm to return the hash code of GetType. Further detection of whether an object had override its GetHashCode method is added using reflection. (When an object property changs, ContainsKey method doesn't return the desired value for some object unless GetHashCode was overriden)

Looking back at the solution, simple, override both method. Sometimes, it really comes down to understand what MSDN says.

GetHashCode returns a value based on the current instance that is suited for hashing algorithms and data structures such as a hash table. Two objects that are the same type and are equal must return the same hash code to ensure that instances of HashTable and Dictionary</TKEY,> work correctly. To fix a violation of this rule, provide an implementation of GetHashCode. For a pair of objects of the same type, you must ensure that the implementation returns the same value if your implementation of Equals returns true for the pair.

Here is some sample code.

            public int GetHashCode( object obj )
{
MethodInfo mi = obj.GetType( ).GetMethod( "GetHashCode" );
if( ((mi.Attributes & MethodAttributes.NewSlot) == MethodAttributes.NewSlot) )
{
ordinaryObjectMapping.Add( obj, true );
return obj.GetHashCode( );
}
else
{
ordinaryObjectMapping.Add( obj, false );
return obj.GetType( ).GetHashCode( );
}
}

Friday, June 26, 2009

C# Double Decimal Place using String Format

String Format for Double [C#]

The following examples show how to format float numbers to string in C#. You can use static method String.Format or instance methods double.ToString and float.ToString.

Digits after decimal point

This example formats double to string with fixed number of decimal places. For two decimal places use pattern „0.00“. If a float number has less decimal places, the rest digits on the right will be zeroes. If it has more decimal places, the number will be rounded.

[C#]

// just two decimal places
String.Format("{0:0.00}", 123.4567); // "123.46"
String.Format("{0:0.00}", 123.4); // "123.40"
String.Format("{0:0.00}", 123.0); // "123.00"


Next example formats double to string with floating number of decimal places. E.g. for maximal two decimal places use pattern „0.##“.



[C#]



// max. two decimal places
String.Format("{0:0.##}", 123.4567); // "123.46"
String.Format("{0:0.##}", 123.4); // "123.4"
String.Format("{0:0.##}", 123.0); // "123"


Digits before decimal point



If you want a float number to have any minimal number of digits before decimal point use N-times zero before decimal point. E.g. pattern „00.0“ formats a float number to string with at least two digits before decimal point and one digit after that.



[C#]



// at least two digits before decimal point
String.Format("{0:00.0}", 123.4567); // "123.5"
String.Format("{0:00.0}", 23.4567); // "23.5"
String.Format("{0:00.0}", 3.4567); // "03.5"
String.Format("{0:00.0}", -3.4567); // "-03.5"


Thousands separator



To format double to string with use of thousands separator use zero and comma separator before an usual float formatting pattern, e.g. pattern „0,0.0“ formats the number to use thousands separators and to have one decimal place.



[C#]



String.Format("{0:0,0.0}", 12345.67);     // "12,345.7"
String.Format("{0:0,0}", 12345.67); // "12,346"


Zero



Float numbers between zero and one can be formatted in two ways, with or without leading zero before decimal point. To format number without a leading zero use # before point. For example „#.0“ formats number to have one decimal place and zero to N digits before decimal point (e.g. „.5“ or „123.5“).



Following code shows how can be formatted a zero (of double type).



[C#]



String.Format("{0:0.0}", 0.0);            // "0.0"
String.Format("{0:0.#}", 0.0); // "0"
String.Format("{0:#.0}", 0.0); // ".0"
String.Format("{0:#.#}", 0.0); // ""


Align numbers with spaces



To align float number to the right use comma „,“ option before the colon. Type comma followed by a number of spaces, e.g. „0,10:0.0“ (this can be used only in String.Format method, not in double.ToString method). To align numbers to the left use negative number of spaces.



[C#]



String.Format("{0,10:0.0}", 123.4567);    // "     123.5"
String.Format("{0,-10:0.0}", 123.4567); // "123.5 "
String.Format("{0,10:0.0}", -123.4567); // " -123.5"
String.Format("{0,-10:0.0}", -123.4567); // "-123.5 "


Custom formatting for negative numbers and zero



If you need to use custom format for negative float numbers or zero, use semicolon separator „;“ to split pattern to three sections. The first section formats positive numbers, the second section formats negative numbers and the third section formats zero. If you omit the last section, zero will be formatted using the first section.



[C#]



String.Format("{0:0.00;minus 0.00;zero}", 123.4567);   // "123.46"
String.Format("{0:0.00;minus 0.00;zero}", -123.4567); // "minus 123.46"
String.Format("{0:0.00;minus 0.00;zero}", 0.0); // "zero"


Some funny examples



As you could notice in the previous example, you can put any text into formatting pattern, e.g. before an usual pattern „my text 0.0“. You can even put any text between the zeroes, e.g. „0aaa.bbb0“.



[C#]



String.Format("{0:my number is 0.0}", 12.3);   // "my number is 12.3"
String.Format("{0:0aaa.bbb0}", 12.3); // "12aaa.bbb3"