Wednesday, October 28, 2015

ADO.Net - Retrieve Table Schema Information using CommandBehavior.SchemaOnly

1. Introduction

When you are working on Ado.net sometimes you need to get the table schema information like Column name and data type of that column. In this example article, I will show getting the table schema information. Have a look at the below screenshot:

Ado.net Retrieve Table Schema
Fig1. Table Schema for Pubs.Discounts

Here, we are going to display the table schema marked as 2 for the table “discounts” which marked as 1.


2. About the example

The screenshot of the sample is shown below:

Ado.net Retrieve Table Schema
Fig 2. Example Application Screenshot

The item marked as 1 is a multiline textbox control used to display the table schema shown in section 1 of this article. The “Get Table Schema” button (Marked as 2) once clicked displays the schema information of the discount table in the multi-line textbox. Before using the sample, you should configure the connection string to the Pubs database. Setting the connection string for the “NorthWnd“ database is shown in the below video. The same procedure can be followed to set the connection string for the Pubs database (Name the connection string as PUBSDB) as the table DISCOUNTS resides in it.

Video 1: Forming the connection string



3. Code behind the form

1) First, the required namespace is used in the .cs file. Code is below:

 //Sample 01: For accessing the required functionality
using System.Data.SqlClient;

2) Next, connection to the Pubs database is established by making use of the connection string “PubsDB” formed in the previous section. The SqlConnection object is created using the connection string which is stored as the application property. Once the connection object is created, the Open method on the object is called to establish the connection to the Pubs database. Below is the code:

//Sample 2.1: Open Connection to SQL
SqlConnection Con = new SqlConnection(Properties.Settings.Default.PubsDB);
Con.Open();

When you are typing the above code, the intelli-sense displays the Connection string name when it is configured as the application property. The below screenshot shows the that:
Ado.net Retrieve Table Schema
Fig 3. Intellisence for ConnectionString property

3) After the successful connection, a SqlCommand object is created on the DISCOUNTS table of the PUBS database. Then, ExecuteReader method on the Cmd object is called by passing the parameter CommandBehavior.SchemaOnly. This informs the command object that we need only the schema information on the underlying command object in our case it is schema information of Discounts table. The code is given below:

//Sample 2.2: Create Command Object and get schema reader
SqlCommand Cmd = new SqlCommand("Select * from Discounts", Con);
SqlDataReader Schema_Reader = Cmd.ExecuteReader(CommandBehavior.SchemaOnly);

4) Now we have the Schema_Reader of SqlDataReader which we can to iterate through to get the column information of the DISCOUNTS table. Inside the iteration loop, the schema information is retrieved and displayed in the multiline textbox. The code for this is given below:

//Sample 2.3: Iterate through the Reader and get the Schema information
int Total_Column = Schema_Reader.FieldCount;
for (int i = 0; i < Total_Column; i++)
{
    string schema_inormation = string.Format("Column Name: {0}, Column Type: {1}",
        Schema_Reader.GetName(i), Schema_Reader.GetDataTypeName(i));
    txtSchemaInfo.AppendText(schema_inormation + Environment.NewLine);
}

//Sample 2.4 : Close all the objects
Schema_Reader.Close();
Con.Close();


Source code : Download

Wednesday, October 14, 2015

C# - Creating Custom Debugger Visualizer

1. Introduction

"Debugger Visualizers" are visual studio IDE’s debugging component. These components represent the variables and class objects in meaningful form so that it can be easily visualised. Say; for example, you have class ABC to store the “Passport Size” photo of a person and using the visualizer you can see the photo of the person while debugging the object of ABC. The below video shows the existing visualizer for the strings:

Video 1: Visualizer for String


2. About the Example

The Example is divided into two parts. One is a Class Library project called VisualStack which can be opened through visualStack.sln. The another project is a Stack & Queue Example downloaded from this website (Check the C# Section). The class library project is the one we used to create the “Custom Debugger Visualizer” for the Dot.net Stack Object.  Once the project is ready and visualizer is deployed, you can invoke the visualizer for any Stack object as your visual studio IDE understands how to visualise the Stack object through the deployed class library project.

Have a look at the below screenshot:

Custom Debugger Visualizer
Fig 1 - About the Debugger Visualizer Example

The Item marked as 1 show five elements pushed into the Stack object of the Dot Net Framework (2.0). Item marked as two shows the Visual studio debugger showing the Visualizer written by us. If you write multiple visualizers for the same Stack object, all are listed here and you can select the default one by placing the check mark. Clicking the lens icon invokes the default visualizer. The item marked as three shows the simple stack visualizer which lists all the items in the stack. Left is the Top of the Stack and right is the bottom of the stack.


3. Create Class Library Project

Follow the below steps to Setup the Project (or) Watch the video in this Section End.

1) Create a class library project and this project will be deployed to Visual Studio 2005 IDE so that IDE understands the visualizer exists for System.Collections.Stack. Below screen shot shows the Project creation steps:

Custom Debugger Visualizer
Fig 2 - Creating Class Library Project


2) Once the Project is created, Add dot.net assembly reference to the project, so that we can make use the Visual Studio debugger visualizer. The screen shot below shows adding the required reference - Microsoft.VisualStudio.DebuggerVisualizers. 

