Click here to Skip to main content
Click here to Skip to main content
Go to top

Sql Script Generator in C# From SQL Table ,get the SQLTable name And Generate The Insert and Update Script

, 11 Oct 2013
Rate this:
Please Sign up or sign in to vote.
Sql Script Generator in C# From SQL Table ,get the SQLTable name And Generate The Insert and Update Script

Editorial Note

This articles was originally at wiki.asp.net but has now been given a new home on CodeProject. Editing rights for this article has been set at Bronze or above, so please go in and edit and update this article to keep it fresh and relevant.

Introduction

SQL Script Generator

Generator SQL Script Generator Generates the SQL Scripts to run on sql query analyzer these queries of only insert and update.

Background

SQL Script Generator in C# From SQL, MS Access, FoxPro, MySql etc Table Generator SQL Script Generator Generates the SQL Scripts to run on SQL query analyzer these queries of only insert and update this class still not contain Delete query but u can change according to ur requirement this contain methods only for sql still not for others u can change it this also contain a method from which u can find ID or Primary Key Name of a table method name is GetprimaryKey(string tableName)

Note

"sp_pkeys" is sql server default store procedure u just pass it only table Name it will return primary key column

Using the Code

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Windows;
using System.Windows.Forms;
using System.Collections;
//using Microsoft.SqlServer.Management.Smo;
//using Microsoft.SqlServer.Management.Nmo;
//using Microsoft.SqlServer.Management.Smo.Agent;
namespace FoxPro_2_SQL_Synchroniser
{
class SQLScriptGenerater
{
//This class generates the SQL Scripts of of a table from SQL Table
// these are globle variable
//DesTableName this is destination table name for which script will be //generated like qry= insert into //DesTableName(id,name)value('1','idrees')
// I have destination table name as "Tableee"
//source table name is TableName name as " Tab "; this of sql //Table
string primaryKey, updateqry, Insertqry,DesTableName="Tableee", TableName = "Tab";
string updateAdd = "";
public SQLScriptGenerater()
{
}
public void Run(string cnString)
{
string values, IDValues = "", insqry, upqry ;
int i = 0;
SqlDataReader myReader;
SqlConnection mySqlConnection = new SqlConnection();
SqlConnection mSqlConnection = new SqlConnection();
SqlCommand mySqlCommand = new SqlCommand();
SqlCommand msqlCommand = new SqlCommand();
string cnnString = " cnnString ";
mSqlConnection = new SqlConnection(cnnString);
mySqlConnection = new SqlConnection(cnnString);
mySqlCommand = new SqlCommand("select * from Tab", mySqlConnection);
TableName = "Tab";
primaryKey = GetprimaryKey(TableName);
insqry = "";
mySqlConnection.Open();
myReader = mySqlCommand.ExecuteReader();
if (myReader != null)
{
while (myReader.Read())
{
// i this variable count the total number of record
i = i + 1;
// once a query is written at next it a vriable should be empty to build query again
updateAdd = "";
insqry = "";
string celldata = "", coulmenName = "";
for (int j = 0; j <> 0)
{
// this condition is used for "," in insert and update qyery
{
coulmenName += "," + myReader.GetName(j).ToString();
celldata += ",'" + myReader[j].ToString() + "'";
}
}
else
{
coulmenName += myReader.GetName(j).ToString();
celldata += "'" + myReader[j].ToString() + "'";
}
if (primaryKey == myReader.GetName(j).ToString())
{
IDValues = myReader[j].ToString();
}
if (IDValues != null)
{
//Generates the update Query
upqry = UpdateQuery(coulmenName, celldata, primaryKey, IDValues);
updateAdd += upqry;
//Generates the Insert Query
insqry = InsertQuery(coulmenName, celldata, DesTableName);
}
}
WriteScripts(DesTableName, insqry, updateAdd, IDValues, primaryKey, i);
}
MessageBox.Show("Total number of record in database are=" + i);
}
}
#region this Methods retun ID columan of table which table we pass to
public string GetprimaryKey(string tableName ,string cnnString)
{
string names, ID = "";
SqlDataReader mReader;
SqlConnection mSqlConnection = new SqlConnection();
SqlCommand mSqlCommand = new SqlCommand();
string cnString = cnString
mSqlConnection = new SqlConnection(cnString);
mSqlConnection.Open();
// sp_pkeys is sql server default store procedure u just pass it only table Name it will return //primary key column
mSqlCommand = new SqlCommand("sp_pkeys", mSqlConnection);
mSqlCommand.CommandType = CommandType.StoredProcedure;
mSqlCommand.Parameters.Add("@table_name", SqlDbType.NVarChar).Value = tableName;
mReader = mSqlCommand.ExecuteReader();
while (mReader.Read())
{
// the primary key column reside at 4 index
ID = mReader[3].ToString();
}
return ID;
}
#endregion
#region this methods retun ID values to compaire for insert or Update
public void WriteScripts(string tableName, string insertqry, string updateqry, string IDvalues, string PrimaryKey, int i)
{
string script = "";
updateqry = "update " + DesTableName + " set " + updateqry + " Where " + PrimaryKey + " = '" + IDvalues + "'";
int index = updateqry.LastIndexOf(",");
string updatqry = updateqry.Remove(index, 1);
if (i == 1)
{
//if will be first time executed and all required variable are declared and next all times else //condition will be executed
script += "DECLARE @updateCount INT;"+Environment.NewLine;
script += "DECLARE @insertCount INT;"+ Environment.NewLine;
script += "DECLARE @count INT;"+Environment.NewLine;
script += "SET @updateCount = 0;"+Environment.NewLine;
script += "SET @insertCount = 0;"+Environment.NewLine;
script += "SELECT @count = COUNT(*) FROM [" + tableName + "] WHERE [" + PrimaryKey + "] = '" + IDvalues + "'" + Environment.NewLine;
script += "IF @count = 0" + Environment.NewLine;
script += "BEGIN ";
script += insertqry + "" + Environment.NewLine;
script += "SET @insertCount = @insertCount + 1 " + Environment.NewLine;
script += "END" + Environment.NewLine;
script += "ELSE" + Environment.NewLine;
script += "BEGIN " + Environment.NewLine;
script += updatqry + "" + Environment.NewLine;
script += "SET @updateCount = @updateCount + 1 " + Environment.NewLine;
script += "END" + Environment.NewLine;
StreamWriter sw = new StreamWriter(@"d:\script1.txt", true,Encoding.UTF8);
sw.Write(script);
sw.Close();
}
else
{
script += "SELECT @count = COUNT(*) FROM [" + tableName + "] WHERE [" + PrimaryKey + "] = '" + IDvalues + "'" + Environment.NewLine;
script += "IF @count = 0" + Environment.NewLine;
script += "BEGIN " + Environment.NewLine;
script += insertqry + "" + Environment.NewLine;
script += "SET @insertCount = @insertCount + 1 " + Environment.NewLine;
script += "END" + Environment.NewLine;
script += "ELSE" + Environment.NewLine;
script += "BEGIN " + Environment.NewLine;
script += updatqry + "" + Environment.NewLine;
script += "SET @updateCount = @updateCount + 1 " + Environment.NewLine;
script += "END" + Environment.NewLine;
StreamWriter sw = new StreamWriter(@"d:\script1.txt", true, Encoding.UTF8);
sw.Write(script);
sw.Close();
}
}
#endregion
#region this methods retun insert query and update query
public string InsertQuery(string coulmenName, string celldata,string TableName)
{
return Insertqry = "insert into " + TableName + "(" + coulmenName + ")values(" + celldata + ")";
}
public string UpdateQuery(string coulmenName, string celldata, string Name, string Value)
{
string IDName, IDValue, Ud = "", name = "", values = "";
IDName = Name;
IDValue = Value;
if (IDName != null)
{
int indexcolumn = coulmenName.LastIndexOf(",");
int indexValues = celldata.LastIndexOf(",");
if (indexcolumn > 0 && indexValues > 0)
{
coulmenName = coulmenName.Substring(indexcolumn);
celldata = celldata.Substring(indexValues);
name = coulmenName.Replace(",", "");
values = celldata.Replace(",", "");
if (name != IDName && values != IDValue)
{
Ud = name + "=" + values + ",";
}
}
else
{
name = coulmenName;
values = celldata;
if (name != IDName && values != IDValue)
{
Ud = name + "=" + values + ",";
}
}
}
return Ud;
}
#endregion
}
}

This also contain a method from which u can find ID or Primary Key Name of a table method name is GetprimaryKey(string tableName)

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

ASP.NET Community

United States United States
The ASP.NET Wiki was started by Scott Hanselman in February of 2008. The idea is that folks spend a lot of time trolling the blogs, googlinglive-searching for answers to common "How To" questions. There's piles of fantastic community-created and MSFT-created content out there, but if it's not found by a search engine and the right combination of keywords, it's often lost.
 
The ASP.NET Wiki articles moved to CodeProject in October 2013 and will live on, loved, protected and updated by the community.
Group type: Collaborative Group

247 members


Comments and Discussions

 
GeneralMy vote of 1 PinmemberAugustin Juričić29-Oct-13 2:38 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 11 Oct 2013
Article Copyright 2013 by ASP.NET Community
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid