Hi All,
I am a newbie in Design Patterns.
Let’s assume that we have an N number of flat files inside the directory. Let the flat files having comma separated information and consider the first line will be column information that is improperly ordered and next lines will be row information. What we want to do means we have to process the flat files and put that file info into database table.
For Example:
Database Table: Student
Columns:
ID-int
Name- varchar
Address – varchar
Flat file1 : flatfile1.txt
ID , Name,,Address
1,Vijai,Salem
2,Anand,,Attur
Flat file2:flatfile2.csv
ID,Name,Age
1,Vijai,,20
2,anand,20
Flatfile3:flatfile3.csv
1,Vijai,20
2,anand
Can anyone help me to design for this problem using any design pattern c# .Ultimately what I have to do is to validate the file information and put that in corresponding database as I mentioned above.
Thanks in Advance.
What I have tried:
public static class Common
{
public const char CONTENT_SPLIT = ',';
}
interface IFile
{
IEnumerable<string> ReadFile(string filePath);
DataTable FillFileContentintoDataTable(IEnumerable<string> fileContent);
void WriteFile(DataTable dataTable,string tableName);
}
public class TextFile : IFile
{
public IEnumerable<string> ReadFile(string filePath)
{
IEnumerable<string> fileContents=null;
if (File.Exists(filePath))
{
fileContents = File.ReadLines(filePath);
}
return fileContents;
}
public DataTable FillFileContentintoDataTable(IEnumerable<string> fileContent)
{
int rowCount = 0;
int colCount=0;
StudentDataset objStudentDs = new StudentDataset();
DataTable dt = objStudentDs.Tables[0];
if (fileContent != null)
{
foreach (string content in fileContent)
{
if (rowCount != 0)
{
colCount = 0;
string[] rowContent=content.Split(Common.CONTENT_SPLIT);
int noOfSplitDelimeters = rowContent.Length;
DataRow dr = dt.NewRow();
foreach (DataColumn column in dt.Columns)
{
if (colCount != noOfSplitDelimeters)
{
dr[column.ColumnName] = Convert.ToString(rowContent[colCount]);
}
else
{
break;
}
colCount += 1;
}
dt.Rows.Add(dr);
}
rowCount += 1;
}
}
return dt;
}
public void WriteFile(DataTable dataTable,string tableName)
{
DatabaseHandler objDBHandler = new DatabaseHandler(dataTable);
objDBHandler.WritetoDB(tableName);
}
}
class FileFactory
{
public static IFile CreateFileObject(string fileExtension)
{
IFile fileObject=null;
switch (fileExtension)
{
case ".txt":
fileObject= new TextFile();
break;
default:
fileObject = null;
break;
}
return fileObject;
}
}
public class CheckValidFileContent
{
private IEnumerable<string> FileContent { get; set; }
public CheckValidFileContent(IEnumerable<string> fileContent)
{
FileContent = fileContent;
}
public bool IsValidFileContent()
{
bool isValid = false;
int rowCount = 0,colCount=0;
StudentDataset objStudentDataSet = new StudentDataset();
DataTable objStudentDataTable = objStudentDataSet.Tables[0];
if (FileContent != null)
{
foreach (string content in FileContent)
{
if (rowCount == 0)
{
string[] columnContent=content.Split(Common.CONTENT_SPLIT);
foreach (DataColumn column in objStudentDataTable.Columns)
{
string contentName = Convert.ToString(columnContent[colCount]);
if (contentName != "")
{
if (Convert.ToString(columnContent[colCount]).ToUpper() == column.ColumnName.ToUpper())
{
isValid = true;
}
else
{
isValid = false;
break;
}
}
else
{
isValid = false;
break;
}
colCount += 1;
}
}
else
{
break;
}
rowCount += 1;
}
}
else
{
isValid = false;
}
return isValid;
}
}
public class DatabaseHandler
{
private string cnStr = ConfigurationManager.ConnectionStrings["cnStr"].ConnectionString.ToString();
private DataTable DBdataTable { get; set; }
public DatabaseHandler( DataTable dataTable)
{
DBdataTable = dataTable;
}
public void WritetoDB(string tableName)
{
int colCount=0;
if (DBdataTable != null)
{
using (SqlConnection cn = new SqlConnection(cnStr))
{
cn.Open();
foreach (DataRow dr in DBdataTable.Rows)
{
colCount = 0;
string query = "insert into " + tableName + " ( ";
string colValues = null;
foreach (DataColumn dc in DBdataTable.Columns)
{
if (colCount == 0)
{
query = query + dc.ColumnName;
}
else
{
query = query + "," + dc.ColumnName;
}
if (colValues == null)
{
if (dc.DataType == typeof(Int32))
{
colValues = Convert.ToString(dr[dc.ColumnName]);
}
else
{
colValues = "'" + Convert.ToString(dr[dc.ColumnName]) + "'";
}
}
else
{
if (dc.DataType == typeof(Int32))
{
colValues = colValues + "," + Convert.ToString(dr[dc.ColumnName]);
}
else
{
colValues = colValues + "," + "'" + Convert.ToString(dr[dc.ColumnName]) + "'";
}
}
if ((DBdataTable.Columns.Count - 1) == colCount)
{
query = query + " ) values ( " + colValues + ")";
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = query;
cmd.ExecuteNonQuery();
}
colCount += 1;
}
}
cn.Close();
}
}
}
}
class Program
{
static void Main(string[] args)
{
string flatFilesDirectoryPath = @"E:\FlatFiles";
string flatFileExtension = null;
foreach(var filePath in Directory.GetFiles(flatFilesDirectoryPath))
{
flatFileExtension = Path.GetExtension(filePath);
IFile objFile = FileFactory.CreateFileObject(flatFileExtension);
IEnumerable<string> fileContents = objFile.ReadFile(filePath);
CheckValidFileContent objCheckValidFile = new CheckValidFileContent(fileContents);
bool isValid = objCheckValidFile.IsValidFileContent();
if (isValid == true)
{
DataTable dbTable = objFile.FillFileContentintoDataTable(fileContents);
objFile.WriteFile(dbTable, "student");
}
}
}
}
anyone tell some good approach to solve this problem..
Submit an article or tip