HOw to implement LINQ in C#.NET to Perform Aggregate Function as in SQL Server

First Add the namespace - System.Collections.Generic and System.Linq

Now we get data into datatable : 

DataTable dtt = new DataTable();
string _cquery = "Select * from table1";
dtt = du.getdatatable(_cquery);

Now apply group by clause on datatable dtt using LINQ :

var query = from row in dtt.AsEnumerable()
                                group row by row.Field<string>("col1") into grp
                                select new { AfterDistinct = grp.Key};
                    DataTable ddldt = new DataTable();
                    ddldt.Columns.Add("col1");
                    foreach (var row in query)
                    {
                        int i = 0;
                        DataRow dr = ddldt.NewRow();
                        dr["col1"] = row.AfterDistinct.ToString();
                        ddldt.Rows.Add(dr);
                        i++;
                    }
                    ddl1.Items.Clear();
                    ddl1.DataSource = ddldt;
                    ddl1.DataTextField = "col1";
                    ddl1.DataValueField = "col1";
                    ddl1.DataBind();
                    ddl1.Items.Insert(0, new ListItem("--Select--", "0"));


in this example we get data with group by clause from DataTable and bind this data to DropdownList ddl1.

Common Table Expression (CTE) in SQL Server 2008 R2 and get Column value split on comma as row

Introduced in SQL Server 2005, the common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. You can also use a CTE in a CREATE VIEW statement, as part of the view’s SELECT query. In addition, as of SQL Server 2008, you can add a CTE to the new MERGE statement.

SQL Server supports two types of CTEs—recursive and non-recursive. In this article, I explain how to create both types. The examples I provide are based on a local instance of SQL Server 2008.

State Table

CREATE TABLE [dbo].[tb_State](

[SID] [nchar](40) NOT NULL,

[Statename] [nvarchar](200) NULL

) ON [PRIMARY]

City Table

CREATE TABLE [dbo].[tb_City](

[CID] [nchar](40) NOT NULL,

[Statename] [nvarchar](200) NULL,

[Cityname] [nvarchar](500) NULL

) ON [PRIMARY]

Working with Common Table Expressions

You define CTEs by adding a WITH clause directly before your SELECT, INSERT, UPDATE, DELETE, or MERGE statement. The WITH clause can include one or more CTEs, as shown in the following syntax:

[WITH <common_table_expression> [,...]]

<common_table_expression>::=

cte_name [(column_name [,...])]

AS (cte_query)

Example of Common Table Expressions:

WITH cte_BRIJESH AS

(

SELECT a.CID,a.Statename,b.SID,SUBSTRING(',' + a.Cityname + ',', Number + 1,

CHARINDEX(',', ',' + a.Cityname + ',', Number + 1) - Number -1)AS CityName

FROM master..spt_values

cross join dbo.tb_City a

 JOIN tb_State b ON a.Statename = b.Statename

where type = 'P'

and Number <= LEN(',' + a.Cityname + ',') - 1

AND SUBSTRING(',' + a.Cityname + ',', Number, 1) = ','

)

SELECT * FROM cte_BRIJESH

Result:

SQL Server Basics of Cursors

Cursor is a database objects to retrieve data from a result set one row at a time, instead of the T-SQL commands that operate on all the rows in the result set at one time. We use cursor when we need to update records in a database table in singleton fashion means row by row.

Life Cycle of Cursor

1. Declare Cursor

   A cursor is declared by defining the SQL statement that returns a result set.

2. Open

   A Cursor is opened and populated by executing the SQL statement defined by the cursor.

3. Fetch

   When cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.

4. Close

   After data manipulation, we should close the cursor explicitly.

5. Deallocate

   Finally, we need to delete the cursor definition and released all the system resources associated with the cursor.

Syntax to Declare Cursor

Declare Cursor SQL Comaand is used to define the cursor with many options that impact the scalablity and loading behaviour of the cursor. The basic syntax is given below

 DECLARE cursor_name CURSOR
 [LOCAL | GLOBAL] --define cursor scope
 [FORWARD_ONLY | SCROLL] --define cursor movements (forward/backward)
 [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] --basic type of cursor
 [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] --define locks
 FOR select_statement --define SQL Select statement
 FOR UPDATE [col1,col2,...coln] --define columns that need to be updated 

Syntax to Open Cursor

A Cursor can be opened locally or globally. By default it is opened locally. The basic syntax to open cursor is given below:

 OPEN [GLOBAL] cursor_name --by default it is local 

Syntax to Fetch Cursor

Fetch statement provides the many options to retrieve the rows from the cursor. NEXT is the default option. The basic syntax to fetch cursor is given below:

 FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n]
FROM [GLOBAL] cursor_name 
INTO @Variable_name[1,2,..n] 

Syntax to Close Cursor

Close statement closed the cursor explicitly. The basic syntax to close cursor is given below:

 CLOSE cursor_name --after closing it can be reopen 

Syntax to Deallocate Cursor

Deallocate statement delete the cursor definition and free all the system resources associated with the cursor. The basic syntax to close cursor is given below:

 DEALLOCATE cursor_name --after deallocation it can't be reopen 

SQL SERVER – Simple Examples of Cursors

 CREATE TABLE Employee
(
 EmpID int PRIMARY KEY,
 EmpName varchar (50) NOT NULL,
 Salary int NOT NULL,
 Address varchar (200) NOT NULL,
)
GO
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(1,'Mohan',12000,'Noida')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(2,'Pavan',25000,'Delhi')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(3,'Amit',22000,'Dehradun')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(4,'Sonu',22000,'Noida')
INSERT INTO Employee(EmpID,EmpName,Salary,Address) VALUES(5,'Deepak',28000,'Gurgaon')
GO
SELECT * FROM Employee 

 SET NOCOUNT ON
