Update alternate rows in sqlserver

update s set recstatus=’I’ from
(select row_number() over (order by id) sno,* from mastertbl)s
where sno%2=0

or

with c as (
select row_number() over (order by id) sno,* from mastertbl
) update c set recstatus=’I’ where sno%2=0

Int range in .net

Int16 : The value of this constant is 32767 : hexadecimal 0x7FFF

Int16 (or short) uses 16 bits to store a value, giving it a range from -32,768 to 32,767

Int32 uses 32 bits to store a value, giving it a range from -2,147,483,648 to 2,147,483,647

Int 64 -- (-9223372036854775808 to +9223372036854775807)
SByte Min: -128 Max: 127
Byte Min: 0 Max: 255
Int16 Min: -32768 Max: 32767
Int32 Min: -2147483648 Max: 2147483647
Int64 Min: -9223372036854775808 Max: 9223372036854775807
Single Min: -3.402823E+38 Max: 3.402823E+38
Double Min: -1.79769313486232E+308 Max: 1.79769313486232E+308
Decimal Min: -79228162514264337593543950335 Max: 79228162514264337593543950335

9 February

i). Machine. Config

ii). Web.Config (For desktops app.Config)

Common paints for confn files:

• These are simple text files & can be edited using any simple text editor.


• These are xml formatted (case sensitive) & any xml programs can use these files or they can be directly edited because xml is human readable.


• * They are automatically cached on memory for faster retrieval of settings (Cache memory àprogrammed memory area).


• * Processed in hierarchical manner which means every configuration file inherits its parent configuration file settings. The root for all these files is Machine.Config.


• Also called as configuration inheritance apart from this configuration is also hierarchical as xml is hierarchical.

Machine.Config points:

• It is called per-server basis file. Only one machine.config is allowed for one runtime/server.


• Located .NET framework folder not on our project & all settings are common to all websites running in that system. * 4 MB (default)(more than 4.0 go to machine.config – one file- change)


• Most of the settings are maintained by administrator but not by application developers.


• here we find all website settings(default) i.e., namespaces, services of support, net support, compileation settings, deploy settings, db settings ......

Web.Config points:

• It is called as application Configuration file.


• **** It is optional today also to have web.config in our application located in root & can be created sub folders also. We can create multiple web.config’s on our site maximum is how many folders we have in our project.


• we can perform the following functionalities in web.config file:

1.  Maintain connection strings


2. Custom errors


3. Security


4. Debugging


5. Tracing

----------------------------

The Cursor Process has the following steps involved in it: (exception handling in sp)

• Declare a Cursor


• Open a Cursor


• Fetch data from the Cursor


• Close the Cursor


• De-allocate the Cursor

----------------------------

“When a client, makes a request for an aspx page, it will be processed at server & all the result produced is concatenated as a single string along with some hash values in it.

These concatenated values are converted into base 64 format***. These base 64 values are stored in one or more hidden fields*** by server sent to client along with other from contents.

User adds/modifies the content & resubmits the form to server. Server first reads the hidden field values so that it retains the last given values (act as stateful) &responds the same process with new repeated as long as user is working with current page....”

---------------------------

What is the scope of application variable?

1. Accessible to all users of application, irrespective of the user who created it.


2. The common memory area which is created in the webserver, which is common for all the users is k.led as App. Memory.


3. Storing the data in this app.memory is k.ed App. state in State mngt. sys      


4. .: The values stord by the webuser in the app memory, can be accessed by the other users also.                  


5. The App State can store obj data types.


6. App.State have to be used to store the Data which will be common for all the users of website.

---------------------------

All session variables are accessible to current user only.

Session variable are also of type object which means they can store any type of data (Load on server should be considered).

Session data will be stored @ server only & only session id travels. This data will be available as long as session is preserved by server. Sessions are normally ended based on timeout***.

In ASP.NET by default 20 min’s it the timeout (idle timeout) using configuration settings as well as programmatically with session object we can change this default time.

A session can be ended programmatically also. Session object has a method called Abandon () which kills the current session.

<system.web>

<SessionState timeout=”10”></SessionState>

-------------------------------







Non.Persistent Cookies: These Cookies will be created in the browser's process area. They r not mentioned with Time duration. These Cookies will be destroyed when the app is closed

Persistent Cookies: The Cookies which are created & exists for a given time duration., is k.ld as the Persistent Cookie. They are created in the Client machine

Query string:                                                                                                                                                               Query string is Client side st.mngt sys. It can hold only strings. The Query string Memory will destroyed, when the app is closed.                                                                                                                                                The values Stored in Query string can be accessed from any page through out the app. In general Query string is used 2 carry small values from one to another page.

