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