SQL SERVER – List All the Tables for All Databases Using System Tables

sp_msforeachdb 'select "?" AS db, * from [?].sys.tables'

Table-Valued Parameters in Sql Server

This is a new feature intorduced in Sql Server 2008. Table-Valued Parameters provides option for the Client Applications to pass multiple rows of Data to Stored Procedure.

Prior to this, if we were needed to pass multiple rows of Data from client application to Sql Server, then we use to model the input data as xml and pass it to the stored procedure and in Stored Procedure convert this xml to a table variable/temporary table.

In this article we will not only go over this Table-Valued Parameter we will also understand how to call the Stored Procedure with Table-Valued Parameter from Sql Server and C# .Net Code.

Table-Valued User Defined Data Type
First we need to Create a User Defined Table Type which can be reused in multiple stored procedures as input table parameter data type.

CREATE TYPE dbo.CustomerTableType AS TABLE
(
[CustomerID] INT,
[Name] VARCHAR(50)
)
GOStored Procedure with Table-Valued input Parameter
Now let us create a simple stored procedure which takes CustomerType User Definde Table Type which we have created previously

CREATE PROCEDURE dbo.GetCustomerDetails
(
@Customers AS dbo.CustomerTableType READONLY
)
AS
BEGIN
SET NOCOUNT ON

SELECT *
FROM @Customers
ENDUsing Stored Procedure With Table Valued Parameter in Sql Server

Declare @CustomerDetails As dbo.CustomerTableType
Insert Into @CustomerDetails Values(1,'Narsimha'),
(2,'Mithun'),
(3,'Sachin')

Exec dbo.GetCustomerDetails @CustomerDetails
GO
Result:
CustomerID Name
----------- ------------
1 Narsimha
2 Mithun
3 Sachin

Calling Stored Procedure with Table Valued Parameter from C# Code
Below is a sample C# Code example which calls the GetCustomerDetails Stored Procedure with Table-Valued Parameter which is created in the previous section.


using System;
using System.Data;
using System.Data.SqlClient;
namespace TableValuedParameter
{
class Program
{
static void Main(string[] args)
{
//Create and open a connection object
SqlConnection conn = new SqlConnection(
"ENTER A VALID CONNECTION STRING");
conn.Open();

//Create a command object specify the stored procedure
SqlCommand cmd = new SqlCommand("dbo.GetCustomerDetails", conn);
cmd.CommandType = CommandType.StoredProcedure;
//PrePare the rows of Data to be Passed to the Stored Procedure
DataTable dataTable = new DataTable("Customer");
dataTable.Columns.Add("Id", typeof(Int32));
dataTable.Columns.Add("Name", typeof(string));
dataTable.Rows.Add(1, "Narsimha");
dataTable.Rows.Add(2, "Mithun");
dataTable.Rows.Add(3, "Sachin");

//Add the Table-Valued Parameter value to the Command Object
SqlParameter param = new SqlParameter("@Customers", dataTable);
param.SqlDbType = SqlDbType.Structured;
cmd.Parameters.Add(param);
// Execute the command
SqlDataReader rdr = cmd.ExecuteReader();
// iterate through results, printing each record to the console
while (rdr.Read())
{
Console.WriteLine("Employee ID: {0} Name: {1}"
,rdr["CustomerID"],rdr["Name"]);
}
conn.Close();
rdr.Close();
Console.ReadKey();
}
}
}

Result Seen on the console:
Employee ID: 1 Name: Narsimha
Employee ID: 2 Name: Mithun
Employee ID: 3 Name: Sachin

EXECUTE Permission ON Table Type User Defined Type: Eventhough we have Execute permission on the Stored Procedure, we still need to give Execute permission on the Table Type User Defined Type. Below is the syntax for granting execute persmission on the Table Type User Defined Type

GRANT EXECUTE ON TYPE::dbo.CustomerTableType TO UserName

New Features in Sql Server 2008

Following are the some of the new features of the Sql Server 2008 which are very helpful to the Sql Developers

