private void btnConnect_Click(object sender, EventArgs e)
{
string connStr = GetConnectionString();
DbObject dbo = new DbObject(connStr);
string databasesQuery = string.Empty;
string serverVersion = GetServerVersion();
if (int.Parse(serverVersion) <= 8)
{
databasesQuery = Resources.strDatabasesList2000;
}
else
{
databasesQuery = Resources.strDatabasesList2005;
}
//try
{
DataSet dsDatabases = dbo.RunQuery(databasesQuery, "Databases");
tvEntities.Nodes.Clear();
//TreeGenerator.GenerateTreeView(tvEntities, dsDatabases);
foreach (DataRow parentRow in dsDatabases.Tables[0].Rows)
{
tvEntities.Nodes.Add(parentRow["Text"].ToString());
}
if (tvEntities.Nodes.Count > 0)
{
foreach (TreeNode dbNode in tvEntities.Nodes)
{
DataSet dsTables = dbo.RunQuery(string.Format(Resources.strTablesList, dbNode.Text), "Tables");
foreach (DataRow tableRow in dsTables.Tables[0].Rows)
{
TreeNode node = new TreeNode(tableRow["Table_Name"].ToString(), 1, 1);
dbNode.Nodes.Add(node);
}
}
}
}
//catch (Exception ex)
//{
// MessageBox.Show(ex.Message);
//}
}
private void btnRunSQL_Click(object sender, EventArgs e)
{
try
{
if (txtSQL.Document.Text != string.Empty)
{
string[] seperators = new string[] { "GO" };
string[] commands = txtSQL.Document.Text.Split(seperators, StringSplitOptions.None);
string dbName = tvEntities.SelectedNode.Parent.Text;
string connStr = GetConnectionString(dbName);
DbObject dbo = new DbObject(connStr);
foreach (string command in commands)
{
dbo.RunQuery(command, "tmp");
}
}
}
catch (Exception exp)
{
MessageBox.Show(exp.Message);
}
}
private void tbtnGenerate_Click(object sender, EventArgs e)
{
if (tvEntities.SelectedNode != null && tvEntities.SelectedNode.Level == 1)
{
if (tvEntities.SelectedNode != null)
{
string connStr = GetConnectionString();
DbObject dbo = new DbObject(connStr);
string dbName = tvEntities.SelectedNode.Parent.Text;
string tableName = tvEntities.SelectedNode.Text;
string x = string.Format(Resources.strTablesAndColumns, dbName);
DataSet dsTablesAndColumns = dbo.RunQuery(x, "TablesAndColumns");
DataRow[] rows = dsTablesAndColumns.Tables[0].Select("Table_Name = '" + tableName + "'");
GenerateSQL(dbName, tableName, rows);
bool GenerateWrapperClass = bool.Parse(Session.LoadFromSession("GenerateWrapperClass").ToString());
if (GenerateWrapperClass == true)
GenerateWrapperClassCSharp(tableName, rows);
}
}
}
private string GetServerVersion()
{
string connStr = GetConnectionString();
DbObject dbo = new DbObject(connStr);
DataSet dsServer = dbo.RunQuery("SELECT SERVERPROPERTY('ProductVersion') as ServerVersion", "Server");
string version = string.Empty;
if (dsServer.Tables[0].Rows.Count > 0)
{
char[] seperatores = { '.' };
string[] tmpVersion = dsServer.Tables[0].Rows[0]["ServerVersion"].ToString().Split(seperatores);
version = tmpVersion[0];
}
return version;
}
// generate sql sps and class code together
private void GenerateSqlSpAndClassCodeAndGetSps()
{
//this.btnGenerateCode.Enabled = false;
#region initialize new some DataColumn objects for retreieve all informations about stored procedures in db
DataColumn dcSpName = new DataColumn("SpName");
DataColumn dcParameterName = new DataColumn("ParameterName");
DataColumn dcSystemType = new DataColumn("SystemType");
DataColumn dcLength = new DataColumn("Length");
DataColumn dcIsOutputParameter = new DataColumn("IsOutputParameter");
this.dtSPs = new DataTable();
this.dtSPs.Columns.Add(dcSpName);
this.dtSPs.Columns.Add(dcParameterName);
this.dtSPs.Columns.Add(dcSystemType);
this.dtSPs.Columns.Add(dcLength);
this.dtSPs.Columns.Add(dcIsOutputParameter);
#endregion
string dbName;
string tableName;
string colName;
string spName;
string sp_parameter;
string sqlScript = string.Empty;
TreeNode currentNode = this.treeView1.SelectedNode;
if (currentNode.Parent != null)
{
// this node either Tables/Procedures or table/procedure or column/procedureParameter
TreeNode parentNode1 = currentNode.Parent;
if (parentNode1.Parent != null)
{
// this node is table/procedure or column/procedureParameter
if (parentNode1.Parent.Parent != null)
{
// this node is column/procedureParameter
TreeNode dbNode = parentNode1.Parent.Parent;
dbName = dbNode.Text;
int tblCount = 0;
int spCount = 0;
#region Actions to Create code for Stored Procedures
if (radioGenSpCode.Checked)
{
foreach (TreeNode spNode in dbNode.Nodes[0].Nodes)
{
spCount++;
spName = spNode.Text;
foreach (TreeNode sp_paramNode in spNode.Nodes)
{
string[] txts = sp_paramNode.Text.Split(new char[] { ',' });
string colParameterName = "";
string colSystemType = "";
string colLength = "";
int colIsOutputParameter = 0;
if (txts.Length > 2)
{
colParameterName = txts[0];
colIsOutputParameter = txts[2] == "OutPut" ? 1 : 0;
string[] txts2 = txts[1].Split('(', ')');
colSystemType = txts2[0];
colLength = txts2[1];
this.dtSPs.Rows.Add(spName, colParameterName, colSystemType, colLength, colIsOutputParameter);
}
else
{
colParameterName = txts[0];
string[] txts2 = txts[1].Split('(', ')');
colSystemType = txts2[0];
colLength = txts2[1];
this.dtSPs.Rows.Add(spName, colParameterName, colSystemType, colLength, colIsOutputParameter);
}
}
this.dtSPs.Rows.Add(spName, "NULL", "NULL", "NULL", "NULL");
}
if (this.cmbLanguage.Text == "C#")
this.GenerateDocumentForSps(dbName, "DAL", Providers.CsProvider);
if (this.cmbLanguage.Text == "VB")
this.GenerateDocumentForSps(dbName, "DAL", Providers.VbProvider);
}
#endregion
#region Actions to Create class for tables or Cenerate Stored Procedures
// initialize objects for query through database to generate sql sps
string connStr = GetConnectionString();
DbObject dbo = new DbObject(connStr);
string x = string.Format(Resources.strTablesAndColumns, dbName);
DataSet dsTablesAndColumns = dbo.RunQuery(x, "TablesAndColumns");
foreach (TreeNode tblNode in dbNode.Nodes[1].Nodes)
{
tableName = tblNode.Text;
tblCount++;
if (this.radioGenSp.Checked)
{
// execute some actions, then generate sql sps
this.PrintToLog(string.Format("Generating Stored Procedures(SelectAll,SelectRow,Insert,Update,DeleteRow) for table '{0}' ...", tableName));
DataRow[] rows = dsTablesAndColumns.Tables[0].Select("Table_Name = '" + tableName + "'");
sqlScript += GenerateSQL(dbName, tableName, rows);
}
List<CodeDomDatabaseSQLDMO.Column> columnCollection = new List<CodeDomDatabaseSQLDMO.Column>();
foreach (TreeNode colNode in tblNode.Nodes)
{
string[] txts = colNode.Text.Split(new char[] { ',' });
string colName3 = txts[0];
string columnType = txts[1];
CodeDomDatabaseSQLDMO.Column col = new CodeDomDatabaseSQLDMO.Column(colName3, columnType);
columnCollection.Add(col);
}
// check if user select generate class for tables, then generate class for tables
if (this.checkGenTblClass.Checked)
{
this.PrintToLog(string.Format("Generating Class '{0}' for table '{1}' in Namespace '{2}' ...", this.ToUpperFirstChar(tableName), tableName, dbName));
System.Windows.Forms.Application.DoEvents();
this.GenerateDocumentForCode(dbName, tableName, columnCollection);
}
}
if (this.radioGenSp.Checked == true && sqlScript.Length > 0)
this.PrintToLog(string.Format("Sp generation successfully completed for {0} table(s).", tblCount));
if (tblCount > 0 && this.checkGenTblClass.Checked)
this.PrintToLog(string.Format("Code generation successfully completed for {0} table(s)", tblCount));
#endregion
}
else
{
// this node is table/procedure
TreeNode dbNode = parentNode1.Parent;
dbName = dbNode.Text;
int tblCount = 0;
int spCount = 0;
#region Actions to Create code for Stored Procedures
if (radioGenSpCode.Checked)
{
foreach (TreeNode spNode in dbNode.Nodes[0].Nodes)
{
spCount++;
spName = spNode.Text;
foreach (TreeNode sp_paramNode in spNode.Nodes)
{
string[] txts = sp_paramNode.Text.Split(new char[] { ',' });
string colParameterName = "";
string colSystemType = "";
string colLength = "";
int colIsOutputParameter = 0;
if (txts.Length > 2)
{
colParameterName = txts[0];
colIsOutputParameter = txts[2] == "OutPut" ? 1 : 0;
string[] txts2 = txts[1].Split('(', ')');
colSystemType = txts2[0];
colLength = txts2[1];
this.dtSPs.Rows.Add(spName, colParameterName, colSystemType, colLength, colIsOutputParameter);
}
else
{
colParameterName = txts[0];
string[] txts2 = txts[1].Split('(', ')');
colSystemType = txts2[0];
colLength = txts2[1];
this.dtSPs.Rows.Add(spName, colParameterName, colSystemType, colLength, colIsOutputParameter);
}
}
this.dtSPs.Rows.Add(spName, "NULL", "NULL", "NULL", "NULL");
}
if (this.cmbLanguage.Text == "C#")
this.GenerateDocumentForSps(dbName, "DAL", Providers.CsProvider);
if (this.cmbLanguage.Text == "VB")
this.GenerateDocumentForSps(dbName, "DAL", Providers.VbProvider);
}
#endregion
#region Actions to Create class for tables or Cenerate Stored Procedures
// initialize objects for query through database to generate sql sps
string connStr = GetConnectionString();
DbObject dbo = new DbObject(connStr);
string x = string.Format(Resources.strTablesAndColumns, dbName);
DataSet dsTablesAndColumns = dbo.RunQuery(x, "TablesAndColumns");
foreach (TreeNode tblNode in dbNode.Nodes[1].Nodes)
{
tableName = tblNode.Text;
tblCount++;
if (this.radioGenSp.Checked)
{
// execute some actions, then generate sql sps
this.PrintToLog(string.Format("Generating Stored Procedures(SelectAll,SelectRow,Insert,Update,DeleteRow) for table '{0}' ...", tableName));
DataRow[] rows = dsTablesAndColumns.Tables[0].Select("Table_Name = '" + tableName + "'");
sqlScript += GenerateSQL(dbName, tableName, rows);
}
List<CodeDomDatabaseSQLDMO.Column> columnCollection = new List<CodeDomDatabaseSQLDMO.Column>();
foreach (TreeNode colNode in tblNode.Nodes)
{
string[] txts = colNode.Text.Split(new char[] { ',' });
string colName3 = txts[0];
string columnType = txts[1];
CodeDomDatabaseSQLDMO.Column col = new CodeDomDatabaseSQLDMO.Column(colName3, columnType);
columnCollection.Add(col);
}
// check if user select generate class for tables, then generate class for tables
if (this.checkGenTblClass.Checked)
{
this.PrintToLog(string.Format("Generating Class '{0}' for table '{1}' in Namespace '{2}' ...", this.ToUpperFirstChar(tableName), tableName, dbName));
System.Windows.Forms.Application.DoEvents();
this.GenerateDocumentForCode(dbName, tableName, columnCollection);
}
}
if (this.radioGenSp.Checked == true && sqlScript.Length > 0)
this.PrintToLog(string.Format("Sp generation successfully completed for {0} table(s).", tblCount));
if (tblCount > 0 && this.checkGenTblClass.Checked)
this.PrintToLog(string.Format("Code generation successfully completed for {0} table(s)", tblCount));
#endregion
}
}
else
{
// this node is Tables or Procedures node
dbName = parentNode1.Text;
TreeNode dbNode = currentNode.Parent;
int tblCount = 0;
int spCount = 0;
#region Actions to Create code for Stored Procedures
if (radioGenSpCode.Checked)
{
foreach (TreeNode spNode in dbNode.Nodes[0].Nodes)
{
spCount++;
spName = spNode.Text;
foreach (TreeNode sp_paramNode in spNode.Nodes)
{
string[] txts = sp_paramNode.Text.Split(new char[] { ',' });
string colParameterName = "";
string colSystemType = "";
string colLength = "";
int colIsOutputParameter = 0;
if (txts.Length > 2)
{
colParameterName = txts[0];
colIsOutputParameter = txts[2] == "OutPut" ? 1 : 0;
string[] txts2 = txts[1].Split('(', ')');
colSystemType = txts2[0];
colLength = txts2[1];
this.dtSPs.Rows.Add(spName, colParameterName, colSystemType, colLength, colIsOutputParameter);
}
else
{
colParameterName = txts[0];
string[] txts2 = txts[1].Split('(', ')');
colSystemType = txts2[0];
colLength = txts2[1];
this.dtSPs.Rows.Add(spName, colParameterName, colSystemType, colLength, colIsOutputParameter);
}
}
this.dtSPs.Rows.Add(spName, "NULL", "NULL", "NULL", "NULL");
}
if(this.cmbLanguage.Text == "C#")
this.GenerateDocumentForSps(dbName, "DAL", Providers.CsProvider);
if(this.cmbLanguage.Text == "VB")
this.GenerateDocumentForSps(dbName, "DAL", Providers.VbProvider);
}
#endregion
#region Actions to Create class for tables or Cenerate Stored Procedures
// initialize objects for query through database to generate sql sps
string connStr = GetConnectionString();
DbObject dbo = new DbObject(connStr);
string x = string.Format(Resources.strTablesAndColumns, dbName);
DataSet dsTablesAndColumns = dbo.RunQuery(x, "TablesAndColumns");
foreach (TreeNode tblNode in currentNode.Nodes[1].Nodes)
{
tableName = tblNode.Text;
tblCount++;
if (radioGenSp.Checked)
{
// execute some actions, then generate sql sps
this.PrintToLog(string.Format("Generating Stored Procedures(SelectAll,SelectRow,Insert,Update,DeleteRow) for table '{0}' ...", tableName));
DataRow[] rows = dsTablesAndColumns.Tables[0].Select("Table_Name = '" + tableName + "'");
sqlScript += GenerateSQL(dbName, tableName, rows);
}
List<CodeDomDatabaseSQLDMO.Column> columnCollection = new List<CodeDomDatabaseSQLDMO.Column>();
foreach (TreeNode colNode in tblNode.Nodes)
{
string[] txts = colNode.Text.Split(new char[] { ',' });
string colName3 = txts[0];
string columnType = txts[1];
CodeDomDatabaseSQLDMO.Column col = new CodeDomDatabaseSQLDMO.Column(colName3, columnType);
columnCollection.Add(col);
}
// check if user select generate class for tables, then generate class for tables
if (this.checkGenTblClass.Checked)
{
this.PrintToLog(string.Format("Generating Class '{0}' for table '{1}' in Namespace '{2}' ...", this.ToUpperFirstChar(tableName), tableName, dbName));
System.Windows.Forms.Application.DoEvents();
this.GenerateDocumentForCode(dbName, tableName, columnCollection);
}
}
if (this.radioGenSp.Checked == true && sqlScript.Length > 0)
this.PrintToLog(string.Format("Sp generation successfully completed for {0} table(s).", tblCount));
if (tblCount > 0 && this.checkGenTblClass.Checked)
this.PrintToLog(string.Format("Code generation successfully completed for {0} table(s)", tblCount));
#endregion
}
}
else
{
// this node is database node
dbName = currentNode.Text;
int tblCount = 0;
int spCount = 0;
#region Actions to Create code for Stored Procedures
if (radioGenSpCode.Checked)
{
foreach (TreeNode spNode in currentNode.Nodes[0].Nodes)
{
spCount++;
spName = spNode.Text;
foreach (TreeNode sp_paramNode in spNode.Nodes)
{
string[] txts = sp_paramNode.Text.Split(new char[] { ',' });
string colParameterName = "";
string colSystemType = "";
string colLength = "";
int colIsOutputParameter = 0;
if (txts.Length > 2)
{
colParameterName = txts[0];
colIsOutputParameter = txts[2] == "OutPut" ? 1 : 0;
string[] txts2 = txts[1].Split('(', ')');
colSystemType = txts2[0];
colLength = txts2[1];
this.dtSPs.Rows.Add(spName, colParameterName, colSystemType, colLength, colIsOutputParameter);
}
else
{
colParameterName = txts[0];
string[] txts2 = txts[1].Split('(', ')');
colSystemType = txts2[0];
colLength = txts2[1];
this.dtSPs.Rows.Add(spName, colParameterName, colSystemType, colLength, colIsOutputParameter);
}
}
this.dtSPs.Rows.Add(spName, "NULL", "NULL", "NULL", "NULL");
}
if (this.cmbLanguage.Text == "C#")
this.GenerateDocumentForSps(dbName, "DAL", Providers.CsProvider);
if (this.cmbLanguage.Text == "VB")
this.GenerateDocumentForSps(dbName, "DAL", Providers.VbProvider);
}
#endregion
#region Actions to Create class for tables or Cenerate Stored Procedures
// initialize objects for query through database to generate sql sps
string connStr = GetConnectionString();
DbObject dbo = new DbObject(connStr);
string x = string.Format(Resources.strTablesAndColumns, dbName);
DataSet dsTablesAndColumns = dbo.RunQuery(x, "TablesAndColumns");
foreach (TreeNode tblNode in currentNode.Nodes[1].Nodes)
{
tableName = tblNode.Text;
tblCount++;
if (radioGenSp.Checked)
{
// execute some actions, then generate sql sps
this.PrintToLog(string.Format("Generating Stored Procedures(SelectAll,SelectRow,Insert,Update,DeleteRow) for table '{0}' ...", tableName));
DataRow[] rows = dsTablesAndColumns.Tables[0].Select("Table_Name = '" + tableName + "'");
sqlScript += GenerateSQL(dbName, tableName, rows);
}
List<CodeDomDatabaseSQLDMO.Column> columnCollection = new List<CodeDomDatabaseSQLDMO.Column>();
foreach (TreeNode colNode in tblNode.Nodes)
{
string[] txts = colNode.Text.Split(new char[] { ',' });
string colName3 = txts[0];
string columnType = txts[1];
CodeDomDatabaseSQLDMO.Column col = new CodeDomDatabaseSQLDMO.Column(colName3, columnType);
columnCollection.Add(col);
}
// check if user select generate class for tables, then generate class for tables
if (this.checkGenTblClass.Checked)
{
this.PrintToLog(string.Format("Generating Class '{0}' for table '{1}' in Namespace '{2}' ...", this.ToUpperFirstChar(tableName), tableName, dbName));
System.Windows.Forms.Application.DoEvents();
this.GenerateDocumentForCode(dbName, tableName, columnCollection);
}
}
if (this.radioGenSp.Checked == true && sqlScript.Length > 0)
this.PrintToLog(string.Format("Sp generation successfully completed for {0} table(s).", tblCount));
if (tblCount > 0 && this.checkGenTblClass.Checked)
this.PrintToLog(string.Format("Code generation successfully completed for {0} table(s)", tblCount));
#endregion
}
if (this.radioGenSp.Checked)
{
// save sql sps to file
DirectoryInfo dirInfo = Directory.CreateDirectory(this.txtSaveTo.Text + "\\Sql");
StreamWriter sw = new StreamWriter(File.Create(dirInfo.FullName + "\\" + dbName + "_Sps.sql"));
sw.Write(sqlScript);
sw.Flush();
sw.Close();
}
}