Tuesday, December 29, 2009

How to merge tables in a Dataset - Sql Server

DataSet ....

The DataSet contains copy of the data we requested through the SQL statement. The SqlDataAdapter object allows us to populate DataTable in a DataSet. We can use Fill method in the SqlDataAdapter for populating data in a Dataset. We can populate Dataset with more than one table at a time using SqlDataAdapter Object. The DataTableCollection contains zero or more DataTable objects.

In some situation we want to combine the result of multiple SQL query as a single result set. In that case we can use the Dataset's Merge method for doing this. The tables involved in the merge should be identical, that is the columns are similar data types .


Private Sub GetUsersList()

Dim connetionString As String
Dim connection As SqlConnection
Dim command As SqlCommand
Dim adapter As New SqlDataAdapter
Dim ds As New DataSet
Dim dt As DataTable
Dim firstSql As String
Dim secondSql As String
Dim i As Integer
connetionString = "Data Source=.;Initial Catalog=Nafex;User ID=sa;Password=111"
firstSql = "Select * from tblUser"
secondSql = "Select * from tluVrType"
connection = New SqlConnection(connetionString)
Try
connection.Open()
command = New SqlCommand(firstSql, connection)
adapter.SelectCommand = command
adapter.Fill(ds, "Table(0)")
adapter.SelectCommand.CommandText = secondSql
adapter.Fill(ds, "Table(1)")
adapter.Dispose()
command.Dispose()
connection.Close()

ds.Tables(0).Merge(ds.Tables(1))
dt = ds.Tables(0)

For i = 0 To dt.Rows.Count - 1
MsgBox(dt.Rows(i).Item(0) & " -- " & dt.Rows(i).Item(1))
Next
Catch ex As Exception
MsgBox("Can not open connection ! ")
End Try

End Sub

Sunday, December 20, 2009

String Format for Double ( 0.00)

