public static void spGetNewVestaErrands(SqlDateTime date)
{
DateTime d = date.Value;
String reqStr = String.Format("http://{0}/services/internalSite/errands/sendNewVestaErrandsMails?date={1}",
UserDefinedFunctions.fGetWapServerName(), d.ToString("s"));
SqlContext.Pipe.Send(reqStr);
HttpWebRequest rq = (HttpWebRequest)WebRequest.Create(reqStr);
rq.KeepAlive = false;
XmlDocument xdoc = new XmlDocument();
using(HttpWebResponse rs = (HttpWebResponse)rq.GetResponse())
using(Stream stream = rs.GetResponseStream())
xdoc.Load(stream);
XmlNode root = xdoc["result"];
SqlDataRecord rec = new SqlDataRecord(new SqlMetaData("id", SqlDbType.NVarChar, -1),
new SqlMetaData("text", SqlDbType.NVarChar, -1),
new SqlMetaData("creator", SqlDbType.NVarChar, -1),
new SqlMetaData("email", SqlDbType.NVarChar, -1)
);
SqlContext.Pipe.SendResultsStart(rec);
foreach(XmlNode ch in root.ChildNodes)
{
rec.SetValues(ch["id"].InnerText,
ch["text"].InnerText,
ch["creator"].InnerText,
ch["email"].InnerText
);
SqlContext.Pipe.SendResultsRow(rec);
}
SqlContext.Pipe.SendResultsEnd();
}
private static SqlDataRecord createRecordPopulatedWithData(SqlDataReader dataReader, SqlMetaData[] meta)
{
SqlDataRecord rec = new SqlDataRecord(meta);
object[] recordData = new object[dataReader.FieldCount];
dataReader.GetSqlValues(recordData);
rec.SetValues(recordData);
return rec;
}
private static void SendTable(SqlDataReader reader, TRowSetMap Map)
{
//SqlDataRecord ReadRecord = new SqlDataRecord(DataReaderFields(reader));
DataTable LDataTable = reader.GetSchemaTable();
SqlDataRecord WriteRecord;
List<TFieldAlias> Fields = new List<TFieldAlias>();
TFieldAlias Field;
string FieldName;
int FieldCount = reader.FieldCount, WriteFieldCount = 0;
int i;
SqlMetaData[] WriteFields;
if(Map.Fields.Length > 0)
{
WriteFields = new SqlMetaData[0];
foreach (string FieldMap in Map.Fields.Split(new char[] {','}))
{
i = FieldMap.IndexOf('=');
if(i >= 0)
{
Field.Name = FieldMap.Substring(0, i);
FieldName = FieldMap.Substring(i + 1);
}
else
{
Field.Name = FieldMap;
FieldName = FieldMap;
}
for(i = 0; i < FieldCount; i++)
{
if(FieldName.ToUpper() == reader.GetName(i).ToUpper())
break;
}
if((i < 0) || (i >= FieldCount))
throw new SystemException("RowSet Field = [" + FieldName + "] not found.");
Field.FieldIndex = i;
Fields.Add(Field);
Array.Resize(ref WriteFields, ++WriteFieldCount);
//WriteFields[WriteFieldCount - 1] = SqlMetaData(LDataTable.Rows[WriteFieldCount - 1], Field.Name);
WriteFields[WriteFieldCount - 1] = SqlMetaData(LDataTable.Rows[Field.FieldIndex], Field.Name);
}
}
else
{
WriteFields = new SqlMetaData[FieldCount];
for (; WriteFieldCount < reader.FieldCount; WriteFieldCount++)
WriteFields[WriteFieldCount] = SqlMetaData(LDataTable.Rows[WriteFieldCount]);
}
WriteRecord = new SqlDataRecord(WriteFields);
try
{
SqlContext.Pipe.SendResultsStart(WriteRecord);
Object[] values = new Object[FieldCount];
while (reader.Read())
{
reader.GetValues(values);
if(Map.Fields.Length > 0)
{
for(i = 0; i < WriteFieldCount; i++)
WriteRecord.SetValue(i, values[Fields[i].FieldIndex]);
}
else
{
WriteRecord.SetValues(values);
}
SqlContext.Pipe.SendResultsRow(WriteRecord);
}
}
finally
{
SqlContext.Pipe.SendResultsEnd();
}
}
public override void Start()
{
try
{
Status = TaskStatus.Running;
var token = _cancelToken.Token;
Task.Run(() =>
{
try
{
_dtResult = new DataTable();
var tableList = new List<Tuple<string, int, int, int>>().Select(item => new { FullTableName = item.Item1, HasSameColumns = item.Item2, HasPK = item.Item3, MaxRowCountFromBoth = item.Item4 }).ToList();
if (!UserPermissions.Instance.UserSpecificPermissions[Owner.ToUpper()].CopyAndSearchFromDatabaseSeverList.Contains(_databaseServer))
throw new Exception("No permission to use this server.");
List<string> tableListRevided = new List<string>();
if (!String.IsNullOrWhiteSpace(_listOfTablesToCompare))
_listOfTablesToCompare.Replace("--", "").Split(',').ToList().ForEach(item => tableListRevided.Add(item.Trim()));
AppendOutputText(String.Format("Retrieving list of table to compare...{0}", Environment.NewLine));
var serverToUse = DatabaseServers.Instance.ItemsList.FirstOrDefault(item => item.Name == _databaseServer);
try
{
using (SqlConnection conn = new SqlConnection(serverToUse.ConnectionString + ";Connection Timeout=30"))
{
SqlMetaData[] meta = new[]
{
new SqlMetaData("item", SqlDbType.NVarChar, 200),
};
var results = tableListRevided.Select(item =>
{
SqlDataRecord newRow = new SqlDataRecord(meta);
newRow.SetValues(item);
return newRow;
});
SqlCommand command = conn.CreateCommand();
command.CommandTimeout = 5 * 60;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "dbo.GetTablesForDataCompare";
command.Parameters.Add(new SqlParameter("@FirstDatabaseName", _databaseNameToCompare));
command.Parameters.Add(new SqlParameter("@SecondDatabaseName", _databaseNameToCompareAgaints));
SqlParameter param1 = new SqlParameter("@TablesToCompare", SqlDbType.Structured);
param1.TypeName = "dbo.StringList";
param1.Value = tableListRevided.Count > 0 ? results : null;
command.Parameters.Add(param1);
conn.Open();
SqlDataReader myReader = command.ExecuteReader(CommandBehavior.CloseConnection);
while (myReader.Read())
{
tableList.Add( new { FullTableName = myReader["FullTableName"].ToString(), HasSameColumns = Int32.Parse(myReader["HasSameColumns"].ToString()), HasPK = Int32.Parse(myReader["HasPK"].ToString()), MaxRowCountFromBoth = Int32.Parse(myReader["MaxRowCountFromBoth"].ToString()) });
}
conn.Close();
AppendOutputText(String.Format("Finished retrieving list of table.{0}", Environment.NewLine));
}
}
catch (Exception ex)
{
Status = TaskStatus.Failed;
this.AppendOutputText(ex.Message);
Log.ErrorFormat("Exception while retrieving list of table to compare from server: {0}, exception:{1}", serverToUse.Name, ex.Message);
return;
}
token.ThrowIfCancellationRequested();
int totalCount = tableList.Count;
if (totalCount == 0)
AppendOutputText(String.Format("There are no tables to compare.{0}", Environment.NewLine));
Parallel.ForEach(tableList, (item, loopState) =>
{
if (token.IsCancellationRequested)
{
loopState.Stop();
return;
}
if (item.HasSameColumns == 0)
{
AppendOutputText(String.Format("Finished {1,4} of {2}. Table {0} will not be compared, column schema difference.{3}", item.FullTableName, Interlocked.Increment(ref _taskProgress), totalCount, Environment.NewLine));
}
else if (item.HasPK == 0)
{
AppendOutputText(String.Format("Finished {1,4} of {2}. Table {0} will not be compared, missing primary key.{3}", item.FullTableName, Interlocked.Increment(ref _taskProgress), totalCount, Environment.NewLine));
}
else if (item.MaxRowCountFromBoth == 0)
{
AppendOutputText(String.Format("Finished {1,4} of {2}. Table {0} will not be compared, empty on both databases.{3}", item.FullTableName, Interlocked.Increment(ref _taskProgress), totalCount, Environment.NewLine));
}
else if (_maxTableRowCount.HasValue && item.MaxRowCountFromBoth > _maxTableRowCount.Value)
{
AppendOutputText(String.Format("Finished {1,4} of {2}. Table {0} will not be compared, rowcount exceding specified limit of {4}.{3}", item.FullTableName, Interlocked.Increment(ref _taskProgress), totalCount, Environment.NewLine, _maxTableRowCount.Value));
}
else
{
try
{
DataTable dtResults = new DataTable(item.FullTableName);
using (SqlConnection conn = new SqlConnection(serverToUse.ConnectionString + ";Connection Timeout=30"))
{
SqlCommand command = conn.CreateCommand();
command.CommandTimeout = 30 * 60;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "dbo.CompareDataInTable";
command.Parameters.Add(new SqlParameter("@TableName", item.FullTableName));
command.Parameters.Add(new SqlParameter("@FirstDatabaseName", _databaseNameToCompare));
command.Parameters.Add(new SqlParameter("@SecondDatabaseName", _databaseNameToCompareAgaints));
SqlDataAdapter dataAdaper = new SqlDataAdapter(command);
dataAdaper.Fill(dtResults);
AppendOutputText(String.Format("Finished {1,4} of {2}. Finished comparing data for table {0}.{3}", item.FullTableName, Interlocked.Increment(ref _taskProgress), totalCount, Environment.NewLine));
}
if (dtResults.Rows.Count > 0)
{
lock (_sync)
{
_dtResult.Merge(dtResults);
}
}
}
catch (Exception ex)
{
AppendOutputText(String.Format("Exception during CompareDataInTable for table {0}. Exception:{1}{2}", item.FullTableName, ex.Message, Environment.NewLine));
Log.ErrorFormat("Exception during CompareDataInTable for table {0}. Exception:{1}", item.FullTableName, ex.Message);
}
}
});
token.ThrowIfCancellationRequested();
if (_dtResult.Rows.Count > 0)
{
_dtResult.DefaultView.Sort = "[TableName] asc, [TableKeyValues] asc";
this.Result = _dtResult.DefaultView.ToTable("Result");
}
Status = TaskStatus.Succeeded;
}
catch(TaskCanceledException)
{
Status = TaskStatus.Aborted;
}
catch (Exception ex)
{
AppendOutputText(ex.Message);
Status = TaskStatus.Failed;
Log.Error("Failed to compare databases.", ex);
}
});
}catch(Exception ex)
{
Log.Error("Unable to start task.", ex);
throw;
}
}
public static List<string> GetTablesForDataTrack(string databaseServer, string databaseName, string tablesToTrackCommaSeparated, string databaseOwner)
{
try
{
List<string> sqlStatementList = new List<string>();
DatabaseServerInfo server = DatabaseServers.Instance.ItemsList.First(item => String.Compare(item.Name, databaseServer, true) == 0);
List<string> tablesToTrackList = new List<string>();
if (!String.IsNullOrWhiteSpace(tablesToTrackCommaSeparated))
tablesToTrackCommaSeparated.Replace("--", "").Split(',').ToList().ForEach(item => tablesToTrackList.Add(item.Trim()));
try
{
using (SqlConnection conn = new SqlConnection(server.ConnectionString))
{
SqlMetaData[] meta = new [] { new SqlMetaData("item", SqlDbType.NVarChar, 200)};
var results = tablesToTrackList.Select(item =>
{
SqlDataRecord newRow = new SqlDataRecord(meta);
newRow.SetValues(item);
return newRow;
});
var command = conn.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "dbo.GetTablesForDataTrack";
command.Parameters.AddWithValue("@DatabaseName", databaseName);
command.Parameters.AddWithValue("@DatabaseOwner", databaseOwner);
SqlParameter param1 = new SqlParameter("@TableNames", System.Data.SqlDbType.Structured);
param1.TypeName = "dbo.StringList";
param1.Value = tablesToTrackList.Count > 0 ? results : null;
command.Parameters.Add(param1);
conn.Open();
SqlDataReader myReader = command.ExecuteReader(CommandBehavior.CloseConnection);
while (myReader.Read())
{
sqlStatementList.Add(myReader.GetString(0));
}
}
return sqlStatementList;
}
catch (Exception ex)
{
Log.Error(String.Format("Problem accessing server: {0}.", server.Name), ex);
throw;
}
}
catch (Exception ex)
{
Log.Error(ex);
throw;
}
}
public static void OpenQueryCLR(string ConnStr, string Query) {
try {
PrintOutput = null;
using (SqlConnection connection = new SqlConnection(ConnStr))
{
connection.Open();
connection.InfoMessage += new SqlInfoMessageEventHandler(connection_InfoMessage);
using (IDbCommand qry = new SqlCommand())
{
qry.Connection = connection;
qry.CommandText = Query;
qry.CommandType = CommandType.Text;
//execute the proc and get a reader back
using (IDataReader rdr = qry.ExecuteReader(CommandBehavior.CloseConnection))
{
//use the returned columns to build a sql resultset
using (DataTable columns = rdr.GetSchemaTable())
{
//only return a resultset if we actually got one to return, many /400 procs are just parm outputs
if ((columns != null) && (columns.Rows.Count > 0))
{
SqlMetaData[] md = new SqlMetaData[columns.Rows.Count];
for (int c = 0; c < columns.Rows.Count; c++)
{
md[c] = new SqlMetaData(columns.Rows[c]["ColumnName"].ToString(), SqlDbType.VarChar, -1);
}
SqlDataRecord record = new SqlDataRecord(md);
object[] vals = new object[columns.Rows.Count];
SqlContext.Pipe.SendResultsStart(record);
int rowcount = 0;
while (rdr.Read())
{
rowcount++;
rdr.GetValues(vals);
for (int i = 0; i < vals.Length; i++) vals[i] = vals[i].ToString().Trim();
record.SetValues(vals);
SqlContext.Pipe.SendResultsRow(record);
}
SqlContext.Pipe.SendResultsEnd();
}
}
}
//connection.Close(); // this happens automatically as a result of "CommandBehavior.CloseConnection" passed in to the qry.ExecuteReader
}
}
if (PrintOutput != null) SqlContext.Pipe.Send(PrintOutput);
}
catch (Exception ex) {
if (SqlContext.Pipe.IsSendingResults) SqlContext.Pipe.SendResultsEnd();
SqlContext.Pipe.Send("Exception: " + ex.Message + "\r\n" + ex.StackTrace);
}
}
