ASPForums.Net RSS Feedhttp://www.aspforums.net/Handlers/RSS.ashxLatest additions to the content that appears on ASPForums.Net(c) 2019 www.aspforums.com. All rights reserved.Import CSV file to database without using oledb in ASP.Net<p>Hi&nbsp;<a class="username" rel="gpiyush152">gpiyush152,</a></p> <p>Check this example. Now please take its reference and correct your code.</p> <p>Rerering the below article i have created the example.</p> <h1 class="header"><a title="Import (Upload) CSV file data to SQL Server database in ASP.Net using C# and VB.Net'a" href="https://www.aspsnippets.com/Articles/Import-Upload-CSV-file-data-to-SQL-Server-database-in-ASPNet-using-C-and-VBNet.aspx">Import (Upload) CSV file data to SQL Server database in ASP.Net using C# and VB.Net</a></h1> <p><strong><span style="text-decoration: underline;">HTML</span></strong></p> <pre class="brush: html">&lt;asp:Button ID=&#34;btnImport&#34; runat=&#34;server&#34; Text=&#34;Import&#34; OnClick=&#34;ImportCSV&#34; /&gt;</pre> <p><strong><span style="text-decoration: underline;">Namespaces</span></strong></p> <p><strong>C#</strong></p> <pre class="brush: csharp">using System.IO; using System.Data; using System.Configuration; using System.Data.SqlClient;</pre> <p><strong>VB.Net</strong></p> <pre class="brush: vb">Imports System.IO Imports System.Data Imports System.Data.SqlClient</pre> <p><strong><span style="text-decoration: underline;">Code</span></strong></p> <p><strong>C#</strong></p> <pre class="brush: csharp">protected void ImportCSV(object sender, EventArgs e) { string csvPath = Server.MapPath(&#34;~/Files/Sample.csv&#34;); DataTable dt = new DataTable(); string csvData = File.ReadAllText(csvPath); int rowCount = 0; foreach (string row in csvData.Split(&#39;\n&#39;)) { if (!string.IsNullOrEmpty(row)) { if (rowCount == 0) { foreach (string column in row.Split(&#39;,&#39;)) { dt.Columns.Add(column); } } else { dt.Rows.Add(); int i = 0; foreach (string cell in row.Split(&#39;,&#39;)) { dt.Rows[dt.Rows.Count - 1][i] = cell; i++; } } rowCount++; } } if (dt.Rows.Count &gt; 0) { string consString = ConfigurationManager.ConnectionStrings[&#34;constr&#34;].ConnectionString; using (SqlConnection con = new SqlConnection(consString)) { using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con)) { //Set the database table name sqlBulkCopy.DestinationTableName = &#34;dbo.CustomerTest&#34;; for (int i = 0; i &lt; dt.Columns.Count; i++) { if (!string.IsNullOrEmpty(ColumnExist(dt.Columns[i].ToString().Trim()))) { sqlBulkCopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName.Trim()); } } con.Open(); sqlBulkCopy.WriteToServer(dt); con.Close(); } } } } private string ColumnExist(string columnName) { string conString = ConfigurationManager.ConnectionStrings[&#34;constr&#34;].ConnectionString; string query = &#34;SELECT COL_LENGTH(&#39;Customers&#39;,&#39;&#34; + columnName.Trim() + &#34;&#39;) Length&#34;; SqlCommand cmd = new SqlCommand(query); SqlConnection con = new SqlConnection(conString); con.Open(); cmd.Connection = con; string length = Convert.ToString(cmd.ExecuteScalar()); con.Close(); return length; }</pre> <p><strong>VB.Net</strong></p> <pre class="brush: vb">Protected Sub ImportCSV(ByVal sender As Object, ByVal e As EventArgs) Dim csvPath As String = Server.MapPath(&#34;~/Files/Sample.csv&#34;) Dim dt As DataTable = New DataTable() Dim csvData As String = File.ReadAllText(csvPath) Dim rowCount As Integer = 0 For Each row As String In csvData.Split(vbLf) If Not String.IsNullOrEmpty(row) Then If rowCount = 0 Then For Each column As String In row.Split(&#34;,&#34;c) dt.Columns.Add(column) Next Else dt.Rows.Add() Dim i As Integer = 0 For Each cell As String In row.Split(&#34;,&#34;c) dt.Rows(dt.Rows.Count - 1)(i) = cell i += 1 Next End If rowCount += 1 End If Next If dt.Rows.Count &gt; 0 Then Dim consString As String = ConfigurationManager.ConnectionStrings(&#34;constr&#34;).ConnectionString Using con As SqlConnection = New SqlConnection(consString) Using sqlBulkCopy As SqlBulkCopy = New SqlBulkCopy(con) sqlBulkCopy.DestinationTableName = &#34;dbo.CustomerTest&#34; For i As Integer = 0 To dt.Columns.Count - 1 If Not String.IsNullOrEmpty(ColumnExist(dt.Columns(i).ToString().Trim())) Then sqlBulkCopy.ColumnMappings.Add(dt.Columns(i).ColumnName, dt.Columns(i).ColumnName.Trim()) End If Next con.Open() sqlBulkCopy.WriteToServer(dt) con.Close() End Using End Using End If End Sub Private Function ColumnExist(ByVal columnName As String) As String Dim conString As String = ConfigurationManager.ConnectionStrings(&#34;constr&#34;).ConnectionString Dim query As String = &#34;SELECT COL_LENGTH(&#39;CustomerTest&#39;,&#39;&#34; &amp; columnName.Trim() &amp; &#34;&#39;) Length&#34; Dim cmd As SqlCommand = New SqlCommand(query) Dim con As SqlConnection = New SqlConnection(conString) con.Open() cmd.Connection = con Dim length As String = Convert.ToString(cmd.ExecuteScalar()) con.Close() Return length End Function</pre> <p><strong><span style="text-decoration: underline;">Screenshot</span></strong></p> <p><strong><span style="text-decoration: underline;"><img src="https://i.imgur.com/9a83Re7.jpg" alt="" width="472" height="143" /></span></strong></p>https://www.aspforums.net:443/Threads/778080/Import-CSV-file-to-database-without-using-oledb-in-ASPNet/https://www.aspforums.net:443/Threads/778080/Import-CSV-file-to-database-without-using-oledb-in-ASPNet/Wed, 02 May 2018 03:48:19 GMT