We need 3 separate elements in order to import data to SQL table. I use SQL Server as the database engine.
Download Source Files
1. Schema.ini
CSV Delimited Format - For CSV files
TAB Delimited Format - For text files
2. CSV or Text File
CSV File - Separated by comma
Text File - Separated by TAB
3. SQL Table
To simply the process I have developed a DelimitedDataImporter class which can be found at the end of this post. You can use an instance of it for this purpose. Below are the five steps that you may need to follow in-order to import data to SQL table.
- Upload a file
- Instantiate DelimitedDataImporter and set the connection string
- Find existing records and set filter statement [OPTIONAL]
- Set column mappings
- Import data to SQL table
<%@ Page Language="C#" %> <html xmlns="http://www.w3.org/1999/xhtml"> <head id="Head1" runat="server"> <script runat="server"> protected void Import(object sender, EventArgs e) { String path = Server.MapPath("~/Data/"); try { if (this.fuFile.HasFile) { /// /// Upload file /// string fileName = this.fuFile.FileName; string fullName = Path.Combine(path, fileName); if (File.Exists(fullName)) File.Delete(fullName); this.fuFile.PostedFile.SaveAs(fullName); /// /// Instantiate DelimitedDataImporter and set connection string /// DelimiterType type = DelimiterType.CsvDelimited; if (this.ddlFileType.SelectedValue.Equals("TAB")) type = DelimiterType.TabDelimited; DelimitedFileImporter importer = new DelimitedFileImporter(type); importer.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; /// /// Find existing records and set filter statement [OPTIONAL] /// StringBuilder sb = new StringBuilder(string.Empty); using (SqlConnection con = new SqlConnection( ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString)) { SqlCommand cmd = new SqlCommand("SELECT Id FROM Student", con); con.Open(); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) sb.AppendFormat("{0},", reader.GetInt32(0).ToString()); reader.Close(); con.Close(); } string ids = string.Empty; if (!string.IsNullOrEmpty(sb.ToString())) ids = sb.ToString().Remove(sb.Length - 1, 1); if (!string.IsNullOrEmpty(ids)) importer.Filter = string.Format("Id NOT IN ({0})", ids); /// /// Set column mappings /// importer.ColumnMappings.Add(new ColumnMapping("Id", "Id")); importer.ColumnMappings.Add(new ColumnMapping("Name", "Name")); importer.ColumnMappings.Add(new ColumnMapping("Grade", "Grade")); /// /// Import data to sql table /// importer.ImportToDatabase(fullName, "Student"); } } catch { throw new Exception("Import Faild"); } } </script> </head> <body> <form id="form1" runat="server"> <asp:FileUpload runat="server" ID="fuFile" /> <asp:Button runat="server" ID="btnImport" OnClick="Import" Text="Import" /> <asp:DropDownList runat="server" ID="ddlFileType"> <asp:ListItem Value="CSV" Selected="True">CSV Delimited</asp:ListItem> <asp:ListItem Value="TAB">Tab Delimited</asp:ListItem> </asp:DropDownList> </form> </body> </html>
DelimitedDataImporter implementation:
#region EventArgs Specification public enum DelimiterType { TabDelimited, CsvDelimited, CustomDelimited } public class ImportDelimitedEventArgs : EventArgs { private ReadOnlyCollection<object> content; private int lineNumber; public ImportDelimitedEventArgs(OleDbDataReader reader, int number) { object[] columns = new object[reader.FieldCount]; reader.GetValues(columns); content = new ReadOnlyCollection<object>(columns); lineNumber = number; } public bool BreakImport { get; set; } public int LineNumber { get { return lineNumber; } } public ReadOnlyCollection<object> Content { get { return content; } } } #endregion #region Mappings public class ColumnMapping { public string SourceColumn { get; set; } public string DestinationColumn { get; set; } public ColumnMapping(string sourceColumn, string destinationColumn) { this.SourceColumn = sourceColumn; this.DestinationColumn = destinationColumn; } } #endregion #region Importer public class DelimitedFileImporter { #region Instance values private string customDelimiter; private string filter; private DelimiterType delimiter = DelimiterType.TabDelimited; #endregion #region Event handler public event EventHandler<ImportDelimitedEventArgs> ProcessLine; private bool HandleLine(OleDbDataReader reader, int number) { bool result = true; EventHandler<ImportDelimitedEventArgs> temp = ProcessLine; if (temp != null) { ImportDelimitedEventArgs args = new ImportDelimitedEventArgs(reader, number); temp(this, args); result = !args.BreakImport; } return result; } #endregion #region Constructors public DelimitedFileImporter() : this(DelimiterType.TabDelimited, null) { this.Initialize(); } public DelimitedFileImporter(DelimiterType delimiter) : this(delimiter, null) { this.Initialize(); } public DelimitedFileImporter(DelimiterType delimiterType, string delimiter) { this.delimiter = delimiterType; customDelimiter = delimiter; this.Initialize(); } private void Initialize() { this.ColumnMappings = new List<ColumnMapping>(); } #endregion #region Properties public DelimiterType Delimiter { get { return delimiter; } set { delimiter = value; } } public string CustomDelimiter { get { return customDelimiter; } set { customDelimiter = value; } } public string Filter { get { return filter; } set { filter = value; } } public string ConnectionString { get; set; } public List<ColumnMapping> ColumnMappings { get; set; } #endregion #region Methods public void ImportToDatabase(string fileName, string tableName) { if (string.IsNullOrEmpty(this.ConnectionString)) throw new ArgumentNullException("Connection String"); FileInfo file = new FileInfo(fileName); WriteSchemaIniFile(file); using (OleDbConnection con = JetConnection(file)) { using (OleDbCommand cmd = JetCommand(file, con)) { con.Open(); using(OleDbDataReader reader = cmd.ExecuteReader()) { SqlBulkCopy sqlBulk = new SqlBulkCopy(this.ConnectionString); sqlBulk.DestinationTableName = tableName; foreach (ColumnMapping map in this.ColumnMappings) sqlBulk.ColumnMappings.Add(map.DestinationColumn, map.SourceColumn); sqlBulk.WriteToServer(reader); } } } } private OleDbConnection JetConnection(FileInfo file) { StringBuilder connection = new StringBuilder("Provider=Microsoft.Jet.OLEDB.4.0"); connection.AppendFormat(";Data Source=\"{0}\"", file.DirectoryName); connection.Append(";Extended Properties='text;HDR=Yes"); if (delimiter == DelimiterType.CustomDelimited) { if (customDelimiter == null) throw new InvalidOperationException("Custom delimiter is not specified"); connection.AppendFormat(";FMT=Delimited({1})", customDelimiter); } connection.Append("';"); return new OleDbConnection(connection.ToString()); } private OleDbCommand JetCommand(FileInfo file, OleDbConnection con) { StringBuilder commandText = new StringBuilder("SELECT * FROM "); commandText.AppendFormat("[{0}]", file.Name); if (filter != null) { commandText.Append(" WHERE "); commandText.Append(filter); } OleDbCommand cmd = new OleDbCommand(commandText.ToString(), con); cmd.CommandTimeout = 60000; return cmd; } private void WriteSchemaIniFile(FileInfo file) { string schema = Path.Combine(file.DirectoryName, "Schema.ini"); if (!File.Exists(schema)) File.Delete(schema); using (StreamWriter writer = new StreamWriter(schema)) { writer.WriteLine(string.Format(CultureInfo.InvariantCulture, "[{0}]", file.Name)); switch (delimiter) { case DelimiterType.CustomDelimited: writer.WriteLine(string.Format(CultureInfo.InvariantCulture, "Format=Delimited({0})", customDelimiter)); break; case DelimiterType.CsvDelimited: case DelimiterType.TabDelimited: default: writer.WriteLine(string.Format(CultureInfo.InvariantCulture, "Format={0}", delimiter)); break; } } } #endregion } #endregion
Reference
No comments:
Post a Comment