Wednesday, June 17, 2009

C# data type corresponding to VARCHAR(MAX)

I stumbled into size limitations while specifying VARCHAR(MAX) in C# code.

Following two options can be the C# alternative to VARCHAR(MAX) or NVARCHAR(MAX) that I have tried for SQL Server 2005.

Lets go step by step and verify as well.

STEP 1: Create a table

CREATE TABLE TBL_MAXTEST (LARGE_DATA VARCHAR(MAX))

STEP 2: C# Code
Run the following locs.

2a) Set parameter size equal to minus one (-1)
//Create a brand new object
SqlParameter theParam = new SqlParameter();

//Fill in the public properties
theParam.ParameterName = "@LARGE_DATA";
theParam.SqlDbType = SqlDbType.VarChar;
theParam.Size = -1;
theParam.Value = strLargeData;
theParam.Direction = ParameterDirection.Input;

//Add and enjoy!
cmdSQL.Parameters.Add(theParam);


2b) Or donot specify any column size.
By default the length is assumed to be -1;




STEP 3: Verify data length
Verify the length of the data.

SELECT DATALENGTH(LARGE_DATA) FROM TBL_MAXTEST

The above shall give the length of the data. I have tried it with 7999 bytes, 8000, 8001, and 10000 bytes.

Basically, VARCHAR and NVARCHAR comes in Large-Value Data Types. NVARCHAR supports Unicode characters which means a wider "range" of characters/code pages. And since its Unicode therefore a single character is stored on two bytes; which inherently means NVARCHAR is twice the size of VARCHAR.

While VARCHAR stores one char per byte; and the problem with data types that use one byte to encode each character is that the data type can only represent 256 different characters.

The following table shows the relationship between the large -value data types and their counterparts from earlier versions of SQL Server.

Large-value data types Earlier versions (SQL Server 2000/etc)
varchar(max) text*

nvarchar(max) ntext*

varbinary(max) image



Note that, TEXT, NTEXT, and IMAGE data types are now a deprecated data types.

  • Use char when the sizes of the column data entries are consistent/same.
  • Use varchar when the sizes of the column data entries vary considerably; that is the max and minimum amount of length is known.
  • Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.
A table can contain a maximum of 8,060 bytes per row; but this restriction does not apply to varchar(max), nvarchar(max), varbinary(max), text, image, or xml columns.

You can use the large-value data types, i.e. the MAX value, to store up to 2^31-1 bytes of data; precisely 2147483648 – 1 bytes; approximately 2 GB.

Monday, June 15, 2009

"Fail to plan, plan to fail"

The lifestyle and thoughts of Hazrat Ali has always been a source of motivation in my life; Probably mostly because I heard alot of stories of the Might, and Kindness, and Bravery, and his Thoughtfulness. There is this famous story that used to roam around us all the time when we were young: Once, a man came to Hazrat Ali and questioned that exactly how much control do we have on our lives? Hazrat Ali smiled thoughtfully, and asked him to stand and fold one of his leg; The man followed, and looked at Hazrat Ali as to how this is going to answer his question; Harzat Ali said; fold your other leg. The man wondered, puzzled, said he cannot do that. Thats about it, thats all the control that you have on your life.

