예제 #1
0
        //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);
        }
예제 #2
0
        //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);
        }
예제 #3
0
        //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);
        }
예제 #4
0
        //-------------------------------------------------------------------------------------------------------------------------------------------------------

        //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;
        }
예제 #5
0
        // 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);
        }
예제 #6
0
        // 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);
        }