----------------------------

1. Caching is the process of storing the page or objects in the cache memory of browse, proxy servers or webserver.      


2. in general, caching is used to improve the performance i.e., the page or object that is cached will be accessed from the cache memory tor the next request with out fetching it from the db server.


3. caching can be done at client side as well as server side.

These are 3 types of caching:

client side caching:

• output page caching (or) entire page caching


• fragment caching (or) partial page caching

server side caching:

• data caching (or) object caching.

using system.web.caching;

//create{                                                                                                                                                                                                            CacheDependency cd = new Cache Dependency(Server.MapPath("XMLfile.xml"), Cache.Insert("key1",ds.Tables[0]),cd,DateTime.Now.AddMinutes(10),Cache.NoSlidingExpression);              }

//get{                                                                                                                                                                                         gv1.DataSource = (DataTable)Cache.Get("key1");                                                                                                                      gv1.DataBind();                                                                                                                                                                                             }

1. Obj caching or data caching is server side State Management Sys.


2. we can create this kind of caching in 2 cryterious

• cache dependency: By using this, cache memory will be destroyed if the dependent file is modified.


• cache expiration: By using this parameter, cache mem. will be destroyed with in a given time after the creation.

-----------------------------

Primary Key:
Primary Key enforces uniqueness of the column on which they are defined.
Primary Key creates a clustered index on the column.
Primary Key does not allow Nulls.

CREATE TABLE EMP (eID INT NOT NULL PRIMARY KEY,
eName VARCHAR(100) NOT NULL)

Alter table with Primary Key:
ALTER TABLE EMP
ADD CONSTRAINT pk_eID PRIMARY KEY (eID)

Unique Key:
Unique Key enforces uniqueness of the column on which they are defined.
Unique Key creates a non-clustered index on the column.
Unique Key allows only one NULL Value.

Alter table to add unique constraint to column:
ALTER TABLE EMP ADD CONSTRAINT UK_empName UNIQUE(empName)

SQL SERVER – Introduction to JOINs – Basic of JOINs

INNER JOIN

This join returns rows when there is at least one match in both the tables.

OUTER JOIN

There are three different Outer Join methods.

LEFT OUTER JOIN
This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.


RIGHT OUTER JOIN
This join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.


FULL OUTER JOIN
This join combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match.

CROSS JOIN

This join is a Cartesian join that does not necessitate any condition to join. The resultset contains records that are multiplication of record number from both the tables.

Additional Notes related to JOIN:

The following are three classic examples to display where Outer Join is useful. You will notice several instances where developers write query as given below.

SELECT t1.*
FROM Table1 t1
WHERE t1.ID NOT IN (SELECT t2.ID FROM Table2 t2)
GO

The query demonstrated above can be easily replaced by Outer Join. Indeed, replacing it by Outer Join is the best practice. The query that gives same result as above is displayed here using Outer Join and WHERE clause in join.

/* LEFT JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL



The above example can also be created using Right Outer Join.



NOT INNER JOIN
Remember, the term Not Inner Join does not exist in database terminology. However, when full Outer Join is used along with WHERE condition, as explained in the above two examples, it will give you exclusive result to Inner Join. This join will give all the results that were not present in Inner Join.



You can download the complete SQL Script here, but for the sake of complicity I am including the same script here.

USE AdventureWorks
GO
CREATE TABLE table1
(ID INT, Value VARCHAR(10))
INSERT INTO Table1 (ID, Value)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 4,'Fourth'
UNION ALL
SELECT 5,'Fifth'
GO
CREATE TABLE table2
(ID INT, Value VARCHAR(10))
INSERT INTO Table2 (ID, Value)
SELECT 1,'First'
UNION ALL
SELECT 2,'Second'
UNION ALL
SELECT 3,'Third'
UNION ALL
SELECT 6,'Sixth'
UNION ALL
SELECT 7,'Seventh'
UNION ALL
SELECT 8,'Eighth'
GO
SELECT *
FROM Table1
SELECT *
FROM Table2
GO
USE AdventureWorks
GO
/* INNER JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
INNER JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* LEFT JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* RIGHT JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
RIGHT JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* OUTER JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID
GO
/* LEFT JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL
GO
/* RIGHT JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
RIGHT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t1.ID IS NULL
GO
/* OUTER JOIN - WHERE NULL */
SELECT t1.*,t2.*
FROM Table1 t1
FULL OUTER JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t1.ID IS NULL OR t2.ID IS NULL
GO
/* CROSS JOIN */
SELECT t1.*,t2.*
FROM Table1 t1
CROSS JOIN Table2 t2
GO
DROP TABLE table1
DROP TABLE table2
GO

