Combining multiple rows in single row

DECLARE @combinedString VARCHAR(MAX)
SELECT @combinedString = COALESCE(@combinedString + ‘, ‘, ”) + stringvalue
FROM jira.customfieldValue
WHERE customfield = 12534
AND ISSUE = 19602

SELECT @combinedString as StringValue

Css issue in html and .aspx (style does not apply in .net control)

Suppose I’ve some predefined style for <h3> tag in css. And it applies on browser very well when i write,

<h3> Add </h3>

But when I write  <h3><asp:Label ID=”lblTitle” runat=”server” Text=”Add”></asp:Label></h3>,

It does not show me the style. In this case I used asp:Localize,

<h3><asp:Localize ID=”lcTitle” runat=”server” Text=”Add”></asp:Localize></h3>

And now it shows style well.

Linq to SQL Vs. Linq to Objects Vs. Linq to Entities

Ref: http://www.codeproject.com/Articles/215712/LINQ-to-SQL-Basic-Concepts-and-Features

Below is a diagram showing the usage of LINQ to SQL in a .NET application:

Pic02.jpg

Comparing LINQ to SQL with LINQ to Objects

In the previous article, we used LINQ to query in-memory objects. Before we dive further to the world of LINQ to SQL, we will first look at the relationships between LINQ to SQL and LINQ to Objects.

Followings are some key differences between LINQ to SQL and LINQ to Objects:

  • LINQ to SQL needs a Data Context object. The Data Context object is the bridge between LINQ and the database. LINQ to Objects doesn’t need any intermediate LINQ provider or API.
  • LINQ to SQL returns data of type IQueryable<T> while LINQ to Objects returns data of type IEnumerable<T>.
  • LINQ to SQL is translated to SQL by way of Expression Trees, which allow them to be evaluated as a single unit and translated to the appropriate and optimal SQL statements. LINQ to Objects does not need to be translated.
  • LINQ to SQL is translated to SQL calls and executed on the specified database while LINQ to Objects is executed in the local machine memory.

The similarities shared between all aspects of LINQ are the syntax. They all use the same SQL like syntax and share the same groups of standard query operators. From a language syntax point, working with a database is the same as working with in-memory objects.

LINQ to Entities

For LINQ to SQL, another product that you will want to compare with is the .NET Entity Framework. Before comparing LINQ to SQL with the Entity Framework, let’s first see what Entity Framework is.

ADO.NET Entity Framework (EF) was first released with Visual Studio 2008 and .NET Framework 3.5 Service Pack 1. So far, many people view EF as just another ORM product from Microsoft, though by design it is supposed to be much more powerful than just an ORM tool.

With Entity Framework, developers work with a conceptual data model, an Entity Data Model, or EDM, instead of the underlying databases. The conceptual data model schema is expressed in the Conceptual Schema Definition Language (CSDL), the actual storage model is expressed in the Storage Schema Definition Language (SSDL), and the mapping in between is expressed in the Mapping Schema Language (MSL). A new data-access provider, EntityClient, is created for this new framework but under the hood, the ADO.NET data providers are still being used to communicate with the databases. The diagram below, which has been taken from the July 2008 issue of the MSDN Magazine, shows the architectures of the Entity Framework.

Pic03.jpg

From the diagram, you can see that LINQ is one of the query languages that can be used to query against Entity Framework Entities. LINQ to Entities allows developers to create flexible, strongly typed queries against the Entity Data Model (EDM) by using LINQ expressions and the LINQ standard query operators. It is the same as what LINQ to SQL can do, though LINQ to Entities supports more features than LINQ to SQL, like multiple-table inheritance, and it supports many other mainstream RDBMS databases besides Microsoft SQL Server, like Oracle, DB2, and MySQL.

Comparing LINQ to SQL with LINQ to Entities

As described earlier, LINQ to Entities applications work against a conceptual data model (EDM). All mappings between the languages and the databases go through the new EntityClient mapping provider. The application no longer connects directly to a database or sees any database-specific construct; the entire application operates in terms of the higher-level EDM model.

This means that you can no longer use the native database query language; not only will the database not understand the EDM model, but also current database query languages do not have the constructs required to deal with the elements introduced by the EDM such as inheritance, relationships, complex-types, etc.

On the other hand, for developers that do not require mapping to a conceptual model, LINQ to SQL enables developers to experience the LINQ programming model directly over an existing database schema.

