/// <summary> /// 查詢SP或View的Code /// </summary> /// <param name="Type"> P: StoreProcedure V: View </param> /// <returns></returns> public ActionResult SPOrViewCodes(string dbName, string spOrViewName, string type) { // *** 步驟(1) 建立SQL Server 的 Adapter IDbEngineAdapter db = DbEngineAdapter.CreateSQLServerDB(); // *** 步驟(2-1) 建立SQL ParameterBuilder var builder = new SQLParameterBuilder(); builder.Add_Input_Parameter("@name", spOrViewName, SqlDbType.VarChar); builder.Add_Input_Parameter("@type", type, SqlDbType.VarChar); // *** 步驟(2-2) Add Input 或 Output 參數 SqlParameter[] pArray = builder.ToArray(); // *** 步驟(3) 執行 SQL 敘述 => 丟入 Sql 或 預存程序名稱 、 CommandType 、 SqlParameter[] var ds = db.Excute( $@" SELECT *,definition FROM {dbName}.sys.sql_modules WHERE object_id = ( SELECT object_id FROM {dbName}.sys.objects WHERE type=@type AND name= @name ) ", CommandType.Text, pArray ); string codes = "<span style='color:red'>查無資料</span>"; if (ds != null && ds.Tables[0].Rows.Count > 0) { codes = ds.Tables[0].Rows[0]["definition"].ToString().Replace("\n", "<br />"); } ViewBag.Code = codes; return(View()); }
/// <summary> /// 查詢SP或View的Code /// </summary> /// <param name="Type"> P: StoreProcedure V: View </param> /// <returns></returns> public ActionResult SPOrViewCodes(string dbName, string spOrViewName, char type) { // *** 步驟(1) 建立SQL Server 的 Adapter DbEngineAdapter db = new DbEngineAdapter(connectString); // *** 步驟(2-1) 建立SQL ParameterBuilder var builder = new SQLParameterBuilder(); // *** 步驟(2-2) Add Input 或 Output 參數 builder.Add_Input_Parameter("@name", spOrViewName, SqlDbType.VarChar); builder.Add_Input_Parameter("@type", type, SqlDbType.VarChar); // *** 步驟(2-3) 將builder 轉成 SqlParameter 陣列 SqlParameter[] pArray = builder.ToArray(); // *** 步驟(3) 執行 SQL 敘述 => 丟入 Sql 或 預存程序名稱 、 CommandType 、 SqlParameter[] DataSet ds = null; db.Open(); switch (type) { case 'P': //SP ds = db.Excute( $@" SELECT ROUTINE_DEFINITION AS definition FROM {dbName}.INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_NAME = @name ", CommandType.Text, pArray ); break; case 'V': //SP ds = db.Excute( $@" SELECT VIEW_DEFINITION AS definition FROM {dbName}.INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = @name ", CommandType.Text, pArray ); break; } db.Dispose(); //關閉連線 string codes = "<span style='color:red'>查無資料</span>"; if (ds != null && ds.Tables[0].Rows.Count > 0) { codes = ds.Tables[0].Rows[0]["definition"].ToString().Replace("\n", "<br />"); } ViewBag.Code = codes; return(View()); }
public JsonResult GetSpNeedParameters(string dbName, string spName) { // *** 步驟(1) 建立SQL Server 的 Adapter DbEngineAdapter db = new DbEngineAdapter(connectString); // *** 步驟(2-1) 建立SQL ParameterBuilder var builder = new SQLParameterBuilder(); builder.Add_Input_Parameter("@spName", spName, SqlDbType.VarChar); // *** 步驟(2-2) Add Input 或 Output 參數 SqlParameter[] pArray = builder.ToArray(); // *** 步驟(3) 執行 SQL 敘述 => 丟入 Sql 或 預存程序名稱 、 CommandType 、 SqlParameter[] db.Open(); var ds = db.Excute( $@" SELECT PARAMETER_NAME AS Parameter, DATA_TYPE AS Type, (case when PARAMETER_MODE='IN' then 0 when PARAMETER_MODE='INOUT' then 1 end) AS OutPut, ISNULL(CHARACTER_MAXIMUM_LENGTH,0) AS Size, ISNULL(NUMERIC_PRECISION,0) AS Precision, ISNULL(NUMERIC_SCALE,0) AS Scale FROM {dbName}.INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME = @spName order by OutPut,Parameter ", CommandType.Text, pArray ); db.Dispose(); //關閉連線 // *** 步驟(4) 轉成List var pList = ds.Tables[0].ToList <ParameterAndType>(); return(Json(pList)); }
public ActionResult Result(PostResource resource) { // *** 步驟(1) 建立SQL Server 的 Adapter DbEngineAdapter db = new DbEngineAdapter(connectString, resource.TimeOut); // *** 步驟(2-1) 建立SQL ParameterBuilder var builder = new SQLParameterBuilder(); if (resource.SqlParameterResourceList != null) { // 迴圈 加入SQL參數 foreach (var parameter in resource.SqlParameterResourceList) { string pName = parameter?.Name.FirstOrDefault() == '@' ? parameter?.Name : '@' + parameter?.Name; if (pName == null) { // Model State Set Error ... return(View()); } // *** 步驟(2-2) Add Input 或 Output 參數 if (parameter.Direction.ToLower() == "input") { builder.Add_Input_Parameter(pName, parameter.Value, parameter.SqlDbType); } else if (parameter.Direction.ToLower() == "output") { if (parameter.Scale == 0 && parameter.Precision == 0) { builder.Add_Output_Parameter(pName, parameter.SqlDbType, parameter.Size);// 無精確度 } else { builder.Add_Output_Parameter(pName, parameter.SqlDbType, parameter.Precision, parameter.Scale, parameter.Size);// 有精確度 } } } } // *** 步驟(2-3) 將builder 轉成 SqlParameter 陣列 SqlParameter[] pArrar = builder.ToArray(); // *** 步驟(3) 執行 SQL 敘述 => 丟入 Sql 或 預存程序名稱 、 CommandType 、 SqlParameter[] db.Open(); //開啟連線 var ds = db.Excute( resource.Sql, resource.CommandType, pArrar ); db.Dispose(); //關閉連線 // *** 步驟(4) (非必要) 可將SqlParameter轉Class // 或是DataSet內的Table轉List<> // IList<YourClass> list = bag.DataSet.Tables[0].ToList<YourClass>(); // YourClass para = bag.OutputParameters.ToClass<YourClass>(); // ..... // 先清除舊的 Session Session["tempParameters"] = null; Session["tempTable"] = null; DataTable dt = null; if (ds.Tables.Count > 0) { dt = ds.Tables[0]; } if (pArrar.Any(p => p.Direction == ParameterDirection.Output)) { Session["tempParameters"] = pArrar; } Session["tempTable"] = dt; DataTable tableNew = dt; //DataSet 做分頁動作 if (tableNew != null) { if (tableNew.Rows.Count > 0) { tableNew = dt.AsEnumerable() .Skip(0) .Take(pageRow) .CopyToDataTable(); } ViewBag.TotalPage = (dt.Rows.Count % pageRow == 0) ? (int)(dt.Rows.Count / pageRow) : (int)(dt.Rows.Count / pageRow) + 1; //總頁數 ViewBag.TotalRow = dt.Rows.Count; } return(View(tableNew)); }