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