0

I want to know if there is any way to create a SQL query based on a C# datatable?

Using a dll I'm able to read data in an excel file, I have all sheets in a DataSet containing DataTables, every sheet is in a separate DatatTable.

I want to generate a query involving CREATE clause to create a table and INSERT clause to insert data into that table for every single sheet.

I don't know if it's possible by something like Entity Framework or not.

The way I can imagine, for Create is to loop through DataSet and extract name of columns in every DataTable then add it in an string as query text. Here's what I mean:

  1. Extract table structure, suppose it's like following:

'ID' INT, 'Name' VARCHAR(5), 'SurName' VARCHAR(6), 'Age' INT

  1. Create a DataRowModel:

    public class DataRowModel { public string ColumnName { get; set; } public string ColumnType { get; set; } }

  2. Populate list of DataRowModel by extracted data:

List<DataRowModel> TableModel = new List<DataRowModel>();

  1. USing a foreach I can reach to following query:

string query = @"CREATE TABLE IF NOT EXISTS Test ( 'ID' INT, 'Name' VARCHAR(5), 'SurName' VARCHAR(6), 'Age' INT );";

But it seems dirty, what is the recommended solution? In fact I want to create a tool, something like this cool website.

Muhammad Musavi
  • 2,512
  • 2
  • 22
  • 35
  • This sounds a lot like an [xy problem](http://xyproblem.info/) to me. Why do you need to generate a create table script using a DataTable? That just seems really strange. Where did the data come from and why can't you use that table to generate your script? – Sean Lange Jun 12 '19 at 15:39
  • In fact I want to create a tool, something like [this cool website](https://sqlizer.io/#/). – Muhammad Musavi Jun 12 '19 at 15:40
  • So are you planning on receiving an excel file and then moving the data to a DataSet with a table for each sheet? Seems to me that the DataSet is an extra step. You should just evaluate the data in the spreadsheet without the overhead of a DataSet. – Sean Lange Jun 12 '19 at 15:44
  • No just like the website I offered, I want to make a tool in which user can upload a excel file, then s/he gets a query in return, to make a similar table in the database. The dll I mentioned creates a dataSet involving Datatables representing for every sheet. – Muhammad Musavi Jun 12 '19 at 15:48

1 Answers1

0

I'll give some code for example. Hope it helps

So the first step is getting columns list from the Excel.

    string filename = UploadFile(fuCompanies);
    OleDbConnection Econ = new OleDbConnection(string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", Server.MapPath("~/files/excels/" + filename)));
    DataSet ds = new DataSet();
    try
    {
        Econ.Open();

        DataTable activityDataTable = Econ.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        OleDbCommand Ecom = new OleDbCommand();
        Ecom.Connection = Econ;
        Ecom.CommandText = string.Format("Select * FROM [{0}]", activityDataTable.Rows[0]["TABLE_NAME"]);

        OleDbDataAdapter oda = new OleDbDataAdapter(Ecom.CommandText, Econ);

        oda.Fill(ds);
    }
    catch (Exception ex)
    {
        //Exception
    }
    finally
    {
        Econ.Close();
    }

    foreach(DataColumn dc in ds.Tables[0].Columns){
        // take columns here and create a dynamic class
    }

For example on how to create a dynamic model (class), I'll suggest following link: How to dynamically create a class

Parts 3 and 4 can be realized using 2 steps shown above

Khazratbek
  • 1,656
  • 2
  • 10
  • 17