Learn C# ASP.NET MVC WCF SQL Angular

Tuesday, March 29, 2016

Comment Box in ASP.NET C#

In this article I am going to explain how to write standard code to create comment box in ASP.NET c#.

Create a SQL table for insert comment

CREATE TABLE InsComment (

CommentId BIGINT identity(1, 1)

,NAME VARCHAR(50)

,Comment VARCHAR(500)

)

Now I am writing a XSLT file code to display comment after Save (Create a XSLT fie in "XSLT" floder with name "History.xslt")

Solution Explorer look like

<?xml version="1.0" encoding="utf-8"?>

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="/">

<xsl:for-each select="Comments/Comment">

</div>

<div style="font-weight:bold; float:left; width:100px;">Comment:</div>

</div>

</div>

</xsl:for-each>

</xsl:template>

</xsl:stylesheet>

Default.aspx Code

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

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

.drp

{

background: #F4F4F4;

border: 1px solid #DEDCDD;

color: #555555;

height: 25px;

padding-left: 10px;

margin-bottom:10px;

-moz-transition: all 0.3s linear;

-o-transition: all 0.3s linear;

-webkit-transition: all 0.3s linear;

transition: all 0.3s linear;

font-family: Arial, Helvetica, sans-serif;

font-size: 12px;

}

