/Veerabook/databaseWithDDL.aspx.cs
C# | 298 lines | 275 code | 17 blank | 6 comment | 11 complexity | 51089a4dd223a18ce8d76d9a0751bf81 MD5 | raw file
- using System;
- using System.Collections.Generic;
- using System.Data;
- using System.Linq;
- using System.Web;
- using System.Web.UI;
- using System.Web.UI.WebControls;
- using System.Text.RegularExpressions;
- using Newtonsoft.Json.Linq;
- using Newtonsoft.Json;
- using System.Xml.Linq;
- using System.Web.UI.HtmlControls;
- using System.Xml;
- using System.Text;
- using System.IO;
- namespace Veerabook
- {
- public partial class databaseWithDDL : System.Web.UI.Page
- {
- private Xtools xtools = new Xtools();
- protected void Page_Load(object sender, EventArgs e)
- {
- if (!IsPostBack)
- {
- loadTablesName();
- }
- }
- protected void loadTableFields()
- {
- DataSet ds = new DataSet();
- //string sqlQuery = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = "BASE TABLE" AND TABLE_CATALOG = "DB_A1715B_Veera"";
- string sqlQuery = "SELECT * FROM DB_A1715B_Veera.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'"+ ddlDatabaseTables.SelectedItem.Text+"'";
- ds = xtools.SQLREAD(sqlQuery);
-
- lvTableFields.Visible = true;
- gvTrial.DataSource = ds.Tables[0];
- gvTrial.DataBind();
- lvTableFields.DataSource = ds.Tables[0];
- lvTableFields.DataBind();
- }
- protected void loadTablesName()
- {
- DataSet ds = new DataSet();
- string sqlQuery = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'DB_A1715B_Veera'";
- ds = xtools.SQLREAD(sqlQuery);
- ddlDatabaseTables.DataValueField = "Table_NAME";
- ddlDatabaseTables.DataSource = ds.Tables[0];
- ddlDatabaseTables.DataBind();
- ddlDatabaseTables.Items.Insert(0, new ListItem("Select Table", "-1"));
- }
- protected void ddlDatabaseTables_SelectedIndexChanged(object sender, EventArgs e)
- {
- loadTableFields();
- }
- int rowCount = 0;
- protected void lvTableFields_ItemDataBound(object sender, ListViewItemEventArgs e)
- {
-
- if (Page.IsPostBack)
- {
- if (e.Item.ItemType == ListViewItemType.DataItem)
- {
- DropDownList ddl = e.Item.FindControl("ddlFieldType") as DropDownList;
- DataSet ds = new DataSet();
- //string sqlQuery = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = "BASE TABLE" AND TABLE_CATALOG = "DB_A1715B_Veera"";
- string sqlQuery = "SELECT * FROM DB_A1715B_Veera.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'" + ddlDatabaseTables.SelectedItem.Text + "'";
- ds = xtools.SQLREAD(sqlQuery);
- // done
- ddl.DataSource = ds.Tables[0];
- ddl.DataValueField = "DATA_TYPE";
- ddl.SelectedValue = ds.Tables[0].Rows[rowCount]["DATA_TYPE"].ToString();
- ddl.Items.FindByValue(ddl.SelectedValue).Selected = true;
- ddl.DataBind();
- rowCount++;
- ddl.SelectedIndexChanged += new EventHandler(ddlFieldType_SelectedIndexChanged);
- }
- }
- }
- protected void ddlFieldType_SelectedIndexChanged(object sender, EventArgs e)
- {
- }
- protected void btnAddCol_Click(object sender, EventArgs e)
- {
-
- }
- protected void btnDropCol_Click(object sender, EventArgs e)
- {
- HtmlButton ddlListFind = (HtmlButton)sender;
- // Get the List View
- ListViewItem item1 = (ListViewItem)ddlListFind.NamingContainer;
- // Spot the Field Name Label we want
- Label lblFieldName = (Label)item1.FindControl("lblFieldName");
- string colName = lblFieldName.Text;
- string dsDrop;
- string sqlQuery = "ALTER TABLE " + ddlDatabaseTables.SelectedItem.Text + " DROP COLUMN "+ colName;
- dsDrop = xtools.SQLINSERT(sqlQuery);
- loadTableFields();
- }
- protected void updateXMLTbl(string tblName, string entity, string entities, string title, string pk, int fieldsNum)
- {
- ListView lvTableField = (ListView)lvTableFields;
- string fields = "";
- //"<field id = '138' type = 'number' label = 'ID' dbcolumn = 'ID' dbcolumnread = 'ID' maxlength = '100' readonly= '0' required = '' optional = '' search = '1' searchadv = '1' searchlist = '1' height = '1' width = '40' />" +
-
- string x;
- string fieldName = "";
- string fieldType = "text";
- for (int i = 0; i < fieldsNum; i++)
- {
- x = "ctrl" + i;
- ListViewDataItem item1 = (ListViewDataItem)lvTableField.FindControl(x);
- Label lblFieldName = (Label)item1.FindControl("lblFieldName");
- fieldName = lblFieldName.Text;
- TextBox txtDataType = (TextBox)item1.FindControl("txtDataType");
- DropDownList ddlDataType = (DropDownList)item1.FindControl("ddlFieldType");
- if (txtDataType.Text == null || txtDataType.Text.Trim() == "")
- {
- txtDataType.Text = ddlDataType.SelectedItem.Text;
- }
- if (txtDataType.Text == "int")
- {
- fieldType = "number";
- }
- else
- {
- fieldType = "text";
- }
- fields += "<field id = '138' type = '"+ fieldType+"' label = '"+ fieldName+"' dbcolumn = '"+ fieldName+"' dbcolumnread = '"+ fieldName+"' maxlength = '100' readonly= '0' required = '' optional = '' search = '1' searchadv = '1' searchlist = '1' height = '1' width = '100' />";
- }
- string myQuery = "<?xml version='1.0' encoding='UTF-8'?>" +
- "<form title = '"+ title+"' description = '' xmlns = 'http://www.evolutility.com' >" +
- "<data dbtable='"+tblName+"' entity='"+ entity+"' entities='"+ entities+"' icon='cube.gif' dbcolumnpk='"+pk+"' sppaging='EvoSP_PagedItem @SQLselect, @SQLtable, @SQLfrom, @SQLwhere, @SQLorderby, @SQLpk, @pageid, @pagesize' />" +
- "<panel label = '"+ title+"' width = '100' >" +
- fields +
- "</panel>" +
- "</form> ";
- myQuery = myQuery.Replace("'", @"""");
- DataSet dsID = xtools.SQLREAD("select ID from [dbo].[RCMS_XML] where tblName = '"+ tblName+"'");
- int XID = int.Parse(dsID.Tables[0].Rows[0][0].ToString());
- string updateQuery = "UPDATE RCMS_XML SET XML = '" + myQuery + "' WHERE ID = " + XID;
- createXML(myQuery, XID);
- xtools.SQLINSERT(updateQuery);
- }
- protected void createXML(string content, int XID)
- {
- XmlDocument xDoc = new XmlDocument();
- string xmlPath = "~/XML/" + XID + ".xml";
- xDoc.LoadXml(content);
- xDoc.Save(Server.MapPath(xmlPath));
- }
- protected void btnEditCol_Click(object sender, EventArgs e)
- {
- DataSet ds = xtools.SQLREAD("SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_catalog = 'DB_A1715B_Veera' AND table_name = '"+ ddlDatabaseTables.SelectedItem.Text+"'");
- int countCols = int.Parse(ds.Tables[0].Rows[0][0].ToString());
- updateXMLTbl(ddlDatabaseTables.SelectedItem.Text , "test", "tests","test","ID", countCols);
- HtmlButton ddlListFind = (HtmlButton)sender;
- // Get the List View
- ListViewItem item1 = (ListViewItem)ddlListFind.NamingContainer;
- // Spot the Field Name Label we want
- Label lblFieldName = (Label)item1.FindControl("lblFieldName");
- TextBox txtDataType = (TextBox)item1.FindControl("txtDataType");
- string colName = lblFieldName.Text;
- string dsEdit;
- string sqlQuery = "ALTER TABLE " + ddlDatabaseTables.SelectedItem.Text + " ALTER COLUMN "+colName+" "+ txtDataType.Text;
- dsEdit = xtools.SQLINSERT(sqlQuery);
- loadTableFields();
- }
- protected void btnSubmit_Click(object sender, EventArgs e)
- {
- string dsAdd;
- string sqlQuery = "ALTER TABLE " + ddlDatabaseTables.SelectedItem.Text + " ADD "+txtColName.Text+" "+txtColDataType.Text+" ";
- dsAdd = xtools.SQLINSERT(sqlQuery);
- loadTableFields();
- }
- protected void btnSubmitTbl_Click(object sender, EventArgs e)
- {
- string sqlQuer = " CREATE TABLE "+ txtTableName.Text + " (";
- string colQuery = "";
- string colNm = "";
- string colTyp = "";
- string colIdn = "";
- string colPrm = "";
- string colNull = "";
- int x = 1;
- foreach (ListViewDataItem item in lvColData.Items)
- {
-
- TextBox TxtColName2 = (TextBox)item.FindControl("txtColName2");
- colNm = " " + TxtColName2.Text+ " ";
- TextBox TxtDataType = (TextBox)item.FindControl("txtDataType");
- colTyp = " " +TxtDataType.Text+" ";
- DropDownList DdlIdentity = (DropDownList)item.FindControl("ddlIdentity");
- if (DdlIdentity.SelectedItem.Text == "YES")
- {
- colIdn = " IDENTITY(1,1) ";
- }
- DropDownList DdlPrimary = (DropDownList)item.FindControl("ddlPrimary");
- if (DdlPrimary.SelectedItem.Text == "YES")
- {
- colPrm = " PRIMARY KEY (" + colNm + ")";
- }
- DropDownList DdlNull = (DropDownList)item.FindControl("ddlNull");
- if (DdlNull.SelectedItem.Text == "NO")
- {
- colNull = " NOT NULL ";
- }
- colQuery += colNm + colTyp + colIdn + colNull;
- colIdn = colNull = "";
- colQuery += " , ";
- if (!(x < lvColData.Items.Count()))
- {
- colQuery += colPrm;
- }
- x++;
- }
- sqlQuer += " " + colQuery + ")";
- xtools.SQLREAD(sqlQuer);
- }
- protected void txtCountCols_TextChanged(object sender, EventArgs e)
- {
- DataSet ds = new DataSet();
- DataTable dt = new DataTable();
- dt.Columns.Add("IDCount", typeof(int));
- for (int i = 0; i < int.Parse(txtCountCols.Text); i++)
- {
- dt.Rows.Add((i+1));
- }
- ds.Tables.Add(dt);
- lvColData.DataSource = ds;
- lvColData.DataBind();
- upColsTbl.Update();
- }
- protected void btnDropTable_Click(object sender, EventArgs e)
- {
- string ddlChoice = ddlDatabaseTables.SelectedItem.Text;
- string qur = "DROP TABLE " + ddlChoice;
- xtools.SQLREAD(qur);
- }
- //protected void ddlFieldType_DataBound(object sender, EventArgs e)
- //{
- // DataSet ds = new DataSet();
- // //string sqlQuery = 'SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'DB_A1715B_Veera'';
- // string sqlQuery = 'SELECT * FROM DB_A1715B_Veera.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'' + ddlDatabaseTables.SelectedItem.Text + ''';
- // ds = xtools.SQLREAD(sqlQuery);
- // DropDownList ddlListFind = (DropDownList)sender;
- // // Get the List View
- // ListViewItem item1 = (ListViewItem)ddlListFind.NamingContainer;
- // // Spot the Adult Dropdownbox we want
- // DropDownList ddlFieldType = (DropDownList)item1.FindControl('ddlFieldType');
- // ddlFieldType.DataValueField = 'DATA_TYPE';
- // ddlFieldType.DataSource = ds.Tables[0];
- // ddlFieldType.DataBind();
- //}
- //protected void ddlFieldType_DataBinding(object sender, EventArgs e)
- //{
- // DataSet ds = new DataSet();
- // //string sqlQuery = 'SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'DB_A1715B_Veera'';
- // string sqlQuery = 'SELECT * FROM DB_A1715B_Veera.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'' + ddlDatabaseTables.SelectedItem.Text + ''';
- // ds = xtools.SQLREAD(sqlQuery);
- // DropDownList ddlListFind = (DropDownList)sender;
- // // Get the List View
- // ListViewItem item1 = (ListViewItem)ddlListFind.NamingContainer;
- // // Spot the Adult Dropdownbox we want
- // DropDownList ddlFieldType = (DropDownList)item1.FindControl('ddlFieldType');
- // ddlFieldType.DataValueField = 'DATA_TYPE';
- // ddlFieldType.DataSource = ds.Tables[0];
- // ddlFieldType.DataBind();
- //}
- }
- }