Insert statement into a Datatable

Question

I am trying to run an insert statement into a DataTable (I'm doing it this way so that all functions in the statement will be executed so I can get the intended values)

    private void insertintoDT(DataTable destination, string InsertStatement)
    {
        SqlCommand comm = new SqlCommand(InsertStatement);
        SqlDataReader reader = new SqlDataReader();

        destination.Load(reader);
    }
}

This DataTable will then be loaded into a DataGrid so I can see the values that will be inserted. I tried breaking up the insert statement like so:

    private void breakupInsert(String insert)
    {
        DataTable dt = new DataTable();
        dgvInsert.Rows.Clear();
        string stemp = insert;

        int len = stemp.Length;
        int itemp = 0;
        stemp = stemp.Remove(0, 12);
        itemp = stemp.IndexOf("VALUES") - 1;
        gvtable = stemp.Substring(0, itemp);
        stemp = stemp.Remove(0, itemp + 9);
        int itemcount = stemp.Count(x => x == ',') + 1;
        string[] values = new string[itemcount - 1];
        //populate values
        int h = 0;//Used as a start index
        int itemc = 0; //item index
                for (int k = 0; k < stemp.Length; k++)
                {
                    if (k == stemp.Length - 2)
                    {
                        values[itemc] = ExecuteSQL(stemp.Substring(h, (k + 1) - h));
                        break;
                    }
                    else

                    if (stemp.Substring(k, 2) == (", "))
                    {
                        itemp = stemp.IndexOf(", ");
                        values[itemc] = ExecuteSQL(stemp.Substring(h, k - h));
                        h = k + 2;
                        itemc = itemc + 1;
                        k = k + 1;
                    }
                    else if (stemp.Substring(k, 2) == ("),"))
                    {
                        itemp = stemp.IndexOf("),");
                        values[itemc] = ExecuteSQL(stemp.Substring(h, k - h));
                        h = k + 2;
                        itemc = itemc + 1;
                        k = k + 1;
                    }
                    else if (stemp.Substring(k, 2) == ("',"))
                    {
                        itemp = stemp.IndexOf("',");
                        values[itemc] = ExecuteSQL(stemp.Substring(h, (k - h) + 1));
                        h = k + 2;
                        itemc = itemc + 1;
                        k = k + 1;
                    }

        }
        for (int j = 0; j < values.Length; j++)
        {
            this.dgvInsert.Rows.Insert(j, j + 1, values[j], values[j].Length);
        }

    }

However this breaks when a function is in the insert statement ( cast for example)

Is there a way to do it or is this a lost cause?

To Clarify: I'm essentially trying to provide the user with a text box that they can paste an SQL statement into, this statement will then be reflected in a datagrid. So if an insert statement is put in, the datatable will reflect the values that are being inserted. But without ever inserting them into the database.

Also i don't see you open and close the connection to the server. And you could use a sqlcommand.parameters — EnvelopedDevil
– EnvelopedDevil, Commented Jan 9, 2015 at 9:26
I'm still building the procedure to import into the datatable, I wanted to see if it was possible without actually making a call to the database. — Darkestlyrics
– Darkestlyrics, Commented Jan 9, 2015 at 9:28
I think the best method to use would be to create a temp table within SQL server, thenexecute the insert to there and then fill the datatable from that temp table. However that seems like a rather lengthy process. — Darkestlyrics
– Darkestlyrics, Commented Jan 12, 2015 at 9:10

Community · Accepted Answer · 2017-05-23 11:50:56Z

1

Queries are database-specific, so parsing queries is done by a database-specific component, probably on the database server and otherwise the ADO.NET driver for that database.

You could use SQLite to create an in-memory database, so your end users won't have to set up a separate database (it does require SQLite dll's shipped with your application), however using that approach forces you to use the SQLite dialect, and some queries might not work if you attempt to use them on a different database. It does sound to me like the easiest way out though.

This answer suggests using the entity framework to parse queries, but I don't know if it can help you update the datatable. I haven't tried this myself but it might be worth looking into.

edited May 23, 2017 at 11:50

CommunityBot

11 silver badge

answered Jan 12, 2015 at 14:44

C.Evenhuis

26.4k2 gold badges60 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Darkestlyrics Over a year ago

Thanks for the answer, I went about looking into converting the given query into linq by means of Linqer However this would end up costing me. the other way that was mentioned was to actually build a parser myself.

Collectives™ on Stack Overflow

Insert statement into a Datatable

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related