//INSERT INTO table_name (column1, column2, column3, ...) VALUES(value1, value2, value3, ...); public static int Insert(SQLDBConnection myDB, string table_name, string cols, string values) { int result = 0; string query = $"{INSERTINTO}{table_name} ({cols}) VALUES ({values})"; if (myDB.GetConnection()) { myDB.CreateCMD(); myDB.SetSQLQuery(query); try { result = myDB.CMD.ExecuteNonQuery(); } catch (SqlException MySqlError) { Console.WriteLine(MySqlError.Message); } } else { Console.WriteLine("ERROR Trying to connect", Color.Red); } myDB.Close(); return(result); }
//UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; // colAndVal = "column1 = value1, column2 = value2" // condition = ""column1 LIKE 'Archivo'" public static int Update(SQLDBConnection myDB, string table_name, string colAndVal, string condition) { int result = 0; string query = $"UPDATE {table_name} SET {colAndVal} WHERE {condition}"; if (myDB.GetConnection()) { myDB.CreateCMD(); myDB.SetSQLQuery(query); try { result = myDB.CMD.ExecuteNonQuery(); } catch (SqlException MySqlError) { Console.WriteLine(MySqlError.Message); } } else { Console.WriteLine("ERROR Trying to connect", Color.Red); } myDB.Close(); return(result); }
//EXECUTE AvailableRooms '01/09/2020', '02/16/2020'; public static DataTable ReadFromSP(SQLDBConnection myDB, string sp, DateTime[] checkIN_OUT) { string query; SqlDataReader readerCollection = null; DataTable dt = new DataTable(); query = $"EXECUTE {sp} '{checkIN_OUT[0].ToString("MM/dd/yyyy")}', '{checkIN_OUT[1].ToString("MM/dd/yyyy")}'"; if (myDB.GetConnection()) { myDB.CreateCMD(); myDB.SetSQLQuery(query); try { readerCollection = myDB.CMD.ExecuteReader(); dt.Load(readerCollection); } catch (SqlException MySqlError) { Console.WriteLine(MySqlError.Message); } } else { Console.WriteLine("ERROR Trying to connect", Color.Red); } myDB.Close(); return(dt); }
public static void LoadRooms(SQLDBConnection myDB) { int result; //Para los resultados de las consultas RUDI bool exit; DataTable dTable; Console.WriteLine("Section Para Cargar Las Habitaciones (RoomID -> Automatic, Disponibilidad -> 0/1)"); do { string strAvailable, roomNumber; Console.Write("Room Number: "); roomNumber = Console.ReadLine(); Console.Write("Disponibilidad: "); strAvailable = Console.ReadLine(); if (strAvailable == "0" || strAvailable == "1") { //result = RUDI.Insert(myDB, "Rooms", "RoomID, Available", $"{roomID},{Convert.ToString( (Convert.ToInt32(strAvailable) == 1) ? 0 : 1)}"); result = RUDI.Insert(myDB, "Rooms", "RoomNumber, Available", $"{roomNumber},{Convert.ToString((Convert.ToInt32(strAvailable) == 1) ? 0 : 1)}"); if (result == 1) { int roomID; dTable = RUDI.Read(myDB, "Rooms", "RoomID", $"RoomNumber = {roomNumber}"); //SELECT RoomID FROM Rooms WHERE RoomNumber = roomNumber roomID = Convert.ToInt32(dTable.Rows[0]["RoomID"]); Console.WriteLine($"La Habitación {roomNumber} ha sido añadida con exito bajo el ID#: {roomID}"); } exit = false; } else { exit = true; } } while (!exit); }
//------------------------------------------------------------------------------------------------------------------------------------------------------- //SELECT column1, column2, ... FROM table_name WHERE condition; //https://stackoverflow.com/questions/41040189/fastest-way-to-map-result-of-sqldatareader-to-object public static async Task <T> Read2 <T>(SQLDBConnection myDB, string table_name, string cols = "*", string condition = null) where T : class, new() { string query; SqlDataReader readerCollection = null; if (condition != null) { query = $"SELECT {cols} FROM {table_name} WHERE {condition}"; } else { query = $"SELECT {cols} FROM {table_name}"; } if (myDB.GetConnection()) { myDB.CreateCMD(); myDB.SetSQLQuery(query); try { readerCollection = myDB.CMD.ExecuteReader(); } catch (SqlException MySqlError) { Console.WriteLine(MySqlError.Message); } } else { Console.WriteLine("ERROR Trying to connect", Color.Red); } myDB.Close(); if (readerCollection.HasRows) { var newObject = new T(); if (await readerCollection.ReadAsync()) { MapDataToObject(readerCollection, newObject); } return(newObject); } else { return(null); } //return readerCollection; }
// https://www.experts-exchange.com/questions/29106915/C-Convert-SQLDataReader-to-List.html public static List <T> Read1 <T>(SQLDBConnection myDB, string table_name, string cols = "*", string condition = null) where T : class, new() { string query; SqlDataReader readerCollection = null; if (condition != null) { query = $"SELECT {cols} FROM {table_name} WHERE {condition}"; } else { query = $"SELECT {cols} FROM {table_name}"; } if (myDB.GetConnection()) { myDB.CreateCMD(); myDB.SetSQLQuery(query); try { readerCollection = myDB.CMD.ExecuteReader(); } catch (SqlException MySqlError) { Console.WriteLine(MySqlError.Message); } } else { Console.WriteLine("ERROR Trying to connect", Color.Red); } List <T> res = new List <T>(); while (readerCollection.Read()) { T t = new T(); for (int inc = 0; inc < readerCollection.FieldCount; inc++) { Type type = t.GetType(); PropertyInfo prop = type.GetProperty(readerCollection.GetName(inc)); //https://gist.github.com/mrkodssldrf/7023997 } res.Add(t); } readerCollection.Close(); return(res); }
// Recibe DNI y busca si el cliente existe en la DB public static bool ClientExist1(SQLDBConnection myDB, string strDNI) { DataTable dTable; if (strDNI.Length == 9) { dTable = RUDI.Read(myDB, "Clients", "ClientID", $"DNI LIKE '{strDNI.ToUpper()}'"); //SELECT ClientID FROM Clients WHERE DNI = strDNI if (dTable != null && dTable.Rows.Count > 0) { return(true); } } return(false); }
//Carga Clienets en la DB public static void LoadClients(SQLDBConnection myDB) { int result; //Para los resultados de las consultas RUDI bool exit; DataTable dTable; Console.WriteLine("Section Para Registrar Clientes"); do { string strFirstName, strLastName, strDNI; Console.Write("DNI: "); strDNI = Console.ReadLine(); if (strDNI != "0" && !ClientExist1(myDB, strDNI)) { Console.Write("Name: "); strFirstName = Console.ReadLine(); Console.Write("Last Name: "); strLastName = Console.ReadLine(); result = RUDI.Insert(myDB, "Clients", "Name, LastName, DNI", $"'{strFirstName}', '{strLastName}', '{strDNI.ToUpper()}'"); if (result == 1) { int clientID; dTable = RUDI.Read(myDB, "Clients", "ClientID", $"DNI LIKE '{strDNI}'"); //SELECT ClientID FROM Clients WHERE DNI = strDNI clientID = Convert.ToInt32(dTable.Rows[0]["ClientID"]); Console.WriteLine($"El cliente '{strFirstName} {strLastName}' ha sido creado con exito bajo el ID#: {clientID}"); } exit = false; } else { if (strDNI == "0") //Este if se puede quitar, es solo para romper el do while, cuando el DNI es 0 { exit = true; } else { Console.WriteLine("ERROR -> El cliente Ya existe en la BD. Intente con otro DNI", Color.Red); Menu.WriteContinue(); exit = false; } } } while (!exit); }
// http://www.codedigest.com/CodeDigest/172-How-to-Convert-SqlDataReader-object-to-DataTable-in-C---ADO-Net-.aspx public static DataTable Read(SQLDBConnection myDB, string table_name, string cols = "*", string condition = null) { string query; SqlDataReader readerCollection = null; DataTable dt = new DataTable(); if (condition != null) { query = $"SELECT {cols} FROM {table_name} WHERE {condition}"; } else { query = $"SELECT {cols} FROM {table_name}"; } if (myDB.GetConnection()) { myDB.CreateCMD(); myDB.SetSQLQuery(query); try { readerCollection = myDB.CMD.ExecuteReader(); dt.Load(readerCollection); } catch (SqlException MySqlError) { Console.WriteLine(MySqlError.Message); } } else { Console.WriteLine("ERROR Trying to connect", Color.Red); } myDB.Close(); return(dt); }