C# Discussion Boards - CodeProject

Re: Parse 17,000 text files and insert into database

15-Nov-16 8:17

davers wrote:
I believe they prohibit bulk inserts here

Does that mean you can't prepare a DataTable and send the lot via a DataAdapter? That doesn't use the SqlBulkOperations class, but it does mean that the insert becomes one message in each direction instead of message-response-message-response-... which is going to be significantly slower as it's a new command parse and construct each time at the SQL end.
Performing Batch Operations Using DataAdapters[^]

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...

Gerry Schmitz15-Nov-16 8:19

Gerry Schmitz

15-Nov-16 8:19

Glad you've narrowed it down, Dave.

As others have suggested, if you can do a BULK INSERT from a file, then parse all your data first into a single (csv) file and pass that to BULK INSERT. Very fast.

Re: Parse 17,000 text files and insert into database

peterkmx15-Nov-16 8:50

peterkmx

15-Nov-16 8:50

Indeed, parsing is usually faster than inserting each record separately using SQL. I did a quick search right now and this [^] resembles a possible approach using parametrized SQL, but it is also possible without explicit "BULK" operation, just a parametrized insert query would do (wrapped in a a SQL transaction...)
Cheers,

Re: Parse 17,000 text files and insert into database

Philippe Mori15-Nov-16 8:11

Philippe Mori

15-Nov-16 8:11

Your code is prone to SQL injection (if the source file can be modified).
You might want to process files in order.
Your code do not validate that the data is the expected one.
There are a bunch of hard-coded constant.

If you do it one, then it does not matters much but it would also do not matters much that it would take about one hour to process the file.

One thing that would have a significant impact on the performance is to keep the connection opened for the whole importation if it is not already done.

Philippe Mori

Re: Parse 17,000 text files and insert into database

Nathan Minier15-Nov-16 8:33

Nathan Minier

15-Nov-16 8:33

I would think a RegEx would work a bit faster.

"There are three kinds of lies: lies, damned lies and statistics."
- Benjamin Disraeli

Re: Parse 17,000 text files and insert into database

Mycroft Holmes15-Nov-16 11:54

Mycroft Holmes

15-Nov-16 11:54

I would do this completely differently.

Read the text files into memory
Insert each record into a data table in memory - no transformation
Bulk copy the raw data into a staging table in sql server
Use a stored procedure to transform the data.

Bulk copy and stored procedures will reduce the impact of the primary bottle necks of IO and transforms

Never underestimate the power of human stupidity
RAH

Re: Parse 17,000 text files and insert into database

Bernhard Hiller15-Nov-16 21:33

Bernhard Hiller

15-Nov-16 21:33

Two simple things to consider:
- at 4-5 files per second, those 17000 files will take an hour. So what? Run it somewhen during night time when nobody else is at work.
- SQL statements can be Prepared. That means you have to change it to a parameterized query first, call Prepare, and later only add the fresh parameters before calling ExecuteNonQuery.

Re: Parse 17,000 text files and insert into database

Jimmanuel23-Nov-16 10:48

Jimmanuel

23-Nov-16 10:48

I recently had a similar problem and gained some speed with a 2 step change: parallelize the

foreach (string f in files)

and then ditch the file streaming in favor of File.ReadAllLines(...). Basically the disk was my bottleneck and it performed better doing a bulk reads of data instead of streaming line by line; after each bulk read I had a chunk of data in memory that I could process bound only by the limitations of my CPU. This does, of course, assume that you have some memory to spare because you're reading entire files in at a time. There was also a limit though as to how many concurrent file reads I could throw at my disk, after a certain amount the performance dropped significantly so I had to throttle back how many parallel file reads my app could do. I did some trial and error to find the best number, YMMV.

That would also require you to redo your database access though since you probably don't want to be doing that from multiple threads, but that kind of goes along with some of the other suggestions you've received. Collecting that data in memory and do a bulk insert later on instead of individual inserts would make a big difference, too.

Badger | [badger,badger,badger,badger...]

Develop Web Service REST

Member 1283622014-Nov-16 3:37

Member 12836220

14-Nov-16 3:37

I want to develop a Web Service restful CRUD between a data base PostgreSQL and a web application in asp. I look many examples and tutorials but I've not find solution. I'am here :
I have a service "Service191" that I can call by Mozilla or by WCF Test Client :

public class Service191 : IService191
{
    public string data;
    public static NpgsqlConnection conn;