String Format for Double [C#]

The following examples show how to format float numbers to string in C#. You can use static method String.Format or instance methods double.ToString and float.ToString.

Digits after decimal point

This example formats double to string with fixed number of decimal places. For two decimal places use pattern „0.00“. If a float number has less decimal places, the rest digits on the right will be zeroes. If it has more decimal places, the number will be rounded.

[C#]
// just two decimal places
String.Format("{0:0.00}", 123.4567); // "123.46"
String.Format("{0:0.00}", 123.4); // "123.40"
String.Format("{0:0.00}", 123.0); // "123.00"

Next example formats double to string with floating number of decimal places. E.g. for maximal two decimal places use pattern „0.##“.

[C#]
// max. two decimal places
String.Format("{0:0.##}", 123.4567); // "123.46"
String.Format("{0:0.##}", 123.4); // "123.4"
String.Format("{0:0.##}", 123.0); // "123"

Digits before decimal point

If you want a float number to have any minimal number of digits before decimal point use N-times zero before decimal point. E.g. pattern „00.0“ formats a float number to string with at least two digits before decimal point and one digit after that.

[C#]
// at least two digits before decimal point
String.Format("{0:00.0}", 123.4567); // "123.5"
String.Format("{0:00.0}", 23.4567); // "23.5"
String.Format("{0:00.0}", 3.4567); // "03.5"
String.Format("{0:00.0}", -3.4567); // "-03.5"

Thousands separator

To format double to string with use of thousands separator use zero and comma separator before an usual float formatting pattern, e.g. pattern „0,0.0“ formats the number to use thousands separators and to have one decimal place.

[C#]
String.Format("{0:0,0.0}", 12345.67);     // "12,345.7"
String.Format("{0:0,0}", 12345.67); // "12,346"

Zero

Float numbers between zero and one can be formatted in two ways, with or without leading zero before decimal point. To format number without a leading zero use # before point. For example „#.0“ formats number to have one decimal place and zero to N digits before decimal point (e.g. „.5“ or „123.5“).

Following code shows how can be formatted a zero (of double type).

[C#]
String.Format("{0:0.0}", 0.0);            // "0.0"
String.Format("{0:0.#}", 0.0); // "0"
String.Format("{0:#.0}", 0.0); // ".0"
String.Format("{0:#.#}", 0.0); // ""

Align numbers with spaces

To align float number to the right use comma „,“ option before the colon. Type comma followed by a number of spaces, e.g. „0,10:0.0“ (this can be used only in String.Format method, not in double.ToString method). To align numbers to the left use negative number of spaces.

[C#]
String.Format("{0,10:0.0}", 123.4567);    // "     123.5"
String.Format("{0,-10:0.0}", 123.4567); // "123.5 "
String.Format("{0,10:0.0}", -123.4567); // " -123.5"
String.Format("{0,-10:0.0}", -123.4567); // "-123.5 "

Custom formatting for negative numbers and zero

If you need to use custom format for negative float numbers or zero, use semicolon separator;“ to split pattern to three sections. The first section formats positive numbers, the second section formats negative numbers and the third section formats zero. If you omit the last section, zero will be formatted using the first section.

[C#]
String.Format("{0:0.00;minus 0.00;zero}", 123.4567);   // "123.46"
String.Format("{0:0.00;minus 0.00;zero}", -123.4567); // "minus 123.46"
String.Format("{0:0.00;minus 0.00;zero}", 0.0); // "zero"

Some funny examples

As you could notice in the previous example, you can put any text into formatting pattern, e.g. before an usual pattern „my text 0.0“. You can even put any text between the zeroes, e.g. „0aaa.bbb0“.

[C#]
String.Format("{0:my number is 0.0}", 12.3);   // "my number is 12.3"
String.Format("{0:0aaa.bbb0}", 12.3); // "12aaa.bbb3"

Wednesday, December 09, 2009

OFAC Block Person List

Office of Foreign Assets Control ("OFAC")

The Office of Foreign Assets Control ("OFAC") of the US Department of the Treasury administers and enforces economic and trade sanctions based on US foreign policy and national security goals against targeted foreign countries and regimes, terrorists, international narcotics traffickers, those engaged in activities related to the proliferation of weapons of mass destruction, and other threats to the national security, foreign policy or economy of the United States. OFAC acts under Presidential national emergency powers, as well as authority granted by specific legislation, to impose controls on transactions and freeze assets under US jurisdiction. Many of the sanctions are based on United Nations and other international mandates, are multilateral in scope, and involve close cooperation with allied governments.


http://www.treas.gov/offices/enforcement/ofac/sdn/index.shtml


OFAC web services into our project (OFAC with .NET VB/V#)

There are many web services online live sites who provided the OFAC web services.
But this OFAC web services is not free only for Free try Testing after that Buy the OFAC web services and implement into project.

I Implement one of the OFAC web services here are the Steps for Implementation OFAC.
this is one of the best site for OFAC web services i m useing this .






https://www.xignite.com/Login.aspx?ReturnUrl=/MyAccount/MyAccountHome.aspx&mesg=yes


Step 1:
Create Registration

Step 1:
Login into your Account.

Step 3:
Create VB.NET Project (Web Project)

Step 4:
Header Code for OFAC Web services Code into your Project.

Dim objSearchResults As New com.xignite.www.SearchResults

'RemoteQuotes is the web reference to the XigniteQuotes service
'objRemoteQuote is an instance of the service proxy
Dim objRemoteQuote As New com.xignite.www.Header
'assign your username / password , same as you Registration on xignite.com

objRemoteQuote.Password = "*******
objRemoteQuote.Username = "*****@****.com"

'assign the header to the service proxy
objOFACService.HeaderValue = objRemoteQuote
'----------------------------------------Searching OFAC CODE

objOFACService.SearchByAddressAsync(com.xignite.www.OFACTypes.All , "Person Name","Address" , "City" ,"Country" , com.xignite.www.SearchTypes.Contains )
Dim ct, adct As Integer
For ct = 0 To result.Count
address = result.Matches(ct).Addresses.ToString()
If result.Matches(ct).Addresses.Length > 0 Then
For adct = 0 To result.Matches(ct).Addresses.Length
txtaddress.Text = result.Matches(ct).Addresses(adct).Country.ToString()
OfacChk = True
OfacResponsIDnumb = result.Matches(ct).Id
Next

End If

Next



========================================
OFAC Person match in Live OFAC List.
by Asma Qureshi
Project manager
Dubai UAE

Saturday, November 14, 2009

Ye Haath Salamat Hain Jab Tak
Is Khoon May Hararat Hai Jab Tak
Is Dil May Sadaqat Hai Jab Tak

Wednesday, November 11, 2009

Adding Row Number to SQL SELECT result

ntroduction This article will explain how we can add sequence row number to a SQL select query starting from 1 onwards. This can be achieved by using built in SQL function ?ROW_NUMBER()?. This function simply generates row number for each row in the result. You can specify the partition and order by criteria. This is how it works:

e.g. Suppose we have a table ?Employees?.
SELECT EmployeeId, EmployeeName, Salary
FROM Employees

EmployeeId EmployeeName Salary
-------------------------------------
1002 Alden 4000
2343 Lawson 4500
2004 Barbra 4800
1105 Marsden 4500
3116 Mac 5000

Use of ROW_NUMBER() will assign sequence number to rows as:
SELECT ROW_NUMBER()
OVER (ORDER BY EmployeeName) AS Row,
EmployeeId, EmployeeName, Salary
FROM Employees

Row EmployeeId EmployeeName Salary
-------------------------------------------
1 1002 Alden 4000
2 2343 Lawson 4500
3 2004 Barbra 4800
4 1105 Marsden 4500
5 3116 Mac 5000

Using ROW_NUMBER() for calculating Nth highest salary

We can utilize this function for calculating Nth highest salary of a employee. Suppose we want to find employee with 4th highest salary. This can be done as:
SELECT * FROM
(SELECT ROW_NUMBER()
OVER (ORDER BY Salary) AS Row,
EmployeeId, EmployeeName, Salary
FROM Employees) AS EMP
WHERE Row = 4

Row EmployeeId EmployeeName Salary
-------------------------------------------
4 1105 Marsden 4500

Using ROW_NUMBER() in case of pagination

This can also be used for getting rows which belongs to a particular page only. This is very common scenario of a business application where we have lots of rows in database and we want to filter based on page number.
SELECT * FROM
(SELECT ROW_NUMBER()
OVER (ORDER BY EmployeeName) AS Row,
EmployeeId, EmployeeName, Salary
FROM Employees) AS EMP
WHERE Row BETWEEN 2 AND 4

Row EmployeeId EmployeeName Salary
-------------------------------------------
2 2343 Lawson 4500
3 2004 Barbra 4800
4 1105 Marsden 4500



happy programming :D

Wednesday, October 28, 2009

Using the CASE expression instead of dynamic SQL in SQL Server

Problem
I have a lot of SQL update queries where I need to make IF/ELSE types of decisions. I am using a cursor in some cases but looping through thousands of rows for update takes a long time. I also use some dynamic SQL to handle some query parameter decision making. Is there a better alternative?

Solution
The CASE expression is a really powerful tool that can you use to solve your SQL Server query problems. You're probably familiar with its use in mimicking if/else processing when issuing SELECT statements. However, its use is not confined strictly to this kind of processing.

Among the ways I've leveraged the CASE expression in my code:

  • To eliminate a cursor loop when updating rows
  • To perform specialized processing when using aggregate functions
  • To create dynamic ORDER BY and WHERE clauses without using dynamic SQL

Let's look at some examples

We'll first create a new table called Customer and insert some rows

CREATE TABLE dbo.Customer
(
customerid INT IDENTITY PRIMARY KEY,
firstname VARCHAR(40) NOT NULL,
lastname VARCHAR(40) NOT NULL,
statecode VARCHAR(2) NOT NULL,
totalsales money NOT NULL DEFAULT 0.00
)

INSERT INTO dbo.Customer (firstname, lastname, statecode, totalsales)
SELECT 'Thomas', 'Jefferson', 'VA', 100.00

INSERT INTO dbo.Customer (firstname, lastname, statecode, totalsales)
SELECT 'John', 'Adams', 'MA', 200.00

INSERT INTO dbo.Customer (firstname, lastname, statecode, totalsales)
SELECT 'Paul', 'Revere', 'MA', 300.00

INSERT INTO dbo.Customer (firstname, lastname, statecode, totalsales)
SELECT 'Ben', 'Franklin', 'PA', 400.00
GO

Example 1
A requirement has come in to denormalize the table for reporting purposes by adding a state description column. Now, you could use a cursor and loop through the table, updating each row, but cursors can be performance killers. You could also create multiple UPDATE statements, but that would be unwieldly. Instead, you can use an UPDATE statement with CASE to efficiently update the table with one SET operation.

ALTER TABLE dbo.Customer ADD statedescription VARCHAR(50) NULL
GO

UPDATE dbo.Customer
SET stateDescription = CASE WHEN statecode = 'MA' THEN 'Massachusetts'
WHEN statecode = 'VA' THEN 'Virginia'
WHEN statecode = 'PA' THEN 'Pennsylvania'
ELSE NULL
END

Example 2
A second requirement has come in where we need to report on the total number of all customers, the total number of all Massachusetts customers, and an average of all sales made by all Massachusetts customers. We could limit the query to just Massachusetts customers but that would make it cumbersome to get our count of total customers. To solve this problem, you can write the query to use a CASE expression within the aggregate functions to get Massachusetts specific information:

SELECT COUNT(*) AS TotalCustomers,
SUM(CASE WHEN statecode = 'MA' THEN 1 ELSE NULL END) AS TotalMassCustomers,
AVG(CASE WHEN statecode = 'MA' THEN totalsales ELSE NULL END) AS TotalMassSales
FROM dbo.Customer

Since NULL values are discarded when performing aggregate functions, we can easily get the required totals.




Monday, October 26, 2009

Sql server - Standard Date Formats

SQL Server Date Formats
Standard Date Formats
Date Format Standard SQL Statement Sample Output
Mon DD YYYY 1
HH:MIAM (or PM)
Default SELECT CONVERT(VARCHAR(20), GETDATE(), 100) Jan 1 2005 1:29PM 1
MM/DD/YY USA SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY] 11/23/98
MM/DD/YYYY USA SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] 11/23/1998
YY.MM.DD ANSI SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD] 72.01.01
YYYY.MM.DD ANSI SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD] 1972.01.01
DD/MM/YY British/French SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY] 19/02/72
DD/MM/YYYY British/French SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY] 19/02/1972
DD.MM.YY German SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY] 25.12.05
DD.MM.YYYY German SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY] 25.12.2005
DD-MM-YY Italian SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY] 24-01-98
DD-MM-YYYY Italian SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY] 24-01-1998
DD Mon YY 1 - SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY] 04 Jul 06 1
DD Mon YYYY 1 - SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY] 04 Jul 2006 1
Mon DD, YY 1 - SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY] Jan 24, 98 1
Mon DD, YYYY 1 - SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY] Jan 24, 1998 1
HH:MM:SS - SELECT CONVERT(VARCHAR(8), GETDATE(), 108) 03:24:53
Mon DD YYYY HH:MI:SS:MMMAM (or PM) 1 Default +
milliseconds
SELECT CONVERT(VARCHAR(26), GETDATE(), 109) Apr 28 2006 12:32:29:253PM 1
MM-DD-YY USA SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY] 01-01-06
MM-DD-YYYY USA SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY] 01-01-2006
YY/MM/DD - SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD] 98/11/23
YYYY/MM/DD - SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD] 1998/11/23
YYMMDD ISO SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD] 980124
YYYYMMDD ISO SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD] 19980124
DD Mon YYYY HH:MM:SS:MMM(24h) 1 Europe default + milliseconds SELECT CONVERT(VARCHAR(24), GETDATE(), 113) 28 Apr 2006 00:34:55:190 1
HH:MI:SS:MMM(24H) - SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)] 11:34:23:013
YYYY-MM-DD HH:MI:SS(24h) ODBC Canonical SELECT CONVERT(VARCHAR(19), GETDATE(), 120) 1972-01-01 13:42:24
YYYY-MM-DD HH:MI:SS.MMM(24h) ODBC Canonical
(with milliseconds)
SELECT CONVERT(VARCHAR(23), GETDATE(), 121) 1972-02-19 06:35:24.489
YYYY-MM-DDTHH:MM:SS:MMM ISO8601 SELECT CONVERT(VARCHAR(23), GETDATE(), 126) 1998-11-23T11:25:43:250
DD Mon YYYY HH:MI:SS:MMMAM 1 Kuwaiti SELECT CONVERT(VARCHAR(26), GETDATE(), 130) 28 Apr 2006 12:39:32:429AM 1
DD/MM/YYYY HH:MI:SS:MMMAM Kuwaiti SELECT CONVERT(VARCHAR(25), GETDATE(), 131) 28/04/2006 12:39:32:429AM


