I'm learning database connectivity using C# Windows Form Application in Visual Studio and SQL Server Management Studio.

The problem I'm having is I'm unable to access the AddParams() and sqlQueryCmd() functions from the SQLConn.cs Class. I've tried changing the access modifiers from private to public but it didn't work. Can anyone guide me here? I don't know what I'm doing wrong.

SQLConn.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Microsoft.Data.SqlClient;

namespace dbConnectivityTest1
{
    public class SQLConn
    {
        public static void Main1(string[] args)
        {
            using (SqlConnection connString = new SqlConnection("Server=ServerName;Database=DatabaseName;User Id=UserID;\r\nPassword=Password;"))
            {
                List<SqlParameter> paramList = new List<SqlParameter>();
                string AddParams(string tableName, object insVal)
                {
                    SqlParameter sqlparams = new SqlParameter(tableName, insVal);
                    paramList.Add(sqlparams);
                }
                SqlCommand sqlCmd = new SqlCommand();

                string sqlQueryCmd(string sqlQuery)
                {
                    int recordCount = 0;
                    string excptnShow = "";

                    try
                    {
                        connString.Open();
                        SqlCommand sqlCmd = new SqlCommand(sqlQuery, connString);
                        paramList.ForEach(p => { sqlCmd.Parameters.Add(p); });
                        paramList.Clear();
                        DataTable sqlDT = new DataTable();
                        SqlDataAdapter sqlDA = new SqlDataAdapter(sqlCmd);
                        recordCount = sqlDA.Fill(sqlDT);
                    }
                    catch(Exception ex)
                    {
                        excptnShow = "Error: \r\n"   ex.Message;
                    }
                    finally
                    {
                        if (connString.State == ConnectionState.Open)
                        {
                            connString.Close();
                        }
                    }
                }
            }
        }
    }
}

Form1.cs

namespace dbConnectivityTest1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private SQLConn sqlDB = new SQLConn();

        private void enterNametextBox_Click(object sender, EventArgs e)
        {
            enterNametextBox.Clear();
        }

        private void submitButton_Click(object sender, EventArgs e)
        {
            while(fullNameTextBox.Text.Length <= 0)
            {
                MessageBox.Show("Please enter your Full Name!", "Caption", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation);
            }
            while(contactTextBox.Text.Length <= 0)
            {
                MessageBox.Show("Please enter your Contact Number!", "Caption", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation);
            }
            while(ageTextBox.Text.Length <= 0)
            {
                MessageBox.Show("Please enter your Age!", "Caption", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation);
            }
            while(emailTextBox.Text.Length <= 0)
            {
                MessageBox.Show("Please enter your E-mail!", "Caption", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation);
            }
            System.Text.RegularExpressions.Regex emailRegex = new System.Text.RegularExpressions.Regex(@"^[a-zA-Z0-9 _.-] @[a-zA-Z0-9.-] $");
            while(!emailRegex.IsMatch(emailTextBox.Text))
            {
                MessageBox.Show("Please enter a valid E-mail!", "Caption", MessageBoxButtons.OKCancel, MessageBoxIcon.Exclamation);
            }
            InsVal();
        }

        public void InsVal()
        {
            **I am trying to call the SQLConn.cs Functions here**
        }
    }
}

CodePudding user response：

As people in the comments stated, you'll have to move the functions out of Main1.

sing System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Microsoft.Data.SqlClient;

namespace dbConnectivityTest1
{
    public class SQLConn
    {
        public string AddParams(string tableName, object insVal)
        {
                SqlParameter sqlparams = new SqlParameter(tableName, insVal);
                paramList.Add(sqlparams);
        }

        public string sqlQueryCmd(string sqlQuery)
        {
                int recordCount = 0;
                string excptnShow = "";
                try
                {
                    connString.Open();
                    SqlCommand sqlCmd = new SqlCommand(sqlQuery, connString)
                    paramList.ForEach(p => { sqlCmd.Parameters.Add(p); });
                    paramList.Clear();
                    DataTable sqlDT = new DataTable();
                    SqlDataAdapter sqlDA = new SqlDataAdapter(sqlCmd);
                    recordCount = sqlDA.Fill(sqlDT);
                }
                catch(Exception ex)
                {
                    excptnShow = "Error: \r\n"   ex.Message;
                }
                finally
                {
                    if (connString.State == ConnectionState.Open)
                    {
                        connString.Close();
                    }
                }
        }

        public static void Main1(string[] args)
        {
            using (SqlConnection connString = new SqlConnection("Server=ServerName;Database=DatabaseName;User Id=UserID;\r\nPassword=Password;"))
            {
                List<SqlParameter> paramList = new List<SqlParameter>();
                
                SqlCommand sqlCmd = new SqlCommand();
            }
        }
    }
}

CodePudding user response：

Movint addParam method/function would not solve the problem since the SqlParam variable is not available in sqlConn class. the better approach for your goal is to to this:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace dbConnectivityTest1
{
public class SQLConn
{

    private readonly string _connectionString;
    private List<SqlParameter> _paramList;
    public SQLConn()
    {
        _connectionString="Server=ServerName;Database=DatabaseName;User Id=UserID;\r\nPassword=Password;";
        _paramList = new List<SqlParameter>();
    }

    public void AddParams(string tableName, object insVal)
    {
        SqlParameter sqlparams = new SqlParameter(tableName, insVal);
        _paramList.Add(sqlparams);
    }

    public void sqlQueryCmd(string sqlQuery)
    {
        using (var sqlConn = new SqlConnection(_connectionString))
        {

            int recordCount = 0;
            string excptnShow = "";

            try
            {
                sqlConn.Open();
                SqlCommand sqlCmd = new SqlCommand(sqlQuery, sqlConn);
                _paramList.ForEach(p => { sqlCmd.Parameters.Add(p); });
                _paramList.Clear();
                DataTable sqlDT = new DataTable();
                SqlDataAdapter sqlDA = new SqlDataAdapter(sqlCmd);
                recordCount = sqlDA.Fill(sqlDT);
            }
            catch (Exception ex)
            {
                excptnShow = "Error: \r\n"   ex.Message;
            }
            finally
            {
                if (sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                }
            }
        }
    }
    
}

}

in this scenario there is no need for Main method since every application can have only one Main method and winForms by default already have one Main method.

secondly, the SQLConn class can have private field of type List, and a public method called AddParam which its responsibility is to add new params to it.

Lastly, a public SqlQueryCmd method is added to do the rest of the job.

you can now use this class to execute your query like this:

private void button1_Click(object sender, EventArgs e)
    {
        var sqlConn = new SQLConn();

        sqlConn.AddParams("tableName","your Object");
        sqlConn.AddParams("tableName","your Object");
        sqlConn.AddParams("tableName","your Object");

        sqlConn.sqlQueryCmd("Query");
    }