public static MapperReturn CsvPost(SqlConnection cn, DataBaseTableDetails dboDetails)
{
List <TableDetails> tableDetails = new List <TableDetails>();
List <string> fieldMap = new List <string>();
MapperReturn csv = new MapperReturn();
using (SqlCommand cmd = new SqlCommand("SELECT column_id, name FROM sys.columns WHERE object_id = OBJECT_ID('" + dboDetails.TableNameDbo + "')", cn))
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
tableDetails.Add(new TableDetails()
{
FieldIndex = Convert.ToInt32(reader["column_id"]),
FieldName = Convert.ToString(reader["name"])
});
}
foreach (TableDetails table in tableDetails)
{
fieldMap.Add(dboDetails.TableNameMap + ".Add(new CsvFieldMap { FieldIndex = " + (table.FieldIndex - 1) + ", FieldName = '" + table.FieldName + "'});");
}
csv = new MapperReturn()
{
ListCreator = "List<CsvFieldMap> " + dboDetails.TableNameMap + " = new List<CsvFieldMap>();",
FieldMap = fieldMap,
ListAdd = "list.Add(new CsvDataSource('" + dboDetails.TableName + "', " + dboDetails.TableNameMap + "));"
};
}
return(csv);
}
public static MapperReturn LumensPost(SqlConnection cn, DataBaseTableDetails dboDetails)
{
List <TableDetails> tableDetails = new List <TableDetails>();
List <string> fieldMap = new List <string>();
MapperReturn csv = new MapperReturn();
using (SqlCommand cmd = new SqlCommand("SELECT DATA_TYPE, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + dboDetails.TableNameDbo + "'", cn))
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
tableDetails.Add(new TableDetails()
{
Type = ReturnType(Convert.ToString(reader["DATA_TYPE"])),
FieldName = Convert.ToString(reader["COLUMN_NAME"])
});
}
foreach (TableDetails table in tableDetails)
{
fieldMap.Add(dboDetails.TableNameCol + ".Add(new Column { Name = '" + table.FieldName + "', Type = typeof(" + table.Type + ") });");
}
csv = new MapperReturn()
{
ListCreator = "List<Column> " + dboDetails.TableNameCol + " = new List<Column>();",
FieldMap = fieldMap,
ListAdd = "list.Add(new LumenColumns('" + dboDetails.TableName + "', " + dboDetails.TableNameCol + "));"
};
}
return(csv);
}
public MapperReturn Post(string path, string tableName)
{
DataBaseTableDetails tableDetails = new DataBaseTableDetails()
{
TableName = tableName,
TableNameDbo = "Load_" + tableName,
TableNameCol = tableName + "Col",
TableNameMap = tableName + "Map",
};
SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder()
{
// add your connection strings here
};
using (SqlConnection cn = new SqlConnection(scsb.ConnectionString)){
cn.Open();
switch (path)
{
case "CSV":
return(CsvMappingService.CsvPost(cn, tableDetails));
case "LUMEN":
return(LumenMappingService.LumensPost(cn, tableDetails));
}
return(null);
}
}