Here are some more date formats that does not come standard in SQL Server as part of the CONVERT function.

Extended Date Formats
Date Format SQL Statement Sample Output
YY-MM-DD
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD]
99-01-24
YYYY-MM-DD
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD]
1999-01-24
MM/YY SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY]
SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY]
08/99
MM/YYYY SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY] 12/2005
YY/MM SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM] 99/08
YYYY/MM SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM] 2005/12
Month DD, YYYY 1 SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY] July 04, 2006 1
Mon YYYY 1 SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY] Apr 2006 1
Month YYYY 1 SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY] February 2006 1
DD Month 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS [DD Month] 11 September 1
Month DD 1 SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD] September 11 1
DD Month YY 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY] 19 February 72 1
DD Month YYYY 1 SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY] 11 September 2002 1
MM-YY SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY]
SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY]
12/92
MM-YYYY SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY] 05-2006
YY-MM SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM]
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM]
92/12
YYYY-MM SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM] 2006-05
MMDDYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY] 122506
MMDDYYYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY] 12252006
DDMMYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY] 240702
DDMMYYYY SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY] 24072002
Mon-YY 1 SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mon-YY] Sep-02 1
Mon-YYYY 1 SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mon-YYYY] Sep-2002 1
DD-Mon-YY 1 SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY] 25-Dec-05 1
DD-Mon-YYYY 1 SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY] 25-Dec-2005 1

