Introduction
SQL Script Generator generates the SQL Scripts to run on SQL query to analyze only insert and update queries.
Background
SQL Script Generator in C# from SQL, Microsoft Access, FoxPro, MySql, etc.
Table Generator SQL Script Generator generates the SQL Scripts to run on SQL query analyzes only insert and update queries. This class still does not contain the Delete query but you can change it according to your requirement.
This only contains methods for SQL. It does not contain methods for others, you can change it. This also contains a method from which you can find ID or Primary Key Name of a table. The method name is:
GetprimaryKey(string tableName)
NOTE
"sp_pkeys
" is the SQL server default stored procedure. You pass it only the table Name and it will return the 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;
namespace FoxPro_2_SQL_Synchroniser
{
class SQLScriptGenerater
{
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 = i + 1;
updateAdd = "";
insqry = "";
string celldata = "", coulmenName = "";
for(int j = 0; j < myReader.FieldCount; j++)
{
if (j > 0)
{
{
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)
{
upqry = UpdateQuery(coulmenName, celldata,
primaryKey, IDValues);
updateAdd += upqry;
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 return ID column 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();
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())
{
ID = mReader[3].ToString();
}
return ID;
}
#endregion
#region this methods return ID values to compare 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)
{
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 return 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
}
}
History
- 8th September, 2007: Initial post