C# 从 SQL 表生成 SQL 脚本，获取 SQL 表名并生成插入和更新脚本

引言

SQL 脚本生成器

生成器 SQL 脚本生成器生成要在 SQL 查询分析器上运行的 SQL 脚本，这些查询仅用于插入和更新。

背景

C# 从 SQL、MS Access、FoxPro、MySql 等表生成 SQL 脚本生成器。SQL 脚本生成器生成要在 SQL 查询分析器上运行的 SQL 脚本，这些查询仅用于插入和更新。这个类目前还不包含删除查询，但您可以根据您的需求进行修改。它仅包含 SQL 的方法，尚未包含其他数据库的方法，您可以进行修改。它还包含一个方法，可以用来查找表的主键或 ID 名称，方法名称是 GetprimaryKey(string tableName)

注意

“sp_pkeys”是 SQL Server 默认存储过程，您只需传递表名，它将返回主键列。

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
}
}

它还包含一个方法，可以用来查找表的主键或 ID 名称，方法名称是 GetprimaryKey(string tableName)