Get HTML code from a website C#

string urlAddress = "http://vfet.no-ip.biz:9211/" + (HFHTML.Value.Remove(0, 2));

HttpWebRequest request = (HttpWebRequest)WebRequest.Create(urlAddress);
HttpWebResponse response = (HttpWebResponse)request.GetResponse();

if (response.StatusCode == HttpStatusCode.OK)
{
Stream receiveStream = response.GetResponseStream();
StreamReader readStream = null;

if (response.CharacterSet == null)
{
readStream = new StreamReader(receiveStream);
}
else
{
readStream = new StreamReader(receiveStream, Encoding.GetEncoding(response.CharacterSet));
}

data = readStream.ReadToEnd();
response.Close();
readStream.Close();
}
mail.Body = data;

SQL Wildcards

SQL wildcards can substitute for one or more characters when searching for data in a database.

SQL wildcards must be used with the SQL LIKE operator.

With SQL, the following wildcards can be used:

Wildcard	Description
%	A substitute for zero or more characters
_	A substitute for exactly one character
[charlist]	Any single character in charlist
[^charlist] or [!charlist]	Any single character not in charlist

---

SQL Wildcard Examples

We have the following "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

---

Using the % Wildcard

Now we want to select the persons living in a city that starts with "sa" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE City LIKE 'sa%'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes

Next, we want to select the persons living in a city that contains the pattern "nes" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE City LIKE '%nes%'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes

---

Using the _ Wildcard

Now we want to select the persons with a first name that starts with any character, followed by "la" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE FirstName LIKE '_la'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes

Next, we want to select the persons with a last name that starts with "S", followed by any character, followed by "end", followed by any character, followed by "on" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE LastName LIKE 'S_end_on'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
2	Svendson	Tove	Borgvn 23	Sandnes

---

Using the [charlist] Wildcard

Now we want to select the persons with a last name that starts with "b" or "s" or "p" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE LastName LIKE '[bsp]%'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

Next, we want to select the persons with a last name that do not start with "b" or "s" or "p" from the "Persons" table.

We use the following SELECT statement:

SELECT * FROM Persons
WHERE LastName LIKE '[!bsp]%'

The result-set will look like this:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes

Rolling up multiple rows into a single row and column for SQL Server data

If you want all of the data concatenated into a single column in a single row. In this tip we look at a simple approach to accomplish this.

 we can use the FOR XML PATH option to return the results as an XML string which will put all of the data into one row and one column.

SELECT PatFullName FROM IE_Patients
FOR XML PATH('')

STUFF function in SQL Server

The SQL Server (Transact-SQL) STUFF function deletes a sequence of characters from a source string and then inserts another sequence of characters into the source string, starting at a specified position.

Syntax:

The syntax for the STUFF function in SQL Server (Transact-SQL) is:

STUFF( source_string, start, length, add_string )

Example:

SELECT STUFF('C#Infrasolution', 8, 2, '1234');
Result: 'C#Infra1234tion'

SQL SERVER – Computed Column – PERSISTED and Performance

A computed column is a virtual column that is not physically stored in the table, unless the column is marked PERSISTED. A computed column expression can use data from other columns to calculate a value for the column to which it belongs.

There are two types of computed columns namely persisted and non-persisted.

1. Non-persisted columns are calculated on the fly (ie when the SELECT query is executed) whereas persisted columns are calculated as soon as data is stored in the table.

2. Non-persisted columns do not consume any space as they are calculated only when you SELECT the column. Persisted columns consume space for the data

Example:

Duration Calculator Between Two Dates Using ASP.Net C#

if (txt_LeaveFrom.Text != "" && txt_LeaveTo.Text != "")  

{      

DateTime FromYear = Convert.ToDateTime(clsObjCommon.GetDateForDB(txt_LeaveFrom.Text));

DateTime ToYear = Convert.ToDateTime(clsObjCommon.GetDateForDB(txt_LeaveTo.Text));

//Creating object of TimeSpan Class  

TimeSpan objTimeSpan = ToYear - FromYear;

//years  

int Years = ToYear.Year - FromYear.Year;

//months  

int month = ToYear.Month - FromYear.Month;

//TotalDays  

double Days = Convert.ToDouble(objTimeSpan.TotalDays);

//Total Months  

int TotalMonths = (Years * 12) + month;

//Total Hours  

double TotalHours = objTimeSpan.TotalHours;

//Total Minutes  

double TotalMinutes = objTimeSpan.TotalMinutes;

//Total Seconds  

double TotalSeconds = objTimeSpan.TotalSeconds;

//Total Mile Seconds  

double TotalMileSeconds = objTimeSpan.TotalMilliseconds;

} 

