//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);
}