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

ASP.NET - display records to grid view

Reference:

http://www.codersource.net/asp_net_grid_view_whidbey.aspx

Below is an example of using C#/ASP.NET to display content to a gridview (need to specify the object of "GridView1" in design view of ASP.NET first):

protected void Page_Load(object sender, EventArgs e)
{
if(!Page.IsPostBack)
{
BindData();
}
}

public void BindData()
{ // local variables
SqlConnection conn = null;
SqlCommand cmd = null;

try
{
conn = new SqlConnection("server=xxx;User ID=xxx;Password=xxx");
conn.Open();

cmd = new SqlCommand("select top(100) xxxx", conn);

SqlDataAdapter mySqlAdapter = new SqlDataAdapter(cmd);
DataSet myDataSet = new DataSet();
mySqlAdapter.Fill(myDataSet);

GridView1.DataSource = myDataSet;
GridView1.DataBind();

if (conn != null)
{
conn.Close(); conn = null;
}
}
catch (Exception ex)
{
if (conn != null)
{
conn.Close(); conn = null;

strLog = "[ (!!!Fail) Error=" + ex.Message + "]";
Response.Write(strLog);

}
}

Use code as below to add horizontal scrolling


// add code of GridView here


Use code as below to edit content of a row
1. Define functions


2. Function of Edit:

protected void GridView_MatchingServer_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView_MatchingServer.EditIndex = e.NewEditIndex;
BindData();
}

3. Function of Update
Add code as below to retrieve content of field being edited.

GridViewRow row = GridView_MatchingServer.Rows[e.RowIndex];
strMess1 = ((TextBox)(row.Cells[1].Controls[0])).Text; // RecID
strMess2 = ((TextBox)(row.Cells[3].Controls[0])).Text; // content of field

Where Cells[1] and Cells[3] represent the 1st and 3rd columns.

ASP.NET - save content as ,excel file

Below is an example which indicates how to save content to excel file. It will pop-up a window to ask user to specify proper directory to store the file.

protected void Button1_Click(object sender, EventArgs e)
{
// make sure nothing is in response stream
Response.Clear();
Response.Charset = "";

// set MIME type to be Excel file.
Response.ContentType = "application/vnd.ms-excel";

// add a header to response to force download (specifying filename)
Response.AddHeader("Content-Disposition", "attachment; filename=\"MyFile.xls\"");

// Send the data. Tab delimited, with newlines.
Response.Write("Col1\tCol2\tCol3\tCol4\n");

Response.Write("Data 1\tData 2\tData 3\tData 4\n");
Response.Write("Data 1\tData 2\tData 3\tData 4\n");
Response.Write("Data 1\tData 2\tData 3\tData 4\n");
Response.Write("Data 1\tData 2\tData 3\tData 4\n");

// Close response stream.
Response.End();
}

SQL - Encryption / decryption

Today find a web page which provides example about usage of symmetric and asymmetric for SQL encryption:

http://www.mssqltips.com/tip.asp?tip=1886&home