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);
}