    /*
     * Connection à la base de donnée
     */
    public void connection()
    {
        try
        {
                Mails mail = new Mails();
                string strConnString = "Server=194.206.X.XXX; Port=5432; Database=XXXXX; User Id=XXXX; Password=XXXXX";
                DAL.DAL dal = new DAL.DAL(strConnString);

                //TestSelectCommand(mail, dal);
                //TestXMLSerialization();


                GenerateGetRequest();
                //GeneratePostRequest();


        }
        catch (Exception e)
        {
            MessageBox.Show(e.ToString());
        } 
    }

For test this I call the function GenerateGetRequest() :

private static void GenerateGetRequest()
    {
        string url = "http://localhost:49761/Service191.svc/mails?id=14";
        HttpWebRequest GETRequest = (HttpWebRequest)WebRequest.Create(url);
        GETRequest.Method = WebRequestMethods.Http.Get;

       HttpWebResponse GETResponse = (HttpWebResponse)GETRequest.GetResponse();
        Stream GetResponseStream = GETResponse.GetResponseStream();
        StreamReader sr = new StreamReader(GetResponseStream);

        MessageBox.Show(sr.ReadToEnd());
    }

Url is what my web application send later. "Mails" is the table where the web service will do the request. I've got too a Handler :

public class Handler : IHttpHandler
{
    private DAL.DAL dal;
    private string connString;
    private Mails mail;
    private ErrorHandler.ErrorHandler errHandler;

    #region HANDLER

    public bool IsReusable
    {
        get
        {
            throw new NotImplementedException();
        }
    }

    public void ProcessRequest(HttpContext context)
    {
        try
        {
            string url = Convert.ToString(context.Request.Url);
            connString = "Server = 194.206.X.XXX; Port = 5432; Database = XXXX; User Id = XXXX; Password = XXXXX";
            dal = new DAL.DAL(connString);
            errHandler = new ErrorHandler.ErrorHandler();

            switch (context.Request.HttpMethod)
            {
                case "GET":
                    READ(context);
                    break;
                case "POST":
                    CREATE(context);
                    break;
                case "PUT":
                    UPDATE(context);
                    break;
                case "DELETE":
                    DELETE(context);
                    break;
                default:
                    break;
            }
        }
        catch (Exception ex)
        {
            errHandler.ErrorMessage = ex.Message.ToString();
            context.Response.Write(errHandler.ErrorMessage);
            //MessageBox.Show(ex.ToString());
        }
    }

    #endregion

    #region CRUD

    private void READ(HttpContext context)
    {
        try
        {
            int id = Convert.ToInt16(context.Request["id"]);
            mail = dal.GetMail(id);
            if (mail == null)
                context.Response.Write(id + "No mail found");

            string serialized = Serialize(mail);
            context.Response.ContentType = "text/xml";
            WriteResponse(serialized);
            MessageBox.Show("mail READ");
        }
        catch (Exception ex)
        {
            errHandler.ErrorMessage = dal.GetException();
            errHandler.ErrorMessage = ex.Message.ToString();
            //MessageBox.Show(ex.ToString());
        }
    }

    private void CREATE(HttpContext context)
    {
        try
        {
            byte[] PostData = context.Request.BinaryRead(context.Request.ContentLength);
            string str = Encoding.UTF8.GetString(PostData);
            Mails mail = Deserialize(PostData);
            dal.AddMail(mail);
            MessageBox.Show("mail CREATE");
        }
        catch (Exception ex)
        {
            errHandler.ErrorMessage = dal.GetException();
            errHandler.ErrorMessage = ex.Message.ToString();
            //MessageBox.Show(ex.ToString());
        }

    }

    private void UPDATE(HttpContext context)
    {
        try
        {
            byte[] PUTRequestByte = context.Request.BinaryRead(context.Request.ContentLength);
            context.Response.Write(PUTRequestByte);

            Mails mail = Deserialize(PUTRequestByte);
            dal.UpdateMail(mail);
            MessageBox.Show("mail UPDATE");
        }
        catch (Exception ex)
        {
            errHandler.ErrorMessage = dal.GetException();
            errHandler.ErrorMessage = ex.Message.ToString();
            //MessageBox.Show(ex.ToString());
        }
    }

    private void DELETE(HttpContext context)
    {
        try
        {
            int id = Convert.ToInt16(context.Request["id"]);
            dal.DeleteMail(id);
            MessageBox.Show("mail DELETE");
        }
        catch (Exception ex)
        {
            errHandler.ErrorMessage = dal.GetException();
            errHandler.ErrorMessage = ex.Message.ToString();
        }
    }

