sql syntaxes

Select Statement
SELECT "column_name" FROM "table_name"

Distinct
SELECT DISTINCT "column_name" FROM "table_name"

Where
SELECT "column_name" FROM "table_name" WHERE "condition"

And/Or
SELECT "column_name" FROM "table_name" WHERE "simple condition" {[AND|OR] "simple condition"}+

In
SELECT "column_name" FROM "table_name" WHERE "column_name" IN ('value1', 'value2', ...)

Between
SELECT "column_name" FROM "table_name" WHERE "column_name" BETWEEN 'value1' AND 'value2'

Like
SELECT "column_name" FROM "table_name" WHERE "column_name" LIKE {PATTERN}

Order By
SELECT "column_name" FROM "table_name" [WHERE "condition"] ORDER BY "column_name" [ASC,DESC]

Count
SELECT COUNT("column_name") FROM "table_name"

Group By
SELECT "column_name1", SUM("column_name2") FROM "table_name" GROUP BY "column_name1"

Having
SELECT "column_name1", SUM("column_name2") FROM "table_name" GROUP BY "column_name1" HAVING (arithematic function condition)

Create Table Statement
CREATE TABLE "table_name" ("column 1" "data_type_column_1", "column 2" "data_type_for_column_2",.... )

Drop Table Statement
DROP TABLE "table_name"

Truncate Table Statement
TRUNCATE TABLE "table_name"

Insert Into Statement
INSERT INTO "table_name" ("column1", "column2", ...) VALUES ("value1", "value2", ...)

Update Statement
UPDATE "table_name" SET "column_1" = [new value] WHERE {condition}

Delete From Statement
DELETE FROM "table_name" WHERE {condition}

select--scalar--string, update,insert,delete--nonquery--int

Ref: 

http://www.1keydata.com/sql/sql-syntax.html

http://www.sqlcommands.net/

C# Features not in Java

•No automatic fall-through from one case block to the next

•Strongly-typed enums

•By reference calls are explicit at caller AND callee

•Method overrides are explicit

•Supports versioning

•Structs (value types)

•Integrated support for properties and events

•Can still use pointers with RAD language

Can share data and use functionality with components written in many different languages

Development tools and Documentation

—Server-side is well supported by both Java and .NET IDEs

—On the client-side .NET IDEs benefit from the fact that .NET CF is so close to .NET  (With Java there are separate IDEs for desktop and mobile application development)

—Compatibility problems between Java vendors

—Java IDEs are slow!

—C# is a richer/more complex language than Java

—Both Java and .NET have well documented API

—Web service documentation

—.NET - MSDN

—Java – Google

—Support for encryption of web services

—.Net CF: HTTPS and SOAP extensions

J2ME: HTTPS, but only in CDC & MIDP 2.0

C# and JAVA

CSharp and JAVA are two different Object Oriented Languages , both have some similarities and differences also . The Csharp and JAVA derived from their own single ancestor Class "Object". All Classes in C# are descended from System.Object Class and in JAVA all classes are subclasses of java.lang.Object Class.

Both C# and JAVA have their own runtime environments . C# source codes are compiled to Microsoft Intermediate Language (MSIL) and during the execution time runs it with the help of runtime environments - Common Language Runtime (CLR). Like that JAVA source codes are compiled to Java Byte Code and during the execution time runs it with the help of runtime environments - Java Virtual Machine (JVM). Both CSharp and JAVA supports native compilation via Just In Time compilers.

More over both C# and JAVA have their own Garbage Collector. In the case of keywords comparison some keywords are similar as well as some keywords are different also. The following are the examples of few similar and different keywords.

Similar Keywords examples

class , new , if , case , for , do , while , continue , int , char , double , null

 

joins example

table1                                  table2

col1 col2                         col3   col4

 1      a                              1       a

 2      b                              2       b

null    c                              3       c

 4     null                           null     d

inner join result:

col1 col2 col3 col4

 1      a     1      a

 2      b     2      b

left outer join result:

col1 col2 col3 col4

  1      a     1     a

  2      b      2    b

 null    c    null  null

  4     null  null  null

