Date format for sql and string

   DateTimeFormatInfo finfo = new DateTimeFormatInfo();
        finfo.ShortDatePattern = "dd/MM/yyyy";
        Convert.ToDateTime("31/01/2009", finfo)


1  select convert(varchar, getdate(), 1)  12/30/06 
2  select convert(varchar, getdate(), 2)  06.12.30 
3  select convert(varchar, getdate(), 3)  30/12/06 
4  select convert(varchar, getdate(), 4)  30.12.06 
5  select convert(varchar, getdate(), 5)  30-12-06 
6  select convert(varchar, getdate(), 6)  30 Dec 06 
7  select convert(varchar, getdate(), 7)  Dec 30, 06 
10  select convert(varchar, getdate(), 10)  12-30-06 
11  select convert(varchar, getdate(), 11)  06/12/30 
101  select convert(varchar, getdate(), 101)  12/30/2006 
102  select convert(varchar, getdate(), 102)  2006.12.30 
103  select convert(varchar, getdate(), 103)  30/12/2006 
104  select convert(varchar, getdate(), 104)  30.12.2006 
105  select convert(varchar, getdate(), 105)  30-12-2006 
106  select convert(varchar, getdate(), 106)  30 Dec 2006 
107  select convert(varchar, getdate(), 107)  Dec 30, 2006 
110  select convert(varchar, getdate(), 110)  12-30-2006 
111  select convert(varchar, getdate(), 111)  2006/12/30 
  
  string a;
        Response.Write(string.Format("{0:y yy yyy yyyy}", dt)+"<br/>");  // "8 08 008 2008"   year
        Response.Write(string.Format("{0:M MM MMM MMMM}", dt)+"<br/>"); // "3 03 Mar March"  month
        Response.Write(string.Format("{0:d dd ddd dddd}", dt)+"<br/>"); // "9 09 Sun Sunday" day
        Response.Write(string.Format("{0:h hh H HH}", dt)+"<br/>"); // "4 04 16 16"      hour 12/24
        Response.Write(string.Format("{0:m mm}", dt)+"<br/>"); // "5 05"            minute
        Response.Write(string.Format("{0:s ss}", dt)+"<br/>"); // "7 07"            second
        Response.Write(string.Format("{0:f ff fff ffff}", dt)+"<br/>"); // "1 12 123 1230"   sec.fraction
        Response.Write(string.Format("{0:F FF FFF FFFF}", dt)+"<br/>"); // "1 12 123 123"    without zeroes
        Response.Write(string.Format("{0:t tt}", dt)+"<br/>"); // "P PM"            Response.Write(string.M. or P.M.
        Response.Write(string.Format("{0:z zz zzz}", dt)+"<br/>"); // "-6 -06 -06:00"   time zone
        Response.Write(string.Format("{0:t}", dt)+"<br/>"); // "4:05 PM"                         ShortTime
        Response.Write(string.Format("{0:d}", dt)+"<br/>"); // "3/9/2008"                        ShortDate
        Response.Write(string.Format("{0:T}", dt)+"<br/>"); // "4:05:07 PM"                      LongTime
        Response.Write(string.Format("{0:D}", dt)+"<br/>"); // "Sunday, March 09, 2008"          LongDate
        Response.Write(string.Format("{0:f}", dt)+"<br/>"); // "Sunday, March 09, 2008 4:05 PM"  LongDate+ShortTime
        Response.Write(string.Format("{0:F}", dt)+"<br/>"); // "Sunday, March 09, 2008 4:05:07 PM" FullDateTime
        Response.Write(string.Format("{0:g}", dt)+"<br/>"); // "3/9/2008 4:05 PM"                ShortDate+ShortTime
        Response.Write(string.Format("{0:G}", dt)+"<br/>"); // "3/9/2008 4:05:07 PM"             ShortDate+LongTime
        Response.Write(string.Format("{0:m}", dt)+"<br/>"); // "March 09"                        MonthDay
        Response.Write(string.Format("{0:y}", dt)+"<br/>"); // "March, 2008"                     YearMonth
        Response.Write(string.Format("{0:r}", dt)+"<br/>"); // "Sun, 09 Mar 2008 16:05:07 GMT"   RFC1123
        Response.Write(string.Format("{0:s}", dt)+"<br/>"); // "2008-03-09T16:05:07"             SortableDateTime
        Response.Write(string.Format("{0:u}", dt)+"<br/>"); // "2008-03-09 16:05:07Z"            UniversalSortableDateTime
        // month/day numbers without/with leading zeroes
        Response.Write(string.Format("{0:M/d/yyyy}", dt)+"<br/>");           // "3/9/2008"
        Response.Write(string.Format("{0:MM/dd/yyyy}", dt)+"<br/>");         // "03/09/2008"

        // day/month names
        Response.Write(string.Format("{0:ddd, MMM d, yyyy}", dt)+"<br/>");   // "Sun, Mar 9, 2008"
        Response.Write(string.Format("{0:dddd, MMMM d, yyyy}", dt)+"<br/>"); // "Sunday, March 9, 2008"
        Response.Write(string.Format("{0:MMMM dd, yyyy}", dt) + "<br/>");
        // two/four digit year
        Response.Write(string.Format("{0:MM/dd/yy}", dt)+"<br/>");           // "03/09/08"
        Response.Write(string.Format("{0:MM/dd/yyyy}", dt)+"<br/>");         // "03/09/2008"
        // date separator in german culture is "." (so "/" changes to ".")
        Response.Write(string.Format("{0:d/M/yyyy HH:mm:ss}", dt)+"<br/>");// "9/3/2008 16:05:07" - english (en-US)
        Response.Write(string.Format("{0:d/M/yyyy HH:mm:ss}", dt)+"<br/>");// "9.3.2008 16:05:07" - german (de-DE)
        //Response.Write(a);

