private void bGetDBt_Click(object sender, EventArgs e) { if (!Ut.IsCommand(tbSQLConn)) return; using (SqlConnection db = new SqlConnection(tbSQLConn.Text)) { db.Open(); using (SqlDataReader dr = new SqlCommand("SELECT (SELECT name FROM sys.databases WHERE sys.databases.database_id = sys.master_files.database_id)as db,name,physical_name FROM sys.master_files;", db).ExecuteReader()) { DataTable dt = new DataTable(); int cx = dr.FieldCount; for (int x = 0; x < cx; x++) { dt.Columns.Add(dr.GetName(x), dr.GetFieldType(x)); } Object[] vals = new Object[cx]; while (dr.Read()) { dr.GetValues(vals); dt.Rows.Add(vals); } dt.AcceptChanges(); gv.DataSource = dt; gv.Refresh(); gv.AutoResizeColumns(); } db.Close(); } }
public virtual object[] getDataAry(string argString) { this.rState = 0; string cmdText = argString; object[] objArray = null; try { SqlConnection connection = new SqlConnection(this.connStr); connection.Open(); SqlDataReader reader = new SqlCommand(cmdText, connection).ExecuteReader(); int fieldCount = reader.FieldCount; object[,] objArray2 = null; while (reader.Read()) { objArray2 = new object[fieldCount, 2]; for (int i = 0; i < fieldCount; i++) { objArray2[i, 0] = reader.GetName(i); objArray2[i, 1] = reader.GetValue(i); } objArray = cls.mergeAry(objArray, objArray2); } reader.Close(); connection.Close(); } catch (Exception exception) { this.rState = 1; objArray = null; this.eMessage = exception.Message; } return objArray; }
private void BulkCopy_DoWork(object sender, DoWorkEventArgs e) { //TODO: use more usings! var sw = Stopwatch.StartNew(); SqlConnection connSource = null; SqlConnection connDest = null; var result = new BulkCopyResult(); e.Result = result; var parameters = (BulkCopyParameters)e.Argument; try { connSource = new SqlConnection(parameters.Source.ConnectionString); connDest = new SqlConnection(parameters.Destination.ConnectionString); connSource.Open(); connDest.Open(); SqlDataReader reader = null; foreach (var table in parameters.Tables.Values) { if (worker.CancellationPending) { e.Cancel = true; return; } Log.Information("Copying rows to {0}", "[" + connDest.DataSource + "].[" + connDest.Database + "]." + table.FullName); SqlTransaction transaction = null; SqlBulkCopy bulkCopy = null; try { //Use Dictionary<,> so that destination column names can be case-insensitively located in their proper case because SqlBulkCopy mappings are case-sensitive! var destSchema = connDest.GetSchema("Columns", new[] {connDest.Database, table.Schema, table.Name}); var destColumnsMap = destSchema.AsEnumerable().Select(x => x.Field<string>("COLUMN_NAME")).ToDictionary(k => k, v => v, StringComparer.OrdinalIgnoreCase); transaction = connDest.BeginTransaction(); string query = String.Format(parameters.Query, table.FullName); reader = new SqlCommand(query, connSource) { CommandTimeout = 9000 }.ExecuteReader(); //TODO: any FKs should be dropped and then recreated after truncating try { new SqlCommand(String.Format(Query_TruncateTable, table.FullName), connDest, transaction) { CommandTimeout = 120 }.ExecuteNonQuery(); } catch { new SqlCommand(String.Format(Query_DeleteAllInTable, table.FullName), connDest, transaction) { CommandTimeout = 120 }.ExecuteNonQuery(); } bulkCopy = new SqlBulkCopy(connDest, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.KeepNulls, transaction) { BulkCopyTimeout = 9000, BatchSize = 10000, NotifyAfter = 10000, DestinationTableName = table.FullName }; bulkCopy.SqlRowsCopied += sbc_SqlRowsCopied; //Iterate over the Reader to get source column names because they may be defined by query results rather than a table-schema var sourceColumns = new HashSet<string>(StringComparer.OrdinalIgnoreCase); for (int i = 0; i < reader.FieldCount; i++) { sourceColumns.Add(reader.GetName(i)); } var mapColumns = bulkCopy.ColumnMappings; foreach (var column in sourceColumns) { if (destColumnsMap.ContainsKey(column)) { mapColumns.Add(column, destColumnsMap[column]); } } rowsInCurrentTable = table.RowCount; //Make sure the progress indicators are updated immediately, so the correct progress details are shown sbc_SqlRowsCopied(bulkCopy, new SqlRowsCopiedEventArgs(0)); bulkCopy.WriteToServer(reader); transaction.Commit(); Log.Information("Copied approximately {0} rows to {1}", table.RowCount, "[" + connDest.DataSource + "].[" + connDest.Database + "]." + table.FullName); } catch (Exception ex) { result.FailedTables[table.FullName] = ex; transaction?.Rollback(); } finally { bulkCopy?.Close(); reader?.Close(); } if (worker.CancellationPending) { e.Cancel = true; return; } } } finally { connDest?.Close(); connSource?.Close(); sw.Stop(); result.Elapsed = sw.Elapsed; } }
public virtual string getFieldList(string argString) { string cmdText = argString; string argObject = ""; try { SqlConnection connection = new SqlConnection(this.connStr); connection.Open(); SqlDataReader reader = new SqlCommand(cmdText, connection).ExecuteReader(); int fieldCount = reader.FieldCount; for (int i = 0; i < fieldCount; i++) { argObject = argObject + reader.GetName(i) + "|"; } if (!cls.isEmpty(argObject)) { argObject = cls.getLRStr(argObject, "|", "leftr"); } reader.Close(); connection.Close(); } catch (Exception exception) { argObject = ""; this.eMessage = exception.Message; } return argObject; }