/* $Id: MSSQLDetailAnalyzer.cs,v 1.9 2005/01/26 08:26:31 larsbm Exp $
* Copyright (c) 2004 Engine EAR GmbH & Co. KG
* Developed by: Lars Behrmann, lb@engine.de
*/
using System;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace SQLToNeo.Model{
/// <summary>
/// Zusammenfassung fr MSSQLDetailAnalyzer.
/// </summary>
public class MSSQLDetailAnalyzer
{
private OleDbConnection _oleCon;
private SqlConnection _sqlCon;
#region Property get set Analyzer
private MSSQLAnalyzer _analyzer;
public MSSQLAnalyzer Analyzer
{
get { return this._analyzer; }
set { this._analyzer = value; }
}
#endregion
#region Property get set OleDbS
private OleDBServerConnector _oleDbs;
public OleDBServerConnector OleDbS
{
get { return this._oleDbs; }
set { this._oleDbs = value; }
}
#endregion
#region Property get set SqlSC
private SQLServerConnector _sqlsc;
public SQLServerConnector SqlSC
{
get { return this._sqlsc; }
set { this._sqlsc = value; }
}
#endregion
public MSSQLDetailAnalyzer(MSSQLAnalyzer analyzer)
{
Analyzer = analyzer;
CreateOleDbServerConnInst();
CreateSqlServerConnInst();
CreateConnections();
}
private void CreateOleDbServerConnInst()
{
OleDbS = new OleDBServerConnector();
//Don't care about the possibilty of "false",
//because i connect with the same string at the
//beginning of the programm and catch errors there!
OleDbS.CreateConnectionString(Analyzer.Server,Analyzer.User,Analyzer.Password,Analyzer.NTAuthentication);
}
private void CreateSqlServerConnInst()
{
SqlSC = new SQLServerConnector();
SqlSC.CreateConnectionString(Analyzer.Server,Analyzer.User,Analyzer.Password,Analyzer.NTAuthentication);
}
private void CreateConnections()
{
string cOle = OleDbS.ConnectionString+";Initial Catalog="+Analyzer.Catalog;
string cSql = SqlSC.ConnectionString+";Initial Catalog="+Analyzer.Catalog;
try
{
_oleCon = new OleDbConnection(cOle);
}
catch(Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message + @"\n" + cOle);
}
try
{
_sqlCon = new SqlConnection(cSql);
}
catch(Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message + @"\n" + cSql);
}
}
public void Fill()
{
_oleCon.Open();
_sqlCon.Open();
GetTables();
GetTableColumns();
_oleCon.Close();
_sqlCon.Close();
}
private void GetTables()
{
try
{
//_oleCon.Open();
DataTable dt = _oleCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] {null, null, null, "Table"});
//_oleCon.Close();
InsertIntoTableTable(dt);
}
catch(Exception ex)
{
Console.WriteLine("MSSQLDetailAnalyzer Exception in GetTables(): {0}", ex.Message);
}
}
private void InsertIntoTableTable(DataTable dt)
{
foreach(DataRow dr in dt.Rows)
{
DataRow drn = Analyzer.DS.Tables["table"].NewRow();
drn["name"] = dr[2].ToString();
drn["defaultProperty"] = "";
drn["idMethod"] = "null";
drn["manyToMany"] = false;
drn["create"] = true;
Analyzer.DS.Tables["table"].Rows.Add(drn);
Console.WriteLine("Table: {0} ", drn["name"].ToString());
}
}
private void GetTableColumns()
{
try
{
foreach(DataRow dr in Analyzer.DS.Tables["table"].Rows)
{
string tmpSQL = "SELECT *, COLUMNPROPERTY(OBJECT_ID('" + dr["name"].ToString() + "'), COLUMN_NAME, 'IsIdentity') AS IsIdentity FROM Information_Schema.columns where table_name = '" + dr["name"].ToString() + "'";
SqlDataAdapter da = new SqlDataAdapter(tmpSQL, _sqlCon);
DataTable dt = new DataTable();
da.Fill(dt);
InsertIntoTableColumns(dt, Convert.ToInt32(dr["id"].ToString()));
DataTable dtPk = _oleCon.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys,
new object[] {null, null, dr["name"].ToString()});
SetPrimaryKeys(dtPk, Convert.ToInt32(dr["id"].ToString()));
DataTable dti = _oleCon.GetOleDbSchemaTable(OleDbSchemaGuid.Foreign_Keys,
new object[] {null, null, dr["name"].ToString(), null});
InsertIntoTableIForeignkeys(dti, Convert.ToInt32(dr["id"].ToString()));
GetTableForeignKeys(dr["name"].ToString(), Convert.ToInt32(dr["id"].ToString()));
}
//_sqlCon.Close();
//_oleCon.Close();
}
catch(Exception ex)
{
Console.WriteLine("MSSQLDetailAnalyzer Exception in GetTableColumns(): {0}", ex.Message);
System.Windows.Forms.MessageBox.Show("MSSQLDetailAnalyzer Exception in GetTableColumns(): "+ ex.Message);
}
}
private void InsertIntoTableColumns(DataTable dt, int tableid)
{
IDBTypeConverter converter = new SQLDbTypeConverter();
foreach(DataRow dr in dt.Rows)
{
try
{
DataRow drn = Analyzer.DS.Tables["column"].NewRow();
drn["tableid"] = tableid;
drn["name"] = dr["COLUMN_NAME"].ToString();
drn["create"] = true;
drn["type"] = converter.Convert(dr["DATA_TYPE"].ToString());
drn["required"] = (dr["IS_NULLABLE"].ToString().Equals("No")) ? false : true;
Console.WriteLine("{0} required {1}", drn["name"], drn["required"]);
drn["size"] = dr["CHARACTER_MAXIMUM_LENGTH"];
drn["primaryKey"] = false;
drn["hidden"] = false;
drn["identity"] = (dr["IsIdentity"].ToString().Equals("0")) ? false : true;
Analyzer.DS.Tables["column"].Rows.Add(drn);
}
catch(Exception ex)
{
System.Windows.Forms.MessageBox.Show("InsertIntoTableColumns : "+ex.Message.ToString());
}
}
}
private void SetPrimaryKeys(DataTable dt, int tableid)
{
foreach(DataRow d in dt.Rows)
foreach(DataRow dr in Analyzer.DS.Tables["column"].Rows)
{
if(dr["name"].ToString() == d["COLUMN_NAME"].ToString()
&& dr["tableid"].ToString() == tableid.ToString())
{
dr["primaryKey"] = true;
Console.WriteLine("Table id {0}, PK = {1}", tableid, dr["name"]);
}
}
}
private void InsertIntoTableIForeignkeys(DataTable dt, int tableid)
{
foreach(DataRow dr in dt.Rows)
{
DataRow drn = Analyzer.DS.Tables["iforeignkey"].NewRow();
drn["tableid"] = tableid;
drn["foreigntable"] = dr["FK_TABLE_NAME"].ToString();
drn["local"] = dr["PK_COLUMN_NAME"].ToString();
drn["foreign"] = dr["FK_COLUMN_NAME"].ToString();
drn["onUpdate"] = GetUpdateAndDeleteAction(dr["UPDATE_RULE"].ToString());
drn["onDelete"] = GetUpdateAndDeleteAction(dr["DELETE_RULE"].ToString());
Analyzer.DS.Tables["iforeignkey"].Rows.Add(drn);
}
}
private void GetTableForeignKeys(string tablename, int tableid)
{
//TODO: PRIO 3 : Found a OleDB way to recieve foreignkeys!
//bool NeedToCloseConn = false;
string sp = "sp_fkeys";
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = sp;
cmd.Connection = _sqlCon;
SqlParameter par = new SqlParameter("@fktable_name", SqlDbType.VarChar );
par.Direction = ParameterDirection.Input;
par.Value = tablename;
cmd.Parameters.Add(par);
SqlDataAdapter sda = new SqlDataAdapter();
sda.SelectCommand = cmd;
DataSet ds = new DataSet();
try
{
// if (_sqlCon.State != System.Data.ConnectionState.Open)
// {
// _sqlCon.Open();
// NeedToCloseConn = true;
// }
sda.Fill(ds, tablename);
if(ds.Tables.Count == 1)
InsertIntoTableForeignkeys(ds.Tables[0], tableid);
}
catch(SqlException ex)
{
System.Windows.Forms.MessageBox.Show("Failed to connect via SqlAdapter!\n"+ex.Message, "Sql Connection Error");
}
// finally
// {
// if (NeedToCloseConn)
// _sqlCon.Close();
// }
}
private void InsertIntoTableForeignkeys(DataTable dt, int tableid)
{
foreach(DataRow dr in dt.Rows)
{
DataRow drn = Analyzer.DS.Tables["foreignkey"].NewRow();
drn["tableid"] = tableid;
drn["foreigntable"] = dr["PKTABLE_NAME"].ToString();
drn["local"] = dr["FKCOLUMN_NAME"].ToString();
drn["foreign"] = dr["PKCOLUMN_NAME"].ToString();
drn["onUpdate"] = GetUpdateAndDeleteAction(dr["UPDATE_RULE"].ToString());
drn["onDelete"] = GetUpdateAndDeleteAction(dr["DELETE_RULE"].ToString());
Analyzer.DS.Tables["foreignkey"].Rows.Add(drn);
}
}
private string GetUpdateAndDeleteAction(string action)
{
Console.WriteLine("Update and delete {0}", action);
switch(action.ToLower())
{
case "0":
return "cascade";
case "1":
return "none";
case "2":
return "setnull";
case "no action":
return "none";
case "cascade":
return "cascade";
default:
return "none"; //Should never happen
}
}
}
}
/*
* $Log: MSSQLDetailAnalyzer.cs,v $
* Revision 1.9 2005/01/26 08:26:31 larsbm
* Fixed required bug - true and false were switched
* Fixed bug in check for conflicts, javaName and name were switched
*
* Revision 1.8 2005/01/12 06:36:30 larsbm
* - Bugfixing: javaName and name were permuted
*
* Revision 1.7 2004/11/29 12:02:26 larsbm
* - bugfix in writing foregein and iforeignrelations, now, foreigntable, local and foreign fields refers to the name
* field instead of the javaName field
*
*/
|