2 replies to this topic

[Payload]

Ich habe keine Ahnung von C# und nen Kumpel hat Probleme mit seinem Code.
Es geht um nen "Combo Manager" welcher Mail:Pass in ner SQLite speichert und liest.
Laut ihm wird das ganze extrem langsam ab 20m Lines.

Irgendwelche Ideen wie man das ganze optimieren könnte?

 

Source:
 

using Colorful;
using System.Data.SQLite;
using System;
using System.Drawing;
using System.IO;
using System.Linq;
using Console = Colorful.Console;

namespace TextManager
{
    class Program
    {
        static void Main()
        {
            Console.Title = "DB Manager";
            Console.WriteLine("\n");
            Console.WriteAscii("DB Manager", Color.LimeGreen);
            StyleSheet styleSheet = new StyleSheet(Color.White);
            styleSheet.AddStyle(@"(?<=\[)(.*)(?=\])", Color.LimeGreen);
            Console.WriteLineStyled("[" + DateTime.Now.ToString("HH:mm:ss") + "] What do you want to do?\n", styleSheet);
        START:
            Console.WriteLineStyled("[1] Insert combos from the import folder.", styleSheet);
            Console.WriteLineStyled("[2] Search in the database.\n", styleSheet);
        FALSE:
            Console.WriteStyled("[" + DateTime.Now.ToString("HH:mm:ss") + "]:", styleSheet);
            string text = System.Console.ReadLine();
            if (text == "1")
            {
                // Creates new sqlite database if it is not found
                using (var conn = new SQLiteConnection(@"Data Source=db.sqlite"))
                {
                    // Be sure you already created the Table!

                    conn.Open();
                    decimal valid = 0;
                    decimal dupe = 0;
                    decimal progress = 0;
                    decimal num = Directory.EnumerateFiles("import", "*.txt").Count();

                    Console.WriteStyled("\n[" + DateTime.Now.ToString("HH:mm:ss") + "] Started\n\n", styleSheet);
                    foreach (string txt in Directory.EnumerateFiles("import", "*.txt"))
                    {
                        num += File.ReadLines(txt).Count();
                    }
                    using (var cmd = new SQLiteCommand(conn))
                    {
                        foreach (string file in Directory.EnumerateFiles("import", "*.txt"))
                        {
                            using var transaction = conn.BeginTransaction();
                            string[] lines = File.ReadAllLines(file);
                            decimal bar = Math.Round(100 / num * progress);
                            Console.WriteStyled("\r[" + DateTime.Now.ToString("HH:mm:ss") + "] " + bar.ToString() + "%\n", styleSheet);

                            foreach (string line in lines)
                            {
                                if (line.Contains("'") == true)
                                {
                                    continue;
                                }
                                if (line.Split(':').Length != 2)
                                {
                                    continue;
                                }
                                string USER = line.Split(':')[0];
                                string PASS = line.Split(':')[1];
                                cmd.CommandText = $"INSERT INTO Mailpass (Email, Password) VALUES ('{USER}','{PASS}');";

                                try
                                {
                                    cmd.ExecuteNonQuery();
                                    progress++;
                                }
                                catch
                                {
                                    dupe++;
                                }
                            }
                            transaction.Commit();
                            string r = file.Substring(file.IndexOf(@"\") + 1);
                            Directory.Move(file, "imported\\" + r);
                        }
                    }

                    Console.WriteLineStyled("[" + DateTime.Now.ToString("HH:mm:ss") + "] Inserted " + valid.ToString() + " lines and filtered " + dupe.ToString() + " duplicates.\n", styleSheet);

                    conn.Close();
                }
                goto START;
            }
            else if (text == "2")
            {
                Console.WriteLineStyled("\n[" + DateTime.Now.ToString("HH:mm:ss") + "] Please enter the email you want to look up.\n", styleSheet);
                Console.WriteStyled("[" + DateTime.Now.ToString("HH:mm:ss") + "]: ", styleSheet);
                string text2 = System.Console.ReadLine();
                using var con = new SQLiteConnection(@"Data Source=db.sqlite");
                con.Open();

                string stm = $"SELECT Email,Password FROM MailPass WHERE Email LIKE '{text2}';";

                using var cmd = new SQLiteCommand(stm, con);
                using SQLiteDataReader rdr = cmd.ExecuteReader();

                if (rdr.HasRows)
                {
                    Console.WriteLineStyled("\n[" + DateTime.Now.ToString("HH:mm:ss") + "] Found matching result(s).\n", styleSheet);
                    Console.WriteLine($"{rdr.GetName(0)} {rdr.GetName(1)}");
                    while (rdr.Read())
                    {

                        Console.WriteLine($"{rdr.GetString(0),-10} {rdr.GetString(1),-11}", styleSheet);
                    }
                }
                else
                {
                    Console.WriteLineStyled("\n[" + DateTime.Now.ToString("HH:mm:ss") + "] Email does not exist in the database.", styleSheet);
                }
                Console.WriteLine("");
                con.Close();
                goto START;
            }
            else
            {
                Console.WriteLineStyled("Please type 1 or 2", styleSheet);
                goto FALSE;
            }
        }
    }
}

SQLite File:

 

CREATE TABLE "MailPass" (
	"Email"	TEXT NOT NULL,
	"Password"	TEXT NOT NULL,
	UNIQUE("Password","Email")
)

[IRET]

1. statt FIle.ReadAllLines() den Stream Zeile für Zeile lesen.

using (System.IO.StreamReader f = new System.IO.StreamReader(@"c:\test.txt") {
    while((line = file.ReadLine()) != null)
        // do smth with line
}

2. SQL-Insert unterstützt auch mehrere Datensätze auf einmal. Am besten Chunks von 50 Datensätzen sammeln und die auf einmal in die DB eintragen.

INSERT INTO Mailpass (Email, Password) VALUES
('USER1','PASS1'),
('USER2','PASS2'),
('USER3','PASS3');

Außerdem schau dir mal Prepared Statements an. Hast du SQLIs drinnen.

[ProHex]

1. statt FIle.ReadAllLines() den Stream Zeile für Zeile lesen.

using (System.IO.StreamReader f = new System.IO.StreamReader(@"c:\test.txt") {
    while((line = file.ReadLine()) != null)
        // do smth with line
}

2. SQL-Insert unterstützt auch mehrere Datensätze auf einmal. Am besten Chunks von 50 Datensätzen sammeln und die auf einmal in die DB eintragen.

INSERT INTO Mailpass (Email, Password) VALUES
('USER1','PASS1'),
('USER2','PASS2'),
('USER3','PASS3');

Außerdem schau dir mal Prepared Statements an. Hast du SQLIs drinnen.

 

Lovely as usual