Thursday, December 17, 2009

LINQ to SQL : Understanding Query Execution

Query Execution

Whether you write a query as a high-level query expression or build one out of the individual operators, the query that you write is not an imperative statement executed immediately. It is a description. For example, in the declaration below the local variable q refers to the description of the query not the result of executing it.

var q =
   from c in db.Customers
   where c.City == "London"
   select c;
foreach (Customer c in q)
   Console.WriteLine(c.CompanyName);

The actual type of q in this instance is IQueryable<Customer>. It's not until the application attempts to enumerate the contents of the query that it actually executes. In this example the foreach statement causes the execution to occur.

An IQueryable object is similar to an ADO.NET command object. Having one in hand does not imply that a query was executed. A command object holds onto a string that describes a query. Likewise, an IQueryable object holds onto a description of a query encoded as a data structure known as an Expression. A command object has an ExecuteReader() method that causes execution, returning results as a DataReader. An IQueryable object has a GetEnumerator() method that causes the execution, returning results as an IEnumerator<Customer>.

Therefore, it follows that if a query is enumerated twice it will be executed twice.

var q =
   from c in db.Customers
   where c.City == "London"
   select c;
// Execute first time
foreach (Customer c in q)
   Console.WriteLine(c.CompanyName);
// Execute second time
foreach (Customer c in q)
   Console.WriteLine(c.CompanyName);

This behavior is known as deferred execution. Just like with an ADO.NET command object it is possible to hold onto a query and re-execute it.

Of course, application writers often need to be very explicit about where and when a query is executed. It would be unexpected if an application were to execute a query multiple times simply because it needed to examine the results more than once. For example, you may want to bind the results of a query to something like a DataGrid. The control may enumerate the results each time it paints on the screen.

To avoid executing multiple times convert the results into any number of standard collection classes. It is easy to convert the results into a list or array using the standard query operators ToList() or ToArray().

var q =
   from c in db.Customers
   where c.City == "London"
   select c;
// Execute once using ToList() or ToArray()
var list = q.ToList();
foreach (Customer c in list)
   Console.WriteLine(c.CompanyName);
foreach (Customer c in list)
   Console.WriteLine(c.CompanyName);

This post is modified version of article "Introduction to LINQ" given at MSDN

Namaste !

Anugrah Atreya


Tuesday, December 15, 2009

LINQ : CopyToDataTable Where the Generic Type T Is Not a DataRow

Hi All,

Last week I was working on a LINQ2DataSet and LINQ2XML based application. There I happened to use .CopyToDataTable() method.

DataSet ds = new DataSet();

ds.ReadXml("C:\TestXML");

var query = from row in ds.Tables[0].AsEnumerable()

where row.Field<string>("IsActive") == "1"

select new { ID = row.Field<string>("ID"), UserName = row.Field<string>("UserName") };

DataTable dtResults = query.CopyToDataTable();

I got an error something like this:

The type 'AnonymousType#1' cannot be used as type parameter 'T' in the generic type or method 'System.Data.DataTableExtensions.CopyToDataTable<T>(System.Collections.Generic.IEnumerable<T>)'. There is no implicit reference conversion from 'AnonymousType#1' to 'System.Data.DataRow'.

This error will go if you select complete DataRow like this:

DataSet ds = new DataSet();

ds.ReadXml("C:\TestXML");

var query = from row in ds.Tables[0].AsEnumerable()

where row.Field<string>("IsActive") == "1"

select row;

DataTable dtResults = query.CopyToDataTable();

The root cause of this issue is the defination of CopyToDataTable ().

Simply right click on function name and select "GoTo Defination".

public static DataTable CopyToDataTable<T>(this IEnumerable<T> source) where T : DataRow;

You can see that CopyToDataTable<T> is restricted for DataRow by "where T : DataRow" clause.

In case you have to have this working on the purely generic type (i.e.runtime DataRow object) you need to use custom method CopyToDataTable2() for which you need to manually add these extra files to your project as mentioned on these links:

http://msdn.microsoft.com/en-us/library/bb669096.aspx

Namaste !

Anugrah Atreya

Sunday, November 8, 2009

Detect and avoid memory leaks in .NET

Sharing this great memory leak article on MSDN (Though I myself have to read it)
http://msdn.microsoft.com/en-us/library/ee658248.aspx

Namaste (Greetings)
| Anugrah Atreya

Monday, October 19, 2009

BULK COPY/INSERT Example !

Hi,

Recently I faced a scenario where I have to copy multiline data in varchar(2000) field from one data base to another database.
I tried copy and paste in SQL Server Management Studio and always eneded up with only top row of multi row data in target table.

Writing an ETL (previously know as DTS) was an expensive approach.
I was testing something so I just copied the data to query editor and wrote an insert query  and managed with duplicate multi-line data in all rows.