    #endregion

    private Mails Deserialize (byte[] xmlByteData)
    {
        try
        {
            XmlSerializer ds = new XmlSerializer(typeof(Mails));
            MemoryStream memoryStream = new MemoryStream(xmlByteData);
            Mails mail = new Mails();
            mail = (Mails)ds.Deserialize(memoryStream);
            return mail;
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
            throw;
        }
    }

    private static void WriteResponse(string strMessage)
    {
        HttpContext.Current.Response.Write(strMessage);
    }

    private String Serialize(Mails mail)
    {
        try
        {
            String XmlizedString = null;
            XmlSerializer xs = new XmlSerializer(typeof(Mails));
            MemoryStream memoryStream = new MemoryStream();
            XmlTextWriter xmlTextWriter = new XmlTextWriter(memoryStream, Encoding.UTF8);
            xs.Serialize(xmlTextWriter, mail);
            memoryStream = (MemoryStream)xmlTextWriter.BaseStream;
            XmlizedString = UTF8ByteArrayToString(memoryStream.ToArray());
            return XmlizedString;
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
            throw;
        }
    }

    private String UTF8ByteArrayToString(Byte[] characters)
    {
        UTF8Encoding encoding = new UTF8Encoding();
        String constructedString = encoding.GetString(characters);
        return (constructedString);
    }
}

But I don't understand why my handler is never call. So I have always a 400 error.

I've got too a DAL class who permiss the connection et request to the database :

public class DAL
{
    private NpgsqlConnection conn;
    private NpgsqlCommand command;
    private static string connString;
    private static List<Mails> mailList;
    private ErrorHandler.ErrorHandler err;

    public DAL(string _connString)
    {
        err = new ErrorHandler.ErrorHandler();
        connString = _connString;
    }

