How to use variable name in sql command

[Nugget]

I am a novice on C# so please help.

The code is to list the names of all the tables in a database and count number of records in each table, error in using the variable tabname, please help, many thanks:

private void button1_Click(object sender, EventArgs e)
{
    var list = new List<string>();
    int maxcount;
    String connetionString;
    String tabname;
    SqlConnection cnn;

    connetionString = @"Server=DESKTOP-E441IBF\SQLEXPRESS2017;Database=TZ_Demo;Trusted_Connection=true";
    cnn = new SqlConnection(connetionString);
    cnn.Open();

    DataTable schemaTable = cnn.GetSchema("Tables");

    foreach (DataRow row in schemaTable.Rows)
    {
        list.Add((string)row["TABLE_NAME"]);
    }

    maxcount = list.Count;
    for (int i = 0; i < maxcount; i++)
    {
        tabname = list; // list of table names
        SqlCommand comd = new SqlCommand("select count(*) FROM  'tabname' ", cnn); // error is here, syntax error
        int count = Convert.ToInt32(comd.ExecuteScalar());
        Console.WriteLine(count);
    }

    cnn.Close();
}

 

[Skydiver]

The primary issueis on line 24 where C# and SQL are two different languages, and furthermore, C# code typically will be running on your computer, while the SQL will be executing within the database engine (and may running on another machine). So your tabname variable within your C# code does not get translated or sent to SQL.

Your secondary issue is that you can't assign a List<string> to a string in C#. (Other languages may support this, but alas, C# in it's current incarnation does not.) So your line 23 should also be giving you an error. To fix this issue, pull a string out of the list by indexing into the list; OR iterate over the list using foreach.

So you have a few options open to you with regards to getting the name into your SQL query:
The first option, is the most straight forward. You could dynamically compose a new string with the name. You could use the more traditional string formatting (String.Format("The table name is {0}", tabname) or the modern string interpolation operators ($"The table name is {variable}"). Most others won't even use String.Format() and just use plain old string concatenation.

The second option is using a parameterized query. In general prefer parameterized queries because it protects you from SQL injection attacks if you don't have full control of the strings that ill be going into the SQL query from the first option above.

Obligatory SQL injection humor:

 

[Nugget]

Many thanks Skydiver, please change my code using parameterized query as I have tried a few things but it asked for some declarations and I do not know how to, thanks again for your time & advice.

 

[Skydiver]

We are not a code writing service.

Perhaps look at this other thread that was posted the same day your thread was posted. He uses a parameterized query. It's not that hard:

Question - Update issue. Foreign Key not updaing on SQL call.

I'm having a problem where the LocationID which is a foreign key is not updating under this SQL call. The Question attribute is updating fine but for some reason, the LocationID is not.

csharpforums.net

Otherwise, post what you have tried as well as post the errors you are getting. Some one can point you in the right direction to get your parameterized queries to work.

 

[jmcilhinney]

@Skydiver is right about using parameters to avoid SQL injection and other issues in the case of specifying values but you cannot use parameters for identifiers, including table names, so parameters are not an option in this case. SQL injection is always a risk that needs to be mitigated but, if you're getting these table names directly from the database then there's no opportunity for a malicious user to inject SQL so you're OK. Just use String.Format or string interpolation as suggested. Also, use a foreach loop to enumerate your table names. If you have a for loop and the only thing you're using the loop counter for is to index one list, you should be using a foreach loop.