Query to get multiple rows into string

DECLARE @String VARCHAR(100)

SET @String =( SELECT CONVERT(nvarchar(10),PC.HierarchyId)+'|'+HierarchyName+'~'

FROM PortalHierarchyCustomers PC INNER JOIN PortalEntitiyHierarchies PH

on PC.HierarchyId = PH.HierarchyId

INNER JOIN PortalEntityTypes PE on PE.EntityTypeId = PH.EntityTypeId

WHERE (InternalId = 1) and PE.EntityTypeName='Area'  for xml path(''))

SELECT LEFT(@String, LEN(@String) - 1)

SQL Server Tips

How many stored procedure's  we are using given table?

SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%PropertyLookup%'

----Option 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'
----Option 2
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'

IEnumerable vs IQueryable in LINQ

     How to write code via IEnumerable and IQueryable and what the differences are between them. IEnumerable and IQueryable are used for data manipulation in LINQ from the database and collections. For getting the data from the database, I have created a table named "Employee" that has some data and looks like:

Then creating the Data Context class (.dbml class) in your project that converts the database table named "Employee" as a class.
Now I will tell you the functionality and some basic differences between IEnumerable and IQueryable using the object of the Data Context class..

IEnumerable Code



SQL statement after execution of above query

After the execution of line number 18, the SQL statement will look like the following until the end:

IQueryable Code



SQL statement after execution of the preceding query

After the execution of line number 22, the SQL statement will look like:

But after the execution of line number 23, SQL statement will add the Top for the filtering.

In both syntaxes I am accessing the data from the Employee table and then taking only 3 rows from that data. 

Differences

IEnumerable

1. IEnumerable exists in the System.Collections namespace.
    
2. IEnumerable is suitable for querying data from in-memory collections like List, Array and so on.
    
3. While querying data from the database, IEnumerable executes "select query" on the server-side, loads data in-memory on the client-side and then filters the data. 
    
   
4. IEnumerable is beneficial for LINQ to Object and LINQ to XML queries.

IQueryable

1. IQueryable exists in the System.Linq Namespace.
   
   
    
2. IQueryable is suitable for querying data from out-memory (like remote database, service) collections. 
3. While querying data from a database, IQueryable executes a "select query" on server-side with all filters.
   
   
   
4. IQueryable is beneficial for LINQ to SQL queries.

Simple SELECT, INSERT, UPDATE and DELETE Using LINQ to SQL

Language-INtegrated Query (LINQ) is a Microsoft .NET Framework component that adds native data querying capabilities to .NET languages. In other words LINQ has the power of querying on any source of data (Collection of objects, database tables or XML Files). We can easily retrieve data from any object that implements the IEnumerable<T> interface and any provider that implements the IQueryable<T> interface.

Microsoft basically divides LINQ into the following three areas:

• LINQ to Object : Queries performed against in-memory data
• LINQ to ADO.Net
• LINQ to SQL (formerly DLinq) : Queries performed against the relation database; only Microsoft SQL Server is supported.
• LINQ to DataSet : Supports queries by using ADO.NET data sets and data tables.
• LINQ to Entities : Microsoft ORM solution
• LINQ to XML (formerly XLinq) : Queries performed against the XML source.

LINQ to SQL

LINQ to SQL translates our actions to SQL and submits the changes to the database. Here we will perform Select, Insert, Update and Delete operations on a COURSE table.

Step 1: Create a COURSE Table in the database

CREATE TABLE COURSE