full outer join result: 

col1 col2 col3 col4

  1      a     1     a

  2      b      2    b

  null    c    null  null

   4     null  null  null

 null    null    3    c

 null    null   null  d

ddl in js

opt=document.createElement("OPTION");
document.getElementById('ddlPages').options.add(opt);
opt.text=cnt-1;
opt.value=cnt-1;

how to find server/system name from sqlserver name using query

select host_name()

querystring n gv rows in javascript

query string in js: '<%=Request.QueryString["val"]%>';
--------------------------
gridrows in js: Totalrows = parseInt('<%= this.gv1.Rows.Count %>');
--------------------------
var rows = document.getElementById('gv').rows;
for(i=0;i <rows.length;i++)
{
   fd = rows[i].cells[4].innerText;
   rows[i].cells[4].style.display="none";
   rows[i].cells[8].style.display="none";
}

------------------------------------
<asp:Label ID="lblSno" runat="server" Text="<%# Container.DataItemIndex + 1%>"  />    ------------> to get the serial no for the gridview rows after bind the data (take the control to item template field of the grid)

ws vs wcf

webservices uses XmlSerializer. A Web Service is programmable application logic accessible via standard Web protocols(Simple Object Access Protocol), which uses standards based technologies (XML for data description and HTTP for transport) to encode and transmit application data. consumers only need to understand how to send and receive SOAP messages (HTTP and XML). webservice can be hosted in iis and outside of iis. In Web service System.XML.Serialization is supported while in the WCF Service System.RunTime.Serialization is supported

wcf uses DataContractSerializer. Windows Communication Foundation (WCF) is a framework for building service-oriented applications. for secure applications, chat service that allows two people to communicate, to poll a service for the latest data feeds we use wcf. WCF is a replacement for all earlier web service technologies from Microsoft. An endpoint in WCF can be communicated with just as easily over SOAP/XML,TCP/binary,as this reduces the amount of new code needed.
WCF is flexible because its services can be hosted in different types of applications (IIS,WAS, Self-hosting, Managed Windows Service)

limits of xmlserializer:
----------------------
1.Only the public fields and properties of .NET Framework objects are translated into XML.
2.Hashtable cannot be serialized into XML.
3.Web Services can be accessed only over HTTP & it works in stateless environment

validate checkbox list using javascript

function valid()
{
var chkBoxList = document.getElementById('CheckBoxList11');
var chkBoxCount= chkBoxList.getElementsByTagName("input");
var k=0;
for(var i=0;i<chkBoxCount.length;i++)
{
if(chkBoxCount[i].checked)
k++;
}
if(k==0)
alert("select any item");
return false;
}

add js functions in .cs file in simple

Literal li = new Literal();
li.Text = "alert('hello');";
Page.Controls.Add(li);
------------------------------------
Page.ClientScript.RegisterClientScriptBlock(this.GetType(), "", jsfunction(), true);

Page.RegisterStartupScript("new","<script>alert('this is alert');</script>");

Page.ClientScript.RegisterStartupScript(this.GetType(), "", "alert('hai');", true);

Page.ClientScript.RegisterStartupScript(this.GetType(), "", jsfunction();, true);

Page.RegisterClientScriptBlock("a", "<script>jsfunction();</script>");

ScriptManager.RegisterClientScriptBlock(UpdatedatePanel1, this.GetType(), "Message", "Status(true);", true);

ScriptManager

.RegisterStartupScript(Page, Page.GetType(), Guid.NewGuid().ToString(), "$('#bd').attr('className', 'capse');", true);

Button.Attributes.Add("onclick", "return fnGet()");

for !ispostback:..

ClientScriptManager scm = this.ClientScript;
scm.RegisterStartupScript(this.GetType(), "onload", " __doPostBack('form1',null);setInterval(fncheck,10000);", true);
-------------------------------------
Response.Write("<script language="javascript">window.close();</script>");
Response.End();

check whether the given value is string or not

