INSERT\UPDATE Query Generator Based on Table Values

• Download source files - 12.28 KB

Introduction

This tool would help anyone to create insert\update query based on values in a table, without any data or with custom data.

Background

It has always been annoying for me to write insert\ update queries for a table having a lot of columns. It sometimes looks tiresome to copy and paste all the columns. Moreover, recently I came across an issue in an project where I added some values into a setting table and then needed to replicate the same at the client end. As I manually updated SQL table, there does not seem any easier way to replicate that at the client end without accessing their SQL Server box or using DTS.

Solution

To solve these issues, I developed this tool and hope it would help the person who has come across the same problem. Moreover, I think this is an ideal tool to increase your productivity when you are creating lots of insert\update queries either in stored procedure or application.

When you start this tool, you would be required to connect to a SQL Server. On being successfully connected, it will display a list of table and views in the dropdown table.The code to access list of table and view is:

string sql = "SELECT TABLE_NAME as Name, TABLE_NAME as Value _
	FROM Information_Schema.Tables";
sql += "  WHERE TABLE_TYPE IN ('BASE TABLE', 'VIEW') ORDER BY TABLE_TYPE, TABLE_NAME";
SqlDataAdapter da = new SqlDataAdapter(sql, _connection);
DataTable dt = new DataTable();
da.Fill(dt)

Once a user selects a table or view, the list of columns belonging to that table\view are displayed in a panel. These columns are displayed using checkbox with AutoScroll property of panel set to True for making it scrollable. The checkbox would help the user to select what column she/he wants in insert\update SQL.

The code to get a list of columns for a table or view is:

string[] restrictions = new string[4] { _database, null, objectName, null };
DataTable dt = _connection.GetSchema("Columns", restrictions);
return dt;
Where _database is your database name and ObjectName is name of tabel\view

You could filter the data from select query using filter text box. Once, you have done so, clicking on QUERY button would populate the grid with the columns selected in the panel.

Now, if you want an empty template of insert\update query, don't select any row and click on GENERATE buttons.

This would open another form with the generated query and also save the text into clipboard.
To create an insert\update query based on existing value in table, select the corresponding row and click GENERATE button.

You could also change any cell value(locally) and generate insert\update query with custom data. This tool caters to the proper datatype and appends single quotes to text columns. I have added the column type to the column header (for quicker development work) and extract them when creating queries.

Note: I have developed this in a couple of hours and have not tested it thoroughly, so if anyone finds a bug, please report it to me and I would be more than happy to solve that.

This article, along with any associated source code and files, is licensed under The GNU General Public License (GPLv3)