spark reads HIVE data through HiveServer2 JDBC through the HiveServer2 JDBC method.

2023-01-23   ES  

In April 2001, I posted a small tool in the blog, which was a auxiliary tool for SQL Server written in ASP.NET. During this period, some people seem to be more interested in that tool, so I can often receive emails that ask for source code. Just, I released my MVC framework again last month, so I planned to use it[My ASP.NET MVC framework]to rewrite this tool,and open source.

Tools:
1. Written in ASP.NET, and usemymvc framework
2. In order to better approach the operation experience of the desktop program, the website is implemented with pure AJAX.
3. The interface uses jQuery Easy UI
4. The syntax of the code uses syntaxhighlightr (JavaScript class library)

Tool positioning:is just auxiliary tools, so the function is limited, but it is necessary to make limited functions as well.

Below will introduce the functions that the tool can complete, andKey implementation code.
Instructions: All the source code of the tool can be downloaded at the end of this article.

Project introduction

The source code structure of the entire tool is as follows:

The

project was created by Visual Studio 2008, including three parts:

1. Webapp: A ASP.NET website, which is the operating part of the tool.
website only contains some HTML, CSS, JavaScript, DLL files.

2. MyMVCEX: A library project, it provides providesmymvc framework

The implementation class of the two IACTIONRESULT interfaces of
Used to output datatable, dataset to the browser client

3. SQLServersmallTooltib: All the background code required to run the website,
Including: Controller, BLL class, etc.

mymvc framework played

From the introduction of the previous project, we can see that the entire website does not have an ASPX file, only HTML file,
All calls to the server are all implemented by AJAX, such as: the following [comparative database] code fragment:

