private void btnGenInsertSql_Click(object sender, EventArgs e)
{
try
{
var helper = new DbHelper(DBGlobalService.Connection);
var sql = this.textEditorControl1.Text.Trim();
var ds = helper.ExecuteDataSet(sql);
var insertSql = "insert into " + ds.Tables[0].TableName;
insertSql += "(";
foreach (DataColumn column in ds.Tables[0].Columns)
{
insertSql += column.ColumnName;
}
insertSql += ")";
foreach (var row in ds.Tables[0].Rows)
{
}
helper.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
private void CompareViewCode(ConnectionType otherConn, List<TableType> thisObjs, List<TableType> otherObjs, StringBuilder builder)
{
try
{
var helper = new DbHelper(this.dataInfo);
var otherHelper = new DbHelper(otherConn);
var views = new List<string>();
foreach (var thisView in thisObjs)
{
if (otherObjs.FindTable(thisView.Name) != null)
{
views.Add(thisView.Name);
continue;
}
}
var v_str = "";
for (var index = 0; index < views.Count; index++)
{
if (index > 0)
v_str += ",";
v_str += string.Format("'{0}'", views[index]);
}
var sql = string.Format(@"SELECT obj.name,m.definition
FROM sys.sql_modules m JOIN sys.objects obj
ON m.object_id= obj.object_id
and obj.type='v'
and obj.name in ({0}) order by obj.name", v_str);
var thisDs = helper.ExecuteDataSet(sql);
var otherDs = otherHelper.ExecuteDataSet(sql);
for (var index = 0; index < thisDs.Tables[0].Rows.Count; index++)
{
var thisRow = thisDs.Tables[0].Rows[index];
var otherRow = otherDs.Tables[0].Rows[index];
var thisSql = thisRow["definition"].ToString().Trim();
var otherSql = otherRow["definition"].ToString().Trim();
var thisReader = new System.IO.StringReader(thisSql);
var otherReader = new System.IO.StringReader(otherSql);
var thisFirstLine = thisReader.ReadLine().Replace("[", "").Replace("]","");
var otherFirstLine = otherReader.ReadLine().Replace("[", "").Replace("]", "");
if (thisFirstLine != otherFirstLine)
{
builder.AppendFormat("View Difference {0}", thisRow["name"].ToString());
builder.AppendLine();
continue;
}
var this_ = thisReader.ReadToEnd();
var other_ = otherReader.ReadToEnd();
if (this_ != other_)
{
builder.AppendFormat("View Difference {0}", thisRow["name"].ToString());
builder.AppendLine();
continue;
}
//if (thisRow["definition"].ToString().Trim() != otherRow["definition"].ToString().Trim())
//{
// builder.AppendFormat("View Difference {0}", thisRow["name"].ToString());
// builder.AppendLine();
//}
}
helper.Close();
otherHelper.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
