how to pass & keyword, special char in my mvc controller method

encodeURIComponent(title)

prevent-caching-in-asp-net-mvc

http://stackoverflow.com/questions/10011780/prevent-caching-in-asp-net-mvc-for-specific-actions-using-an-attribute/10011896#10011896



create  a class file : example file name  NoCacheAttribute


 [AttributeUsage(AttributeTargets.Class | AttributeTargets.Method)]
    public sealed class NoCacheAttribute : ActionFilterAttribute
    {
       public override void OnResultExecuting(ResultExecutingContext filterContext)
        {
            filterContext.HttpContext.Response.Cache.SetExpires(DateTime.UtcNow.AddDays(-1));
            filterContext.HttpContext.Response.Cache.SetValidUntilExpires(false);
            filterContext.HttpContext.Response.Cache.SetRevalidation(HttpCacheRevalidation.AllCaches);
            filterContext.HttpContext.Response.Cache.SetCacheability(HttpCacheability.NoCache);
            filterContext.HttpContext.Response.Cache.SetNoStore();
            base.OnResultExecuting(filterContext);
        }
    }


then apply in your mvc controller


 [NoCache]
public class AbcController : Controller
{
    //  write your on code
}
 

allow special character in MVC

write in your web config  

<system.web>      <httpRuntime targetFramework="4.5"
        executionTimeout="240"
         maxRequestLength="20480"
        minFreeThreads="88"
     minLocalRequestFreeThreads="76"
    relaxedUrlToFileSystemMapping="true"/>

</system.web>

Getting SqlException "Invalid column name 'User_Id' from EF4 code-only

case 1- you need to check your current domain model and mapping.
   (i) May be you added two times same property/ column name,
   (ii) you may add any FK which name is changed in your child  table, then you need to add fluent API

  example :-

 suppose your domain class

Parent class

public class User : Entity<int>, IAggregateRoot
    {
        public User()
        {
            Events = new Collection<IDomainEvent>();
        }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public virtual ICollection<ErrorLog> ErrorLogs { get; set; }     
    }

Child class

public class ErrorLog : Entity<int>, IAggregateRoot
    {
        public DateTime ErrorDate { get; set; }
    
        public string ErrorDescription { get; set; }     
        public int? ByUserId{ get; set; }   
        public virtual User User { get; set; }
    }

       Property(t => t.UserId).HasColumnName("UserId");

  HasOptional(t => t.User)
                .WithMany(t => t.ErrorLogs)
                .HasForeignKey(d => d.ByUserId);
----------------------------------------------------------------------------------

case 2 :- You may not have any FK relation with your current domain model, but you mention the collection object in your parent  class, then you need to remove that.






 

Method may only be called on a Type for which Type.IsGenericParameter is true.

1- you need to register your event in domain registry
2- you need to add your event in your domain model constructor

sql alter

--   DROP CONSTRAINT --
IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[FK_EmailServicesSendActivityLUT_EmailServicesContactLUT]')
 AND parent_object_id = OBJECT_ID(N'[dbo].[EmailServicesCampaignContact]'))
   BEGIN
    ALTER TABLE [dbo].[EmailServicesCampaignContact]
     DROP CONSTRAINT [FK_EmailServicesSendActivityLUT_EmailServicesContactLUT]
   END



--   DROP COLUMN --
IF EXISTS (
  SELECT *
  FROM   sys.columns   WHERE  object_id = OBJECT_ID(N'EmailServicesContactLUT') AND name = 'EmailServicesContactLUTId'
)
Begin
ALTER TABLE dbo.EmailServicesCampaignContact DROP COLUMN EmailServicesContactLUTId
End


--   DROP TABLE --
IF (EXISTS (SELECT *
                 FROM INFORMATION_SCHEMA.TABLES
                 WHERE TABLE_SCHEMA = 'dbo'
                 AND  TABLE_NAME = 'EmailServicesContactLUT'))
Begin
 DROP TABLE EmailServicesContactLUT
End



--  Add column
IF NOT EXISTS (
  SELECT *
  FROM   sys.columns   WHERE  object_id = OBJECT_ID(N'EmailServicesCampaignContact') AND name = 'ConstituentLUTId'
)
Begin
ALTER TABLE EmailServicesCampaignContact  Add  ConstituentLUTId int NOT NULL
End


--  ADD  CONSTRAINT
IF EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[FK_EmailServicesCampaignContact_ConstituentLUT]')
 AND parent_object_id = OBJECT_ID(N'[dbo].[EmailServicesCampaignContact]'))
   BEGIN
