Mighty Code: SQL vs. CLR vs. Standalone

I recently had some friends who are just starting out in the world of development ask a common question. "Can I do a while loop in SQL?" This, of course, sparked a lengthy conversation about the dangers of cursors, merits of CLR procs, and logic flow in general. When thinking about it later, I realized that I get this question in one form or another from time to time and it might be helpful to have an example to point them to.

So here it is. Examples of how to perform the same actions in:
SQL Procs
vs
CLR Procs (C#)
vs
Windows App (C# and VB)
Eventually I will add LINQ examples for windows apps.

Code samples are color coded for easy identification.
I decided to give examples of how to perform the following actions:
1. Mass Delete of all records in two tables
2. Mass Insert of records from one table to another
3. Mass update of all records in one table
4. Mass update of the records in one table by criteria
5. Looping through all records in one table and for each record, performing both an insert on a second table and update on the current table

So first we need to create these tables we will play with. Run the following in SQL Management Studio.

USE [master]

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test_table1]') AND type in (N'U'))
DROP TABLE [dbo].[test_table1]
GO

CREATE TABLE [dbo].[test_table1] (
id int,
description varchar(10),
status varchar(10)
)
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[test_table2]') AND type in (N'U'))
DROP TABLE [dbo].[test_table2]
GO

CREATE TABLE [dbo].[test_table2] (
id int,
description varchar(10),
status varchar(10)
)

OK, so lets get started. You can run these objects after creation and see that the results are the same. Before running CLR Procs, you must install the dll. Details on that and on building windows applications are outside the scope of this post. I am strictly concentrating on what the code looks like in the various technologies.

1. Mass Delete of all records in two tables

SQL Proc:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_MassDelete]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_MassDelete]
GO

create procedure SP_MassDelete
AS
BEGIN
delete from test_table1
delete from test_table2
END
GO

C# function:
SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=master;Integrated Security=SSPI;");

private void btnMassDelete_Click(object sender, EventArgs e)
{
try
{
//this is the command for deleting the records
SqlCommand cmd = new SqlCommand("delete from test_table1", conn);

conn.Open();

//now we run the statements
cmd.ExecuteNonQuery();
cmd.CommandText = "delete from test_table2";
cmd.ExecuteNonQuery();

}
catch (Exception ex)
{
MessageBox.Show("Error in btnMassDelete_Click: " + ex.ToString());
}
finally
{
//remember to close the connection regardless of errors
conn.Close();
}

}

VB function:
Private conn As SqlConnection = New SqlConnection("Data Source=(local);Initial Catalog=master;Integrated Security=SSPI;")

Private Sub btnMassDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMassDelete.Click
Try
'this is the command for deleting the records
Dim cmd As SqlCommand = New SqlCommand("delete from test_table1", conn)

conn.Open()

'now we run the statements
cmd.ExecuteNonQuery()
cmd.CommandText = "delete from test_table2"
cmd.ExecuteNonQuery()

Catch ex As Exception
MessageBox.Show("Error in btnMassDelete_Click: " + ex.ToString())

Finally
'remember to close the connection regardless of errors
conn.Close()
End Try

End Sub

2. Mass Insert of records from one table to another

SQL Proc:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_MassInsert]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_MassInsert]
GO

create procedure SP_MassInsert
AS
BEGIN
insert into test_table1
select object_id,substring(name,1,10),'Inserted' from sys.objects

END

C# function:
private void btnMassInsert_Click(object sender, EventArgs e)
{
try
{
//this is the command for inserting the records
SqlCommand cmd = new SqlCommand("insert into test_table1 select object_id,substring(name,1,10),'Inserted' from sys.objects", conn);

conn.Open();

//now we run the command
cmd.ExecuteNonQuery();

}
catch (Exception ex)
{
MessageBox.Show("Error in btnMassInsert_Click: " + ex.ToString());
}
finally
{
//remember to close the connection regardless of errors
conn.Close();
}

}

