How to avoid writing same code for different types

Question

I'm writing a C# program with SQL Server and SQL Server CE. How can I avoid writing same code like below, variable databasestate is used to see if program should use SQL Server or SQL Server CE.

public static void Delete()
{
    SqlCeCommand commce = new SqlCeCommand("Delete From Projekat WHERE IDProjekat = @ConID", conce);

    SqlCommand comm = new SqlCommand("Delete From Projekat WHERE IDProjekat = @ConID", con);

    if (CrossFormData.databasestate == false)
    {
        commce.Parameters.AddWithValue("@ConID", CrossFormData.ProjectID);

        try
        {
            conce.Open();
            commce.ExecuteNonQuery();
            conce.Close();

            MessageBox.Show("Deleted!");
        }
        catch (Exception)
        {
            MessageBox.Show("Something went wrong");
            conce.Close();
        }
    }
    else
    {
        comm.Parameters.AddWithValue("@ConID", CrossFormData.ProjectID);

        try
        {
            con.Open();
            comm.ExecuteNonQuery();
            con.Close();

            MessageBox.Show("Deleted!");
        }
        catch (Exception)
        {
            MessageBox.Show("Something went wrong");
            con.Close();
        }
    }
}

If using SQL Server CE is making you angry, I'm sorry next time I will use SQLite.

If I don't find a way I will separate them in two different classes so it's more organized.

"If I don't find a way I will separate them in two different classes" - you make it sound like it's a bad thing. Why not just do that? — Xerillio
– Xerillio, Commented Jul 30, 2022 at 18:29
In any case, this question should be asked on codereview.stackexchange.com — Xerillio
– Xerillio, Commented Jul 30, 2022 at 18:30
are you sure that you have mysql as databse and not sql server? — nbk
– nbk, Commented Jul 30, 2022 at 18:46
Unrelated : con.Close(); should be in a finally block if you are not using a using. — Fildor
– Fildor, Commented Jul 30, 2022 at 18:57
Separating in two different classes isn't bad I didn't want it to sound that way, I just wanted to see if there is some other way, as for mysql I have database as well as sql server. Thank you for answering my question. — Notwellorganized
– Notwellorganized, Commented Jul 30, 2022 at 19:08

Bradley Grainger · Accepted Answer · 2022-07-30 19:46:13Z

0

Both SqlCeCommand and SqlCommand derive from DbCommand, so program against the base class:

var connection = CrossFormData.databasestate ? (DbConnection) con : conce;
using var command = connection.CreateCommand();
command.CommandText = "Delete From Projekat WHERE IDProjekat = @ConID";
command.Parameters.AddWithValue("@ConID", CrossFormData.ProjectID);

try
{
    connection.Open();
    command.ExecuteNonQuery();
    MessageBox.Show("Deleted!");
}
catch (DbException)
{
    MessageBox.Show("Something went wrong");
}
finally
{
    connection.Close();
}

Even better, use a helper library such as Dapper to eliminate a lot of this boilerplate:

var connection = CrossFormData.databasestate ? (DbConnection) con : conce;
connection.Execute("Delete From Projekat WHERE IDProjekat = @ConID", new { ConID = CrossFormData.ProjectID });

answered Jul 30, 2022 at 19:46

Bradley Grainger

28.3k4 gold badges95 silver badges117 bronze badges

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

Comments

Traveller · Accepted Answer · 2022-07-30 19:48:14Z

0

I fail to see how separating into two classes solves the issue of duplicate code, but here's a starting point: SqlCeCommand and SqlCommand both derive from DbCommand, so if you could rewrite the code maybe with an input parameter of the command (as a DbCommand) is should work regardless of which case it is. Additionally, from what I gather con and conce should either be the same class or share a parent class, so that hopefully shouldn't be an issue. I looked up the methods used as well, and they all come from DbCommand. Best of luck, and let me know if you need more info (or I can write some code to show you what I mean, I'm just at dinner right now lol).

answered Jul 30, 2022 at 19:48

Traveller

6146 silver badges17 bronze badges

2 Comments

Traveller Over a year ago

Aww, you beat me to it @Bradley Grainger...

Notwellorganized Over a year ago

Thank you for the answer, It would have helped me if Bradley hadn't already answered. I hope you had great dinner

Collectives™ on Stack Overflow

How to avoid writing same code for different types

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related