LINQ to SQL allows developers to generate .NET classes that represent data. Rather than mapping to a conceptual data model, these generated classes map directly to database tables, views, Stored Procedures, and user defined functions. Using LINQ to SQL, developers can write code directly against the storage schema using the same LINQ programming pattern as previously described for in-memory collections, Entities, or the DataSet, as well as other data sources such as XML.

Compared to LINQ to Entities, LINQ to SQL has some limitations, mainly because of its direct mapping against the physical relational storage schema. For example, you can’t map two different database entities into one single C# or VB object, and underlying database schema changes might require significant client application changes.

So in summary, if you want to work against a conceptual data model, use LINQ to Entities. If you want to have a direct mapping to the database from your programming languages, use LINQ to SQL.

The table below lists some supported features by these two data access methodologies:

Features LINQ to SQL LINQ to Entities
Conceptual Data Model No Yes
Storage Schema No Yes
Mapping Schema No Yes
New Data Access Provider No Yes
Non-SQL Server Database Support No Yes
Direct Database Connection Yes No
Language Extensions Support Yes Yes
Stored Procedures Yes Yes
Single-table Inheritance Yes Yes
Multiple-table Inheritance No Yes
Single Entity from Multiple Tables No Yes
Lazy Loading Support Yes Yes

We will use LINQ to SQL in this article, because we will use it in the data access layer, and the data access layer is only one of the three layers for a WCF service. LINQ to SQL is much less complex than LINQ to Entities, so we can still cover it in the same article with WCF. However, once you have learned how to develop WCF services with LINQ to SQL through this article, and you have learned how to use LINQ to Entities through some other means, you can easily migrate your data access layer to using LINQ to Entities.

IEnumerable Vs. IQueryable

Ref: http://www.codeproject.com/Articles/231163/IQueryable-Vs-IEnumerable-in-terms-of-LINQ-to-SQL

IEnumerable<employee> emp = 
         dc.Employees.Where(x => x.Desc.StartsWith("soft"));
emp = emp.Take(1);

But later on, I found it was taking too much time to get the count.

So to try something else, I uses an IQueryable to store the result and to get the count.

IQueryable<employee> emplist = 
         dc.Employees.Where(x => x.Desc.StartsWith("soft"));
emplist = emplist.Take(1);

After using IQueryable, I found that I got the result faster than the last time.

To find out what was causing this, I used SQL Profile to find out the SQL query fired by the code.

The first block of code fired the following query, i.e., the one which uses IEnumrable to store the output of the LINQ query.

SELECT [t0].[Id], [t0].[Name], [t0].[Address], [t0].[Desc] AS [Desc]
FROM [dbo].[Employee] AS [t0]
WHERE [t0].[Desc] LIKE @p0

The second block of code fired the following query, i.e., which uses IQuerable to store the output of the LINQ query.

SELECT TOP (1) [t0].[Id], [t0].[Name], [t0].[Address], [t0].[Desc] AS [Desc]
FROM [dbo].[Employee] AS [t0]
WHERE [t0].[Desc] LIKE @p0

The major difference between the queries is the first one doesn’t contain the TOP clause to get the first record but the second one makes use of TOP to get the first record.

Difference

The major difference is that IEnumerable will enumerate all elements, while IQueryable will enumerate elements (or even do other things) based on a query. In the case of the IQueryable, the LINQ query gets used by IQueryProvider which must be interpreted or compiled in order to get the result. I.e., the extension methods defined for IQueryable take Expression objects instead of Func objects (which is what IEnumerable uses), meaning the delegate it receives is an expression tree instead of a method to invoke.

IEnumerable is great for working with in-memory collections, but IQueryable<t> allows for a remote data source, like a database or web service.

Paypal payment

Response.Redirect(“PaypalSubmit.aspx?” & “business=” & PayPalEmailId & “&quantity=1&item_name=” & subscriptionText & “&userid=” & objUserData.UserID & “:” & Convert.ToString(promocodeExtraDays) & “:” & promoId & “&item_number=” & dtRuleDetail.Rows(0)(“SubscriptionRuleId”).ToString + “:” + combineString & “&amount=” & (Amount + totalAmount).ToString().Replace(“,”, “.”) & “&no_shipping=1&return=” & .SitePath & “PayPalReturnPage.aspx&cancel_return=” &  SitePath & “OrderMessageCancel.aspx&notify_url=” & SitePath & “ipnvalidatorsubscription.aspx&cn=How+did+you+hear+about+us%3F&currency_code=” & currency & “”, False)

