PageRenderTime 2ms CodeModel.GetById 3ms app.highlight 14ms RepoModel.GetById 1ms app.codeStats 0ms

/Veerabook/databaseWithDDL.aspx.cs

https://bitbucket.org/veerabooking/veerabooking
C# | 298 lines | 275 code | 17 blank | 6 comment | 11 complexity | 51089a4dd223a18ce8d76d9a0751bf81 MD5 | raw file
  1using System;
  2using System.Collections.Generic;
  3using System.Data;
  4using System.Linq;
  5using System.Web;
  6using System.Web.UI;
  7using System.Web.UI.WebControls;
  8using System.Text.RegularExpressions;
  9using Newtonsoft.Json.Linq;
 10using Newtonsoft.Json;
 11using System.Xml.Linq;
 12using System.Web.UI.HtmlControls;
 13using System.Xml;
 14using System.Text;
 15using System.IO;
 16
 17namespace Veerabook
 18{
 19    public partial class databaseWithDDL : System.Web.UI.Page
 20    {
 21        private Xtools xtools = new Xtools();
 22        protected void Page_Load(object sender, EventArgs e)
 23        {
 24            if (!IsPostBack)
 25            {
 26                loadTablesName();
 27            }
 28        }
 29        protected void loadTableFields()
 30        {
 31            DataSet ds = new DataSet();
 32            //string sqlQuery = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = "BASE TABLE" AND TABLE_CATALOG = "DB_A1715B_Veera"";
 33            string sqlQuery = "SELECT * FROM DB_A1715B_Veera.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'"+ ddlDatabaseTables.SelectedItem.Text+"'";
 34            ds = xtools.SQLREAD(sqlQuery);
 35
 36            
 37            lvTableFields.Visible = true;
 38            gvTrial.DataSource = ds.Tables[0];
 39            gvTrial.DataBind();
 40            lvTableFields.DataSource = ds.Tables[0];
 41            lvTableFields.DataBind();
 42        }
 43
 44        protected void loadTablesName()
 45        {
 46            DataSet ds = new DataSet();
 47            string sqlQuery = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'DB_A1715B_Veera'";
 48            ds = xtools.SQLREAD(sqlQuery);
 49
 50
 51            ddlDatabaseTables.DataValueField = "Table_NAME";
 52
 53            ddlDatabaseTables.DataSource = ds.Tables[0];
 54            ddlDatabaseTables.DataBind();
 55            ddlDatabaseTables.Items.Insert(0, new ListItem("Select Table", "-1"));
 56
 57        }
 58
 59        protected void ddlDatabaseTables_SelectedIndexChanged(object sender, EventArgs e)
 60        {
 61            loadTableFields();
 62        }
 63        int rowCount = 0;
 64        protected void lvTableFields_ItemDataBound(object sender, ListViewItemEventArgs e)
 65        {
 66            
 67            if (Page.IsPostBack)
 68            {
 69                if (e.Item.ItemType == ListViewItemType.DataItem)
 70                {
 71                    DropDownList ddl = e.Item.FindControl("ddlFieldType") as DropDownList;
 72                    DataSet ds = new DataSet();
 73                    //string sqlQuery = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = "BASE TABLE" AND TABLE_CATALOG = "DB_A1715B_Veera"";
 74                    string sqlQuery = "SELECT * FROM DB_A1715B_Veera.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'" + ddlDatabaseTables.SelectedItem.Text + "'";
 75                    ds = xtools.SQLREAD(sqlQuery);
 76                    // done
 77                    ddl.DataSource = ds.Tables[0];
 78                    ddl.DataValueField = "DATA_TYPE";
 79
 80                    ddl.SelectedValue = ds.Tables[0].Rows[rowCount]["DATA_TYPE"].ToString();
 81                    ddl.Items.FindByValue(ddl.SelectedValue).Selected = true;
 82                    ddl.DataBind();
 83                    rowCount++;
 84                    ddl.SelectedIndexChanged += new EventHandler(ddlFieldType_SelectedIndexChanged);
 85
 86                }
 87            }
 88        }
 89
 90        protected void ddlFieldType_SelectedIndexChanged(object sender, EventArgs e)
 91        {
 92
 93        }
 94
 95        protected void btnAddCol_Click(object sender, EventArgs e)
 96        {
 97            
 98        }
 99        protected void btnDropCol_Click(object sender, EventArgs e)
100        {
101            HtmlButton ddlListFind = (HtmlButton)sender;
102            // Get the List View
103            ListViewItem item1 = (ListViewItem)ddlListFind.NamingContainer;
104            // Spot the Field Name Label we want 
105            Label lblFieldName = (Label)item1.FindControl("lblFieldName");
106            string colName = lblFieldName.Text;
107            string dsDrop;
108            string sqlQuery = "ALTER TABLE " + ddlDatabaseTables.SelectedItem.Text + " DROP COLUMN "+ colName;
109            dsDrop = xtools.SQLINSERT(sqlQuery);
110            loadTableFields();
111        }
112        protected void updateXMLTbl(string tblName, string entity, string entities, string title, string pk, int fieldsNum)
113        {
114            ListView lvTableField = (ListView)lvTableFields;
115            string fields = "";
116                //"<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' />" +
117                
118            string x;
119            string fieldName = "";
120            string fieldType = "text";
121            for (int i = 0; i < fieldsNum; i++)
122            {
123                x = "ctrl" + i;
124                ListViewDataItem item1 = (ListViewDataItem)lvTableField.FindControl(x);
125                Label lblFieldName = (Label)item1.FindControl("lblFieldName");
126                fieldName = lblFieldName.Text;
127                TextBox txtDataType = (TextBox)item1.FindControl("txtDataType");
128                DropDownList ddlDataType = (DropDownList)item1.FindControl("ddlFieldType");
129                if (txtDataType.Text == null || txtDataType.Text.Trim() == "")
130                {
131                    txtDataType.Text = ddlDataType.SelectedItem.Text;
132                }
133                if (txtDataType.Text == "int")
134                {
135                    fieldType = "number";
136                }
137                else
138                {
139                    fieldType = "text";
140                }
141                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' />";
142            }
143            string myQuery = "<?xml version='1.0' encoding='UTF-8'?>" +
144                "<form title = '"+ title+"' description = '' xmlns = 'http://www.evolutility.com' >" +
145                "<data dbtable='"+tblName+"' entity='"+ entity+"' entities='"+ entities+"' icon='cube.gif' dbcolumnpk='"+pk+"' sppaging='EvoSP_PagedItem @SQLselect, @SQLtable, @SQLfrom, @SQLwhere, @SQLorderby, @SQLpk, @pageid, @pagesize' />" +
146                "<panel label = '"+ title+"' width = '100' >" +
147                fields +
148                "</panel>" +
149                "</form> ";
150            myQuery = myQuery.Replace("'", @"""");
151            DataSet dsID = xtools.SQLREAD("select ID from [dbo].[RCMS_XML] where tblName = '"+ tblName+"'");
152            int XID = int.Parse(dsID.Tables[0].Rows[0][0].ToString());
153            string updateQuery = "UPDATE RCMS_XML SET XML = '" + myQuery + "'  WHERE ID = " + XID;
154            createXML(myQuery, XID);
155            xtools.SQLINSERT(updateQuery);
156        }
157        protected void createXML(string content, int XID)
158        {
159            XmlDocument xDoc = new XmlDocument();
160
161            string xmlPath = "~/XML/" + XID + ".xml";
162            xDoc.LoadXml(content);
163            xDoc.Save(Server.MapPath(xmlPath));
164
165        }
166        protected void btnEditCol_Click(object sender, EventArgs e)
167        {
168            DataSet ds = xtools.SQLREAD("SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS  WHERE table_catalog = 'DB_A1715B_Veera'  AND table_name = '"+ ddlDatabaseTables.SelectedItem.Text+"'");
169            int countCols = int.Parse(ds.Tables[0].Rows[0][0].ToString());
170            updateXMLTbl(ddlDatabaseTables.SelectedItem.Text , "test", "tests","test","ID", countCols);
171            HtmlButton ddlListFind = (HtmlButton)sender;
172            // Get the List View
173            ListViewItem item1 = (ListViewItem)ddlListFind.NamingContainer;
174            // Spot the Field Name Label we want 
175            Label lblFieldName = (Label)item1.FindControl("lblFieldName");
176            TextBox txtDataType = (TextBox)item1.FindControl("txtDataType");
177            string colName = lblFieldName.Text;
178            string dsEdit;
179            string sqlQuery = "ALTER TABLE " + ddlDatabaseTables.SelectedItem.Text + " ALTER COLUMN "+colName+" "+ txtDataType.Text;
180            dsEdit = xtools.SQLINSERT(sqlQuery);
181            loadTableFields();
182        }
183        protected void btnSubmit_Click(object sender, EventArgs e)
184        {
185            string dsAdd;
186            string sqlQuery = "ALTER TABLE " + ddlDatabaseTables.SelectedItem.Text + " ADD "+txtColName.Text+" "+txtColDataType.Text+" ";
187            dsAdd = xtools.SQLINSERT(sqlQuery);
188            loadTableFields();
189        }
190
191        protected void btnSubmitTbl_Click(object sender, EventArgs e)
192        {
193            string sqlQuer = " CREATE TABLE "+ txtTableName.Text + " (";
194            string colQuery = "";
195            string colNm = "";
196            string colTyp = "";
197            string colIdn = "";
198            string colPrm = "";
199            string colNull = "";
200            int x = 1;
201            foreach (ListViewDataItem item in lvColData.Items)
202            {
203                
204                TextBox TxtColName2 = (TextBox)item.FindControl("txtColName2");
205                colNm = " " + TxtColName2.Text+ " ";
206                TextBox TxtDataType = (TextBox)item.FindControl("txtDataType");
207                colTyp = " " +TxtDataType.Text+" ";
208                DropDownList DdlIdentity = (DropDownList)item.FindControl("ddlIdentity");
209                if (DdlIdentity.SelectedItem.Text == "YES")
210                {
211                    colIdn = " IDENTITY(1,1) ";
212                }
213                DropDownList DdlPrimary = (DropDownList)item.FindControl("ddlPrimary");
214                if (DdlPrimary.SelectedItem.Text == "YES")
215                {
216                    colPrm = " PRIMARY KEY (" + colNm + ")";
217                }
218                DropDownList DdlNull = (DropDownList)item.FindControl("ddlNull");
219                if (DdlNull.SelectedItem.Text == "NO")
220                {
221                    colNull =  " NOT NULL "; 
222                }
223                colQuery += colNm + colTyp + colIdn + colNull;
224                colIdn = colNull = "";
225                colQuery += " , ";
226                if (!(x < lvColData.Items.Count()))
227                {
228                    colQuery += colPrm;
229                }
230                x++;
231            }
232            sqlQuer += " " + colQuery + ")";
233            xtools.SQLREAD(sqlQuer);
234        }
235
236        protected void txtCountCols_TextChanged(object sender, EventArgs e)
237        {
238            DataSet ds = new DataSet();
239            DataTable dt = new DataTable();
240            dt.Columns.Add("IDCount", typeof(int));
241            for (int i = 0; i < int.Parse(txtCountCols.Text); i++)
242            {
243                dt.Rows.Add((i+1));
244            }
245            ds.Tables.Add(dt);
246            lvColData.DataSource = ds;
247            lvColData.DataBind();
248
249            upColsTbl.Update();
250        }
251
252        protected void btnDropTable_Click(object sender, EventArgs e)
253        {
254            string ddlChoice =  ddlDatabaseTables.SelectedItem.Text;
255            string qur = "DROP TABLE " + ddlChoice;
256            xtools.SQLREAD(qur);
257        }
258
259        //protected void ddlFieldType_DataBound(object sender, EventArgs e)
260        //{
261        //    DataSet ds = new DataSet();
262        //    //string sqlQuery = 'SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'DB_A1715B_Veera'';
263        //    string sqlQuery = 'SELECT * FROM DB_A1715B_Veera.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'' + ddlDatabaseTables.SelectedItem.Text + ''';
264        //    ds = xtools.SQLREAD(sqlQuery);
265
266
267        //    DropDownList ddlListFind = (DropDownList)sender;
268        //    // Get the List View
269        //    ListViewItem item1 = (ListViewItem)ddlListFind.NamingContainer;
270        //    // Spot the Adult Dropdownbox we want 
271        //    DropDownList ddlFieldType = (DropDownList)item1.FindControl('ddlFieldType');
272
273        //    ddlFieldType.DataValueField = 'DATA_TYPE';
274        //    ddlFieldType.DataSource = ds.Tables[0];
275        //    ddlFieldType.DataBind();
276
277        //}
278
279        //protected void ddlFieldType_DataBinding(object sender, EventArgs e)
280        //{
281        //    DataSet ds = new DataSet();
282        //    //string sqlQuery = 'SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'DB_A1715B_Veera'';
283        //    string sqlQuery = 'SELECT * FROM DB_A1715B_Veera.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'' + ddlDatabaseTables.SelectedItem.Text + ''';
284        //    ds = xtools.SQLREAD(sqlQuery);
285
286
287        //    DropDownList ddlListFind = (DropDownList)sender;
288        //    // Get the List View
289        //    ListViewItem item1 = (ListViewItem)ddlListFind.NamingContainer;
290        //    // Spot the Adult Dropdownbox we want 
291        //    DropDownList ddlFieldType = (DropDownList)item1.FindControl('ddlFieldType');
292
293        //    ddlFieldType.DataValueField = 'DATA_TYPE';
294        //    ddlFieldType.DataSource = ds.Tables[0];
295        //    ddlFieldType.DataBind();
296        //}
297    }
298}