Custom Debugger Visualizer
Fig 3 - Adding Project Reference

3) After the reference is added to the class library project, add a form to the Project and name the file as StackView.cs. This is shown in the below screenshot:

Custom Debugger Visualizer
Fig 4 - Adding form to the Project

Video 2: Starting the Project


4. The DebuggerVisualizer Attribute

In the previous step, we had created a class library project. Now we will set the DebuggerVisualizer attribute to our class library and note that the DebuggerVisualizer attribute will be set at the assembly level. First, we will start using the required namespaces as shown in the below code:

//Sample 01: Add Reference, then use the below
using Microsoft.VisualStudio.DebuggerVisualizers;
using System.Diagnostics;
using System.Collections;

The namespace Microsoft.VisualStudio.DebuggerVisualizers is used to access the DialogDebuggerVisualizer which we will see soon. The “System.Diagnostics” namespace is used to have access to DebuggerVisualizer Attribute". Finally in order to use the Stack, System.Collection namespace is used by this class library project.

Once the required namespaces are included, the visualizer attribute is set to the class library project. This attribute is set at assembly level by making use of the “assembly:<attribute>” notation. The code for the attribute is shown below:


//Sample 02: Add the attribute for Custom Visualizer
[ assembly: DebuggerVisualizer(
    typeof(CustomVisualizer.StackVisualizer),
    typeof(VisualizerObjectSource),
    Target=typeof(Stack),
    Description="Custom Visualizer for Stack")
]
namespace CustomVisualizer
{

To have a better understanding of the above attribute, have a look at the below picture:

Custom Debugger Visualizer
Fig 5 - Debugger Visualizer attribute Explained

The attribute shown in the above picture is by-parted and explained as below:
1: The debugger visualizer attribute set at the assembly level
2: The class used as the Custom visualizer. In our case, StackVisualizer class takes that responsibility of visualising the Stack object
4: The class that we want to represent visually. In our case, we want to write custom visualizer for Stack object
3: The bridge between Visualizer and object that needs to be visualised. In our case, the Bridge is between Stack and StackVisualizer
5: Description string that appears in the debug window (Under the lens icon)


5. Visualizer Dialog


In section three we already added a form to the class library project. Open the form and add a text box control to it. Set the multi-line property to true and also provide proper docking and anchor property so that it occupies entire area of the form even when the form is resized. The form is shown below:

Custom Debugger Visualizer
Fig 6 - Adding Visualizer Dialog

The video below shows setting up the form with the multi-line box control in it.

Video 3: Setting up the Visualizer Form


After the form design, access the code for the form and add a public function called SetStack. This function takes the Stack object and displays the content of it in the multi-line textbox control. To iterate through the stack object, "IEnumerable" is extracted from the stack object and it is iterated through the foreach construct. The code for that is shown below:

//Sample 05: Implement the set stack which displays the Stack Content
public void SetStack(Stack stack)
{
    IEnumerable ItemCol = (IEnumerable)stack;
    foreach (int item in ItemCol)
        txtStackView.Text = txtStackView.Text + "[" + item.ToString() + "] ";
}


6. Coding the Visualizer


1) Inherit the default class provided by the class library project from DialogDebuggerVisualizer. Now our class has the behavior of the debugger visualizer. The code is given below:
//Sample 04: Inherit from DialogDebuggerVisualizer
public class StackVisualizer: DialogDebuggerVisualizer

2) Next override the “show() method" of the DialogDebuggerVisualizer. The parameter "IDialogVisualizerService" is used to display the dialog that we developed in the previous step. The second parameter "IVisualizerObjectProvider" is used to supply the target object for which we are writing the custom visualizer. In our case, the second parameter supplies the Stack object from the debug session. The function signature is shown below: 

//Sample 05: Override the show method
protected override void Show(IDialogVisualizerService windowService,
    IVisualizerObjectProvider objectProvider)
{

3) The stack object from the debug session is taken from the objectprovider and stored in a reference called stack. The form created in section 5 of this article is instantiated and stored in the reference frm. The Stack object from debug session is supplied to the visualizer form by calling the “SetStack” method and we know that the SetStack method iterates through the stack content and displays that in the multi-line textbox. Once everything is ready, the form is displayed by calling “ShowDialog() method" of the IDialogVisualizerService. The Code is shown below:  

//Sample 06: Handover Stack from debug session and
// display information the way you want
Stack stack = (Stack) objectProvider.GetObject();
StackView frm = new StackView();
frm.SetStack(stack);
windowService.ShowDialog(frm);

7. Deploying the Custom Visualizer

Once the coding of class library project (i.e.) our custom visualizer project is completed, build the project to create the dll output. This dll should be deployed to the debugger visualizer folder of the Microsoft Visual studio installation location. In the below screenshot the location in which the debugger to be copied is shown. The yellow highlighted dll are already existing dll(s) and blue highlighted one is custom visualizer developed by us. Once the copy is over, restart all the visual studio instance (if already running). After the restart, you can see custom visualizer invoked for "System.Collections.Stack".

Custom Debugger Visualizer
Fig 7 - Deploying custom debugger Visualizer
  

Video 4: Deploying and using Stack Visualizer


Source Code: Download


Like this site? Tell it to your Firend :)