/// <summary>
/// (펑션)연결된 서버를 테스트 한다. 실패 시 exception 발생
/// </summary>
/// <param name="conn"></param>
/// <param name="linkedServerName"></param>
public static void fnc_LinkedServer_Test(MsSQL.strConnect conn, string linkedServerName)
{
MsSQL sql = new MsSQL(conn);
string qry = $"exec sp_testlinkedserver {linkedServerName}";
sql.Excute_Query(qry, "", false);
}
/// <summary>
/// (펑션)DataBase 삭제 한다.
/// </summary>
/// <param name="conn"></param>
/// <param name="dbName"></param>
public static void fnc_DataBase_drop(MsSQL.strConnect conn, string dbName)
{
MsSQL sql = new MsSQL(conn);
string qry = $@"DROP database {dbName}";
sql.Excute_Query(qry, "");
}
/// <summary>
/// (펑션)연결된 서버 목록을 조회 한다.
/// </summary>
/// <param name="conn"></param>
/// <returns></returns>
public static DataTable fnc_LinkedServers(MsSQL.strConnect conn)
{
MsSQL sql = new MsSQL(conn);
string qry = "sp_linkedservers";
return(sql.Excute_StoredProcedure(qry, new SqlParameter[] { }, string.Empty, false).Tables[0]);
}
/// <summary>
/// (펑션)프로시져를 삭제 한다.
/// </summary>
/// <param name="conn"></param>
/// <param name="SPName"></param>
public static void fnc_Procedure_Drop(MsSQL.strConnect conn, string SPName)
{
MsSQL sql = new MsSQL(conn);
string qry = string.Format(@"IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{0}') AND type in (N'P', N'PC'))
DROP PROCEDURE {0}", SPName);
sql.Excute_Query(qry, "", false);
}
/// <summary>
/// (펑션)연결된 서버를 삭제 한다.
/// </summary>
/// <param name="conn"></param>
/// <param name="linkedServerName"></param>
public static void fnc_LinkedServer_Drop(MsSQL.strConnect conn, string linkedServerName)
{
MsSQL sql = new MsSQL(conn);
string qry = string.Format("IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'{0}')EXEC master.dbo.sp_dropserver @server=N'{0}', @droplogins='droplogins'",
linkedServerName);
sql.Excute_Query(qry, "", false);
}
/// <summary>
/// (펑션)sql db연결 체크용 시간조회
/// </summary>
/// <returns></returns>
public static DateTime fnc_date_get(MsSQL.strConnect conn)
{
MsSQL sql = new MsSQL(conn);
string qry = string.Format("SELECT GETDATE()");
DataTable dt = sql.Excute_Query(qry, "").Tables[0];
return((DateTime)dt.Rows[0][0]);
}
public static DataTable SPListGet(strConnect conn)
{
string qry = "SELECT name FROM SYS.procedures order by name";
MsSQL sql = new MsSQL(conn);
using (DataSet ds = sql.Excute_Query(qry, ""))
{
return(ds.Tables[0]);
}
}
/// <summary>
/// (펑션)DataBase 존재 여부를 확인한다.
/// </summary>
/// <param name="conn"></param>
/// <param name="dbName"></param>
/// <returns></returns>
public static bool fnc_DataBase_Exists(MsSQL.strConnect conn, string dbName)
{
MsSQL sql = new MsSQL(conn);
string qry = $@"SELECT *
FROM model.[sys].[databases]
WHERE 1=1
AND NAME = '{dbName}'";
DataTable dt = sql.Excute_Query(qry, "").Tables[0];
return(dt.Rows.Count > 0);
}
public static DataTable DBListGet(strConnect conn)
{
string qry = "SELECT NAME FROM SYS.databases ORDER BY NAME";
MsSQL sql = new MsSQL(conn);
sql.retryTimes_Connecting = 1;
sql.RetryTimes_Query = 1;
using (DataSet ds = sql.Excute_Query(qry, ""))
{
return(ds.Tables[0]);
}
}
/// <summary>
/// (펑션)기본(master) 파일 경로를 조회 한다.
/// </summary>
/// <param name="conn"></param>
/// <returns></returns>
public static string fnc_filepath_get(MsSQL.strConnect conn)
{
MsSQL sql = new MsSQL(conn);
string qry = @"select filename
from [master].[dbo].sysfiles
where fileid = 1";
DataTable dt = sql.Excute_Query(qry, "").Tables[0];
string[] path = Function.system.clsFile.FileSplitPath_Name(Fnc.obj2String(dt.Rows[0]["filename"]));
return(path[0]);
}
public static DataTable SPInfoGet(strConnect conn, string spname)
{
string qry = string.Format(@"
SELECT OBJECT_ID, P.name, parameter_id, P.system_type_id, T.name datatype
FROM SYS.all_parameters P
LEFT OUTER JOIN SYS.types T ON P.system_type_id = T.system_type_id AND P.USER_type_id = T.USER_type_id
where object_id =
(SELECT object_id FROM SYS.procedures WHERE name = '{0}')
order by parameter_id", spname);
MsSQL sql = new MsSQL(conn);
using (DataSet ds = sql.Excute_Query(qry, ""))
{
return(ds.Tables[0]);
}
}
public static DataTable TableInfoGet(strConnect conn, string tablename)
{
string qry = string.Format(@"
SELECT OBJECT_ID, P.name, column_id, P.system_type_id, T.name datatype
FROM SYS.columns p
LEFT OUTER JOIN SYS.types T ON P.system_type_id = T.system_type_id AND P.USER_type_id = T.USER_type_id
where object_id =
(SELECT object_id FROM SYS.tables WHERE name = '{0}')
ORDER BY column_id", tablename);
MsSQL sql = new MsSQL(conn);
using (DataSet ds = sql.Excute_Query(qry, ""))
{
return(ds.Tables[0]);
}
}