If File or Directory not exist then create folder and file and if exist then create

string path = "uploads\\investeducationfiles";
                    bool folderExists = Directory.Exists(Server.MapPath(path));
                    if (!folderExists)
                        Directory.CreateDirectory(Server.MapPath(path));

                    string path1 = Server.MapPath("uploads\\investeducationfiles\\log.xls");

                    if (!File.Exists(path1))
                    {
                        using (StreamWriter sw = File.CreateText(path1))
                        {
                            sw.Write(strattechment.ToString());
                        }
                    }
                    else
                    {
                        using (FileStream fs = new FileStream(path1, FileMode.Create))
                        {
                            using (StreamWriter sw = new StreamWriter(fs))
                            {
                                sw.Write(strattechment.ToString());
                            }
                        }
                    }

Create customise Excel by Code in c sharp

Hint:-

When Excel Sheet Data Uploading and sheet data is not well formatted then Excel Sheet not read data properly due to mix data format
But this issue resolved just change excel connection string :

1)    connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sFilePath + @";Extended Properties=" + Convert.ToChar(34).ToString() + @"Excel 12.0;Imex=1;HDR=Yes;" + Convert.ToChar(34).ToString();
2)    connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sFilePath + @";Extended Properties=" + Convert.ToChar(34).ToString() + @"Excel 8.0;Imex=1;HDR=Yes;" + Convert.ToChar(34).ToString();
some time numeric data then varchar data insert into database by excel that time geting problam
 --------------------------------------------------------------
using System;
using System.Collections;
using System.Configuration;
using System.IO;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using Crypto;
using System.Text;

public partial class Admin_reports : System.Web.UI.Page
{
    public SqlConnection Connection;

    public Admin_reports()
    {
            Crypto.Crypto crp = new Crypto.Crypto(Crypto.Crypto.Providers.RC2);
            string SqlConnectinString = (ConfigurationManager.ConnectionStrings["ConnStr_Leads"].ConnectionString);

            SqlConnectinString = crp.Decrypt(SqlConnectinString);
            Connection = new SqlConnection(SqlConnectinString);
        }

    public SqlCommand cmd;
    public DataSet ds;
    public int i;

  
  

