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;
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 <> 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 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();
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 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)
{
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)