How to find or calculating all nearest or given Distance locations from a coordinates (latitude,longitude) using sql server

 ///////////////// Method I

declare @lat decimal(18,4)=17.24880000

,@lng decimal(18,4)=76.40320000

,@Distance decimal(18,1)=20.0
 
------To search by kilometers instead of miles just multiply by 1.60934
 
select * from(SELECT locationname,locationcode,lat,long,(60 * 1.1515 * (180/pi()) *
acos(
cos(radians(@lat))*cos(radians(Lat)) * cos(radians(Long) - radians(@lng))+ sin (radians(@lat)) * sin(radians(lat))
)
) AS distance FROM mstlocation where SW_GW=2)dt
where distance < @Distance

 ///////////////// Method II

------ 1 miles= 1.60934 Kms and pi()==22/7 or 3.14159265358979323846, 180= degree
select * from(
SELECT locationname,locationcode,lat,long,(
(
acos(sin((@lat*pi()/180)) * sin((lat*pi()/180)) + cos((@lat*pi()/180)) * cos((lat*pi()/180)) * cos(((@lng- long) * pi()/180)))
)
 * (180/pi()) * 60 * 1.1515 *1.60934) as distance FROM mstlocation where SW_GW=2 and statecode='10')dt where distance<=@Distance

///////////// Indicators

----@lng       – this is the variable where passing the longitude of the point.
----@lat       – this is the variable where passing the longitude of the point.
----@Distance  – this is the distance that you would like to find all the markers.
----table      – this is the table… you’ll want to replace that with your table name.
----lat        – this is the field (latitude) of my table..
----long       – this is the field (longitude) of my table.

///////////// Result

lat                      long

17.230000000     76.553333000

17.168333000     76.610000000

17.335000000     76.568333000

17.341667000     76.596667000

17.225000000     76.481667000

Calculate distance between two latitude longitude points using SQL Server

 /////////// Sql query

DECLARE @Lat decimal(18,4) = 17.491716000
,@Long decimal(18,4) = 76.514721000
,@Lat_2 decimal(18,4) = 17.341667000
,@Long_2 decimal(18,4) = 76.596667000
, @Location decimal(18,4) 
 
SET @Location = SQRT(POWER(69.1 * ( @Lat_2 - @Lat),2) + POWER(69.1 * ( @Long - @Long_2)* COS(@Lat_2 / 57.3), 2)) 
select @Location

/////////// Result

11.6913

Convert comma or other delimiters to a Table or List in SQL Server

--///////// Methode 1
 
drop table #TempTbl;
DECLARE @Str NVARCHAR(MAX) = N'1, 2, 3, 4, 5, 6, 7'
DECLARE @sql_xml XML = Cast('<K>'+ Replace(@Str, ',', '</K><K>')+ '</K>' AS XML)   
SELECT f.x.value('.', 'varchar(max)')[ID] INTO #TempTbl FROM @sql_xml.nodes('/K') f(x)   

SELECT * FROM #TempTbl
 
--///////// Methode 2
 
declare @TempTbl TABLE (ID int identity(1,1),Value NVARCHAR(MAX))
DECLARE @Str NVARCHAR(MAX) = N'1, 2, 3, 4, 5, 6, 7'
DECLARE @sql_xml XML = Cast('<K>'+ Replace(@Str, ',', '</K><K>')+ '</K>' AS XML)
insert into @TempTbl(Value) 
SELECT f.x.value('.', 'varchar(max)')[value] FROM @sql_xml.nodes('/K') f(x)   
SELECT * FROM @TempTbl

//////Result

 

Add Dynamic Template filed and controls in Grid-view footer-Row asp.net

 ////////// Html
<div style="width: 100%; overflow: auto;">
            <button type="button" onclick="ClearRows()">Add</button>
            <asp:GridView ID="GridView1" HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White"
                runat="server" AutoGenerateColumns="true" OnRowDataBound="OnRowDataBound" ShowFooter="true">
                <Columns>
                    <asp:TemplateField>
                        <%--<HeaderTemplate>
                            <button type="button" onclick="ClearRows()">Add</button>
                        </HeaderTemplate>--%>
                        <ItemTemplate>
                            <asp:LinkButton runat="server" ID="MM" Text="Edit" OnClick="CLick_EditCLick">                       
                            </asp:LinkButton>
                        </ItemTemplate>
                        <%-- <FooterTemplate>
                            <asp:Button runat="server" ID="jjj" Text="CLick Me" OnClick="Click_ClickME" />
                        </FooterTemplate>--%>
                    </asp:TemplateField>
                </Columns>
            </asp:GridView>
           <asp:Button runat="server" ID="jjj" Text="CLick Me" OnClick="Click_ClickME" />
     </div>