public static bool IsNumeric(String strVal)
{
Regex reg = new Regex("[^0-9-]");
Regex reg2 = new Regex("^-[0-9]+$|^[0-9]+$");
return (!reg.IsMatch(strVal) && reg2.IsMatch(strVal));
}
-----------------------------

isNaN(objnumber)

get files from a directory folder

string[] files = System.IO.Directory.GetFiles(serverpath + "/" + inputfolder);

filter data of dataset / datatable

ds.Tables[0].Select("Name LIKE %" + "abc%" );
------------------------------
DataView dv = ds.Tables[0].DefaultView;
dv.RowFilter = "lastname like '" + txtName.Text + "%'";
DataTable dt = dv.ToTable();
------------------------------
if (((DataRow[])dt.Select("dname="+newdnam)).Length > 0)
---------------------------------------------------------------
DataTable dt = (DataTable)Session["data"];
DataRow[] drow = dt.Select("id="+id+"");
Label1.Text = drow[0].ItemArray[1].ToString();
---------------------------------------------------------------
dt.Constraints.Clear();
dt.PrimaryKey = newDataColumn[] { dt.Columns["id"] };DataRow[] dr = dt.Rows.Find(val.ToString());

don't allow duplicates for a listbox

if (!ContactsListBox.Items.Contains(item))
{
ContactsListBox.Items.Add(item);
}

get datetime in user friendly (datetime convertion) in sqlserver

select convert (varchar(10), getdate(),101)
------------------------------------------
ddat = new Date();
ddat = new Date(ddat.setDate(new Date(stdate.value).getDate()+parseInt(days)));
var d=newdate.getDate();
var m=(newdate.getMonth()+1);
var y=newdate.getFullYear();
-----------------------------------------
day = new Date()
day = new Date("August15, 2006 08:25:00")
day = new Date(06,8,15)
day = new Date(06,8,15,8,25,0)

Ref: http://msdn.microsoft.com/en-us/library/ms187928.aspx with convertion chart

 

select convert(varchar, getdate(), 100) convertResult,100 style union
select convert(varchar, getdate(), 101),101 union
select convert(varchar, getdate(), 102),102 union
select convert(varchar, getdate(), 103),103 union
select convert(varchar, getdate(), 104),104 union
select convert(varchar, getdate(), 105),105 union
select convert(varchar, getdate(), 106),106 union
select convert(varchar, getdate(), 107),107 union
select convert(varchar, getdate(), 108),108 union
select convert(varchar, getdate(), 109),109 union
select convert(varchar, getdate(), 110),110 union
select convert(varchar, getdate(), 111),111 union
select convert(varchar, getdate(), 112),112 union
select convert(varchar, getdate(), 113),113 union
select convert(varchar, getdate(), 114),114  union
select convert(varchar, getdate(), 120),120  union
select convert(varchar, getdate(), 121),121  union
select convert(varchar, getdate(), 126),126  union
select convert(varchar, getdate(), 127),127  union
select convert(varchar, getdate(), 130),130  union
select convert(varchar, getdate(), 131),131
order by 2

 

convertResult	style
Mar 18 2016  5:27AM	100
3/18/2016	101
2016.03.18	102
18/03/2016	103
18.03.2016	104
18-03-2016	105
18-Mar-16	106
18-Mar-16	107
5:27:19	108
Mar 18 2016  5:27:19:257AM	109
3/18/2016	110
3/18/2016	111
20160318	112
18 Mar 2016 05:27:19:257	113
05:27:19:257	114
3/18/2016 5:27	120
27:19.3	121
2016-03-18T05:27:19.257	126
2016-03-18T05:27:19.257	127
9 ????? ??????? 1437  5:27:19	130
9/06/1437  5:27:19:257AM	131

 

how to disable the all controls in a page

private void Disable(Control c)
{
if ((c is TextBox) || (c is LinkButton) || (c is Button) || (c is CheckBox) || (c is CheckBoxList) ||
(c is RadioButtonList) || (c is DropDownList) || (c is Panel) || (c is ImageButton))
{
   ((WebControl)c).Enabled = false;
}
foreach (Control ct in c.Controls)
Disable(ct);
}