VB function:
Private Sub btnMassInsert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMassInsert.Click
Try
'this is the command for inserting the records
Dim cmd As SqlCommand = New SqlCommand("insert into test_table1 select object_id,substring(name,1,10),'Inserted' from sys.objects", conn)

conn.Open()

'now we run the command
cmd.ExecuteNonQuery()

Catch ex As Exception
MessageBox.Show("Error in btnMassInsert_Click: " + ex.ToString())

Finally
'remember to close the connection regardless of errors
conn.Close()
End Try
End Sub

3. Mass Update of all records in one table

SQL Proc:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_MassUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_MassUpdate]
GO

create procedure SP_MassUpdate
AS
BEGIN
update test_table1
set status='Updated'
END
GO

C# function:
private void btnMassUpdate_Click(object sender, EventArgs e)
{
try
{
//this is the command for updating the records
SqlCommand cmd = new SqlCommand("update test_table1 set status='Updated'", conn);

conn.Open();

//now we run the command
cmd.ExecuteNonQuery();

}
catch (Exception ex)
{
MessageBox.Show("Error in btnMassUpdate_Click: " + ex.ToString());
}
finally
{
//remember to close the connection regardless of errors
conn.Close();
}

}

VB function:
Private Sub btnMassUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMassUpdate.Click
Try
'this is the command for updating the records
Dim cmd As SqlCommand = New SqlCommand("update test_table1 set status='Updated'", conn)

conn.Open()

'now we run the command
cmd.ExecuteNonQuery()

Catch ex As Exception
MessageBox.Show("Error in btnMassUpdate_Click: " + ex.ToString())

Finally
'remember to close the connection regardless of errors
conn.Close()
End Try

End Sub

4. Mass Update of the records in one table by criteria

SQL Proc:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_MassUpdatebyCriteria]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_MassUpdatebyCriteria]
GO

create procedure SP_MassUpdatebyCriteria
AS
BEGIN
update test_table1
set status='Big'
where id>100
END
GO

C# function:
private void btnMassUpdatebyCriteria_Click(object sender, EventArgs e)
{
try
{
//this is the command for updating the records
SqlCommand cmd = new SqlCommand("update test_table1 set status='Big' where id>100", conn);

conn.Open();

//now we run the command
cmd.ExecuteNonQuery();

}
catch (Exception ex)
{
MessageBox.Show("Error in btnMassUpdatebyCriteria_Click: " + ex.ToString());
}
finally
{
//remember to close the connection regardless of errors
conn.Close();
}
}

VB function:
Private Sub btnMassUpdatebyCriteria_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMassUpdatebyCriteria.Click
Try

'this is the command for updating the records
Dim cmd As SqlCommand = New SqlCommand("update test_table1 set status='Big' where id>100", conn)

conn.Open()

'now we run the command
cmd.ExecuteNonQuery()

Catch ex As Exception
MessageBox.Show("Error in btnMassUpdatebyCriteria_Click: " + ex.ToString())

Finally
'remember to close the connection regardless of errors
conn.Close()
End Try
End Sub

5. Looping through all records in one table and for each record, performing both an insert on a second table and update on the current table

SQL Proc:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_Loop]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SP_Loop]
GO

create procedure SP_Loop
AS
BEGIN

declare @id int,
@desc varchar(10),
@status varchar(10)

declare c_loop cursor for
select id,description,status
from test_table1
where status='Big'
for update of status

open c_loop
fetch next from c_loop into @id,@desc,@status
while @@fetch_status=0
BEGIN
insert into test_table2
select (@id/2),@desc,convert(varchar(10),@id)

update test_table1
set status='Archived'
where current of c_loop

fetch next from c_loop into @id,@desc,@status
END
close c_loop
deallocate c_loop
END
GO

