Place to Tech

Find Client PC Operating System and It's Service Pack

2011-05-04T22:31:00.000+05:30

These days I am working on a project and today (04.05.2011) I came up with some sort of a small modification to the application by letting the user to see the current OS and It's Service Pack in their application. So I wrote the following piece of code and thought of sharing it with my techi friends :)

Note : Please add reference to System.Management

using System;
using System.Management;

namespace RetrieveSystemInfo
{

class Program
{

static void Main(string[] args)
{

ManagementObjectSearcher searcher2 = new
ManagementObjectSearcher("select * from Win32_OperatingSystem");

foreach (ManagementObject objMgmt in searcher2.Get())
{
string sss = objMgmt["name"].ToString();
string [] aa = sss.Split(new char [] {'|'});
string bb = aa[0].ToString();

OperatingSystem OS = Environment.OSVersion;
string ServicePackDetails = OS.ServicePack;

Console.WriteLine("OS Version is : " + bb);
Console.WriteLine("Service Pack is : " + ServicePackDetails);

}

}
}
}

GUID or INT for my Primary key field

2011-04-12T00:50:00.000+05:30

When my Tech Lead came to me couple of months ago and asked me to do a particular SQL Database task and the task was to create a sample data table and create a .net application to to the CURD operations on it.

At that time I was a bit nervous, whether to use INT data type as the primary key or to use GUID type as the primary key; so I started working on that and took a chance to speak to my other team members and do some kind of a research on it. after having a proper understanding on that I used INT as my primary key based on the details I found on technical articles.

Below are the main advantages and disadvantages about both INT and GUID data type.

Advantages

INT

Small amount of storage size (and integer is 4 bytes)
Increased readability, practical use in testing, and easy to understand
Support for functions that return the last primary key generated (@@IDENTITY, SCOPE_IDENTITY())

GUID

The primary key is uniquely identified in the entire system (Unique across the server.)
Easy to merge tables
Don't have problems when inserting a large number of operations

Disadvantages

INT

Hard to work with distributed tables
Primary key in the form of INT/BIGINT is LUID, local unique identifier, which is only used locally in a table
After a large number of operations (insert, delete), the counter for primary key can be reset, bringing the problem of chronology to 1

GUID

Bigger storage size (16 bytes - four times the size of an integer)
Hard to remember and understand

Sync Services for ADO.NET

2011-04-05T22:25:00.001+05:30

Introduction to Sync Services for ADO.NET and the Visual Studio Designer

Hi techies,
Just thought of sharing some important stuff that can be used with Visual Studio and its non other than Sync services. In this session this will introduce Sync Services for ADO.NET which is a part of the Microsoft Sync Framework (MSF). You can watch the video using the following link. Hope you will enjoy it. :)

Sync Service

SS SLUG: June 2010 Meetup

2010-06-11T12:16:00.000+05:30

SS SLUG: June 2010 Meetup

It's time again for another edition of the SQL Server Sri Lanka User Group meeting. Please see image below for all the details.

.net articles will be coming soon....

2010-05-17T15:25:00.001+05:30

OUTPUT Clause (Transact-SQL)

2010-05-16T18:14:00.004+05:30

This Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. The results can also be inserted into a table or table variable. Additionally, you can capture the results of an OUTPUT clause in a nested INSERT, UPDATE, DELETE, or MERGE statement, and insert those results into a target table or view.

Here I use the AdventureWorks sample database to show the sample T SQL for OUTPUT clause.

USE AdventureWorks;

BEGIN TRANSACTION;

DECLARE @testoutput TABLE

(

SalesOrderID int

, SalesOrderDetailID int

, QtyBefore int

, QtyAfter int

, ModifiedDate datetime2

, UserName varchar(30)

)

SELECT * FROM @testoutput;

UPDATE Sales.SalesOrderDetail

SET OrderQty = 5

OUTPUT inserted.SalesOrderID

, inserted.SalesOrderDetailID

, deleted.OrderQty

, inserted.OrderQty

, GETDATE()

, SUSER_NAME()

INTO @testoutput

WHERE SalesOrderID = 43659 AND SalesOrderDetailID = 1;

SELECT * FROM @testoutput;

ROLLBACK TRANSACTION;

SELECT OrderQty FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43659 AND SalesOrderDetailID = 1;

SSSLUG May 2010 Meetup

2010-05-14T16:33:00.005+05:30

We have organized the May 2010 meetup of the SS SLUG for May 19. Please see below details regarding it. If you are in the Colombo area on that day, we would be happy to see you there. For more information checkout the SS SLUG home page.

