Inner Join with Update in Datatable using Linq in C#

dtDummTable.AsEnumerable().Join(NewDisUpdate.AsEnumerable(), dtMst => dtMst["stone_no"], dtChid => dtChid["StoneNo"], (dtMst, dtChid) => new { dtMst, dtChid }).ToList().ForEach(o => { o.dtMst.SetField("orderdiscount", o.dtChid["Discount"]); });

MST_XML_TO_TABLE

GO
/****** Object:  StoredProcedure [dbo].[MST_XML_TO_TABLE]    Script Date: 12/21/2015 13:01:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[MST_XML_TO_TABLE]
(
       @XML XML = ''
       ,@DataType varchar(100) = '' --Pass 'Columns' for columns ELSE DEFAULT TABLE WILL BE RETURN
       ,@ColumnString nvarchar(4000) = '' output
)
AS

BEGIN

 IF LOWER(@DataType) = 'columns' -- THIS WILL RETURN COLUMNS OF XML FILE
    BEGIN
        DECLARE @Columlist varchar(8000) = ''
       
        SELECT @Columlist = @Columlist + T.N.value('local-name(.)', 'sysname') + ','
        FROM @XML.nodes('/DocumentElement/BFormatFile[1]/*') as T(N);
       
        SELECT @ColumnString = SUBSTRING(@Columlist,0,LEN(@Columlist))
       
     END

 ELSE -- THIS WILL RETURN DATA OF XML IN TABULAR FORMAT
    BEGIN
            DECLARE @SQL NVARCHAR(MAX) = ''
            DECLARE @Col NVARCHAR(MAX) = ', T.N.value(''[COLNAME][1]'', ''varchar(100)'') as [COLNAME]'

            SELECT @SQL = @SQL + REPLACE(@Col, '[COLNAME]', T.N.value('local-name(.)', 'sysname'))
            FROM @XML.nodes('/DocumentElement/BFormatFile[1]/*') as T(N)
       
            SET @SQL = 'select '+ STUFF(@SQL, 1, 2, '')+' from @XML.nodes(''/DocumentElement/BFormatFile'') as T(N)'

            --select @SQL
            EXEC sp_executesql @SQL, N'@XML xml', @XML
    END
END

All About Web Service Information in .Net

What is Web Service?

• Web Service is an application that is designed to interact directly with other applications over the internet. In simple sense, Web Services are means for interacting with objects over the Internet.
  
• Web Service is
• Language Independent
• Protocol Independent
• Platform Independent
• It assumes a stateless service architecture.
• We will discuss more on web service as the article proceed. Before that lets understand bit on how web service comes into picture.

History of Web Service or How Web Service comes into existence?

• As i have mention before that Web Service is nothing but means for Interacting with objects over the Internet.
• 1. Initially Object - Oriented Language comes which allow us to interact with two object within same application.
• 2. Than comes Component Object Model (COM) which allows to interact two objects on the same computer, but in different applications.
• 3. Than comes Distributed Component Object Model (DCOM) which allows to interact two objects on different computers, but within same local network.
• 4. And finally the web services, which allows two object to interact internet. That is it allows to interact between two object on different computers and even not within same local network.

Example of Web Service

• Weather Reporting: You can use Weather Reporting web service to display weather information in your personal website.
• Stock Quote: You can display latest update of Share market with Stock Quote on your web site.
• News Headline: You can display latest news update by using News Headline Web Service in your website.
• In summary you can any use any web service which is available to use. You can make your own web service and let others use it. Example you can make Free SMS Sending Service with footer with your companies advertisement, so whosoever use this service indirectly advertise your company... You can apply your ideas in N no. of ways to take advantage of it.

Web Service Communication
Web Services communicate by using standard web protocols and data formats, such as

• HTTP
• XML
• SOAP

Advantages of Web Service Communication
Web Service messages are formatted as XML, a standard way for communication between two incompatible system. And this message is sent via HTTP, so that they can reach to any machine on the internet without being blocked by firewall.

Terms which are frequently used with web services

• What is SOAP?
• SOAP are remote function calls that invokes method and execute them on Remote machine and translate the object communication into XML format. In short, SOAP are way by which method calls are translate into XML format and sent via HTTP.

• What is WSDL?
• WSDL stands for Web Service Description Language, a standard by which a web service can tell clients what messages it accepts and which results it will return.
• WSDL contains every details regarding using web service
• Method and Properties provided by web service
• URLs from which those method can be accessed.
• Data Types used.
• Communication Protocol used.

• What is UDDI?
• UDDI allows you to find web services by connecting to a directory.

• What is Discovery or .Disco Files?
• Discovery files are used to group common services together on a web server.
• Discovery files .Disco and .VsDisco are XML based files that contains link in the form of URLs to resources that provides discovery information for a web service.
• .Disco File (static)
• .Disco File contains
• URL for the WSDL
• URL for the documentation
• URL to which SOAP messages should be sent.
• A static discovery file is an XML document that contains links to other resources that describe web services.
• .VsDisco File (dynamic)
• A dynamic discovery files are dynamic discovery document that are automatically generated by VS.Net during the development phase of a web service.

• What is difference between Disco and UDDI?
• Disco is Microsoft's Standard format for discovery documents which contains information about Web Services, while UDDI is a multi-vendor standard for discovery documents which contains information about Web Services.

• What is Web Service Discovery Tool (disco.exe) ?
• The Web Services Discovery Tool (disco.exe) can retrieve discovery information from a server that exposes a web service.

• What is Proxy Class?
• A proxy class is code that looks exactly like the class it meant to represent; however the proxy class doesn't contain any of the application logic. Instead, the proxy class contains marshalling and transport logic.
• A proxy class object allows a client to access a web service as if it were a local COM object.
• The Proxy must be on the computer that has the web application.

• What is Web Service Description Language Tool (wsdl.exe)?
• This tool can take a WSDL file and generate a corresponding proxy class that you can use to invoke the web service.
• Alternate of generating Proxy class through WSDL.exe is you can use web reference. Web Reference automatically generate a proxy classes for a web service by setting a web reference to point to the web service.
• Advantage of using Web Reference as compare to using WSDL.exe Tool is you can update changes done in web service class easily by updating web reference, which is more tedious task with WSDL.exe tool.

• Testing a Web Service?
• You can test web service without building an entire client application.
• With Asp.net you can simply run the application and test the method by entering valid input paramters.
• You can also use .Net Web Service Studio Tool comes from Microsoft.

Example of Creating Web Service in .Net
This Web Service will retrieve CustomerList Country Wise and return as dataset to client application for display.
Step1: Create a Web Service Application by File > New > Web Site > Asp.net Web Services
Named the web service, for example here i have choosen name "WSGetCustomerCountryWise"

Step2: Rename the default Service.asmx file to proper name, you also need to switch design view and change the class name with the same name you use to rename the service.asmx.
For example, "WSGetCustomerCountryWise.asmx" and switch to design view and change the class="Service" to class="WSGetCustomerCountryWise"

Step3: Rename the Service.CS File to proper name, you need to change the class name and constructor name too.
For example, "WSGetCustomerCountryWise.CS" and switch to code view and change the class and constructor name to "WSGetCustomerCountryWise"

After three steps your solution explorer looks as shown in figure




Step4: Create a Logic for Web Service

• Create a Method "GetCustomerCountryWise"
• Note: You need to specify [WebMethod] before method definition, if you want it to be accessible public, otherwise the method would not be accessible remotely.
• Specify proper argument and return type for method in web service.
• It is also good practise to specify the use "Description" attribute to tell what method is meant for.

For example, here i need to access data of northwind customers and want to return customer list country wise, so add namespace for

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

[WebMethod(Description="It will generate Customer List, Country Wise")] public System.Xml.XmlElement GetCustomerCountryWise(string sCountry) 
{
string sConn = ConfigurationManager.ConnectionStrings["connStr"].ToString();

string sSQL = "select CustomerId, CompanyName, ContactTitle, City " +
" from Customers where country = '" + sCountry + "'";

SqlConnection connCustomer = new SqlConnection(sConn);

DataSet dsCustomer = new DataSet();

SqlDataAdapter daCustomer = new SqlDataAdapter(sSQL, sConn);

daCustomer.Fill(dsCustomer,"Customers");

//Known bug while return "DataSet" is "Data source is an invalid type.
It must be either an IListSource, IEnumerable, or IDataSource."
//For more details on Error: http://support.microsoft.com/kb/317340

//So to access data we need to make use of XmlElement.

// Return the DataSet as an XmlElement.
System.Xml.XmlDataDocument xdd = new System.Xml.XmlDataDocument(dsCustomer);
System.Xml.XmlElement docElem = xdd.DocumentElement;
return docElem;
}
Step5: Build Web Service and Run the Web Service for testing by pressing F5 function key.






By pressing "Invoke" button will generate XML File.

So you are done creating web service application.

Example of Testing Web Service in .Net
This Web Service will display the information which had been retrieved from Remote computer by accessing public method "GetCustomerCountryWise".
Step1: Create a Test Web Site by File > New > Web Site > Asp.net Web Site
Named the web site, for example here i have choosen name "TestGetCustomerCountryWise"

Step2: Displaying data in gridview, so drag the gridview on to the form.

Step3: Right Click Solution Explorer and Choose "Add Web Reference"



Step4: Choose the option Web Service on the local machine or you can enter the .WSDL File address directly in URL space and press Go button.




Step5: Press "Add Reference button"


Step6: Writing Code for Displaying data in GridView
Here note: I have Pass "USA" as parameter in Country Field.

protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
//Create object of WSGetCustomerCountryWise Object
localhost.WSGetCustomerCountryWise objGetCustomerCountryWise
= new localhost.WSGetCustomerCountryWise();

DataSet dsCustomer = new DataSet();

// Get the data from Webservice.
XmlElement elem = objGetCustomerCountryWise.GetCustomerCountryWise("USA");

// Load the XML to the Typed DataSet that you want.
XmlNodeReader nodeReader = new XmlNodeReader(elem);
dsCustomer.ReadXml(nodeReader, XmlReadMode.Auto);

GridView1.DataSource = dsCustomer;

GridView1.DataBind();
}
}


Step7: Output as data displayed in GridView.




Few Facts about Web Service in .Net

• Each Response from web service is a new object, with a new state.
• Web Service are asynchronous because the request object from the client application and the response object from the web service are unique SOAP envelopes that do not require shared connection.
• This allow client application and the web service to continue processing while the interaction is ongoing.
• Instead of a user interface, it provides a standard defined interface called a contract.

Comma separated values using linq from dataset in C#

 DataSet ds = new DataSet();
            System.Data.DataTable dtResults = new System.Data.DataTable();
            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection("server=testserver;uid=testuser;pwd=pwd;database=testDB");
            con.Open();
            System.Data.SqlClient.SqlCommand cmd = new SqlCommand("SPName", con);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter DA = new SqlDataAdapter(cmd);
            DA.Fill(ds);

            if (ds.Tables != null && ds.Tables[0].Rows.Count > 0)

            {
                string ourstring = String.Join(",", ds.Tables[0].AsEnumerable().Select(x => x.Field<int>      ("ColumnName").ToString()).ToArray());

            }

Introduction to OOPS Concepts

Introduction to OOPS Concepts

OOP stands for Object Oriented Programming. It is a programming methodology that uses Objects to build a system or web applications using programming languages like C#, Vb.net etc.

Here, Objects plays a very important role because it hides the implementation details and exposed only the needed functionalities and related stuff that is required to adopt it. We can access class properties and methods by creating class object that I’ll explain below in this tutorial.

OOPS Concepts, Features & Fundamentals

OOPS contains list of elements that are very helpful to make object oriented programming stronger. Here is the list of top 10 OOPS concepts that we can implement in all major programming languages like c#, vb.net etc.

1] Class:

A class is a collection of objects and represents description of objects that share same attributes and actions. It contains characteristics of the objects such as objects attributes, actions or behaviors.

Here is the syntax and declaration example of Class:

public class Bike
{
    //your code goes here..
}

2] Method:

Method is an object’s behavior.

For example, if you consider “Bike” as a class then its behavior is to get bike color, engine, mileage etc. Here is the example of Method:

public class Bike
{
    //here is some properties of class Bike
    public string color;
    public string engine;
    public int mileage;

    //here is some behavior/methods of class Bike
    public string GetColor()
    {
        return "red";
    }
    public int GetMileage()
    {
        return 65;
    }
}

In above example GetColor() and GetMileage() are the methods considered as a object’s behavior.

3] Object:

An object is a real-world entity that keeps together property states and behaviors.

For example, A “Bike” usually has common elements such as bike color, engine, mileage etc. In OOP terminology these would be called as a Class Properties or Attributes of a Bike object. Here is the example of Object:

public class Bike
{
    //This is the class that contains all properties and behavior of an object

    //here is some properties of class Bike
    public string color;
    public string engine;
    public int mileage;

    //here is some behavior of class Bike
    public string GetColor()
    {
        return "red";
    }
    public int GetMileage()
    {
        return 65;
    }
}

Now we have a complete set of class with its properties and methods. So the question raise in mind is how we can access the class with its object? Right?

It is simple to create its object and access Bike class. You can create object of class via single line of code as shown below.

//It also considered as an "Instance of a Bike Class"
Bike objBike = new Bike();

//Accessing Bike class methods
objBike.GetColor();
objBike.GetMileage();

4] Encapsulation:

Encapsulation is the process of keeping or enclosing one or more items within a single physical or logical package. In object oriented programming methodology it prevents access to implementation details.

Encapsulation is implemented by using access specifiers. An access specifier defines the scope and visibility of a class member. Available access specifiers are public, private, protected, internal etc.

How we can achieve Encapsulation?
We can achieve Encapsulation by using private access modifier as shown in below example method.

private string GetEngineMakeFormula()
{
    private string formula = "a*b";
    return formula;
}

Example – [Encapsulation]

public class Bike
{
    public int mileage = 65;
    public string color = "Black";
    private string formula = "a*b";

    //Its public – so accessible outside class
    public int GetMileage()
    {
        return mileage;
    }

    //Its public – so accessible outside class
    public string GetColor()
    {
        return color;
    }

    //Its private – so not accessible outside class
    private string GetEngineMakeFormula()
    {
        return formula;
    }
}

public class Program
{
    public static void Main(string[] args)
    {
        Bike objBike = new Bike();
        Console.WriteLine("Bike mileage is : " + objBike.GetMileage()); //accessible outside "Bike"
        Console.WriteLine("Bike color is : " + objBike.GetColor()); //accessible outside "Bike"
        //we can't call this method as it is inaccessible outside "Bike"
        //objBike.GetEngineMakeFormula(); //commented because we can't access it
        Console.Read();
    }
}

So as you can see from above code that we hide GetEngineMakeFormula() method by using private access modifier because there is no need to give the make formula to users. So exposed only necessary methods for users to use it using public access modifier.

5] Abstraction:

Abstraction is the process of providing only essential information to the outside real world and hiding overall background details to present an object. It relies on the separation of interface and implementation.

For example, we continue with “Bike” as an example, we have no access to the piston directly, we can use start button to run the piston. Just imagine if a bike manufacturer allows direct access to piston, it would be very difficult to control actions on the piston. That’s the reason why a bike provider separates its internal implementation from its external interface.

Example – [Abstraction]

public class Bike
{
    public int mileage = 65;
    public string color = "Black";
    private string formula = "a*b";

    //Its public – so accessible outside class
    public int GetMileage()
    {
        return mileage;
    }

    //Its public – so accessible outside class
    public string GetColor()
    {
        return color;
    }

    //Its private – so not accessible outside class
    private string GetEngineMakeFormula()
    {
        return formula;
    }

    //Its public – so accessible outside class
    public string DisplayMakeFormula()
    {
        //"GetEngineMakeFormula()" is private but accessible and limited to this class only
        return GetEngineMakeFormula();
    }
}

public class Program
{
    public static void Main(string[] args)
    {
        Bike objBike = new Bike();
        Console.WriteLine("Bike mileage is : " + objBike.GetMileage()); //accessible outside "Bike"
        Console.WriteLine("Bike color is : " + objBike.GetColor()); //accessible outside "Bike"
        //we can't call this method as it is inaccessible outside "Bike"
        //objBike.GetEngineMakeFormula(); //commented because we can't access it
        Console.WriteLine("Bike color is : " + objBike.DisplayMakeFormula()); //accessible outside
        Console.Read();
    }
}

As you can see from the above code that necessary methods and properties exposed using public access modifier and unnecessary methods and properties are hidden using private access modifier. This way we can implement abstraction or we can achieve abstraction in our code or web application.

6] Information Hiding:

Information Hiding concept restricts direct exposure of the data. Data is accessed indirectly using safe mechanism, methods in case of programming object. Follow the example given in Abstraction.

7] Inheritance:

Inheritance in OOP allows us to create a new class using an existing one meaning extending one class to another.

This concept can also be related with the real world example. Let’s take a Bike example again. A Bike manufacturer uses same mechanism of existing version of the bike while launching a new version with some additional added functionality. This allows manufacturer to save their time and efforts both.

The main advantage of extending classes is that it provides a convenient way to reuse existing fully tested code in different context thereby saving lots of time with existing coding and its model style.

Example – [Inheritance]

public class Base
{
    public Base()
    {
        Console.WriteLine("Constructor of Base Class");
    }

    public void DisplayMessage()
    {
        Console.WriteLine("Hello, how are you?");
    }
}

public class Child : Base
{
    public Child()
    {
        Console.WriteLine("Constructor of Child class");
    }
}

public class Program
{
    public static void Main(string[] args)
    {
        Child objChild = new Child();
        //Child class don't have DisplayMessage() method but we inherited from "Base" class
        objChild.DisplayMessage();
        Console.Read();
    }
}

As you can see in the previous example code, We created an object of a Child class in Main() method and then called DisplayMessage() method of Base class. If you notice that the Child class doesn’t have DisplayMessage()method in it. So obviously it is inherited from the Base class. When you execute following code, result would be as show below:
Example Result

Constructor of Base Class
Constructor of Child class
Hello, how are you?

As per sample result, we can say that the “Base” class constructor will automatically be called before the “Child” class constructor.

Thus, here conclusion is that “Base/Parent” classes are automatically instantiated before “Child/Derived” classes.

8] Polymorphism:

The word Polymorphism means having many forms. Generally, polymorphism occurs when there is a hierarchy of classes and they are related by inheritance.

Let’s take Bike example, A Bike can be into two forms like cell start or kick start. We can later on decide which form or method we will use to start bike to go for drive (meaning at runtime).

There are two types of Polymorphism:

• Compile time polymorphism: In this type of polymorphism, compiler identifies which polymorphism form it has to take and execute at compile time is called as compile time polymorphism or early binding. Examples of early binding are Method Overloading and Operator Overloading. The Method Overloading means more than one method having same name but different signatures (or parameters) in the same or different class.

  – Advantage: Execution will be fast because everything about the method is known to compiler during compilation.
  – Disadvantage: It has lack of flexibility.
Example – [Method Overloading]
public class Base
{
    //1st: same method name, return type (object) but different parameter type (object)
    public object Display(object a)
    {
        return (a);
    }
       
    //2nd: same method name, return type (int) but different parameter type (int)
    public int Display(int a)
    {
        return (a);
    }
}
public class Program
{
    public static void Main(string[] args)
    {
        Base objBase = new Base();
        int val = 7;
        //here 2nd method will be called with type "int"
        Console.WriteLine(objBase.Display(val));
        Console.Read();
    }
}
In above example, when you run the program, Display(int a) method will be called first because val is of typeint at compile time. The assigned val is only refer to as a int at execution time.
Example Result
Note: While overloading methods, a rule to follow is the overloaded methods must differ either in number of arguments they take or the data type of at least one argument. We can also consider Method Overriding as a compile time polymorphism that is called directly by using derived objects.
• Runtime polymorphism: In this type of polymorphism, compiler identifies which polymorphism form it has to take and execute at runtime but not at compile time is called as runtime polymorphism or late binding. Example of early binding is Method Overriding. The Method Overriding means having two methods with same name and same signature, one method in base class and other method in derived class. It must require changing the behavior of the base class methods in derived class to use its functionality differently.

  – Advantage: It has flexibility to adjust object types at runtime.
  – Disadvantage: Execution will be slow as compiler has to get the information about the method to execute at runtime.
We need to use either virtual methods or abstract method to allow the derived class to override a method of the base class.
Example – [Method Overriding by using virtual method]
public class Base
{
    public virtual string BlogName()
    {
        return "AspnetO";
    }
}
public class Child : Base
{
    //same method name with same signature/parameters
    public override string BlogName()
    {
        return "AspnetO – Quick Way To Learn Asp.net";
    }
}
public class Program
{
    public static void Main(string[] args)
    {
        Base objBase = new Child();
        Console.WriteLine(objBase.BlogName());
        Console.Read();
    }
}
In above example, when you run the program, at compile time the type of objBase is Base but it will still call the child class’s override method because at the runtime, the type of the objBase object refers to is Child.
Example – [Method Overriding by using abstract method]
public abstract class Base
{
    public abstract string BlogName();
}
public class Child : Base
{
    //same method name with same signature/parameters
    public override string BlogName()
    {
        //It's mandatory to implement abstract method in derived/child class
        return "AspnetO – Quick Way To Learn Asp.net";
    }
}
public class Program
{
    public static void Main(string[] args)
    {
        Base objBase = new Child();
        Console.WriteLine(objBase.BlogName());
        Console.Read();
    }
}
In above example, when you run the program, at compile time the type of objBase is Base but it will still call the child class’s override method because at the runtime, the type of the objBase object refers to is Child.

Note: Method Overloading and Method Overriding both are different OOP concepts and important also. Don’t be panic with their names it looks similar.

9] Constructors:

Constructors are special methods, used when instantiating a class. A constructor can never return anything, which is why you don’t have to define a return type for it.

A normal method is defined like this:

public string bike()
{
}

A simple constructor(without parameters) can be defined like this:

public bike()
{
}

And here is the example of parameterized constructor:

public class bike
{
    private int mileage;
    private string color;

    public bike()
    {
        //constructor without parameter
    }

    public bike(int mil, string col)
    {
        //constructor with two parameters "mil" and "col"
        mil = mileage;
        col = color;
    }

    public void DisplayBikeData()
    {
        Console.WriteLine("Bike's Mileage is " + mileage + " and color is " + color);
    }
}

Key points to note about constructor are:

• If no constructor defined then the CLR(Common Language Runtime) will provide an implicit constructor which is known as a Default Constructor.
• Constructor doesn’t return a value.
• Constructors can be overloaded.
• A class can have any number of constructors and they vary with the number of arguments that are passed, which is they should have different parameters or signatures.
• We don’t use references or pointers on constructors because their addresses cannot be taken.
• Constructor doesn’t be declared with the virtual keyword.

10] Destructors:

Since garbage cleanup is automatic system, framework will free the objects that are no longer in use BUT there may be times where we need to do some manual cleanup. In this case we can use Destructor, which is used to destroy the objects that we no longer want to use.

A destructor method called once an object is disposed, can be used to cleanup resources used by the object. Destructors don’t look very much like other methods.

Here is an example of a destructor for our Bike class:

public class Bike
{
    public Bike()
    {
        //Constructor
    }
    ~Bike()
    {
        //Destructor
    }
}

Once the class object is instantiated, Constructor will be called and when object is collected by the garbage collector, Destructor method will be called.