Split Date Range using SQL Server

Declare @StartDate DateTime, @EndDate DateTime, @FromDate DateTime, @ToDate DateTime, @CurrentCalYearStart DateTime, @CurrentCalYearEnd DateTime

Set @StartDate='06/25/1980'
Set @EndDate='06/26/1983'

SET @FromDate = @StartDate
SET @ToDate = @EndDate
WHILE (year(@FromDate) <= YEAR(@EndDate))
BEGIN
    SET @CurrentCalYearStart = DATEADD(MM,0,DATEADD(YY,DATEDIFF(YY,0,@FromDate),0))
    SET @CurrentCalYearEnd = DATEADD(MM,12,DATEADD(YY,DATEDIFF(YY,0,@FromDate),-1))
   
    IF (datediff(year,@StartDate,@CurrentCalYearStart) = 0 AND datediff(year,@EndDate,@CurrentCalYearEnd) = 0)
    BEGIN
        SET @FromDate = @StartDate
        SET @ToDate = @EndDate
    END
    ELSE IF (datediff(year,@StartDate,@CurrentCalYearStart) = 0 AND datediff(year,@EndDate,@CurrentCalYearEnd) <> 0)
    BEGIN
        SET @FromDate = @StartDate
        SET @ToDate = @CurrentCalYearEnd
    END
    ELSE IF (datediff(year,@StartDate,@CurrentCalYearStart) <> 0 AND datediff(year,@EndDate,@CurrentCalYearEnd) <> 0)
    BEGIN
        SET @FromDate = @CurrentCalYearStart
        SET @ToDate = @CurrentCalYearEnd
    END
    ELSE IF (datediff(year,@StartDate,@CurrentCalYearStart) <> 0 AND datediff(year,@EndDate,@CurrentCalYearEnd) = 0)
    BEGIN
        SET @FromDate = @CurrentCalYearStart
        SET @ToDate = @EndDate
    END

SELECT @FromDate,@ToDate

SET @FromDate = DATEADD(YEAR,1,@FromDate)

END

Storing and Retrieving From App.config

 <?xml version="1.0"?>
<configuration>
  <configSections>
    <sectionGroup name="Country">
        <section name="CountryMapping" type="System.Configuration.NameValueSectionHandler"/>
    </sectionGroup>
  </configSections>
<connectionStrings>
    <clear />
    <add name="XYZ" connectionString="Data Source=localhost;Initial Catalog=NorthWind; Integrated Security=True" providerName="System.Data.SqlClient" />
  </connectionStrings>
<Country>
    <CountryMapping>
      <add key="United States of America" value="USA"/>
      <add key="Kingdom of Great Britain" value="UK"/>
      <add key="China" value="CH"/>
    </CountryMapping>
  </Country>
....
</configuration>

C# Code:
using System.Collections.Specialized;

 NameValueCollection section = (NameValueCollection)ConfigurationManager.GetSection("Country/CountryMapping");
foreach (String valueCollection in section)
{
  if (dr["Country"].ToString() == valueCollection)
  {
     dr["Country"] = section[valueCollection];
  }
} 

For more reference:
http://www.codeproject.com/Articles/21036/Working-with-Configuration-Files-app-config-in-C-C

Convert Datareader to Dataset C#

public static DataSet convertDataReaderToDataSet(SqlDataReader reader)
        {
            DataSet dataSet = new DataSet();
            do
            {
                // Create new data table
                DataTable schemaTable = reader.GetSchemaTable();
                DataTable dataTable = new DataTable();
                if (schemaTable != null)
                {
                    // A query returning records was executed
                    for (int i = 0; i < schemaTable.Rows.Count; i++)
                    {
                        DataRow dataRow = schemaTable.Rows[i];
                        // Create a column name that is unique in the data table
                        string columnName = (string)dataRow["ColumnName"]; //+ "<C" + i + "/>";
                        // Add the column definition to the data table
                        DataColumn column = new DataColumn(columnName, (Type)dataRow["DataType"]);
                        dataTable.Columns.Add(column);
                    }
                    dataSet.Tables.Add(dataTable);
                    // Fill the data table we just created
                    while (reader.Read())
                    {
                        DataRow dataRow = dataTable.NewRow();
                        for (int i = 0; i < reader.FieldCount; i++)
                            dataRow[i] = reader.GetValue(i);
                        dataTable.Rows.Add(dataRow);
                    }
                }
                else
                {
                    // No records were returned
                    DataColumn column = new DataColumn("RowsAffected");
                    dataTable.Columns.Add(column);
                    dataSet.Tables.Add(dataTable);
                    DataRow dataRow = dataTable.NewRow();
                    dataRow[0] = reader.RecordsAffected;
                    dataTable.Rows.Add(dataRow);
                }
            }
            while (reader.NextResult());
            return dataSet;
        }

SSRS:Export to Excel in Individual tabs

 ' Returns a properly formatted list of comma seperated marks
    ' Assumes that the input list is a list of comma seperated values
    ' Adds single quotes around each value and then joins them back together
    Public Function FormattedCommaSeperatedList(ByVal pCommaSeparatedString As String) As String       
        Dim sTokens As String() = pCommaSeparatedString.Split(",")
        Dim iTokenCount As Integer = 0
        FormattedCommaSeperatedList = ""
        If sTokens.Length > 1 Then
            Dim sToken As String
            For Each sToken In sTokens
                'for each token, concat with the single quote and put back into output string
                FormattedCommaSeperatedList = FormattedCommaSeperatedList + "'" + Trim(sToken) + "'"

                If iTokenCount < sTokens.Length - 1 Then
                    FormattedCommaSeperatedList = FormattedCommaSeperatedList + ","
                End If
                iTokenCount = iTokenCount + 1
            Next

        Else
            FormattedCommaSeperatedList = FormattedCommaSeperatedList + "'" + sTokens(0) + "'"
        End If
    End Function

Add .svc in MIME type IIS7

1. Add application in IIS7
2. Right click+switch to Features view
3. Double Click on MIME type
4. From Actions--->  click Add

5. .svc and application/octetstream + Click OK
OR
(try from your side.)
1.) a. Cd %systemRoot%\Microsoft.Net\Framework\v3.0\Windows Communication Foundation\
b. ServiceModelReg.exe -i
c. ServiceModelReg.exe -r

Procedure with output parameter

With the help of Enterprise library:
Solution:

 DbCommand cmdDel = _db.GetStoredProcCommand("USP_YOURPROCEDURE");
                _db.AddOutParameter(cmdDel, "@STATUS", DbType.String, int.MaxValue);
                _db.AddInParameter(cmdDel, "@VID", DbType.Int32, HFVID.Value);
                _db.ExecuteDataSet(cmdDel);
               
                lblStatus.Text = cmdDel.Parameters["@STATUS"].Value.ToString();

A name was started with an invalid character. Error processing resource 'http://localhost/WCF...'

<>The XML page cannot be displayed

Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.

---

A name was started with an invalid character. Error processing resource 'http://localhost/WCFIISService/Service.svc'. Line...

<%@ ServiceHost Service="WCFDefault.Service1" %>

Solution:

IN IIS>Handler Mappings>Add Managed Handlers

Request-path
*.svc

Type:
System.ServiceModel.Activation.HttpHandler, System.ServiceModel, Version=3.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089

Name
svc-integrated