WPF根据Oracle数据库的表,生成CS文件小工具

开发小工具的原因:

1、我们公司的开发是客户端用C#,服务端用Java,前后台在通讯交互的时候,会用到Oracle数据库的字段,因为服务器端有公司总经理开发的一个根据Oracle数据库的表生成的class文件,每次都是等服务端都写好了实体类以后,我们再复制、粘贴,修改字段的类型为string,因为在生成的时候,是根据Oracle数据库里的字段类型生成的java文件,但是我们在前后台通信的时候,为了不失精度的一些问题,一般都用string类型来传递,因此每次修改都比较费事。

2、VS2015在封装字段的时候,不会像2012一样,在字段的下方,而是统一的封装到一起,即字段都在上方,属性统一的在下方。

小工具界面效果:

WPF根据Oracle数据库的表,生成CS文件小工具

这个是刚打开小工具的页面,连接按钮上面的几个Textbox就是连接数据库时需要的参数。

WPF根据Oracle数据库的表,生成CS文件小工具

连接成功以后,把上面的连接部分隐藏掉,为了能有一个稍微大一点的空间,左侧用的是TreeView,父节点是表的Owner,子节点是表

WPF根据Oracle数据库的表,生成CS文件小工具

点击左侧TreeView的父节点,则其拥有的表全部显示在右侧

WPF根据Oracle数据库的表,生成CS文件小工具

点击左侧的子节点,则显示出来,这个表具体有哪些字段,要生成的VO里的字段就是指这些字段。

WPF根据Oracle数据库的表,生成CS文件小工具WPF根据Oracle数据库的表,生成CS文件小工具

NameSpace是指要生成的文件的命名空间,如果不输入,则默认Test,点击生成VO,则会同时生成两个文件,在不同的文件夹下,一个带通知机制,一个不带通知机制,需要哪个是根据实际情况来定。

开发过程:

1、要连接数据库,则自然要有连接数据库的实体类

public class ConnVo : ObjectNotifyPropertyChanged
{
private string connIP;//ip地址
private string connPort;//端口
private string connSid;//服务器名称
private string connUser;//用户名
private string connPwd;//密码 public string ConnIP
{
get
{
return connIP;
} set
{
connIP = value;
RaisePropertyChanged("ConnIP");
}
} public string ConnPort
{
get
{
return connPort;
} set
{
connPort = value;
RaisePropertyChanged("ConnPort");
}
} public string ConnSid
{
get
{
return connSid;
} set
{
connSid = value;
RaisePropertyChanged("ConnSid");
}
} public string ConnUser
{
get
{
return connUser;
} set
{
connUser = value;
RaisePropertyChanged("ConnUser");
}
} public string ConnPwd
{
get
{
return connPwd;
} set
{
connPwd = value;
RaisePropertyChanged("ConnPwd");
}
}
}

ConnVo

2、因为是对Oracle进行操作,因此数据库连接、Select语句神马的肯定少不了,因此专门有一个数据库操作类