1 To make the month name in upper case, simply use the UPPER string function.


Happy SQL server

Happy Programming

Saturday, October 24, 2009

Windows Live multi Messenger 2009

The support for multi-WLM is not enabled by default, but can be easily turned on and enable with registry hack.

1. Run Registry Editor (regedit).

2. Navigate to the following registry key :
KEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows Live\Messenge

For 64-bit (x64) OS, go to following registry branch instead:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows Live\Messenger

3. In the right pane, right click on any blank space, select New on context menu, and then click on DWORD (32-bit) Value.

4. Name the new registry value entry as MultipleInstances.

5. Modify the MultipleInstances and set its value data as 1.

6. Start as many Windows Live Messenger windows as you want from Start Menu.



Note that you need a different Windows Live account (MSN Passport user name) and password in order to run two or more instances of Windows Live Messenger. If you have set automatically login upon running WLMessenger, an error may pop up when additional instance is run if your login is still signed in when new instance opening. Just change to your second WL Messenger login.


Happy Multi Messenger

Tuesday, September 29, 2009

join in SQL update statement ( update with match two tables)

join in SQL update statement

I had to update value from a staging table( this might be a temp table )
into another database table. Here is how I did it using a inner join in
the Update statement.



UPDATE Table1

SET Table1.Field1 = StagingTable.Field1