DECLARE @Id int
DECLARE @name varchar(50)
DECLARE @salary int
 DECLARE cur_emp CURSOR
STATIC FOR 
SELECT EmpID,EmpName,Salary from Employee
OPEN cur_emp
IF @@CURSOR_ROWS > 0
 BEGIN 
 FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
 WHILE @@Fetch_status = 0
 BEGIN
 PRINT 'ID : '+ convert(varchar(20),@Id)+', Name : '+@name+ ', Salary : '+convert(varchar(20),@salary)
 FETCH NEXT FROM cur_emp INTO @Id,@name,@salary
 END
END
CLOSE cur_emp
DEALLOCATE cur_emp
SET NOCOUNT OFF 

Create Table with same Columns of existing table

Hey...try this Sql Query to create new table with same schema as existing table

"select * into newtable from existingtable"

SAVE AND RETRIEVE FILES FROM SQL SERVER DATABASE USING ASP.NET

As you can see above for the id field I have set Identity Specification true, so that it automatically increments itself.

Field	Relevance
id	Identification Number
Name	File Name
Content Type	Content Type for the file
Data	File stored as Binary Data

Content Type

Depending on the type of the file below are the content types

File Type	Content Type
Word Document	application/vnd.ms-word
Excel Document	application/vnd.ms-excel
JPEG Image	image/jpeg
Portable Document Format	application/pdf

Connection String

Below is the connection string to the database. You can modify it to suit yours

<connectionStrings>

<add name="conString" connectionString="Data Source=.\SQLEXPRESS;database=dbFiles; Integrated Security=true"/>

</connectionStrings >

Reading the File

The files will be read into a File Stream and then the File Stream will be converted into byte array using BinaryReader in order to save into the database table.

C#

// Read the file and convert it to Byte Array

string filePath = Server.MapPath("APP_DATA/TestDoc.docx");

string filename = Path.GetFileName(filePath);

FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);

BinaryReader br = new BinaryReader(fs);

Byte[] bytes = br.ReadBytes((Int32)fs.Length);

br.Close();

fs.Close();                                                                                  

Saving the File to Database

Once the File is converted into Byte Array it will be inserted into the database. The File Name, File Content Type and the Binary data which resembles the file are stored in the database.

The figure below shows the data being stored in the table.

C#

//insert the file into database

string strQuery = "insert into tblFiles(Name, ContentType, Data) values (@Name, @ContentType, @Data)";

SqlCommand cmd = new SqlCommand(strQuery);

cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename;

cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value = "application/vnd.ms-word";

cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes;

InsertUpdateData(cmd);

And the function InsertUpdateData accepts the SqlCommand object, executes the query and inserts the data into the database.

C#

private Boolean InsertUpdateData(SqlCommand cmd)

{

    String strConnString = System.Configuration.ConfigurationManager

    .ConnectionStrings["conString"].ConnectionString;

    SqlConnection con = new SqlConnection(strConnString);

    cmd.CommandType = CommandType.Text;

    cmd.Connection = con;

    try

    {

        con.Open();

        cmd.ExecuteNonQuery();

        return true;

    }

    catch (Exception ex)

    {

        Response.Write(ex.Message);

        return false;

    }

    finally

    {

        con.Close();

        con.Dispose();

    }

}

Retrieving the File from Database

To retrieve the file from the database, a select query is executed and the ID of the file is passed as the parameter.

The command object is prepared and is passed to the GetData which returns the DataTable which contains the desired file data.

Then the DataTable is passed to the download function which starts the download of the file.

C#

string strQuery = "select Name, ContentType, Data from tblFiles where id=@id";

SqlCommand cmd = new SqlCommand(strQuery);

cmd.Parameters.Add("@id", SqlDbType.Int).Value = 1;

DataTable dt = GetData(cmd);

if (dt != null)

{

    download(dt);

}

Below is the code for the GetData function. It is a simple function that executes the Select query.

C#

private DataTable GetData(SqlCommand cmd)

{

    DataTable dt = new DataTable();

    String strConnString = System.Configuration.ConfigurationManager

    .ConnectionStrings["conString"].ConnectionString;

    SqlConnection con = new SqlConnection(strConnString);

    SqlDataAdapter sda = new SqlDataAdapter();

    cmd.CommandType = CommandType.Text;

    cmd.Connection = con;

    try

    {

        con.Open();

        sda.SelectCommand = cmd;

        sda.Fill(dt);

        return dt;

    }

    catch

    {

        return null;

    }

    finally

    {

        con.Close();

        sda.Dispose();

        con.Dispose();

    }

}

Download the File

Here is the function which initiates the download of file. It basically reads the file contents into a Byte array and also gets the file name and the Content Type. Then it writes the bytes to the response using Response.BinaryWrite

C#

private void download (DataTable dt)

{

    Byte[] bytes = (Byte[])dt.Rows[0]["Data"];

    Response.Buffer = true;

    Response.Charset = "";

    Response.Cache.SetCacheability(HttpCacheability.NoCache);

    Response.ContentType = dt.Rows[0]["ContentType"].ToString();

    Response.AddHeader("content-disposition", "attachment;filename="

    + dt.Rows[0]["Name"].ToString());

    Response.BinaryWrite(bytes);

    Response.Flush();

    Response.End();

}