This is a very well known story that most of us has heard, but I believe seldom are the people who would know the meaning of this story. And well being "smart" I believe I am able to "decypher" the meaning (0:

All it means, that everything, basically every "Result", is based upon effort. The more the effort, the better the effort, the more "Managed-Effort", the more well-planned-effort, the more predictable-obvious-desireable-result.

And if, after well planned effort, you did not get the desired-result, so according to Hazrat Ali, that is out of the control of our life. Thats where the "Fate" comes in. I donot have to do anything and that I "cannot-do" anything about it. Exactly like the man who couldnt fold the other leg at the same time, otherwise he would fall down. And the good thing is that atleast he tried; otherwise he wouldnt have known.

And I believe in effort. Result is the outcome of the effort. "Fail to plan, plan to fail".

Obviously I am talking about "normal" human beings, and not the especial-exceptional-cases.

thinking about this story some years back, following was the "aamad('synchronous-flow-of-incoming-thoughts' is how I would define it :0)" I had:

~ poochay hain kay maula, ye taqdeer kya bala?
kehtay hain kay koshish mein hai taqdeer bas chupi.

~ jana meray maulna nay, Rehman ko tabhi.
iraadon kay tootnay pay tadab'bur kiya jabhi!

The second verse is from a quote from Hazrat Ali, that I like, and that I "rendered" into a poetic-verse. (o:

I like this: "Most gulls don't bother to learn more than the simplest facts of flight—how to get from shore to food and back again. For most gulls, it is not flying that matters, but eating. For this gull, though, it was not eating that mattered, but flight. More than anything else, Jonathan Livingston Seagull loved to fly." — Richard Bach, 'Jonathan Livingston Seagull'

Saturday, June 13, 2009

WebBrowser Control: Bypass integrated authentication


How bypass integrated authentication using WebBrowser control?

Well, sometime back, I was hosting the WebBrowser control over WinForm and I wanted to access a remote/secure(https) website; The website was throwing a windows authentication dialog box whenever the url was typed in.

Fig1: The well known network credentials popup dialog box

This started bugging me and my program as well; since I wanted my program to automate this behavior and provide the credentials “hautomatically” (handle-automatically :0) So I told myself, issues are sweet, that’s how you get to learn more; Lets get sweetened!

Therefore, with absolute positive energy, I dived into google, and my “at-the-moment-favourite” – overflowing stack

Now the question was how to provide integrated authentication while having no access to the site, that is, my integrated windows credentials are not valid for the remote site; So how to force a different credential(other than the logged in one) plus override the WebBrowser credentials popup prompt.

Note, for those, who are in a hurry and running after the unstretchable deadline, please close your eyes - copy and paste the "The Code" section in your .NET WinForm application... and follow following steps:

STEP 1: Create a plain windows form application, double click over the form in design more to implement its OnLoad event.
STEP 2: Copy paste all of the code in the FormTest.cs file

...and well, hit the Run!

-- UPDATE 2011/04/09 --

Please download the zipped solution (.SLN) from here. Thanks to Eduardo for his feedback.

Rest, please follow.

There are four types of authentication, Windows, Forms, Passport, and None. Usual use is of the forms based authentication and integrated windows authentication (IWA). My scenario obviously had an integrated authentication system; and the logged in user is popped up with a window for required credentials.

Fig 2: [Illustration by Vesku’s blog]

Following is somewhat, in most cases, a generic site authentication process:

Fig 3: [Illustration by IBM] A generic authentication flow.

For this, following interfaces needed implementation. IAuthenticate, IServiceProvider, IOleClientSite;

IAuthenticate, is used by the WebBrowser object to automatically retrieve the user credentials and provides it to the website upon request.

Urlmon.dll uses the QueryInterface method on the client application's implementation of IBindStatusCallback to get a pointer to the client application's IAuthenticate interface.
If the client application is hosting Mshtml.dll, Mshtml.dll requests a pointer to the client application's implementation of IAuthenticate interface by calling QueryInterface on the client application's IServiceProvider interface.
IOleClientSite, is used to notify the WebBrowser about the website that is going to require the credentials.

Within a compound document, each embedded object has its own client site — the place where it is displayed and through which it receives information about its storage, user interface, and other resources. IOleObject::SetClientSite is the only method enabling an embedded object to obtain a pointer to its client site.

A container can notify an object of its client site either at the time the object is created or, subsequently, when the object is initialized.
The IServiceProvider interface is a generic access mechanism to locate a GUID-identified service that is provided through a control or any other object that the service can communicate with. For example, an embedded object (such as an OLE control) typically communicates only with its associated client site object in the container through the IOleClientSite interface that is supplied by using IOleObject::SetClientSite. The embedded object must ask the client site for some other service that the container supports when that service might not be implemented in the client site.

The client site must provide a means by which the control that is managed by the site can access the service when necessary. For example, the IOleInPlaceSite::GetWindowContext function can be used by an in-place object or control to access interface pointers for the document object that contains the site and the frame object that contains the document.

Because these interface pointers exist on separate objects, the control cannot call the site's QueryInterface to obtain those pointers. Instead, use the IServiceProvider interface.

The IServiceProvider interface has only one member, QueryService, through which a caller specifies the service ID (SID, a GUID), the IID of the interface to return, and the address of the caller's interface pointer variable.
The Code

Following is how IOleObject is going to be implemented into your windows form;
[ComImport,
    Guid("00000112-0000-0000-C000-000000000046"),
    InterfaceType(ComInterfaceType.InterfaceIsIUnknown)]
    public interface IOleObject
    {
    void SetClientSite(IOleClientSite pClientSite);
    void GetClientSite(IOleClientSite ppClientSite);
    void SetHostNames(object szContainerApp, object szContainerObj);
    void Close(uint dwSaveOption);
    void SetMoniker(uint dwWhichMoniker, object pmk);
    void GetMoniker(uint dwAssign, uint dwWhichMoniker, object ppmk);
    void InitFromData(IDataObject pDataObject, bool
    fCreation, uint dwReserved);
    void GetClipboardData(uint dwReserved, IDataObject ppDataObject);
    void DoVerb(uint iVerb, uint lpmsg, object pActiveSite,
    uint lindex, uint hwndParent, uint lprcPosRect);
    void EnumVerbs(object ppEnumOleVerb);
    void Update();
    void IsUpToDate();
    void GetUserClassID(uint pClsid);
    void GetUserType(uint dwFormOfType, uint pszUserType);
    void SetExtent(uint dwDrawAspect, uint psizel);
    void GetExtent(uint dwDrawAspect, uint psizel);
    void Advise(object pAdvSink, uint pdwConnection);
    void Unadvise(uint dwConnection);
    void EnumAdvise(object ppenumAdvise);
    void GetMiscStatus(uint dwAspect, uint pdwStatus);
    void SetColorScheme(object pLogpal);
    }

IOleClientSite requires following implementation:
[ComImport,
    Guid("00000118-0000-0000-C000-000000000046"),
    InterfaceType(ComInterfaceType.InterfaceIsIUnknown)]
    public interface IOleClientSite
    {
    void SaveObject();
    void GetMoniker(uint dwAssign, uint dwWhichMoniker, object ppmk);
    void GetContainer(object ppContainer);
    void ShowObject();
    void OnShowWindow(bool fShow);
    void RequestNewObjectLayout();
    } 

IServiceProvider has following:
[ComImport,
    GuidAttribute("6d5140c1-7436-11ce-8034-00aa006009fa"),
    InterfaceTypeAttribute(ComInterfaceType.InterfaceIsIUnknown),
    ComVisible(false)]
    public interface IServiceProvider
    {
    [return: MarshalAs(UnmanagedType.I4)]
    [PreserveSig]
    int QueryService(ref Guid guidService, ref Guid riid, out IntPtr
    ppvObject);
    } 

[ComImport, GuidAttribute("79EAC9D0-BAF9-11CE-8C82-00AA004BA90B"),
    InterfaceTypeAttribute(ComInterfaceType.InterfaceIsIUnknown),
    ComVisible(false)]
    public interface IAuthenticate
    {
    [return: MarshalAs(UnmanagedType.I4)]
    [PreserveSig]
    int Authenticate(ref IntPtr phwnd,
    ref IntPtr pszUsername,
    ref IntPtr pszPassword
    );
    } 

You may implement your form class like following:
public partial class FrmTest : Form, IOleClientSite, IServiceProvider, IAuthenticate
    {
    public static Guid IID_IAuthenticate = new Guid("79eac9d0-baf9-11ce-8c82-00aa004ba90b");
    public const int INET_E_DEFAULT_ACTION = unchecked((int)0x800C0011);
    public const int S_OK = unchecked((int)0x00000000);


    public FrmTest()
    { InitializeComponent(); }


    private void button1_Click(object sender, EventArgs e)
    {
    string oURL = this.textBox1.Text;
    webBrowser1.Navigate(oURL);
    }

Note, that now, when we host the WebBrowser control and implement the IAuthenticate interface to programmatically bypass the authentication process, sometimes Internet Explorer does not call the Authenticate function, which causes an authentication dialog to appear. So, this is somewhat buggy.
So, the workaround is to; "first" move to "about:blank" i.e.: Navigate("about:blank"), before moving to the secure site.

private void FrmTest_Load(object sender, EventArgs e)
    {
    //Navigate to about:blank
    string oURL = "about:blank";
    webBrowser1.Navigate(oURL);

    //Notify the WebBrowser object about the client site.
    //The client site, informs an embedded object of its display location
    object obj = webBrowser1.ActiveXInstance;
    IOleObject oc = obj as IOleObject;
    oc.SetClientSite(this as IOleClientSite);
    } 

IOleClientSite member definitions:
public void SaveObject()
    {
    // TODO: Add FrmTest.SaveObject implementation
    }


    public void GetMoniker(uint dwAssign, uint dwWhichMoniker, object
    ppmk)
    {
    // TODO: Add FrmTest.GetMoniker implementation
    }


    public void GetContainer(object ppContainer)
    {
    ppContainer = this;
    }


    public void ShowObject()
    {
    // TODO: Add FrmTest.ShowObject implementation
    }


    public void OnShowWindow(bool fShow)
    {
    // TODO: Add FrmTest.OnShowWindow implementation
    }


    public void RequestNewObjectLayout()
    {
    // TODO: Add FrmTest.RequestNewObjectLayout implementation
    } 

Interface IServiceProvider explicit implementation:

public int QueryService(ref Guid guidService, ref Guid riid, out IntPtr ppvObject)
    {
    int nRet = guidService.CompareTo(IID_IAuthenticate); // Zero returned if the compared objects are equal
    if (nRet == 0)
    {
    nRet = riid.CompareTo(IID_IAuthenticate); // Zero returned if the compared objects are equal
    if (nRet == 0)
    {
    //This method returns an interface pointer that represents the requested interface on the specified object. It is particularly useful if you have an unmanaged method that expects to be passed an interface pointer; Add to the COM interface reference count.
    ppvObject = Marshal.GetComInterfaceForObject(this, typeof(IAuthenticate));
    return S_OK;
    }
    }
    ppvObject = new IntPtr();
    return INET_E_DEFAULT_ACTION;
    }

The main, IAuthenticate implementation
public int Authenticate(ref IntPtr phwnd, ref IntPtr pszUsername,
    ref IntPtr pszPassword)
    {
    //Copies the contents of a managed String to a block of memory allocated from the unmanaged COM task allocator.
    IntPtr strUser = Marshal.StringToCoTaskMemAuto(txtUserID.Text);
    IntPtr strPassword = Marshal.StringToCoTaskMemAuto(txtPassword.Text);


    pszUsername = strUser;
    pszPassword = strPassword;
    return S_OK;
    }

    }

Here is how to pass the proxy credentials in WebBrowser control:
Uri uri = new Uri("http://www.somewheresurl.com");

//Alternatively, you may use System.Text.UnicodeEncoding.UTF8.GetBytes()
string additionalHeaderInfo = "Authorization: Basic " + Convert.ToBase64String(Encoding.ASCII.GetBytes("MyUsername" + ":" + "MyPassword")) + System.Environment.NewLine;

webBrowser1.Navigate(uri, null, null, additionalHeaderInfo);
This is for me, and for those who came across similar sweet issue. Hope this helps.

Related Posts

Popular Posts