FROM Table1 INNER JOIN StagingTable ON Table1.Field2 = StagingTable.Field2



You can also add a where clause to this SQL:



WHERE StagingTable.Field3 IS NOT NULL

join in SQL update statement ( update with match two tables)

join in SQL update statement

I had to update value from a staging table( this might be a temp table )
into another database table. Here is how I did it using a inner join in
the Update statement.



UPDATE Table1

SET Table1.Field1 = StagingTable.Field1

FROM Table1 INNER JOIN StagingTable ON Table1.Field2 = StagingTable.Field2



You can also add a where clause to this SQL:



WHERE StagingTable.Field3 IS NOT NULL

Thursday, September 17, 2009

SQL SERVER – Add Column With Default Column

SQL SERVER – Add Column With Default Column


ALTER TABLE TestTable
ADD NewCol VARCHAR(50)
CONSTRAINT DF_TestTable_NewCol DEFAULT '' NOT NULL




ALTER TABLE Engineers
ADD book bit default '0'


Finally, you may wish to add a default constraint to an existing table. This uses the ALTER TABLE statement. The statement syntax is similar to that for adding a column specification except that in this case you specify that you wish to add a default constraint. As no column is being added, the previous default constraint syntax must be modified slightly to specify the name of the column being modified. The complete syntax is therefore:



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