Create virtual files in asp

    private void virfile()
    {
        if (!Directory.Exists(Server.MapPath("download/")))
            Directory.CreateDirectory(Server.MapPath("download/"));
        string filpath = Server.MapPath("download/" + "data.txt");
        string filedir = filpath.Substring(0, filpath.LastIndexOf('.'));
        string fileext = System.IO.Path.GetExtension(filpath);
        if (!File.Exists(filpath))
        {
            File.AppendAllText(filpath, "file downloaded on : " + DateTime.Now.ToString() + "\r\n");
        }
        else
        {
            int i = 1;
            while (true)
            {
                filpath = filedir + "(" + i + ")" + fileext;
                if (!File.Exists(filpath))
                {
                    File.AppendAllText(filpath, "file downloaded on : " + DateTime.Now.ToString() + "\r\n");
                    break;
                }
                i++;
            }
        }
    }

WCF Tutorial

                             WCF(WINDOWS COMMUNICATION FOUNDATION)

Introduction to WCF:

Windows Communication Foundation (Code named Indigo) is a programming platform and runtime system for building, configuring and deploying network-distributed services. It is the latest service oriented technology; Interoperability is the fundamental characteristics of WCF. It is unified programming model provided in .Net Framework 3.0. WCF is a combined features of Web Service, Remoting, MSMQ and COM+. WCF provides a common platform for all .NET communication.

Below figures shows the different technology combined to form WCF.

Advantage:

   1. WCF is interoperable with other services when compared to .Net Remoting,where the client and service have to be .Net.

   2. WCF services provide better reliability and security in compared to ASMX web services.

   3. In WCF, there is no need to make much change in code for implementing the security model and changing the binding. Small changes in the configuration will make your requirements.

   4. WCF has integrated logging mechanism, changing the configuration file settings will provide this functionality. In other technology developer has to write the code.

WCF Architecture

The following figure illustrates the major components of WCF.

[Wcf Architechture]

Figure 1: WCF Architecture

Contracts

Contracts layer are next to that of Application layer. Developer will directly use this contract to develop the service. We are also going to do the same now. Let us see briefly what these contracts will do for us and we will also know that WCF is working on message system.

Service contracts

- Describe about the operation that service can provide. Example, Service provided to know the temperature of the city based on the zip code, this service we call as Service contract. It will be created using Service and Operational Contract attribute.

Data contract

- It describes the custom data type which is exposed to the client. This defines the data types, are passed to and from service. Data types like int, string are identified by the client because it is already mention in XML schema definition language document, but custom created class or datatype cannot be identified by the client e.g. Employee data type. By using DataContract we can make client aware that we are using Employee data type for returning or passing parameter to the method.

Message Contract

- Default SOAP message format is provided by the WCF runtime for communication between Client and service. If it is not meeting your requirements then we can create our own message format. This can be achieved by using Message Contract attribute.

Policies and Binding

- Specify conditions required to communicate with a service e.g security requirement to communicate with service, protocol and encoding used for binding.

Service Runtime

- It contains the behaviors that occur during runtime of service.

    * Throttling Behavior- Controls how many messages are processed.

    * Error Behavior - Specifies what occurs, when internal error occurs on the service.

    * Metadata Behavior - Tells how and whether metadata is available to outside world.

    * Instance Behavior - Specifies how many instance of the service has to be created while running.

    * Transaction Behavior - Enables the rollback of transacted operations if a failure occurs.

    * Dispatch Behavior - Controls how a message is processed by the WCF Infrastructure.

Messaging

- Messaging layer is composed of channels. A channel is a component that processes a message in some way, for example, by authenticating a message. A set of channels is also known as a channel stack. Channels are the core abstraction for sending message to and receiving message from an Endpoint. Broadly we can categories channels as

    * Transport Channels

      Handles sending and receiving message from network. Protocols like HTTP, TCP, name pipes and MSMQ.

    * Protocol Channels

      Implements SOAP based protocol by processing and possibly modifying message. E.g. WS-Security and WS-Reliability.

Activation and Hosting

- Services can be hosted or executed, so that it will be available to everyone accessing from the client. WCF service can be hosted by following mechanism

    * IIS

      Internet information Service provides number of advantages if a Service uses Http as protocol. It does not require Host code to activate the service, it automatically activates service code.

    * Windows Activation Service

      (WAS) is the new process activation mechanism that ships with IIS 7.0. In addition to HTTP based communication, WCF can also use WAS to provide message-based activation over other protocols, such as TCP and named pipes.

    * Self-Hosting

      WCF service can be self hosted as console application, Win Forms or WPF application with graphical UI.

    * Windows Service

      WCF can also be hosted as a Windows Service, so that it is under control of the Service Control Manager (SCM).