ALTER TABLE [dbo].[EmailServicesCampaignContact]  WITH CHECK ADD  CONSTRAINT [FK_EmailServicesCampaignContact_ConstituentLUT]
FOREIGN KEY([ConstituentLUTId])
REFERENCES [dbo].[ConstituentLUT] ([ConstituentLUTId])
ALTER TABLE [dbo].[EmailServicesCampaignContact] CHECK CONSTRAINT [FK_EmailServicesCampaignContact_ConstituentLUT]
End



-- DROP COLUMN
IF EXISTS (
  SELECT *
  FROM   sys.columns   WHERE  object_id = OBJECT_ID(N'EmailServicesCampaignContact') AND name = 'EmailAddress'
)
Begin
ALTER TABLE dbo.EmailServicesCampaignContact DROP COLUMN EmailAddress
End



-- DROP COLUMN
IF EXISTS (
  SELECT *
  FROM   sys.columns   WHERE  object_id = OBJECT_ID(N'EmailServicesCampaignContact') AND name = 'ConstituentLUT'
)
Begin
ALTER TABLE dbo.EmailServicesCampaignContact DROP COLUMN ConstituentLUT
End

drop CONSTRAINT and add CONSTRAINT

/*  DROP CONSTRAINT   */
ALTER TABLE [dbo].[EmailServicesCampaignContact] DROP CONSTRAINT [FK_EmailServicesSendActivityLUT_EmailServicesContactLUT]




/*  ADD  CONSTRAINT  */
ALTER TABLE [dbo].[EmailServicesCampaignContact]  WITH CHECK ADD  CONSTRAINT [FK_EmailServicesCampaignContact_ConstituentLUT]
FOREIGN KEY([ConstituentLUTId])
REFERENCES [dbo].[ConstituentLUT] ([ConstituentLUTId])
ALTER TABLE [dbo].[EmailServicesCampaignContact] CHECK CONSTRAINT [FK_EmailServicesCampaignContact_ConstituentLUT]

drop column if exist

IF EXISTS (
  SELECT *
  FROM   sys.columns   WHERE  object_id = OBJECT_ID(N'emailTable') AND name = 'EmailAddress'
)
Begin
ALTER TABLE dbo.emailTable DROP COLUMN EmailAddress
End

drop table if exist

IF (EXISTS (SELECT *
                 FROM INFORMATION_SCHEMA.TABLES
                 WHERE TABLE_SCHEMA = 'dbo'
                 AND  TABLE_NAME = empTable))
Begin
 DROP TABLE empTable
End

install the services to a machine

/// See http://msdn.microsoft.com/en-us/library/zt39148a.aspx for
        /// example walkthrough.  To manually install the services to a machine,
        /// see http://msdn.microsoft.com/en-us/library/sd8zc8ha.aspx.

open computer management shortcut

compmgmt.msc

how to run window service

C:\Windows\Microsoft.NET\Framework64\v4.0.30319\installutil.exe put you path

ex:


C:\Windows\Microsoft.NET\Framework64\v4.0.30319\installutil.exe E:\Test2\Test2_app\app.WindowsService.Jobtest\bin\Debug\crm.Service.JobOnline.exe

host file path

C:\Windows\System32\drivers\etc

download file in C#

[HttpGet]
        public ActionResult DownloadDocument(string fileName)
        {
            try
            {
                var filePath = GetImportsPath();
                var newFileFullPath = Path.Combine(filePath, fileName);
                var fileExtension = Path.GetExtension(fileName);
                var reConstructedFileName = "ConstituentId" + "_" + DateTime.UtcNow.ToShortDateString() + "_" + "OriginalFileName" + fileExtension;
                if (fileExtension == null)
                    throw new PersistenceValidationException("Export Error", new List<BrokenRule>
                {
                    new BrokenRule("InvalidDownload", "The file you are looking for download does not exist.")
                });
                if (fileExtension.ToLower() == ".zip" || fileExtension.ToLower() == ".rar")
                {
                    return File(newFileFullPath, "application/zip", reConstructedFileName);
                }
                return File(newFileFullPath, "application/octet-stream", reConstructedFileName);
            }
            catch (PersistenceValidationException ex)
            {
                Response.StatusCode = (int)HttpStatusCode.BadRequest;
                return Json(ex.BrokenRules, JsonRequestBehavior.AllowGet);
            }
        }

