C# - komunikacja z baza danych Sql

W tym poście chciałbym opisać sposób komunikacji z bazą danych SQL.

[Źródło: https://docs.microsoft.com/en-us/dotnet/]

Program:

Na samym początku należy zdeklarować biblioteki jakie będą potrzebne do nawiązania komunikacji z bazą danych SQL.

1. using System.Data.Sql;
2. using System.Data.SqlClient;
3. using System.Data.SqlTypes;

Nawiązania połączenia wygląda następująco:

1. public class sqlCon
2. {
3.    protected static SqlConnection sqlCon;
5.    public static bool connectWithSqlDb()
6.    {
7.        sql.sqlConectionInfo.readConData();
9.        string conString = sql.sqlConectionInfo.prepareConString();
10.        sqlCon= new SqlConnection(conString);
11.        try
12.        {
13.            sqlCon.Open();
14.            sqlCon.Close();
15.            return true;
16.        }
17.        catch
18.        {
19.            return false;
20.        }
21.    }
22. }

Klasa powyżej odpowiada za nawiązanie i przetestowanie połączeni z bazą danych. Na samym początku pobiera informacje o połączeniu, po czym przygotowywany jest string z połączeniem. W przypadku poprawnego otwarcia bazy zwracana jest wartość True, gdy wystąpił błąd zwracana jest wartość False.

1. public class sqlConectionInfo : checkIfConStringData
2. {
3.     private static string serverNameStr = "";
4.     private static string dataBaseNameStr = "";
5.     private static string conStr = "";
6.     private static string userName = "";
7.     private static string userPassword = "";
9.     public static string ap_serverNameStr
10.     {
11.         get { return serverNameStr; }
12.         private set { serverNameStr = value; }
13.     }
15.     public static string ap_dataBaseNameStr
16.     {
17.         get { return dataBaseNameStr; }
18.         private set { dataBaseNameStr = value; }
19.     }
21.     public static string ap_conStr
22.     {
23.         get { return conStr; }
24.         private set { conStr = value; }
25.     }
27.     public static string ap_userName
28.     {
29.         get { return userName; }
30.         private set { userName = value; }
31.     }
33.     public static string ap_userPassword
34.     {
35.         get { return userPassword; }
36.         private set { userPassword = value; }
37.     }
39.     public static void readConData()
40.     {
41.         ap_serverNameStr = Properties.Settings.Default.serverNameRetData;
42.         ap_dataBaseNameStr = Properties.Settings.Default.dataBaseNameRetData;
43.         ap_conStr = checkIfConStringData.checkIfConStringWriteInMemIfNotWriteDef(Properties.Settings.Default.conStringRetData);
44.         ap_userName = Properties.Settings.Default.userNameRetData;
45.         ap_userPassword = Properties.Settings.Default.userPassRetData;
46.     }
48.     public static string prepareConString()
49.     {
50.         return String.Format(ap_conStr, ap_serverNameStr, ap_dataBaseNameStr, ap_userName, ap_userPassword);
51.     }
52. }
53. public class checkIfConStringData
54. {
55.     private const string conStrDefault = "Server={0};Database={1};user id={2};Password={3};Integrated       Security=True;MultipleActiveResultSets=True";
57.     protected static string checkIfConStringWriteInMemIfNotWriteDef(string conString)
58.     {
59.         if (conString == "")
60.         {
61.             return conStrDefault;
62.         }
63.         return conString;
64.     }
65. }

String z danymi o połączeniu przyjmuje takie parametry jak nazwa serwera (tutaj podaje się również port po przecinku np. nazwaserwera,port), nazwę bazy danych z którą dokonywane jest połączenie, login, hasło. Następnie jest parametr Integrated Securit, który ustawiony na true oznacza, że do uwierzytelniania wykorzystywane są dane dla konta Windows. Kolejny parametr czyli MultipleActiveResultSets dla true pozwala na używanie wielu instancji przy jednym połączeniu.

Komunikacja z bazą odbywa się poprzez zapytania SQL.

Aby odczytać wszystkie informacje w bazie można posłużyć się następującą funkcją:

1. public static DataSet readWholeTable()
2. {
3.     DataSet ds = new System.Data.DataSet();
5.     SqlCommand command;
7.     using (sqlCon)
8.     {
9.         sqlCon= new SqlConnection(sql.sqlConectionInfo.prepareConString());
10.         cnnCards.Open();
12.         command = new SqlCommand(sql.sqlComm.getAllData, sqlCon);
14.         SqlDataAdapter da = new SqlDataAdapter(command);
16.         da.Fill(ds);
17.         sqlCon.Close();
18.         da.Dispose();
19.     }
21.     return ds;
22. }

Tutaj tworzony jest element klasy DataSet, który będzie przechowywał odczytane dane. Wykonywana komenda wygląda następująco:

1. public const string getAllData = "SELECT * FROM name_of_table;";

W przypadku odczytu jednej komuny można posłużyć się następującą operacją:

1. public static List<string> readColumn()
2. {
3.     List<string> list = new List<string>();
4.     DataTable ds = new System.Data.DataTable();
5.     using (sqlCon)
6.     {
7.         try
8.         {
9.             sqlCon= new SqlConnection(sql.sqlGetDataConInfo.prepareConString());
10.             sqlCon.Open();
11.         }
12.         catch
13.         {
14.             list = null;
15.             sqlCon.Close();
16.             return list;
17.         }
18.        
19.         /* readColumn = "SELECT column_name FROM tablename;"; */
20.         using (SqlCommand cmd = new SqlCommand(sql.sqlComm.readColumn, sqlCon))
21.         {
22.             try
23.             {
24.                 using (SqlDataReader reader = cmd.ExecuteReader())
25.                 {
26.                     if (reader != null)
27.                     {
28.                         while (reader.Read())
29.                         {
30.                             list.Add(reader[sql.sqlPracComm.localizationColumnName].ToString());
31.                         }
32.                     }
33.                 }
34.             }
35.             catch
36.             {
37.                 list = null;
38.                 sqlCon.Close();
39.                 return list;
40.             }
41.         }
42.     }
43.     sqlCon.Close();
44.     return list;
45. }

Tutaj dane zostają zapisane do stworzonej listy, która po wykonaniu poprawnego odczytu zostaje zwrócona.

Dodanie nowego rekordu do bazy:

1. public static bool insertIntoDB(string name, string surname, string number)
2. {
3.     string insertData = "insert into tableName ([id], [name_first], [name_last], [numberData]) " +
4.                                 "values (@id, @name, @surname, @numberData);";
6.     int affectedRows = 0;
7.     SqlCommand command;
9.     Int32 id = readMaxIDInDb();
11.     if(id == -1)
12.     {
13.         return false;
14.     }
16.     id += 1;
18.     try
19.     {
20.         sqlCon = new SqlConnection(sql.sqlConectionInfo.prepareConString());
21.         sqlCon.Open();
22.         command = new SqlCommand(insertData, sqlCon);
24.         command.Parameters.AddWithValue("@id", id.ToString());
25.         command.Parameters.AddWithValue("@name", name);
26.         command.Parameters.AddWithValue("@surname", surname);
27.         command.Parameters.AddWithValue("@numberData", number);
28.         affectedRows = command.ExecuteNonQuery();
29.         sqlCon.Close();
30.     }
31.     catch
32.     {
33.         return false;
34.     }
36.     if (affectedRows > 0)
37.     {
38.         return true;
39.     }
41.     return false;
42. }

Tutaj najpierw tworzony jest nowy rozkaz, następnie sprawdzany jest aktualny numer ID w bazie po czym dodawane są dane i komenda zostaje wykonana. Gdy wystąpi błąd lub liczba wykonanych operacji będzie mniejsza od 1 to zwrócona zostaje wartość false, oznaczająca błąd wykonywania komendy.

1. private static Int32 readMaxIDInDb()
2. {
3.     Int32 readedIdValue = -1;
4.     SqlCommand command;
6.     try
7.     {
8.         sqlCon = new SqlConnection(sql.sqlConectionInfo.prepareConString());
9.         sqlCon.Open();
10.         /* readMaxIdValue = "SELECT MAX(cards.id) FROM tablename;"; */
11.         command = new SqlCommand(sql.sqlCardsComm.readMaxIdValue, sqlCon);
12.         readedIdValue = Convert.ToInt32(command.ExecuteScalar());
14.         sqlCon.Close();
16.         return readedIdValue;
17.      }
18.      catch
19.      {
20.         return -1;
21.      }
22. }

Wyszukiwanie osoby w bazie danych na podstawie imienia i nazwiska:

1. public static byte searchInDBBaseOnNameAndSurname(string name, string surname)
2. {
3.     byte readedRows = 0;
4.     try
5.     {
6.         sqlCon = new SqlConnection(sql.sqlConectionInfo.prepareConString());
7.         sqlCon.Open();
9.         string commandToExecute = String.Format(sql.sqlCardsComm.findPersonBaseOnNameOrSurname, name, surname);
11.         SqlCommand myCommand = new SqlCommand(commandToExecute, sqlCon);
12.         SqlDataReader myReader = myCommand.ExecuteReader();
13.         while (myReader.Read())
14.         {
15.             readedRows++;
16.         }
18.         sqlCon.Close();
19.     }
20.     catch
21.     {
22.         return 0;  
23.     }
24.     return readedRows;
25. }