Click here to Skip to main content
15,868,164 members
Articles / Programming Languages / C#

INSERT\UPDATE Query Generator Based on Table Values

Rate me:
Please Sign up or sign in to vote.
3.44/5 (6 votes)
15 Feb 2006GPL32 min read 96.2K   1.2K   36   13
This tool would help anyone to create insert\update query based on values in a table, without any data or with custom data
Sample Image - QueryGenerator.jpg

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:

SQL
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:

SQL
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.

License

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


Written By
Architect
Australia Australia
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Generalwhen downloading source, file size is 0 Pin
Alexis Rzewski31-Aug-09 7:59
Alexis Rzewski31-Aug-09 7:59 
GeneralRe: when downloading source, file size is 0 Pin
S Sansanwal31-Aug-09 13:23
S Sansanwal31-Aug-09 13:23 
Generalsql storedprocedure Pin
murugavelk11-Nov-08 23:00
murugavelk11-Nov-08 23:00 
GeneralIn case identity insert is off Pin
yordan_georgiev21-Sep-08 19:10
yordan_georgiev21-Sep-08 19:10 
QuestionHow to get column info for sql table using sqldmo? Pin
lildiapaz8-Aug-07 9:34
lildiapaz8-Aug-07 9:34 
AnswerRe: How to get column info for sql table using sqldmo? Pin
S Sansanwal8-Aug-07 12:58
S Sansanwal8-Aug-07 12:58 
GeneralRe: How to get column info for sql table using sqldmo? Pin
lildiapaz9-Aug-07 11:13
lildiapaz9-Aug-07 11:13 
Thanks for the quick response,

But the article didn't help me get what I needed. I'm trying to do something similar to you. I want to display column info from an sql table into a checked listbox and display the info in a datagrid.

Here's what I tried:
command.Connection = myConnection;
command.CommandText = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'ADDRESS'";
myReader = command.ExecuteReader();
table = myReader.GetSchemaTable();
//foreach (DataRow row in table.Rows)
string[] restrictions1 = new string[4] { "exampleA", null, null, "ADDRESS" };
foreach (DataColumn col in table.Columns)
{
this.checkedListBox1.Items.Add(myReader.GetSchemaTable().Columns.ToString());
}

I don't understand why this doesn't work. I also tried your method of table = myConnection.GetSchema("Columns", restrictions1); this method doesn't work either.

Can you please help? This is very important

Thanks
GeneralOracle compatibility Pin
Deepak Srivathsan23-Jul-07 3:25
Deepak Srivathsan23-Jul-07 3:25 
GeneralRe: Oracle compatibility Pin
S Sansanwal23-Jul-07 12:53
S Sansanwal23-Jul-07 12:53 
GeneralRe: Oracle compatibility Pin
Deepak Srivathsan24-Jul-07 12:06
Deepak Srivathsan24-Jul-07 12:06 
GeneralSuggestion Pin
neil young7-Feb-06 21:47
neil young7-Feb-06 21:47 
GeneralCool start Pin
Tom Pester2-Feb-06 11:32
Tom Pester2-Feb-06 11:32 
GeneralRe: Cool start Pin
S Sansanwal2-Feb-06 19:46
S Sansanwal2-Feb-06 19:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.