////////////// cs
protected void Page_Load(object sender, EventArgs e){
        if (!this.IsPostBack){}
        Bind_Gridview();
    }
 
    public void Populate_DynamicFooter(GridView gv)
    {
        DataTable dt = Populate_Datatable();
        for (int i = 0; i < dt.Columns.Count; i++)
        {
            if (i > 0){
                TextBox txtb = new TextBox
                {
                    ID = "txt" + dt.Columns[i].ColumnName,
                    Text = Convert.ToString(dt.Rows[0][i])
                };
                gv.FooterRow.Cells[i].Controls.Add(txtb);
            }
        }
    }
    public void Populate_DynamicFooter1(GridViewRow gr)
    {
        for (int i = 0; i < gr.Cells.Count; i++)
        {
            if (i > 0)
            {
                TextBox txtb = new TextBox
                {
                    ID = "txt_" + i.ToString(),
                    Text = ""
                };
                gr.Cells[i].Controls.Add(txtb);
            }
        }  
    }
 
   
    public void Bind_Gridview()
    {
        DataTable dt = Populate_Datatable();
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }
    public DataTable Populate_Datatable()
    {
        DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[8] {
                        new DataColumn("Name", typeof(string)),
                        new DataColumn("Age",typeof(string)),
                        new DataColumn("Gender",typeof(string)),
                        new DataColumn("Country",typeof(string)) ,
                        new DataColumn("State",typeof(string)),
                        new DataColumn("District",typeof(string)),
                        new DataColumn("Block",typeof(string)),
                        new DataColumn("Village",typeof(string)),
 
 
        });
 
        dt.Rows.Add("Jai Hind Jai Bharat", "32", "Male", "India", "UP", "", "", "");
        dt.Rows.Add(2, "", "", "", "India", "UP", "", "");
        dt.Rows.Add(3, "", "", "", "India", "UP", "", "");
        dt.Rows.Add(4, "", "", "", "India", "UP", "", "");
        dt.Rows.Add(5, "", "", "", "India", "UP", "", "");
        return dt;
 
    }
 
    protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.Footer)
        {
            Populate_DynamicFooter1( e.Row);
        }
    }
 
    protected void Click_ClickME(object sender, EventArgs e)
    {
        string ll = "";
        for (int i = 0; i < GridView1.FooterRow.Cells.Count; i++)
        {
            if (i > 0)
            {
                TextBox txtb = ((TextBox)GridView1.FooterRow.Cells[i].FindControl("txt_" + i.ToString()));
                if (txtb != null)
                {
                    ll = ll + (ll.Length > 0 ? "," : "") + txtb.Text;
                }
            }
        }
        ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert('" + ll + "')", true);
    }
 
    protected void CLick_EditCLick(object sender, EventArgs e)
    {
        LinkButton b = (sender) as LinkButton;
        GridViewRow gr = ((GridViewRow)b.NamingContainer);
        int idx = gr.RowIndex;
        for (int i = 0; i < GridView1.FooterRow.Cells.Count; i++)
        {
          string stst=  GridView1.Rows[idx].Cells[i].Text;
            TextBox txtb = ((TextBox)GridView1.FooterRow.Cells[i].FindControl("txt_" + i.ToString()));
            if (txtb != null)
            {
                txtb.Text = stst;
            }
        }
    }


///////// Result

Order the result of a comma separated string generated with STUFF in sql server

 Sql query

DECLARE @Cols nvarchar(max);

SET @Cols = STUFF((SELECT ', ' + QUOTENAME(para_name) FROM t_WQ_Cont_list ORDER BY rpt_seq FOR XML PATH(''), TYPE).value('.', 'nvarchar(MAX)'),1,1,'')

SELECT @Cols;

///////// Result

 [Cl], [EC], [TDS], [Fe], [pH], [SO4]

Get input value from the table using class name in jquery

 

//////// Assign Value

$("input[name^='news']").val("news here!");

////////// Filter

  $('#ControlID').find('input:not("button")').filter(

      function () {

        return $(this).hasClass('className')

  });

///////////////////// Get val

$('#ControlID tr').find('input[class="ClassName"]').val()

Create, save and download excel file using String-Builder asp.net c#

 

public void Create_Save_Download_Excel()

    {

        DataTable dt = new DataTable();

        dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Col1", typeof(string)),

                            new DataColumn("Col2", typeof(string)),

                            new DataColumn("Col3",typeof(string))});

 

        dt.Rows.Add("1", "TestA", "A");

        dt.Rows.Add("2", "TestB", "B");

        dt.Rows.Add("3", "TestB", "C");

        dt.Rows.Add("4", "TestB", "D");

 

        StringBuilder sb = new StringBuilder();

        sb.Append("<html >");

        sb.Append("<head>");

        sb.Append("</head>");

        sb.Append("<body>");

        sb.Append("<table border='1px' cellpadding='1' cellspacing='1' bgcolor='lightyellow' style='font-family:Arial; font-size:large'>");

        sb.Append("<tr >");

        foreach (DataColumn myColumn in dt.Columns)

        {

            sb.Append("<td >");

            sb.Append(myColumn.ColumnName);

            sb.Append("</td>");

        }

        sb.Append("</tr>");

        foreach (DataRow myRow in dt.Rows)

        {

            sb.Append("<tr >");

            foreach (DataColumn myColumn in dt.Columns)

            {

                sb.Append("<td >");

                sb.Append(myRow[myColumn.ColumnName].ToString());

                sb.Append("</td>");

            }

            sb.Append("</tr>");

        }

        //Close tags. 

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

        sb.Append("</body>");

        sb.Append("</html>");

        string Htmltext = sb.ToString();

 

        string CopyTxt = Htmltext;

        ////////////////// Save Data on specific location

 

        string filePath = Server.MapPath("~/Import/");

        if (!System.IO.Directory.Exists(filePath))

        {

            System.IO.Directory.CreateDirectory(filePath);

        }

        string fileName = "Jaiho_"+System.DateTime.Now.ToString("yyyyMMddhhmmss")+".xls";

        System.IO.File.WriteAllText(filePath + fileName, CopyTxt);

 

 

        ////////////////// Download data

 

        try

        {

            string strcontentType = "application/excel";

            Response.ClearContent();

            Response.ClearHeaders();

            Response.BufferOutput = true;

            Response.ContentType = strcontentType;

            Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);

            Response.Write(Htmltext.ToString());

            Response.Flush();

           // Response.Close();

           // Response.End();

        }

        catch (Exception ex)

        {

            string msg = ex.Message;

        }  

    }