    public void AddMail (Mails mail)
    {
        try
        {
            using (conn)
            {
                string npgsqlInsertString = "INSERT INTO mails (id_entete, emmetteur, destinataires, objet, contenu, date_envoi, heure_envoi) VALUES (@id_entete, @emmetteur, @destinataires, @objet, @contenu, @date_envoi, @heure_envoi)";

                conn = new NpgsqlConnection(connString);

                command = new NpgsqlCommand();
                command.Connection = conn;
                command.Connection.Open();
                command.CommandText = npgsqlInsertString;

                NpgsqlParameter idParam = new NpgsqlParameter("@id_entete", mail.Id_entete);
                NpgsqlParameter emmParam = new NpgsqlParameter("@id_entete", mail.Emmetteur);
                NpgsqlParameter destParam = new NpgsqlParameter("@id_entete", mail.Destinataires);
                NpgsqlParameter objParam = new NpgsqlParameter("@id_entete", mail.Objet);
                NpgsqlParameter contParam = new NpgsqlParameter("@id_entete", mail.Contenu);
                NpgsqlParameter dateParam = new NpgsqlParameter("@id_entete", mail.Date_envoi);
                NpgsqlParameter heureParam = new NpgsqlParameter("@id_entete", mail.Heure_envoi);

                command.Parameters.AddRange(new NpgsqlParameter[] { idParam, emmParam, destParam, objParam, contParam, dateParam, heureParam });
                command.ExecuteNonQuery();
                command.Connection.Close();

            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }
    }

    public void UpdateMail (Mails mail)
    {
        try
        {
            using (conn)
            {
                string npgsqlUpdateString = "UPDATE mails SET id_entete=@id_entete, emmetteur=@emmetteur, destinataires=@destinataires, objet=@objet, contenu=@contenu, date_envoi=@date_envoi, heure_envoi=@heure_envoi WHERE id=@id";

                conn = new NpgsqlConnection(connString);

                command = new NpgsqlCommand();
                command.Connection = conn;
                command.Connection.Open();
                command.CommandText = npgsqlUpdateString;

                NpgsqlParameter idParam = new NpgsqlParameter("@id_entete", mail.Id_entete);
                NpgsqlParameter emmParam = new NpgsqlParameter("@id_entete", mail.Emmetteur);
                NpgsqlParameter destParam = new NpgsqlParameter("@id_entete", mail.Destinataires);
                NpgsqlParameter objParam = new NpgsqlParameter("@id_entete", mail.Objet);
                NpgsqlParameter contParam = new NpgsqlParameter("@id_entete", mail.Contenu);
                NpgsqlParameter dateParam = new NpgsqlParameter("@id_entete", mail.Date_envoi);
                NpgsqlParameter heureParam = new NpgsqlParameter("@id_entete", mail.Heure_envoi);

                command.Parameters.AddRange(new NpgsqlParameter[] { idParam, emmParam, destParam, objParam, contParam, dateParam, heureParam });
                command.ExecuteNonQuery();
                command.Connection.Close();
            }
        }
        catch (Exception ex)
        {
            err.ErrorMessage = ex.Message.ToString();
            throw;
        }
    }

    public void DeleteMail (int id)
    {
        try
        {
            using (conn)
            {
                string npgsqlDeleteString = "DELETE FROM mails WHERE id=@id";

                conn = new NpgsqlConnection(connString);

                command = new NpgsqlCommand();
                command.Connection = conn;
                command.Connection.Open();
                command.CommandText = npgsqlDeleteString;

                NpgsqlParameter idParam = new NpgsqlParameter("@id", id);
                command.Parameters.Add(idParam);
                command.ExecuteNonQuery();
                command.Connection.Close();
            }
        }
        catch (Exception ex)
        {
            err.ErrorMessage = ex.Message.ToString();
            throw;
        }
    }

    public Mails GetMail(int ID)
    {
        try
        {
            if (mailList == null)
            {
                mailList = GetMails();
            }
            foreach (Mails mail in mailList)
            {
                if (mail.Id == ID)
                {
                    return mail;
                }
            }
            return null;
        }
        catch (Exception ex)
        {
            //MessageBox.Show(ex.ToString());
            err.ErrorMessage = ex.Message.ToString();
            throw;
        }
    }

    private List<Mails> GetMails()
    {
        try
        {
            using (conn)
            {
                mailList = new List<Mails>();

                conn = new NpgsqlConnection(connString);
                string npgsqlSelectString = "SELECT * FROM mails";
                command = new NpgsqlCommand(npgsqlSelectString, conn);
                command.Connection.Open();

                NpgsqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Mails mail = new Mails();
                    mail.Id = (int)reader[0];
                    mail.Id_entete = (int)reader[1];
                    mail.Emmetteur = reader[2].ToString().Replace(" ", "");
                    mail.Destinataires = reader[3].ToString().Replace(" ", "");
                    mail.Objet = reader[4].ToString().Replace(" ", "");
                    mail.Contenu = reader[5].ToString().Replace(" ", "");
                    mail.Date_envoi = reader[6].ToString().Replace(" ", "");
                    mail.Heure_envoi = reader[7].ToString().Replace(" ", "");
                    mailList.Add(mail);
                }
                command.Connection.Close();
                return mailList;
            }
        }
        catch (Exception ex)
        {
            //MessageBox.Show(ex.ToString());
            err.ErrorMessage = ex.Message.ToString();
            throw;
        }
    }

    public string GetException()
    {
        return err.ErrorMessage.ToString();
    }
}

So, who can I do for call the function ProcessRequest(HttpContext context) ? Thank you for your help and sorry for my bad english ... ! Smile | :)

Textbox updation

Avinash.Banda14-Nov-16 0:24

14-Nov-16 0:24

Hi All,
In my project textbox will update value based on the timer tick trigger, i want to bypass the textbox update when i want to change value in textbox.

OriginalGriff14-Nov-16 0:31

14-Nov-16 0:31

And?
What have you tried?
Where are you stuck?
What help do you need?

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...

Avinash.Banda14-Nov-16 0:36

14-Nov-16 0:36

I tried textbox enter property, so when i select the text box it is not updating and i used textbox leave property to update the entered value. but i have 50 textboxes. i want to make a common function for all textboxes.

OriginalGriff14-Nov-16 0:39

14-Nov-16 0:39

So do: you can call the same event handler for all your boxes, and use the Tag property to hold the "I'm editing" flag. You get the appropriate TextBox instance passed in as the sender parameter of the event handler.

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...

Avinash.Banda14-Nov-16 0:43

14-Nov-16 0:43

i am newbee for c#, can please explain how to do.

OriginalGriff14-Nov-16 0:45

14-Nov-16 0:45

Which bit? This is all basic stuff ...

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...

Avinash.Banda14-Nov-16 0:52

14-Nov-16 0:52

thats ok. but i don't know, which active control is enabled when i select textbox.

OriginalGriff14-Nov-16 1:03

14-Nov-16 1:03

Sorry?
Do you mean you don't know which control it was when you enter the event handler? Because as I said, that is given to you as the sender parameter.

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...

Avinash.Banda14-Nov-16 1:04