.drp:focus { outline:none; border:1px solid #F90; }

.med-form-col-1-8 { width:12.5%; }

.med-form-col-2-8 { width:25%; }

.med-form-col-3-8 { width:37.5%; }

.med-form-col-4-8 { width:50%; }

.med-form-col-5-8 { width:62.5%; }

.med-form-col-6-8 { width:75%; }

.med-form-col-7-8 { width:87.5%; }

.med-form-col-8-8 { width:100%; }

.med-form-col-1-8,

.med-form-col-2-8,

.med-form-col-3-8,

.med-form-col-4-8,

.med-form-col-5-8,

.med-form-col-6-8,

.med-form-col-7-8,

.med-form-col-8-8

{

float:left;

padding:1px 10px;

box-sizing: border-box;

-moz-box-sizing: border-box;

-ms-box-sizing: border-box;

-webkit-box-sizing: border-box;

-khtml-box-sizing: border-box;

}

.med-form-col-1-8 label,

.med-form-col-2-8 label,

.med-form-col-3-8 label,

.med-form-col-4-8 label,

.med-form-col-5-8 label,

.med-form-col-6-8 label,

.med-form-col-7-8 label,

.med-form-col-8-8 label,

{

display:block !important;

width:100% !important;

margin:0 !important;

float:none !important;

}

.med-form-item.large, .med-form-item.large .drp { width:100%; }

.med-form-item.medium, .med-form-item.medium .drp { width:75%; }

.med-form-item.small, .med-form-item.small .drp { width:55%; }

.frmlbl { color: #50505b; padding: 0px 10px 0px 0px; font-size: 14px; font-weight:400; !important }

.clr { clear:both; }

@media only screen and (max-width:600px) {

.med-form-col-1-8,

.med-form-col-2-8,

.med-form-col-3-8,

.med-form-col-4-8,

.med-form-col-5-8,

.med-form-col-6-8,

.med-form-col-7-8,

.med-form-col-8-8

{

display:block !important;

width:100% !important;

margin:0 !important;

float:none !important;

}

.btn { height: 50px; margin: 0; padding: 0 20px; vertical-align: middle; background: #9c9c9c; border: 0; font-family: 'Raleway', sans-serif; font-size: 16px; font-weight: 300; line-height: 50px; color: #fff; -moz-border-radius: 4px; -webkit-border-radius: 4px; border-radius: 4px; text-shadow: none; -moz-box-shadow: none; -webkit-box-shadow: none; box-shadow: none; -o-transition: all .3s; -moz-transition: all .3s; -webkit-transition: all .3s; -ms-transition: all .3s; transition: all .3s; }

</style>

function Enablebutton(val) {

if (val.value.trim() != "") {

document.getElementById('#btnsavecomment').disabled = false;

$('#btnsavecomment').attr("style", "background-color:Yellow");

}

else {

document.getElementById('#btnsavecomment').disabled = true;

$('#btnsavecomment').removeAttr("style");

}

</script>

</head>

<body>

<legend>Comment</legend>

<asp:LinkButton ID="btnlink" runat="server" Text="View all" onclick="btnlink_Click" ></asp:LinkButton>

<asp:LinkButton ID="btnlinktop" runat="server" Text="Last one" onclick="btnlinktop_Click"></asp:LinkButton>

</label>

<asp:Xml ID="xmlContacts" runat="server"></asp:Xml>

<asp:TextBox ID="txtcomment" onkeyup="Enablebutton(this);" runat="server" MaxLength="500" Height="80" CssClass="drp" TextMode="MultiLine">

</asp:TextBox>

</div>

</div>

<asp:Label ID="lblmsg" runat="server"></asp:Label>

<asp:Button ID="btnsavecomment" OnClick="btnsavecomment_Click" runat="server" Text="Post comment" CssClass="btn btn-primary" />

</div>

</fieldset>

</form>

</body>

</html>

Default.aspx.cs code

using System;

using System.Linq;

using System.Data;

using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page

{

string constr = "Data Source=.;Initial Catalog=Test;uid=******;pwd=**********";

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

Getcomments();

}

private string Getxml(DataTable dt)

{

DataTable dtclone = dt.Clone();

foreach (DataColumn column in dtclone.Columns)

{

column.DataType = typeof(string);

}

foreach (DataRow row in dt.Rows)

{

dtclone.ImportRow(row);

}

foreach (DataRow row in dtclone.Rows)

{

for (int i = 0; i < dtclone.Columns.Count; i++)

{

dtclone.Columns[i].ReadOnly = false;

if (string.IsNullOrEmpty(row[i].ToString()))

{

row[i] = string.Empty;

}

else

{

row[i] = Convert.ToString(row[i]).Trim();

}

DataSet dsclone = new DataSet();

dsclone.Tables.Add(dtclone);

dsclone.DataSetName = "Comments";

dsclone.Tables[0].TableName = "Comment";

return dsclone.GetXml();

}

private void Getcomments(string loadtype = "L")

{

SqlDataAdapter da = new SqlDataAdapter("select CommentId,NAME,Comment from InsComment",constr);

DataSet ds = new DataSet();

da.Fill(ds);

if (ds != null)

{

if (ds.Tables.Count > 0)

{

if (ds.Tables[0].Rows.Count > 0)

{

lbltotcomment.Text = "Comments " + Convert.ToString(ds.Tables[0].Rows.Count);

var dt = ds.Tables[0];

if (loadtype == "L")

{

dt= ds.Tables[0].AsEnumerable()

.Where((r, i) => i >= dt.Rows.Count-1).CopyToDataTable();

}

string xml = Getxml(dt);

btnlink.Visible = true;

btnlinktop.Visible = true;

xmlContacts.DocumentContent = xml;

xmlContacts.TransformSource = Server.MapPath("~/XSLT/History.xslt");

}

else

{

lbltotcomment.Text = "Comments 0";

btnlink.Visible = false;

btnlinktop.Visible = false;

}

else

{

lbltotcomment.Text = "Comments 0";

btnlink.Visible = false;

}

protected void btnsavecomment_Click(object sender, EventArgs e)

{

try

{

using (SqlConnection connection = new SqlConnection(constr))

{

connection.Open();

SqlCommand myCommand = new SqlCommand("Insert into InsComment (NAME,Comment) values ('Varun',@Comment)", connection);

myCommand.Parameters.Add("@Comment", SqlDbType.VarChar, 500);

myCommand.Parameters["@Comment"].Value = txtcomment.Text.Trim();

myCommand.CommandType = CommandType.Text;

int res = myCommand.ExecuteNonQuery();

connection.Close();

if (res > 0)

{

txtcomment.Text = string.Empty;

Getcomments();

lblmsg.Text = "Save Successfully";

}

else

{

lblmsg.Text = "Data Not Saved";

}

catch (Exception ex)

{

lblmsg.Text = ex.Message.ToString();

}

protected void btnlinktop_Click(object sender, EventArgs e)

{

Getcomments();

btnlinktop.Enabled = false;

btnlink.Enabled = true;

}

protected void btnlink_Click(object sender, EventArgs e)

{

Getcomments("A");

btnlinktop.Enabled = true;

btnlink.Enabled = false;

}

OutPut

Check Uncheck all CheckBoxes in ASP.Net GridView using Javascript

In this article I am going to explain how to check and unchecked all check-box in asp.net grid view using JavaScript function

Code to check uncheck checkboxes

function SelectAllCheckboxes(chk) {

$('#<%=TypeHeregidviewname.ClientID%>').find("input[id*='chkitem']:checkbox").each(function () {

if (this != chk) {

this.checked = chk.checked;

}

});

}

</script>

Code of Default..aspx

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

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

function SelectAllCheckboxes(chk) {

$('#<%=gvselectall.ClientID%>').find("input[id*='chkitem']:checkbox").each(function () {

if (this != chk) {

this.checked = chk.checked;

}

});

}

</script>

</head>

<body>

<asp:GridView ID="gvselectall" runat="server" AutoGenerateColumns="false">

<asp:TemplateField>

<asp:CheckBox ID="chkheader" runat="server" onclick="SelectAllCheckboxes(this);" />

</HeaderTemplate>

<asp:CheckBox ID="chkitem" runat="server" />

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Name">

<asp:Label ID="lblname" runat="server" Text='<%#Eval("name") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

<asp:TemplateField HeaderText="Gender">

<asp:Label ID="lblgender" runat="server" Text='<%#Eval("gender") %>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

</Columns>

</asp:GridView>

</form>

</body>

</html>

Code of Default..aspx.cs

using System;

using System.Data;

public partial class _Default : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

DataTable dt = new DataTable();

dt.Columns.Add("name");

dt.Columns.Add("gender");

DataRow rr = dt.NewRow();

rr["name"] = "Sharad";

rr["gender"] = "Male";

dt.Rows.Add(rr);

DataRow rr1 = dt.NewRow();

rr1["name"] = "Varun";

rr1["gender"] = "Male";

dt.Rows.Add(rr1);

DataRow rr2 = dt.NewRow();

rr2["name"] = "Devesh";

rr2["gender"] = "Male";

dt.Rows.Add(rr2);

DataRow rr3 = dt.NewRow();

rr3["name"] = "Sudhir";

rr3["gender"] = "Male";

dt.Rows.Add(rr3);

DataRow rr4 = dt.NewRow();

rr4["name"] = "Swati";

rr4["gender"] = "Female";

dt.Rows.Add(rr4);

gvselectall.DataSource = dt;

gvselectall.DataBind();

}

Output

After Click on header check-box

Monday, March 28, 2016

Validate strong password using RegularExpressionValidator

In this article I am going to explain how to validate a user for entering strong password.

Validation Expression

ValidationExpression="^(?=.*[A-Za-z])(?=.*\d)(?=.*[$@$!%*#?&])[A-Za-z\d$@$!%*#?&]{8,16}$"

Note

Must have at least a minimum of (8 characters) and not more than a maximum of (16 characters) as the length.
Must have 1 numeric digits.
Must have 1 alphabet and 1 Special characters like {! @ # $ % ^ & *}

Code

<asp:TextBox ID="txtpassword" TextMode="Password" runat="server" ></asp:TextBox>

<asp:RequiredFieldValidator ID="rfvtxtpassword" ValidationGroup="S" ControlToValidate="txtpassword" runat="server" ErrorMessage="Please Enter User Name"></asp:RequiredFieldValidator>

<asp:RegularExpressionValidator ID="Regtxtpassword" runat="server" ValidationGroup="S"

Display="None" ErrorMessage="Password Message" ControlToValidate="txtpassword"

ValidationExpression="^(?=.*[A-Za-z])(?=.*\d)(?=.*[$@$!%*#?&])[A-Za-z\d$@$!%*#?&]{8,16}$"></asp:RegularExpressionValidator>

for 
RegularExpressionValidator 
download ajaxtoolkit

Sunday, March 20, 2016

String function in SQL

In the last article, I was explain about

Arithmetic functions

Now In this article I am going to explain string function in SQL.

String Functions

The following are the string functions.

ASCII (s)

It returns the ASCII value of the left character in the given string.

Example

select ASCII ('A')              Ans: 65
select ASCII (.ABC')        Ans: 65

Char (n)

It returns the character representing the given ASCII value.

Example

select Char (66) Ans: B

Lower (str)

It converts string into lower case.

Example

select Lower ('SHARAD') Ans: sharad

Upper (str)

It converts string into upper case.

Example
select Upper ('sharad') Ans: SHARAD

Len (str)

It returns the length of the given string.

Example

select Len (‘Sharad’) Ans: 6

Left (str, n)

It returns specified 'n' no. of characters from the left side of the given string.

Example

select Left ('Sharad', 4) Ans: Shar
select * from Emp where Left (ename, 3) Ans: Sha

Right (str, n)

It returns specified n no. of characters from the right side of the given string.

Example

select Right ('Sharad', 4) Ans: arad

Ques Write a query to get a list of employees whose names end with the character 'rd'?
Ans select * from Emp where Right (ename, 2)='RD'

Ques Write a query to get a details of employees whose names 3rd & 4th characters are 'IT'?
Ans select * from Emp where Right (Left (ename), 4), 2)='IT'

Substring (s, START, length)

It returns a part of the string from the given string 's', From the specified start character and no. of characters specified as length.
Example

select Substring ('Hello', 1, 3) Ans Hel
select Substring ('Hello', 2, 3) Ans ell

Ques Write a query to get a details of employees whose names 3rd & 4th characters are 'IT'?
Ans select * from Emp where Substring (ename, 3, 2)='IT'

Reverse (str)

It returns the given string in the reverse order.

Example
select Reverse ('shri') Ans: irhs

LTRIM (str)

It returns a string length eliminating any empty characters if present in the left side of the given string.

Example

select Len ('..shar')      Ans: 6
select LTRIM ('..shar') Ans: 4

RTRIM (str)

It returns a string length eliminating any empty characters if present in the right side of the given string.

Example

select Len ('Shar..')     Ans: 4
select RTRIM ('Shar..') Ans: 4

NOTE

RTRIM (str) function is not much useful because any empty characters in the end of the string is not considered.

Replace (str, search, replace)

It returns a new string by replacing each occurrences of the ‘search’ character with ‘replace’ character in the given string 'str'.

Example

select Replace ('Hello', 'L', 'K') Ans: HeKKo

Ques Write a select statement which retrieves the details of the customers whose name is 'SHARAD GUPTA'?
Ans select * from Emp where Replace (ename, ' ', '') Ans: SHARADGUPTA

CharIndex (search, str [,start]

It is used for finding the index position of the search character within the given string 's'.

Example
select CharIndex ('E', 'Hello')                 Ans: 2
select CharIndex ('o', 'Hello World')     Ans: 5
select CharIndex ('o', 'Hello World', 6) Ans: 8

NOTE

1 In the above case even if the string contains multiple ‘o’ characters in it, it always search and returns the index of the first character because the search starts from the first character.
2 'Start' is used for specifying the location from where the search has to be started.
select CharIndex ('X', 'Hello') Ans: 0

Ques Write a query to get the list of all the employees whose name contains character 'M' in them?
Ans select * from Emp where CharIndex ('M', ename) > 0

STUFF (str, start, length, Replace)

It replaces the given string with the Replace string from the specified starting character to the no. of characters specified as length.

Example

select STUFF (‘AXXBXXC’, 2, 2, ‘ZZ’)             Ans: AZZBXXC
select STUFF (‘AXXBXXC’, 2, 2, ‘MNO’)        Ans: AMNOBXXC

SOUNDEX (str)

The function returns a 4 digit alphanumeric string for each given string value.

Example
select Soundex ('color')                    Ans: C460
select Soundex ('colour')                 Ans: C460

1 It performs the calculation in the way the strings are sounded (or) pronounced.
2 We can make use of this function to perform comparisons between two strings which are sounded in the same way but has different spelling.

Like

select * from Emp where Soundex (ename) = Soundex ('Smyth')

NOTE

While applying the Soundex function it has to be applied on both sides of the
condition.

Friday, March 18, 2016

Function in SQL

These are provided for retrieving the information from the tables in various scenarios. They are 2 types

Single Row Functions
Group function

Single Row Functions

These functions execute once for each row that is present in the table i.e. if the table has '0' row it execute 'zero' times or 'n' rows it execute 'n' times.

Group function

These functions take a Group of rows into consideration & returns a single value a single value as an O/P i.e. if the table contains 'zero' or 'n' row it executes once & returns an O/P.
A function can be used in 2 different ways

Columns list of a select statement : like select name, len (ename) from Emp
Condition of a select statement : like select * from Emp where len (name) = 5

Syntax of calling function

Select <function name> ([argument list]) [from <Tname> <condition>]

Note
From is optional if the argument does not contain any column name in it.
Example

Select len ('sharad')
Select len (ename) from Emp

Single Row Functions

Arithmetic functions
String functions
Date functions
Conversion functions
System function

Arithmetic Functions

The following are the arithmetic function

ABS (n)

It returns the absolute value of the given n

Example:

1 select ABS (15)   Ans: 15
2 select ABS (-15) Ans: 15

Sign(n)

It returns 1 if the given n is > 0 (or)
It returns 0 (zero) if the given n is = 0 (or)
It returns -1 if the given n is < 0.

Example

select sign (100)       Ans: 1
select sign (0)           Ans: 0
select sign (-100)     Ans: -1

Ceiling (n)

It returns the least integer that is greater than the given n.

Example

select Ceiling (15.3) Ans: 16
select Ceiling (-15.3) Ans: -15

Floor (n)

It returns the highest integer less than the given number n.

Example

select Floor (15.6) Ans: -15
select Floor (-15.6) Ans: -16

Round (n, size [,funValue]

It returns the given n value rounded with specified decimals as size.

Example

select Round (156.678, 2) Ans 156.68
select Round (156.678, 0) Ans 157
select Round (156.678,-1) Ans 160
select Round (156.678,-3) Ans 0

The size can be negative value also. If it is negative, the rounding is performed before the decimal.

The function value which is an optional parameter can be anything greater than '0' If it is used the function behaves as Truncate. So it will cut if the values but will not round them.

Example

select Round (156.678, 2, 1) Ans 156.67
select Round (156.678,-1, 1) Ans 150
select Round (156.678,-2, 1) Ans 100

PI ()
It returns the constant value of PI.

Example

select Pi () Ans: 3.14159263
select Round (Pi (), 2) Ans: 3.14

Power (n, m)

It returns the value of n power m.

Example

select Power (2,3) Ans: 8
select Power (3,4) Ans: 81

Square (n)

It returns the square of the given 'n'.

Example

select Square (2) Ans: 4

Sqrt (n)

It returns the square root of the given 'n'.

Example

select Sqrt (4) Ans: 2

Log (n)

It returns the natural logarithmic value of the given 'n' i.e. base 'e'

Example

select Log (10)
select Log10 (n) Ans: It returns the logarithmic value of given ‘n’ to base 10.

Rand ([seed])

It returns a random no. ranging between 0.0 to 1 for each time it is executed.

Example

select Rand () Ans: 0.8077, 0.5377, etc.
select Rand () * 50 Ans: 42.172 ……….upto 50

If we want to generate a random no. between 1 and specified upper limit, multiply the function with the upper limit value and round it as following.

Example

select Rand () * 50
select Round (Rand () * 50, 0)
The optional parameter seed can be used for repeating the value one more time because for a given seed if always returns the same value. If the seed changes the value also changes.

NOTE
The Arithmetic functions provide with all the trigonometric functions like Sin, Cos, Tan, etc for which we need to provide the degrees as 'n' value.

In the next article we will discuss about string function.