{

    course_id int identity(1,1) primary key,

    course_name navarchar(50) not null,

    course_desc nvarchar(255,

    modified_date date

}

Step 2: Create a ContextData file using the Object Relational Designer:

Create a new item, select the LINQ to SQL classes (as shown in the following figure) and name it Operation.dbml.

After clicking the Add button the ContextData file is created. Now we should drag all the tables onto the left-hand side of the designer and save (as shown in the following figure). This will create all the mappings and settings for each table and their entities.

For .dbml files the database connection string is defined in the web.config file as:

<connectionStrings>

    <add name="nageshConnectionString" connectionString="Data Source=.\sqlexpress;Initial Catalog=nagesh;Persist Security Info=True;User ID=sa;Password=nagesh"

      providerName="System.Data.SqlClient" />

  </connectionStrings>

We can use a connection string from the web.config file or we can pass a connection string as a parameter in the constructor of the DataContext class to create an object of the DataContext class.

The SELECT Operation

private void GetCourses()
{
      //create DataContext object
      OperationDataContext OdContext = new OperationDataContext();
      var courseTable = from course in OdContext.GetTable<COURSE>() select course;
      //grdCourse is gridview id
      grdCourse.DataSource = courseTable;
      grdCourse.DataBind();
}

The INSERT Operation

private void AddNewCourse()
{
      //Data maping object to our database
      OperationDataContext OdContext = new OperationDataContext();
      COURSE objCourse = new COURSE();
      objCourse.course_name = "B.Tech";
      objCourse.course_desc = "Bachelor Of Technology";
      objCourse.modified_date = DateTime.Now;
      //Adds an entity in a pending insert state to this System.Data.Linq.Table<TEntity>and parameter is the entity which to be added
      OdContext.COURSEs.InsertOnSubmit(objCourse);
      // executes the appropriate commands to implement the changes to the database
      OdContext.SubmitChanges();
}

The Update Operation

private void UpdateCourse()
{
      OperationDataContext OdContext = new OperationDataContext();
      //Get Single course which need to update
      COURSE objCourse = OdContext.COURSEs.Single(course => course.course_name == "B.Tech");
      //Field which will be update
      objCourse.course_desc = "Bachelor of Technology";
      // executes the appropriate commands to implement the changes to the database
      OdContext.SubmitChanges();
 }

The DELETE Operation

private void DeleteCourse()
{
      OperationDataContext OdContext = new OperationDataContext();
      //Get Single course which need to Delete
      COURSE objCourse = OdContext.COURSEs.Single(course => course.course_name == "B.Tech");
      //Puts an entity from this table into a pending delete state and parameter is the entity which to be deleted.
      OdContext.COURSEs.DeleteOnSubmit(objCourse);
      // executes the appropriate commands to implement the changes to the database
      OdContext.SubmitChanges();
}

Conculsion

To perform select, insert, update and delete operations we create a table and create a data context class; in other words a dbml file. In this file designer view we drag and drop the COURSE table from the Server Explorer. This data context class is an Object and table mapping and we perform the operation on the object and database updated according to the action using the submitChanges() method.

ASP.NET Page Life Cycle

Init	This event happens in the ASP.NET page and can be used for: Ø             Creating controls dynamically, in case you have controls to be created on runtime. Ø             Any setting initialization. Ø             Master pages and the settings. Ø         In this section, we do not have access to viewstate, postedvalues and neither the controls are initialized.
Load	In this section, the ASP.NET controls are fully loaded and you write UI manipulation logic or any other logic over here.
Validate	If you have valuators on your page, you would like to check the same here.
Render	It’s now time to send the output to the browser. If you would like to make some changes to the final HTML which is going out to the browser, you can enter your HTML logic here.
Unload	Page object is unloaded from the memory.

                                                                                                                                          

Seq	Events	Controls Initialized	View state Available	Form data Available	What Logic can be written here?
1	Init	No	No	No	Note: You can access form data etc. by using ASP.NET request objects but not by Server controls.Creating controls dynamically, in case you   have controls to be   created on runtime. Any settinginitialization.Master pages and them settings. In this section, we do not have access to viewstate , posted values and neither the controls are initialized.
2	Load view state	Not guaranteed	Yes	Not guaranteed	You can access view state and any synch logic where you want viewstate to be pushed to behind code variables can be done here.
3	PostBackdata	Not guaranteed	Yes	Yes	You can access form data. Any logic where you want the form data to be pushed to behind code variables can be done here.
4	Load	Yes	Yes	Yes	This is the place where you will put any logic you want to operate on the controls. Like flourishing a combobox from the database, sorting data on a grid, etc. In this event, we get access to all controls,   viewstate and their posted values.
5	Validate	Yes	Yes	Yes	If your page has validators or you want to execute validation for your page, this is the right place to the same.
6	Event	Yes	Yes	Yes	If this is a post back by a button click or a dropdown change, then the relative events will be fired. Any kind of logic which is related to that event can be executed here.
7	Pre-render	Yes	Yes	Yes	If you want to make final changes to the UI objects like changing tree structure or property values, before these controls are saved in to view state.
8	Save view state	Yes	Yes	Yes	Once all changes to server controls are done, this event can be an opportunity to save control data in to view state.
9	Render	Yes	Yes	Yes	If you want to add some custom HTML to the output this is the place you can.
10	Unload	Yes	Yes	Yes	Any kind of clean up you would like to do here.