public static bool CreateTable(DataTable dataTable, SQLParameters sqlParameters)
{
try
{
string sSQL = GenerateScript(dataTable);
if (BeginSomeStep != null)
{
BeginSomeStep(className, new dbfCoreEventArgs(sSQL, ""));
}
//Console.WriteLine(sSQL);
using (SqlConnection sqlConnection = new SqlConnection(sqlParameters.sqlConnectionString))
{
sqlConnection.Open();
using (SqlCommand cmd = sqlConnection.CreateCommand())
{
//cmd.Transaction = sqlParameters.sqlTransaction;
cmd.CommandText = sSQL;
cmd.ExecuteNonQuery();
}
sqlConnection.Close();
}
return(true);
}
catch (Exception ex)
{
if (BeginSomeError != null)
{
BeginSomeError(className, new dbfCoreEventArgs(ex.ToString(), ""));
}
//Console.WriteLine(ex.ToString());
return(false);
}
}
public static int GetRowCountFromTable(SQLParameters sqlParameters)
{
try
{
string sSQL = GenerateScriptCount(sqlParameters.sqlTableName);
if (BeginSomeStep != null)
{
BeginSomeStep(className, new dbfCoreEventArgs(sSQL, ""));
}
//Console.WriteLine(sSQL);
int result;
using (SqlConnection sqlConnection = new SqlConnection(sqlParameters.sqlConnectionString))
{
sqlConnection.Open();
using (SqlCommand cmd = sqlConnection.CreateCommand())
{
cmd.CommandText = sSQL;
result = (int)cmd.ExecuteScalar();
}
sqlConnection.Close();
}
return(result);
}
catch (Exception ex)
{
if (BeginSomeError != null)
{
BeginSomeError(className, new dbfCoreEventArgs(ex.ToString(), ""));
}
//Console.WriteLine(ex.ToString());
return(0);
}
}
public static int BulkCopyToSQL(DataTable dataTable, SQLParameters sqlParameters, string targetTable = "")
{
if (string.IsNullOrEmpty(targetTable))
{
targetTable = dataTable.TableName;
}
int cnt = 0;
SqlTransaction sqlTransaction = null;
try
{
using (SqlConnection sqlConnection = new SqlConnection(sqlParameters.sqlConnectionString))
{
sqlConnection.Open();
using (sqlTransaction = sqlConnection.BeginTransaction())
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnection, SqlBulkCopyOptions.Default, sqlTransaction))
{
bulkCopy.DestinationTableName = targetTable;
bulkCopy.BatchSize = sqlParameters.sqlBulkCopyBatchSize;
bulkCopy.WriteToServer(dataTable);
sqlTransaction.Commit();
}
using (SqlCommand cmd = sqlConnection.CreateCommand())
{
//cmd.Transaction = sqlTransaction;
cmd.CommandText = string.Format("SELECT COUNT(*) FROM {0}", targetTable);
cnt = (int)cmd.ExecuteScalar();
}
}
sqlConnection.Close();
}
}
catch
{
if (sqlTransaction != null)
{
sqlTransaction.Rollback();
}
throw;
}
return(cnt);
}
public static DataTable GetDataFromTable(SQLParameters sqlParameters, int batchNumber = 0)
{
try
{
DataTable dataTable = new DataTable();
string sSQL = GenerateSelectScript(sqlParameters.sqlTableName, sqlParameters.rowsToSelect, batchNumber);
if (BeginSomeStep != null)
{
BeginSomeStep(className, new dbfCoreEventArgs(sSQL, ""));
}
//Console.WriteLine(sSQL);
using (SqlConnection sqlConnection = new SqlConnection(sqlParameters.sqlConnectionString))
{
sqlConnection.Open();
using (SqlCommand cmd = sqlConnection.CreateCommand())
{
cmd.CommandText = sSQL;
SqlDataAdapter dataAdapter = new SqlDataAdapter(cmd);
dataAdapter.Fill(dataTable);
dataAdapter.Dispose();
}
sqlConnection.Close();
}
return(dataTable);
}
catch (Exception ex)
{
if (BeginSomeError != null)
{
BeginSomeError(className, new dbfCoreEventArgs(ex.ToString(), ""));
}
//Console.WriteLine(ex.ToString());
return(null);
}
}
