Multiple Object use across class c#

Question

I have three classes that hold data from an input form and then each submit an insert query to the database.

There must be a better way of doing this with using the existing objects and a singular stored procedure, but I can't get the existing objects to work in another class. I apologise for the simplicity of the question as I imagine this to be a very straightforward fix to tidy up my code.

With the code below what I am trying to achieve is to reuse the existing instances of EndUser, Bank and Company in the StoredProc class so I don't have to use SQL queries in each class method and only one save method from the StoredProc class.

Edit To clarify the database stuff ie the SQL string isn't the issue, what I am trying to ask is can I use instances of existing objects (three of them) in the storedproc class so I can use one (already written) stored procedure?

Apologies the code is a bit long but I have thinned it down as much as possible whilst still making sense (as well as running):

Form backend

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

    private void button1_Click(object sender, EventArgs e)
    {
        EndUser newUser = new EndUser(textBox1.Text, textBox2.Text);
        Company newcmpny = new Company(textBox4.Text, textBox3.Text);
        Bank newbank = new Bank(textBox6.Text, textBox5.Text);
        newUser.Save();
        newcmpny.Save();
        newbank.Save();
    }
}

DataHold classes (all in one file)

    class EndUser
{
    public EndUser(string first, string last) {
        firstName = first;
        lastName = last;
    }
    public int iD { get; set; }
    public string firstName { get; set; }
    public string lastName { get; set; }

    public void Save()
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TestDatabase"].ConnectionString))
        {
            string sqlQuery = (@"INSERT INTO [EndUser] (FirstName, LastName)
                            VALUES (@FirstName, @LastName)");
            connection.Open();

            using (SqlCommand command = new SqlCommand(sqlQuery, connection))
            {
                command.Parameters.AddWithValue("FirstName", firstName).ToString();
                command.Parameters.AddWithValue("LastName", lastName).ToString();
                command.ExecuteNonQuery();
            }
        }
    }
}

class Company
{
    public Company(string cmpny, string tele)
    {
        company = cmpny;
        telephone = tele;
    }

    public string company { get; set; } // textbox4
    public string telephone { get; set; } // textbox3

    public void Save()
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TestDatabase"].ConnectionString))
        {
            string sqlQuery = (@"INSERT INTO [Company] (CName, Telephone)
                            VALUES (@CName, @Telephone)");
            connection.Open();

            using (SqlCommand command = new SqlCommand(sqlQuery, connection))
            {
                command.Parameters.AddWithValue("CName", company).ToString();
                command.Parameters.AddWithValue("Telephone", telephone).ToString();
                command.ExecuteNonQuery();
            }
        }
    }
}

class Bank
{
    public Bank(string bn, string scode)
    {
        name = bn;
        sortcode = scode;
    }
    public string name { get; set; } // textbox6
    public string sortcode { get; set; } // textbox5
    public void Save()
    {
        using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["TestDatabase"].ConnectionString))
        {
            string sqlQuery = (@"INSERT INTO [Bank] (BankName, SortCode)
                            VALUES (@BankName, @SortCode)");
            connection.Open();

            using (SqlCommand command = new SqlCommand(sqlQuery, connection))
            {
                command.Parameters.AddWithValue("BankName", name).ToString();
                command.Parameters.AddWithValue("SortCode", sortcode).ToString();
                command.ExecuteNonQuery();
            }
        }
    }
}

class StoredProc
{
    public void ToTheDB()
    {
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TestDatabase"].ConnectionString))
        {
            con.Open();
            using(SqlCommand cmd = new SqlCommand("Procedure",con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                // cmd.Parameters.AddWithValue("FirstName", newUser.firstName);
                cmd.ExecuteNonQuery();
            }
        }
    }
}

}

Arkadiusz Kałkus · Accepted Answer

First of all - IMO it's a badly written code.

My advises:

Do not mix the models and SQL queries or any database logic.
Do not use plain SQL in C# code, but rather use Entity Framework or Stored Procedures.
Do not save multiple entities which share the same business logic without wrapping them with single transaction.

You have asked: "can I use instances of existing objects (three of them) in the storedproc class so I can use one (already written) stored procedure"

The answer is - you can hardly use the existing code in that way. You don't have the stored procedure as far as I can see. You have just strings with SQL queries.

Anyway, you can try to use your classes as models in storedproc class and create new stored procedure which uses them.

It should look something like this:

class StoredProc
{
    public void ToTheDB(EndUser endUser, Company company, Bank bank)
    {
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TestDatabase"].ConnectionString))
        {
            con.Open();
            using(SqlCommand cmd = new SqlCommand("Procedure",con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                //Here you can use data from your "model" classes and add them as parameters for your stored procedure.
                cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = endUser.firstName;
                //the rest of the parameters from EndUser, Company and Bank classes

                cmd.ExecuteNonQuery();
            }
        }
    }
}

Once again, you should separate your logic and models.

It's an example model:

public class Bank
{
    public string name { get; set; }
    public string sortCode { get; set; }
}

Ant this is method of Data Access Layer or Repository:

void AddBank(Bank bank)
{
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["TestDatabase"].ConnectionString))
        {
            con.Open();
            //Procedure for inserting
            using(SqlCommand cmd = new SqlCommand("Procedure",con))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = bank.name;
                cmd.ExecuteNonQuery();
            }
        }
}

Multiple Object use across class c#

Answers (2)

Related Questions