CLR Proc:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CLR_Loop()
{
SqlConnection conn = new SqlConnection();
try
{
//we use the existing connection inside SQL
conn.ConnectionString = "Context Connection=true";

//this is the command for getting the records and inserting and updating
SqlCommand cmd = new SqlCommand("select id,description,status from test_table1 where status='Big'", conn);

//we must issue an open to the connection even though in reality it is already an active connection
conn.Open();

//we can't use a data reader, since we can only have one command active on the connection at a time
//so we will use a data adapter to get the records and store them in a datatable in memory so we can work with them
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);

//now we will loop through each record and work on it
foreach (DataRow dr in dt.Rows)
{
//first we will insert a new records elsewhere
cmd.CommandText = "insert into test_table2 select (" + dr["id"].ToString() + "/2),'" + dr["description"].ToString() + "',convert(varchar(10),'" + dr["id"].ToString() + "')";
cmd.ExecuteNonQuery();

//then we will update the existing record
cmd.CommandText = "update test_table1 set status='Archived' where id=" + dr["id"].ToString();
cmd.ExecuteNonQuery();

}
}
catch (Exception ex)
{
SqlContext.Pipe.Send("Error in CLR proc CLR_Loop: " + ex.ToString());
}
finally
{
//remember to close the connection regardless of errors
conn.Close();
}
}
};

C# function:
private void btnLoop_Click(object sender, EventArgs e)
{
SqlDataReader rdr = null;
SqlConnection conn2=new SqlConnection(conn.ConnectionString);
try
{
//this is the command for getting the records
SqlCommand cmd = new SqlCommand("select id,description,status from test_table1 where status='Big'", conn);

//this is the command for inserting and updating
SqlCommand cmd2 = new SqlCommand("",conn2 );

conn.Open();
conn2.Open();

rdr = cmd.ExecuteReader();

while (rdr.Read())
{
//first we will insert a new records elsewhere
cmd2.CommandText = "insert into test_table2 select (" + rdr.GetInt32(0).ToString() + "/2),'" + rdr.GetString(1) + "',convert(varchar(10),'" + rdr.GetInt32(0).ToString() + "')";
cmd2.ExecuteNonQuery();

//then we will update the existing record
cmd2.CommandText = "update test_table1 set status='Archived' where id=" + rdr.GetInt32(0).ToString();
cmd2.ExecuteNonQuery();
}
}
catch (Exception ex)
{
MessageBox.Show("Error in btnLoop_Click: " + ex.ToString());
}
finally
{
//remember to close the reader and connection regardless of errors
if (rdr != null)
{
rdr.Close();
}
conn.Close();
conn2.Close();
}
}

VB function:
Private Sub btnLoop_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLoop.Click
Dim rdr As SqlDataReader = Nothing
Dim conn2 As SqlConnection = New SqlConnection(conn.ConnectionString)
Try
'this is the command for getting the records
Dim cmd As SqlCommand = New SqlCommand("select id,description,status from test_table1 where status='Big'", conn)

'this is the command for inserting and updating
Dim cmd2 As SqlCommand = New SqlCommand("", conn2)

conn.Open()
conn2.Open()

rdr = cmd.ExecuteReader()

Do While rdr.Read()
'first we will insert a new records elsewhere
cmd2.CommandText = "insert into test_table2 select (" + rdr.GetInt32(0).ToString() + "/2),'" + rdr.GetString(1) + "',convert(varchar(10),'" + rdr.GetInt32(0).ToString() + "')"
cmd2.ExecuteNonQuery()

'then we will update the existing record
cmd2.CommandText = "update test_table1 set status='Archived' where id=" + rdr.GetInt32(0).ToString()
cmd2.ExecuteNonQuery()
Loop

Catch ex As Exception
MessageBox.Show("Error in btnLoop_Click: " + ex.ToString())

Finally
'remember to close the reader and connection regardless of errors
If Not rdr Is Nothing Then
rdr.Close()
End If
conn.Close()
conn2.Close()
End Try
End Sub

One thing you will notice is that the SQL code is always smallest. But that doesn't mean it is necessarily the best way to do something.

Enjoy!

Did this help you? If so, please leave a comment!

Mighty Code

Tuesday, September 14, 2010

SQL vs. CLR vs. Standalone

No comments:

Post a Comment

SQL 2022 TSQL snapshot backups!

Followers