public static async Task <int> ImportToMySqlAsync(string dbname, string dtname, string connectionString, string filepath, ControlSet controlSet, bool firstLineColNames) { if (string.IsNullOrWhiteSpace(filepath)) { return(-1); } string _connectionString = connectionString;// @"server=localhost;user id=root;persistsecurityinfo=False;database=sakila"; MySqlConnection mySqlConnection; mySqlConnection = new MySqlConnection(_connectionString); FileStream fileStream = new FileStream(filepath, FileMode.Open, FileAccess.Read, FileShare.Read, 4096, true); StreamReader streamReader = new StreamReader(fileStream); int importedRecordCount = 0; if (!File.Exists(filepath)) { MessageBox.Show($@"File {filepath} no found."); return(0); } //try //{ mySqlConnection.Open(); //} //catch (Exception e) //{ // controlSet.textBoxLog.AppendText($"{e.Message}\r\n"); //} int currentLineNumber = 0; long lineCount = getLineCount(streamReader, false); setProgressBarMaxValue(controlSet.toolStripProgressBar, (int)lineCount); streamReader.BaseStream.Position = 0; while (!streamReader.EndOfStream) { currentLineNumber++; string str = streamReader.ReadLine(); List <string> splited = GetSplitedStrings(str); if (splited == null) { continue; } // When current line number is 1 if (currentLineNumber == 1) { // If the first line contains column name if (firstLineColNames) { string fields = ""; //if(string.IsNullOrWhiteSpace(splited)) for (int i = 0; i < splited.Count; i++) { if (i == splited.Count - 1) { fields += $"`{splited[i]}` varchar(255) null"; } else { fields += $"`{splited[i]}` varchar(255) null,"; } } //string checkDbExistsString = "IF (DB_ID) is " string createDbCommandString = $"CREATE DATABASE IF NOT EXISTS {dbname};"; string createDtCommandString = $"USE {dbname} ; CREATE TABLE IF NOT EXISTS {dtname}({fields});"; //controlSet.textBoxLog.AppendText(createDtCommandString); MySqlCommand createDbCommand = new MySqlCommand(createDbCommandString, mySqlConnection); controlSet.textBoxLog.AppendText($"Executed {createDbCommandString} successfully.\r\n"); MySqlCommand createDtCommand = new MySqlCommand(createDtCommandString, mySqlConnection); controlSet.textBoxLog.AppendText($"Executed {createDtCommandString} successfully.\r\n"); createDbCommand.ExecuteNonQuery(); createDtCommand.ExecuteNonQuery(); createDtCommand.Dispose(); createDbCommand.Dispose(); } else { string valueString = ""; for (int i = 0; i < splited.Count; i++) { if (i == splited.Count - 1) { valueString += $"'{splited[i]}'"; break; } string str2 = ""; if (splited[i].Contains("'")) { str2 = splited[i].Replace("'", "''"); } else { str2 = splited[i]; } valueString += $"'{str2}',"; } string insertCommandString = $"USE {dbname};INSERT INTO {dtname} VALUES({valueString});"; //try //{ MySqlCommand insertCommand = new MySqlCommand(insertCommandString, mySqlConnection); try { await insertCommand.ExecuteNonQueryAsync(); } catch (Exception e) { //controlSet.toolStripStatusLabeL.Text += @" Text format incorrect."; Program.getMainForm().gettextBoxLog().AppendText($"\nExecute \"{insertCommandString}\" failed.\n" + $"{e.Message} \n"); var dialogResult = MessageBox.Show("Continue ?", "", MessageBoxButtons.YesNo); if (dialogResult == DialogResult.OK) { } else { break; } } controlSet.toolStripStatusLabeL.Text = $@"Progress : {currentLineNumber}/{lineCount}"; controlSet.toolStripProgressBar.Value = currentLineNumber; insertCommand.Dispose(); importedRecordCount++; } } else { string valueString = ""; for (int i = 0; i < splited.Count; i++) { if (i == splited.Count - 1) { valueString += $"'{splited[i]}'"; break; } else { string str2 = ""; if (splited[i].Contains("'")) { str2 = splited[i].Replace("'", "''"); } else { str2 = splited[i]; } valueString += $"'{str2}',"; } } string insertCommandString = $"USE {dbname};INSERT INTO {dtname} VALUES({valueString});"; //try //{ MySqlCommand insertCommand = new MySqlCommand(insertCommandString, mySqlConnection); try { controlSet.textBoxLog.AppendText($"Execute {insertCommandString}.\r\n"); await insertCommand.ExecuteNonQueryAsync(); } catch (Exception e) { //controlSet.toolStripStatusLabeL.Text += @" Text format incorrect."; Program.getMainForm().gettextBoxLog().AppendText($"\nExecute \"{insertCommandString}\" failed.\n" + $"{e.Message} \n"); var dialogResult = MessageBox.Show(@"Continue ?", "", MessageBoxButtons.YesNo); if (dialogResult == DialogResult.Yes) { continue; } break; } controlSet.toolStripStatusLabeL.Text = $@"Progress : {currentLineNumber}/{lineCount}"; controlSet.toolStripProgressBar.Value = currentLineNumber; insertCommand.Dispose(); importedRecordCount++; } } MessageBox.Show($@"Imported {importedRecordCount} record."); mySqlConnection.Dispose(); streamReader.Dispose(); fileStream.Dispose(); return(importedRecordCount); }
/// <summary> /// Import data from text file to Sql server /// </summary> /// <param name="dbname">Database name</param> /// <param name="filepath">Filepath</param> /// <param name="dtname">Datatable name</param> /// <param name="controlSet"></param> /// <param name="firstLineColNames">Whether ignore first line, default value is <value>false</value></param> /// <returns></returns> public static async Task <int> WriteToSqlServer(string dbname, string dtname, string filepath, ControlSet controlSet, bool firstLineColNames) { if (string.IsNullOrWhiteSpace(filepath)) { return(-1); } string connectionString = @"Data Source=.;Initial Catalog=master;Integrated Security=True";//ConfigurationManager.ConnectionStrings["DBmaster"].ConnectionString; sqlConnection = new SqlConnection(connectionString); fileStream = new FileStream(filepath, FileMode.Open, FileAccess.Read, FileShare.Read, 4096, true); try { ; } catch { } StreamReader streamReader = new StreamReader(fileStream); int importedRecordCount = 0; if (!File.Exists(filepath)) { MessageBox.Show($@"File {filepath} no found."); return(-1); } try { sqlConnection.Open(); } catch (Exception e) { controlSet.textBoxLog.AppendText($"{e.Message}\r\n"); } int currentLineNumber = 0; long lineCount = getLineCount(streamReader, false); mainForm.toolStripProgressBar1.GetCurrentParent().Invoke( new OperateControls.setProgressBarValueDelegate(OperateControls.setProgressBar), (int)lineCount); streamReader.BaseStream.Position = 0; while (!streamReader.EndOfStream) { currentLineNumber++; string str = streamReader.ReadLine(); List <string> splited = GetSplitedStrings(str); if (splited == null) { continue; } if (currentLineNumber == 1) { if (firstLineColNames) { string fields = ""; for (int i = 0; i < splited.Count; i++) { fields += $"[{splited[i]}] varchar(255) null,"; } string createDbCommandString = $"IF(DB_ID('{dbname}') IS NULL) CREATE DATABASE [{dbname}];"; string createDtCommandString = $"USE {dbname}; IF NOT EXISTS(SELECT [NAME] FROM SYS.TABLES WHERE [NAME] = '{dtname}') CREATE TABLE {dtname}({fields});"; SqlCommand createDbCommand = new SqlCommand(createDbCommandString, sqlConnection); SqlCommand createDtCommand = new SqlCommand(createDtCommandString, sqlConnection); createDbCommand.ExecuteNonQuery(); createDtCommand.ExecuteNonQuery(); createDtCommand.Dispose(); createDbCommand.Dispose(); } } else { string valueString = ""; for (int i = 0; i < splited.Count; i++) { if (i == splited.Count - 1) { valueString += $"'{splited[i]}'"; break; } else { string str2 = ""; if (splited[i].Contains("'")) { str2 = splited[i].Replace("'", "''"); } else { str2 = splited[i]; } valueString += $"'{str2}',"; } } string insertCommandString = $"USE {dbname};INSERT INTO {dtname} VALUES({valueString});"; SqlCommand insertCommand = new SqlCommand(insertCommandString, sqlConnection); try { await insertCommand.ExecuteNonQueryAsync(); } catch (Exception e) { mainForm.textBoxLog.Invoke( new OperateControls.appendTextBoxTextDelegate(OperateControls.appendTextBoxText), $"\nExecute \"{insertCommandString}\" failed.\n" + $"{e.Message} \n"); var dialogResult = MessageBox.Show("Continue ?", "", MessageBoxButtons.YesNo); if (dialogResult == DialogResult.OK) { } else { break; } } controlSet.toolStripStatusLabeL.Text = $@"Progress : {currentLineNumber}/{lineCount}"; controlSet.toolStripProgressBar.Value = currentLineNumber; insertCommand.Dispose(); importedRecordCount++; } } MessageBox.Show($@"Imported {importedRecordCount} record."); sqlConnection.Dispose(); streamReader.Dispose(); fileStream.Dispose(); return(importedRecordCount); }