ALTER TABLE table-name
ADD CONSTRAINT constraint-name DEFAULT default-value FOR column-name

Wednesday, September 16, 2009

Wednesday, September 09, 2009

Arabic Naat collection

Arabic Naat collection

Hasbi Rabbi







Naat Hasbi Rabbi 2




you came to me. Ramadan Nasheed




Mohammed Nabina




Qasida Burda Sharif in different five languages - Arabic Naat with Daff / dafli / duff

Saturday, August 08, 2009

SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored Procedure

Following code will help to find all the Stored Procedures (SP) which are related to one or more specific tables. sp_help and sp_depends does not always return accurate results.


SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'



this query will get all tables referenced in a store procedure.

SQL SERVER – Stored Procedure to display code (text) of Stored

SQL SERVER – Stored Procedure to display code (text) of Stored Procedure, Trigger, View or Object


How to see text/content/code of Stored Procedure. System stored procedure that prints the text of a rule, a default, or an unencrypted stored procedure, user-defined function, trigger, or view.

From this you can view Stored Procedure to display by text (code)

Syntax
sp_helptext @objname = 'sp_name'


sp_helptext @objname = 'sp_viewemp'

SQL Server 2000/2005 show all tables in a Database

Freom this Query you can see all the Tables in Database


Select table_name FROM Information_Schema.Tables

SQL Server List all the Stored Procedures of a Database

List all the Stored Procedures of Database and their Definitions
using T-SQL in SQL Server 2005/2008 | Sql Server
From this query you can view all Stored Procedures in DataBase.
-------------------------------------------------------------------------------
SELECT obj.Name as SPName,
modu.definition as SPDefinition,
obj.create_date as SPCreationDate
FROM sys.sql_modules modu
INNER JOIN sys.objects obj
ON modu.object_id = obj.object_id

WHERE obj.Name = 'stp_tblname'

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

Thursday, June 11, 2009

DataReader vs DataSet

DataReader
1. Its an connection oriented, whenever you want fetch the data from database that you need the connection. after fetch the data connection is diconnected.
2. Its an Read only format, you cann't update records.


DataSet
1. Its connectionless. whenever you want fetch data from database. its connects indirectly to the database and create a virtual database in local system. then disconnected from database.
2. Its easily read and write data from virtual database.

I think Dataset is the Best one................



*I like to do as little work as I can when I code, so I used to like the DataSet. It can be filled and ready to go in just 3 lines of code, and then iterated using a nice, simple foreach loop (it’s even easier if you use typed DataSets!). It’s a nice collection to work with. But often, performance is required at the expense of elegance -- especially on a performance-critical Web application*

The DataSet actually uses a DataReader to populate itself. A DataReader is a lean, mean access method that returns results as soon as they’re available, rather than waiting for the whole of the query to be populated into a DataSet. This can boost your application performance quite dramatically, and, once you get used to the methodology, can be quite elegant in itself.
12:20 PM


*The disconnected environment.

In the disconnected environment, the application keeps open the connection for one kind of work on the database and every time it needs to work again, the connection must be reopened. It's costly to open a connection in a network environment due to the communication protocols*