public class DBHelper
{
/// <summary>
/// 声明连接
/// </summary>
protected static OracleConnection Connection; /// <summary>
/// 返回Connection
/// </summary>
/// <param name="ip">地址</param>
/// <param name="port">端口</param>
/// <param name="sid">服务名称</param>
/// <param name="user">用户</param>
/// <param name="pwd">密码</param>
/// <returns>OleDbConnection</returns>
private static OracleConnection ConnForOracle(string ip, string port, string sid, string user, string pwd)
{
string connStr;
connStr = "Data Source=(DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = " + ip + ")(PORT = " + port + "))) (CONNECT_DATA = (SERVICE_NAME = " + sid + ")));User ID=" + user + ";Password=" + pwd + ";";
Connection = new OracleConnection(connStr);
return Connection;
} /// <summary>
/// 连接数据库
/// </summary>
/// <param name="ip">地址</param>
/// <param name="port">端口</param>
/// <param name="sid">服务名称</param>
/// <param name="user">用户</param>
/// <param name="pwd">密码</param>
public static bool OpenConnection(string ip, string port, string sid, string user, string pwd)
{
ConnForOracle(ip, port, sid, user, pwd);
try
{
//不为空 并且 是关闭或者断了的情况下,才连接
if (Connection != null && (Connection.State == System.Data.ConnectionState.Closed || Connection.State == System.Data.ConnectionState.Broken))
{
Connection.Open();
ReturnOwner = Select("SELECT OWNER, TABLE_NAME FROM ALL_TAB_COMMENTS ORDER BY OWNER, TABLE_NAME");
}
MessageBox.Show(Connection.State.ToString());
return true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
return false;
}
}
/// <summary>
/// 关闭
/// </summary>
public static void CloseConnection()
{
//不为空 并且 是打开状态下才关闭
if (Connection != null && Connection.State == System.Data.ConnectionState.Open)
{
Connection.Close();
}
} /// <summary>
/// 查询
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns></returns>
private static DataTable Select(string sql)
{
OracleCommand cmd = new OracleCommand(sql, Connection);
OracleDataAdapter oda = new OracleDataAdapter(cmd);
DataTable dt = new DataTable();
oda.Fill(dt);
cmd.Dispose();
return dt;
} /// <summary>
/// 获取表名
/// </summary>
/// <param name="owner">拥有者</param>
public static void GetTableName(string owner)
{
ReturnTableName = Select("SELECT OWNER, TABLE_NAME, TABLE_TYPE, COMMENTS FROM ALL_TAB_COMMENTS WHERE OWNER='" + owner + "' ORDER BY OWNER, TABLE_TYPE, TABLE_NAME");
}
/// <summary>
/// 获取表内容
/// </summary>
/// <param name="owner">拥有者</param>
/// <param name="name">表名</param>
public static void GetTableContent(string owner, string name)
{
ReturnTableContent = Select("SELECT COLUMN_NAME, DATA_TYPE, NULLABLE, DATA_LENGTH,(SELECT COMMENTS FROM ALL_COL_COMMENTS WHERE TABLE_NAME = '" + name + "' AND OWNER = '" + owner + "' AND COLUMN_NAME = a.COLUMN_NAME)COMMENTS FROM ALL_TAB_COLUMNS a WHERE TABLE_NAME = '" + name + "' AND OWNER = '" + owner + "' ORDER BY NULLABLE, COLUMN_NAME ");
}
#region 字段
/// <summary>
/// 要返回拥有者
/// </summary>
private static DataTable returnOwner;
public static DataTable ReturnOwner
{
get
{
return returnOwner;
} set
{
returnOwner = value; }
} /// <summary>
/// 返回表名
/// </summary>
private static DataTable returnTableName;
public static DataTable ReturnTableName
{
get
{
return returnTableName;
} set
{
returnTableName = value;
}
}
/// <summary>
/// 返回表内容
/// </summary>
private static DataTable returnTableContent; public static DataTable ReturnTableContent
{
get
{
return returnTableContent;
} set
{
returnTableContent = value;
}
}
#endregion
}

DBHelper

3、为了方便显示,要写一个Oracle的实体类,就是DataGrid显示的内容

public class TableVo : ObjectNotifyPropertyChanged
{
private string schema;//图表
private string name;//名称
private string comments;//注解
private string type;//类型
private string nullable;//是否可空
private string dataLength;//字段长度 public string Schema
{
get
{
return schema;
} set
{
schema = value;
RaisePropertyChanged("Schema");
}
} public string Name
{
get
{
return name;
} set
{
name = value;
RaisePropertyChanged("Name");
}
} public string Comments
{
get
{
return comments;
} set
{
comments = value;
RaisePropertyChanged("Comments");
}
} public string Type
{
get
{
return type;
} set
{
type = value;
RaisePropertyChanged("Type");
}
} public string Nullable
{
get
{
return nullable;
} set
{
nullable = value;
RaisePropertyChanged("Nullable");
}
} public string DataLength
{
get
{
return dataLength;
} set
{
dataLength = value;
RaisePropertyChanged("DataLength");
}
}
}

TableVo

4、由于小工具的左侧是一个TreeView,因此有一个TreeView实体,更便于管理和修改,而且此实体中一定要有父节点和子节点,已方便区分,和后期开发

public class PropertyNodeItem:ObjectNotifyPropertyChanged
{
private string icon;
private string parentName;
private string childrenName;
private bool isExpanded;
private List<PropertyNodeItem> children; public string Icon
{
get
{
return icon;
} set
{
icon = value;
RaisePropertyChanged("Icon");
}
} public string ParentName
{
get
{
return parentName;
} set
{
parentName = value;
RaisePropertyChanged("ParentName");
}
} public string ChildrenName
{
get
{
return childrenName;
} set
{
childrenName = value;
RaisePropertyChanged("ChildrenName");
}
} public bool IsExpanded
{
get
{
return isExpanded;
} set
{
isExpanded = value;
RaisePropertyChanged("IsExpanded");
}
} public List<PropertyNodeItem> Children
{
get
{
return children;
} set
{
children = value;
RaisePropertyChanged("Children");
}
} public PropertyNodeItem()
{
Children = new List<PropertyNodeItem>();
}
}

