SQL GROUP BY Statement

SQL GROUP BY Statement:

The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer


GROUP BY More Than One Column

SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders
GROUP BY Customer,OrderDate

LINQ - Language-Integrated Query - Basics in C# 3.0

LINQ - Language-Integrated Query - Basics in C# 3.0


-To indicate that query is an integrated feature of the developer's primary programming languages.
-Language-integrated query allows query expressions to benefit from the rich metadata, compile-time syntax checking,
static typing and IntelliSense that was previously available only to imperative code .
-The standard query operators allow queries to be applied to any IEnumerable-based information source.
-The extensibility of the query architecture is used in the LINQ project itself to provide implementations
that work over both XML and SQL data.
-The query operators over XML (LINQ to XML) use an efficient, easy-to-use, in-memory XML facility to provide XPath/XQuery
functionality in the host programming language.

simple C# 3.0 program

using System;
using System.Linq;
using System.Collections.Generic;

class app {
static void Main() {
string[] names = { "Burke", "Connor", "Frank",
"Everett", "Albert", "George",
"Harris", "David" };

IEnumerable query = from s in names
where s.Length == 5
orderby s
select s.ToUpper();

foreach (string item in query)
Console.WriteLine(item);
}
}

Output:

BURKE
DAVID
FRANK

-The local variable query is initialized with a query expression. A query expression operates on one or more information
sources by applying one or more query operators from either the standard query operators or domain-specific operators.
This expression uses three of the standard query operators: Where, OrderBy, and Select.

Enumerable query = names
.Where(s => s.Length == 5)
.OrderBy(s => s)
.Select(s => s.ToUpper());

Single Click Selection - CheckListbox Change Property Double click to Single Click C# and VB.NET

Single Click Selection - CheckListbox Change Property Double click to Single Click

Add code at runtime

chkListBox.CheckOnClick = true;

Shrink Query in Sql Server 2008

Shrink Query in Sql Server 2008

--Use Database name
USE AdventureWorks2008R2;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2008R2_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2008R2
SET RECOVERY FULL;
GO

Preventing the Drop and Alter table in Sql Server Using DDL Trigger

Preventing the Drop and Alter table in Sql Server Using DDL Trigger

In the following example, DDL trigger safety will fire whenever a DROP_TABLE or ALTER_TABLE
event occurs in the database.

CREATE TRIGGER safety
ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
PRINT 'You must disable Trigger "safety" to drop or alter tables!'
ROLLBACK
;

Difference between sp_executesql and EXEC() in Sql Server

Difference between sp_executesql and EXEC() in Sql Server


EXEC() - whose first parameter is a parameterized SQL statement.
sp_executesql - The second parameter is a parameter-list declaration, similar to the parameter
list present in the declaration of a stored procedure.

If the malicious user enters a value as Sql Injection EXEC() execute the maliculous code
but sp_executesql It will search for the value ' or '1'='1 as product name in the database.
Thus preventing SQL Injection attacks.

Connection String For dotnet framework Vb.net and C# Connectivity

Connection String For dotnet framework

Vb.net Connectivity

'VB
Dim connection as DbConnection = new SqlConnection()
connection.ConnectionString = _
"Server=.;Database=pubs;Trusted_Connection=true" connection.Open()

'Do lots of cool work here

connection.Close()

C# Connectivity

//C#

DbConnection connection = new SqlConnection();connection.ConnectionString =
"Server=.;Database=pubs;Trusted_Connection=true"; connection.Open();
//Do lots of cool work here connection.Close();


Creating an instance of the SqlConnection class using the SQL Server .NET provider creates
the DbConnection. The ConnectionString property is initialized to use the local machine (".")
and the database is set to pubs. Lastly, the connection uses a trusted connection for
authentication when connecting to SQL Server.