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, Jan 09 '15 at 09: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, Jan 09 '15 at 09: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, Jan 12 '15 at 09:10
Update: after some reading it seems like an ExecuteScalar() function should do it, however this will then insert to the database which I am trying to avoid. — Darkestlyrics, Jan 12 '15 at 13:20

score 1 · Accepted Answer · edited May 23 '17 at 11:50

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.

Thanks for the answer, I went about looking into converting the given query into linq by means of [Linqer](http://www.sqltolinq.com/) However this would end up costing me. the other way that was mentioned was to actually build a parser myself. — Darkestlyrics, Jan 13 '15 at 05:50

Insert statement into a Datatable

1 Answers1