public static void CreateSQLTable(string pathtocsv, Int32 rowstoestimatedatatype, string tablename, string connstring)
{
char separator;
using (StreamReader sr = new StreamReader(pathtocsv))
{
separator = '\t';
}
string[,] sqldts = DataTypeIdentifier.SQLDataTypes(pathtocsv, rowstoestimatedatatype, separator);
string createTable_string = string.Empty;
for (int i = 0; i < sqldts.GetLength(1); i++)
{
if (i == sqldts.GetLength(1) - 1)
{
createTable_string = createTable_string + "[" + sqldts[1, i] + "]" + " " + sqldts[0, i];
}
else
{
createTable_string = createTable_string + "[" + sqldts[1, i] + "]" + " " + sqldts[0, i] + ", ";
}
}
try
{
using (SqlConnection con = new SqlConnection(connstring))
{
con.Open();
string createTable = @"CREATE TABLE " + tablename + " (" + createTable_string + ");";
using (SqlCommand command = new SqlCommand(createTable, con))
{
command.CommandTimeout = 0;
command.ExecuteNonQuery();
}
con.Close();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message.ToString());
Environment.Exit(0);
}
}
// Return sql data types:
public static string[,] SQLDataTypes(string pathtocsv, Int32 norowsreview, char sep)
{
// "c:\\PathToFile\\data.csv"
DataTable dt = CSVReader.csvToDataTable(pathtocsv, norowsreview, sep);
string[,] sqldatatype_array = new string[2, dt.Columns.Count];
try
{
for (int i = 0; i < dt.Columns.Count; i++)
{
//// Basic logic should goes here:
// Check bool type
if (DataTypeIdentifier.IsBoolType(dt, i))
{
sqldatatype_array[0, i] = "bit";
sqldatatype_array[1, i] = dt.Columns[i].ToString();
continue;
}
// Check int type
else if (DataTypeIdentifier.IsIntType(dt, i))
{
sqldatatype_array[0, i] = "int";
sqldatatype_array[1, i] = dt.Columns[i].ToString();
continue;
}
// Check double
else if (DataTypeIdentifier.IsDoubleType(dt, i))
{
sqldatatype_array[0, i] = "float";
sqldatatype_array[1, i] = dt.Columns[i].ToString();
continue;
}
// check date
else if (DataTypeIdentifier.IsDateType(dt, i))
{
sqldatatype_array[0, i] = "date";
sqldatatype_array[1, i] = dt.Columns[i].ToString();
continue;
}
// check datetime
else if (DataTypeIdentifier.IsDateTimeType(dt, i))
{
sqldatatype_array[0, i] = "datetime";
sqldatatype_array[1, i] = dt.Columns[i].ToString();
continue;
}
// else string
else
{
Int32 varcharLength = maxStringLength(dt, i);
sqldatatype_array[0, i] = "varchar(" + varcharLength + ")";
sqldatatype_array[1, i] = dt.Columns[i].ToString();
continue;
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message.ToString());
Environment.Exit(1);
}
return(sqldatatype_array);
}
