C# i obsługa wielu tabel SQL

Cześć Wszystkim,

Mam pewien dylemat na temat właściwej konstrukcji obsługi SQL w C#. Mam kilka tabel w bazie (w moim przypadku to baza sqlite) I tu przy budowie klasy do obsługi sql pojawia się problem. Która konstrukcja będzie właściwa:

1. Metoda z klasy sql przyjmuje jako argument kompletny string jaki jest przekazywany do sqlCommnad. Dzięki temu mam możliwość budowania zapytania sql na poziomie obiektu operującego danymi, nie ma konieczności tworzenia przeciążonych klas w zależności od ilości aktualizowanych / dodawanych pól i nazewnictwa pól w tabeli.

public void SaveNew(string sqlCommandText)
        {
		//ciało metdy
	}

2. Metoda z klasy sql przyjmuje jako argument nazwę tabeli oraz nazwy pól bazy i wartości. Taka konstrukcja pozwala wywołać metodę z klasy sql podając docelową tabelę, nazwę pola oraz wartość, wymaga to jednak w zależności ilości wprowadzanych danych do tabeli w jednym zapytaniu przeciążania metody, podstawowa metoda przyjmuje tylko jedną wartość do zmiany / zapisu w tabeli, przeciążone metody zapewniają obsługę bazy w przypadku kiedy w jednym zapytaniu sql chcemy zaktualizować / zapisać więcej niż jedno pole.

        public void SaveNew(string table, string field, string value)
        {
        }

        public void SaveNew(string table, string field1, string value1, string field2, string value2)
        {
        }

I tu pytanie do speców, jak sensownie to rozwiązać, by było wszystko czytelne i możliwe w przyszłości do adaptacji do obsługi nowo dodanych tabel.

Najwłaściwiej będzie tak:

public void AddPerson(string name, string surname)
{
	using(var conn = new SqlConnection("connect-string"))
	{
		using(var cmd = new SqlCommand("cmd-text", conn))
		{
			cmd.Parameters.AddWithValue("@name",name);
			cmd.Parameters.AddWithValue("@surname",surname);
			
			conn.Open();
			cmd.ExecuteNonQuery();
		}
	}
}

Pisane trochę z pamięci, ale po nitce do kłębka dojdziesz o co chodzi.

Ale w bazie jest kilka tabel, tabele mają różne nazewnictwo, nie zawsze chcę zaktualizować wszystkie wiersze tabeli. Nie mogę zakładać stałego cmd-text. Taką metodę rozważałem ale problemem staje się mnogość przeciązonych funkcji.

Nie mogę zakładać stałego cmd-text - i nie znajdziesz. Jeżeli chcesz budować zapytanie dynamicznie, w zależności od danych wejściowych to najlepiej będzie do tego zrobić procedurę składowaną i w niej składać zapytanie korzystając z dynamic-sql. Należy przy tym zadbać o walidację wejścia, ponieważ takie rzeczy są podatne na sql-injection, o czym chyba ostatnio pisałem na mikroblogu.

Jeżeli natomiast planujesz odpalać po kolei inserty/update'y w zależności od danych wejściowych to także zrób to w procedurze przechowywanej. Po prostu w zależności od warunków, które tam sobie wymyślisz wykonuj wstawianie czy aktualizację. Możesz też spiąć to w transakcję dzięki czemu operacja nabierze atomowego charakteru.

Nazwę takiej procedury wpisujesz później w cmd-text, a sam typ zapytania ustawiasz w C# jako StoredProcedure.

Sqlite nie obsługuje StoredProcedure. Insert czy update muszę składać w metodzie obsługującej sql bądź też przekazywać jako parametr metody z klasy sql operującej na danych w bazie.

A czemu po prostu nie użyjesz jakiegoś istniejącego rozwiązania?

somekind napisał(a):

A czemu po prostu nie użyjesz jakiegoś istniejącego rozwiązania?

Podaj jakiś sensowny przykład, bo wszystkie co do tej pory znalazłem wymuszają tworzenie par metoda / tablica sql, a ja chciałbym obskoczyć jedną metodą wszystkie przypadki insert / update.

Nie wiem jaki jest cel - opisałeś jedynie jak próbujesz go zrealizować i wspomniałeś coś o "klasie do obsługi SQL". Generalnie jeśli chodzi o obsługę baz danych, to najpierw bierze się jakiegoś ORMa, a jeśli żaden nie spełnia wymagań, to dopiero można myśleć o pisaniu własnych rozwiązań.

Jeśli chodzi o to, co opisałeś w pierwszym poście, to punkt 2. można ulepszyć tworząc prosty obiekt przechowujący nazwę kolumny i wartość, i podając do metody kolekcję takich obiektów. Wówczas nie potrzebujesz żadnych przeciążeń metod, bo jedna będzie w stanie obsłużyć zmianę w tylu kolumnach tabeli, w ilu chcesz.

somekind napisał(a):

Jeśli chodzi o to, co opisałeś w pierwszym poście, to punkt 2. można ulepszyć tworząc prosty obiekt przechowujący nazwę kolumny i wartość, i podając do metody kolekcję takich obiektów. Wówczas nie potrzebujesz żadnych przeciążeń metod, bo jedna będzie w stanie obsłużyć zmianę w tylu kolumnach tabeli, w ilu chcesz.

Coś takiego chodzi mi po głowie. A więc stworzyłem obiekt przechowujący parę nazwa kolumny / wartość.

     public class SQLData
    {
        public string field { get; private set; }
        public string value { get; private set; }

        public SQLData(string field, string value)
        {
            this.field = field;
            this.value = value;
        }
    }

A następnie metoda z klasy sql przyjmuje jako parametr ten obiekt lub listę generyczną tych obiektów

        public static void SaveNew(string table, List<SQLData> data)
        {
            using (SQLiteConnection connection = getConnection())
            {
                connection.Open();

                string fields = "";
                string values = "";


                foreach(SQLData x in data)
                {
                    fields = x.field + ", ";
                    values = "@" + x.field + ", ";
                }


                using (SQLiteCommand insertSQL = new SQLiteCommand("INSERT INTO "+ table +" ("+ fields.Substring(0, fields.Length - 2) + ") VALUES (" + values.Substring(0, values.Length - 2) + ")", connection))
                {
                    foreach (SQLData x in data)
                    {
                        insertSQL.Parameters.AddWithValue("@" + x.field, x.value);
                    }

                    try
                    {
                        insertSQL.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message);
                    }
                }

            }
        }

Może nie jest to optymalne rozwiązanie, ale przynajmniej pozwoli mi jedną metodą obsłużyć wszystkie tabele bez znaczenia na ilość danych i nazewnictwo kolumn.

Pytanie tylko, czy trzymanie danych w aplikacji jako lista par klucz-wartość jest dobre. No, ale to zależy od rodzaju aplikacji oczywiście.