存储过程生成器
此软件为选定的表生成插入、更新、删除和选择存储过程（根据您的需要）。
引言
存储过程生成器是一个强大的工具，可以为特定数据库的选定表生成Insert、Update、Delete和Select存储过程（根据您的需要）。您可以连接到任何 SQL Server 数据库服务器。
描述
从组合框中选择您的数据库服务器。如果找不到所需的服务器，请手动输入。 选中单选按钮以指定数据库服务器是使用 Windows 身份验证还是 SQL Server 身份验证。 如果是 SQL Server 身份验证，请输入用户名和密码。 您将能够看到所选数据库服务器上的所有数据库。 选择所需的数据库。 您可以通过单击测试连接来测试您的连接。 单击“确定”按钮。 您将能够在网格中看到所选数据库的所有表。 选中您想要生成存储过程的表。 如果您希望为所有表生成，请选中表名上方的复选框（在名称旁边）。 选择要保存脚本文件的文件夹路径。 系统将为脚本文件指定名称（StoredProcedures.sql）。
选择您想要的存储过程类型（Insert、Update等）。 单击“确定”按钮以在选定路径上生成脚本文件。 系统将根据以下格式为存储过程命名tablename_type（其中type是 Insert、Update 等）。 例如 Person_Insert。
Using the Code
以下代码生成创建存储过程的脚本文件
private void GenerateStoredProcedures()
{
string sp = "";
if (tablesDataGridView.Rows[0].Cells["Catalog"] != null)
{
sp = "USE " + tablesDataGridView.Rows[0].Cells["Catalog"].Value.ToString() +
"\n";
sp += "GO \n";
}
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
foreach (string item in spCheckedListBox.CheckedItems)
{
switch (item)
{
case "Insert":
sp+=GenerateInsertStoredProcedure();
break;
case "Update":
sp+=GenerateUpdateStoredProcedure();
break;
case "Delete":
sp+=GenerateDeleteStoredProcedure();
break;
case "Select":
sp+=GenerateSelectStoredProcedure();
break;
}
}
connection.Close();
StreamWriter writer = new StreamWriter(filePathTextBox.Text +
"\\StoredProcedures.sql");
writer.Write(sp);
writer.Close();
}
private string GenerateInsertStoredProcedure()
{
string sp = "";
foreach (DataGridViewRow tableRow in tablesDataGridView.Rows)
{
if (tableRow.Cells["IsSelected"].Value != null)
{
if ((bool)tableRow.Cells["IsSelected"].Value == true)
{
SqlCommand command = new SqlCommand("sp_columns", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@table_name", SqlDbType.NVarChar).Value =
tableRow.Cells["TableName"].Value.ToString();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(command);
da.Fill(ds);
string spName = "[" + tableRow.Cells["Schema"].Value.ToString() + "].[" +
tableRow.Cells["TableName"].Value.ToString() + "_Insert]";
sp = sp + "if exists (select * from dbo.sysobjects where id =
object_id(N'" + spName + "') and OBJECTPROPERTY(id,
N'IsProcedure') = 1) DROP PROCEDURE " + spName + "\n";
sp = sp + "GO \n";
sp = sp + "CREATE PROCEDURE " + spName + "\n";
string columnNames = "";
string parameters = "";
foreach (DataRow row in ds.Tables[0].Rows)
{
if (row["TYPE_NAME"].ToString() != "int identity")
{
string nullable = "";
if (row["NULLABLE"].ToString() == "1")
{
nullable = "=null";
}
sp = sp + "@" + row["COLUMN_NAME"].ToString() + " " +
row["TYPE_NAME"].ToString() + nullable + ",";
columnNames = columnNames + "[" + row["COLUMN_NAME"].ToString() +
"],";
parameters = parameters + "@" +
row["COLUMN_NAME"].ToString() + ",";
}
}
sp = sp.Substring(0, sp.Length - 1);
columnNames = columnNames.Substring(0, columnNames.Length - 1);
parameters = parameters.Substring(0, parameters.Length - 1);
sp = sp + "\n AS \n INSERT INTO " +
tableRow.Cells["TableName"].Value.ToString() +
"(" + columnNames + ") VALUES(" + parameters + ")\n";
sp = sp + "GO \n";
}
}
}
return sp;
}
private string GenerateUpdateStoredProcedure()
{
string sp = "";
foreach (DataGridViewRow tableRow in tablesDataGridView.Rows)
{
if (tableRow.Cells["IsSelected"].Value != null)
{
if ((bool)tableRow.Cells["IsSelected"].Value == true)
{
SqlCommand command = new SqlCommand("sp_columns", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@table_name", SqlDbType.NVarChar).Value =
tableRow.Cells["TableName"].Value.ToString();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(command);
da.Fill(ds);
string spName = "[" + tableRow.Cells["Schema"].Value.ToString() + "].[" +
tableRow.Cells["TableName"].Value.ToString() + "_UpdateById]";
sp = sp + "if exists (select * from dbo.sysobjects where id =
object_id(N'" + spName + "') and OBJECTPROPERTY(id,
N'IsProcedure') = 1) DROP PROCEDURE " + spName + "\n";
sp = sp + "GO \n";
sp = sp + "CREATE PROCEDURE " + spName + "\n";
string columnNames = "";
string identityColumn = "";
foreach (DataRow row in ds.Tables[0].Rows)
{
string dataType = row["TYPE_NAME"].ToString();
if (dataType == "int identity")
{
identityColumn = row["COLUMN_NAME"].ToString();
dataType = "int";
}
else
{
columnNames = columnNames + "[" + row["COLUMN_NAME"].ToString() +
"]=@" + row["COLUMN_NAME"].ToString() + ",";
}
string nullable = "";
if (row["NULLABLE"].ToString() == "1")
{
nullable = "=null";
}
sp = sp + "@" + row["COLUMN_NAME"].ToString() + " " + dataType +
nullable + ",";
}
sp = sp.Substring(0, sp.Length - 1);
columnNames = columnNames.Substring(0, columnNames.Length - 1);
sp = sp + "\n AS \n UPDATE " +
tableRow.Cells["TableName"].Value.ToString() + " SET " +
columnNames + " WHERE [" + identityColumn + "]=@" +
identityColumn + "\n";
sp = sp + "GO \n";
}
}
}
return sp;
}
private string GenerateDeleteStoredProcedure()
{
string sp = "";
foreach (DataGridViewRow tableRow in tablesDataGridView.Rows)
{
if (tableRow.Cells["IsSelected"].Value != null)
{
if ((bool)tableRow.Cells["IsSelected"].Value == true)
{
SqlCommand command = new SqlCommand("sp_columns", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@table_name", SqlDbType.NVarChar).Value =
tableRow.Cells["TableName"].Value.ToString();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(command);
da.Fill(ds);
string spName = "[" + tableRow.Cells["Schema"].Value.ToString() + "].[" +
tableRow.Cells["TableName"].Value.ToString() + "_DeleteById]";
sp = sp + "if exists (select * from dbo.sysobjects where id =
object_id(N'" + spName + "') and OBJECTPROPERTY(id,
N'IsProcedure') = 1) DROP PROCEDURE " + spName + "\n";
sp = sp + "GO \n";
sp = sp + "CREATE PROCEDURE " + spName + "\n";
string identityColumn = "";
foreach (DataRow row in ds.Tables[0].Rows)
{
string dataType = row["TYPE_NAME"].ToString();
if (dataType == "int identity")
{
identityColumn = row["COLUMN_NAME"].ToString();
dataType = "int";
sp += "@" + identityColumn + " " + dataType + "\n";
}
}
sp = sp + "\n AS \n DELETE FROM " +
tableRow.Cells["TableName"].Value.ToString() + " WHERE [" +
identityColumn + "]=@" + identityColumn + "\n";
sp = sp + "GO \n";
}
}
}
return sp;
}
private string GenerateSelectStoredProcedure()
{
string sp = "";
foreach (DataGridViewRow tableRow in tablesDataGridView.Rows)
{
if (tableRow.Cells["IsSelected"].Value != null)
{
if ((bool)tableRow.Cells["IsSelected"].Value == true)
{
SqlCommand command = new SqlCommand("sp_columns", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@table_name", SqlDbType.NVarChar).Value =
tableRow.Cells["TableName"].Value.ToString();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(command);
da.Fill(ds);
string spName = "[" + tableRow.Cells["Schema"].Value.ToString() + "].[" +
tableRow.Cells["TableName"].Value.ToString() + "_SelectAll]";
sp = sp + "if exists (select * from dbo.sysobjects where id =
object_id(N'" + spName + "') and OBJECTPROPERTY(id,
N'IsProcedure') = 1) DROP PROCEDURE " + spName + "\n";
sp = sp + "GO \n";
sp = sp + "CREATE PROCEDURE " + spName + "\n";
sp += "AS \n SELECT * FROM " +
tableRow.Cells["TableName"].Value.ToString() + "\n";
sp += "GO \n";
spName = "[" + tableRow.Cells["Schema"].Value.ToString() + "].[" +
tableRow.Cells["TableName"].Value.ToString() + "_SelectById]";
sp = sp + "if exists (select * from dbo.sysobjects where id =
object_id(N'" + spName + "') and OBJECTPROPERTY(id,
N'IsProcedure') = 1) DROP PROCEDURE " + spName + "\n";
sp = sp + "GO \n";
sp = sp + "CREATE PROCEDURE " + spName + "\n";
string identityColumn = "";
foreach (DataRow row in ds.Tables[0].Rows)
{
string dataType = row["TYPE_NAME"].ToString();
if (dataType == "int identity")
{
identityColumn = row["COLUMN_NAME"].ToString();
dataType = "int";
sp += "@" + identityColumn + " " + dataType + "\n";
}
}
sp = sp + "\n AS \n SELECT * FROM " +
tableRow.Cells["TableName"].Value.ToString() +
" WHERE [" + identityColumn + "]=@" + identityColumn + "\n";
sp = sp + "GO \n";
}
}
}
return sp;
}
未来的增强
我计划使其更加通用，以便它可以为其他数据库生成存储过程。