IIS 5/6 Hosting

The main advantage of hosting service in IIS is that, it will automatically launch the host process when it gets the first client request. It uses the features of IIS such as process recycling, idle shutdown, process health monitoring and message based activation. The main disadvantage of using IIS is that, it will support only HTTP protocol.

Let as do some hands on, to create service and host in IIS

Step 1:Start the Visual Studio 2008 and click File->New->Web Site. Select the 'WCF Service' and Location as http. This will directly host the service in IIS and click OK.

Step 2: I have created sample HelloWorld service, which will accept name as input and return with 'Hello' and name. Interface and implementation of the Service is shown below.

IMyService.cs

[ServiceContract]

public interface IMyService

{

    [OperationContract]

    string HelloWorld(string name);   

}

MyService.cs

public class MyService : IMyService

{

    //#region IMyService Members

    public string HelloWorld(string name)

    {

        return "Hello " + name;

    }

   // #endregion

}

Step 3: Service file (.svc) contains name of the service and code behind file name. This file is used to know about the service.

MyService.svc

<%@ ServiceHost Language="C#" Debug="true"

Service="MyService" CodeBehind="~/App_Code/MyService.cs" %>

Step 4: Server side configurations are mentioned in the config file. Here I have mention only one end point which is configured to 'wsHttpBinding', we can also have multiple end point with differnet binding. Since we are going to hosted in IIS. We have to use only http binding. We will come to know more on endpoints and its configuration in later tutorial. Web.Config

 

  

        <endpoint address="http://localhost/IISHostedService/MyService.svc"

        binding="wsHttpBinding" contract="IMyService">

       

       

       

       

       

  

 

 

 

   

        <!-- To avoid disclosing metadata information,

        set the value below to false and remove the

        metadata endpoint above before deployment -->

      

        <!-- To receive exception details in faults for

        debugging purposes, set the value below to true. 

        Set to false before deployment to avoid disclosing exception information -->

       

       

  

 

Note:

You need to mention the service file name, along with the Address mention in the config file. IIS Screen shot

This screen will appear when we run the application.

Step 5: Now we successfully hosted the service in IIS. Next we have to consume this service in client application. Before creating the client application, we need to create the proxy for the service. This proxy is used by the client application, to interact with service. To create the proxy, run the Visual Studio 2008 command prompt. Using service utility we can create the proxy class and its configuration information.

svcutil  http://localhost/IISHostedService/MyService.svc

After executing this command we will find two file generated in the default location.

• MyService.cs - Proxy class for the WCF service
• output.config - Configuration information about the service.

Step 6: Now we will start creating the Console application using Visual Studio 2008(Client application).

Step 7: Add the reference 'System.ServiceModel'; this is the core dll for WCF.

Step 8: Create the object for the proxy class and call the HelloWorld method.

static void Main(string[] args)

        {

            //Creating Proxy for the MyService

             MyServiceClient client = new MyServiceClient();

             Console.WriteLine("Client calling the service...");

             Console.WriteLine(client.HelloWorld("Ram"));

             Console.Read();

        }

Step 9: If we run the application we will find the output as shown below.

Self Hosting

In web service, we can host the service only in IIS, but WCF provides the user to host the service in any application (e.g. console application, Windows form etc.). Very interestingly developer is responsible for providing and managing the life cycle of the host process. Service can also be in-pro i.e. client and service in the same process. Now let's us create the WCF service which is hosted in Console application. We will also look in to creating proxy using 'ClientBase'abstract class.

Note: Host process must be running before the client calls the service, which typically means you have to prelaunch it.

Step 1: First let's start create the Service contract and it implementation. Create a console application and name it as MyCalculatorService. This is simple service which return addition of two numbers.

Exercise:3

Step 2: Add the System.ServiceModel reference to the project.

Step 3: Create an ISimpleCalculator interface, Add ServiceContract and OperationContract attribute to the class and function as shown below. You will know more information about these contracts in later session. These contracts will expose method to outside world for using this service.

IMyCalculatorService.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.ServiceModel;

namespace MyCalculatorService

{

    [ServiceContract()]

    public interface ISimpleCalculator

    {

        [OperationContract()]

        int Add(int num1, int num2);

    }

}

Step 4: MyCalculatorService is the implementation class for IMyCalculatorService interface as shown below.

MyCalculatorService.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

namespace MyCalculatorService

{

    class SimpleCalculator : ISimpleCalculator

    {

        public int Add(int num1, int num2)

