SQL Server batch data replacement assistant V1.0 released

This method is cumbersome, and it is difficult for people who do not know the database. I was tempted to write a small program. After two days of hard work, I finally met you. I hope you will give more comments to your children's shoes. After that, let's go to the interface first, ^... ^

Now let's talk about the development idea of this small program.
Step 1: Use the sp_helpdb system stored procedure to obtain the names of all databases in SqlServer.
Copy codeThe Code is as follows:
# Region test database connection and display the Database List
/// <Summary>
/// Test the database connection and display the Database List
/// </Summary>
/// <Param name = "sender"> </param>
/// <Param name = "e"> </param>
Private void btnTest_Click (object sender, EventArgs e)
{
This. btnTest. Enabled = false;
SaveConfig ();

ConfigInfo. Server = this.txt IP. Text. Trim ();
ConfigInfo. DataBase = "master ";
ConfigInfo. UID = this.txt UID. Text. Trim ();
ConfigInfo. Pwd = this.txt Pwd. Text. Trim ();

Try
{
DataTable dt = Data. SqlHelper. ExecuteDataset (ConfigInfo. getConnect (), CommandType. Text, "sp_helpdb"). Tables [0];

This. cmbDataBaseList. DataSource = dt;
This. cmbDataBaseList. DisplayMember = "name ";
This. cmbDataBaseList. SelectedIndex = 0;
This. cmbDataBaseList. DropDownStyle = ComboBoxStyle. DropDownList;

This. ExecuteFilterBtn. Enabled = true;
}
Catch (Exception ex)
{
This. ExecuteFilterBtn. Enabled = false;
MessageBox. Show (string. Format ("error: {0 }! ", Ex. Message)," Error prompt ", MessageBoxButtons. OK, MessageBoxIcon. Error );
}
Finally
{
This. btnTest. Enabled = true;
}
}
# Endregion


Step 2: When you select a database, you can obtain all the table information in the database. You can use the following SQL statement to query the information.
Select [name] from sysobjects where xtype = 'U' order by [name] asc

Copy codeThe Code is as follows:
# Region when selecting a different database, read the table information of the database
/// <Summary>
/// Read the table information of the database when different databases are selected
/// </Summary>
/// <Param name = "sender"> </param>
/// <Param name = "e"> </param>
Private void combobox#selectedindexchanged (object sender, EventArgs e)
{
This. chkboxTableList. Items. Clear ();
ConfigInfo. DataBase = (DataRowView) this. cmbDataBaseList. SelectedItem) ["name"]. ToString ();
DataSet ds = Data. sqlHelper. executeDataset (ConfigInfo. getConnect (), CommandType. text, "select [name] from sysobjects where xtype = 'U' order by [name] asc ");

Foreach (DataRow row in ds. Tables [0]. Rows)
{
This. chkboxTableList. Items. Add (row ["name"]. ToString ());
}
}
# Endregion


Step 3: Click the replace button to obtain the information of the selected table, traverse the row and column information in the table, and search and replace the information.

Copy codeThe Code is as follows:
# Region performing batch replacement
/// <Summary>
/// Perform the batch Replacement Operation
/// </Summary>
/// <Param name = "sender"> </param>
/// <Param name = "e"> </param>
Private void ExecuteFilterBtn_Click (object sender, EventArgs e)
{
SaveConfig ();
Total = 0;
If (this. chkboxTableList. CheckedIndices. Count = 0) return; // No table is selected
If (this.txt SearchKey. Text. Trim () = "")
{
DialogResult result = MessageBox. Show ("the current search content is empty. Are you sure you want to perform this operation? "," Prompt ", MessageBoxButtons. YesNo, MessageBoxIcon. Question, MessageBoxDefaultButton. Button1 );
If (result = DialogResult. No) return;
}

This. ExecuteFilterBtn. Enabled = false;

List <TableInfo> tabList = new List <TableInfo> ();
String searchString = this.txt SearchKey. Text. Trim () = ""? "": This.txt SearchKey. Text;
String replaceString = this.txt ReplaceStr. Text;
KeyType kt = this. chkIsRegex. Checked = true? KeyType. Regex: KeyType. Text;
Bool isRegex = this. chkIsRegex. Checked;

// Obtain the basic information of the selected table and add it to the set
Foreach (int index in this. chkboxTableList. CheckedIndices)
{
String tabName = this. chkboxTableList. Items [index]. ToString ();
TableInfo tInfo = FilterInfo. initTableInfo (tabName );
If (tInfo = null)
{
Continue;
}
TabList. Add (tInfo );
}

Try
{
If (tabList. Count = 0) return; // No data table that meets the detection Condition

PBar1.Visible = true;
PBar1.Minimum = 1;
PBar1.Maximum = tabList. Count;
PBar1.Value = 1;
PBar1.Step = 1;

// The data to be replaced in the cyclic filtering table
Foreach (TableInfo info in tabList)
{
FilterInfo. Execute (info, searchString, replaceString, kt );
Pbar1.20.mstep (); // progress bar
}
}
Catch (Exception ex)
{
MessageBox. Show (string. Format ("exception: {0}", ex. Message), "Error", MessageBoxButtons. OK, MessageBoxIcon. Error );
Return;
}
Finally
{
This. ExecuteFilterBtn. Enabled = true;
}

MessageBox. Show (string. Format ("data has been replaced. A total of {0} rows of data have been modified! ", Total)," message ", MessageBoxButtons. OK, MessageBoxIcon. Information );
}
# Endregion

The above is the general idea. For details, refer to the source code.

With some operations, I hope you can see more clearly.

This is the injected data. Of course, there are actually differences.

Write the search content and enable the regular expression matching function.

Haha, the data has finally recovered !!
Source program