business : abc@gmail.com (your paypal Id or email address associated with paypal account . Email addresses must be confirmed.)

Quantity : 1 (Number of items. If profile-based shipping rates are configured with a basis of quantity, the sum of quantity values is used to calculate the shipping charges for the transaction. PayPal appends a sequence number to uniquely identify the item in the PayPal Shopping Cart (e.g., quantity1, quantity2).

Note: The value for quantity must be a positive integer. Null, zero, or negative numbers are not allowed)

item_name : subscriptionText

( Describe the item being sold . If omitted, customer will see a field in which they have the option of entering an Item Name)

custom : userid = UserID & “:” & promocodeExtraDays & “:” & promoId

( User-defined field which will be passed through the system and returned in your merchant payment notification email. This field will not be shown to your subscribers)

item_number : dtRuleDetail.Rows(0)(“SubscriptionRuleId”).ToString + “:” + combineString

(Pass-through variable for you to track product or service purchased or the contribution made. The value you specify passed back to you upon payment completion)

amount: Amount + totalAmount

(The price or amount of the product, service, or contribution, not including shipping, handling, or tax. If omitted from Buy Now or Donate buttons, payers enter their own amount at the time of payment.)

Required for Add to Cart buttons

Optional for Buy Now and Donate buttons

Not used with Subscribe or Buy Gift Certificate buttons

no_shipping :

Do not prompt payers for shipping address. Allowable values:

0 – prompt for an address, but do not require one

1 – do not prompt for an address

2 – prompt for an address, and require one

The default is 0.

return : SitePath & “PayPalReturnPage.aspx

(The URL to which the payer’s browser is redirected after completing the payment; for example, a URL on your site that displays a “Thank you for your payment” page. )

Default – The browser is redirected to a PayPal web page.

cancel_return:  SitePath & “shop/OrderMessageCancel.aspx

(A URL to which the payer’s browser is redirected if payment is cancelled; for example, a URL on your website that displays a “Payment Canceled” page.

Default – The browser is redirected to a PayPal web page.

notify_url:  SitePath & “shop/ipnvalidatorsubscription.aspx

(The URL to which PayPal posts information about the transaction, in the form of Instant Payment Notification messages. )

Cn: How+did+you+hear+about+us%3F

(Label that appears above the note field. This value is not saved and will not appear in any of your notifications. If omitted, the default label above the note field is “Add special instructions to merchant.” The cn variable is not valid with Subscribe buttons or if you include no_note=”1″.)

currency_code:currency

The currency of prices for trial periods and the subscription. The default is USD

the PaypalSubmit page will be redirected to paypal site , and we get response from paypal on page we specify in notify_url .

After we get paypal response on url given in parameter notify_url , on that page following database transactions are done

database transactions : ipnvalidatorsubscription.aspx

from custom field get detail of user by whom subscription done

Hash table vs. Arraylist

According to http://msdn.microsoft.com/en-us/library/ms379571(v=vs.80).aspx, a Hash table is the fastest data structure in C# when you know the number of records you are going to store because of the collision resulution method.

 

1)In Array We Can Add any datatype value,Every item in arraylist is treated as object.
2)Hashtable is collection of key,value pairs

i)Key Can be any datatype
ii)Key Cannot be null refrrence
iii)but value can be null referrence

Retrieving by key in Hashtable is faster than retrieving in Arraylist,
Example of Arraylist:
Arraylist ar = new Arraylist();
ar.add(“X”);
ar.add(1);
ar.add(2);
Now if we Retrieve from Arraylist like
foreach(string str in ar)
{

}
It will Compile but it will give Runtime
Error,because ‘1’ and ‘2’ in Arraylist are integers.

Example Hashtable:
Hashtable ht = new Hashtable();
ht.add(1,”sample”);
ht.add(“2″,”Test”);
ht.add(3,4);
foreach(Dictionaryentry dr in ht.keys.values)
{
Console.Writeline(dr.keys + “=” + dr.values);
}

The Above Code wont give any runtime Error Because We are iterating through DictionaryEntries(Hashtable implements IDictionary) it is Collection of Key/Value Pairs