        {

            return num1 + num2;

        }

    }

}

Step 5: Now we are ready with service. Let's go for implementing the hosting process. Create a new console application and name it as 'MyCalculatorServiceHost'

Step 6: ServiceHost is the core class use to host the WCF service. It will accept implemented contract class and base address as contractor parameter. You can register multiple base addresses separated by commas, but address should not use same transport schema.

Uri httpUrl

= new Uri("http://localhost:8090/MyService/SimpleCalculator");

Uri tcpUrl

= new Uri("net.tcp://localhost:8090/MyService/SimpleCalculator");

ServiceHost host

= new ServiceHost(typeof(MyCalculatorService.SimpleCalculator), httpUrl, tcpUrl);

Multiple end points can be added to the Service using AddServiceEndpoint() method. Host.Open() will run the service, so that it can be used by any client.

Step 7: Below code show the implementation of the host process.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.ServiceModel;

using System.ServiceModel.Description;

namespace MyCalculatorServiceHost

{

    class Program

    {

        static void Main(string[] args)

        {

            //Create a URI to serve as the base address

            Uri httpUrl = new Uri("http://localhost:8090/MyService/SimpleCalculator");

            //Create ServiceHost

            ServiceHost host

            = new ServiceHost(typeof(MyCalculatorService.SimpleCalculator), httpUrl);

            //Add a service endpoint

            host.AddServiceEndpoint(typeof(MyCalculatorService.ISimpleCalculator)

            , new WSHttpBinding(), "");

            //Enable metadata exchange

            ServiceMetadataBehavior smb = new ServiceMetadataBehavior();

            smb.HttpGetEnabled = true;

            host.Description.Behaviors.Add(smb);

            //Start the Service

            host.Open();

            Console.WriteLine("Service is host at " + DateTime.Now.ToString());

            Console.WriteLine("Host is running... Press key to stop");

            Console.ReadLine();

        }

    }

}

Step 8: Service is hosted, now we need to implement the proxy class for the client. There are different ways of creating the proxy

• Using SvcUtil.exe, we can create the proxy class and configuration file with end points.
• Adding Service reference to the client application.
• Implementing ClientBase class

Of these three methods, Implementing ClientBase is the best practice. If you are using rest two method, we need to create proxy class every time when we make changes in Service implementation. But this is not the case for ClientBase. It will create the proxy only at runtime and so it will take care of everything.

MyCalculatorServiceProxy.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.ServiceModel;

using MyCalculatorService;

namespace MyCalculatorServiceProxy

{

    public class MyCalculatorServiceProxy :

        //WCF create proxy for ISimpleCalculator using ClientBase

        ClientBase,

        ISimpleCalculator

    {

        public int Add(int num1, int num2)

        {

            //Call base to do funtion

            return base.Channel.Add(num1, num2);

        }

    }

}

Step 9: In the client side, we can create the instance for the proxy class and call the method as shown below. Add proxy assembly as reference to the project.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.ServiceModel;

namespace MyCalculatorServiceClient

{

    class Program

    {

        static void Main(string[] args)

        {

            MyCalculatorServiceProxy.MyCalculatorServiceProxy proxy ;

            proxy= new MyCalculatorServiceProxy.MyCalculatorServiceProxy();

            Console.WriteLine("Client is running at " + DateTime.Now.ToString());

            Console.WriteLine("Sum of two numbers... 5+5 ="+proxy.Add(5,5));

            Console.ReadLine();

        }

    }

}

Step 10 : End point (same as service) information should be added to the configuration file of the client application.

 

   

      <endpoint address ="http://localhost:8090/MyService/SimpleCalculator"

                binding ="wsHttpBinding"

                contract ="MyCalculatorService.ISimpleCalculator">

       

     

   

 

Step 11: Before running the client application, you need to run the service. Output of the client application is shown below.

This self host shows advantage such as in-Pro hosting, programmatic access and it can be used when there need singleton service. I hope you have enjoyed the Self hosting session, now let go for hosting using Windows Activation service.

Service Contract

Service contract describes the operation that service provide. A Service can have more than one service contract but it should have at least one Service contract.

Service Contract can be define using [ServiceContract] and [OperationContract] attribute. [ServiceContract] attribute is similar to the [WebServcie] attribute in the WebService and [OpeartionContract] is similar to the [WebMethod] in WebService.

• It describes the client-callable operations (functions) exposed by the service
• It maps the interface and methods of your service to a platform-independent description
• It describes message exchange patterns that the service can have with another party. Some service operations might be one-way; others might require a request-reply pattern
• It is analogous to the element in WSDL

