Creating SQL table using C#

Question

I would like to create two SQL tables using C#, within a loop. Each table is different, and has its column names stored in an array. Each array of column names is actually obtained from the header of a csv file.

 ### fnames is an array of file paths (2 csv files)
 foreach string f in fnames)
 {
      ## snip
      using (StreamReader rdr = new StreamReader(f))
      {
           string header = read.line();  ## This is the array of table columns
      }
      string tab = Path.GetFileNameWithoutExtension(f);
      string query = @"create table "+ tab + ..."; #I am not sure how to write the column names and types dynamically
 }

Imagine that:

The columns for table 1 are : Date (datetime), Value (int)
The columns for table 2 are : Date (datetime), ID (varchar(255)), Return (int)

Note that the two tables have different columns with different types. How can I achieve this?

It all depends on how you are determining the types of the columns, work out how you are going to get a list of column names and types from your headers string then come back and tackle the creating of the table. — Scott Chamberlain
– Scott Chamberlain, Commented Sep 27, 2013 at 14:02
That's exactly the problem. Let's simplify and let the first column of each table be datetime and all the other columns be varchar(255). Is there a way of doing this then? — Mayou
– Mayou, Commented Sep 27, 2013 at 14:03

Scott Chamberlain · Accepted Answer · 2013-09-27 14:53:53Z

You should break the problem apart, first you need to get a list of objects that define your column headers, after you have that you can loop over that list and build the query.

class HeaderInfo
{
    public HeaderInfo(string header)
    {
        throw new NotImplementedException("Parse out your header info here and populate the class")
    }

    public string Name {get; private set;}
    public string TypeInfo {get; private set;}
}

private List<HeaderInfo> ParseHeader(string header)
{
    var headerInfo = new List<HeaderInfo>();
    string[] headerItems = //Split your header line in to indvidual items some how
    foreach(headerItem in headerItems)
    {
         headerInfo.Add(new HeaderInfo(headerItem));
    }
    return headerInfo;
}

private string TableString(List<HeaderInfo> headerInfo)
{
     StringBuilder sb = new StringBuilder();

     foreach(var info in headerInfo)
     {
         sb.AppendFormat("{0} {1}, ", info.Name, info.TypeInfo);
     }

     sb.Remove(sb.Length -2, 2); //Remove the last ", "

     return sb.ToString();
}

private void YourMethod(string[] fnames)
{
    ### fnames is an array of file paths (2 csv files)
    foreach string f in fnames)
    {
         ## snip
         List<HeaderInfo> headerInfo;
         using (StreamReader rdr = new StreamReader(f))
         {
              string headerLine = read.line();  ## This is the array of table columns
              headerInfo = ParseHeader(headerLine);
         }
         string tab = Path.GetFileNameWithoutExtension(f);
         string query = String.Format(@"create table [{0}] ({1})", tab, TableString(headerInfo));
    }
}

Collectives™ on Stack Overflow

Creating SQL table using C#

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related