_________________________________________

 protected static string GetImportsPath()
        {
            var fileUploadLocation = ConfigurationManager.AppSettings["importsPath"];

            if (!Directory.Exists(fileUploadLocation))
                Directory.CreateDirectory(fileUploadLocation);

            return fileUploadLocation;
        }

___________________________
 write below code in your webconfig file under appsetting

<appSettings>
 
    <add key="importsPath" value="C:\LocalFiles\" />
   
  </appSettings

join between two table in linq C#

var paymentMethos = from p in DbContext.StagingPaymentMethods
                                join c in DbContext.StagingContributions on p.ContributionGuid equals c.StagingContributionGuid
                                where c.ImportMappingGuid == importMappingGuid
                                select p;
            var creditCards = from cr in DbContext.StagingCreditCards
                              join c in DbContext.StagingContributions on cr.ContributionGuid equals c.StagingContributionGuid
                              where c.ImportMappingGuid == importMappingGuid
                              select cr;

entity framework validation

if (tran != null)



{

tran.Commit();

tran.Dispose();

}

}



catch (DbEntityValidationException dbEx)



{

foreach (var validationErrors in dbEx.EntityValidationErrors)



{

foreach (var validationError in validationErrors.ValidationErrors)



{

var xyz = string.Format("Property: {0} Error: {1}", validationError.PropertyName,validationError.ErrorMessage);

abc = !string.IsNullOrEmpty(abc) ? xyz + " HHH " : xyz;

// Trace.TraceInformation("Property: {0} Error: {1}", validationError.PropertyName, validationError.ErrorMessage);



}

}

}

copy array

 // Array.Copy(a, 1, b, 0, 3);
                            // a = source array
                            //1 = start index in source array
                            //b = destination array
                            //0 = start index in destination array
                            //3 = elements to copy

check duplicate records in list c#

var duplicateCount = campaignDtoList.Select(x => x.CampaignId).GroupBy(n => n).Any(c => c.Count() > 1);



NOTE:   duplicateCount  is return bool value

get table count in sql server

SELECT COUNT(*) from information_schema.tables
WHERE table_type = 'base table'

add range in List C#

public List<EmpDto> GetEmps()
        {
            var empDtoList = new List<EmpDtoList>();
            using (_dbContextScopeFactory.Create(connectionString))
            {
                var empEntity = _empRepository.GetAll();
                empDtoList .AddRange(empEntity .Select(x => new EmpDto
                {
                    Id = x.Id,
                    Name = x.Name,
                    Description = x.Description,
                  
                }));
            }
            return empDtoList ;
        }

update order in sql

with EmpUpdate as (

select Employee.*,

row_number() over (partition by  empId

order by SRNumber

) as seqnum

from Employee

)

update EmpUpdate

set EmpOrder =seqnum

where seqnum > 0;

contain list in C#

//var valueIdM = existingValueList.Where(e=>splitedValue.Contains(e.CustomFieldValueName))

// .Select(y => y.CustomFieldValueId).ToArray();

//var abc = string.Join(",", valueIdM);

//answereValue = "[" + abc + "]";

get file name in C#

/// <summary>
    /// Get the extension from the given filename
    /// </summary>
    /// <param name="fileName">the given filename ie:abc.123.txt</param>
    /// <returns>the extension ie:txt</returns>
    public static string GetFileExtension(this string fileName)
    {
        string ext = string.Empty;
        int fileExtPos = fileName.LastIndexOf(".", StringComparison.Ordinal);
        if (fileExtPos >= 0)
            ext = fileName.Substring(fileExtPos, fileName.Length - fileExtPos);
        return ext;
    }

private int?  GetCountryIdByCountryName(int countryName)
{

int? countryId= Country!= null ? (int?)Country.Id: null;
return countryId
}

coply datatable to another table specific column in C#

 string[] selectedColumns = new[]
            {
               // "Title",
                "FirstName",
                //"MiddleName", "LastName", "Suffix", "Company", "InformalSalutation", "FormalSalutation",
                //"AddressName",
                "Address1","Address2"
                //,"City","State","Zip","Country"
                };
            DataTable dtTest = new DataView(dataTable1).ToTable(false, selectedColumns);

convert from csv to datatable

for  TextFieldParser

we need to add dll
Microsoft.VisualBasic