To create a service contract you define an interface with related methods representative of a collection of service operations, and then decorate the interface with the ServiceContractAttribute to indicate it is a service contract. Methods in the interface that should be included in the service contract are decorated with the OperationContractAttribute.

[ServiceContract()]

    public interface ISimpleCalculator

    {

        [OperationContract()]

        int Add(int num1, int num2);

    }

Once we define Service contract in the interface, we can create implement class for this interface.

public  class SimpleCalculator : ISimpleCalculator

    {

   

        public int Add(int num1, int num2)

        {

            return num1 + num2;

        }

    }

With out creating the interface, we can also directly created the service by placing Contract in the implemented class. But it is not good practice of creating the service

[ServiceContract()]

   public class SimpleCalculator

   {

       [OperationContract()]

       public int Add(int num1, int num2)

       {

           return num1 + num2;

       }

   }

Data Contract

A data contract is a formal agreement between a service and a client that abstractly describes the data to be exchanged.
Data contract can be explicit or implicit. Simple type such as int, string etc has an implicit data contract. User defined object are explicit or Complex type, for which you have to define a Data contract using [DataContract] and [DataMember] attribute.
A data contract can be defined as follows:

• It describes the external format of data passed to and from service operations
• It defines the structure and types of data exchanged in service messages
• It maps a CLR type to an XML Schema
• t defines how data types are serialized and deserialized. Through serialization, you convert an object into a sequence of bytes that can be transmitted over a network. Through deserialization, you reassemble an object from a sequence of bytes that you receive from a calling application.
• It is a versioning system that allows you to manage changes to structured data

We need to include System.Runtime.Serialization reference to the project. This assembly holds the DataContract and DataMemberattribute.
Create user defined data type called Employee. This data type should be identified for serialization and deserialization by mentioning with [DataContract] and [DataMember] attribute.

 [ServiceContract]

    public interface IEmployeeService

    {

        [OperationContract]

        Employee GetEmployeeDetails(int EmpId);

    }

    [DataContract]

    public class Employee

    {

        private string m_Name;

        private int m_Age;

        private int m_Salary;

        private string m_Designation;

        private string m_Manager;

        [DataMember]

        public string Name

        {

            get { return m_Name; }

            set { m_Name = value; }

        }

        [DataMember]

        public int Age

        {

            get { return m_Age; }

            set { m_Age = value; }

        }

        [DataMember]

        public int Salary

        {

            get { return m_Salary; }

            set { m_Salary = value; }

        }

        [DataMember]

        public string Designation

        {

            get { return m_Designation; }

            set { m_Designation = value; }

        }

        [DataMember]

        public string Manager

        {

            get { return m_Manager; }

            set { m_Manager = value; }

        }

    }

Implementation of the service class is shown below. In GetEmployee method we have created the Employee instance and return to the client. Since we have created the data contract for the Employee class, client will aware of this instance whenever he creates proxy for the service.

public class EmployeeService : IEmployeeService

    {

        public Employee GetEmployeeDetails(int empId)

        {

            Employee empDetail = new Employee();

            //Do something to get employee details and assign to 'empDetail' properties

            return empDetail;

        }

    }

Client side

On client side we can create the proxy for the service and make use of it. The client side code is shown below.

protected void btnGetDetails_Click(object sender, EventArgs e)

        {

            EmployeeServiceClient objEmployeeClient = new EmployeeServiceClient();

            Employee empDetails;

            empDetails = objEmployeeClient.GetEmployeeDetails(empId);

//Do something on employee details

        }

WCF Tutorial

                             WCF(WINDOWS COMMUNICATION FOUNDATION)

Introduction to WCF:

Windows Communication Foundation (Code named Indigo) is a programming platform and runtime system for building, configuring and deploying network-distributed services. It is the latest service oriented technology; Interoperability is the fundamental characteristics of WCF. It is unified programming model provided in .Net Framework 3.0. WCF is a combined features of Web Service, Remoting, MSMQ and COM+. WCF provides a common platform for all .NET communication.

Below figures shows the different technology combined to form WCF.

Image binary format c#

