/// <summary>
/// 获取数据库类型
/// </summary>
/// <param name="edt">枚举数据库类型</param>
/// <returns></returns>
public static string GetDbTypeStr(EnumDbType edt)
{
string result = string.Empty;
switch (edt)
{
case EnumDbType.SqlServer:
result = "sqlserver";
break;
case EnumDbType.Oracle:
result = "oracle";
break;
case EnumDbType.MySql:
result = "mysql";
break;
case EnumDbType.DB2:
result = "db2";
break;
case EnumDbType.SQLite:
result = "sqlite";
break;
default:
result = string.Empty;
break;
}
return(result);
}
/// <summary>
/// Get Primary Key In Table
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public static List<string> GetPrimayKeys(string tableName, EnumDbType dbType)
{
string sqlstr = string.Empty;
if (dbType == EnumDbType.Oracle)
{
sqlstr =
string.Format(
@"SELECT * FROM user_cons_columns ucc WHERE UPPER(ucc.table_name)='{0}' AND UPPER(ucc.constraint_name)=CONCAT('PK_','{1}')",
tableName, tableName);
}
else if (dbType == EnumDbType.SqlServer)
{
sqlstr =
string.Format(@" SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='{0}'",
tableName);
}
DataTable dtPrimaryKeys = DBHelper.GetDataTable(sqlstr);
var primayKeys = new List<string>();
foreach (DataRow item in dtPrimaryKeys.Rows)
{
string columnName = item["column_name"].ToString();
if (!primayKeys.Contains(columnName))
{
primayKeys.Add(columnName);
}
}
return primayKeys;
}
public static void Generate(DataGridView dgvTables, bool isPascal, EnumDbType dbType)
{
var listSql = new List<string>();
List<Table> list = TableControl.GetCheckedTableName(dgvTables, isPascal);
if (list.Count == 0)
{
MessageBox.Show(ConstantUtil.CheckedNoTable);
return;
}
var ds = new DataSet();
foreach (Table item in list)
{
DataTable dtColumns = ColumnControl.GetTableColumnsByTableName(item.TableName, dbType);
dtColumns.TableName = item.TabPascalName + "$" + item.Comments;
bool b = ColumnControl.GetColRelaPascalName(dtColumns);
if (isPascal && !b)
{
MessageBox.Show(ConstantUtil.NoRelaPascalCol);
return;
}
ds.Tables.Add(dtColumns);
}
CreateWordUtil.Create(ds, dbType);
}
public DALControl(string dbTableName, string PascalTableName, bool isPascal, string preNS,
string preClass, string preModel,
bool isLog, CheckedListBox checkedListBox1, EnumDbType dbType)
{
this.TableName = dbTableName;
this.PascalTableName = PascalTableName;
this.IsPascal = isPascal;
this.PreNs = preNS;
this.SbTemp = new StringBuilder();
this.PreClass = preClass;
this.PreModel = preModel;
this.IsLog = isLog;
this.CheckedListBox1 = checkedListBox1;
this.DbType = dbType;
if (this.DbType == EnumDbType.Oracle)
{
dbHelper = "DbHelperOracle";
parameter = "OracleParameter";
paramsHelper = "ParamsHelperOracle";
}
else if (this.DbType == EnumDbType.SqlServer)
{
dbHelper = "DbHelperSqlServer";
parameter = "SqlParameter";
paramsHelper = "ParamsHelperSqlServer";
}
listPrimaryKey = TableControl.GetPrimayKeys(dbTableName, this.DbType);
dtColumns = ColumnControl.GetTableColumnsByTableName(dbTableName, this.DbType);
}
/// <summary>
/// Gets the connection string.
/// </summary>
/// <param name="zoneName">Name of the zone.</param>
/// <param name="dbType">Type of the db.</param>
/// <returns></returns>
public string GetConnectionString(string zoneName, EnumDbType dbType)
{
if (string.IsNullOrEmpty(zoneName))
{
zoneName = CurrentZoneName;
}
if (string.IsNullOrEmpty(zoneName))
{
zoneName = "Share";
}
string strDbType = ConvertHelper.ConvertEnumToString(dbType);
string key = BuildKey(zoneName, strDbType);
if (_connectionDic.ContainsKey(key))
{
return(_connectionDic[key]);
}
else
{
//如果没有对应区的连接就获取默认区
key = BuildKey("Share", strDbType);
if (_connectionDic.ContainsKey(key))
{
return(_connectionDic[key]);
}
else
{
//LogHelper.Insert("Can't find connectionstring,zonename:" + zoneName + ",db:" + dbType.ToString(), LogType.Error);
return(string.Empty);
}
}
}
public static void Generate(DataGridView dgvTables, bool isPascal, EnumDbType dbType)
{
var listSql = new List <string>();
List <Table> list = TableControl.GetCheckedTableName(dgvTables, isPascal);
if (list.Count == 0)
{
MessageBox.Show(ConstantUtil.CheckedNoTable);
return;
}
var ds = new DataSet();
foreach (Table item in list)
{
DataTable dtColumns = ColumnControl.GetTableColumnsByTableName(item.TableName, dbType);
dtColumns.TableName = item.TabPascalName + "$" + item.Comments;
bool b = ColumnControl.GetColRelaPascalName(dtColumns);
if (isPascal && !b)
{
MessageBox.Show(ConstantUtil.NoRelaPascalCol);
return;
}
ds.Tables.Add(dtColumns);
}
CreateWordUtil.Create(ds, dbType);
}
/// <summary>
/// Get Primary Key In Table
/// </summary>
/// <param name="tableName"></param>
/// <returns></returns>
public static List <string> GetPrimayKeys(string tableName, EnumDbType dbType)
{
string sqlstr = string.Empty;
if (dbType == EnumDbType.Oracle)
{
sqlstr =
string.Format(
@"SELECT * FROM user_cons_columns ucc WHERE UPPER(ucc.table_name)='{0}' AND UPPER(ucc.constraint_name)=CONCAT('PK_','{1}')",
tableName, tableName);
}
else if (dbType == EnumDbType.SqlServer)
{
sqlstr =
string.Format(@" SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='{0}'",
tableName);
}
DataTable dtPrimaryKeys = DBHelper.GetDataTable(sqlstr);
var primayKeys = new List <string>();
foreach (DataRow item in dtPrimaryKeys.Rows)
{
string columnName = item["column_name"].ToString();
if (!primayKeys.Contains(columnName))
{
primayKeys.Add(columnName);
}
}
return(primayKeys);
}
public static string CreateQueryFolk(EnumDbType dbType)
{
string sql = string.Empty;
switch (dbType)
{
case EnumDbType.SqlServer:
sql = @"
SELECT Id, Name, Birthday, Address, Phone, EmploymentOn FROM Folks
WHERE
EmploymentOn >= CONVERT(DATETIME, @StartDate, 102)
AND EmploymentOn <= CONVERT(DATETIME, @EndDate, 102)
";
break;
case EnumDbType.Postgres:
sql = @"
SELECT ""Id"", ""Name"", ""Birthday"", ""Address"", ""Phone"", ""EmploymentOn"" FROM public.""Folks""
WHERE ""EmploymentOn"" >= to_date(@StartDate, 'YYYY-MM-DD')
AND ""EmploymentOn"" <= to_date(@EndDate, 'YYYY-MM-DD');
";
break;
}
return(sql);
}
/// <summary>
/// 获取数据库类型
/// </summary>
/// <param name="strDbType">字符串数据库类型</param>
/// <returns></returns>
public static EnumDbType GetDbType(string strDbType)
{
EnumDbType edt = EnumDbType.Null;
strDbType = strDbType.ToLower();
if (strDbType.IndexOf("sqlserver") >= 0)
{
edt = EnumDbType.SqlServer;
}
else if (strDbType.IndexOf("oracle") >= 0)
{
edt = EnumDbType.Oracle;
}
else if (strDbType.IndexOf("mysql") >= 0)
{
edt = EnumDbType.MySql;
}
else if (strDbType.IndexOf("db2") >= 0)
{
edt = EnumDbType.DB2;
}
else if (strDbType.IndexOf("sqlite") >= 0)
{
edt = EnumDbType.SQLite;
}
else
{
edt = EnumDbType.Null;
}
return(edt);
}
public ModelControl(string dbTableName, string tableName, string preNS, string preModel, bool isPascal, EnumDbType dbType)
{
this.dbTableName = dbTableName;
this.tableName = tableName;
this.preNS = preNS;
this.preModel = preModel;
this.isPascal = isPascal;
this.dbType = dbType;
}
public ModelControl(string dbTableName, string tableName, string preNS, string preModel, bool isPascal, EnumDbType dbType)
{
this.dbTableName = dbTableName;
this.tableName = tableName;
this.preNS = preNS;
this.preModel = preModel;
this.isPascal = isPascal;
this.dbType = dbType;
}
public static void SwithDb(EnumDbType dbType)
{
switch (dbType)
{
case EnumDbType.Oracle:
DBHelper.ToOracle();
break;
case EnumDbType.SqlServer:
DBHelper.ToSqlServer();
break;
}
}
public static void SwithDb(EnumDbType dbType)
{
switch (dbType)
{
case EnumDbType.Oracle:
DBHelper.ToOracle();
break;
case EnumDbType.SqlServer:
DBHelper.ToSqlServer();
break;
}
}
/// <summary>
/// ��̬���ݷ��ʶ���
/// </summary>
/// <param name="connectionString">���������ַ���</param>
/// <param name="dbType">���ݿ�����</param>
/// <returns>���ݷ��ʽӿ�</returns>
public static IDBAccess Instance(string connectionString, EnumDbType dbType)
{
Guard.ArgumentNotNullOrEmptyString("���������ַ���", connectionString, true);
lock (typeof(DatabaseFactory))
{
IDBAccess instance = objCache.ContainsKey(connectionString) ? objCache[connectionString] : null;
if (instance == null)
{
instance = new DatabaseFactory().CreateInstance(dbType,connectionString);
if (instance != null)
objCache[connectionString] = instance;
}
return instance;
}
}
/// <summary>
/// Create DbConnection
/// </summary>
/// <param name="dbType">DB type</param>
/// <param name="dbConnection">DB connection name</param>
/// <returns>IDbConnection instance</returns>
public async Task <IDbConnection> CreateAsync(EnumDbType dbType, string dbConnection)
{
string connectionString = this.appSettings.ConnectionStrings.GetType().GetProperty(dbConnection).GetValue(this.appSettings.ConnectionStrings, null) as string;
switch (dbType)
{
case EnumDbType.SqlServer:
return(await Task.FromResult(new SqlConnection(connectionString)));
case EnumDbType.Postgres:
return(await Task.FromResult(new NpgsqlConnection(connectionString)));
default:
throw new NotSupportedException();
}
}
public static string CreateMergeFolk(EnumDbType dbType)
{
string sql = string.Empty;
switch (dbType)
{
case EnumDbType.SqlServer:
sql = @"
MERGE INTO Folks a USING
(
SELECT @Id as Id,
@Name as Name,
@Birthday as Birthday,
@Address as Address,
@Phone as Phone,
@EmploymentOn as EmploymentOn
) b ON
(a.Id = b.Id)
WHEN MATCHED THEN UPDATE SET
a.Name = b.Name,
a.Birthday = b.Birthday,
a.Address = b.Address,
a.Phone = b.Phone,
a.EmploymentOn = b.EmploymentOn
WHEN NOT MATCHED THEN INSERT(Id, Name, Birthday, Address, Phone, EmploymentOn)
VALUES(@Id, @Name, @Birthday, @Address, @Phone, @EmploymentOn);
";
break;
case EnumDbType.Postgres:
sql = @"
INSERT INTO ""Folks""(""Id"", ""Name"", ""Birthday"", ""Address"", ""Phone"", ""EmploymentOn"")
VALUES(@Id, @Name, @Birthday, @Address, @Phone, @EmploymentOn)
ON CONFLICT (""Id"")
DO UPDATE SET
""Name"" = @Name,
""Birthday"" = @Birthday,
""Address"" = @Address,
""Phone"" = @Phone,
""EmploymentOn"" = @EmploymentOn;
";
break;
}
return(sql);
}
public static DataTable GetColumnAndPascalNameFromXml(string tableName,EnumDbType dbType)
{
DataTable dtColumns = ColumnControl.GetTableColumnsByTableName(tableName, dbType);
XmlControl.CreateCorrespondingXmlWhenNotExist();
var dic = XmlControl.ReadXml();
for (int i = 0; i < dtColumns.Rows.Count; i++)
{
string name = dtColumns.Rows[i]["Column_Name"].ToString();
if (dic.ContainsKey(name))
{
dtColumns.Rows[i]["PascalName"] = dic[name];
}
}
dtColumns.AcceptChanges();
return dtColumns;
}
public static DataTable GetColumnAndPascalNameFromXml(string tableName, EnumDbType dbType)
{
DataTable dtColumns = ColumnControl.GetTableColumnsByTableName(tableName, dbType);
XmlControl.CreateCorrespondingXmlWhenNotExist();
var dic = XmlControl.ReadXml();
for (int i = 0; i < dtColumns.Rows.Count; i++)
{
string name = dtColumns.Rows[i]["Column_Name"].ToString();
if (dic.ContainsKey(name))
{
dtColumns.Rows[i]["PascalName"] = dic[name];
}
}
dtColumns.AcceptChanges();
return(dtColumns);
}
/// <summary>
/// Change DataBase Type
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void rbOracle_CheckedChanged(object sender, EventArgs e)
{
dbType = rbOracle.Checked ? EnumDbType.Oracle : EnumDbType.SqlServer;
if (rbOracle.Checked)
{
this.rbPascal.Checked = true;
this.rbOri.Checked = false;
}
else
{
this.rbOri.Checked = true;
this.rbPascal.Checked = false;
}
ConfigUtil.SwithDb(dbType);
XmlControl.CreateCorrespondingXmlWhenNotExist();
Dictionary <string, string> dic = XmlControl.ReadXml();
dgvDataTables.DataSource = TableControl.GetTables(dic, string.Empty, dbType);
dgvDataTables.AutoGenerateColumns = false;
}
public static DataTable GetTableColumnsByTableName(string tableName, EnumDbType dbType)
{
string sqlstr = String.Empty;
if (dbType == EnumDbType.Oracle)
{
sqlstr = String.Format(
@"SELECT utc.*,ucc.comments,'' PascalName FROM user_tab_columns utc
INNER JOIN user_col_comments ucc ON utc.table_name=ucc.table_name AND utc.column_name=ucc.column_name
WHERE UPPER(utc.table_name)='{0}'", tableName);
}
else if (dbType == EnumDbType.SqlServer)
{
sqlstr = String.Format(
@"Declare @tblName nvarchar(1000)
set @tblName='{0}'
declare @TblID int
set @TblID=(select [object_id] as tblID from sys.all_objects where [type] ='U' and [name]<>'dtproperties' and [name]=@tblName)
select syscolumns.name as Column_Name,
systypes.name as DATA_TYPE,
syscolumns.length as DATA_LENGTH,
syscolumns.prec as DATA_PRECISION, --xprec
syscolumns.scale as DATA_SCALE, --xscale
(SELECT [value] FROM ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', object_name(@TblID), 'column', syscolumns.name) as e where e.name='MS_Description') as comments,
'' PascalName
from sysColumns
left join sysTypes on sysTypes.xtype = sysColumns.xtype and sysTypes.xusertype = sysColumns.xusertype
left join sysobjects on sysobjects.id = syscolumns.cdefault and sysobjects.type='D'
left join syscomments on syscomments.id = sysobjects.id
where syscolumns.id=@TblID
ORDER BY Column_Name", tableName);
}
var dtColumns = DBHelper.GetDataTable(sqlstr);
return(dtColumns);
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="enumDbType">数据库类型枚举</param>
/// <param name="strConnStr">链接字符串</param>
public DbAccess(EnumDbType enumDbType, string strConnStr)
{
connStr = strConnStr;
dbType = enumDbType;
}
/// <summary>
/// bind Table Data
/// </summary>
/// <param name="dic"></param>
public static DataTable GetTables(Dictionary <string, string> dic, string filterValue, EnumDbType dbType)
{
string strSql = string.Empty;
if (dbType == EnumDbType.Oracle)
{
strSql =
"SELECT ut.table_name TableName ,utc.comments,'' PascalName FROM user_tables ut " +
"INNER JOIN user_tab_comments utc ON ut.table_name=utc.table_name";
if (filterValue != string.Empty)
{
strSql += " WHERE LOWER(ut.table_name) LIKE '" + filterValue + "%'";
}
strSql += " ORDER BY ut.table_name";
}
else if (dbType == EnumDbType.SqlServer)
{
strSql =
@"SELECT t.[name] as TableName,ep.[value] comments,'' PascalName FROM sys.tables t
LEFT JOIN sys.extended_properties ep
ON t.[object_id]=ep.major_id AND ep.minor_id=0";
if (filterValue != string.Empty)
{
strSql += " WHERE LOWER(ep.[name]) LIKE '" + filterValue + "%'";
}
}
DataTable dt = DBHelper.GetDataTable(strSql);
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dic.ContainsKey(dt.Rows[i]["TableName"].ToString()))
{
dt.Rows[i]["PascalName"] = dic[dt.Rows[i]["TableName"].ToString()];
}
}
return(dt);
}
/// <summary>
/// if int equal int.MinValue,update empty string
/// </summary>
/// <param name="sbTemp"></param>
/// <param name="colName"></param>
/// <param name="type"></param>
/// <param name="dbType"></param>
private void updateEmptyCol(StringBuilder sbTemp, string colName, string type, EnumDbType dbType)
{
this.SbTemp.AppendFormat("\r\nif (model.{0} == {1}.MinValue)", colName, type);
this.SbTemp.Append("\r\n{");
this.SbTemp.AppendFormat("\r\nsbParam.Append(\"{0}='',\");", colName);
this.SbTemp.Append("\r\n}");
this.SbTemp.Append("\r\nelse");
this.SbTemp.Append("\r\n{");
if (this.DbType == EnumDbType.Oracle)
{
this.SbTemp.AppendFormat("\r\nsbParam.Append(\"{0}=:{1},\");", colName, colName);
this.SbTemp.AppendFormat("\r\nparam.Add(\":{0}\", model.{1});", colName, colName);
}
else if (this.DbType == EnumDbType.SqlServer)
{
this.SbTemp.AppendFormat("\r\nsbParam.Append(\"{0}=@{1},\");", colName, colName);
this.SbTemp.AppendFormat("\r\nparam.Add(\"@{0}\", model.{1});", colName, colName);
}
this.SbTemp.Append("\r\n}");
}
/// <summary>
/// Change DataBase Type
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void rbOracle_CheckedChanged(object sender, EventArgs e)
{
dbType = rbOracle.Checked ? EnumDbType.Oracle : EnumDbType.SqlServer;
if (rbOracle.Checked)
{
this.rbPascal.Checked = true;
this.rbOri.Checked = false;
}
else
{
this.rbOri.Checked = true;
this.rbPascal.Checked = false;
}
ConfigUtil.SwithDb(dbType);
XmlControl.CreateCorrespondingXmlWhenNotExist();
Dictionary<string, string> dic = XmlControl.ReadXml();
dgvDataTables.DataSource = TableControl.GetTables(dic, string.Empty, dbType);
dgvDataTables.AutoGenerateColumns = false;
}
/// <summary>
/// Create Word Document
/// </summary>
/// <param name="ds">Every DataTable in DataSet Generate a table</param>
/// <param name="dbType"> </param>
/// <returns></returns>
public static string Create(DataSet ds, EnumDbType dbType)
{
string msg;
Object nothing = Missing.Value;
Application wordApp = new ApplicationClass();
wordApp.NormalTemplate.Saved = true; //Avoid PopUp normal.dot Dialog
Document wordDoc = wordApp.Documents.Add(ref nothing, ref nothing, ref nothing, ref nothing);
object filePath = @"C:\" + DateTime.Now.ToShortDateString().Replace("/", "") +
DateTime.Now.ToLongTimeString().Replace(":", "") + ".doc"; //Saved Path
try
{
int dtCount = 1;
foreach (DataTable dt in ds.Tables)
{
string[] strTable = dt.TableName.Split('$');
string tableName = strTable[0];
string tableComments = strTable[1];
int rowCount = dt.Rows.Count;
List<string> primaryKeys = TableControl.GetPrimayKeys(tableName.ToUpper(), dbType); //Get Table Primary Key
//WordDoc.Range(2, 2).InsertParagraphAfter(); //insert return
wordApp.Selection.ParagraphFormat.LineSpacing = 15f; //设置文档的行间距
////移动焦点并换行
object count = 14;
object wdLine = WdUnits.wdLine; //换一行;
wordApp.Selection.MoveDown(ref wdLine, ref count, ref nothing); //移动焦点
wordApp.Selection.TypeParagraph(); //插入段落(每个表格间增加一行),可视为换行
//添加表名称(表格外)
wordApp.Selection.TypeText("表" + Convert.ToString(dtCount) + ":" + tableName + "(" + tableComments +
")");
//停留1000毫秒，否则创建表格时会报异常
Thread.Sleep(1000);
//文档中创建表格( +2表示：表名行和列头行）
//5表示列数量
Table newTable = wordDoc.Tables.Add(wordApp.Selection.Range, rowCount + 2, 5, ref nothing,
ref nothing);
//设置表格样式
//newTable.Borders.OutsideLineStyle = Word.WdLineStyle.wdLineStyleThickThinLargeGap;
newTable.Borders.OutsideLineStyle = WdLineStyle.wdLineStyleSingle;
newTable.Borders.InsideLineStyle = WdLineStyle.wdLineStyleSingle;
newTable.Columns[1].Width = 50f; //序号
newTable.Columns[2].Width = 100f; //列名
newTable.Columns[3].Width = 100f; //数据类型
newTable.Columns[4].Width = 100f; //主键
newTable.Columns[5].Width = 100f; //说明
#region 填充表名称(表格内)
newTable.Cell(1, 1).Range.Text = "表名：" + tableName + "(" + tableComments + ")";
newTable.Cell(1, 1).Range.Bold = 2; //设置单元格中字体为粗体
//合并单元格
newTable.Cell(1, 1).Merge(newTable.Cell(1, 5));
newTable.Cell(1, 1).Range.Font.Color = WdColor.wdColorRed; //设置单元格内字体颜色
newTable.Cell(1, 1).Select(); //选中
wordApp.Selection.Cells.VerticalAlignment = WdCellVerticalAlignment.wdCellAlignVerticalCenter;
//垂直居中
wordApp.Selection.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter; //水平居中
#endregion
#region 填充列标题
newTable.Cell(2, 1).Range.Text = "序号";
newTable.Cell(2, 1).Range.Font.Color = WdColor.wdColorBlack; //设置单元格内字体颜色
newTable.Cell(2, 2).Range.Font.Bold = 2;
newTable.Cell(2, 2).Range.Font.Size = 9;
newTable.Cell(2, 2).Select(); //选中
newTable.Cell(2, 2).VerticalAlignment = WdCellVerticalAlignment.wdCellAlignVerticalCenter;
//WordApp.Selection.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;//水平居中
newTable.Cell(2, 2).Range.Text = "列名";
newTable.Cell(2, 2).Range.Font.Color = WdColor.wdColorBlack; //设置单元格内字体颜色
newTable.Cell(2, 2).Range.Font.Bold = 2;
newTable.Cell(2, 2).Range.Font.Size = 9;
newTable.Cell(2, 2).Select(); //选中
newTable.Cell(2, 2).VerticalAlignment = WdCellVerticalAlignment.wdCellAlignVerticalCenter;
//WordApp.Selection.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;//水平居中
newTable.Cell(2, 3).Range.Text = "数据类型";
newTable.Cell(2, 3).Range.Font.Color = WdColor.wdColorBlack; //设置单元格内字体颜色
newTable.Cell(2, 3).Range.Font.Bold = 2;
newTable.Cell(2, 3).Range.Font.Size = 9;
newTable.Cell(2, 3).VerticalAlignment = WdCellVerticalAlignment.wdCellAlignVerticalCenter;
newTable.Cell(2, 3).Select(); //选中
//WordApp.Selection.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;//水平居中
newTable.Cell(2, 4).Range.Text = "主键";
newTable.Cell(2, 4).Range.Font.Color = WdColor.wdColorBlack; //设置单元格内字体颜色
newTable.Cell(2, 4).VerticalAlignment = WdCellVerticalAlignment.wdCellAlignVerticalCenter;
newTable.Cell(2, 4).Range.Font.Bold = 2;
newTable.Cell(2, 4).Range.Font.Size = 9;
newTable.Cell(2, 4).Select(); //选中
//WordApp.Selection.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;//水平居中
newTable.Cell(2, 5).Range.Text = "说明";
newTable.Cell(2, 5).Range.Font.Color = WdColor.wdColorBlack; //设置单元格内字体颜色
newTable.Cell(2, 5).VerticalAlignment = WdCellVerticalAlignment.wdCellAlignVerticalCenter;
newTable.Cell(2, 5).Range.Font.Bold = 2;
newTable.Cell(2, 5).Range.Font.Size = 9;
newTable.Cell(2, 5).Select(); //选中
//WordApp.Selection.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;//水平居中
#endregion
#region 填充表格内容
for (int i = 0; i < dt.Rows.Count; i++)
{
string dataType = dt.Rows[i]["Data_Type"].ToString(); //数据类型
string dataPrecision = dt.Rows[i]["Data_Precision"].ToString(); //精度
string dataLength = dt.Rows[i]["Data_Length"].ToString(); //长度
string dataScale = dt.Rows[i]["Data_Scale"].ToString(); //
string type;
if (dataPrecision == string.Empty) //字符串、日期等
{
if (dataType == "DATE")
{
type = dataType + "()";
}
else
{
type = dataType + "(" + dataLength + ")";
}
}
else //数字类型等
{
if (dataScale == "0")
{
type = dataType + "(" + dataPrecision + ")";
}
else
{
type = dataType + "(" + dataPrecision + "," + dataScale + ")";
}
}
//序号
newTable.Cell(i + 3, 1).Range.Text = Convert.ToString(i + 1);
newTable.Cell(i + 3, 1).VerticalAlignment = WdCellVerticalAlignment.wdCellAlignVerticalCenter;
//列名
string pascalColName = dt.Rows[i]["PascalName"].ToString();
string columnName = dt.Rows[i]["Column_Name"].ToString();
newTable.Cell(i + 3, 2).Range.Text = pascalColName == string.Empty ? columnName : pascalColName;
newTable.Cell(i + 3, 2).VerticalAlignment = WdCellVerticalAlignment.wdCellAlignVerticalCenter;
//WordApp.Selection.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;//水平居左
//数据类型
newTable.Cell(i + 3, 3).Range.Text = type;
newTable.Cell(i + 3, 3).VerticalAlignment = WdCellVerticalAlignment.wdCellAlignVerticalCenter;
//WordApp.Selection.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;//水平居左
//主键
newTable.Cell(i + 3, 4).Range.Text = primaryKeys.Contains(dt.Rows[i]["Column_Name"].ToString())
? "是"
: "";
newTable.Cell(i + 3, 4).VerticalAlignment = WdCellVerticalAlignment.wdCellAlignVerticalCenter;
//WordApp.Selection.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;//水平居左
//说明
newTable.Cell(i + 3, 5).Range.Text = dt.Rows[i]["Comments"].ToString();
newTable.Cell(i + 3, 5).VerticalAlignment = WdCellVerticalAlignment.wdCellAlignVerticalCenter;
//WordApp.Selection.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;//水平居左
}
#endregion
//在表格中增加行，注意不是在每个表格间增加行。这就是加了这行代码后为什么每个表格会多出空白行的原因
//WordDoc.Content.Tables[dtCount].Rows.Add(ref Nothing);
//WordDoc.Paragraphs.Last.Range.Text = "文档创建时间：" + DateTime.Now.ToString();//“落款”
//WordDoc.Paragraphs.Last.Alignment = Word.WdParagraphAlignment.wdAlignParagraphRight;
//WordDoc.Content.Tables[dtCount].Rows.Add(ref Nothing);
dtCount++;
#region 插入分页（每一个表格在一页中显示）
//object mymissing = System.Reflection.Missing.Value;
//object myunit = Word.WdUnits.wdStory;
//WordApp.Selection.EndKey(ref myunit, ref mymissing);
//object pBreak = (int)Word.WdBreakType.wdPageBreak;
//WordApp.Selection.InsertBreak(ref pBreak);
#endregion
}
msg = "OK";
}
catch (Exception ex)
{
msg = ex.Message;
}
finally
{
//文件保存
wordDoc.SaveAs(ref filePath, ref nothing, ref nothing, ref nothing, ref nothing, ref nothing,
ref nothing, ref nothing, ref nothing, ref nothing, ref nothing, ref nothing, ref nothing,
ref nothing, ref nothing, ref nothing);
wordDoc.Close(ref nothing, ref nothing, ref nothing);
wordApp.Quit(ref nothing, ref nothing, ref nothing);
//wordtype.InvokeMember("Quit", System.Reflection.BindingFlags.InvokeMethod, null, appclass, null);//退出
Marshal.ReleaseComObject(wordDoc);
Marshal.ReleaseComObject(wordApp);
GC.Collect();
}
Process.Start(filePath.ToString());
return msg;
}
/// <summary>
/// Gets the connection string.
/// </summary>
/// <param name="dbType">Type of the db.</param>
/// <returns></returns>
public string GetConnectionString(EnumDbType dbType)
{
return(GetConnectionString(CurrentZoneName, dbType));
}
/// <summary>
/// bind Table Data
/// </summary>
/// <param name="dic"></param>
public static DataTable GetTables(Dictionary<string, string> dic, string filterValue, EnumDbType dbType)
{
string strSql = string.Empty;
if (dbType == EnumDbType.Oracle)
{
strSql =
"SELECT ut.table_name TableName ,utc.comments,'' PascalName FROM user_tables ut " +
"INNER JOIN user_tab_comments utc ON ut.table_name=utc.table_name";
if (filterValue != string.Empty)
{
strSql += " WHERE LOWER(ut.table_name) LIKE '" + filterValue + "%'";
}
strSql += " ORDER BY ut.table_name";
}
else if (dbType == EnumDbType.SqlServer)
{
strSql =
@"SELECT t.[name] as TableName,ep.[value] comments,'' PascalName FROM sys.tables t
LEFT JOIN sys.extended_properties ep
ON t.[object_id]=ep.major_id AND ep.minor_id=0";
if (filterValue != string.Empty)
{
strSql += " WHERE LOWER(ep.[name]) LIKE '" + filterValue + "%'";
}
}
DataTable dt = DBHelper.GetDataTable(strSql);
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dic.ContainsKey(dt.Rows[i]["TableName"].ToString()))
{
dt.Rows[i]["PascalName"] = dic[dt.Rows[i]["TableName"].ToString()];
}
}
return dt;
}
public static DataTable GetTableColumnsByTableName(string tableName, EnumDbType dbType)
{
string sqlstr = String.Empty;
if (dbType == EnumDbType.Oracle)
{
sqlstr = String.Format(
@"SELECT utc.*,ucc.comments,'' PascalName FROM user_tab_columns utc
INNER JOIN user_col_comments ucc ON utc.table_name=ucc.table_name AND utc.column_name=ucc.column_name
WHERE UPPER(utc.table_name)='{0}'", tableName);
}
else if (dbType == EnumDbType.SqlServer)
{
sqlstr = String.Format(
@"Declare @tblName nvarchar(1000)
set @tblName='{0}'
declare @TblID int
set @TblID=(select [object_id] as tblID from sys.all_objects where [type] ='U' and [name]<>'dtproperties' and [name]=@tblName)
select syscolumns.name as Column_Name,
systypes.name as DATA_TYPE,
syscolumns.length as DATA_LENGTH,
syscolumns.prec as DATA_PRECISION, --xprec
syscolumns.scale as DATA_SCALE, --xscale
(SELECT [value] FROM ::fn_listextendedproperty(NULL, 'user', 'dbo', 'table', object_name(@TblID), 'column', syscolumns.name) as e where e.name='MS_Description') as comments,
'' PascalName
from sysColumns
left join sysTypes on sysTypes.xtype = sysColumns.xtype and sysTypes.xusertype = sysColumns.xusertype
left join sysobjects on sysobjects.id = syscolumns.cdefault and sysobjects.type='D'
left join syscomments on syscomments.id = sysobjects.id
where syscolumns.id=@TblID
ORDER BY Column_Name", tableName);
}
var dtColumns = DBHelper.GetDataTable(sqlstr);
return dtColumns;
}
/// <summary>
/// 数据库访问
/// </summary>
/// <param name="p_dbType">数据库类型</param>
/// <param name="p_strConn">数据库连接串</param>
public DbHelp(EnumDbType p_dbType, string p_strConn)
{
m_dbType = p_dbType;
PubConstant.ConntionString = p_strConn;
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="connConfig">数据库链接配置结构</param>
public DbAccess(ConnConfig connConfig)
{
connStr = connConfig.str_ConnStr;
dbType = connConfig.enum_dbType;
}
/// <summary>
/// Create Word Document
/// </summary>
/// <param name="ds">Every DataTable in DataSet Generate a table</param>
/// <param name="dbType"> </param>
/// <returns></returns>
public static string Create(DataSet ds, EnumDbType dbType)
{
string msg;
Object nothing = Missing.Value;
Application wordApp = new ApplicationClass();
wordApp.NormalTemplate.Saved = true; //Avoid PopUp normal.dot Dialog
Document wordDoc = wordApp.Documents.Add(ref nothing, ref nothing, ref nothing, ref nothing);
object filePath = @"C:\" + DateTime.Now.ToShortDateString().Replace("/", "") +
DateTime.Now.ToLongTimeString().Replace(":", "") + ".doc"; //Saved Path
try
{
int dtCount = 1;
foreach (DataTable dt in ds.Tables)
{
string[] strTable = dt.TableName.Split('$');
string tableName = strTable[0];
string tableComments = strTable[1];
int rowCount = dt.Rows.Count;
List <string> primaryKeys = TableControl.GetPrimayKeys(tableName.ToUpper(), dbType); //Get Table Primary Key
//WordDoc.Range(2, 2).InsertParagraphAfter(); //insert return
wordApp.Selection.ParagraphFormat.LineSpacing = 15f; //设置文档的行间距
////移动焦点并换行
object count = 14;
object wdLine = WdUnits.wdLine; //换一行;
wordApp.Selection.MoveDown(ref wdLine, ref count, ref nothing); //移动焦点
wordApp.Selection.TypeParagraph(); //插入段落(每个表格间增加一行),可视为换行
//添加表名称(表格外)
wordApp.Selection.TypeText("表" + Convert.ToString(dtCount) + ":" + tableName + "(" + tableComments +
")");
//停留1000毫秒，否则创建表格时会报异常
Thread.Sleep(1000);
//文档中创建表格( +2表示：表名行和列头行）
//5表示列数量
Table newTable = wordDoc.Tables.Add(wordApp.Selection.Range, rowCount + 2, 5, ref nothing,
ref nothing);
//设置表格样式
//newTable.Borders.OutsideLineStyle = Word.WdLineStyle.wdLineStyleThickThinLargeGap;
newTable.Borders.OutsideLineStyle = WdLineStyle.wdLineStyleSingle;
newTable.Borders.InsideLineStyle = WdLineStyle.wdLineStyleSingle;
newTable.Columns[1].Width = 50f; //序号
newTable.Columns[2].Width = 100f; //列名
newTable.Columns[3].Width = 100f; //数据类型
newTable.Columns[4].Width = 100f; //主键
newTable.Columns[5].Width = 100f; //说明
#region 填充表名称(表格内)
newTable.Cell(1, 1).Range.Text = "表名：" + tableName + "(" + tableComments + ")";
newTable.Cell(1, 1).Range.Bold = 2; //设置单元格中字体为粗体
//合并单元格
newTable.Cell(1, 1).Merge(newTable.Cell(1, 5));
newTable.Cell(1, 1).Range.Font.Color = WdColor.wdColorRed; //设置单元格内字体颜色
newTable.Cell(1, 1).Select(); //选中
wordApp.Selection.Cells.VerticalAlignment = WdCellVerticalAlignment.wdCellAlignVerticalCenter;
//垂直居中
wordApp.Selection.ParagraphFormat.Alignment = WdParagraphAlignment.wdAlignParagraphCenter; //水平居中
#endregion
#region 填充列标题
newTable.Cell(2, 1).Range.Text = "序号";
newTable.Cell(2, 1).Range.Font.Color = WdColor.wdColorBlack; //设置单元格内字体颜色
newTable.Cell(2, 2).Range.Font.Bold = 2;
newTable.Cell(2, 2).Range.Font.Size = 9;
newTable.Cell(2, 2).Select(); //选中
newTable.Cell(2, 2).VerticalAlignment = WdCellVerticalAlignment.wdCellAlignVerticalCenter;
//WordApp.Selection.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;//水平居中
newTable.Cell(2, 2).Range.Text = "列名";
newTable.Cell(2, 2).Range.Font.Color = WdColor.wdColorBlack; //设置单元格内字体颜色
newTable.Cell(2, 2).Range.Font.Bold = 2;
newTable.Cell(2, 2).Range.Font.Size = 9;
newTable.Cell(2, 2).Select(); //选中
newTable.Cell(2, 2).VerticalAlignment = WdCellVerticalAlignment.wdCellAlignVerticalCenter;
//WordApp.Selection.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;//水平居中
newTable.Cell(2, 3).Range.Text = "数据类型";
newTable.Cell(2, 3).Range.Font.Color = WdColor.wdColorBlack; //设置单元格内字体颜色
newTable.Cell(2, 3).Range.Font.Bold = 2;
newTable.Cell(2, 3).Range.Font.Size = 9;
newTable.Cell(2, 3).VerticalAlignment = WdCellVerticalAlignment.wdCellAlignVerticalCenter;
newTable.Cell(2, 3).Select(); //选中
//WordApp.Selection.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;//水平居中
newTable.Cell(2, 4).Range.Text = "主键";
newTable.Cell(2, 4).Range.Font.Color = WdColor.wdColorBlack; //设置单元格内字体颜色
newTable.Cell(2, 4).VerticalAlignment = WdCellVerticalAlignment.wdCellAlignVerticalCenter;
newTable.Cell(2, 4).Range.Font.Bold = 2;
newTable.Cell(2, 4).Range.Font.Size = 9;
newTable.Cell(2, 4).Select(); //选中
//WordApp.Selection.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;//水平居中
newTable.Cell(2, 5).Range.Text = "说明";
newTable.Cell(2, 5).Range.Font.Color = WdColor.wdColorBlack; //设置单元格内字体颜色
newTable.Cell(2, 5).VerticalAlignment = WdCellVerticalAlignment.wdCellAlignVerticalCenter;
newTable.Cell(2, 5).Range.Font.Bold = 2;
newTable.Cell(2, 5).Range.Font.Size = 9;
newTable.Cell(2, 5).Select(); //选中
//WordApp.Selection.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;//水平居中
#endregion
#region 填充表格内容
for (int i = 0; i < dt.Rows.Count; i++)
{
string dataType = dt.Rows[i]["Data_Type"].ToString(); //数据类型
string dataPrecision = dt.Rows[i]["Data_Precision"].ToString(); //精度
string dataLength = dt.Rows[i]["Data_Length"].ToString(); //长度
string dataScale = dt.Rows[i]["Data_Scale"].ToString(); //
string type;
if (dataPrecision == string.Empty) //字符串、日期等
{
if (dataType == "DATE")
{
type = dataType + "()";
}
else
{
type = dataType + "(" + dataLength + ")";
}
}
else //数字类型等
{
if (dataScale == "0")
{
type = dataType + "(" + dataPrecision + ")";
}
else
{
type = dataType + "(" + dataPrecision + "," + dataScale + ")";
}
}
//序号
newTable.Cell(i + 3, 1).Range.Text = Convert.ToString(i + 1);
newTable.Cell(i + 3, 1).VerticalAlignment = WdCellVerticalAlignment.wdCellAlignVerticalCenter;
//列名
string pascalColName = dt.Rows[i]["PascalName"].ToString();
string columnName = dt.Rows[i]["Column_Name"].ToString();
newTable.Cell(i + 3, 2).Range.Text = pascalColName == string.Empty ? columnName : pascalColName;
newTable.Cell(i + 3, 2).VerticalAlignment = WdCellVerticalAlignment.wdCellAlignVerticalCenter;
//WordApp.Selection.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;//水平居左
//数据类型
newTable.Cell(i + 3, 3).Range.Text = type;
newTable.Cell(i + 3, 3).VerticalAlignment = WdCellVerticalAlignment.wdCellAlignVerticalCenter;
//WordApp.Selection.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;//水平居左
//主键
newTable.Cell(i + 3, 4).Range.Text = primaryKeys.Contains(dt.Rows[i]["Column_Name"].ToString())
? "是"
: "";
newTable.Cell(i + 3, 4).VerticalAlignment = WdCellVerticalAlignment.wdCellAlignVerticalCenter;
//WordApp.Selection.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;//水平居左
//说明
newTable.Cell(i + 3, 5).Range.Text = dt.Rows[i]["Comments"].ToString();
newTable.Cell(i + 3, 5).VerticalAlignment = WdCellVerticalAlignment.wdCellAlignVerticalCenter;
//WordApp.Selection.ParagraphFormat.Alignment = Word.WdParagraphAlignment.wdAlignParagraphCenter;//水平居左
}
#endregion
//在表格中增加行，注意不是在每个表格间增加行。这就是加了这行代码后为什么每个表格会多出空白行的原因
//WordDoc.Content.Tables[dtCount].Rows.Add(ref Nothing);
//WordDoc.Paragraphs.Last.Range.Text = "文档创建时间：" + DateTime.Now.ToString();//“落款”
//WordDoc.Paragraphs.Last.Alignment = Word.WdParagraphAlignment.wdAlignParagraphRight;
//WordDoc.Content.Tables[dtCount].Rows.Add(ref Nothing);
dtCount++;
#region 插入分页（每一个表格在一页中显示）
//object mymissing = System.Reflection.Missing.Value;
//object myunit = Word.WdUnits.wdStory;
//WordApp.Selection.EndKey(ref myunit, ref mymissing);
//object pBreak = (int)Word.WdBreakType.wdPageBreak;
//WordApp.Selection.InsertBreak(ref pBreak);
#endregion
}
msg = "OK";
}
catch (Exception ex)
{
msg = ex.Message;
}
finally
{
//文件保存
wordDoc.SaveAs(ref filePath, ref nothing, ref nothing, ref nothing, ref nothing, ref nothing,
ref nothing, ref nothing, ref nothing, ref nothing, ref nothing, ref nothing, ref nothing,
ref nothing, ref nothing, ref nothing);
wordDoc.Close(ref nothing, ref nothing, ref nothing);
wordApp.Quit(ref nothing, ref nothing, ref nothing);
//wordtype.InvokeMember("Quit", System.Reflection.BindingFlags.InvokeMethod, null, appclass, null);//退出
Marshal.ReleaseComObject(wordDoc);
Marshal.ReleaseComObject(wordApp);
GC.Collect();
}
Process.Start(filePath.ToString());
return(msg);
}
/// <summary>
/// �������ݷ��ʽӿ�ʵ����
/// </summary>
/// <returns></returns>
protected virtual IDBAccess CreateInstance(EnumDbType dbType, string connString)
{
IDBAccess access = null;
switch (dbType)
{
case EnumDbType.SqlServer:
access = new SqlDBAccess(connString);
break;
case EnumDbType.OleDb:
access = new OleDBAccess(connString);
break;
default:break;
}
return access;
}