.NET User Group Meeting May 2010

2010-05-13T13:32:00.003+05:30

.NET User Group Meeting May 2010

Basic transaction concepts

2010-04-25T22:16:00.007+05:30

This is mostly referred as "ACID" properties.

So, what are they?

Atomicity - When Two or more transactions are taking place, either all of them are committed or none of them are committed.

Consistency - That is, after a transaction, valid and new data exists in the database or the data returned to its original state. ROLLBACK function provides the facility of returning data to its original state.

Isolation – During a transaction, data must be in an isolated level there by not allowing other transactions to access it.

Durability - Data is still available even if the server fails. This is achieved by CHECKPOINTS and DATABASE RECOVERY PROECESS.

Correlated Subqueries

2010-03-29T18:27:00.007+05:30

Any queries can be evaluated by executing the subquery once and substituting the resulting value or values into the WHERE clause of the outer query. In queries that include a correlated subquery (also known as a repeating subquery), the subquery depends on the outer query for its values. This means that the subquery is executed repeatedly, once for each row that might be selected by the outer query.

This query retrieves one instance of each employee's first and last name for which the bonus in the SalesPerson table is 5000 and for which the employee identification numbers match in the Employee and SalesPerson tables.

USE AdventureWorks;

SELECT DISTINCT c.LastName, c.FirstName, e.EmployeeID

FROM Person.Contact AS c JOIN HumanResources.Employee AS e

ON e.ContactID = c.ContactID

WHERE 5000.00 IN

(SELECT Bonus

FROM Sales.SalesPerson sp

WHERE e.EmployeeID = sp.SalesPersonID) ;

And the result is,

LastName FirstName EmployeeID

------------------ -- ------------------ -----------------

Ansman-Wolfe Pamela 280

Saraiva José 282

(2 row(s) affected)

Correlated subqueries can also include table-valued functions in the FROM clause by referencing columns from a table in the outer query as an argument of the table-valued function. In this case, for each row of the outer query, the table-valued function is evaluated according to the subquery.

Data Compression in SQL Server 2008

2010-03-07T19:28:00.047+05:30

Estimate the Savings in Storage Space Using Inbuilt Stored Procedure..

sp_estimate_data_compression_savings inbuilt system stored procedure gives the DBA the facility of estimating the saving in storage space . The mentioned TSQL Query shows the space saved when Row Level Compression is enabled for Production.TransactionHistoryArchive Table of AdventureWorks Database.

The attached SQL code snippet shows that there are columns namely size_with_current_compression_settings (KB) and size_with_requested_compression_settings (KB) which provides you an estimate on the amount of disk space which you can save for Production.TransactionHistoryArchive table.

Estimate the Savings in Storage Space Using Data Compression Wizard
SQL Server 2008 inbuilt wizard Data Compression Wizard provides the facility of estimate the savings of storage space when compression is enabled for a particular table or index in a database.

To do that:

Click on the AdventureWorks and under the tables find "Production.TransactionHistoryArchive".
Right Click on the Production.TransactionHistoryArchive and then click on "Storage" and then "Manage Compression".
Click on next

After that click on "Row" from the Compression type drop down list.

Select the compression type option as Row and click the calculate button on the wizard, it will calculate the amount of storage space required when row compression type is used.

Click Next to go to; Select an Output Option Page. There are many options such as create a script, run immediately, or create a Schedule. Click Next to continue with the wizard.

Finally Click on Finish.

Database Design Mistakes

2010-02-26T22:55:00.008+05:30

Hi All

This is my first article. hooray for sql server and my friends specially Gogula (MVP - SQL Server), Dinesh P (MVP - SQL Server) and a lot more.. and more specially Roshan M Karunaratne my Head of IT. Thank you all for yours' valuable information...

Let's back to our topic.

Since this is my first article, I would prefer to present this as simple as possible.

So, as developers (.net, database or another technology developers) we make mistakes and make sure that the same mistake will not repeat in the future.

Hopefully, after reading this article, you will realize that what practices and terms should be considered when designing a database.

So, here are the things...

Poor designing / Planing.
Normalization issues.
Proper naming standards.
Documentation issues.
One table to hold all domain values.
Using identity/guid columns as your only key.
Not using SQL facilities to protect data integrity.
Not using stored procedures to access data.
Lack of testing.

I will be discussing these issues one by one in my next post and it will be available for you by 28th of Feb 2010.

Thanks