SQL - retrieve variable in Stored Procedure

Below are some examples of retrieve content from a table within a SP:

select @iNumRec = count(*) from XXXX where mess_content like '%test%'

select @MessID = a.mess_id from XXXX as a where a.service_timestamp>'2009-12-31'

select @MessID = a.mess_id from XXXX as a order by service_timestamp desc

C# - example of string manipulation

[Convert string to decimal]
string s = "123.45";
decimal de = decimal.Parse(s);

[Convert string to integer]
string s = "123";
int iSIN = Int32.Parse(s);

[Read field of tinyint from DB and convert to int and string]
int iTemp = (int)reader_src.GetOrdinal(g_strField[iLpCnt]);
strTemp = iTemp.ToString();

[Format integer to string with fixed length]
String.Format("{0:X}", iRecID).PadLeft(5, '0')

[Convert byte array to string]
byte [] szContent;
szContent = System.Convert.FromBase64String(strContentIn);
System.Text.UTF8Encoding enc = new System.Text.UTF8Encoding();
string strLog = enc.GetString(szContent);

SQL - usage of Transaction and Lock

Below is an example of using "Transaction" / "Lock" to update / insert a record

{
Random random = new Random();
strTxID = "TX_" + random.Next(1000).ToString();

strCmd = @"BEGIN TRANSACTION " + strTxID;
cmd.CommandText = strCmd;
cmd.ExecuteNonQuery();

// check if record with specified recid already exist
strCmd = "select count(*) as NumRec from XXX with (HOLDLOCK) where SIN='...
cmd.CommandText = strCmd;
iNumRec = (int)cmd.ExecuteScalar();
....

// form value of SID (session ID)
strCmd = "insert into XXX with (HOLDLOCK) (SID,...
cmd.CommandText = strCmd;
cmd.ExecuteNonQuery();
…

// commit Tx
strCmd = @"COMMIT TRANSACTION " + strTxID;
cmd.CommandText = strCmd;
cmd.ExecuteNonQuery();
....
}

SQL - memory usage

Ref URL:
http://www.sqlservercentral.com/articles/Performance+Tuning/analyzingmemoryrequirementsforsqlserver/2498/

http://www.codeproject.com/KB/database/Dynamic_Management_Views.aspx

SQL - Usage of SMO

Below is an example of using SMO (by using C#) to check field(s) of SQL Database:

1. Download SMO Library and install the tool to the SQL server

2. Need to add below asemblies (add reference) for the C# program:
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoEnum
Microsoft.SqlServer.SqlEnum

3. Include the following:
using Microsoft.SqlServer.Management.Smo;

4. Example to login server
Server SrcServer = new Server(strSrcServer); // Server myServer = new Server(@"ARSHADALI\SQL2008");

//Using SQL Server authentication
SrcServer.ConnectionContext.LoginSecure = false;
SrcServer.ConnectionContext.Login = "sa";
SrcServer.ConnectionContext.Password = "boni_2005";

SrcServer.ConnectionContext.Connect();
if (SrcServer.ConnectionContext.IsOpen)
{
....

// close connection
SrcServer.ConnectionContext.Disconnect();
}

5. Example to check property of "field":

Server svr = new Server();
//strLog = svr.Name + " " + svr.Information.VersionString;
//MessageBox.Show(strLog);

foreach (Database myDatabase in svr.Databases)
{
//strLog = myDatabase.Name;
//MessageBox.Show(strLog);

if (myDatabase.Name == "MyClick")
{
foreach (Table myTbl in myDatabase.Tables)
{
if (myTbl.Name == "MMS_Usage")
{
foreach (Column myCol in myTbl.Columns)
{
strLog = "[" + myCol.Name + "],[" + myCol.DataType.ToString() + "]";
MessageBox.Show(strLog);
}

}
}
}
}

SQL - CLR (access DBase through Context)

Below is an example which shows how to use "context" to access dbase within a CLR assembly

{
...
[Microsoft.SqlServer.Server.SqlFunction]
public static int DBBackup()
{ // local variables
SqlConnection conn = null;
SqlCommand cmd = null;

try
{ conn = new SqlConnection("context connection=true");
conn.Open();

strCmd = "insert DBase_Ctrl..dbase_backup_log (log_event) values('Test')";
cmd = new SqlCommand(strCmd, conn);
cmd.CommandText = strCmd;
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{ if (conn != null) { conn.Close(); conn = null; }
}
...
}

SQL - CLR (External Access)

To register a CLR assembly (with external access) for SQL, need to do the following

1. Set property of the Assembly to "external access" (by using Visual Studio)

2. Set "TRUSTWORTHY" of the database to ON
Use DBase_Ctrl
ALTER DATABASE DBase_Ctrl SET TRUSTWORTHY ON

3. Issue command as below in SSMS to register the assembly:

Use DBase_Ctrl
CREATE ASSEMBLY SQL_Backup FROM
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\SQL_Backup.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS