-4

I'm trying to write a function -like Dcount and Dlookup in VBA Access- in a public class to use it everywhere in my project so I did the following :

  public class MyTools
    {
        SqlConnection Cn = new SqlConnection(@"Server = AMR-PC\SQLEXPRESS ; Database=PlanningDB ; Integrated Security = True");
        SqlDataAdapter da;
        DataTable dt = new DataTable();
     //   DataView dv = new DataView();
        SqlCommand cmd;
        SqlDataReader DataRead;

        // Variables
        string MyColumn, MyTable, MyCondition,DlookResult;
        int DcountResult;

        // Methods & Functions
        // Dcount
        public int DCount(string MyColumn, string MyTable, string MyCondition)
        {
            da = new SqlDataAdapter("Select Count(@MyColumn) from @MyTable where @MyColumn = @MyCondition", Cn);
            da.Fill(dt);
            DcountResult = int.Parse(dt.Rows[0].ToString());
            return DcountResult;
        }
    }

    // Dlookup


}

And tried to use it like this :

  int Result = DCount(txtColumn.Text, txtTable.Text, txtCond.Text);
            txtResult.Text = null;
            txtResult.Text = Result.ToString();

But it throws the error "Must declare the scalar variable "@MyColumn". I tried to use sqlcommand and DataRead but I need to close the connection after the return and it became Unreachable or close before the return so it returns nothing , That's why i used SqlDataAdapter. Thanks in advance .

Csharp Newbie
  • 303
  • 1
  • 10
  • You can't substitute in the table and column names that way... only literal values. Additionally, it's a mistake to keep an actual connection object for the lifetime of a class like this; it interferes with ADO.Net's own connection pooling and makes things slower and use more memory. Instead, only keep a local copy of the connection string. The rest of the variables should all be scoped to the method. – Joel Coehoorn Apr 28 '22 at 20:08
  • `SqlDataAdapter` has nothing to do with the ability to close the connection; if the respective code was marked as unreachable, you did something wrong. You are not providing any variables at all to the SQL command, which the error message is telling you about. Even if you were [providing parameters](https://stackoverflow.com/q/542510/11683) to the SQL command correctly, you [cannot](https://stackoverflow.com/q/2838490/11683) parametrize `@MyTable` or `@MyColumn`. – GSerg Apr 28 '22 at 20:09
  • Thanks for reply , What is the best way to do what I need ? I need a function which counts a variable value from variable column from variable table , So I can use it everywhere without keep typing SQL queries ? – Csharp Newbie Apr 28 '22 at 20:12
  • Side note: while I like trolling (and even better self-trolling) I'd recommend not to use the same names for fields/properties and method parameters. – Alexei Levenkov Apr 28 '22 at 20:12
  • Look into dynamic sql if you need that flexibility. – Trevor Apr 28 '22 at 20:12
  • Looks like regular LINQ-to-SQL should be much better `myRepo.GetCars().Where(x=> x.Color=="green").Count()`... – Alexei Levenkov Apr 28 '22 at 20:14
  • That SQL statement has some errors. 1- Your `from @MyTable` would not work as intended, because SQL Server does not allow passing table names as parameters. 2- In a similar fashion, but different, the `@MyColumn` would not be replaced by your column name, you would get something similat to `where 'name' = 'Josh'` and that is not what you want. 3- You never pass to the SqlCommand inside the SqlAdapter any parameters. – Cleptus Apr 28 '22 at 20:15
  • Also you need to dispose the connection, command, reader and adapter with `using`, do *not* cache them. – Charlieface Apr 28 '22 at 21:39

1 Answers1

4

It would have to look something more like this:

public class MyTools
{
    private static string ConnectionString {get;} = @"Server = AMR-PC\SQLEXPRESS ; Database=PlanningDB ; Integrated Security = True";

    public static int DCount(string MyTable, string MyColumn, string MyCondition)
    {
        string sql = $"Select Count({MyColumn}) from {MyTable} where {MyColumn} = @MyCondition";

        using (var cn = new SqlConnection(ConnectionString))
        using (var cmd = new SqlCommand(sql, cn))
        {
            cmd.Parameters.AddWithValue("@MyCondition", MyCondition);
            cn.Open();
            return (int)cmd.ExecuteScalar();
        }
    }
}

Just be aware this uses dynamic SQL, and is more than a little dangerous. In fact, you should not do this. I know you don't want to "keep typing SQL queries", but that might be exactly what you should do.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794