    /// <summary>
    /// Generate excel file to download data.
    /// </summary>
    private void WriteOutputToExcel()
    {
        try
        {
            lblDataMessage.Text = "";
            Connection.Open();
            SqlDataAdapter da = new SqlDataAdapter();
            ds = new DataSet();
            cmd = new SqlCommand("spGetCustomerEmailReport", Connection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@fromdate", txtFromDate.Text);
            cmd.Parameters.AddWithValue("@todate", txtToDate.Text);
            da.SelectCommand = cmd;
            da.Fill(ds);
            if (ds.Tables[0].Rows.Count > 0)
            {
                StringBuilder str = new StringBuilder();

                str.Append("<table width='100%' border='1'>");
                str.Append("<tr>");
                str.Append("<td colspan='12' align='center'>");
                str.Append(" Email ID confirmation and update report");
                str.Append("</td>");
                str.Append("</tr>");

                str.Append("<tr>");
                str.Append("<td colspan='12' align='center'>");
                str.Append("&nbsp;");
                str.Append("</td>");
                str.Append("</tr>");

                str.Append("<tr>");
                str.Append("<td colspan='12' align='center'>");
                str.Append("From Date: " + txtFromDate.Text + " To Date: " + txtToDate.Text);
                str.Append("</td>");
                str.Append("</tr>");

                str.Append("<tr>");
                str.Append("<td colspan='12' align='center'>");
                str.Append("&nbsp;");
                str.Append("</td>");
                str.Append("</tr>");

                str.Append("<tr >");
                str.Append("<td style='font-weight:bold;background-color:#BDBDBD;'>Sr.No.</td>");
                str.Append("<td style='font-weight:bold;background-color:#BDBDBD;' >Customer Name</td>");
                str.Append("<td style='font-weight:bold;background-color:#BDBDBD;' >Customer Id</td>");
                str.Append("<td style='font-weight:bold;background-color:#BDBDBD;' >Mobile No</td>");
                str.Append("<td style='font-weight:bold;background-color:#BDBDBD;' >E-Mail Id</td>");
                str.Append("<td style='font-weight:bold;background-color:#BDBDBD;' >Verification Code</td>");
                str.Append("<td style='font-weight:bold;background-color:#BDBDBD;' >Verify Status</td>");
                str.Append("<td style='font-weight:bold;background-color:#BDBDBD;' >Entry Date/Time</td>");
                str.Append("<td style='font-weight:bold;background-color:#BDBDBD;' >Verify Date/Time</td>");
                str.Append("<td style='font-weight:bold;background-color:#BDBDBD;' >No. of Attempt</td>");
                str.Append("<td style='font-weight:bold;background-color:#BDBDBD;' >Validity</td>");
                str.Append("<td style='font-weight:bold;background-color:#BDBDBD;' >Campaign Id</td>");
                str.Append("</tr>");

                for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
                {
                    str.Append("<tr >");
                    str.Append("<td >" + ds.Tables[0].Rows[i]["Sr.No."].ToString() + "</td>");
                    str.Append("<td >" + ds.Tables[0].Rows[i]["Customer Name"].ToString() + "</td>");
                    str.Append("<td >" + ds.Tables[0].Rows[i]["Customer Id"].ToString() + "</td>");
                    str.Append("<td >" + ds.Tables[0].Rows[i]["Mobile No"].ToString() + "</td>");
                    str.Append("<td >" + ds.Tables[0].Rows[i]["E-Mail Id"].ToString() + "</td>");
                    str.Append("<td >" + ds.Tables[0].Rows[i]["Verification Code"].ToString() + "</td>");
                    str.Append("<td >" + ds.Tables[0].Rows[i]["Verify Status"].ToString() + "</td>");
                    str.Append("<td >" + ds.Tables[0].Rows[i]["Entry Date/Time"].ToString() + "</td>");
                    str.Append("<td >" + ds.Tables[0].Rows[i]["Verify Date/Time"].ToString() + "</td>");
                    str.Append("<td >" + ds.Tables[0].Rows[i]["No. of Attempt"].ToString() + "</td>");
                    str.Append("<td >" + ds.Tables[0].Rows[i]["Validity"].ToString() + "</td>");
                    str.Append("<td >" + ds.Tables[0].Rows[i]["Campaign Id"].ToString() + "</td>");
                    str.Append("</tr>");
                }

                str.Append("</table>");

                //DataGrid dggrid = new DataGrid();
                //dggrid.DataSource = ds;
                //dggrid.DataBind();

                //StringWriter tw = new StringWriter();
                //HtmlTextWriter hw = new HtmlTextWriter(tw);
                //dggrid.HeaderStyle.BackColor = System.Drawing.Color.LightGray;
                //dggrid.RenderControl(hw);
                Response.ContentType = "application/vnd.ms-excel";
                Response.AppendHeader("content-disposition", "attachment; filename=Customer_Email_Report.xls");
                Response.Write(str.ToString());
                Response.End();

            }
            else
            {
                lblDataMessage.Text = "No data found.";
            }
            Connection.Close();
        }
        catch (Exception ex)
        {

        }
    }

   

   

}

Js function call on cs side

