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(); } }