Difference between stored procedure and function

1) Procedure can return zero or n values whereas function can return one value which is mandatory.

2) Procedures can have input, output parameters for it whereas functions can have only input parameters.

3) Procedure allows select as well as DML statement in it whereas function allows only select statement in it.

4) Functions can be called from procedure whereas procedures cannot be called from function.

5) Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.

6) We can go for transaction management in procedure whereas we can't go in function.

7) Procedures cannot be utilized in a select statement whereas function can be embedded in a select statement.

What is the difference between UNION and UNION ALL ?

Both UNION and UNION ALL is used to select information from structurally similar tables. That means corresponding columns specified in the union should have same data type. For example, in the above query, if FIRST_NAME is DOUBLE and LAST_NAME is STRING above query wont work. Since the data type of both the columns are VARCHAR, union is made possible. Difference between UNION and UNION ALL is that , UNION query return only distinct values. 

How to validate field in asp.net using Javascript

Write in header section of page

<script type="text/javascript">

        function ValidateFiled() {

if (document.getElementById("<%= txt_LeaveNOD.ClientID  %>").value == "") {

                document.getElementById("<%= lbl_Msg.ClientID  %>").innerHTML = "Please Enter Number of Days...!";

                return false;

            }

}

    </script>

Write in body section of page

<asp:Button ID="btn_Save" runat="server" Text="Save" Width="15%" OnClientClick="return ValidateFiled();"/>

How to clear data in asp.net using Javascript

Write in header section of page

 

<script type="text/javascript">

        function funClearField() {

            document.getElementById("<%= txt_DwL_ID.ClientID  %>").value = "";

            document.getElementById("<%= txt_Designation.ClientID  %>").value = "";

            document.getElementById("<%= txt_LeaveName.ClientID  %>").value = "";

            document.getElementById("<%= txt_LeaveNOD.ClientID  %>").value = "";

            document.getElementById("<%= lbl_Msg.ClientID  %>").innerHTML = "";

            return false;

        }

    </script>

Write in body section of page

<asp:Button ID="btn_Reset" runat="server" Text="Reset" Width="15%"  OnClientClick="return funClearField();"/>

How to import data from excel in Asp.net

Call this function:-

private void ImportExcel(Label lbl)

    {

        string connectionString = "";

        string strFileType = Path.GetExtension(fu_excel_N.FileName).ToLower();

        string path = fu_excel_N.PostedFile.FileName;

        if (strFileType.Trim() == ".xlsx")

        {

            connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FileToConvert + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";

        }

        try

        {

            OleDbConnection connection = new OleDbConnection(connectionString);

            if (connection.State == ConnectionState.Closed)

                connection.Open();

            OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connection);

            DataSet ds = new DataSet();

            DataTable dt = new DataTable();

            adapter.Fill(ds);

            dt = ds.Tables[0];

            System.Web.HttpContext.Current.Session["ImportExcel"] = dt;

            connection.Close();

            int number_of_columns = dt.Columns.Count;

            string[] columnNames = new string[number_of_columns];

            for (int j = 0; j < number_of_columns; j++)

            {

                columnNames[j] = dt.Columns[j].ToString();

            }

           

        }

        catch (Exception ex)

        {

            lbl.Text = "Error loading file !!!, Make sure that the file is in '.xlsx' format";

            lbl.Visible = true;

            lbl.ForeColor = System.Drawing.Color.Red;

            return;

        }

    }

How to Expot data Asp.net to Excel

    protected void btnExcel_Click(object sender, EventArgs e)
    {
        try
        {
            Export();
        }
        catch (Exception)
        {
        }
    }
    private void Export()
    {
        Response.Clear();
        Response.AddHeader("content-disposition", "attachment; filename=DepartmentWiseReportOPD.xls");
        Response.Charset = "";
        Response.ContentType = "application/vnd.xls";
        System.IO.StringWriter stringWrite = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
        dynamicDiv.RenderControl(htmlWrite);
        Response.Write(stringWrite.ToString());
        Response.End();
    }

How to get the repeated record in Table?

if u want the get number of repeated record in your table, write this query:-

  IE_Visitdetail

Visit_ID	PatID	Pat_name	VisitDate	ConsultantName

select Visit_ID from IE_Visitdetail group by Visit_ID having count(Visit_ID)>1