PropertyNodeItem

5、创建CS文件,需要同时创建两个,一个带有通知机制,一个不带有通知机制,创建方法无外乎就是拼接String,然后写入文件,很简单,就是在写的过程中,注意一下格式和转义字符

public class CreateVo
{
//不含通知机制
private static string pathVo1 = @"Dis/VoWithoutNotify";
//包含通知机制
private static string pathVo2 = @"Dis/VoWithNotify"; /// <summary>
/// 判断是否存在文件夹,不存在则创建
/// </summary>
/// <returns></returns>
public static bool CreateDirectory()
{
if (!Directory.Exists(pathVo1))
{
Directory.CreateDirectory(pathVo1);
}
if (!Directory.Exists(pathVo2))
{
Directory.CreateDirectory(pathVo2);
} if (Directory.Exists(pathVo1) && Directory.Exists(pathVo2))
return true;
else
return false;
} /// <summary>
/// 生成不含通知机制的VO
/// </summary>
/// <param name="NameSpace">命名空间</param>
/// <param name="name">文件名</param>
/// <param name="listName">列表</param>
public static void CreateVoNoINotifyPropertyChanged(string NameSpace,string name,List<TableVo> listName)
{
FileStream fs = new FileStream(pathVo1 + "/" + name + ".cs", FileMode.Create);
StreamWriter sw = new StreamWriter(fs);
sw.Write(CreateCSNoINotifyPropertyChanged(NameSpace,name,listName));
sw.Flush();
sw.Close();
fs.Close();
}
/// <summary>
/// 生成包含通知机制的VO
/// </summary>
/// <param name="NameSpace">命名空间</param>
/// <param name="name">文件名</param>
/// <param name="listName">列表</param>
public static void CreateVoWithINotifyPropertyChanged(string NameSpace, string name, List<TableVo> listName)
{
FileStream fs = new FileStream(pathVo2 + "/" + name + ".cs", FileMode.Create);
StreamWriter sw = new StreamWriter(fs);
sw.Write(CreateCSWithINotifyPropertyChanged(NameSpace, name, listName));
sw.Flush();
sw.Close();
fs.Close();
}
/// <summary>
/// 创建不含通知机制的CS文件
/// </summary>
/// <param name="NameSpace">命名空间</param>
/// <param name="name">文件名</param>
/// <param name="listName">列表</param>
/// <returns></returns>
private static string CreateCSNoINotifyPropertyChanged(string NameSpace,string name,List<TableVo> listName)
{
string content = "";
content += "using System;\r\n";
content += "using System.Collections.Generic;\r\n";
content += "using System.Linq;\r\n";
content += "using System.Text;\r\n";
content += "\r\n";
content += "namespace " + "" + NameSpace + "" + "\r\n";
content += "{\r\n";
content += " public class " + "" + name + "" + "\r\n";
content += " {\r\n";
foreach(TableVo s in listName)
{
content += " private string " + s.Name.ToLower() + ";" + "\r\n";
content += " /// <summary>\r\n";
content += " /// " + s.Comments + "\r\n";
content += " /// </summary>\r\n";
content += " public string " + s.Name.Substring(, ) + s.Name.Substring(, s.Name.Length-).ToLower() + "\r\n";
content += " {\r\n";
content += " get\r\n";
content += " {\r\n";
content += " return " + s.Name.ToLower() + ";\r\n";
content += " }\r\n";
content += "\r\n";
content += " set\r\n";
content += " {\r\n";
content += " " + s.Name.ToLower() + " = value;\r\n";
content += " }\r\n";
content += " }\r\n";
content += "\r\n";
}
content += " }\r\n";
content += "}\r\n";
return content;
}
/// <summary>
/// 创建包含通知机制的CS文件
/// </summary>
/// <param name="NameSpace">命名空间</param>
/// <param name="name">文件名</param>
/// <param name="listName">列表</param>
/// <returns></returns>
private static string CreateCSWithINotifyPropertyChanged(string NameSpace, string name, List<TableVo> listName)
{
string content = "";
content += "using System;\r\n";
content += "using System.Collections.Generic;\r\n";
content += "using System.Linq;\r\n";
content += "using System.Text;\r\n";
content += "using System.ComponentModel;\r\n";
content += "\r\n";
content += "namespace " + "" + NameSpace + "" + "\r\n";
content += "{\r\n";
content += " public class " + "" + name + "" + ":INotifyPropertyChanged" + "\r\n";
content += " {\r\n";
content += " public event PropertyChangedEventHandler PropertyChanged;\r\n";
content += " public void RaisePropertyChanged(string propertyName)\r\n";
content += " {\r\n";
content += " if (PropertyChanged != null)\r\n";
content += " {\r\n";
content += " PropertyChanged(this, new PropertyChangedEventArgs(propertyName));\r\n";
content += " }\r\n";
content += " }\r\n";
content += "\r\n";
foreach (TableVo s in listName)
{
content += " private string " + s.Name.ToLower() + ";" + "\r\n";
content += " /// <summary>\r\n";
content += " /// " + s.Comments + "\r\n";
content += " /// </summary>\r\n";
content += " public string " + s.Name.Substring(, ) + s.Name.Substring(, s.Name.Length - ).ToLower() + "\r\n";
content += " {\r\n";
content += " get\r\n";
content += " {\r\n";
content += " return " + s.Name.ToLower() + ";\r\n";
content += " }\r\n";
content += "\r\n";
content += " set\r\n";
content += " {\r\n";
content += " " + s.Name.ToLower() + " = value;\r\n";
content += " RaisePropertyChanged(\"" + s.Name.Substring(, ) + s.Name.Substring(, s.Name.Length - ).ToLower() + "\");\r\n";
content += " }\r\n";
content += " }\r\n";
content += "\r\n";
}
content += " }\r\n";
content += "}\r\n";
return content;
}
}