$.ajax({
    cache: false, dataType: "json", type: "GET",
    url: '/AjaxService/CompareDB.cspx',
    data:{  srcConnId: $("#hfSrcConnId").val(), 
            destConnId: $("#hfDestConnId").val(), 
            srcDB: $("#cboSrcDB").combobox("getValue"), 
            destDB: $("#cboDestDB").combobox("getValue") ,
            flag: flag
    },

At the server, I can respond to the client’s request as long as I implement such a C# method:

[Action]
public object CompareDB(string srcConnId, string destConnId, string srcDB, string destDB, string flag)
{
    var result = CompareDBHelper.CompareDB(srcConnId, destConnId, srcDB, destDB, flag);
    return new JsonResult(result);
}

As for: How to call this C#method initiated by JS, and the processing of the parameter and return value of the C#method during callingmymvc frameworkto implement.
For the development of AJAX, you can not care about this problem, just write a C# method to call JS call.

Quote MyMVC is a very simple matter. Just do the following configuration in web.config:

<httpHandlers>
    <add path="*.cspx" verb="*" type="MyMVC.AjaxHandlerFactory, MyMVC" validate="true" />
</httpHandlers>

Add a little bit: If you don’t like to see the Action method containing more input parameters, you can also use the following method:

public class CompareDbOption
{
    public string SrcConnId;
    public string DestConnId;
    public string SrcDb;
    public string DestDb;
    public string Flag;
}


[Action]
public object CompareDB(CompareDbOption option)
{
    var result = CompareDBHelper.CompareDB(option.SrcConnId, option.DestConnId, 
                                            option.SrcDb, option.DestDb, option.Flag);
    return new JsonResult(result);
}

If you like to use jQuery and jquery.form.js plug -in in the browser client,
You will find that you can use it on the servermymvc frameworkto achieve AJAX is too convenient.
Another code fragment with connection:

html form code:

<div id="divConnectionDialog" style="padding:10px; width: 420px; height: 320px; display: none;" title= "New/Edit Database Connection Information"> 
 <<<<form id="formConnection" method="post" action="/AjaxService/SubmitConnectionInfo.cspx">

<table cellpadding="4" cellspacing="0" style="width: 100%; border: 0px;">
<tr><td>server ip/name</td><td>
        <input id="txtServerIP" name="ServerIP" type="text" class="myTextbox" style="width: 220px" />
    </td></tr>
<tr><td>Login method</td><td>
        <select id="cboSSPI" name="SSPI" style="width: 222px" panelWidth="222">
            <option value="false">Username / password</option>
            <option value="true">Windows connection</option>
        </select>
    </td></tr>
<tr><td>login name</td><td>
        <input id="txtUserName" name="UserName" type="text" class="myTextbox" style="width: 220px" />
    </td></tr>
<tr><td>login password</td><td>
        <input id="txtPassword" name="Password" type="text" class="myTextbox" style="width: 220px" />
    </td></tr>
<tr><td></td><td>
        <input id="hfConnectionId" name="ConnectionId" type="hidden" value="" />
    </td></tr>
</table>
<div><span id="spanWait" style="display: none;" class="waitText">Please later ...</span></div>
</form>
</div>

javascript Submit the form code:

function SubmitConnectionForm(){
    if( ValidateForm() == false ) return false;
    $("#formConnection").ajaxSubmit({
     
        success: function(responseText, statusText) {            
            if (responseText == "update OK" ){
                $('#divConnectionDialog').dialog('close');
                // omit the following code.

server C# code:

[Action]
public string SubmitConnectionInfo(ConnectionInfo info)
{
    if( string.IsNullOrEmpty(info.ServerIP) )
        throw new MyMessageException("ServerIP is empty.");

    if( info.SSPI == false && string.IsNullOrEmpty(info.UserName) )
        throw new MyMessageException("UserName is empty.");

    bool isAdd = string.IsNullOrEmpty(info.ConnectionId);

    if( isAdd ) {
        info.ConnectionId = Guid.NewGuid().ToString();
        ConnectionManager.AddConnection(info);
        return info.ConnectionId;
    }
    else {
        ConnectionManager.UpdateConnection(info);
        return "update OK";
    }
}


public sealed class ConnectionInfo
{
    public string ConnectionId;
    public string ServerIP;
    public string UserName;
    public string Password;
    public bool SSPI;
    public int Priority;
}

During the development of the entire tool, due to the usemymvc frameworkand jQuery, the implementation of AJAX is simply too easy.

MYMVC framework download address:http://www.cnblogs.com/fish-li/archive/2012/02/21/2361982.html

Tool main interface

After the tool is started, you will be able to see the main interface below:

The [Tool List] on the left of the

main interface contains two independent functional modules.
The upper area on the right is a list of all database connections.
It is recommended to configure the SQL Server connection parameter you need to access when you use it for the first time.

This tool can manage multiple connections, and it will be sorted according to the frequency of connection use to facilitate operation.
If you need to create a connection, you can click the [New Connection] button in the toolbar to appear the following dialog box.

Tools can support two connections: 1. Windows trust connection, 2. Username/password connection.

Database connection List of some webpage code:


<div region="center" style="overflow:hidden;" title= "Database Connection List"iconCls="icon-Relation">
    <div class="easyui-layout" fit="true" border="false">
        <div region="center">
            <table id="tblConnList"></table>
        </div>
        <div region="south" split="true" style="height:220px; padding: 10px;" title= "Operation Instructions"iconCls="icon-help">
            <p>1. "Added connection", "Delete connection", "Set connection" is used to maintain the connection record.</p>
            <p>2. "Open the connection" will open the database object to browse the page according to the selected connection.</p>
        </div>
    </div>
</div>

Connection is stored with xml file, related operating code:

internal static class ConnectionManager
{
    private static List<ConnectionInfo> s_list = null;
    private static readonly Encoding DefaultEncoding = System.Text.Encoding.Unicode;
    private static readonly string s_savePath = Path.Combine(HttpRuntime.AppDomainAppPath, @"App_Data\Connection.xml");

    static ConnectionManager()
    {
        try {
            string appDataPath = Path.Combine(HttpRuntime.AppDomainAppPath, "App_Data");

            if( Directory.Exists(appDataPath) == false )
                Directory.CreateDirectory(appDataPath);
        }
        catch { }
    }

    [MethodImpl(MethodImplOptions.Synchronized)]
    public static List<ConnectionInfo> GetList()
    {
        EnsureListLoaded();

        // Calling this method should be much less than "modified", so it is decided to sort it here.return (from c in s_list orderby c.Priority descending select c).ToList();
    }

    [MethodImpl(MethodImplOptions.Synchronized)]
    public static void AddConnection(ConnectionInfo info)
    {
        EnsureListLoaded();

        s_list.Add(info);
        SaveListToFile();
    }

    [MethodImpl(MethodImplOptions.Synchronized)]
    public static void RemoveConnection(string ConnectionId)
    {
        EnsureListLoaded();

        int index = -1;
        for( int i = 0; i < s_list.Count; i++ )
            if( s_list[i].ConnectionId == ConnectionId ) {
                index = i;
                break;
            }

        if( index >= 0 ) {
            s_list.RemoveAt(index);
            SaveListToFile();
        }
    }

    [MethodImpl(MethodImplOptions.Synchronized)]
    public static void UpdateConnection(ConnectionInfo info)
    {
        EnsureListLoaded();

        ConnectionInfo exist = s_list.FirstOrDefault(x => x.ConnectionId == info.ConnectionId);

        if( exist != null ) {
            exist.ServerIP = info.ServerIP;
            exist.UserName = info.UserName;
            exist.Password = info.Password;
            exist.SSPI = info.SSPI;
            // Note: Other members who have not been listed indicate that they do not need to be updated here.SaveListToFile();
        }
    }

    [MethodImpl(MethodImplOptions.Synchronized)]
    public static ConnectionInfo GetConnectionInfoById(string connectionId, bool increasePriority)
    {
        if( string.IsNullOrEmpty(connectionId) )
            throw new ArgumentNullException("connectionId");

        EnsureListLoaded();

        ConnectionInfo exist = s_list.FirstOrDefault(x => x.ConnectionId == connectionId);
        if( exist == null )
            throw new MyMessageException("connectionId is invalid.");

        if( increasePriority ) {
            exist.Priority++;
            SaveListToFile();
        }
        return exist;
    }


    private static void EnsureListLoaded()
    {
        if( s_list == null ) {
            try {
                s_list = XmlHelper.XmlDeserializeFromFile<List<ConnectionInfo>>(s_savePath, DefaultEncoding);
            }
            catch {
                s_list = new List<ConnectionInfo>();
            }
        }
    }

    private static void SaveListToFile()
    {
        if( s_list == null || s_list.Count == 0 ) {
            try {
                File.Delete(s_savePath);
            }
            catch { }
        }
        else {
            XmlHelper.XmlSerializeToFile(s_list, s_savePath, DefaultEncoding);
        }
    }
}

service side Action implementation code:

[Action]
public object GetAllConnectionInfo()
{
    List<ConnectionInfo> list = ConnectionManager.GetList();

    ConnectionInfoDataGridJsonResult result = new ConnectionInfoDataGridJsonResult();
    result.total = list.Count;
    result.rows = list;

    return new JsonResult(result);
}

[Action]
public string SubmitConnectionInfo(ConnectionInfo info)
{
    if( string.IsNullOrEmpty(info.ServerIP) )
        throw new MyMessageException("ServerIP is empty.");

    if( info.SSPI == false && string.IsNullOrEmpty(info.UserName) )
        throw new MyMessageException("UserName is empty.");

    bool isAdd = string.IsNullOrEmpty(info.ConnectionId);

    if( isAdd ) {
        info.ConnectionId = Guid.NewGuid().ToString();
        ConnectionManager.AddConnection(info);
        return info.ConnectionId;
    }
    else {
        ConnectionManager.UpdateConnection(info);
        return "update OK";
    }

}

[Action]
public void DeleteConnection(string connectionId)
{
    if( string.IsNullOrEmpty(connectionId) )
        throw new MyMessageException("connectionId is empty.");

    ConnectionManager.RemoveConnection(connectionId);
}

[Action]
public string TestConnection(ConnectionInfo info)
{
    BaseBLL instance = BaseBLL.GetInstance(null);
    return instance.TestConnection(info);
}

database browser

In the [Database Connection List] of the main interface, select a connection, and then click the [Open Connection] button on the toolbar to enter the [DataBase Browser] interface.

In this tool, if you need to view the definition of an object of a database, you only need to click the corresponding object node:

In order to be convenient to operate, the tool provides multiple label viewing functions:

Key code for the list of database objects:

private static readonly string s_QueryDatabaseListScript =
    "SELECT dtb.name AS [Database_Name] FROM master.sys.databases AS dtb " +
    "WHERE (CAST(case when dtb.name in ('master','model','msdb','tempdb') then 1 else dtb.is_distributor end AS bit)=0 " +
    "    and CAST(isnull(dtb.source_database_id, 0) AS bit)=0) " +
    "ORDER BY [Database_Name] ASC";

protected override List<string> GetDatabaseNames(DbConnection connection)
{
    return ExecuteQueryToStringList(connection, s_QueryDatabaseListScript);
}

private static readonly string s_GetObjectNamesFormat =
    "select name from ( SELECT obj.name AS [Name],  " +
    "CAST( case when obj.is_ms_shipped = 1 then 1 " +
    "    when ( select major_id from sys.extended_properties  " +
    "        where major_id = obj.object_id and  minor_id = 0 and class = 1 and name = N'microsoft_database_tools_support')  " +
    "        is not null then 1  else 0 " +
    "end  AS bit) AS [IsSystemObject] " +
    "FROM sys.all_objects AS obj where obj.type in ({0}) )as tables where [IsSystemObject] = 0 ORDER BY [Name] ASC ";

private static readonly string s_ProcedureType = " N'P', N'PC' ";
private static readonly string s_FunctionType = " N'FN', N'IF', N'TF', N'FS', N'FT' ";
private static readonly string s_TableType = " N'U' ";
private static readonly string s_ViewType = " N'V' ";

protected override List<string> GetDbProcedureNames(DbConnection connection)
{
    //string sql = "select name from sys.objects where type='P' order by name";
    string sql = string.Format(s_GetObjectNamesFormat, s_ProcedureType);
    return ExecuteQueryToStringList(connection, sql);
}

protected override List<string> GetDbFunctionNames(DbConnection connection)
{
    //string sql = "select name from sys.objects where type='FN' order by name";
    string sql = string.Format(s_GetObjectNamesFormat, s_FunctionType);
    return ExecuteQueryToStringList(connection, sql);
}

protected override List<string> GetDbTableNames(DbConnection connection)
{
    //string sql = "select name from sys.objects where type='U' where name != 'sysdiagrams' order by name";
    string sql = string.Format(s_GetObjectNamesFormat, s_TableType);
    return ExecuteQueryToStringList(connection, sql);
}

protected override List<string> GetDbViewNames(DbConnection connection)
{
    //string sql = "select name from sys.objects where type='V' order by name";
    string sql = string.Format(s_GetObjectNamesFormat, s_ViewType);
    return ExecuteQueryToStringList(connection, sql);
}

View the implementation code of the definition script of the database object:

protected override ItemCode GetProcedureItem(DbConnection connection, string name)
{
    string query = string.Format("SELECT definition FROM sys.sql_modules JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id AND type in ({1}) and name = '{0}'", name, s_ProcedureType);
    string script = TryExecuteQuery(connection, query);
    return new ItemCode(name, ItemType.Procedure, script);
}

protected override ItemCode GetFunctionItem(DbConnection connection, string name)
{
    string query = string.Format("SELECT definition FROM sys.sql_modules JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id AND type in ({1}) and name = '{0}'", name, s_FunctionType);
    string script = TryExecuteQuery(connection, query);
    return new ItemCode(name, ItemType.Function, script);
}

protected override ItemCode GetViewItem(DbConnection connection, string name)
{
    string query = string.Format("SELECT definition FROM sys.sql_modules JOIN sys.objects ON sys.sql_modules.object_id = sys.objects.object_id AND type in ({1}) and name = '{0}'", name, s_ViewType);
    string script = TryExecuteQuery(connection, query);
    return new ItemCode(name, ItemType.View, script);
}

protected override ItemCode GetTableItem(DbConnection connection, string name)
{
    string script = null;
    try {
        script = SmoHelper.ScriptTable(connection, null, name);
        if( string.IsNullOrEmpty(script) )
            script = s_CannotGetScript;
    }
    catch( Exception ex ) {
        script = ex.Message;
    }
    return new ItemCode(name, ItemType.Table, script);
}

Search database

You can select a node in the left tree control on the interface shown in the interface shown above, right -click, and then select “Search in the database”. At this time, the following dialog box will appear:

In the dialog box above, click OK to determine the button, the following search results can appear:
Instructions: The matching will be highlighting.

Search for the relevant code of the database object:

[Action]
public object SearchDB(string connectionId, string dbName, string searchWord,
        int wholeMatch, int caseSensitive, string searchScope, string limitCount)
{
    if( string.IsNullOrEmpty(searchWord) )
        throw new ArgumentNullException("searchWord");
    
    BaseBLL instance = BaseBLL.GetInstance(connectionId);

    DbOjbectType types = CompareDBHelper.GetDbOjbectTypeByFlag(searchScope);
    List<ItemCode> list = instance.GetDbAllObjectScript(instance.ConnectionInfo, dbName, types);

    List<SearchResultItem> result = new List<SearchResultItem>(list.Count);

    int limitResultCount = 0;
    int.TryParse(limitCount, out limitResultCount);

    FishWebLib.StringSearcher searcher = 
        FishWebLib.StringSearcher.GetStringSearcher(searchWord, (wholeMatch == 1), (caseSensitive == 1));

    foreach( ItemCode code in list ) {
        if( limitResultCount != 0 && result.Count >= limitResultCount )
            break;

        if( code.SqlScript.IndexOf(searchWord, StringComparison.OrdinalIgnoreCase) >= 0 ) {
            string[] lines = instance.SplitCodeToLineArray(code.SqlScript);
            for( int i = 0; i < lines.Length; i++ )
                if( searcher.IsMatch(lines[i]) ) {
                    SearchResultItem item = new SearchResultItem();
                    item.LineNumber = i + 1;
                    item.ObjectName = code.Name;
                    item.ObjectType = code.Type.ToString();
                    item.SqlScript = code.SqlScript;
                    result.Add(item);
                    break;
                }
        }
    }

    return new JsonResult(result);
}


public List<ItemCode> GetDbAllObjectScript(ConnectionInfo info, string dbName, DbOjbectType type)
{
    List<ItemCode> list = new List<ItemCode>();
    string connectionString = GetDbConnectionString(info, dbName);

    using( DbConnection connection = CreateConnection(connectionString) ) {
        connection.Open();

        if( (type & DbOjbectType.Table) == DbOjbectType.Table ) {
            List<string> nameList = GetDbTableNames(connection);
            foreach( string name in nameList )
                list.Add(GetTableItem(connection, name));
        }

        if( (type & DbOjbectType.Procedure) == DbOjbectType.Procedure ) {
            List<string> nameList = GetDbProcedureNames(connection);
            foreach( string name in nameList )
                list.Add(GetProcedureItem(connection, name));
        }

        if( (type & DbOjbectType.Function) == DbOjbectType.Function ) {
            List<string> nameList = GetDbFunctionNames(connection);
            foreach( string name in nameList )
                list.Add(GetFunctionItem(connection, name));
        }

        if( (type & DbOjbectType.View) == DbOjbectType.View ) {
            List<string> nameList = GetDbViewNames(connection);
            foreach( string name in nameList )
                list.Add(GetViewItem(connection, name));
        }
    }
    return list;
}

Copy the storage procedure tool

In order to demonstrate this function, you need to create a database. I created a database: testmytool, it has no database object, as shown in the figure below

Then, start [Copy the Storage Line Tool] from the main interface,
Then choose:database connection, database object
Click the [Refresh List] button, and you will see the following results:

We can choose to copy (storage procedure, view, custom function) object:

Finally click the [Start Copy] button to complete the copy process.
At this time, the display result of the database testmytool is:

The core part of this function implementation code:

[Action]
public string CopyProcedures(string srcConnId, string destConnId, string srcDB, string destDB, 
        string spNames, string viewNames, string funcNames)
{
    BaseBLL instance1 = BaseBLL.GetInstance(srcConnId);
    BaseBLL instance2 = BaseBLL.GetInstance(destConnId);
    if( instance1.GetType() != instance2.GetType() )
        throw new Exception("The types of databases are inconsistent, and copy operation cannot be performed.");

    if( srcConnId == destConnId && srcDB == destDB )
        throw new Exception("Invalid operation.");

    List<ItemCode> procedures = instance1.GetDbAllObjectScript(instance1.ConnectionInfo, srcDB,    spNames, viewNames, funcNames);
    return instance2.UpdateProcedures(instance2.ConnectionInfo, destDB, procedures);
}


public List<ItemCode> GetDbAllObjectScript(ConnectionInfo info, 
                        string dbName, string spNames, string viewNames, string funcNames)
{
    List<ItemCode> list = new List<ItemCode>();
    string connectionString = GetDbConnectionString(info, dbName);

    using( DbConnection connection = CreateConnection(connectionString) ) {
        connection.Open();

        if( string.IsNullOrEmpty(spNames) == false ) {
            foreach( string name in spNames.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries) )
                list.Add(GetProcedureItem(connection, name));
        }

        if( string.IsNullOrEmpty(funcNames) == false ) {
            foreach( string name in funcNames.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries) )
                list.Add(GetFunctionItem(connection, name));
        }

        if( string.IsNullOrEmpty(viewNames) == false ) {
            foreach( string name in viewNames.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries) )
                list.Add(GetViewItem(connection, name));
        }
    }
    return list;
}


public override string UpdateProcedures(ConnectionInfo info, string dbName, List<ItemCode> list)
{
    string connectionString = GetDbConnectionString(info, dbName);

    using( DbConnection connection = CreateConnection(connectionString) ) {
        connection.Open();
        DbCommand command = connection.CreateCommand();
        
        foreach(ItemCode item in list) {
            command.CommandText = GetDeleteObjectScript(item.Name, item.Type);
            command.ExecuteNonQuery();

            command.CommandText = string.Format(s_CreateObjectFormat, item.SqlScript.Replace("'", "''"));
            command.ExecuteNonQuery();
        }
    }

    return string.Format("The operation was successful, and a total of {0} object was copied.", list.Count);
}

database comparative tool

In order to facilitate the subsequent introduction, I will copy all the storage procedures to TestmyTool. This process will omit the map.
not only that, I also made a little modification of one of the storage procedures.

Then, in the main interface of the program, start [Database Comparison Tool],
Then select:database connection, database object
Click the [Start Comparison Database] button, you will see the following comparison results.

In the definition of
Each database object, the first non -matching row will be displayed in high bright lines.

In order to let you know the appearance of the non -matching match, the tool will also show the front and back codes that are not matched.

The core part of this function implementation code:

internal static class CompareDBHelper
{
    public sealed class ThreadParam
    {
        public BaseBLL Instance;
        public string DbName;
        public DbOjbectType DbOjbectType;
        public List<ItemCode> Result;
        public Exception Exception;

        public ThreadParam(BaseBLL instance, string dbName, DbOjbectType type)
        {
            this.Instance = instance;
            this.DbName = dbName;
            this.DbOjbectType = type;
            this.Result = new List<ItemCode>();
        }
    }

    private static void ThreadWorkAction(object obj)
    {
        ThreadParam param = (ThreadParam)obj;
        try {
            param.Result = param.Instance.GetDbAllObjectScript(param.Instance.ConnectionInfo, param.DbName, param.DbOjbectType);
        }
        catch( Exception ex ) {
            param.Exception = ex;
        }
    }

    public static DbOjbectType GetDbOjbectTypeByFlag(string flag)
    {
        if( string.IsNullOrEmpty(flag) )
            return DbOjbectType.None;

        DbOjbectType types = DbOjbectType.None;

        if( flag.IndexOf('T') >= 0 )
            types |= DbOjbectType.Table;
        if( flag.IndexOf('V') >= 0 )
            types |= DbOjbectType.View;
        if( flag.IndexOf('P') >= 0 )
            types |= DbOjbectType.Procedure;
        if( flag.IndexOf('F') >= 0 )
            types |= DbOjbectType.Function;

        return types;
    }

    public static List<CompareResultItem> CompareDB(string srcConnId, string destConnId, string srcDB, string destDB, string flag)
    {
        BaseBLL instance1 = BaseBLL.GetInstance(srcConnId);
        BaseBLL instance2 = BaseBLL.GetInstance(destConnId);
        if( instance1.GetType() != instance2.GetType() )
            throw new Exception("The types of databases are inconsistent, it is more meaningful.");


        DbOjbectType types = GetDbOjbectTypeByFlag(flag);
        ThreadParam param1 = new ThreadParam(instance1, srcDB, types);
        ThreadParam param2 = new ThreadParam(instance2, destDB, types);
        Thread thread1 = new Thread(ThreadWorkAction);
        Thread thread2 = new Thread(ThreadWorkAction);
        thread1.Start(param1);
        thread2.Start(param2);
        thread1.Join();
        thread2.Join();

        if( param1.Exception != null )
            throw param1.Exception;
        if( param2.Exception != null )
            throw param2.Exception;

        List<ItemCode> list1 = param1.Result;
        List<ItemCode> list2 = param2.Result;


        List<CompareResultItem> result = new List<CompareResultItem>();
        ItemCode dest = null;

        // Compare the database object category.for( int typeIndex = 0; typeIndex < 4; typeIndex++ ) {
            ItemType currentType = (ItemType)typeIndex;

            foreach( ItemCode item1 in list1 ) {
                // If it is not the object category to be compared at present, skip.if( item1.Type != currentType )
                    continue;

                dest = null;
                foreach( ItemCode item2 in list2 ) {
                    if( item1.Type == item2.Type && string.Compare(item1.Name, item2.Name, true) == 0 ) {
                        dest = item2;
                        break;
                    }
                }

                if( dest == null ) {
                    CompareResultItem cri = new CompareResultItem();
                    cri.ObjectType = item1.TypeText;
                    cri.ObjectName = item1.Name;
                    cri.LineNumber = -1;
                    cri.SrcLine = string.Empty;
                    cri.DestLine = string.Empty;
                    cri.Reason = "Source database exists, and the target database does not exist.";
                    result.Add(cri);
                    continue;
                }
                else {
                    if( item1.SqlScript == dest.SqlScript )
                        continue;

                    // Start the comparison code.CompareResultItem cri = null;
                    string[] lines1 = instance1.SplitCodeToLineArray(item1.SqlScript);
                    string[] lines2 = instance1.SplitCodeToLineArray(dest.SqlScript);

                    for( int i = 0; i < lines1.Length; i++ ) {
                        if( i >= lines2.Length ) {
                            // The number of code rows of the target object is relatively smallcri = new CompareResultItem();
                            cri.ObjectType = item1.TypeText;
                            cri.ObjectName = item1.Name;
                            cri.LineNumber = i + 1;
                            GetNearLines(lines1, lines2, i, cri);
                            cri.Reason = "There is no corresponding code in the target object.";
                            result.Add(cri);
                            break;
                        }

                        string s1 = lines1[i].Trim();
                        string s2 = lines2[i].Trim();
                        if( string.Compare(s1, s2, true) != 0 ) {
                            cri = new CompareResultItem();
                            cri.ObjectType = item1.TypeText;
                            cri.ObjectName = item1.Name;
                            cri.LineNumber = i + 1;
                            GetNearLines(lines1, lines2, i, cri);
                            cri.Reason = "The code is inconsistent.";
                            result.Add(cri);
                            break;
                        }
                    }

                    if( cri != null )
                        continue;    // Compare the next objectif( lines2.Length > lines1.Length ) {
                        // The number of code rows of the target object is relatively smallcri = new CompareResultItem();
                        cri.ObjectType = item1.TypeText;
                        cri.ObjectName = item1.Name;
                        cri.LineNumber = lines1.Length + 1;
                        GetNearLines(lines1, lines2, lines1.Length, cri);
                        cri.Reason = "There is no corresponding code in the source object.";
                        result.Add(cri);
                        break;
                    }
                }
            }


            foreach( ItemCode item2 in list2 ) {
                // If it is not the object category to be compared at present, skip.if( item2.Type != currentType )
                    continue;

                dest = null;
                foreach( ItemCode item1 in list1 ) {
                    if( item1.Type == item2.Type && string.Compare(item1.Name, item2.Name, true) == 0 ) {
                        dest = item2;
                        break;
                    }
                }

                if( dest == null ) {
                    CompareResultItem cri = new CompareResultItem();
                    cri.ObjectType = item2.TypeText;
                    cri.ObjectName = item2.Name;
                    cri.LineNumber = -2;
                    cri.SrcLine = string.Empty;
                    cri.DestLine = string.Empty;
                    cri.Reason = "The target database exists, and the source database does not exist.";
                    result.Add(cri);
                    continue;
                }
            }
        }


        return result;
    }


    private static void GetNearLines(string[] lines1, string[] lines2, int index, CompareResultItem cri)
    {
        int firstLine;
        cri.SrcLine = GetOneNearLines(lines1, index, out firstLine);
        cri.SrcFirstLine = firstLine;

        cri.DestLine = GetOneNearLines(lines2, index, out firstLine);
        cri.DestFirstLine = firstLine;
    }

    private static string GetOneNearLines(string[] lines, int index, out int firstLine)
    {
        firstLine = -1;
        System.Text.StringBuilder sb = new System.Text.StringBuilder();

        int start = index - 5;
        for( int i = 0; i < 11; i++ )
            if( start + i >= 0 && start + i < lines.Length ) {
                if( firstLine < 0 )
                    firstLine = start + i + 1;
                sb.AppendLine(lines[start + i]);
            }

        return sb.ToString();
    }
}

View table structure definition

Tool allows you to easily view the definition of a table structure:

can also view the definition of multiple tables at a time:

can also get the creation script of all objects of the entire database at once:

The core part of this function implementation code:

public class AjaxDataTable
{
    [Action]
    public object TableDescribe(string connectionId, string dbName, string tableName)
    {
        if( string.IsNullOrEmpty(connectionId) || string.IsNullOrEmpty(dbName) || string.IsNullOrEmpty(tableName) )
            throw new ArgumentException("connString or tableName is null.");


        BaseBLL instance = BaseBLL.GetInstance(connectionId);
        DataTable table = instance.GetTableFields(instance.ConnectionInfo, dbName, tableName);
        return new MyMvcEx.DataTableResult(table);
    }


    [Action]
    public object MultiTableDescribe(string connectionId, string dbName, string tableNames)
    {
        if( string.IsNullOrEmpty(connectionId) || string.IsNullOrEmpty(dbName) || string.IsNullOrEmpty(tableNames) )
            throw new ArgumentException("connString or tableName is null.");


        BaseBLL instance = BaseBLL.GetInstance(connectionId);
        DataSet ds = instance.GetTables(instance.ConnectionInfo, dbName, tableNames);
        return new MyMvcEx.DataSetResult(ds);
    }
}

Modify the operating environment

So far, this tool can only run in Visual Studio. Obviously, it has a large difference with the [tool] we often see.

If you want to run this tool conveniently, you can install another small tool for me to quickly start the current tool, the download address of that tool:[ASP.NET program can also run like a winform program]

Then, you can start this small tool in the Windows resource manager:

Is it very much like a desktop program now?

You can even create a starting menu item, or a shortcut to start this small tool. The specific method can refer to the blog:[ASP.NET program can also run like a winform program]

Supplementary instructions on this tool

This small tool only achieves some simple functions, and it is mainly concentrated in viewing the definition of the database.

In the early version of this tool, some people mentioned that the function of viewing the data table requires the realization of the data table.

In today’s version, I did not implement it, but I provided some basic code necessary for implementing this function.
For example, I provided two actionResult (Note: In the ACTION code of the previous section, the following two implementation classes are used):

public class DataTableResult : IActionResult
    {
        private DataTable _table;

        public DataTableResult(DataTable table)
        {
            if( table == null )
                throw new ArgumentNullException("table");

            _table = table;
        }

        void IActionResult.Ouput(HttpContext context)
        {
            context.Response.ContentType = "text/html";
            string html = DataTableHelper.TableToHtml(_table);
            context.Response.Write(html);
        }                
    }

    public class DataSetResult : IActionResult
    {
        private DataSet _ds;

        public DataSetResult(DataSet ds)
        {
            if( ds == null )
                throw new ArgumentNullException("ds");

            _ds = ds;
        }

        void IActionResult.Ouput(HttpContext context)
        {
            List<DataSetJsonItem> list = new List<DataSetJsonItem>();

            for( int i = 0; i < _ds.Tables.Count; i++ ) {
                DataTable table = _ds.Tables[i];
                string html = DataTableHelper.TableToHtml(table);
                list.Add(new DataSetJsonItem { TableName = table.TableName, Html = html });
            }

            JsonResult json = new JsonResult(list);
            (json as IActionResult).Ouput(context);
        }


        public class DataSetJsonItem
        {
            public string TableName;
            public string Html;
        }
    }


    public static class DataTableHelper
    {
        public static string TableToHtml(DataTable table)
        {
            if( table == null )
                throw new ArgumentNullException("table");

            StringBuilder html = new StringBuilder();
            html.AppendLine("<table cellpadding=\"2\" cellspacing=\"1\" class=\"myGridVew\"><thead><tr>");

            for( int i = 0; i < table.Columns.Count; i++ )
                html.AppendFormat("<th>{0}</th>", HttpUtility.HtmlEncode(table.Columns[i].ColumnName));

            html.AppendLine("</tr></thead><tbody>");

            object cell = null;
            for( int j = 0; j < table.Rows.Count; j++ ) {
                html.AppendLine("<tr>");

                for( int i = 0; i < table.Columns.Count; i++ ) {
                    cell = table.Rows[j][i];
                    if( cell == null || DBNull.Value.Equals(cell) )
                        html.Append("<td></td>");
                    else
                        html.AppendFormat("<td>{0}</td>", HttpUtility.HtmlEncode(cell.ToString()));
                }
                html.AppendLine("</tr>");
            }

            html.AppendLine("</tbody></table>");
            return html.ToString();
        }
    }

In order to easily display the form on the client, I also provided a JS function:

function SetGridViewColor(){
    $("table.myGridVew").each(function(){
        $(this).removeClass("myGridVew").addClass("GridView")
            .find(">thead>tr").addClass("GridView_HeaderStyle").end()
            .find(">tbody>tr")
            .filter(':odd').addClass("GridView_AlternatingRowStyle").end()
            .filter(':even').addClass("GridView_RowStyle");
    });
}

If you think it is necessary to integrated the function of the data table in this tool, the function of the data table can be implemented (the tool is open source).
Friendly Tips: Use the code above andmymvc framework, implement oneSimple view of datais easier.

I wrote here today, I hope everyone can like this small tool, andmymvc framework

Click here to download all code

Reprinted: https://www.cnblogs.com/fish-li/archive/2012/03/04/2379612.html

source

Related Posts

Accept a & isstream parameter, print on the standard output Justnothingat

Solution Introduction to mybatis-plus calls SelectByid and Selectlist.

View patient’s scanning HU value distribution situation NEVER

p language basic exercise (2) amber

spark reads HIVE data through HiveServer2 JDBC through the HiveServer2 JDBC method.

Random Posts

Niuke BM57-Number of islands -C ++ C ++ island quantity

Connection-to build the Connecton connection with the Tomcat connection pool The

wp listbox traversing datatemplate (get all controls)

TOMCAT prevents cross -site requests for counterfeiting filter CSRFPREVENTIONTILTER.JAVA source code

Unity guidance bag lost TAG value, layer value