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