C# SqlCommand naming convention

Question

I'm fairly new to working with C# and ASP.NET and I'm curious if there is a convention to naming the SqlCommands when more than one query is being run on a SQL database. For example, I have created my SqlConnection and I wish to call a function, two stored procedures, and just create a regular simple query. Currently, I am using:

SqlCommand function = new SqlCommand();
SqlCommand command = new SqlCommand();
SqlCommand proc1 = new SqlCommand();
SqlCommand proc2 = new SqlCommand();

Is there a more accepted naming convention for these different commands or should I just use a single command as I am using the CommandText and CommandType calls in the later code blocks?

Why don't you wrap all your functions/SPs into one single StoredProcedure.. — Mayank Pathak
– Mayank Pathak, Commented Sep 16, 2015 at 12:33
you don't need to have instance for every query.just change commandType. — Arash
– Arash, Commented Sep 16, 2015 at 12:34
I generally just create a singular SqlCommand cmd, assign its properties and execute it. Then re-assign everything using that same cmd object as needed for other DB-related tasks. Alternatively, as Mayank said you could just make a generic method that takes a procedure name, a collection of parameters and loop over those. — sab669
– sab669, Commented Sep 16, 2015 at 12:44
The commands cannot be placed in a single procedure due to internal coding policy. As such, I had to make multiple calls and wasn't sure if it was acceptable to simply reuse command.* for four entirely different commands. The ones named function and command were already present in the code when it was handed to me, I simply had to refactor them and do some modifications on the SQL side. The two stored procs were new additions and the naming just felt odd to me. — Prof. Bear
– Prof. Bear, Commented Sep 16, 2015 at 13:48

Sevle · Accepted Answer · 2016-02-26 15:53:54Z

5

If you have a lot of commands in the same scope, you could have names like personCommand or productCommand. From the MSDN General Naming Conventions you could:

DO choose easily readable identifier names. For example, a property named HorizontalAlignment is more English-readable than AlignmentHorizontal.

DO favor readability over brevity. The property name CanScrollHorizontally is better than ScrollableX (an obscure reference to the X-axis).

DO NOT use underscores, hyphens, or any other nonalphanumeric characters. X DO NOT use Hungarian notation.

AVOID using identifiers that conflict with keywords of widely used programming languages.

See more about C# Coding Conventions. In other cases, I prefer using just command to Keep It Simple, because the scope will tell me.

Another good tip, when you work with types which implements the IDisposable interface like SqlCommand, SqlConnection, you can use the using() { } structure to dispose the object after this scope. For sample:

public IEnumerable<Person> GetPersons()
{
    var result = new List<Person>();

    using (var connection = new SqlConnection("connection string"))
    {
       // I know it is a person command, because it is in a method for it.
       // Keep it simple!
       using (var command = new SqlCommand("select id, name from persons", connection))
       {
          using (var reader = command.ExecuteReader())
          { 
              while (reader.Read())
              {
                  result.Add(new Person() {
                     Id = (int) reader["id"];
                     Name = reader["name"] as string;                    
                  });
              }
          } // reader is disposed here
       } // command is disposed here
    } // connection is disposed here

    return result;
}

There is much more about coding convention. See the links at references.

edited Feb 26, 2016 at 15:53

Sevle

3,1192 gold badges22 silver badges32 bronze badges

answered Sep 16, 2015 at 12:39

Felipe Oriani

38.7k20 gold badges141 silver badges203 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

sab669 Over a year ago

How / why is this any better than simply creating the object(s) in the method like int foo() { var cmd = new SqlCommand(); /* do stuff */ return someInt; }? Don't they Dispose of themselves once the method returns? Isn't that the point of the Garbage Collector? I've seen this done before so I'm clearly missing something.

sab669 Over a year ago

Adding a second comment in regards to your edit on naming conventions. No underscores? At multiple jobs, and even in college I often saw code like: private string _someString; public string SomeString { get { return _someString; } }. But that seems counter to their guidelines there. Is that indeed considered poor form?

Matthew Whited Over a year ago

to @sab669... the underscore prefix is typically used to define class level variables so you can quickly tell local scope from class shared... a more common pattern in .Net is to use auto properties now. Oh, and you should always call .Dispose() as it is possible that objects are holding system resources that need released. If you wait for the finalizers you can easily create resource leaks.

sab669 Over a year ago

Ah, just adding this for others. (From MSDN) "The using statement ensures that Dispose is called even if an exception occurs while you are calling methods on the object. You can achieve the same result by putting the object inside a try block and then calling Dispose in a finally block; in fact, this is how the using statement is translated by the compiler."

Felipe Oriani Over a year ago

As @MatthewWhited said it is important auto properties is a easy to create properties when you do not have two much rules in the get/set. There are a lot of things about Naming convention and if we try to explaint everything here it will be a book haha.

|

K.J. · Accepted Answer · 2015-09-16 12:35:28Z

0

A good and more readable convention would be to express what the command will do. If you read updateProductCommand or queryCategoryCommand everybody instantly knows the purpose of the command. When you target a stored procedure it´s always good to use the sproc name as the command prefix like sp_UpdateProductCommand.

answered Sep 16, 2015 at 12:35

K.J.

2301 silver badge8 bronze badges

1 Comment

Prof. Bear Over a year ago

This is actually why I was asking. My first instinct was to do this and give the commands descriptive name, but I did not write the original code that I am working with and was trying to follow the convention that was already in place because I wasn't sure if there was a more accepted way of doing things.

Collectives™ on Stack Overflow

C# SqlCommand naming convention

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related