/// <summary>
/// 做成数据库脚本，并存入剪贴板中
/// </summary>
/// <param name="type">操作类型（增、删、改、查）</param>
/// <param name="hasInput">是否生成输入参数</param>
/// <param name="hasHead">是否生成头</param>
/// <param name="tableName">表名</param>
/// <param name="lst">ListView组件</param>
public static void GenerateStoreProcedureScript(SqlOperateType type, bool hasInput,
bool hasHead, bool cutPrefix, string tableName, ListView lst)
{
if (0 == lst.CheckedItems.Count)
{
return;
}
string script = string.Empty;
string inputHead = string.Empty;
string inputParam = string.Empty;
//生成存储过程名
string spName = string.Empty;
//如果是常见表头，处理掉
string spNameTopTwo = string.Empty;
spNameTopTwo = tableName.Substring(0, 2);
spName = cutPrefix ? CutPrefix(tableName) : tableName;
switch (type)
{
case SqlOperateType.Select:
spName = "Get" + spName + "Data";
break;
case SqlOperateType.Update:
spName = "Update" + spName;
break;
case SqlOperateType.Insert:
spName = "Add" + spName;
break;
case SqlOperateType.Delete:
spName = "Delete" + spName;
break;
default:
break;
}
//生成函数头
GenerateTableScriptInput(lst, out inputHead, out inputParam);
if (hasHead)
{
if (!hasInput || type == SqlOperateType.Delete || type == SqlOperateType.Select)
{
inputHead = string.Empty;
}
script = GenerateTableScriptHead(spName, type, inputHead);
}
//生成函数主体
script += GenerateTableScriptBody(inputParam, spName, tableName, hasInput, type);
//存入剪贴板
Clipboard.Clear();
Clipboard.SetText(script);
}
/// <summary>
/// 生成脚本头注释
/// </summary>
/// <param name="spName">存储过程名</param>
/// <param name="type">数据库操作类型</param>
/// <param name="input">头注释输入参数列</param>
/// <returns>脚本头注释</returns>
private static string GenerateTableScriptHead(string spName, SqlOperateType type, string input)
{
string result = string.Empty;
result = string.Format(Resources.Template_StoreProcedureHeader,
spName, //存储过程名
string.Empty, //说明
string.Empty, //返回值
input.Length > 0 ? input : "**", //输入
"**", //输出
"高云鹏", //作成者
DateTime.Now.ToString("yyyy/MM/dd")
);
return result;
}
/// <summary>
/// 生成脚本主体
/// </summary>
/// <param name="inputParam">函数输入参数列</param>
/// <param name="spName">存储过程名</param>
/// <param name="tableName">表名</param>
/// <param name="hasInput">是否生成输入参数</param>
/// <param name="type">数据库操作类型</param>
/// <returns>脚本主体</returns>
private static string GenerateTableScriptBody(string inputParam, string spName, string tableName, bool hasInput, SqlOperateType type)
{
string result = string.Empty;
string param = string.Empty;
string insertParam = string.Empty;
result = "\r\nCREATE PROCEDURE " + spName;
if (hasInput && type != SqlOperateType.Delete && type != SqlOperateType.Select)
{
result += "(\r\n" + inputParam + "\r\n)";
}
result += "\r\nAS\r\nBEGIN\r\n" + Constant.TAB + "SET NOCOUNT ON;\r\n";
StringBuilder paramBuilder = new StringBuilder();
StringBuilder insertParamBuilder = new StringBuilder();
foreach (string aParam in Command.paramsList)
{
switch (type)
{
case SqlOperateType.Select:
paramBuilder.AppendLine(string.Format("{0}{0}{1},", Constant.TAB, aParam));
break;
case SqlOperateType.Update:
paramBuilder.AppendLine(string.Format("{0}{0}{1,-" + maxParamLength + "} = @{1},", Constant.TAB, aParam));
break;
case SqlOperateType.Insert:
paramBuilder.AppendLine(string.Format("{0}{0}{1},", Constant.TAB, aParam));
insertParamBuilder.AppendLine(string.Format("{0}{0}@{1},", Constant.TAB, aParam));
break;
default:
break;
}
}
param = paramBuilder.ToString().Trim().TrimEnd(',');
insertParam = insertParamBuilder.ToString().Trim().TrimEnd(',');
switch (type)
{
case SqlOperateType.Select:
result += Constant.TAB + "SELECT " + param + "\r\n" + Constant.TAB + "FROM " + tableName + "\r\n";
break;
case SqlOperateType.Update:
result += Constant.TAB + "UPDATE " + tableName + " SET\r\n" + Constant.TAB + Constant.TAB + param + "\r\n";
break;
case SqlOperateType.Insert:
result += Constant.TAB + "INSERT INTO " + tableName + "(\r\n" + Constant.TAB + Constant.TAB + param +
"\r\n" + Constant.TAB + ")VALUES(\r\n" + Constant.TAB + Constant.TAB +
insertParam + "\r\n" + Constant.TAB + ")\r\n";
break;
case SqlOperateType.Delete:
result += Constant.TAB + "DELETE FROM " + tableName + "\r\n";
break;
default:
break;
}
result += "END";
return result;
}
