/// <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());
}
public JsonResult GetSpNeedParameters(string dbName, string spName)
{
// *** 步驟(1) 建立SQL Server 的 Adapter
IDbEngineAdapter db = DbEngineAdapter.CreateSQLServerDB();
// *** 步驟(3) 執行 SQL 敘述 => 丟入 Sql 或 預存程序名稱 、 CommandType 、 SqlParameter[]
var ds = db.Excute(
$@" SELECT
p.name AS Parameter,
t.name AS Type,
p.is_output AS OutPut,
p.max_length as Size,
p.precision as Precision,
p.scale as Scale
FROM {dbName}.sys.procedures sp
JOIN {dbName}.sys.parameters p
ON sp.object_id = p.object_id
JOIN {dbName}.sys.types t
ON p.system_type_id = t.system_type_id
WHERE sp.name = '{spName}'
ORDER BY p.name
",
CommandType.Text,
null
);
var pList = ds.Tables[0].ToList <ParameterAndType>()
.Where(p => p.Type != "sysname").ToList(); // 不需要sysname
return(Json(pList));
}
/// <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 GetTableViewAndSPList(string dbName)
{
// *** 步驟(1) 建立SQL Server 的 Adapter
DbEngineAdapter db = new DbEngineAdapter(connectString);
// *** 步驟(3) 執行 SQL 敘述 => 丟入 Sql 或 預存程序名稱 、 CommandType 、 SqlParameter[]
var dsTable = db.Excute(
$@"SELECT (TABLE_NAME) AS name FROM
{dbName}.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME",
CommandType.Text,
null
);
db.Open();
var dsView = db.Excute(
$"SELECT (TABLE_NAME) AS name FROM {dbName}.INFORMATION_SCHEMA.VIEWS ORDER BY TABLE_NAME",
CommandType.Text,
null
);
var dsSP = db.Excute(
$@"
SELECT (Routine_Name) as Name
FROM {dbName}.INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE' AND Left(Routine_Name, 3) NOT IN ('sp_', 'xp_', 'ms_')
ORDER BY SPECIFIC_NAME
",
CommandType.Text,
null
);
db.Dispose(); //關閉連線
// *** 步驟(4) 轉成List
var viewList = dsView.Tables[0].ToList <Names>();
var spList = dsSP.Tables[0].ToList <Names>();
var tableList = dsTable.Tables[0].ToList <Names>();
return(Json(new ViewAndSP()
{
Tables = tableList,
Views = viewList,
SPs = spList
}));
}
static void TestDataReader(string connstr, string driver)
{
DbEngineAdapter db =
new DbEngineAdapter(connstr, driver);
string query = "select * from logs";
if (db.Open())
{
IDataReader reader = db.ExecuteQuery(query);
while (reader.Read())
{
Console.WriteLine(reader.GetString(0));
}
}
db.Close();
}
public JsonResult GetDataBaseList()
{
// *** 步驟(1) 建立SQL Server 的 Adapter
IDbEngineAdapter db = DbEngineAdapter.CreateSQLServerDB();
// *** 步驟(3) 執行 SQL 敘述 => 丟入 Sql 或 預存程序名稱 、 CommandType 、 SqlParameter[]
var ds = db.Excute(
"SELECT name FROM master.dbo.sysdatabases WHERE dbid > 4 ORDER BY name",
CommandType.Text,
null
);
var nameList = ds.Tables[0].ToList <Names>();
return(Json(nameList));
}
static void TestInsert(string connstr, string driver)
{
DbEngineAdapter db =
new DbEngineAdapter(connstr,
driver);
//----- a Test Log Entry
string test = "Log value is " + Math.PI * 1999;
if (db.Open())
{
string query = "INSERT INTO logs VALUES('" +
test + "');";
bool result = db.ExecuteNonQuery(query);
}
db.Close();
return;
}
public JsonResult GetTableViewAndSPList(string dbName)
{
// *** 步驟(1) 建立SQL Server 的 Adapter
IDbEngineAdapter db = DbEngineAdapter.CreateSQLServerDB();
// *** 步驟(3) 執行 SQL 敘述 => 丟入 Sql 或 預存程序名稱 、 CommandType 、 SqlParameter[]
var dsTable = db.Excute(
$@"SELECT (TABLE_NAME) AS name FROM
{dbName}.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME",
CommandType.Text,
null
);
var dsView = db.Excute(
"SELECT (TABLE_NAME) AS name FROM " + dbName + ".INFORMATION_SCHEMA.VIEWS ORDER BY TABLE_NAME",
CommandType.Text,
null
);
var dsSP = db.Excute(
"SELECT (SPECIFIC_NAME) AS name FROM " + dbName + ".INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = 'PROCEDURE' ORDER BY SPECIFIC_NAME",
CommandType.Text,
null
);
var viewList = dsView.Tables[0].ToList <Names>();
var spList = dsSP.Tables[0].ToList <Names>();
var tableList = dsTable.Tables[0].ToList <Names>();
return(Json(new ViewAndSP()
{
Tables = tableList,
Views = viewList,
SPs = spList
}));
}
public JsonResult GetDataBaseList()
{
// *** 步驟(1) 建立SQL Server 的 Adapter
DbEngineAdapter db = new DbEngineAdapter(connectString);
// *** 步驟(3) 執行 SQL 敘述 => 丟入 Sql 或 預存程序名稱 、 CommandType 、 SqlParameter[]
db.Open();
var ds = db.Excute(
@"
SELECT name FROM master.dbo.sysdatabases
WHERE name not in ('master','tempdb','model','msdb')
", /* 依照系統DB做排除 'master','tempdb','model','msdb' */
CommandType.Text,
null
);
db.Dispose(); //關閉連線
var nameList = ds.Tables[0].ToList <Names>();
return(Json(nameList));
}
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));
}
static void TestDataSet(string connstr, string driver)
{
DbEngineAdapter db =
new DbEngineAdapter(connstr, driver);
if (db.Open())
{
string query = "SELECT * from logs";
DataSet ds = db.Execute(query);
DataTable dt = ds.Tables[0];
int i = 0;
int max = dt.Rows.Count;
while (i < max)
{
DataRow dr = dt.Rows[i];
Console.WriteLine(dr[0]);
i++;
}
}
db.Close();
return;
}
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));
}
