I have written a windows service to insert CSV data into MySQL database table using C#.
I have used Answer 1 as a reference for Insert method.
In the Connection String line, there is an error:
System.NullReferenceException HResult=0x80004003 Message=Object reference not set to an instance of an object.
System.Configuration.ConnectionStringSettingsCollection.this[string].get returned null.
Source code for Insert:
public void insert()
{
const string CSV_CONNECTIONSTRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"{0}\";Extended Properties=\"text;HDR=YES;FMT=Delimited\"";
string CSVpath = @"C:\Users\Admin\source\"; // CSV file Path you can use file choose control
var AllFiles = new DirectoryInfo(CSVpath).GetFiles("*.CSV");
string File_Name = string.Empty;
**string ConStr = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString; //error line
for (int i = 0; i < AllFiles.Length; i++)
{
try
{
File_Name = AllFiles[i].Name;
DataTable dt = new DataTable();
using (OleDbConnection con = new OleDbConnection(string.Format(CSV_CONNECTIONSTRING, CSVpath)))
{
using (OleDbDataAdapter da = new OleDbDataAdapter("select * from [" + File_Name + "]", con))
{
da.Fill(dt);
}
}
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(ConStr))
{
bulkCopy.ColumnMappings.Add(0, "Column1");
bulkCopy.ColumnMappings.Add(1, "Column2");
bulkCopy.ColumnMappings.Add(2, "Column3");
bulkCopy.DestinationTableName = "myTable";
bulkCopy.BatchSize = dt.Rows.Count;
bulkCopy.WriteToServer(dt);
bulkCopy.Close();
}
}
catch (Exception ex)
{
throw ex;
}
}
}
App.config
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
<!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
</configSections>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1" />
</startup>
<system.data>
<DbProviderFactories>
<remove invariant="MySql.Data.MySqlClient" />
<add description=".Net Framework Data Provider for MySQL" invariant="MySql.Data.MySqlClient" name="MySQL Data Provider" type="MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data, Version=6.10.6.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />
</DbProviderFactories>
</system.data>
<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
<parameters>
<parameter value="mssqllocaldb" />
</parameters>
</defaultConnectionFactory>
<providers>
<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
<provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6, Version=6.10.6.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d">
</provider></providers>
</entityFramework>
</configuration>
There is one more question regarding the MySQL table information. I could not find the line for MySQL table which data is written.