I want to perform insert into database but in different Tables. Each record i have in my file is assigned with the table Identifiers which will be used as a key. For now what i have done is to make it a generic approach, I have created one more file.txt in which i have specified all the identifiers with the table names. and stored it into a dictionary of string, string.
So, I have example some thing like below in my dictionary:
Table Identifier, Table Name
Table Identifier, Table Name
Then I created another dictionary of string as key (Note: I have used the previous dictionary value as a key into this dictionary) and list of string as values to get the column names of the table name.
So, No I have some thing like this below sample data into my dictionary:
Table Name, Column Names IEnumerable
Table Name, Column Names IEnumerable
Then, The datafile.txt which contains the data as pipe delimited, I split them and saved into a List of KVP of string and List of String. As I mentioned before, I have table identifiers in my data files. So i used them as key in my KVP and store the split values into List of string.
So, No I have some thing like this below sample data into my List of KVP of string , List of string:
Table Identifier, IEnumerable Values
Table Identifier, IEnumerable Values
Now doing half of the work, I am stuck into my FINAL ISSUE:
Now I have All the identifers , Table Names, Column Names and Values with me into the dictionary and List. And only thing to do is to match and merge the records and DUMP it!
For matching: I have thought to match the List of KVPs key with the dictionarys key and then use the values as a key to get the Column Names.
Expected Scenario Image:
List [[List< Column Names>] , [List of String Values]]
MY CODE:
DataTable dt = null;
SqlConnection cn = null;
SqlDataReader dataReader = null;
SqlBulkCopy bulkInsert = null;
StreamReader reader = null;
string path = string.Empty;
public void test()
{
string TableIdentiferFilepath = HttpContext.Current.Server.MapPath("/testfile/TableIdentifer.txt");
Dictionary<string, string> TableIdentifer_TableName = null;
Dictionary<string, List<string>> Table_Name_ColumnName = null;
using (reader = new StreamReader(TableIdentiferFilepath))
{
TableIdentifer_TableName = new Dictionary<string, string>();
Table_Name_ColumnName = new Dictionary<string, List<string>>();
while (!reader.EndOfStream)
{
string[] curr = reader.ReadLine().Split(new string[] { ",", "\r\n" }, StringSplitOptions.None);
TableIdentifer_TableName.Add(curr[0], curr[1]);
using (cn = new SqlConnection(ConString.Connection.conn))
{
cn.Open();
if (cn.State == ConnectionState.Open)
{
string query = string.Format("select column_name from information_schema.columns where table_name = '{0}' order by ordinal_position", curr[1].ToString());
using (SqlCommand cmd = new SqlCommand(query))
{
using (SqlDataAdapter da = new SqlDataAdapter(query, cn))
{
using (dt = new DataTable())
{
da.Fill(dt);
List<string> dataColumns = dt.AsEnumerable().Select(r => r.Field<string>("column_name")).ToList();
Table_Name_ColumnName.Add(curr[1], dataColumns);
}
}
}
}
}
}
}
string path = HttpContext.Current.Server.MapPath("/TextFile/DataSample.txt");
List<KeyValuePair<string, List<string>>> KVPValues = new List<KeyValuePair<string, List<string>>>();
using (reader = new StreamReader(path))
{
while (!reader.EndOfStream)
{
string[] arr = reader.ReadLine().Split(new string[] { "|", "\r\n" }, StringSplitOptions.None);
var collValues = new List<string>();
KVPValues.Add(new KeyValuePair<string, List<string>>(arr[0], arr.Skip(1).AsEnumerable().ToList()));
foreach (var item in TableIdentifer_TableName)
{
foreach (var item2 in Table_Name_ColumnName.Where(c => c.Key == item.Value))
{
var curr_val = item2.Value;
var currKey = KVPValues.Where(p => p.Key == item.Key).ToList();
}
}
}
}
}
This is a BIG PICTURE! Hopefully now people will understand what i want to achieve.