 ClientScript.RegisterStartupScript (GetType(), "Javascript", "javascript: fnShowMessage(); ", true);

ScriptManager.RegisterStartupScript(Page, Page.GetType(), Guid.NewGuid().ToString(), "nextResource();", true);

this.Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "aa", "$('#overlay').show(); $('.form_close').addClass('active');$('.rent_now_wrapper').animate({ left: 0 }, 500);", true);

this.Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "aa", "alert(1);", true);

chart highchart

 url:- http://api.highcharts.com/highcharts#plotOptions.pie.innerSize

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="pichart_2.aspx.cs" Inherits="pichart_2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
            <script type="text/javascript" src="Scripts/jquery.min.js"></script>
     <script type="text/javascript" src="Scripts/jquery-1.4.2.min.js"> </script>
     <script type="text/javascript" src="Scripts/exporting.js"></script>
    <script type="text/javascript" src="Scripts/highcharts.js"></script>
   
</head>
<body>
    <form id="form1" runat="server">
      <asp:HiddenField ID="asstesID" runat="server" />
  <div id="divdata" runat="server">
   
    </div>
    </form>
</body>
</html>

using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using System.Data;

public partial class pichart_2 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        DataTable _dt = new DataTable();
        DataColumn col1 = new DataColumn("AssetsName");
        DataColumn col2 = new DataColumn("Percentage");
        col1.DataType = System.Type.GetType("System.String");
        col2.DataType = System.Type.GetType("System.Int32");

        _dt.Columns.Add(col1);
        _dt.Columns.Add(col2);


        //fore each loop while for etc
        _dt.Rows.Add("Equity ", 75);
        _dt.Rows.Add("Debt", 25);

        string strDataAssets = getPieChartAsstesDataformat(_dt, "divdata");
        //ScriptManager.RegisterClientScriptBlock(this.Page, GetType(), "alertT", strDataAssets, false);
        Page.ClientScript.RegisterClientScriptBlock(GetType(), "al", strDataAssets);

    }

    public string getPieChartAsstesDataformat(DataTable dt, String strDivName)
    {
        //define string
        string seriesData = "";
        string strCol1 = "{type: 'pie',  name: 'AssetsName  Percentage' ,data: [";

        try
        {
            //check if dt is not null
            if (dt != null)
            {
                //chk if has rows
                if (dt.Rows.Count > 0)
                {
                    //loop thru the rows
                    foreach (DataRow dr in dt.Rows)
                    {

                        //if (asstesID.Value == dr["AssetsName"].ToString())
                        //{
                           // strCol1 += "{ name: '" + dr["AssetsName"].ToString() + "',y: " + dr["Percentage"].ToString() + ",sliced: true,selected: true},";
                        //}
                        //else
                        //{
                            strCol1 += " ['" + dr["AssetsName"].ToString() + "' , " + dr["Percentage"].ToString() + "] ,";
                        //}

                        //strCol1 += " ['" + dr["AssetsName"].ToString() + "' , " + dr["Percentage"].ToString() + "] ,";
                    }
                    if (strCol1.EndsWith(","))
                    {
                        strCol1 = strCol1.Substring(0, strCol1.LastIndexOf(",")) + "";
                    }
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }

        //close strngs
        strCol1 += "]}";
        //strCol2 += "]},";

        //append
        seriesData = strCol1;//+ strCol2;

        //create chart data
        StringBuilder strData = new StringBuilder();
        strData.Append("<script type=\"text/javascript\">");
        strData.Append("var chartA;");
        //strData.Append("  document.getElementById('" + strDivName + "').style.visibility = 'visible';");
        strData.Append("$(document).ready(function() {");
        strData.Append("chartA = new Highcharts.Chart({");
        strData.Append("chart: {  ");
        strData.Append("renderTo: '" + strDivName + "',");
        strData.Append("plotBackgroundColor: null,");
        strData.Append("plotBorderWidth: null,");
        strData.Append("plotShadow: true");
        strData.Append("}, ");
        strData.Append("title: {");
        strData.Append("text: '' ");
        //  strData.Append("x: -20 ");
        strData.Append("},");
        strData.Append(" tooltip: { shadow : true,borderWidth: 1, ");
        strData.Append("formatter: function() {");
        strData.Append("return '<b>'+ this.point.name +'</b><br/>'+");
        strData.Append("this.y +' %';");
        strData.Append("}");
        strData.Append("},");

        strData.Append("  plotOptions: {  ");
        strData.Append(" pie: { borderColor: '#C0C0C0',  borderWidth: 5,");
        strData.Append(" allowPointSelect: true,animation:true, ");
        strData.Append(" cursor: 'pointer', ");
        //strData.Append(" point: {events: { ");
        //strData.Append(" click: function(event) { ");
        //strData.Append(" console.log(event.point);");
        //strData.Append(" }");
        //strData.Append(" }},");
        strData.Append(" dataLabels: {  ");
        strData.Append(" enabled: true,   ");
        strData.Append(" formatter: function() {");
        strData.Append(" return '<b>'+ this.point.name +'</b>: '+ this.y +' %';}");
        strData.Append(" },");
        strData.Append(" showInLegend: true ");
        strData.Append(" } ");
        strData.Append(" },");

        strData.Append("subtitle: {   ");
        strData.Append("text: '',");
        strData.Append("x: -20");
        strData.Append("},");
        strData.Append("xAxis: {  ");
        strData.Append("},");
        strData.Append("yAxis: { ");
        strData.Append("lineWidth :1,");
        strData.Append("title: {");
        strData.Append("text: ''");
        strData.Append("}");
        strData.Append("},");


        strData.Append("series: [" + seriesData + "]");
        strData.Append("});");
        strData.Append("});");
        strData.Append("</script>");

        //return string
        return strData.ToString();
    }
}

Xml DataTable dump SQL SP

 @xmlLanguage xml  

SET ARITHABORT ON

if (@Action='insertDownloadCenterDetails')
                Begin
                           insert into DownloadCenter(Title,UploadFile)values(@Title,@UploadFile)
                           set @DownloadCenterId=@@identity
                 end
                   Begin
                       DELETE FROM DownloadCenterDetails WHERE fkDownloadCenterId = @DownloadCenterId
                   end                 
      
            SELECT  
                ISNULL(cast(Colx.query('data(fkTabId)') as varchar(max)),'0') as fkTabId,
                ISNULL(cast(Colx.query('data(fkSubTabId)') as varchar(max)),'0') as fkSubTabId
                INTO #TMPLanguage1 FROM @xmlLanguage.nodes('DocumentElement/Table') AS Tabx(Colx)
                insert into DownloadCenterDetails(fkDownloadCenterId,fkTabId,fkSubTabId)
                select @DownloadCenterId,fkTabId, fkSubTabId from #TMPLanguage1
 =================================================================

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.IO;

public partial class Xml_DataTable_dump_SQL_SP_code : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        DataTable _dt = new DataTable();

        DataColumn col1 = new DataColumn("fkTabId");
        DataColumn col2 = new DataColumn("fkSubTabId");


        col1.DataType = System.Type.GetType("System.Int32");
        col2.DataType = System.Type.GetType("System.Int32");


        _dt.Columns.Add(col1);
        _dt.Columns.Add(col2);


        //fore each loop while for etc
        _dt.Rows.Add("", "");

        //_DownloadCenter.strxmlData = CommonFunctions.GetXMLOfDataTable(_dt);
        //_DownloadCenter.InsertDownloadCenterDetails();
        //_DownloadCenter = null;
    }
    public bool InsertDownloadCenterDetails()
    {
        bool flag = false;
        try
        {

            //obj = _accessData.GetObject();
            //obj[0] = "insertDownloadCenterDetails";
            //obj[1] = _DownloadCenterId;
            //obj[2] = _TabId;
            //obj[3] = _SubTabId;
            //obj[6] = _Title;
            //obj[7] = _UploadFile;
            //obj[12] = strxmlData;
            //flag = _accessData.executeStatement(obj);

            return flag;
        }
        catch (Exception ex)
        {
            throw (ex);
        }
    }
    public static string GetXMLOfDataTable(DataTable dtToProcess)
    {
        string strOutput = "";
        try
        {
            dtToProcess.TableName = "Table";

            StringWriter sw = new StringWriter();

            dtToProcess.WriteXml(sw);
            strOutput = sw.ToString();
        }
        catch (Exception ex)
        {
        }


        return strOutput;
    }
}

Many Rows Cells merge in one cells in SQL

(select REPLACE (replace(SUBSTRING((select distinct ',',RTRIM(LTRIM(p.EmplID))
from tb_tab_Employee_code p where p.EmplID in (select s.EmployeeID from tb_tab_OthereEmployeeInvoled s where
 s.EmployeeReportId=id)
 for xml path('')),2,200000),'<EmplID>',''),'</EmplID>','')) as [Other Involed EmployeeID]