/// <summary>
        /// GetDataTabletFromCSVFile
        /// </summary>
        /// <param name="fileImportCriteria"></param>
        /// <returns></returns>
        private static DataTable GetDataTabletFromCsvFile(FileImportCriteriaModel fileImportCriteria)
        {
            DataTable csvData = new DataTable();
            try
            {
                using (TextFieldParser csvReader = new TextFieldParser(fileImportCriteria.FilePath))
                {
                    csvReader.SetDelimiters(new string[] { fileImportCriteria.ManualDelimiter });
                    csvReader.HasFieldsEnclosedInQuotes = true;
                    string[] colFields = csvReader.ReadFields();
                    foreach (string column in colFields)
                    {
                        DataColumn datecolumn = new DataColumn(column);
                        datecolumn.AllowDBNull = true;
                        csvData.Columns.Add(datecolumn);
                    }
                    while (!csvReader.EndOfData)
                    {
                        string[] fieldData = csvReader.ReadFields();
                        //Making empty value as null
                        for (int i = 0; i < fieldData.Length; i++)
                        {
                            if (fieldData[i] == "")
                            {
                                fieldData[i] = null;
                            }
                        }
                        csvData.Rows.Add(fieldData);
                    }
                }
            }
            catch (System.Exception ex)
            {
            }
            return csvData;
        }

ReadXmlFile

  public FileDataModel ReadXmlFile(string filePath)
        {
            try
            {
                if (!File.Exists(filePath))
                {
                    throw new PersistenceValidationException("Validation Error",
                        new List<BrokenRule>
                        {
                            new BrokenRule(BrokenRuleEnum.Data.ToStringValue(), "FileUpload", "File not found.")
                        });
                }

                XElement xele = XElement.Load(filePath); //get your file
                // declare a new DataTable and pass your XElement to it
                DataTable dataTable1 = XElementToDataTable(xele);
              
                // Convert From Table To Object
                var fileDataModel = ConvertFromTableToObject(dataTable1);
               return fileDataModel;
            }
            catch (XmlException)
            {
                throw new PersistenceValidationException("Validation Error", new List<BrokenRule>
                {
                    new BrokenRule("InvalidXml", "Invalid Xml file")
                });
            }
        }

------------------
 public DataTable XElementToDataTable(XElement x)
        {
            DataTable dtable = new DataTable();
            XElement setup = (from p in x.Descendants() select p).First();
            // build your DataTable
            foreach (XElement xe in setup.Descendants())
                dtable.Columns.Add(new DataColumn(xe.Name.ToString(), typeof(string))); // add columns to your dt
            var all = from p in x.Descendants(setup.Name.ToString()) select p;
            foreach (XElement xe in all)
            {
                DataRow dr = dtable.NewRow();
                foreach (XElement xe2 in xe.Descendants())
                    dr[xe2.Name.ToString()] = xe2.Value; //add in the values
                dtable.Rows.Add(dr);
            }
            return dtable;
        }

------------------

 private static FileDataModel ConvertFromTableToObject(DataTable dataTable1)
        {
            var fileDataModel = new FileDataModel();
            var listDataResult = new List<List<FieldImports>>();
            var columnList = new List<FieldImports>();
            for (var columnIndex = 0; columnIndex < dataTable1.Columns.Count; columnIndex++)
            {
                var result = new FieldImports();
                result.ColumnName = Convert.ToString(dataTable1.Columns[columnIndex]);
                columnList.Add(result);
            }
            for (var rowIndex = 0; rowIndex < dataTable1.Rows.Count; rowIndex++)
            {
                var fileData = new List<FieldImports>();
                for (var columnIndex = 0; columnIndex < dataTable1.Columns.Count; columnIndex++)
                {
                    var result = new FieldImports();
                    result.ColumnValue = Convert.ToString(dataTable1.Rows[rowIndex][columnIndex]);
                    fileData.Add(result);
                }
                listDataResult.Add(fileData);
            }
            fileDataModel.fileData = listDataResult;
            fileDataModel.columnList = columnList;
            return fileDataModel;
        }



 

ReadExcelFile

public FileDataModel ReadExcelFile(FileImportCriteriaModel fileImportCriteria)
        {
            var fileDataModel = new FileDataModel();
            var dataTable1 = new DataTable();
            using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(fileImportCriteria.FilePath, false))
            {
                var worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById("rId7");
                var workSheet = worksheetPart.Worksheet;
                var sheetData = workSheet.GetFirstChild<SheetData>();
                IEnumerable<Row> rows = sheetData.Descendants<Row>();
                foreach (Cell cell in rows.ElementAt(0))
                {
                    dataTable1.Columns.Add(GetCellValue(spreadSheetDocument, cell));
                }
                foreach (Row row in rows)
                {
                    DataRow dataRow = dataTable1.NewRow();
                    for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
                    {
                        dataRow[i] = GetCellValue(spreadSheetDocument, row.Descendants<Cell>().ElementAt(i));
                    }
                    dataTable1.Rows.Add(dataRow);
                }
                dataTable1.Rows.RemoveAt(0);
                // Convert From Table To Object
                fileDataModel = ConvertFromTableToObject(dataTable1);
            }
            return fileDataModel;
        }


 private static string GetCellValue(SpreadsheetDocument document, Cell cell)
        {
            var stringTablePart = document.WorkbookPart.SharedStringTablePart;
            string value = cell.CellValue.InnerXml;
            if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
            {
                return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
            }
            else
            {
                return value;
            }
        }