Somehow I missed to used BULK COPY and INSERT approach and thus I though to share it below:

exec master..xp_cmdshell 'bcp "Select * from myDB..myTable" queryout "C:\Data.txt" -c -T -t "^,~!@"'

BULK INSERT myDB..myTable FROM 'c:\Data.txt' WITH (FIELDTERMINATOR = '^,~!@')

1 important note here is that you need to enable xp_cmdshell before doing this using your SQL Server Management Studio.


usage: bcp {dbtable | query} {in | out | queryout | format} datafile
[-m maxerrors] [-f formatfile] [-e errfile]
[-F firstrow] [-L lastrow] [-b batchsize]
[-n native type] [-c character type] [-w wide character type]
[-N keep non-text native] [-V file format version] [-q quoted identifier]
[-C code page specifier] [-t field terminator] [-r row terminator]
[-i inputfile] [-o outfile] [-a packetsize]
[-S server name] [-U username] [-P password]
[-T trusted connection] [-v version] [-R regional enable]
[-k keep null values] [-E keep identity values]
[-h "load hints"] [-x generate xml format file]

Namaste (Greetings)

Wednesday, September 30, 2009

Most useful GPG Commands : you wont find this anywhere else on internet !

I spent more than 2 days to explore these commands on my own. No documentation, no forum, no post will explain below given points:

1. How to use passphrase in batch command ?

gpg -o "C:\Data_Readable.txt" --batch --passphrase-fd 0 < "C:\My_Passphrase.txt" -d "C:\Data_Encrypted.txt"

i.e. Passphrase is stored in a predefined file and its read from there.

 

2. How to use Fingerprint in delete batch command?


You banged your head against the wall and still getting below given error

gpg: key "61ED 3960 36C8 F91A 406B BEA0 4D3C 8843 3D2D 1636" not found: Unknown system error
gpg: 61ED 3960 36C8 F91A 406B BEA0 4D3C 8843 3D2D 1636: delete key failed: Unknown system error

Here is the right BATCH DELETE command:


gpg --batch --yes --delete-secret-and-public-key 61ED396036C8F91A406BBEA04D3C88433D2D1636

i.e. Fingerprint should not contain any spaces in between.

| namaste from Anugrah

Wednesday, September 16, 2009

Not able to run UnitTest cases when you open your project from shared location ?

Hi All,

I am using Integrated Unit Testing framework of Visual Studio 2008 (i.e. Microsoft.VisualStudio.QualityTools.UnitTestFramework). Recently I faced this issue(in heading/subject) when I opened my project from a dynamic view of clearacse(basically a shared drive)

The actual error which I got was: UTA057: The test 'XXX' cannot be executed because the assembly 'XXX.dll' is not fully trusted by .NET Framework Security Policy. Please use a local directory as the deployment directory for the test run, or use the .NET Framework Configuration tool to grant this assembly the Full Trust permission, and then try again.

The project I was testing is a class library and quick solution for this problem which I got is to uncheck the ENABLE DEPLOYMENT checkbox in LocalTestRun.testrunconfig.

If you don’t want to open this file in VS2008 then you can directly open the file in notepad add following tag <Deployment enabled="false" /> . By default deployment is enabled and the line mentioned above does not exist in this file.

I still need to check that what is the usage of enabling Deployment in this context. If you have used it then do send me comments.

But for now Happy Unit Testing and better development :)

| Anugrah Atreya

Friday, July 24, 2009

Quick Bytes

I hope you would like to keep some basics handy with this document.
http://docs.google.com/View?id=ddjcvgv_15dphh9vvr

Download or Print, have fun.
As the document has lot to read, I'll keep this post short only.

Namaste!
Anugrah

Monday, June 15, 2009

Using .NET component in C++ code

Referring a C++ COM component is easy in .NET; all we have to do is to
  • register (regsvr32 <dll_name_with_full_path>)
  • Add a reference to the COM dll in the Visual Studio Solution
  • An Interop file will be created in the bin directory of .NET project and we are done 
But the reverse is not as simple as above.
So this time I have come up with Steps we need to follow in order to refer a COM visible .NET dll in C++ code. 
 
  • Register the COM Visible .NET assambly with regasm like this:
regasm <dll_name_with_full_path> /tlb
e.g. regasm "C:\SampleProject\bin\Release\Atreya.Anugrah.ComVisibleComponent.dll" /tlb
  • Refer .NET component in .h file like this:
#import "<generated_tlb_name_with_full_path>" named_guids raw_interfaces_only
e.g. #import "C:\SampleProject\bin\Release\Atreya.Anugrah.ComVisibleComponent.tlb" named_guids raw_interfaces_only 
  • Create a class in .h file, and declare member variable like this:
