1

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.

Simon
  • 195
  • 2
  • 4
  • 18
  • 3
    In which line the error thrown? `ConfigurationManager.ConnectionStrings["ConStr"]` probably returns null value, and NRE occurs when accessing `ConnectionString` property. – Tetsuya Yamamoto Sep 28 '18 at 03:31
  • Could you please show your app.config file also? I guest something is wrong in your app.config then you could not retrieve "ConStr" via ConfigurationManager – Nhan Phan Sep 28 '18 at 04:13
  • @Nhan Phan I have updated my question. Thank you! – Simon Sep 28 '18 at 04:17

2 Answers2

1

You missed your connection string inside the app.config. Add it to your app.config.

Ex:

<connectionStrings>
    <add name="ConStr" connectionString="Server=127.0.0.1; Database=databaseName;Uid=yourUserName;Pwd='Yourpasword'" providerName="MySql.Data.MySqlClient"/>
</connectionStrings>
er-sho
  • 9,581
  • 2
  • 13
  • 26
Nhan Phan
  • 1,262
  • 1
  • 14
  • 32
0

My bet is that string ConStr = ConfigurationManager.ConnectionStrings["ConStr"] results in null.

You could:

  1. Split the string ConStr = ConfigurationManager.ConnectionStrings["ConStr"].ConnectionString into 

    var setting = ConfigurationManager.ConnectionStrings["ConStr"];
if( setting == null ) {
      throw new Exception ("Unable to retrieve connection string")
}
string ConStr = setting.ConnectionString;
if( string.IsNullOrEmpty(ConStr) ) {
      throw new Exception ("Connection string is empty");
}

  2. Put a breakpoint on the first line after 'var setting =' and check the value.

Edit

After your edit, I can see you're missing connectionstrings section in your config. It could be something like:

<connectionStrings> <add name="ProductDBConnection" connectionString="Server=tcp:(...);Connection Timeout=30" providerName="System.Data.SqlClient" /> </connectionStrings>

(This one is for MS Sql Server, you need to find one for MySql)

tymtam
  • 31,798
  • 8
  • 86
  • 126