Connect to SQL Server and count the rows of a table column using C# in WPF (.NET Core)

Question

I recently started using C# in WPF (.NET Core). I am very new in using C# so I apologize if the question is relative easy to answer. Briefly I would like to create a button that when clicked by the user will generate the count of rows of a specific table.

My code so far (using the WPF (.NET Core) in Visual Studio 2019

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Data.SqlClient;

namespace TestEnvironment
{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>

    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }

        private dynamic connectionString;

        public ConnectionStringValue()
        {
            connectionString();

            static void connectionString()
            {
                string connectionString = "Data Source=youServerHere;Initial Catalog=YourDataBase;Integrated Security=True;"; #access it with Windows Authentication
            }
        }

        SqlConnection sqlCon = new SqlConnection(connectionString);
        sqlCon.Open();

        SqlCommand sqlCommand = new SqlCommand("SELECT COUNT(*) FROM tblProduct");
        sqlCommand.Connection = sqlCon;
 
        int RecordCount = Convert.ToInt32(sqlCommand.ExecuteScalar());

        private void Button_Click_1(object sender, RoutedEventArgs e)
        {
            Count.Text = RecordCount;
        }
    }
}

My first error that I cannot overpass is the variable connectionString for which I get the error: CS0236 A field initializer cannot reference the non-static field, method, or property 'MainWindow.connectionString'

To fix this I tried to create a private dynamic object named connectionString, but still my error is unresolved.

Thank in advance for any advise and I apologize again for my inexperience with C#.

Code update

{
    /// <summary>
    /// Interaction logic for MainWindow.xaml
    /// </summary>

    public partial class MainWindow : Window
    {
        public MainWindow()
        {
            InitializeComponent();
        }

        private string connectionString;

        public ConnectionStringValue()
        {
            static void connectionString()
            {
                connectionString = "Data Source=youServerHere;Initial Catalog=YourDataBase;User ID=sa; Password=123;";
            }
        }
       
        SqlConnection sqlConnection = new SqlConnection(connectionString);
        sqlConnection.Open();

        SqlCommand sqlCommand = new SqlCommand("SELECT COUNT(*) FROM tblProduct");
        sqlCommand.Connection = sqlConnection;
 
        int RecordCount = Convert.ToInt32(sqlCommand.ExecuteScalar());
        

        private void Button_Click_1(object sender, RoutedEventArgs e)
        {
            Count.Text = RecordCount;
        }
    }
}

Error 1: CS1520 Method must have a return type (for the ConnectionStringValue)
Error 2: CS1656 Cannot assign to 'connectionString' because it is a 'method group' + CS8422 A static local function cannot contain a reference to 'this' or 'base'. (both errors for connectionString string object)

Simple. Remove "string" from following. It is already defined : string connectionString = "Data Source=youServerHere;Initial Catalog=YourDataBase;Integrated Security=True;"; #access it with Windows Authentication. Then change From : private dynamic connectionString; To : private string connectionString; — jdweng
– jdweng, Commented Sep 8, 2020 at 11:04
@jdweng Thank you for the response. I made the changes you proposed. Although, I still get the following error: public ConnectionStringValue()-> error: Method must have a return type. 2nd error connectionString-> Error: CS1656 Cannot assign to 'connectionString' because it is a 'method group' — NikSp
– NikSp, Commented Sep 8, 2020 at 11:14
@jdweng If you check my updated code, I still get the errors even after your comments. Ty in advance for your attention :) — NikSp
– NikSp, Commented Sep 8, 2020 at 11:38

Coskun Ozogul · Accepted Answer · 2020-09-08 13:42:33Z

1

If I skipped something tell me, I would do this in the scope of your button click like this :

{
/// <summary>
/// Interaction logic for MainWindow.xaml
/// </summary>

public partial class MainWindow : Window
{

    private string connectionString = "Data Source=youServerHere;Initial Catalog=YourDataBase;User ID=sa; Password=123;"; 
    public MainWindow()
    {
        InitializeComponent();
    }

      
    

    private void Button_Click_1(object sender, RoutedEventArgs e)
    {
      using(SqlConnection sqlConnection = new SqlConnection(connectionString))
      {         
        using( SqlCommand sqlCommand = new SqlCommand("SELECT COUNT(*) FROM tblProduct", sqlConnection))
          {
            sqlConnection.Open();

            int RecordCount = Convert.ToInt32(sqlCommand.ExecuteScalar());

            Count.Text = RecordCount.ToString();
          }
      }
    }
}

}

edited Sep 8, 2020 at 13:42

answered Sep 8, 2020 at 11:50

Coskun Ozogul

2,5172 gold badges25 silver badges37 bronze badges

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

5 Comments

NikSp Over a year ago

Coskun thanks a lot four your answer. It seems to have no error. However, when I try to debug it I get the following exception: System.InvalidOperationException: 'The connection was not closed. The connection's current state is open.'...Searching about the error I figured out that the use of using() will solve that. However, it didn't work in my case

Coskun Ozogul Over a year ago

NikSp, sorry, I put sqlConnection.Open(); two times. Remove the first one, it will work. I edit my answer. No need to change using states.

NikSp Over a year ago

Coskun your solution worked, perfectly. Since I am newbie in C# could you please share with me in the comments sections, or in your answers, the reasoning behind your answer? Why you initiated the private string=" " outside the button class? 2) Basically the correct structuring. Do you recommend any relevant tutorial on basic C# coding? Thanks a lot in advance

Coskun Ozogul Over a year ago

So, you can define the string inside the button click but this time it will be accessible only in this scope. If you declare it outside the button click, it will be accessible in your entire class. And there are Access modifiers to take a look to understand public, private etc. declerations.

Coskun Ozogul Over a year ago

For the C# coding, you can find a lot of sources by searching c# learning. I like this site :w3schools.com/cs

Collectives™ on Stack Overflow

Connect to SQL Server and count the rows of a table column using C# in WPF (.NET Core)

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related