Custom FxCop Rule.

[smnadig]

Hi All,

We have a requirement to write a custom FxCop rule to generate a warning message when a SQL Query is encountered in the program and suggest to use a Stored Procedure instead.

I have written the following code which is generating a warning when an SQL object is present -- Warning is generated as soon as an object of SqlConnection is created. Hence even when there is only stored procedure and no query warning still pops.

Can anyone please suggest me how do I modify so that warning is generated only if there is a direct SQL Query ie., for SELECT, INSERT,UPDATE & DELETE statements.

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Cci;
using Microsoft.FxCop.Sdk;
using Microsoft.FxCop.Sdk.Introspection;

namespace CompanyRules
{
    public class UseStoredProcedureForSQLQuery : BaseIntrospectionRule
    {
       public UseStoredProcedureForSQLQuery():
                    base("UseStoredProcedureForSQLQuery","CompanyRules.RuleData",      typeofUseStoredProcedureForSQLQuery).Assembly)
     {
     }

 public override ProblemCollection Check(TypeNode type)
 {
      return Problems;
 }

 public override ProblemCollection Check(Member member)
 {
      Method mainMethod = member as Method;
      Instruction instruction;
  
      if (mainMethod == null)
      {
           return null;
      }
      if (mainMethod.Instructions == null)
      {
           return null;
      }
  
      for (int count = 0; count <= mainMethod.Instructions.Length - 1; count++)
      {
           instruction = mainMethod.Instructions[count];

           if (instruction.OpCode == OpCode.Newobj)
           {
                if (((Microsoft.Cci.Method)(instruction.Value)).
                    FullName.Contains("System.Data.SqlClient.SqlConnection.#ctor"))
                {
                    Problems.Add(new Problem(GetResolution("SqlConnection","Cafe.net connection")));
                }

                if (((Microsoft.Cci.Method)(instruction.Value)).
                    FullName.Contains("System.Data.SqlClient.SqlCommand.#ctor"))
                {
                        Problems.Add(new Problem(GetResolution("SqlCommand", "Cafe.net command")));
                }
           }
      }
      return Problems;
    }
    }
}

[Ramy Mahrous]

Stored procedure name as well SQL Statement may be in SqlCommand class, can you fetch some properties of SqlCommand class using Microsoft.Cci??

[smnadig]

Hi,

I am not unable to understand your question. Can you please be eloborate?

Regards,
Sahana

[Ramy Mahrous]

Look what makes difference is that you get SQLCommand.CommandType = ? "Text" or "StoredProcedure"
If you get CommandType value you'll solve your problem I didn't work before with Microsoft.Cci library!

[smnadig]

Hi Ramy,

I have tried the approach you have mentioned but am unable to get the desired result. Also, the check needs to be done not only with commandType.Text or StoredProcedure but with other SQL statements if any in the program. Please see the following code:

using System;
using System.Data;
using System.Data.SqlClient;

namespace example
{

    class Test
    {
        
        public static void Main()
        {
            SqlConnection MyConnection = new SqlConnection(@"Data Source=(local); Initial Catalog = CaseManager; Integrated Security=true");
            MyConnection.Open();

            SqlCommand MyCmd = new SqlCommand(@"INSERT INTO Test(ID, Contact, Email) VALUES(2, 'Greg', 'MacBeth')";, MyConnection);

           MyConnection.Close();
        }
    }
}

There should be a warning generated in to remove the direct SQL query in the SqlCommand.

Can you please suggest how can i capture this?

Thank you.

Regards,
Sahana

[Ramy Mahrous]

You can check if the Command Object's Text value has (Select, Insert, Update or Delete) ? using string operations ? I don't know how to use Cci !! Did you try googlize it?

[smnadig]

Yes I tried googling but did not find anything suitable. I too am working on Microsoft.CCi library for the first time :( !!

Thanks for your suggestion. I shall try to work on that.