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框架。
点击此处下载全部代码
如果,您认为阅读这篇博客让您有些收获,不妨点击一下右下角的【推荐】按钮。
如果,您希望更容易地发现我的新博客,不妨点击一下右下角的【关注 Fish Li】。
因为,我的写作热情也离不开您的肯定支持。
感谢您的阅读,如果您对我的博客所讲述的内容有兴趣,请继续关注我的后续博客,我是Fish Li 。