How to combine two data tables?

I just am getting a situation to combine two data tables. Actually in my end, I had two datasets with two data tables in each of them. I needed to combine first data table in first dataset with first data table in second dataset. Similarly second data table in first dataset to second data table in second dataset.

On that moment I just found the way to merge data tables by using DataTable.Merge() method.

Then I combined my data tables as follows,

ds1.Tables[0].Merge(ds2.Tables[0], false,MissingSchemaAction.Add);
ds1.Tables[1].Merge(ds2.Tables[1], false, MissingSchemaAction.Add);

return ds1;

N.B: Here ds1 is first dataset, ds2 is second dataset.

...S.VinothkumaR.

Selecting Top N Number of Rows - SQL Server 2005

Hi all,

We can get rows from a particular table by using the Select query as follows,

select * from [Table_Name]

If we need 10 rows, we can use Top query like as follows,

select top 10 * from [Table_Name]

Suppose we need to pass the number of rows count as parameter…How to do?

Yes, the way of using ROW_NUMBER(), we can do that. For more, see the following example,

First we are going to create a table namely TestCount.

create table TestCount(SNo int, Name varchar(200))

Insert values in to that table.

insert into TestCount(SNo,Name) values(1,'aaa')
insert into TestCount(SNo,Name) values(2,'bbb')
insert into TestCount(SNo,Name) values(3,'ccc')
insert into TestCount(SNo,Name) values(4,'ddd')
insert into TestCount(SNo,Name) values(5,'eee')
insert into TestCount(SNo,Name) values(6,'fff')
insert into TestCount(SNo,Name) values(7,'ggg')
insert into TestCount(SNo,Name) values(8,'hhh')
insert into TestCount(SNo,Name) values(9,'iii')
insert into TestCount(SNo,Name) values(10,'jjj')

Now use the following query to get top n number of rows.

declare @Count int
set @Count = 5
SELECT SNo,Name FROM
(select ROW_NUMBER() over (order by [SNo])
as Row, * FROM
(SELECT SNo,Name FROM TestCount)
as userquery)
as
SelectionWithRowNumbers WHERE Row >0 AND Row<=@Count

That’s it. Hence we can get top n number of rows from a table.

…S.VinothkumaR.

Creating an Excel application in Dot Net.

We can do the creation of excel application in Dot Net by using the interoperability services. The interoperability services make it easy to work with COM Capable Applications such as Word and Excel.



First of all we need to create a reference in our project to Excel object library by using add reference. You can get an “Add Reference” dialog box when clicking the add reference, choose com tab and select “Microsoft Excel 9.0 Object Library” as follows.



After adding this library we can get Interop.Excel.dll and Interop.Office.dll files in our bin folder. Using these dll files we can manipulate excel like excel file creation, opening and updating.

When we ready to write code, first create an object of Excel.Application, Excel._Workbook and Excel._Worksheet. These are going to be used in creation of excel application.

How to create excel application?

This is the way to create an excel application. See the following code for help us to create an excel application.

protected void btnCreateExcel_Click(object sender, EventArgs e)
{
try
{
file = @"C:\temp\test.xls";
ex = new Excel.Application();
Excel._Workbook wb;
Excel._Worksheet ws;
wb = (Excel._Workbook)(ex.Workbooks.Add(Missing.Value));
ws = (Excel._Worksheet)(wb.Sheets["Sheet1"]);
ws.Name = "MyFirstSheet";
ws.Cells[1, 1] = "Test1";
ws.Cells[1, 2] = "Test2";
ws.Cells[2, 1] = "Test3";
ws.Cells[2, 2] = "Test4";
ws.Cells[3, 1] = "Test5";
ws.Cells[3, 2] = "Test6";
ws.get_Range("A1", "A3").Font.Bold = true;
ws.get_Range("A1", "C3").EntireColumn.AutoFit();
//wb.SaveAs(file, Excel.XlFileFormat.xlCSV, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
wb.SaveCopyAs(file);
wb.Close(null, null, null);
ex.Workbooks.Close();
ex.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wb);
System.Runtime.InteropServices.Marshal.ReleaseComObject(ex);
}
catch (Exception ex)
{
Response.Write("Error: \n" + ex.ToString());
}
}

By using the above code, now we have created an excel application test.xls.

How to open an excel application and how can update with that application?

Workbooks.Open method is used to open an excel application. Then we can assign the values of some particular cells and will save that application by using the save method. For more, see the following code.