1) Table-Valued Parameters in Sql Server: provides option for the Client Applications to pass multiple rows of Data to Sql Server.

Prior to this, if we were needed to pass multiple rows of Data from client application to Sql Server, then we use to model the input data as xml /comma separated values and pass it to the stored procedure and in Stored Procedure convert this xml/comma separated values to a table variable/temporary table.

You can find detailed information on the Table-Valued Parameters and also on calling Stored Procedure with Table-Valued Parameter from Sql Server and C# .Net Code @ http://beginsql.wordpress.com/2011/09/09/table-valued-parameters-in-sql-server/

2) Variable declaration allows initialization:

Prior to Sql Server 2008 to initialize a variable, we needed to first declare the variable and then we can initialize it by using SET/SELECT statement as shown below:

DECLARE @COUNT INT
SET @COUNT =100Now in Sql Server 2008 Variable declaration allows initialization similar to the one we do in C#. Now instead of writing two statements, we can write a single statement as below:

DECLARE @COUNT INT =1003) Insert multiple rows using single INSERT Statement

To understand this feature first create an Employee Table by using the below script:

CREATE TABLE DBO.Employee ( Id INT, Name VARCHAR(50) )Prior to Sql Server 2008, to insert multiple records we use to write statements like below:

INSERT INTO dbo.Employee VALUES(1,'Narsimha')
INSERT INTO dbo.Employee VALUES(2,'Mithun')
INSERT INTO dbo.Employee VALUES(3,'Sachin')Now in Sql Server 2008 we can accomplish the same by writing script like below:

INSERT INTO dbo.Employee VALUES(1,'Narsimha') ,(2,'Mithun') ,(3,'Sachin')

4) Arithematic Assignment Operators
Now Sql Server 2008 also supports the Arithematic Assignment Operators like the below ones:

Operator Usage Description
+= SET @x+=@y Same as: SET @x = @x + @y
-= SET @x-=@y Same as: SET @x = @x - @y
*= SET @x*=@y Same as: SET @x = @x * @y
/= SET @x/=@y Same as: SET @x = @x / @y
%= SET @x%=@y Same as: SET @x = @x % @yExample:

DEClARE @x INT =2 ,@y INT = 2
SET @x+=@y
SELECT @x as x,@y as y
Result:
x y
----------- -----------
4 25)

MERGE Statement

Merge statement is one of the interesting T-Sql enhancements of Sql Server 2008. With Merge statement we can very efficiently perform multiple DML operations like INSERT, UPDATE and DELETE on the target table data based on Source table data and the join condition specified between them.

You can find detailed information on MERGE Statement @ http://beginsql.wordpress.com/2011/09/24/merge-statemen…ql-server-2008/

6) Sparse Column

Sparse Column is one more new feature introduced in SQL SERVER 2008. Storing a null value in a sparse column doesn’t take any space, but storing a non-null value in sparse column takes 4 bytes extra space than the non-sparse columns of the same data type.

Difference between DateTime and DateTime2 DataType

DateTime2 is the new Data Type introduced in Sql Server 2008 for storing Date and Time value. As per MSDN, Microsoft Suggests to use this new Data Type for new work instead of DateTime. Following table summarizes some of the major difference between this new DateTime2 and the old DateTime Data Type.

DateTime DateTime2[(n)]
Min Value 1753-01-01 00:00:00 0001-01-01 00:00:00
Max Value 9999-12-31 23:59:59.997 9999-12-31 23:59:59.9999999
Storage Size 8 Bytes 6 to 8 bytes
Note: Parameter n is optional and if it is not specified then fractional seconds precision is 7 digit and it can be from 0 to 7 digit.
For fractional seconds precision <3, takes 6 bytes For fractional seconds precision 3 or 4 it will take 7 bytes For fractional seconds precision >4 it will take 8 bytes
Usage Declare @now datetime Declare @now datetime2(7)
Compliance Is not an ANSI/ISO compliant Is an ANSI/ISO compliant
Current Date and Time function GetDate() – It returns DB Current Date and Time of DateTime Data Type
Example: SELECT GETDATE()
Result: 2011-09-16 13:23:18.767 SYSDATETIME()- It returns DB Current Date and Time of DateTime2 Data Type
Example: SELECT SYSDATETIME()
Result: 2011-09-16 13:23:18.7676720
+/- days WORKS
Example: DECLARE @nowDateTime DATETIME = GETDATE()
SELECT @nowDateTime + 1
Result: 2011-09-17 13:44:31.247 FAILS – Need to use only DateAdd function
Example: DECLARE @nowDateTime2 DATETIME2= SYSDATETIME()
SELECT @nowDateTime2+1
Result: Msg 206, Level 16, State 2, Line 2
Operand type clash: datetime2 is incompatible with int