CreateVo

6、最后就是需要VM和XAML两个文件来将这些类文件组合到一起,写具体的逻辑关系了

给TreeView写一个SelectedItemChanged事件,来保证每次选中TreeView时能够获取到当前的选中项,刚开始想写TreeView的SelectedItem属性的,可是怎么也写不出来,后来发现因为这个属性是只读的,所以只能写SelectedItemChanged事件了。

<Border Style="{StaticResource BorderStyle}" BorderThickness="2" Background="{StaticResource BlueThemePageBackground}">
<Grid>
<Grid.RowDefinitions>
<RowDefinition Height="40"/>
<RowDefinition Height="*"/>
</Grid.RowDefinitions>
<Grid Grid.Row="0" Background="LightGray">
<TextBlock Text="Creator for Oracle database" FontSize="25" FontWeight="Black" Style="{StaticResource TextBlockStyle}" MouseLeftButtonDown="TextBlock_MouseLeftButtonDown"/>
</Grid>
<Grid Grid.Row="1" Margin="5">
<Grid.RowDefinitions>
<RowDefinition Height="Auto"/>
<RowDefinition Height="Auto"/>
<RowDefinition Height="*"/>
<RowDefinition Height="Auto"/>
</Grid.RowDefinitions>
<Grid Grid.Row="0" DataContext="{Binding ConnectionVo}" Visibility="{Binding DataContext.IsVisibility,RelativeSource={RelativeSource AncestorType=Window,Mode=FindAncestor}}">
<Grid.RowDefinitions>
<RowDefinition Height="*"/>
<RowDefinition Height="*"/>
</Grid.RowDefinitions>
<Grid.ColumnDefinitions>
<ColumnDefinition Width="*"/>
<ColumnDefinition Width="*"/>
<ColumnDefinition Width="*"/>
<ColumnDefinition Width="*"/>
<ColumnDefinition Width="*"/>
</Grid.ColumnDefinitions> <Border Grid.Row="0" Grid.Column="0" Grid.RowSpan="2" Grid.ColumnSpan="5" Style="{StaticResource BorderStyle}"/> <Line Grid.Row="0" Grid.ColumnSpan="5" Style="{StaticResource HorLineStyle}"/> <Line Grid.Column="0" Grid.RowSpan="2" Style="{StaticResource VerLineStyle}"/>
<Line Grid.Column="1" Grid.RowSpan="2" Style="{StaticResource VerLineStyle}"/>
<Line Grid.Column="2" Grid.RowSpan="2" Style="{StaticResource VerLineStyle}"/>
<Line Grid.Column="3" Grid.RowSpan="2" Style="{StaticResource VerLineStyle}"/>
<Line Grid.Column="4" Grid.RowSpan="2" Style="{StaticResource VerLineStyle}"/> <TextBlock Text="IP" Grid.Row="0" Grid.Column="0" Style="{StaticResource TextBlockStyle}"/>
<TextBox Text="{Binding ConnIP}" Grid.Row="1" Grid.Column="0" HorizontalAlignment="Stretch" Style="{StaticResource TextBoxStyle}"/> <TextBlock Text="Port" Grid.Row="0" Grid.Column="1" Style="{StaticResource TextBlockStyle}"/>
<TextBox Text="{Binding ConnPort}" Grid.Row="1" Grid.Column="1" HorizontalAlignment="Stretch" Style="{StaticResource TextBoxStyle}"/> <TextBlock Text="SID" Grid.Row="0" Grid.Column="2" Style="{StaticResource TextBlockStyle}"/>
<TextBox Text="{Binding ConnSid}" Grid.Row="1" Grid.Column="2" HorizontalAlignment="Stretch" Style="{StaticResource TextBoxStyle}"/> <TextBlock Text="User" Grid.Row="0" Grid.Column="3" Style="{StaticResource TextBlockStyle}"/>
<TextBox Text="{Binding ConnUser}" Grid.Row="1" Grid.Column="3" HorizontalAlignment="Stretch" Style="{StaticResource TextBoxStyle}"/> <TextBlock Text="PassWord" Grid.Row="0" Grid.Column="4" Style="{StaticResource TextBlockStyle}"/>
<TextBox Text="{Binding ConnPwd}" Grid.Row="1" Grid.Column="4" HorizontalAlignment="Stretch" Style="{StaticResource TextBoxStyle}"/>
</Grid> <Button Content="连接" Command="{Binding LinkCommand}" Grid.Row="1" Visibility="{Binding IsVisibility}" Style="{StaticResource ButtonStyleForSumbit}"/> <Grid Grid.Row="2">
<Grid.ColumnDefinitions>
<ColumnDefinition Width="300"/>
<ColumnDefinition Width="*"/>
</Grid.ColumnDefinitions>
<TreeView x:Name="tv" ItemsSource="{Binding TreeList}" BorderBrush="{StaticResource BlueThemeBordBursh}" ScrollViewer.HorizontalScrollBarVisibility="Disabled">
<TreeView.ItemContainerStyle>
<Style TargetType="{x:Type TreeViewItem}">
<Setter Property="IsExpanded" Value="{Binding IsExpanded}"/>
</Style>
</TreeView.ItemContainerStyle>
<TreeView.ItemTemplate>
<HierarchicalDataTemplate ItemsSource="{Binding Children}">
<StackPanel Orientation="Horizontal">
<Image VerticalAlignment="Center" Source="{Binding Icon}" Width="18" Height="18" Margin="0,2"/>
<TextBlock x:Name="tb" VerticalAlignment="Center" Text="{Binding ChildrenName}" Style="{StaticResource TextBlockStyle}"/>
</StackPanel>
</HierarchicalDataTemplate>
</TreeView.ItemTemplate>
<i:Interaction.Triggers>
<i:EventTrigger EventName="SelectedItemChanged">
<Custom:EventToCommand Command="{Binding SelectChangedCommand}" CommandParameter="{Binding RelativeSource={RelativeSource Mode=FindAncestor,AncestorType=TreeView},Path=SelectedItem}"/>
</i:EventTrigger>
</i:Interaction.Triggers>
</TreeView> <DataGrid Grid.Column="1" ItemsSource="{Binding TableNameGridList}" SelectedItem="{Binding SelectTableName}" Visibility="{Binding VisibilityName}" HorizontalAlignment="Stretch" CanUserAddRows="False" AutoGenerateColumns="False" Style="{StaticResource GreenThemeDataGridStyle}">
<DataGrid.CellStyle>
<Style TargetType="DataGridCell">
<Setter Property="ToolTipService.ToolTip" Value="{Binding RelativeSource={RelativeSource Mode=Self},Path=Content.Text}"/>
</Style>
</DataGrid.CellStyle>
<DataGrid.Columns>
<DataGridTextColumn Header="Schema" Width="*" Binding="{Binding Schema,Mode=OneWay}"/>
<DataGridTextColumn Header="Type" Width="*" Binding="{Binding Type,Mode=OneWay}"/>
<DataGridTextColumn Header="Table Name" Width="*" Binding="{Binding Name,Mode=OneWay}"/>
<DataGridTextColumn Header="Comments" Width="*" Binding="{Binding Comments,Mode=OneWay}"/>
</DataGrid.Columns>
</DataGrid> <DataGrid Grid.Column="1" ItemsSource="{Binding TableContentGridList}" Visibility="{Binding VisibilityContent}" HorizontalAlignment="Stretch" CanUserAddRows="False" AutoGenerateColumns="False" Style="{StaticResource GreenThemeDataGridStyle}">
<DataGrid.CellStyle>
<Style TargetType="DataGridCell">
<Setter Property="ToolTipService.ToolTip" Value="{Binding RelativeSource={RelativeSource Mode=Self},Path=Content.Text}"/>
</Style>
</DataGrid.CellStyle>
<DataGrid.Columns>
<DataGridTextColumn Header="Column Name" Width="*" Binding="{Binding Name,Mode=OneWay}"/>
<DataGridTextColumn Header="Column Comments" Width="*" Binding="{Binding Comments,Mode=OneWay}"/>
<DataGridTextColumn Header="Type" Width="*" Binding="{Binding Type,Mode=OneWay}"/>
<DataGridTextColumn Header="Nullable" Width="*" Binding="{Binding Nullable,Mode=OneWay}"/>
<DataGridTextColumn Header="DataLength" Width="*" Binding="{Binding DataLength,Mode=OneWay}"/>
</DataGrid.Columns>
</DataGrid>
</Grid> <StackPanel Grid.Row="3" HorizontalAlignment="Center" Orientation="Horizontal">
<TextBlock Text="NameSpace" FontStyle="Italic" FontWeight="Bold" Foreground="Red" Style="{StaticResource TextBlockStyle}"/>
<TextBox Width="150" Text="{Binding Ns}" Style="{StaticResource TextBoxStyle}" Margin="5,0,100,0"/>
<Button Content="创建VO" Command="{Binding CreateVoCommand}" CommandParameter="{Binding ElementName=tv,Path=SelectedItem}" Style="{StaticResource ButtonStyleForSumbit}" Margin="0,0,30,0"/>
<Button Content="关闭" Command="{Binding CloseCommand}" Style="{StaticResource ButtonStyleForCancel}"/>
</StackPanel>
</Grid>
</Grid>
</Border>

