2001年4月份,我在博客中发过一个小工具,它是一个用ASP.NET写的SQL SERVER的辅助小工具。 在这期间,有些人貌似对那个工具比较有兴趣,所以我常能收到索要源代码的邮件。 正好,我上月又发布了我的MVC框架,因此打算用【我的ASP.NET MVC框架】来重写这个工具, 并开源。
工具的特点:
1. 采用ASP.NET编写,并借助MyMVC框架。
2. 为了更好地接近桌面程序的操作体验,网站采用纯AJAX的方式实现。
3. 界面使用了 JQuery Easy UI
4. 代码的语法着色使用了 syntaxhighlighter (JavaScript类库)
工具的定位:只是辅助工具,因此功能有限,但要将有限的功能做得尽量好。
下面将分别介绍工具所能完成的功能,以及关键的实现代码。
说明:工具的所有源代码可以在本文的结尾处下载。
项目介绍
整个工具的源代码结构如下:
项目由Visual Studio 2008创建,包含三个部分: 1. WebApp:一个ASP.NET网站,它是工具的可运行部分。 网站只包含一些HTML, CSS, JavaScript,DLL文件。 2. MyMvcEx:一个类库项目,它提供了MyMVC框架的二个IActionResult接口的实现类, 用于向浏览器客户端输出DataTable, DataSet 3. SqlServerSmallToolLib:运行网站所需的所有后台代码, 包括:Controller,BLL类,等等。 |
MyMVC框架发挥的作用
从前面的项目介绍中,我们可以看到,整个网站没有一个ASPX文件,只有HTML文件,
所有对服务器的调用全由AJAX来实现,比如:下面的【比较数据库】的代码片段:
$.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 },
在服务端,我只要实现这样一个C#方法就可以响应客户端的请求了:
[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); }
至于说:JS发起的请求是如何调用到这个C#方法的,以及这个C#方法在调用时的参数和返回值的处理,全由MyMVC框架来实现。
对于开发AJAX来说,可以不用关心这个问题,只要写出一个C#方法给JS调用就可以了。
引用MyMVC是件很简单的事情,只需要在web.config中做如下的配置即可:
<httpHandlers> <add path="*.cspx" verb="*" type="MyMVC.AjaxHandlerFactory, MyMVC" validate="true" /> </httpHandlers>
再补充一点:如果不喜欢看到Action方法包含较多的输入参数,也可以使用下面的方法:
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); }
如果您喜欢在浏览器的客户端中使用jquery以及jquery.form.js插件,
您会发现在服务端再借助MyMVC框架来实现AJAX实在是太方便了。
再来一个添加连接的代码片段:
Html表单代码:
<div id="divConnectionDialog" style="padding:10px; width: 420px; height: 320px; display: none;" title="新增/编辑 数据库连接信息"> <form id="formConnection" method="post" action="/AjaxService/SubmitConnectionInfo.cspx"> <table cellpadding="4" cellspacing="0" style="width: 100%; border: 0px;"> <tr><td>服务器IP/Name</td><td> <input id="txtServerIP" name="ServerIP" type="text" class="myTextbox" style="width: 220px" /> </td></tr> <tr><td>登录方式</td><td> <select id="cboSSPI" name="SSPI" style="width: 222px" panelWidth="222"> <option value="false">用户名/密码</option> <option value="true">Windows连接</option> </select> </td></tr> <tr><td>登录名</td><td> <input id="txtUserName" name="UserName" type="text" class="myTextbox" style="width: 220px" /> </td></tr> <tr><td>登录密码</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">请稍后......</span></div> </form> </div>
JavaScript提交表单代码:
function SubmitConnectionForm(){ if( ValidateForm() == false ) return false; $("#formConnection").ajaxSubmit({ success: function(responseText, statusText) { if (responseText == "update OK" ){ $('#divConnectionDialog').dialog('close'); // 省略后面的代码。
服务端C#代码:
[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; }
在整个工具的开发过程中,由于使用了MyMVC框架以及JQuery,AJAX的实现简直是太容易了。
MyMVC框架的下载地址:http://www.cnblogs.com/fish-li/archive/2012/02/21/2361982.html
工具主界面
工具启动后,将能看到下面的主界面:
主界面的左边的【工具列表】中包含二个独立的功能模块。
右边的上方区域是所有的数据库连接的列表。
建议在初次使用时,将自己所需要访问的SQL SERVER连接参数配置好。
这个工具可以管理多个连接,而且会根据连接的使用频率来排序,以方便操作。
如果需要创建一个连接,可以点击工具栏中的【新增连接】按键,将出现以下对话框。
工具可以支持二种连接方式:1. Windows信任连接,2. 用户名/密码连接。
数据库连接列表的部分网页代码:
<div region="center" style="overflow:hidden;" title="数据库连接列表" 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="操作说明" iconCls="icon-help">
<p>1. “新增连接”,“删除连接”,“设置连接”用于维护连接记录。</p>
<p>2. “打开连接”将根据选择的连接,打开 Database 对象浏览页面。</p>
</div>
</div>
</div>
连接采用XML文件来保存,相关的操作代码:
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(); // 调用这个方法应该会比“修改”的次数会少很多,所以决定在这里排序。 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; // 注意:其它没列出的成员,表示不需要在此更新。 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); } } }
服务端的Action实现代码:
[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 浏览器
在主界面的【数据库连接列表】中,选择一个连接,然后点击工具栏上的【打开连接】按键,即可进入【Database 浏览器】界面。
在这个工具中,如果需要查看某个数据库对象的定义,只需要点击相应的对象节点就可以了:
为了操作方便,工具提供多标签查看功能:
获取数据库对象列表的关键代码:
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); }
查看数据库对象的定义脚本的实现代码:
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); }
搜索数据库
您可以在上图所示界面的左边树控件中,选择一个节点,右击,然后选择“在数据库中搜索”,此时会出现如下对话框:
在上图的对话框中,点击确定按键后,可出现下面的查找结果:
说明:匹配行就会高亮显示。
搜索数据库对象的相关代码:
[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; }
复制存储过程工具
为了演示这个功能,先需要创建一个数据库。我创建了一个数据库:TestMyTool,它没有任何数据库对象,如下图
然后,从主界面中启动【复制存储过程工具】,
接着选择:数据库连接,数据库对象,
点击【刷新列表】按键,将看到以下结果:
我们可以选择要复制的(存储过程,视图,自定义函数)对象:
最后点击【开始复制】按键,即可完成复制过程。
此时数据库TestMyTool的显示结果为:
此功能的核心部分实现代码:
[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("数据库的种类不一致,不能执行复制操作。"); if( srcConnId == destConnId && srcDB == destDB ) throw new Exception("无效的操作。"); 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("操作成功,共复制了 {0} 个对象。", list.Count); }
数据库比较工具
为了方便后面的介绍,我将复制全部的存储过程到TestMyTool,这个过程将省略贴图。
不仅如此,我还对其中的一个存储过程做了一点修改。
然后,在程序主界面中,启动【数据库比较工具】,
接着选择:数据库连接,数据库对象,
点击【开始比较数据库】按钮后,将能看到以下比较结果。
每个数据库对象的定义中,第一个不匹配的行将以高亮行显示。
为了能让您知道不匹配行的出现位置,工具还会显示不匹配行的前后5行代码。
此功能的核心部分实现代码:
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("数据库的种类不一致,比较没有意义。"); 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; // 按数据库对象类别分次比较。 for( int typeIndex = 0; typeIndex < 4; typeIndex++ ) { ItemType currentType = (ItemType)typeIndex; foreach( ItemCode item1 in list1 ) { // 如果不是当前要比较的对象类别,则跳过。 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 = "源数据库中存在,而目标数据库中不存在。"; result.Add(cri); continue; } else { if( item1.SqlScript == dest.SqlScript ) continue; // 开始比较代码了。 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 ) { // 目标对象的代码行数比较少 cri = new CompareResultItem(); cri.ObjectType = item1.TypeText; cri.ObjectName = item1.Name; cri.LineNumber = i + 1; GetNearLines(lines1, lines2, i, cri); cri.Reason = "目标对象中已没有对应行数的代码。"; 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 = "代码不一致。"; result.Add(cri); break; } } if( cri != null ) continue; // 比较下一个对象 if( lines2.Length > lines1.Length ) { // 目标对象的代码行数比较少 cri = new CompareResultItem(); cri.ObjectType = item1.TypeText; cri.ObjectName = item1.Name; cri.LineNumber = lines1.Length + 1; GetNearLines(lines1, lines2, lines1.Length, cri); cri.Reason = "源对象中已没有对应行数的代码。"; result.Add(cri); break; } } } foreach( ItemCode item2 in list2 ) { // 如果不是当前要比较的对象类别,则跳过。 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 = "目标数据库中存在,而源数据库中不存在。"; 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(); } }
查看表结构定义
工具可以让您轻松地查看一个表结构的定义:
也可以一次查看多个表的定义:
还可一下子得到整个数据库的所有对象的创建脚本:
此功能的核心部分实现代码:
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); } }
修改运行环境
到目前为止,这个工具还只能在Visual Studio中运行,显然它与我们经常见到的【工具】有较大的差别。
如果您希望可以方便地运行这个工具,那么可以安装我的另一个小工具来快速地启动当前这个工具, 那个工具的下载地址:【ASP.NET程序也能像WinForm程序一样运行】
然后,就可以在Windows资源管理器中启动这个小工具:
现在是不是很像一个桌面程序了?
您甚至也可以创建一个开始菜单项,或者一个快捷方式来启动这个小工具, 具体方法可参考博客:【ASP.NET程序也能像WinForm程序一样运行】
关于此工具的补充说明
这个小工具只实现了一些简单的功能,而且主要集中在查看数据库的定义这块。
这个工具的早期版本中,有些人提到了要求实现查看数据表的功能。
在今天的版本中,我并没有实现,但我提供了实现这个功能所必要的一些基础代码。
例如,我提供了二个ActionResult (注意:前面小节的Action代码中,就使用了下面的二个实现类):
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(); } }
为了方便地在客户端将表格显示地漂亮些,我还提供了一个JS函数:
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"); }); }
如果您认为很有必要在这个工具中集成数据表的查看(或者查询数据)的功能,那么可以自行实现(工具是开源的)。
友情提示:使用上面的代码以及MyMVC框架,实现一个简单的查看数据是会比较容易的。
今天就写到这里,希望大家能喜欢这个小工具,以及 MyMVC框架。