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 .

CodePudding user response：

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.