ReadTxtFile

 public FileDataModel ReadTxtFile(string filePath)
        {
            string[] textData = System.IO.File.ReadAllLines(filePath);
            string[] headers = textData[0].Split('\t');
            DataTable dataTable1 = new DataTable();
            foreach (string header in headers)
                dataTable1.Columns.Add(header, typeof(string), null);
            for (int i = 1; i < textData.Length; i++)
            {
                dataTable1.Rows.Add(textData[i].Split('\t'));
            }
            var fileDataModel = new FileDataModel();
           
            // Convert From Table To Object
            fileDataModel = ConvertFromTableToObject(dataTable1);
            return fileDataModel;
        }



private static FileDataModel ConvertFromTableToObject(DataTable dataTable1)
        {
            var fileDataModel = new FileDataModel();
            var listDataResult = new List<List<FieldImports>>();
            var columnList = new List<FieldImports>();
            for (var columnIndex = 0; columnIndex < dataTable1.Columns.Count; columnIndex++)
            {
                var result = new FieldImports();
                result.ColumnName = Convert.ToString(dataTable1.Columns[columnIndex]);
                columnList.Add(result);
            }
            for (var rowIndex = 0; rowIndex < dataTable1.Rows.Count; rowIndex++)
            {
                var fileData = new List<FieldImports>();
                for (var columnIndex = 0; columnIndex < dataTable1.Columns.Count; columnIndex++)
                {
                    var result = new FieldImports();
                    result.ColumnValue = Convert.ToString(dataTable1.Rows[rowIndex][columnIndex]);
                    fileData.Add(result);
                }
                listDataResult.Add(fileData);
            }
            fileDataModel.fileData = listDataResult;
            fileDataModel.columnList = columnList;
            return fileDataModel;
        }

generate alphachar in C#

    private class ProgramTest
    {
        private static void Main(string[] args)
        {
            for (int i = 1; i < 100; i++)
            {
                var abc = TestA.GetColumnNameFromIndex(i);
                Console.WriteLine(abc);
            }
      }
}

----------------------

public  class TestA
    {
        public static int Number;
        public static String GetColumnNameFromIndex(int column)
        {
            column--;
            String col = Convert.ToString((char)('A' + (column % 26)));
            while (column >= 26)
            {
                column = (column / 26) - 1;
                col = Convert.ToString((char)('A' + (column % 26))) + col;
            }
            return col;
        }
    }

read txt file data in C# in datatable

public Tuple<List<FieldImports>, bool> ReadTxtFile(string filePath)
        {
            string[] textData = System.IO.File.ReadAllLines(filePath);
            string[] headers = textData[0].Split('\t');
            DataTable dataTable1 = new DataTable();
            foreach (string header in headers)
                dataTable1.Columns.Add(header, typeof(string), null);
            for (int i = 1; i < textData.Length; i++)
            {
                dataTable1.Rows.Add(textData[i].Split('\t'));
            }
            List<FieldImports> listDataColumn1 = new List<FieldImports>();
            var columnNames = dataTable1.Columns.Cast<DataColumn>()
               .Select(c => c.ColumnName)
               .ToList();

            List<FileDataModel> listDataResult = new List<FileDataModel>();
            for (int columnIndex = 0; columnIndex < dataTable1.Columns.Count; columnIndex++)
            {
                List<FieldImports> listData = new List<FieldImports>();
                FieldImports result = new FieldImports();
                // result.ColumnName = dataTable1.Rows[i][i].ToString();
                result.ColumnName = Convert.ToString(dataTable1.Rows[0][columnIndex]);
                listData.Add(result);
            }
            //listDataResult.Add(listData);
            for (int rowIndex = 0; rowIndex < dataTable1.Rows.Count; rowIndex++)
            {
                List<FieldImports> listData = new List<FieldImports>();
                for (int columnIndex = 0; columnIndex < dataTable1.Columns.Count; columnIndex++)
                {
                    FieldImports result = new FieldImports();
                    // result.ColumnName = dataTable1.Rows[i][i].ToString();
                    result.ColumnValue = Convert.ToString(dataTable1.Rows[rowIndex][columnIndex]);
                    listData.Add(result);  
                }
                listDataResult.Add(listData);
            }
        
         // return new Tuple<List<FieldImports>, bool>(columnListData, false);
            return null;
        }
    }