ComVisibleComponent::<Name_of_Interface>* memberVariablePointer;
e.g. Atreya_Anugrah_ComVisibleComponent::IMyInterface* m_pIMyInterface; 
  • Now, Initialize the declared member variable pointer in .cpp file like this: 
hr = CoCreateInstance( __uuidof(Atreya_Anugrah_ComVisibleComponent::<Name_of_CoClass_Implementing_Interface>),          // COM class id
                                        NULL,                                                                                                                                                  // Outer unknown
                                        CLSCTX_ALL,                                                                                                                                       // Server INFO
                                        Atreya_Anugrah_ComVisibleComponent::IID_<Name_of_Interface>,                                                // interface id
                                        (void**) &m_pIMyInterface   );                                                                                                                 // Returned Interface 
if (FAILED(hr))
{
       _com_error e(hr);
   strErrorMsg.Format("Error: Unable to create COM Visible .NET component. hr = %d. Message = %s",hr, e.ErrorMessage());
   return hr;
}
          //you are good to use your initialized pointer here :)   
     m_pIMyInterface->foo()
 
P.S. This post does not explain that how can we make our .NET component as COM visible. Please refer other post/article for that as it is quite simple activity.
    
Namaste (Thanks and Regards)
| Anugrah Atreya
 

Monday, April 27, 2009

Steps to debug .NET dll (registered in GAC) consumed by unmanaged vb6 code

To debug Shared dll in GAC called by VB6 code:

 

0.) Unregister shfusion.dll in c:\windows\microsoft.net\framework\v<version>\shfusion.dll so that actual GAC folders are visible in Explorer.

1.) Open .NET project and make sure that "Just my code debugging is unchecked" in Tools > Options > Debugging.

2.) Now rebuild the .NET dll in debug mode.

3.) Add dll built in debug mode along with the PDB to GAC

4.) Run the vb6 component.

5.) Attach the .NET project to running vb6.exe process.

6.) Breakpoints will be loaded dynamically at runtime.

 

namaste!

Anugrah Atreya

 

Friday, March 6, 2009

Implemented IObjectSafety in .NET but object still unsafe for scripting : RESOLVED

Hello Friends,

 

The intended audience of this article are not those developers who are looking how to implement IObjectSafety in .NET but those who have already done that and still getting “Object unsafe for scripting” popup in Internet Explorer.

(they can find the implementation on msdn blog here: http://blogs.msdn.com/infopath/archive/2005/04/15/408728.aspx).

 

I saw a lot of confusion related to implementation of IObjectSafety in .NET and thought to share my experience about the same.

 

So the simple cause to this big problem is our knowledge about COM & Guids J

 

Basically when we get the interface from any knowledgebase we include it to out project and like a good programer we generate a new Guid and replace the one which is given in orignal documenttaion.

 

That’s it; we have messed our code and we are not even aware of it.

 

So the crux is that for any IObjectSafety implementation the guid of out interface should always be

{"CB5BDC81-93C1-11cf-8F20-00805F2CD064"}. Period.

 

By the time you end up reading the above line your problem is hopefully solved and your component is now safe for scripting.

 

So this is how your interface will eventually look like:

 

using System;

using System.Runtime.InteropServices;

 

namespace Atreya.SampleCode

{

    /// <summary>

    /// Methods in this interface needs to be implemented to mark any object safe for scripting in IE

    /// </summary>

    [ComImport()]

    [Guid("CB5BDC81-93C1-11cf-8F20-00805F2CD064")]

    [InterfaceType(ComInterfaceType.InterfaceIsIUnknown)]

    internal interface IObjectSafety

    {

        [PreserveSig]

        int GetInterfaceSafetyOptions(ref Guid riid, out int pdwSupportedOptions, out int pdwEnabledOptions);

 

        [PreserveSig]

        int SetInterfaceSafetyOptions(ref Guid riid, int dwOptionSetMask, int dwEnabledOptions);

    }

}

 

Namaste!

  Anugrah Atreya 

 

Friday, January 2, 2009

Transparent Button on Windows Forms

Hi,

  

I was working on some VB6 migration to C#.NET, in VB6 application developers have used some third party controls (e.g. SSRibbon provided by Sheridan 3_0) to create transparent Buttons (required when button is drawn on the background image of form and you want user to click over that part of background image).

 

When I tried to search the internet for a solution in .NET, I didn’t found anything usefull. May be because this is quite simple thing :)

 

But still we need to have the solution on internet, so that someone in need can find it.

So, all you need to do is to add these lines of code for your System.Windows.Forms.Button object. In my case I have named it as btnLink.

 

          btnLink.FlatStyle = FlatStyle.Flat; 

          btnLink.BackColor = Color.Transparent;

          btnLink.FlatAppearance.MouseDownBackColor = Color.Transparent;

          btnLink.FlatAppearance.MouseOverBackColor = Color.Transparent;

 

namste !

  Anugrah Atreya