using System;
using System.IO;
using System.CodeDom;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using TdoCodeGenerator.Generator;
namespace TdoCodeGenerator.Generator{
public static class TdoDiscovery
{
/// <summary>
/// Gets the relative path.
/// </summary>
/// <param name="pcFrom">The pc from.</param>
/// <param name="pcTo">The pc to.</param>
/// <returns></returns>
internal static string getRelativePath(string pcFrom, string pcTo)
{
//rootPath = c:\dir1\dir2\dir3\TdoSolution1.tdosln
//childPath= c:\dir1\dir2\dir3\dir4\dir5\TdoSolution1.tdosln
//newPath = dir4\dir5\TdoSolution1.tdosln
if (Directory.Exists(pcFrom) && Directory.Exists(pcTo))
{
//only for OutputFolder relative path
pcFrom = Path.Combine(pcFrom, "somefile.ext");
pcTo = Path.Combine(pcTo, "somefile.ext");
}
string lcRelativePath = pcTo;
string lcFrom = (pcFrom == null ? "" : pcFrom.Trim());
string lcTo = (pcTo == null ? "" : pcTo.Trim());
if (String.Compare(lcFrom, lcTo, true) == 0)
return ".\\";
if (lcFrom.Length > 0 && lcTo.Length > 0 && System.IO.Path.GetPathRoot(lcFrom.ToUpper()).Equals(System.IO.Path.GetPathRoot(lcTo.ToUpper())))
{
char[] laDirSep = { '\\' };
string lcPathFrom = (System.IO.Path.GetDirectoryName(lcFrom) == null ? System.IO.Path.GetPathRoot(lcFrom.ToUpper()) : System.IO.Path.GetDirectoryName(lcFrom));
string lcPathTo = (System.IO.Path.GetDirectoryName(lcTo) == null ? System.IO.Path.GetPathRoot(lcTo.ToUpper()) : System.IO.Path.GetDirectoryName(lcTo));
string lcFileTo = (System.IO.Path.GetFileName(lcTo) == null ? "" : System.IO.Path.GetFileName(lcTo));
string[] laFrom = lcPathFrom.Split(laDirSep);
string[] laTo = lcPathTo.Split(laDirSep);
int lnFromCnt = laFrom.Length;
int lnToCnt = laTo.Length;
int lnSame = 0;
int lnCount = 0;
while (lnToCnt > 0 && lnSame < lnToCnt)
{
if (lnCount < lnFromCnt)
{
if (laFrom[lnCount].ToUpper().Equals(laTo[lnCount].ToUpper()))
lnSame++;
else
break;
}
else
break;
lnCount++;
}
string lcEndPart = "";
for (int lnEnd = lnSame; lnEnd < lnToCnt; lnEnd++)
{
if (laTo[lnEnd].Length > 0)
{
lcEndPart += laTo[lnEnd] + @"\";
}
else
{
break;
}
}
int lnDiff = Math.Abs(lnFromCnt - lnSame);
if (lnDiff > 0 && laFrom[lnFromCnt - 1].Length > 0)
{
while (lnDiff > 0)
{
lnDiff--;
lcEndPart = @"..\" + lcEndPart;
}
}
lcRelativePath = lcEndPart + lcFileTo;
}
if (lcRelativePath.EndsWith("\\somefile.ext"))
lcRelativePath = lcRelativePath.Substring(0, lcRelativePath.Length - ("\\somefile.ext").Length);
return lcRelativePath;
}
public static string[] Tables(string connectionString)
{
connectionString = connectionString.TrimEnd(';') + ";Connection Timeout=" + TdoCodeGenerator.Properties.Settings.Default.SQLServerConnectionTimeOut.ToString();
SqlDataAdapter da = new SqlDataAdapter("select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE='BASE TABLE' ORDER BY TABLE_SCHEMA, TABLE_NAME", connectionString);
da.SelectCommand.CommandTimeout = TdoCodeGenerator.Properties.Settings.Default.SQLServerCommandTimeOut;
DataTable dtTables = new DataTable("Tables");
da.Fill(dtTables);
string[] tables = new string[dtTables.Rows.Count];
for (int i = 0; i < tables.Length; i++)
{
tables[i] = TdoDiscovery.EnclosedString((string)dtTables.Rows[i]["TABLE_SCHEMA"])+"."+TdoDiscovery.EnclosedString(((string)dtTables.Rows[i]["TABLE_NAME"]));
}
da.Dispose();
return tables;
}
public static string[] Views(string connectionString)
{
connectionString = connectionString.TrimEnd(';') + ";Connection Timeout=" + TdoCodeGenerator.Properties.Settings.Default.SQLServerConnectionTimeOut.ToString();
SqlDataAdapter da = new SqlDataAdapter("select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE='VIEW' ORDER BY TABLE_SCHEMA, TABLE_NAME", connectionString);
da.SelectCommand.CommandTimeout = TdoCodeGenerator.Properties.Settings.Default.SQLServerCommandTimeOut;
DataTable dtViews = new DataTable("Views");
da.Fill(dtViews);
string[] views = new string[dtViews.Rows.Count];
for (int i = 0; i < views.Length; i++)
{
views[i] = TdoDiscovery.EnclosedString((string)dtViews.Rows[i]["TABLE_SCHEMA"])+ "." + TdoDiscovery.EnclosedString(((string)dtViews.Rows[i]["TABLE_NAME"]));
}
return views;
}
public static string[] StoredProcedures(string connectionString)
{
connectionString = connectionString.TrimEnd(';') + ";Connection Timeout=" + TdoCodeGenerator.Properties.Settings.Default.SQLServerConnectionTimeOut.ToString();
SqlDataAdapter da = new SqlDataAdapter("select ROUTINE_SCHEMA, ROUTINE_NAME from INFORMATION_SCHEMA.ROUTINES where ROUTINE_TYPE='PROCEDURE' AND ROUTINE_NAME NOT LIKE 'sp_%' AND ROUTINE_NAME NOT LIKE 'dt_%' ORDER BY ROUTINE_SCHEMA, ROUTINE_NAME", connectionString);
da.SelectCommand.CommandTimeout = TdoCodeGenerator.Properties.Settings.Default.SQLServerCommandTimeOut;
DataTable dtStoredProcedures = new DataTable("Stored Procedures");
da.Fill(dtStoredProcedures);
string[] storedProcedures = new string[dtStoredProcedures.Rows.Count];
for (int i = 0; i < storedProcedures.Length; i++)
{
storedProcedures[i] = TdoDiscovery.EnclosedString((string)dtStoredProcedures.Rows[i]["ROUTINE_SCHEMA"])+ "." + TdoDiscovery.EnclosedString(((string)dtStoredProcedures.Rows[i]["ROUTINE_NAME"]));
}
return storedProcedures;
}
public static string[] ScalarValuedFunctions(string connectionString)
{
connectionString = connectionString.TrimEnd(';') + ";Connection Timeout=" + TdoCodeGenerator.Properties.Settings.Default.SQLServerConnectionTimeOut.ToString();
SqlDataAdapter da = new SqlDataAdapter("select ROUTINE_SCHEMA, ROUTINE_NAME from INFORMATION_SCHEMA.ROUTINES where ROUTINE_TYPE='FUNCTION' AND ROUTINE_NAME NOT LIKE 'fn_%' AND DATA_TYPE<>'TABLE' ORDER BY ROUTINE_SCHEMA, ROUTINE_NAME", connectionString);
da.SelectCommand.CommandTimeout = TdoCodeGenerator.Properties.Settings.Default.SQLServerCommandTimeOut;
DataTable dtUserScalarFunctions = new DataTable("User Scalar-valued Functions");
da.Fill(dtUserScalarFunctions);
string[] userScalarFunctions = new string[dtUserScalarFunctions.Rows.Count];
for (int i = 0; i < userScalarFunctions.Length; i++)
{
userScalarFunctions[i] = TdoDiscovery.EnclosedString((string)dtUserScalarFunctions.Rows[i]["ROUTINE_SCHEMA"])+"." + TdoDiscovery.EnclosedString(((string)dtUserScalarFunctions.Rows[i]["ROUTINE_NAME"]));
}
return userScalarFunctions;
}
public static string[] TableValuedFunctions(string connectionString)
{
connectionString = connectionString.TrimEnd(';') + ";Connection Timeout=" + TdoCodeGenerator.Properties.Settings.Default.SQLServerConnectionTimeOut.ToString();
SqlDataAdapter da = new SqlDataAdapter("select ROUTINE_SCHEMA, ROUTINE_NAME from INFORMATION_SCHEMA.ROUTINES where ROUTINE_TYPE='FUNCTION' AND ROUTINE_NAME NOT LIKE 'fn_%' AND DATA_TYPE='TABLE' ORDER BY ROUTINE_SCHEMA, ROUTINE_NAME", connectionString);
da.SelectCommand.CommandTimeout = TdoCodeGenerator.Properties.Settings.Default.SQLServerCommandTimeOut;
DataTable dtUserTableFunctions = new DataTable("User Table-valued Functions");
da.Fill(dtUserTableFunctions);
string[] userTableFunctions = new string[dtUserTableFunctions.Rows.Count];
for (int i = 0; i < userTableFunctions.Length; i++)
{
userTableFunctions[i] = TdoDiscovery.EnclosedString((string)dtUserTableFunctions.Rows[i]["ROUTINE_SCHEMA"]) + "." + TdoDiscovery.EnclosedString(((string)dtUserTableFunctions.Rows[i]["ROUTINE_NAME"]));
}
return userTableFunctions;
}
public static string[] GetSqlDataSources()
{
DataTable dt=System.Data.Sql.SqlDataSourceEnumerator.Instance.GetDataSources();
string[] servers = new string[dt.Rows.Count];
int count = 0;
foreach (DataRow dr in dt.Rows)
{
string ServerName = dr["ServerName"].ToString().Trim().ToUpper();
string InstanceName=null;
//string Version=null;
if (dr["InstanceName"]!=null && dr["InstanceName"]!=DBNull.Value)
InstanceName = dr["InstanceName"].ToString().Trim().ToUpper();
servers[count] = ServerName;
if (!String.IsNullOrEmpty(InstanceName))
servers[count] += "\\" + InstanceName;
//if (dr["Version"] != null && dr["Version"] != DBNull.Value)
// Version = dr["Version"].ToString().Trim().ToUpper();
//if (!String.IsNullOrEmpty(Version))
// servers[count] += " - " + Version;
count++;
}
Array.Sort<string>(servers);
return servers;
}
public static string[] GetDatabases(string connectionString, int connectionTimeOut)
{
SqlConnection sqlConnection=null;
string[] databasesArray;
StringCollection databasesCollection = new StringCollection();
try
{
sqlConnection = new SqlConnection(connectionString.TrimEnd(';')+";Connection Timeout="+connectionTimeOut.ToString());
sqlConnection.Open();
string sqlversion = sqlConnection.ServerVersion;
sqlversion = sqlversion.Split('.')[0]; //major version
int intsqlversion = Convert.ToInt32(sqlversion);
string sql=String.Empty;
if (intsqlversion>=9)
sql = "use master; select name from sys.databases order by name";
else
sql = "use master; select name from sysdatabases order by name";
SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
sqlCommand.CommandTimeout = TdoCodeGenerator.Properties.Settings.Default.SQLServerCommandTimeOut;
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
while (sqlDataReader.Read())
{
databasesCollection.Add(sqlDataReader["name"].ToString());
}
sqlDataReader.Close();
databasesArray = new string[databasesCollection.Count];
databasesCollection.CopyTo(databasesArray, 0);
return databasesArray;
}
finally
{
if (sqlConnection!=null && sqlConnection.State == ConnectionState.Open)
sqlConnection.Close();
}
}
public static DataRelation[] GetFKDataRelations(SqlConnection sqlConnection, DataSet dataSet, string tableName, bool createConstrain)
{
List<DataRelation> dataRelations = new List<DataRelation>();
SqlCommand cmd = new SqlCommand("sp_fkeys", sqlConnection);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter pkTableName=new SqlParameter("@pktable_name",(object)tableName);
cmd.Parameters.Add(pkTableName);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dtRelations=new DataTable();
da.Fill(dtRelations);
while (dtRelations.Rows.Count > 0)
{
string fkName = (string)dtRelations.Rows[0]["FK_NAME"];
DataRow[] fkeys = dtRelations.Select(String.Format("FK_NAME='{0}'", fkName.Replace("'", "''")));
string PKTableName = (string)fkeys[0]["PKTABLE_NAME"];
string FKTableName = (string)fkeys[0]["FKTABLE_NAME"];
if (PKTableName.Contains(" ")) PKTableName = String.Format("[{0}]", PKTableName);
if (FKTableName.Contains(" ")) FKTableName = String.Format("[{0}]", FKTableName);
DataColumn[] parentColumns = new DataColumn[fkeys.Length];
DataColumn[] childColumns = new DataColumn[fkeys.Length];
bool FKTableExists = false;
for (int i = 0; i < fkeys.Length; i++)
{
if (dataSet.Tables.Contains(FKTableName))
{
FKTableExists = true;
parentColumns[i] = dataSet.Tables[PKTableName].Columns[(string)fkeys[i]["PKCOLUMN_NAME"]];
childColumns[i] = dataSet.Tables[FKTableName].Columns[(string)fkeys[i]["FKCOLUMN_NAME"]];
}
fkeys[i].Delete();
}
if (FKTableExists)
{
bool relationAlreadyExists = false;
foreach (DataRelation dr in dataRelations)
{
if (dr.RelationName == fkName)
{
relationAlreadyExists = true;
break;
}
}
if (!relationAlreadyExists)
dataRelations.Add(new DataRelation(fkName, parentColumns, childColumns, createConstrain));
}
dtRelations.AcceptChanges();
}
DataRelation[] result = new DataRelation[dataRelations.Count];
dataRelations.CopyTo(result);
return result;
}
public static void SetFKDataRelations(SqlConnection sqlConnection, ref DataSet dataSet, bool createConstrain)
{
foreach (DataTable table in dataSet.Tables)
{
DataRelation[] relations = TdoDiscovery.GetFKDataRelations(sqlConnection, dataSet, table.TableName, createConstrain);
foreach (DataRelation rel in relations)
{
try
{
if (!dataSet.Relations.Contains(rel.RelationName))
dataSet.Relations.Add(rel);
}
catch (Exception ex)
{
throw new Exception(String.Format("An error has occurred while adding relation {0} between {1} and {2}", rel.RelationName, rel.ParentTable.TableName, rel.ChildTable.TableName),ex);
}
}
}
}
internal static string EnclosedString(string s)
{
if (s.IndexOfAny(new char[] { ' ', ',', '.', '-' }) != -1)
{
return String.Format("[{0}]", s);
}
else
{
return s;
}
}
}
}
|