DateTime2 with fractional seconds precision of 3 is same as datetime data type. And DateTime(3) uses 7 bytes of storage instead of 8 byte which old datetime datatype uses and it also provides higer date range (i.e. 0001-01-01 to 9999-12-31 ) compared to DateTime data type. Now let us see this with an example:

DECLARE @nowDateTime DATETIME = GETDATE(),
@nowDateTime2 DATETIME2(3)= SYSDATETIME()

SELECT DATALENGTH(@nowDateTime) 'DateTime Storage Size' ,
DATALENGTH(@nowDateTime2) 'DateTime2(3) Storage Size'

Result:
DateTime Storage Size DateTime2 Storage Size
--------------------- ----------------------
8 7

Merge Statement in Sql Server 2008

Merge statement is one of the interesting T-Sql enhancements of Sql Server 2008. With Merge statement we can very efficiently perform multiple DML operations like INSERT, UPDATE and DELETE on the target table data based on Source table data and the join condition specified between them.

This feature is very useful in a scenario where we want to synchronize the data in the target table with source table data. In earlier versions of sql server to achieve this synchronization we would have scanned the source and target tables multiple times(i.e. ones for inserting the new records , second time for updating the matching records and third time for deleting the records in the destination table which are not present in the source table), but with Merge statement we can achieve all this with single statement and with only one time looking-up of the source and target tables.

Let us understand the Merge statement with a simple example.

First create a Source Table with Sample Data:

CREATE TABLE dbo.EmployeeSource(Id INT, Name VARCHAR(50))
GO
INSERT INTO dbo.EmployeeSource
VALUES(1,'Basavaraj Biradar') ,
(3,'Monty')
GO
SELECT * FROM dbo.EmployeeSource WITH(NOLOCK)
GO
--Source Table Data
Id Name
1 Basavaraj Biradar
3 MontyNow create a Target Table with Sample Data:

CREATE TABLE dbo.EmployeeTarget(Id INT, Name VARCHAR(50))
GO
INSERT INTO dbo.EmployeeTarget
VALUES(1,'Basavaraj') ,
(2,'Shashank')
GO
SELECT * FROM dbo.EmployeeTarget WITH(NOLOCK)
GO
--Target Table Data
Id Name
1 Basavaraj
2 ShashankNow Syncronize the target table with source table data using the below Merge statement:

MERGE dbo.EmployeeTarget AS T
USING dbo.EmployeeSource AS S
ON T.Id = S.Id
WHEN MATCHED THEN -- Matching Employee record
UPDATE SET T.NAME = S.NAME
WHEN NOT MATCHED BY TARGET THEN
-- Employee record presnet in Source but not in target
INSERT (Id, Name)
VALUES (S.Id, S.Name)
WHEN NOT MATCHED BY SOURCE THEN
-- Employee record present in destination but not in source
DELETE;Target table data after executing the above Merge statement:

SELECT * FROM dbo.EmployeeTarget WITH(NOLOCK)
GO
--Target Table Data
Id Name
1 Basavaraj Biradar
3 Monty

FileUpload Save Images in Database in ASP.NET C# VB.NET

In this example i am Uploading Images using FileUpload Control and saving or storing them in SQL Server database in ASP.NET with C# and VB.NET.

