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 。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· NetPad:一个.NET开源、跨平台的C#编辑器
· PowerShell开发游戏 · 打蜜蜂