Fun with GO Command of SQL.

In previous post, I explained using of “GO” command.

In this post i am going to explain how to rename “GO” command and use another word that you want as a batch separator.

STEPS:

Tools à Options…à Query Execution à SQL Server à General.

Suppose if i rename 'GO' to 'MYGO' then it can be use in following way:

         

GO Command in SQL

GO is not a T-SQL statement. GO is a command used for signalling the end of a SQL batch. Batch is nothing but a group of one or more Transact-SQL statements at the same time from an application to SQL server for execution.

GO is a utility command that requires no permissions. It can be executed by any user.
Please refer below image for more idea:

Difference between DataView.Table and DataView.ToTable

Both DataView.Table and DataView.ToTable return a DataTable from a DataView.

DataView.Table: It gives you the underlying data, with no changes from the dataview's sorting or filtering.

DataView.ToTable: It gives you the data in the dataview after filtering and sorting.

Please refer following image for more details:

The Spatial Result tab in SQL Server.

Many days ago, i found very interesting feature of SQL server 2008 and above which is Spatial Result Tab so today i am going to show that feature.

SQL Server Management Studio 2008 and above introduces a new feature, the Spatial Results tab, which allows you to visualise the results of ad-hoc spatial queries directly in SQL Server Management Studio. To see the new Spatial Results tab, you simply need to execute and SELECT query that selects at least one column of geometry or geography data.

After you execute SQL query, you will be able to see something which is shown below image:

Download Query

How to create your own code snippet in VS?

Code snippet is simply XML file which contains the code that we need to use most frequently while coding. I also like this feature very much because it save our developing time and increase productivity. You can create your own code snippet very easily. You can also add references and replacement parameters to your code.

For that first you need to install Snippet Designer Extension Tool in visual studio. You can also prepare snippet file(*.snippet) manually and add/import it using: Tool -> Code Snippets Manager (Ctrl + K, Ctrl + B). In this post i would like show you how to create and use snippet using Snippet Designer tool.

Steps to install Snippet Designer:

Goto Tools à Extension Manager.. à It opens Extension Manager window, Now you can install “Snippet Designer” from Online Gallery tab.

How to Create Snippet:

• Just write your most usable code that you want to insert in snippet, just right click on code block and click Export as Snippet.

• Now you can set Snippet Name, Language and replacement setting.

How to Use Snippet:

• Just press Ctrl + K, Ctrl + X in code behind file, it will open intellisense of all available snippet, so just go through snippet that you want. 

BringToFront() and SendToBack() in windows form.

When you work with multiple layer design means when controls goes to overwrite with each other in windows form then Bring to front and Send to back comes handy. You can use these in both design and code behind.

In Design:

Just right click on control that you want to move front or back.

In Code Behind:

panelWait.BringToFront()

panelWait.SendToBack()

Solved : Cannot add a DataRelation or Constraint that has different Locale or CaseSensitive settings between its parent and child tables.

Many days ago, I was implementing parent-child relation in windows DataGridView using adding relation in DataSet among two DataColumns of two different DataTables and I faced below error.

Error Message :

Cannot add a DataRelation or Constraint that has different Locale or CaseSensitive settings between its parent and child tables.

Solution:

I found alternate solution from internet but below solution works for me fine. Its due to two datatable Locale property has different value. If you are going to add relationship then Locale of two datatable must be same. 

Mistake:

1) tblCustomer DataTable Properties:

2) tblOrder DataTable Properties:

How to show parent/child relation in windows DataGridView?

Today I am going to show you how to bind parent/child relation in windows form gridview. It’s very easy to show parent/child relation in grid using third party controls but I want to show relation without using additional controls. I found related help from internet but I like to show parent-child relation by adding DataSet relation on datacolums of DataTable.

C# Code:

       private void BindGridView()

        {

            // Create Tables

            DataTable tblCustomer = new DataTable("tblCustomer");

            DataTable tblOrder = new DataTable("tblOrder");

            // Create DataSet

            DataSet tblDataSet = new DataSet();

            // Create Columns and Add to Tables

            tblCustomer.Columns.Add("ID", typeof(int));

            tblCustomer.Columns.Add("CustomerName", typeof(string));

            tblOrder.Columns.Add("ID", typeof(int));

            tblOrder.Columns.Add("Order", typeof(string));

            tblOrder.Columns.Add("CustomerID", typeof(int));

            // Add Test Data

            tblCustomer.Rows.Add(1, "Bhaumik");

            tblCustomer.Rows.Add(2, "Ramesh");

            tblCustomer.Rows.Add(3, "Suresh");

            tblOrder.Rows.Add(1, "Order1.1", 1);

            tblOrder.Rows.Add(2, "Order1.2", 1);

            tblOrder.Rows.Add(3, "Order1.3", 1);

            tblOrder.Rows.Add(4, "Order2.1", 2);

            tblOrder.Rows.Add(5, "Order3.1", 3);

            tblOrder.Rows.Add(6, "Order3.2", 3);

            // Add Tables to DataSet

            tblDataSet.Tables.Add(tblCustomer);

            tblDataSet.Tables.Add(tblOrder);

            // Create Relation

            tblDataSet.Relations.Add("CustOrderRelation",

                tblCustomer.Columns["ID"], tblOrder.Columns["CustomerID"]);

            BindingSource bsCustomer = new BindingSource();

            bsCustomer.DataSource = tblDataSet;

            bsCustomer.DataMember = "tblCustomer";

            BindingSource bsOrder = new BindingSource();

            bsOrder.DataSource = bsCustomer;

            bsOrder.DataMember = "CustOrderRelation";

            // Bind Data to DataGridViews

            dgvCustomer.DataSource = bsCustomer;

            dgvOrder.DataSource = bsOrder;

        }