Database is having a table named Images with three columns.
1. ID Numeric Primary key with Identity Increment.
2. ImageName Varchar to store Name of Image.
3. Image Image to store image in binary format.

Using C#.Net


protected void btnUpload_Click(object sender, EventArgs e)
{
string strImageName = txtName.Text.ToString();
if (FileUpload1.PostedFile != null &&
FileUpload1.PostedFile.FileName != "")
{
byte[] imageSize = new byte
[FileUpload1.PostedFile.ContentLength];
HttpPostedFile uploadedImage = FileUpload1.PostedFile;
uploadedImage.InputStream.Read
(imageSize, 0, (int)FileUpload1.PostedFile.ContentLength);

// Create SQL Connection
SqlConnection con = new SqlConnection();
con.ConnectionString = ConfigurationManager.ConnectionStrings
["ConnectionString"].ConnectionString;

// Create SQL Command

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "INSERT INTO Images(ImageName,Image)" +
" VALUES (@ImageName,@Image)";
cmd.CommandType = CommandType.Text;
cmd.Connection = con;

SqlParameter ImageName = new SqlParameter
("@ImageName", SqlDbType.VarChar, 50);
ImageName.Value = strImageName.ToString();
cmd.Parameters.Add(ImageName);

SqlParameter UploadedImage = new SqlParameter
("@Image", SqlDbType.Image, imageSize.Length);
UploadedImage.Value = imageSize;
cmd.Parameters.Add(UploadedImage);
con.Open();
int result = cmd.ExecuteNonQuery();
con.Close();
if (result > 0)
lblMessage.Text = "File Uploaded";
GridView1.DataBind();
}
}


Using VB.Net

Protected Sub btnUpload_Click
(ByVal sender As Object, ByVal e As EventArgs)

Dim strImageName As String = txtName.Text.ToString()
If FileUpload1.PostedFile IsNot Nothing AndAlso
FileUpload1.PostedFile.FileName <> "" Then

Dim imageSize As Byte() = New Byte
(FileUpload1.PostedFile.ContentLength - 1) {}

Dim uploadedImage__1 As HttpPostedFile =
FileUpload1.PostedFile

uploadedImage__1.InputStream.Read(imageSize, 0,
CInt(FileUpload1.PostedFile.ContentLength))

' Create SQL Connection
Dim con As New SqlConnection()
con.ConnectionString =
ConfigurationManager.ConnectionStrings
("ConnectionString").ConnectionString

' Create SQL Command

Dim cmd As New SqlCommand()
cmd.CommandText = "INSERT INTO Images
(ImageName,Image) VALUES (@ImageName,@Image)"
cmd.CommandType = CommandType.Text
cmd.Connection = con

Dim ImageName As New SqlParameter
("@ImageName", SqlDbType.VarChar, 50)
ImageName.Value = strImageName.ToString()
cmd.Parameters.Add(ImageName)

Dim UploadedImage__2 As New SqlParameter
("@Image", SqlDbType.Image, imageSize.Length)
UploadedImage__2.Value = imageSize
cmd.Parameters.Add(UploadedImage__2)
con.Open()
Dim result As Integer = cmd.ExecuteNonQuery()
con.Close()
If result > 0 Then
lblMessage.Text = "File Uploaded"
End If
GridView1.DataBind()
End If
End Sub

Programattically render pdf from ReportViewer

Dim warnings As Warning() = Nothing
Dim streamids As String() = Nothing
Dim mimeType As String = Nothing
Dim encoding As String = Nothing
Dim extension As String = Nothing
Dim bytes As Byte()

'First delete existing file
Dim filepath As String = FolderLocation & "PCSummary.PDF"
File.Delete(filepath)
'Then create new pdf file
bytes = ReportViewer1.LocalReport.Render("PDF", Nothing, mimeType, _
encoding, extension, streamids, warnings)
Dim fs As New FileStream(FolderLocation & "PCSummary.PDF", FileMode.Create)
fs.Write(bytes, 0, bytes.Length)
fs.Close()