Filter DataTable

Most of them we come across the situation where we need to filter the data from Datatable. Below are the two ways we can achieve this functionality.

1. Filter the Datatable and save the result in Datarow

SqlConnection myConn = new SqlConnection(strConString);
string strSql = "select * from Employee";DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(strSql, myConn);

// Filters the data by "EmployeeID = 143" and returns an array of DataRow
da.Fill(ds);
DataTable dt = ds.Tables[0];
DataRow[] dRow = dt.Select("EmployeeID=’143’");
for (int i = 0; i < dRow.Length; i++)
{
Response.Write("Row " + dRow[i]["Employee_Name"].ToString());
}

2. Create DataView populate it with Datatable and then use Filter Property of Dataview

DataView dtView = ds.Tables["Employee"].DefaultView;
dtView.RowFilter = "EmpployeeID = '143'";

Copy DataRows from one DataTables to another

using System;
using System.Data;
using System.Data.SqlClient;

namespace ImportRowFromDataTable
{
class Class1
{
static void Main(string[] args)
{
int tbl_1count;
int tbl_2count;
int i;

// Add code to start application here.
DataTable tbl_1 = new DataTable();
DataTable tbl_2 = new DataTable();

// Change the connection string to your server.
SqlConnection Con = new SqlConnection("Connectionstring");

// Create the DataAdapter.
SqlDataAdapter da = new SqlDataAdapter("Select * from tbl_1", Conn);

// Fill the DataSet with data.
DataSet ds = new DataSet();
da.Fill(ds, "tbl_1");
tbl_1 = ds.Tables["tbl_1"];
tbl_1count = tbl_1.Rows.Count;

// Write the number of rows in the Products table to the screen.
Console.WriteLine("Table tbl_1has " + tbl_1count.ToString() + " Rows.");

// Loop through the five rows, and disply the first column.
for (i = 0; i <= 4; ++i)
{
Console.WriteLine("Row(" + i.ToString() + ") = " + tbl_1.Rows[i][1]);
}

// Use Clone method to copy the table tbl_1 structure.
tbl_2 = tbl_1.Clone();

// Use the ImportRow method to copy from tbl_1's data to its clone.
for (i = 0; i <= 4; ++i)
{
tbl_2.ImportRow(tbl_1.Rows[i]);
}
tbl_2count = tbl_2.Rows.Count;


// Write the number of rows in tbl_2table to the screen.
Console.WriteLine("Table tbl_2 has " +tbl_2count.ToString() + " Rows");

// Loop through the rows of tbl_2, and display.
for (i = 0; i <= tbl_2count - 1; ++i)
{
Console.WriteLine("Row(" + i.ToString() + ") = " + tbl_2.Rows[i][1]);
}

Console.ReadLine();

}
}
}

Copy DataRows form DataTables to another

using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

namespace ImportRowFromDataTable
{
class Class1
{
static void Main(string[] args)
{
int tbl_1count;
int tbl_2count;
int i;

// Add code to start application here.
DataTable tbl_1 = new DataTable();
DataTable tbl_2 = new DataTable();

// Change the connection string to your server.
SqlConnection Con = new SqlConnection("Connectionstring");

// Create the DataAdapter.
SqlDataAdapter da = new SqlDataAdapter("Select * from tbl_1", Conn);

// Fill the DataSet with data.
DataSet ds = new DataSet();
da.Fill(ds, "tbl_1");
tbl_1 = ds.Tables["tbl_1"];
tbl_1count = tbl_1.Rows.Count;

// Write the number of rows in the Products table to the screen.
Console.WriteLine("Table tbl_1has " + tbl_1count.ToString() + " Rows.");

// Loop through the five rows, and disply the first column.
for (i = 0; i <= 4; ++i)
{
Console.WriteLine("Row(" + i.ToString() + ") = " + tbl_1.Rows[i][1]);
}

// Use Clone method to copy the table tbl_1 structure.
tbl_2 = tbl_1.Clone();

// Use the ImportRow method to copy from tbl_1's data to its clone.
for (i = 0; i <= 4; ++i)
{
tbl_2.ImportRow(tbl_1.Rows[i]);
}
tbl_2count = tbl_2.Rows.Count;


// Write the number of rows in tbl_2table to the screen.
Console.WriteLine("Table tbl_2 has " +tbl_2count.ToString() + " Rows");

// Loop through the rows of tbl_2, and display.
for (i = 0; i <= tbl_2count - 1; ++i)
{
Console.WriteLine("Row(" + i.ToString() + ") = " + tbl_2.Rows[i][1]);
}

Console.ReadLine();

}
}
}

Stored Procedure to Create Dynamic PIVOT in SQL

We can create dynamic PIVOT in SQL. Check the below example

CREATE PROCEDURE [dbo].[Trainings]

-- Add the parameters for the stored procedure here

AS
BEGIN

SET NOCOUNT ON;
DECLARE @Columns varchar(8000)
DECLARE @SQL varchar(8000)

--Create PIVOT table fields list in ('', '') format

SET @Columns = substring((select ',['+Training_Name+']' from View_Training group by Training_Name for xml path('')),2,8000)

--PIVOT table query

SET @SQL = 'SELECT * FROM
(Select * from View_Training) CrossTraning
PIVOT
(max(Status) for Training_Name in ('+@Columns+')) pivottable'

EXEC(@sql)
END

CSS TO Display text vertically

To Rotate the text 90 degrees (i.e. Display it vertically) we can use below CSS Class

CSS Class.

.verticaltext
{
font-family:Verdana, Arial; font-size:12px;
writing-mode: tb-rl;
filter: flipH() flipV();
}