I am developing a C# Desktop Application where I want to store values stored in an Excel file to a Remote MySql Database.

Early help will really be Appreciated.

Thanks, and Regards Rohit Sharma, The Webgrapghica

Need guidance to solve the issue.

CodePudding user response：

Here is some sample code that should help you get started with connecting to a remote MySQL database from your C# desktop application and storing values from an Excel file into the database.

// Create a connection string for the remote MySQL Database
string connString = "Server=yourServerAddress;Database=yourDatabaseName;User ID=yourUsername;Password=yourPassword;";

// Create a connection object
MySqlConnection conn = new MySqlConnection(connString);

// Open the connection
conn.Open();

// Create a command object to execute the query
MySqlCommand cmd = new MySqlCommand();

// Set the command connection
cmd.Connection = conn;

// Read the contents of the Excel file into a DataTable
string excelFilePath = "C://path/to/excel/file.xlsx";
DataTable excelData = new DataTable();
using (OleDbConnection connection = new OleDbConnection(
    @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="   excelFilePath  
    ";Extended Properties='Excel 12.0;HDR=Yes;'"))
{
    connection.Open();
    OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connection);
    adapter.Fill(excelData);
    connection.Close();
}

// Loop through each row in the DataTable
foreach (DataRow row in excelData.Rows)
{
    // Create an insert query for the remote MySQL Database
    string query = "INSERT INTO yourTableName VALUES ('"   row["column1"]   "', '"   row["column2"]   "', '"   row["column3"]   "')";

    // Set the command text
    cmd.CommandText = query;

    // Execute the query
    cmd.ExecuteNonQuery();
}

// Close the connection
conn.Close();