source:

    <div>

        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"

        OnRowCommand="gv_command">

        <Columns>

            <asp:TemplateField HeaderText="files">

                <ItemTemplate>

                    <asp:LinkButton ID="l1" runat="server" Text='<%#Eval("filenam") %>' CommandArgument='<%#Eval("id") %>' CommandName="link"></asp:LinkButton>

                </ItemTemplate>

            </asp:TemplateField>

            <asp:TemplateField HeaderText="image">

                <ItemTemplate>

                    <asp:ImageButton ID="i1" runat="server" ImageUrl='<%#"Handler.ashx?id=" + Eval("id")  %>' CommandName="image" CommandArgument='<%#Eval("id") %>'

                    Visible='<%#Convert.ToString(Eval("isimage")).Trim()=="yes"?true:false %>' Height="50px" Width="50px" />

                </ItemTemplate>

            </asp:TemplateField>

        </Columns>

        </asp:GridView>

        <asp:FileUpload ID="FileUpload1" runat="server" />

        <asp:Button ID="Button1" runat="server" Text="Upload" OnClick="b1_click"/><br />

        <asp:Image ID="Image1" runat="server" Height="300px" Width="300px"/>

    </div>

aspx.cs:

using System.Data.SqlClient;

using System.Data;

using System.IO;

    SqlConnection con = new SqlConnection("Data Source=.;database=db;UID=sa;Pwd=sa123");

    SqlDataAdapter da = new SqlDataAdapter();

    DataSet ds = new DataSet();

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack) {

            GetData();

        }

    }

    private void GetData()

    {

        da = new SqlDataAdapter("select * from upld order by id", con);

        ds = new DataSet();

        da.Fill(ds);

        GridView1.DataSource = ds;

        GridView1.DataBind();

    }

    protected void gv_command(object sender, GridViewCommandEventArgs e)

    {

        if (e.CommandName == "link")

        {

            string path = "";

            byte[] a = getbyte(e.CommandArgument.ToString(), out path);

 

            if (!File.Exists(Server.MapPath(path)))

                //File.Delete(Server.MapPath(path));

                File.WriteAllBytes(Server.MapPath(path), a);

            System.Diagnostics.Process.Start(Server.MapPath(path));

        }

        else if (e.CommandName == "image")

        {

            string path = "";

            byte[] a = getbyte(e.CommandArgument.ToString(), out path);

            string fil = path.Substring(path.LastIndexOf('.') + 1).ToUpper();

            string[] files = new string[] { "GIF", "PNG", "JPG", "BMP", "ICO" };

            if (files.Contains(fil))

            {

                MemoryStream ms = new MemoryStream(a);

                System.Drawing.Bitmap bmp = new System.Drawing.Bitmap(ms);

                if (!File.Exists(Server.MapPath("d/" + ds.Tables[0].Rows[0][1].ToString())))

                    //File.Delete(Server.MapPath("d/" + ds.Tables[0].Rows[0][1].ToString()));

                    bmp.Save(Server.MapPath("d/" + ds.Tables[0].Rows[0][1].ToString()), System.Drawing.Imaging.ImageFormat.Jpeg);

                Image1.ImageUrl = "d/" + ds.Tables[0].Rows[0][1].ToString();

            }

        }

    }

 

    byte[] getbyte(string s, out string p)

    {

        da = new SqlDataAdapter("select top 1 filebin,filenam from upld where id=" + s, con);

        ds = new DataSet();

        da.Fill(ds);

        string fil = ds.Tables[0].Rows[0][1].ToString().Substring(ds.Tables[0].Rows[0][1].ToString().LastIndexOf('.') + 1);

        p = "d/" + ds.Tables[0].Rows[0][1].ToString();

        return (byte[])ds.Tables[0].Rows[0][0];

    }

    protected void b1_click(object sender, EventArgs e) 

    {

        FileUpload1.PostedFile.SaveAs(Server.MapPath(FileUpload1.FileName));

        string sFileName = Server.MapPath(FileUpload1.FileName);

        byte[] fileData = File.ReadAllBytes(Server.MapPath(FileUpload1.FileName));

        SqlCommand cmd = new SqlCommand();

        cmd.Parameters.Add(new SqlParameter("@filenam", sFileName));

        cmd.Parameters.Add(new SqlParameter("@filebin", fileData));

 

        string fil = FileUpload1.PostedFile.ContentType.ToString();

        fil = fil.Substring(fil.LastIndexOf('/') + 1).ToUpper();

        string[] files = new string[] { "GIF", "PNG", "JPG", "BMP","ICO" };

        fil = files.Contains(fil) ? "yes" : "no";

 

        cmd.CommandText = "insert into upld(path,filebin,filenam,isimage) values(@filenam, @filebin,'" + FileUpload1.FileName + "','" + fil + "')";

        cmd.Connection = con;

        con.Open();

        cmd.ExecuteNonQuery();

        File.Delete(Server.MapPath(FileUpload1.FileName));

        GetData();

    }

ashx.cs:

using System.Data;

using System.Data.SqlClient;

public void ProcessRequest (HttpContext context)