read txt file in C#

public Tuple<List<FieldImports>, bool> ReadTxtFile(string filePath)


{


 string[] textData = System.IO.File.ReadAllLines(filePath);

string[] headers = textData[0].Split('\t');

DataTable dataTable1 = new DataTable();

foreach (string header in headers)

 dataTable1.Columns.Add(header, typeof(string), null);

for (int i = 1; i < textData.Length; i++)


{


 dataTable1.Rows.Add(textData[i].Split('\t'));


}


 // var columnListData = new List<FieldImports>();

// var fileInLines = File.ReadAllLines(filePath);

//// foreach (var line in fileInLines)

// for (var line = 0; line < fileInLines.Length; line++)

// {

// var fileAttributes = fileInLines[0].Split('\t');

// var fieldImportsData = new FieldImports();

// var fieldAttributes = fileInLines[line + 1].Split('\t');

// fieldImportsData.ColumnName = fileAttributes[0];

// //for (var i = 0; i < fileAttributes[i+1].Length; i++)

// //{

// fieldImportsData.ColumnValue = fieldAttributes[line];

// // }

// columnListData.Add(fieldImportsData);

// }

// return new Tuple<List<FieldImports>, bool>(columnListData, false);

return null;


}

Parse XML file in C#

/// <summary>
        /// Parse XML file
        /// </summary>
        /// <param name="filePath"></param>
        public Tuple<List<FieldImports>, bool> ParseXmlFile(string filePath)
        {
            var columnList = new List<FieldImports>();
            try
            {
                if (!File.Exists(filePath))
                {
                    throw new PersistenceValidationException("Validation Error",
                        new List<BrokenRule>
                            {
                                new BrokenRule(BrokenRuleEnum.Data.ToStringValue(), "FileUpload", "File not found.")
                            });
                }
                var xmlDoc = new XmlDocument();
                xmlDoc.Load(filePath);
                if (xmlDoc.DocumentElement != null)
                    foreach (XmlNode xmlNode in xmlDoc.DocumentElement)
                    {
                      
                        //column name
                        var columnsName = xmlNode.Name;
                        //column value
                        var value = xmlNode.InnerText;
                        var fieldImports = new FieldImports
                        {
                            ColumnName = columnsName,
                            ColumnValue = value
                        };
                        columnList.Add(fieldImports);
                    }
                return new Tuple<List<FieldImports>, bool>(columnList, false);
            }
            catch (XmlException)
            {
                throw new PersistenceValidationException("Validation Error", new List<BrokenRule>
                    {
                        new BrokenRule("InvalidXml", "Invalid Xml file")
                    });
            }
        }

read data from text,csv,xml files using C#

XML=>     
                           var  Info = XDocument.Load(completeFilePath);
   var descendants = employeeInfo.Descendants("Element");
   var list = new List<Employee>();
   foreach (var descendant in descendants)
   {
    var emp = new Employee();
    emp.EmployeeId= descendant.Element("Id").Value;
    emp.Name = descendant.Element("Name").Value;
     list.Add(emp);
                            }

Text=>
                                                      var fileInLines = File.ReadAllLines(completeFilePath);
    foreach (var line in fileInLines)
   {
    var fileAttributes = line.Split(' ');
    var emp = new Employee();

    emp.Id = fileAttributes[0];
    emp.Name = fileAttributes[1];
    list.Add(emp);
   }

CSV=>
                         StreamWriter cfile = new StreamWriter(filepath);
     using (var wc = cfile)
   {
    var sb = new StringBuilder();

    foreach (var emp in employees)
    {
     sb.Append(emp.Id + ",");
     sb.Append(emp.Name + ",");
    }
    wc.WriteLine(sb.ToString());
    }
 

and new column and foregin key in sql

ALTER TABLE Employee ADD JobTitleId INT NULL

ALTER TABLE [dbo].Employee
ADD CONSTRAINT [FK_Employee _JobTitle]
FOREIGN KEY (JobTitleId)
 REFERENCES [dbo].JobTitle (JobTitleId)