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