Jul 30, 2007

Connecting to a VS Team Foundation Server and fetching Work Item Details

  1. Download the VS TFS SDK (http://msdn2.microsoft.com/en-us/library/bb130146%28VS.80%29.aspx) or install the Visual Studio Team Suite. This will install the necessary assemblies required to access the VS TFS.
  2. Create a new VS 2005 Project.
  3. Browse to the following path (\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies) and add the following references

    -using Microsoft.TeamFoundation;
    -using Microsoft.TeamFoundation.Common;
    -using Microsoft.TeamFoundation.Client;
    -using Microsoft.TeamFoundation.WorkItemTracking.Client;
  4. Use the following code to access the VS TFS:

    TeamFoundationServer tfs = null;
    WorkItemStore wis = null;

    tfs = new TeamFoundationServer(tfsName, CredentialCache.DefaultCredentials);
    tfs.Authenticate();
    // Display the details about the TeamFoundationServer.
    richTextBox1.Text = "Team Foundation Server details:";
    richTextBox1.Text += " \n Server Name: " + tfs.Name;
    richTextBox1.Text += " \n Uri: " + tfs.Uri;
    richTextBox1.Text += " \n AuthenticatedUserDisplayName: " + tfs.AuthenticatedUserDisplayName;
    richTextBox1.Text += " \n AuthenticatedUserName: " + tfs.AuthenticatedUserName;
  5. To Access the Projects inside the TeamFoundationServer:

    wis = (WorkItemStore)tfs.GetService(typeof(WorkItemStore));
    //Iterate Through Projects
    foreach (Project tfs_project in wis.Projects)
    {
    treeView2.Nodes.Add(tfs_project.Name);
    }
  6. To Access the Workitems in the Projects:

    private void treeView2_NodeMouseDoubleClick(object sender, TreeNodeMouseClickEventArgs e)
    {
    string tfs_projectName = e.Node.Text.Trim(); //The project name

    //Perform WIQL Query
    WorkItemCollection wic = wis.Query(
    " SELECT [System.Id], [System.WorkItemType]," +
    " [System.State], [System.AssignedTo], [System.Title] " +
    " FROM WorkItems " +
    " WHERE [System.TeamProject] = '" + tfs_projectName +
    "' ORDER BY [System.WorkItemType], [System.Id]");


    foreach (WorkItem wi in wic)
    {
    TreeNode newnode = new TreeNode();
    newnode.Text = wi.Id + wi.Title + "-" + wi.Description;
    newnode.Tag = wi;
    treeView1.Nodes.Add(newnode);
    //treeView1.Nodes.Add(wi.Title + "[" + wi.Type.Name + "]" + wi.Description );
    }

Issues: While connecting from home I found that it takes really long, or sometimes times out during performing the following:
wis = (WorkItemStore)tfs.GetService(typeof(WorkItemStore))

The solution that I found in the net was :
Go to Internet Exploreer> Connections> LAN Settings
Instead of having a "Detect Proxy Automatically", put in a definite "Proxy setting"

Jul 5, 2007

Show a context menu when right clicking on a treeview

private void tvwMenu_MouseUp(object sender, MouseEventArgs e)
{
//Select Node When Right Clicked & Set Context Menu To Use
if( e.Button == MouseButtons.Right )
{
TreeNode node = tvwMenu.GetNodeAt( new Point( e.X, e.Y ) );
if( node != null )
{
TreeNodeMenuTag tag = (TreeNodeMenuTag)node.Tag;
tvwMenu.SelectedNode = node;
tvwMenu.ContextMenuStrip = (tag.isMenu) ? cmnuMenuNode :
cmnuProgramNode;
}
}
}

Enum Functions

  1. Iterating through an Enum:

    foreach (enmResponseInterval value in Enum.GetValues(typeof(enmResponseInterval)))
    {
    cmbXXX.Items.Add(value.ToString());
    }

  2. Conditional Switch Statement with Enumeration String Value
    public enum Product
    {
    Word,
    Outlook,
    Excel
    }

    public void Process(string productName)
    {
    switch ((Product)Enum.Parse(typeof(Product), productName.ToString()))
    {
    case Product.Excel:
    // Do something
    break;
    case Product.Outlook:
    // Do Something
    break;
    case Product.Word:
    // Do something
    break;
    }
    }

Jun 26, 2007

SQL Server Projects in C# - Some important links








  1. How to: Connect to a Database -
    http://msdn2.microsoft.com/en-us/library/ms165037(VS.80).aspx
  2. CLR Integration Security Issues -
    http://msdn2.microsoft.com/en-us/library/ms254940(VS.80).aspx
  3. Advantages of Using Managed Code to Create Database Objects -
    http://msdn2.microsoft.com/en-us/library/k2e1fb36(VS.80).aspx
  4. Item Templates for SQL Server Projects -
    http://msdn2.microsoft.com/en-us/library/eaf9k1as(VS.80).aspx
  5. Accessing Application Configuration Settings from SQL CLR -
    http://sqljunkies.com/Article/6CA864E9-E107-408E-B30A-4BA15B0CD11C.scuk
  6. Introducing SQL Server 2005's CLR Integration -
    http://www.devsource.com/print_article2/0,1217,a=151093,00.asp
  7. CLR Integration Programming Model Restrictions -
    http://msdn2.microsoft.com/en-us/library/ms403273.aspx
  8. Invoke WCF Service via SQL -
    http://blogs.msdn.com/saradhic/archive/2007/09/12/invoke-wcf-service-via-sql.aspx
  9. SQL CLR and WCF Compatibility -
    http://blogs.neudesic.com/blogs/shaun_collett/archive/2007/04/29/6048.aspx




  1. SQL Server Projects -
    http://msdn2.microsoft.com/en-us/library/c8dbfz8s(VS.80).aspx
  2. Introduction to SQL Server CLR Integration -
    http://msdn2.microsoft.com/en-us/library/ms254498(VS.80).aspx
  3. Enabling CLR Integration -
    http://msdn2.microsoft.com/en-us/library/ms254506(VS.80).aspx
  4. Compiling and Deploying a CLR Assembly -
    http://msdn2.microsoft.com/en-us/library/ms254956(VS.80).aspx
  5. How to: Debug a SQL CLR Stored Procedure -
    http://msdn2.microsoft.com/en-us/library/ms165051(VS.80).aspx
  6. Debugging SQL Database Objects -
    http://msdn2.microsoft.com/en-us/library/ms165034(VS.80).aspx
  7. Setting Up SQL Debugging -
    http://msdn2.microsoft.com/en-us/library/s4sszxst(VS.80).aspx


Jun 20, 2007

C# : Drawing a Pie Chart and saving as jpeg

////Namespace:
using System.Drawing;
using System.Drawing.Imaging;
using System.Drawing.Text;

////Create canvas and fill the background
Bitmap objBitmap;
Graphics objGraphics;

objBitmap = new Bitmap(400, 440);
objGraphics = Graphics.FromImage(objBitmap);


objGraphics.Clear(Color.White);

////Draw the pie and fill
Pen p=new Pen(Color.Yellow,0);
Rectangle rect=new Rectangle(10,10,280,280);
objGraphics.DrawEllipse(p,rect);

Brush b1=new SolidBrush(Color.Red);
Brush b2=new SolidBrush(Color.Green);
Brush b3=new SolidBrush(Color.Blue);
objGraphics.FillPie(b1, rect, 0f, 60f);
objGraphics.FillPie(b2, rect, 60f, 150f);
objGraphics.FillPie(b3, rect, 210f, 150f);

////Draw font
FontFamily fontfml = new FontFamily(GenericFontFamilies.Serif);
Font font = new Font(fontfml, 16);
SolidBrush brush = new SolidBrush(Color.Blue);
objGraphics.DrawString("Drawing Graphics", font, brush, 70, 300);

////Export and save to picture
objBitmap.Save(Response.OutputStream, ImageFormat.Gif);
objBitmap.Save(Server.MapPath("x.jpg"), ImageFormat.Jpeg);

////End drawing
objBitmap.Dispose();
objGraphics.Dispose();

TableLayoutPanel in C#

The Tablelayoutpanel is a easy way to add Controls in a Tabular format.

////--------Sample code-------
TextBox questionText = new TextBox();
questionText.Text = questionName;
questionText.MouseHover += new EventHandler(Question_MouseHover);
questionText.Width = 200;
questionText.BorderStyle = BorderStyle.FixedSingle;
questionText.BackColor = Color.Black;
questionText.ForeColor = Color.White;

GroupBox grRadios = new GroupBox();
grRadios.Height = 25;
grRadios.Width = 100;
grRadios.Padding = new Padding(2);
grRadios.Tag = questionId.ToString();
grRadios.Text = string.Empty;

RadioButton[] radios = new RadioButton[3];
radios[0] = new RadioButton();
radios[0].Tag = "Yes";
radios[1] = new RadioButton();
radios[1].Tag = "No";
radios[2] = new RadioButton();
radios[2].Tag = "Not Applicable";

////Add a event handler to the radio buttons
for (int i = 0; i < radios.Length; i++)
{
radios[i].Width = 20;
radios[i].MouseHover += new EventHandler(Radio_MouseHover);
if (i == checkedRadio)
{
radios[i].Checked = true;
}
}

grRadios.Controls.AddRange(radios);

radios[0].Left = 0;
radios[0].Top = radios[0].Top;
radios[1].Left = radios[1].Left + 20;
radios[1].Top = radios[0].Top;
radios[2].Left = radios[2].Left + 20;
radios[2].Top = radios[0].Top;


tblQuestions.Controls.Add(questionText);
tblQuestions.Controls.Add(grRadios);

SQL Server - Parse a delimited string


alter procedure web_ParseArray
( @Array varchar(1000),
@separator char(1) )
AS
-- Created by graz@sqlteam.com
-- Modified to result a table by Bart@prove.be

set nocount on
-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma
declare @separator_position int -- This is used to locate each separator character
declare @array_value varchar(1000) -- this holds each array value as it is returned

create table #ParsedArrays (array_Value varchar(1000))

-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
set @array = @array + @separator

-- Loop through the string searching for separtor characters
while patindex('%' + @separator + '%' , @array) <> 0
begin

-- patindex matches the a pattern against a string
select @separator_position = patindex('%' + @separator + '%' , @array)
select @array_value = left(@array, @separator_position - 1)

-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @array_value holds the value of this element of the array
insert #ParsedArrays VALUES (@array_value)


-- This replaces what we just processed with and empty string
select @array = stuff(@array, 1, @separator_position, '')
end

set nocount off
select * from #ParsedArrays
drop table #ParsedArrays
go

May 5, 2007

Configure Database mail

Sometimes we need to send mails from inside a database, both through sql or thru .net codes. We can configure SQLserver to send mails by following these steps

-------CREATING A DEFAULT PROFILE--------------------------------------------
-----------------------------------------------------------------------------
--STEP 1: enable the Database Mail feature on the server
-----------------------------------------------------------------------------
use master
go
sp_configure 'show advanced options',1
go
reconfigure with override
go
sp_configure 'Database Mail XPs',1
--go
--sp_configure 'SQL Mail XPs',0
go
reconfigure
go

-----------------------------------------------------------------------------
--STEP 2 create the account
-----------------------------------------------------------------------------
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'MyMailAccount',
@description = 'Mail account for PMMA Database Mail',
@email_address = 'mayukh@maxx.com,
@display_name = 'PMMA DEV',
@username='mayukhd@maxx.com',
@password='abc123',
@mailserver_name = 'mail.maxx.com'
-----------------------------------------------------------------------------
--STEP 3 create a Database Mail profile
-----------------------------------------------------------------------------
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'My
MailProfile',
@description = 'Profile used for database mail'
-----------------------------------------------------------------------------
--STEP 4 add the Database Mail account we created in step 2, to the Database Mail profile you created in step 3
-----------------------------------------------------------------------------
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'MyMailProfile',
@account_name = 'MyMailAccount',
@sequence_number = 1
-----------------------------------------------------------------------------
--STEP 5 grant the Database Mail profile access to the msdb public database role and to make the profile the default Database Mail profile
-----------------------------------------------------------------------------
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'MyMailProfile',
@principal_name = 'public',
@is_default = 1 ;
-----------------------------------------------------------------------------
--Test
-----------------------------------------------------------------------------
EXEC msdb.dbo.sp_send_dbmail @recipients='mayukh@maxxzone.com',
@subject = 'My Mail Test',
@body = 'Test Body',
@body_format = 'HTML' ;


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


Following is the code to send databse mails through C# :

string SprocSendEmail = "msdb.dbo.sp_send_dbmail";
string msgSubject = "Message Subject";
string msgBodyFormat = "HTML";
//// *******Setup the Static Data[Message Body]*********************************
StringBuilder msgBody = new StringBuilder();
msgBody.Append("Start of Message Body <hr >");
msgBody.Append("Any other body text ");
msgBody.Append(" <hr >");
////*******Call the sp_send_dbmail SPROC*********************************
SqlCommand sqlCommand = new SqlCommand();
sqlCommand.Connection = connection;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = SprocSendEmail;
SqlParameter paramRecipients = new SqlParameter();
paramRecipients.ParameterName = "@recipients";
paramRecipients.SqlDbType = SqlDbType.VarChar;
paramRecipients.Direction = ParameterDirection.Input;
SqlParameter paramBody = new SqlParameter();
paramBody.ParameterName = "@body";
paramBody.SqlDbType = SqlDbType.VarChar;
paramBody.Direction = ParameterDirection.Input;
SqlParameter paramSubject = new SqlParameter();
paramSubject.ParameterName = "@subject";
paramSubject.SqlDbType = SqlDbType.VarChar;
paramSubject.Direction = ParameterDirection.Input;
SqlParameter paramBodyFormat = new SqlParameter();
paramBodyFormat.ParameterName = "@body_format";
paramBodyFormat.SqlDbType = SqlDbType.VarChar;
paramBodyFormat.Direction = ParameterDirection.Input;
sqlCommand.Parameters.Add(paramRecipients); //// parameter 0
sqlCommand.Parameters.Add(paramBody); //// parameter 1
sqlCommand.Parameters.Add(paramSubject); //// parameter 2
sqlCommand.Parameters.Add(paramBodyFormat); //// parameter 3
paramRecipients.Value = systemParametersCollection["mailingAddress"].ToString();
paramBody.Value = msgBody.ToString();
paramSubject.Value = msgSubject;
paramBodyFormat.Value = msgBodyFormat;
sqlCommand.ExecuteNonQuery();





Some additional web resources
How to configure SQL 2005 Database Mail
sp_send_dbmail (Transact-SQL)

Mar 15, 2007

SQL Server 2005: CLR Integration : Using Webservices and Xml Serialization in CLR Integration

2 very informative pages in this regrd

http://blogs.msdn.com/sqlclr/archive/2005/07/25/Vineet.aspx

http://www.u2u.be/Article.aspx?ART=WebServicesinSQL05

Enabling the CLR in the Sql Server 2005

If you want to run a CLR object in Sql Server 2005 (or SqlExpress), (note that CLR is disabled by default on Sql Server 2005).
You will need to enable the CLR.
Here are the commands that I used to enable the CLR.
-----------------------------------------------------------
EXEC sp_configure 'show advanced options' , '1';

go
reconfigure;
go
EXEC sp_configure 'clr enabled' , '1'
go
reconfigure;
-----------------------------------------------------------

Mar 14, 2007

Connecting to Sql Server 2005 from c# winforms An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005

c# winforms An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.)







Q: I get the following error when trying to connect:"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"

I enabled Named Pipes but left TCP/IP disabled because this is a localhost connection on the same computer.

Here is the portion of the code I am using to connect:
string dbCString = "Persist Security Info=False;Integrated Security=true;Trusted_Connection=true;
Initial Catalog=MxData;server=(local)";
SqlConnection ThisConnection = new SqlConnection(dbCString);ThisConnection.Open();





After a lot of research, here's the answer.
The connection string should read as follows:
"Server=.\\SQLEXPRESS;Initial Catalog=MxData;Integrated Security=SSPI";
Note that you need a dot followed by TWO left slashes in your server name.
BTW, Initial Catalog is whatever database you want to use.

Mar 4, 2007

C# Typecasting: Casting Vs System.Convert.ChangeType

C# Typecasting: AS,IS,WHERE Keywords

Type conversion in C#

Bruce Eckel's MindView, Inc: Book Download Sites

Technical Book Site

Send Mail Using Lotus Notes


'This public sub will send a mail and attachment if neccessary to the recipient including the body text.

'Requires that notes client is installed on the system.

Public Sub SendNotesMail(Subject As String, attachment As String, recipient As String, bodytext As String, saveit As Boolean)
'Set up the objects required for Automation into lotus notes
Dim Maildb As Object 'The mail database
Dim UserName As String 'The current users notes name
Dim MailDbName As String 'THe current users notes mail database name
Dim MailDoc As Object 'The mail document itself
Dim AttachME As Object 'The attachment richtextfile object
Dim Session As Object 'The notes session
Dim EmbedObj As Object 'The embedded object (Attachment)
'Start a session to notes
Set Session = CreateObject("Notes.NotesSession")
'Get the sessions username and then calculate the mail file name
'You may or may not need this as for MailDBname with some systems you
'can pass an empty string
UserName = Session.UserName
MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf"
'Open the mail database in notes
Set Maildb = Session.GETDATABASE("", MailDbName)
If Maildb.IsOpen = True Then
'Already open for mail
Else
Maildb.OPENMAIL
End If
'Set up the new mail document
Set MailDoc = Maildb.CREATEDOCUMENT
MailDoc.Form = "Memo"
MailDoc.sendto = recipient
MailDoc.Subject = Subject
MailDoc.Body = bodytext
MailDoc.SAVEMESSAGEONSEND = saveit
'Set up the embedded object and attachment and attach it
If attachment <> "" Then
Set AttachME = MailDoc.CREATERICHTEXTITEM("Attachment")
Set EmbedObj = AttachME.EMBEDOBJECT(1454, "", attachment, "Attachment")
MailDoc.CREATERICHTEXTITEM ("Attachment")
End If
'Send the document
MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder
MailDoc.SEND 0, recipient
'Clean Up
Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session = Nothing
Set EmbedObj = Nothing
End Sub

ADO.NET DataSet Programming Framework 1.1

Enterprise Library for .NET Framework 1.1