{

    SqlConnection con = new SqlConnection("Data Source=.;database=db;UID=sa;Pwd=sa123");

        SqlDataAdapter da = new SqlDataAdapter();

        DataSet ds = new DataSet();

 

        da = new SqlDataAdapter("select top 1 filebin from upld where id=" + context.Request.QueryString["ID"].ToString(), con);

        ds = new DataSet();

        da.Fill(ds);

        context.Response.BinaryWrite((byte[])ds.Tables[0].Rows[0][0]);

}

db: with auto increment id

Row to Column conversion separated delimiter in sql

declare @str1 varchar(max)=',,sql,,,asp,technique,yield,ado,,'
declare @str2 varchar(max)=''
create table #tbl1 (id varchar(max))

while(charindex(',',@str1)>0)
begin
if isnull(@str2,'')<>''
insert into #tbl1 select @str2
select @str2 = substring(@str1,1,charindex(',',@str1)-1)
set @str1 = substring(@str1,charindex(',',@str1)+1,len(@str1))
end

if isnull(@str2,'')<>''
insert into #tbl1 select @str2

select * from #tbl1
drop table #tbl1

Reset Identity for the table in sqlserver

CREATE TABLE IdentTest (Ident INT NOT NULL IDENTITY (1,1), varfield varchar(100))
INSERT INTO IdentTest VALUES ('abc')

DBCC CHECKIDENT ('IdentTest',RESEED,100)

-- here 100 starts identity from 101 if is there any data in table otherwise it starts from 100, for 1 you have to  use 0
INSERT INTO IdentTest VALUES ('def')

SELECT * FROM IdentTest
--Note: if the inserting with already existing identity, then it will raise the error.
--****first POST of the Year

Happy new Year with sqlserver query

SELECT CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15) + CHAR(15) + CHAR(15) + CHAR(15) + CHAR(13) + CHAR(72) + CHAR(65) + CHAR(80) + CHAR(80)+ CHAR(89) + ' ' + CHAR(78) + CHAR(69)+CHAR(87)+ ' ' + CHAR(89)+ CHAR(69)+ CHAR(65) + CHAR(82) +CHAR(13)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)+CHAR(15)

Get each char ASCII value from :

DECLARE @i int
SET @i = 1
WHILE @i < = 256 Begin PRINT CHAR(@i) + ' --> ' + CONVERT(VARCHAR,@i)
SET @i = @i + 1
END

***Last POST of the Year

Create a table named 'select' with column 'table'

SET QUOTED_IDENTIFIER ON
CREATE TABLE "SELECT" ("TABLE" int)

When SET QUOTED_IDENTIFIER is ON, all strings delimited by double quotation marks are interpreted as object identifiers. Therefore, quoted identifiers do not have to follow the Transact-SQL rules for identifiers. They can be reserved keywords and can include characters not usually allowed in Transact-SQL identifiers

Reference: http://msdn.microsoft.com/en-us/library/aa259228(v=sql.80).aspx

or

CREATE TABLE [SELECT] ([TABLE] INT)

Customize sql startup query window from:

C:\Users\My-PC\AppData\Local\VirtualStore\Program Files (x86)\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SsmseeTemplates\Sql\SqlFile.sql

(Chclt)

C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\SqlWorkbenchProjectItems\Sql\SqlFile.sql

Replace multiple spaces with one/none in sql server

select replace(replace(replace(LTrim(RTrim('      6      Spaces  6      Spaces.      ')),'  ',' |'),'| ',''),
--'|','')
' |','')

select replace(
replace(
replace(
LTrim(RTrim('      6      Spaces  6      Spaces.      '))
,' ',' |')
,'| ',''),
' |',' ')

--Trim the field
--Mark double spaces
--Delete double spaces offset by 1
--Tidy up

sort array of int values without using built-in functions (using Bubble sort)

string res = "";
int[] arr = { 800, 11, 50, 771, 649, 770, 240, 9 };
for (int k = 0; k < arr.Length; k++)
{
for (int i = 0; i < arr.Length - 1; i++)
{
if (arr[i] > arr[i + 1])
{
int hold = arr[i + 1];
arr[i + 1] = arr[i];
arr[i] = hold;
}
}
res += arr[k].ToString();
arr[k] = arr[k];
}

string s = "1,2,3,,3,2";
string[] ss = (s.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries));
for (int k = 1; k < ss.Length; k++)
{
for (int i = 0; i < (ss.Length - 1); i++)
{
if (Convert.ToInt32(ss[i].Trim()) > Convert.ToInt32(ss[i + 1].Trim()))
{
string hold = ss[i];
ss[i] = ss[i + 1];
ss[i + 1] = hold;
}
}
}
s = string.Join(",", ss);