Example #1
0
 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();
     }
 }
Example #2
0
 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;
 }
Example #3
0
        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;
            }
        }
Example #4
0
 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;
 }