C# (CSharp) Function.Db MsSQL.strConnect Exemples

Exemple #1

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

Exemple #2

        /// <summary>
        /// (펑션)연결된 서버 이름으로 존재여부를 확인한다.
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="linkedServerName"></param>
        /// <returns></returns>
        public static bool fnc_LinkedServer_Exist(MsSQL.strConnect conn, string linkedServerName)
        {
            DataTable dt = fnc_LinkedServers(conn);

            DataRow[] rows = dt.Select($"SRV_NAME = '{linkedServerName}'");

            return(rows.Length > 0);
        }

Exemple #3

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

Exemple #4

        /// <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, "");
        }

Exemple #5

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

Exemple #6

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

Exemple #7

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

Exemple #8

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

Exemple #9

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