Tuesday, 20 January 2015

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;

Saturday, 17 January 2015

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

Sunday, 11 January 2015

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'

Saturday, 10 January 2015

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:

Wednesday, 7 January 2015

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. 

Tuesday, 6 January 2015

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