XAML

public class MainWindowVM : ViewModelBase
{
public MainWindowVM()
{
SetConnVo();
} #region 字段
/// <summary>
/// 命名空间
/// </summary>
private string ns = "";
public string Ns
{
get
{
return ns;
} set
{
ns = value;
RaisePropertyChanged("Ns");
}
} /// <summary>
/// 连接类
/// </summary>
private ConnVo connectionVo = new ConnVo();
public ConnVo ConnectionVo
{
get
{
return connectionVo;
} set
{
connectionVo = value;
RaisePropertyChanged("ConnectionVo");
}
} /// <summary>
/// 表
/// </summary>
private TableVo table;
public TableVo Table
{
get
{
return table;
} set
{
table = value;
RaisePropertyChanged("Table");
}
} /// <summary>
/// 左侧列表
/// </summary>
private ObservableCollection<PropertyNodeItem> treeList = new ObservableCollection<PropertyNodeItem>();
public ObservableCollection<PropertyNodeItem> TreeList
{
get
{
return treeList;
} set
{
treeList = value;
RaisePropertyChanged("TreeList");
}
} /// <summary>
/// 原始列表
/// </summary>
private List<PropertyNodeItem> orgList = new List<PropertyNodeItem>();
public List<PropertyNodeItem> OrgList
{
get
{
return orgList;
} set
{
orgList = value;
RaisePropertyChanged("OrgList");
}
} /// <summary>
/// 父列表
/// </summary>
private List<string> ownerList = new List<string>();
public List<string> OwnerList
{
get
{
return ownerList;
} set
{
ownerList = value;
RaisePropertyChanged("OwnerList");
}
} /// <summary>
/// 连接成功以后,就隐藏上边的部分
/// </summary>
private Visibility isVisibility = Visibility.Visible;
public Visibility IsVisibility
{
get
{
return isVisibility;
} set
{
isVisibility = value;
RaisePropertyChanged("IsVisibility");
}
} /// <summary>
/// 表名选中项
/// </summary>
private TableVo selectTableName = new TableVo();
public TableVo SelectTableName
{
get
{
return selectTableName;
} set
{
selectTableName = value;
RaisePropertyChanged("SelectTableName");
}
} /// <summary>
/// 表名list
/// </summary>
private ObservableCollection<TableVo> tableNameGridList = new ObservableCollection<TableVo>();
public ObservableCollection<TableVo> TableNameGridList
{
get
{
return tableNameGridList;
} set
{
tableNameGridList = value;
RaisePropertyChanged("TableNameGridList");
}
}
/// <summary>
/// 表内容
/// </summary>
private ObservableCollection<TableVo> tableContentGridList = new ObservableCollection<TableVo>();
public ObservableCollection<TableVo> TableContentGridList
{
get
{
return tableContentGridList;
} set
{
tableContentGridList = value;
RaisePropertyChanged("TableContentGridList");
}
} /// <summary>
/// 表名是否显示
/// </summary>
private Visibility visibilityName = Visibility.Collapsed;
public Visibility VisibilityName
{
get
{
return visibilityName;
} set
{
visibilityName = value;
RaisePropertyChanged("VisibilityName");
}
} /// <summary>
/// 表内容是否显示
/// </summary>
private Visibility visibilityContent = Visibility.Collapsed;
public Visibility VisibilityContent
{
get
{
return visibilityContent;
} set
{
visibilityContent = value;
RaisePropertyChanged("VisibilityContent");
}
}
#endregion #region 函数
/// <summary>
/// 给连接的东西赋值
/// </summary>
private void SetConnVo()
{
ConnectionVo.ConnIP = "*****";
ConnectionVo.ConnPort = "";
ConnectionVo.ConnSid = "*****";
ConnectionVo.ConnUser = "swzg";
ConnectionVo.ConnPwd = "*****";
}
/// <summary>
/// DataTable转换到List
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
private void DataTableToList(DataTable dt)
{
//获取最原始的List
var orgList = (from dataTable in dt.AsEnumerable()
select new PropertyNodeItem()
{
ParentName = dataTable["OWNER"].ToString(),
ChildrenName = dataTable["TABLE_NAME"].ToString()
}).ToList();
OrgList.AddRange(orgList); DataTable newdt = new DataView(dt.Columns["OWNER"].Table).ToTable(true, "OWNER");//去除重复,只留下OWNER
//只获得Owner列表
var newList = (from dataTable in newdt.AsEnumerable()
select dataTable["OWNER"].ToString());
OwnerList.AddRange(newList);
}
/// <summary>
/// 给Treeview赋值
/// </summary>
/// <param name="listOrg">原始列表</param>
/// <param name="listNew">Own列表</param>
private void SetTree(List<PropertyNodeItem> listOrg, List<string> listNew)
{
TreeList.Clear();
for (int i = ; i < listNew.Count; i++)
{
PropertyNodeItem node = new PropertyNodeItem()
{
ParentName = listNew[i].ToString(),
ChildrenName = listNew[i].ToString(),
Icon = @"../Icon/father.ico",
IsExpanded = false
};
ForeachPropertyNode(node, listOrg, listNew[i].ToString());
TreeList.Add(node);
}
}
/// <summary>
/// 向父节点中添加子节点
/// </summary>
/// <param name="node">节点</param>
/// <param name="listOrg">原始的列表</param>
/// <param name="parentName">父节点名称</param>
private void ForeachPropertyNode(PropertyNodeItem node, List<PropertyNodeItem> listOrg, string parentName)
{
var listChildren = (from dataTable in listOrg.AsEnumerable()
where dataTable.ParentName == parentName
select new PropertyNodeItem()
{
ParentName = dataTable.ParentName.ToString(),
ChildrenName = dataTable.ChildrenName.ToString(),
Icon = @"../Icon/children.ico",
IsExpanded = false
});
node.Children.AddRange(listChildren);
} /// <summary>
/// 获取表名列
/// </summary>
/// <param name="owner"></param>
private void GetTableNameList(string owner)
{
TableNameGridList.Clear();
DBHelper.GetTableName(owner);
var list = (from dt in DBHelper.ReturnTableName.AsEnumerable()
select new TableVo()
{
Schema = dt["OWNER"].ToString(),
Name = dt["TABLE_NAME"].ToString(),
Type = dt["TABLE_TYPE"].ToString(),
Comments = dt["COMMENTS"].ToString()
}).ToList();
for (int i = ; i < list.Count; i++)
{
TableVo tablevo = new TableVo()
{
Schema = list[i].Schema,
Comments = list[i].Comments,
Name = list[i].Name,
Type = list[i].Type
};
TableNameGridList.Add(tablevo);
}
}
/// <summary>
/// 获取表内容
/// </summary>
/// <param name="owner"></param>
/// <param name="name"></param>
private void GetTableContentList(string owner, string name)
{
TableContentGridList.Clear();
DBHelper.GetTableContent(owner, name);
var list = (from dt in DBHelper.ReturnTableContent.AsEnumerable()
select new TableVo()
{
Name = dt["COLUMN_NAME"].ToString(),
Type = dt["DATA_TYPE"].ToString(),
Comments = dt["COMMENTS"].ToString(),
Nullable = dt["NULLABLE"].ToString(),
DataLength = dt["DATA_LENGTH"].ToString()
}).ToList();
for (int i = ; i < list.Count; i++)
{
TableVo tablevo = new TableVo()
{
Name = list[i].Name,
Type = list[i].Type,
Comments = list[i].Comments,
Nullable = list[i].Nullable,
DataLength = list[i].DataLength
};
TableContentGridList.Add(tablevo);
}
} /// <summary>
/// 获得数据库里的字段名
/// </summary>
/// <returns></returns>
private List<string> GetListName()
{
List<string> list = new List<string>();
if (TableContentGridList.Count > )
{
for (int i = ; i < TableContentGridList.Count; i++)
{
list.Add(TableContentGridList[i].Name.ToLower());
}
return list;
}
return null;
}
#endregion #region 按钮
/// <summary>
/// 连接按钮
/// </summary>
private RelayCommand linkCommand;
public RelayCommand LinkCommand
{
get
{
return linkCommand = new RelayCommand(() =>
{
if (DBHelper.OpenConnection(ConnectionVo.ConnIP, ConnectionVo.ConnPort, ConnectionVo.ConnSid, ConnectionVo.ConnUser, ConnectionVo.ConnPwd))
{
DataTableToList(DBHelper.ReturnOwner);
SetTree(OrgList, OwnerList);
IsVisibility = Visibility.Collapsed;
}
else
{
MessageBox.Show("连接失败!");
}
});
}
} /// <summary>
/// 关闭按钮
/// </summary>
private RelayCommand closeCommand;
public RelayCommand CloseCommand
{
get
{
return closeCommand = new RelayCommand(() =>
{
DBHelper.CloseConnection();
Messenger.Default.Send<string>(null, "WinClose");
});
}
} /// <summary>
/// 创建VO按钮
/// </summary>
private RelayCommand<PropertyNodeItem> createVoCommand;
public RelayCommand<PropertyNodeItem> CreateVoCommand
{
get
{
return createVoCommand = new RelayCommand<PropertyNodeItem>(p =>
{
if (p == null || (p.ParentName.Equals(p.ChildrenName) && string.IsNullOrEmpty(SelectTableName.Name)))
return;
if (p.ParentName.Equals(p.ChildrenName) && !string.IsNullOrEmpty(SelectTableName.Name))
{
GetTableContentList(p.ParentName, SelectTableName.Name);
//创建文件夹
CreateVo.CreateDirectory();
CreateVo.CreateVoNoINotifyPropertyChanged(Ns != "" ? Ns : "test", SelectTableName.Name, TableContentGridList.ToList());
CreateVo.CreateVoWithINotifyPropertyChanged(Ns != "" ? Ns : "test", SelectTableName.Name, TableContentGridList.ToList());
}
else
{
//创建文件夹
CreateVo.CreateDirectory();
CreateVo.CreateVoNoINotifyPropertyChanged(Ns != "" ? Ns : "test", p.ChildrenName, TableContentGridList.ToList());
CreateVo.CreateVoWithINotifyPropertyChanged(Ns != "" ? Ns : "test", p.ChildrenName, TableContentGridList.ToList());
} });
}
}
#endregion #region 事件
/// <summary>
/// Treeview切换事件
/// </summary>
private RelayCommand<PropertyNodeItem> selectChangedCommand;
public RelayCommand<PropertyNodeItem> SelectChangedCommand
{
get
{
return selectChangedCommand = new RelayCommand<PropertyNodeItem>(p =>
{
if (p.ParentName.Equals(p.ChildrenName))
{
GetTableNameList(p.ParentName);
VisibilityName = Visibility.Visible;
VisibilityContent = Visibility.Collapsed;
}
if (!p.ParentName.Equals(p.ChildrenName))
{
GetTableContentList(p.ParentName, p.ChildrenName);
VisibilityName = Visibility.Collapsed;
VisibilityContent = Visibility.Visible;
}
});
}
}
#endregion
}

ViewModel

注意事项:

在最开始的连接Oracle中,我用的是OleDbConnection,连接语句里用到了“Provider=OraOLEDB.Oracle.1;”,也就是用到了Oracle的驱动,这个在没有注册的时候会弹出

未在本地计算机注册“OraOLEDB.Oracle.1”,在我的机器上注册成功,好使了,但是在我同事的机器上,注册成功了,依然会有这个提示,不能成功连接数据库,因此,最终,选用了Oracle.ManagedDataAccess.DLL文件,它是oracle官方的托管驱动,有6M多,但是有了这个文件以后,直接使用OracleConnection就不会爆出连接问题了。

总结:做这个小工具没有什么难度,用到的方法有Linq、Oracle的Select语句、StreamWriter写入文件等,但是对于我们的开发还是蛮实用的,希望看到的朋友可以根据你们的项目需求,做出其他版本的小工具,大家一起探讨、成长。

GitHub源码

上一篇:iOS中的CocoaPods用法及常用命令


下一篇:c#danliemosih