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 analizere 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)