protected void btnChangeExcel_Click(object sender, EventArgs e)
{
try
{
file = @"C:\temp\test.xls";
ex = new Excel.ApplicationClass();
Excel._Workbook wbObj = null;
Excel._Worksheet wsObj = null;
Excel.Range rngObj = null;
wbObj = ex.Workbooks.Open(file, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true);
wsObj = (Excel._Worksheet)wbObj.Worksheets[1];
wsObj.Cells[1, 1] = "1";
wsObj.Cells[1, 2] = "2";
wsObj.Cells[1, 3] = "3";
wsObj.Cells[2, 1] = "A";
wsObj.Cells[2, 2] = "B";
wsObj.Cells[2, 3] = "C";
rngObj = wsObj.get_Range("A1", "C2");
rngObj.EntireRow.AutoFit();
rngObj.EntireColumn.AutoFit();
wbObj.Save();
wbObj.Close(null, null, null);
ex.Workbooks.Close();
ex.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(rngObj);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wsObj);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wbObj);
System.Runtime.InteropServices.Marshal.ReleaseComObject(ex);
}
catch (Exception ex)
{
Response.Write("Error: \n" + ex.ToString());
}
}


That’s it. Hence we have created an excel application and updating some particular cells in that application.

...S.VinothkumaR.

How to find the queries which are use the most CPU time and most IO?

Most of us wanted to know which queries are use the most CPU time and most IO in a db server.

We can get that information using the dynamic management view, such as dm_exec_query_stats and dm_exec_sql_text.

For this, first of all we need to create the following procedure.

USE [master]
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'[dbo].[dba_TOP_Queries_Average]')
AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[dba_TOP_Queries_Average]
go
CREATE procedure dba_TOP_Queries_Average @topcount int=10,
@type varchar(10) ='cpu'
as
begin try
select Top (@topcount )
creation_time
, last_execution_time
,rank() over(order by (total_worker_time+0.0)/
execution_count desc,
sql_handle,statement_start_offset ) as row_no
, (rank() over(order by (total_worker_time+0.0)/
execution_count desc,
sql_handle,statement_start_offset ))%2 as l1
, (total_worker_time+0.0)/1000 as total_worker_time
, (total_worker_time+0.0)/(execution_count*1000)
as [AvgCPUTime]
, total_logical_reads as [LogicalReads]
, total_logical_writes as [LogicalWrites]
, execution_count
, total_logical_reads+total_logical_writes as [AggIO]
, (total_logical_reads+total_logical_writes)/
(execution_count+0.0) as [AvgIO]
, case when sql_handle IS NULL
then ' '
else ( substring(st.text,(qs.statement_start_offset+2)/2,
(case when qs.statement_end_offset = -1
then len(convert(nvarchar(MAX),st.text))*2
else qs.statement_end_offset
end - qs.statement_start_offset) /2 ) )
end as query_text
, db_name(st.dbid) as db_name
, st.objectid as object_id
from sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
where total_worker_time > 0
order by case when @type='cpu' then (total_worker_time+0.0)/(execution_count*1000)
else (total_logical_reads+total_logical_writes)/(execution_count+0.0) end desc
end try
begin catch
select -100 as row_no
, 1 as l1, 1 as create_time,1 as last_execution_time,1 as total_worker_time,1 as AvgCPUTime,1 as LogicalReads,1
as LogicalWrites
, ERROR_NUMBER() as execution_count
, ERROR_SEVERITY() as AggIO
, ERROR_STATE() as AvgIO
, ERROR_MESSAGE() as query_text
, 0 as db_name
, 0 as object_name
end catch

Now we can get the top 10 queries based on the CPU usage. By default our procedure will return top 10 queries. Because we given the topcount as 10.

For example,

exec [dbo].[dba_TOP_Queries_Average]

If we need to get top 10 queries based on the average I/O usage, do as following.

exec [dbo].[dba_TOP_Queries_Average] @type = 'Avg'

If we need to get top n queries,

exec [dbo].[dba_TOP_Queries_Average] @topcount =14, @type = 'Avg'
exec [dbo].[dba_TOP_Queries_Average] @topcount =14, @type = 'cpu'

That’s it…

...S.VinothkumaR

GROUP BY, HAVING, SUM, AVG, and COUNT(*)

Here is the simple example of using the clauses like where, Having, Group By and the aggregations like Count, Sum and Avg.

First we will have a simple and a small table ‘order’ with data’s as follows,
ORDER STATE AMOUNT
------+-------+-------------
1234 NY 10.00
1235 TX 15.00
1236 CA 20.00
1237 TX 25.00
1238 CA 30.00
1237 NY 35.00
1238 NY 40.00

Using Aggregation:

Aggregation combines rows together and performs some operation on their combined values. Very common aggregations are COUNT, SUM, and AVG.

SELECT COUNT(*) as count,SUM(amount) as sum,AVG(amount) as avg FROM orders

this query will return as,

count sum avg
-----+------+-----
7 175 25

Using Where Clause:
If we need to get result with the basis of any state, use the following query.
SELECT COUNT(*) as count,SUM(amount) as sum,AVG(amount) as avg FROM orders WHERE state = 'NY'

this will return as,
count sum avg
----+------+----------
3 85 28.33333

As from the above results, there is the average has a repeating decimal. So we can use the Round function.

Using Group By Clause:

The GROUP BY clause says that the aggregations should be performed for the distinct values of a column or columns.

SELECT state, COUNT(*) as count,SUM(amount) as sum, ROUND(AVG(amount),0) as avg FROM orders GROUP BY state

This will return as,

STATE count sum avg
------+-----+------+----
NY 3 85 28
TX 2 40 20
CA 2 50 25

Using Having Clause:

The HAVING clause lets us put a filter on the results after the aggregation has taken place. If we need to know which state having average amount of Rs.25 or more, use the following code.

SELECT state, COUNT(*) as count,SUM(amount) as sum, ROUND(AVG(amount),0) as avg FROM orders GROUP BY state HAVING AVG(amount) >= 25

Result of this query,

state count sum avg
------+-----+------+----
NY 3 85 28
CA 2 50 25

Now we can pull results out of a database in a single query with the use of combine the WHERE, GROUP BY, and HAVING as follows,

SELECT state, COUNT(*),SUM(amount) as sum, ROUND(AVG(amount),0) as avg FROM orders WHERE amount > 20 GROUP BY state HAVING avg(amount) >= 30

That's it....

...S.VinothkumaR.

Creating chart with Crystal Report application.

Crystal Report also supports the creation of charts. Now we are going to see how we can display a chart showing the percentages of a particular product ordered by the each customer.
Add new crystal report in our solution and do as the following steps.

1. Add the following tables to the report
Customers
Order Details
Orders
Products
2. Choose the following fields to display:
Customers.CompanyName
Products.ProductName
Order Details.Quantity
3. Group the report by Customers.CompanyName.
4. For the Summaries section, ensure that only Sum of Order Details.Quantity is present.
5. In the Chart dialog, check the Pie Chart option.

Now, add a new Parameter Field to the report and name the parameter Product_Name. Click the Select Expert button and bind the parameter to the Products.ProductName field of the report.

Finally, add the following controls to Form1:
- Label
- ComboBox
- Button
Write the code as following to view chart,

private void Form1_Load(object sender, EventArgs e)
{
string connStr = "Data Source=.\\SQLEXPRESS;Initial Catalog=myDB;Integrated Security=True";
string sql = "SELECT CustomerID FROM Customers";
SqlConnection conn = new SqlConnection(connStr);
SqlCommand comm = new SqlCommand(sql, conn);
conn.Open();
SqlDataReader reader = comm.ExecuteReader;
while (reader.Read)
{
comboBox1.Items.Add(reader(0));
}
conn.Close();
sql = "SELECT ProductName from Products";
comm.CommandText = sql;
conn.Open();
reader = comm.ExecuteReader;
while (reader.Read)
{
ComboBox2.Items.Add(reader(0));
}
conn.Close();
}

In button click event,

private void btnViewReport_Click(object sender, System.EventArgs e)
{
CrystalReport2 report = new CrystalReport2();
report.SetParameterValue("Product_Name", ComboBox2.Text);
{
Form2.CrystalReportViewer1.ReportSource = report;
Form2.ShowDialog();
}
}

That’s it… Now you can test with running the application.

...S.VinothkumaR.

Adding parameters to the Crystal Report:

We can pass parameters to report during runtime. For that you need to follow the steps as below…
Main Report view -- > right click on the parameter fields items located in the field explorer
-- > select new -- > specify Customer_ID as the name -- > click ok -- > In the choose field dialog, choose CustomerId and click ok.

Now click the Select Expert button which is in the toolbar of Visual Studio 2005. In the Select Expert dialog, set the value of the control as CustomerId is equal to Customer_ID.

Now create a wizard in form1 using follows,
- Label (CustomerID )
- Combo Box (comboBox1)
- Button(ViewReports)

Write code in form1 code behind as follows to add parameters to report.

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

private void Form1_Load(object sender, EventArgs e)
{
string connStr = "Data Source=.\\SQLEXPRESS;Initial Catalog=myDB;Integrated Security=True";
string sql = "SELECT CustomerID FROM Customers";
SqlConnection conn = new SqlConnection(connStr);
SqlCommand comm = new SqlCommand(sql, conn);
conn.Open();
SqlDataReader reader = comm.ExecuteReader;
while (reader.Read)
{
comboBox1.Items.Add(reader(0));
}
conn.Close();
}

Now switch to button click event,
private void btnViewReport_Click(object sender, System.EventArgs e)
{
CrystalReport1 report = new CrystalReport1();
report.SetParameterValue("Customer_ID", ComboBox1.Text);
{
Form2.CrystalReportViewer1.ReportSource = report;
Form2.ShowDialog();
}
}

That’s it. Now you can test the application by running.

...S.VinothkumaR.