using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Winton.Wmis.Lib.CommonLib;
using Winton.Wmis.Lib.SystemLib;
using Winton.Wmis.Interface;
using System.IO;
using System.Windows.Forms;
namespace Winton.Wmis.Tools
{
class TransSQL
{
/// <summary>
/// 实际上这边参数指定数据库的都不需要，因为你到时执行SQL时就要指定数据库才能操作
/// </summary>
private string _ConnectionStr = string.Empty;
private string _DBName = string.Empty; //数据库名称
private string _ConnStr = string.Empty; //数据库连接字串
private string sSQL = string.Empty;
public TransSQL(string ADBName,string AConnStr)
{
_DBName = ADBName;
_ConnStr = AConnStr;
}
/// <summary>
/// 获取资料表的SQL脚本
/// </summary>
/// <param name="ATableName">资料表名</param>
/// <param name="ATableSQL">表的SQL脚本</param>
/// <param name="ACONSTRAINT">表的约束脚本</param>
/// <returns></returns>
public bool GetCreateTableSQL(string ATableName, out string ATableSQL, out string ACONSTRAINT )
{
ATableSQL = string.Empty;
ACONSTRAINT = string.Empty;
if (string.IsNullOrEmpty(ATableName))
return false;
string sSQL = string.Format(" SELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH, " +
" COLLATION_NAME,IS_NULLABLE,COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='{0}'",ATableName);
DataTable dttTemp = GetDataBySql(sSQL);
StringBuilder sBuilder = new StringBuilder(11);
sBuilder.Append(string.Format("----------------------------生成資料表{0}的腳本-----------------------------\n\t",ATableName));
sBuilder.Append(string.Format(" Create Table {0} ( \n\t", ATableName));
StringBuilder sCONSTRAINT = new StringBuilder(11);
sCONSTRAINT.Append(string.Format(" Alter Table {0} Add ", ATableName));
foreach (DataRow dtrCurr in dttTemp.Rows)
{
sBuilder.Append(string.Format(" {0} [{1}] {2} {3} {4} ,\n\t",
dtrCurr["COLUMN_NAME"], dtrCurr["DATA_TYPE"],
DataLib.ObjectToString(dtrCurr["DATA_TYPE"]).Trim().ToLower() == "varchar"?"("+ dtrCurr["CHARACTER_MAXIMUM_LENGTH"].ToString()+")":" ",
DataLib.HasStringValue(dtrCurr["COLLATION_NAME"])?"COLLATE "+dtrCurr["COLLATION_NAME"].ToString():" ",
DataLib.ObjectToString(dtrCurr["IS_NULLABLE"]).Trim() =="No"?" NOT NULL":"NULL"));
if (DataLib.HasStringValue(dtrCurr["COLUMN_DEFAULT"]))
{
sCONSTRAINT.Append(string.Format(" CONSTRAINT [DF_{0}_{1}] DEFAULT {2} FOR [{1}] ,\n\t",
ATableName, dtrCurr["COLUMN_NAME"], dtrCurr["COLUMN_DEFAULT"]));
}
}
ACONSTRAINT= sCONSTRAINT.ToString().Substring(0, sCONSTRAINT.Length - 1)+" Go \n\t";
sSQL = string.Format("SELECT CONSTRAINT_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='{0}'", ATableName);
dttTemp = GetDataBySql(sSQL);
sBuilder.Append(string.Format(" CONSTRAINT PK_{0} PRIMARY KEY ( ", ATableName));
foreach (DataRow dtrCurr in dttTemp.Rows)
{
sBuilder.Append(string.Format("{0},", dtrCurr["COLUMN_NAME"]));
}
ATableSQL = sBuilder.ToString().Substring(0, sBuilder.Length - 1);
ATableSQL += ")) \n\t Go \n\t";
WriteToFile(ATableSQL+ACONSTRAINT);
return true;
}
/// <summary>
/// 把字符串写入文件中，是追加写入
/// </summary>
/// <param name="AWriteStr">字符串</param>
public void WriteToFile(string AWriteStr)
{
StreamWriter vWriter = new StreamWriter(Application.StartupPath + "\\"+_DBName+".sql", true);
vWriter.WriteLine(AWriteStr);
vWriter.WriteLine("");
vWriter.Close();
}
/// <summary>
/// 通过SQL语句抓取资料
/// </summary>
/// <param name="ASql"></param>
/// <returns></returns>
public DataTable GetDataBySql(string ASql)
{
DataTable dttTable = new DataTable();
using (SqlConnection vConn = new SqlConnection(_ConnStr))/*select語法不必記錄User*/
{
SqlDataAdapter sdaAdp = new SqlDataAdapter(ASql, vConn);
try
{
sdaAdp.Fill(dttTable);
}
catch (Exception e)
{
dttTable.Clear();
throw;
}
}
return dttTable;
}
/// <summary>
/// 获取该数据库中视图的SQL语法
/// </summary>
/// <param name="AName"></param>
/// <param name="AViewSQL"></param>
/// <returns></returns>
public bool GetViewSQL(string AName, out string AViewSQL)
{
AViewSQL = string.Empty;
if (string.IsNullOrEmpty(AName))
return false;
sSQL = string.Format("SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_CATALOG='{0}' AND TABLE_NAME='{1}'",
_DBName, AName);
DataTable dttTemp = GetDataBySql(sSQL);
if (dttTemp.Rows.Count > 0)
{
AViewSQL = DataLib.ObjectToString(dttTemp.Rows[0]["VIEW_DEFINITION"])+"\n\t";
WriteToFile(AViewSQL);
return true;
}
else
return false;
}
/// <summary>
/// 获取存储过程的SQL脚本
/// </summary>
/// <param name="AName">存储过程名</param>
/// <param name="AProcSQL">脚本</param>
/// <returns></returns>
public bool GetProcedureSQL(string AName, out string AProcSQL)
{
AProcSQL = string.Empty;
if (string.IsNullOrEmpty(AName))
return false;
sSQL = string.Format(" SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_CATALOG='{0}' AND ROUTINE_NAME='{1}'",
_DBName, AName);
DataTable dttTemp = GetDataBySql(sSQL);
if (dttTemp.Rows.Count > 0)
{
AProcSQL = DataLib.ObjectToString(dttTemp.Rows[0]["ROUTINE_DEFINITION"]) + "\n\t";
WriteToFile(AProcSQL);
return true;
}
else
return false;
}
public DataTable GetTable(int ANum)
{
switch (ANum)
{
//获取视图 category<>'0'表示系统view
case 1:
sSQL = string.Format(" SELECT TABLE_CATALOG AS DB,TABLE_NAME AS TableName FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_CATALOG='{0}' AND TABLE_TYPE='VIEW' AND CATEGORY=0 ", _DBName);
break;
//获取存储过程
case 2:
sSQL = string.Format(" SELECT CAST (0 AS BIT) AS Checked,' ' TABLE003Desc,SPECIFIC_NAME TABLE002,' ' TABLE003,SPECIFIC_NAME AS TABLE001 FROM INFORMATION_SCHEMA.ROUTINES A"+
" INNER JOIN sysobjects B on A.ROUTINE_NAME = B.[name] WHERE xtype='P' AND CATEGORY=0 AND ROUTINE_CATALOG='{0}'",
_DBName);
break;
//获取使用者资料表
default:
sSQL = string.Format(" SELECT TABLE_CATALOG AS DB,TABLE_NAME AS TableName FROM INFORMATION_SCHEMA.TABLES A INNER JOIN sysobjects B on A.Table_name = B.[name] WHERE xtype='U' AND TABLE_CATALOG='{0}' AND TABLE_TYPE='BASE TABLE' ", _DBName);
break;
}
DataTable dttTemp = GetDataBySql(sSQL);
return dttTemp;
}
}
}
源码：数据库生成脚本