Demo:

Difference between And("&") and AndAlso("&&") Operator

And(“&”) Operator:

  Ø  It evaluates both sides even first condition goes wrong.

  Ø  It takes longer to execute for long expression.

  Ø  Example:

‘It throws exception.

If dt IsNot Nothing And dt.Rows.Count > 0

        ‘Bind Gridview Logic

End If

AndAlso(“&&”) Operator:

  Ø  It only evaluates the right side if the left side is true.

  Ø  Its faster compare to And operator.

  Ø  Example:

‘Works fine without exception.

If dt IsNot Nothing AndAlso dt.Rows.Count > 0

        ‘Bind Gridview Logic.

Else

        ‘No data found.

End If

Note: Same logic can apply for Or(“|”) and OrElse(“||”)

Difference between Convert.ToString() and object.ToString()?

Both statement are use to convert into string but the main difference is in handling of NULL value.

Convert.ToString() : It handles NULL reference exception.

.ToString() : It doesn’t handle NULL reference exception.

 So for good programming practice, always use Convert.ToString() for safety.

Code Snippet :

// Throws exception

int salary = null;

string oldSalary = salary.tostring();

// Return NULL value

int salary = null;

string oldSalary = Convert.ToString(salary);

Debug Error : No symbols have been loaded for this document.

Many days ago i found very wired issue while debugging and i wasted much time in googling to solve it out and after solved it i found that it was very minor issue.

Issue : I worked on windows service and after install it, i want to debug that windows service so first i attached that service process to debug it using (Ctrl C+P) in visual studio and i got below error message.

Error : “The breakpoint will not currently be hit. No symbols have been loaded for this document.”

Solution : I found many solution from internet but solution works for me is listed below:

Its due to my application made in framework version 4.0 and i was trying to attach process with version below v2.0 so make sure your framework version with Managed code version as its shown below.

"Surround with" feature in Visual Studio.

“Surround with” is a very nice features in visual studio IDE. The overall objective of this feature is to surround the code snippets with a set of statement.

Let’s assume that, i have piece of code block and i want to add try-catch surround that code so rather than cut-copy code block “Surround With” feature comes handy.

For that, you need to select piece of code block that you want to surround then right click or simply press shortcut keys: “CTRL+K, S / CTRL +K , CTRL + S”. It will open intellisense from that select any option that you want.

Before “Surround with”

After “Surround with”

Difference between UNION and UNION ALL in SQL.

Both UNION and UNION ALL concatenate the result of two different SQLs. They differ in the way they handle duplicates.

Ø   UNION performs a DISTINCT on the result set, eliminating any duplicate rows.

Ø   UNION ALL does not remove duplicates, and it therefore faster than UNION.

Note: While using this commands all selected columns need to be of the same data type.

Example: If we have two tables, 1) Employee and 2) Customer

1) Employee table data: 

2) Customer table data:

3) UNION Example (It removes all duplicate records):

4) UNION ALL Example (It just concatenate records, not eliminate duplicates, so it is faster than UNION):

Venn diagram (Visual Representation) of SQL joins.

We all knows SQL joins are used to fetch data from different tables but if for fresher its very easy to understand types of JOINS by visually rather than queries. So today i am going to write article based on that so user can get idea easily.

Ø  LEFT OUTER JOIN: produces a complete set of records from Table A, with the matching records (where available) in Table B. If there is no match, the right side will contain null.

Ø  RIGHT OUTER JOIN: produces a complete set of records from Table B, with the matching records (where available) in Table A. If there is no match, the left side will contain null.

Ø  FULL OUTER JOIN: produces the set of all records in Table A and Table B, with matching records from both sides where available. If there is no match, the missing side will contain null.

Ø  INNER JOIN: produces only the set of records that match in both Table A and Table B.

How to know tab index of each control in design mode?

When you working with windows application then tab index plays an important role and makes application more effective and professional. But if form contain lots of input controls or maybe you add new controls later on then manage tab index of each and every control is very time consuming and cumbersome. So to know tab index of every control in design mode follow following steps:

   Ø  Open form in design mode.

   Ø  Click on “View” from toolbar à “Tab Order” at bottom.

What is use of "SET NOCOUNT" in SQL?

Ø  When SET NOCOUNT is ON, the count (indicating the number of rows affected by a Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is returned. It is used with any SELECT, INSERT, UPDATE, DELETE statement.

Ø  The setting of SET NOCOUNT is set at execute or run time and not at parse time.

Ø  SET NOCOUNT ON improves stored procedure (SP) performance.

Ø  Syntax: SET NOCOUNT { ON | OFF }

Example of SET NOCOUNT ON:

Example of SET NOCOUNT OFF: