Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- //SchemaCreation View
- <script type="text/javascript">
- var TableWidth = null;
- var TableHeight = null;
- $(document).ready(function ()
- {
- //debugger;
- TableWidth = $("#list").width();
- TableHeight = $("#Div1").height();
- });
- jQuery(document).ready(function ()
- {
- //debugger;
- jQuery("#list").jqGrid({
- url: '/Schema/GetGridData?DataSource=' + '@ViewData["ServerName"]' + '&InitialCatalog=' + '@ViewData["DataBaseName"]' + '&UserName=' + '@ViewData["UserName"]' + '&PassWord=' + '@ViewData["PassWord"]',
- datatype: 'json',
- mtype: 'GET',
- colNames: ['Table Names', 'Column Names'],
- colModel: [
- { name: 'Table Names', index: 'Table Names', width: 40, align: 'left', Style:'padding-left:15px;'},
- { name: 'Column Names', index: 'Column Names', width: 40, align: 'left'},//, width: 80, formatter: 'select', type:'select', edittype: 'select', editoptions: { value: "1:One;2:Two" } }
- ],
- pager: jQuery('#pager'),
- rowNum: 10,
- rowList: [5, 10, 20, 50],
- sortname: 'Table Names',
- sortorder: "desc",
- multiselect: true,
- viewrecords: true,
- width: TableWidth,
- Height: 500,
- scroll: true,
- //caption: 'My first grid'
- });
- });
- </script>
- //SchemaController:-
- public ActionResult GetGridData(string sidx, string sord, int page, int rows, string DataSource, string InitialCatalog, string UserName, string PassWord)
- {
- DataSource = CrossCutting.EncryptDecrypt.Decrypt(DataSource, ConfigurationManager.AppSettings["EncryptDecryptKey"]);
- InitialCatalog = CrossCutting.EncryptDecrypt.Decrypt(InitialCatalog, ConfigurationManager.AppSettings["EncryptDecryptKey"]);
- UserName = CrossCutting.EncryptDecrypt.Decrypt(UserName, ConfigurationManager.AppSettings["EncryptDecryptKey"]);
- PassWord = CrossCutting.EncryptDecrypt.Decrypt(PassWord, ConfigurationManager.AppSettings["EncryptDecryptKey"]);
- return Content(JsonHelper.JsonForJqgrid(GetDataTable(sidx, sord, page, rows, DataSource, InitialCatalog, UserName, PassWord), rows, GetTotalCount(DataSource, InitialCatalog, UserName, PassWord), page), "application/json");
- }
- public DataTable GetDataTable(string sidx, string sord, int page, int pageSize, string DataSource, string InitialCatalog, string UserName, string PassWord)
- {
- int startIndex = (page - 1) * pageSize;
- int endIndex = page * pageSize;
- string sql = "select name from sys.tables";
- DataTable dt = new DataTable();
- string connString = "Data Source=" + DataSource + ";Initial Catalog=" + InitialCatalog + ";User ID=" + UserName + ";pwd=" + PassWord;
- SqlConnection conn = new SqlConnection(connString);
- SqlDataAdapter adap = new SqlDataAdapter(sql, conn);
- var rows = adap.Fill(dt);
- return dt;
- }
- public int GetTotalCount(string DataSource, string InitialCatalog, string UserName, string PassWord)
- {
- SqlConnection sqlConnection2 = null;
- string connString = "Data Source=" + DataSource + ";Initial Catalog=" + InitialCatalog + ";User ID=" + UserName + ";pwd=" + PassWord;
- try
- {
- string cmdText = "select name from sys.tables";
- List<string> tablenames = new List<string>();
- using (sqlConnection2 = new SqlConnection(connString))
- {
- sqlConnection2.Open();
- using (SqlCommand sqlCmd = new SqlCommand(cmdText, sqlConnection2))
- {
- SqlDataReader reader = sqlCmd.ExecuteReader();
- while (reader.Read())
- {
- tablenames.Add(reader.GetValue(0).ToString());
- //Count = (int)sqlCmd.ExecuteScalar();
- }
- }
- }
- return tablenames.Count();
- }
- catch
- {
- }
- finally
- {
- try
- {
- if (ConnectionState.Closed != sqlConnection2.State)
- {
- sqlConnection2.Close();
- }
- }
- catch
- {
- }
- }
- return -1;
- }
- //JsonHelper.cs:-
- using System;
- using System.Collections.Generic;
- using System.Data;
- using System.Data.SqlClient;
- using System.Linq;
- using System.Text;
- using System.Web;
- namespace RuleEngine.Helper
- {
- public class JsonHelper
- {
- public static string JsonForJqgrid(DataTable dt, int pageSize, int totalRecords, int page)
- {
- string connString = "Data Source=" + "po8lt8zdqt.database.windows.net" + ";Initial Catalog=" + "iNubeRSBYClaims2" + ";User ID=" + "inubeadmin" + ";pwd=" + "Ravi*vikram123";
- string cmdText = "SELECT table_name,column_name FROM information_schema.columns Group By table_name,column_name ORDER BY table_name,column_name";
- List<SchemaCreation> AllTableAndColumnsName = new List<SchemaCreation>();
- using (SqlConnection SqlConn = new SqlConnection(connString))
- {
- SqlConn.Open();
- using (SqlCommand SqlCmd = new SqlCommand(cmdText, SqlConn))
- {
- SqlDataReader Reader = SqlCmd.ExecuteReader();
- while (Reader.Read())
- {
- AllTableAndColumnsName.Add(new SchemaCreation { Table_Names = Reader.GetValue(0).ToString(), Column_Names = Reader.GetValue(1).ToString() });
- }
- }
- }
- //List<string> ColumnNames = new List<string>();
- int totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize);
- StringBuilder jsonBuilder = new StringBuilder();
- jsonBuilder.Append("{");
- jsonBuilder.Append(""total":" + totalPages + ","page":" + page + ","records":" + (totalRecords) + ","rows"");
- jsonBuilder.Append(":[");
- for (int i = 0; i < dt.Rows.Count; i++)
- {
- jsonBuilder.Append("{"i":" + (i) + ","cell":[");
- for (int j = 0; j < dt.Columns.Count; j++)
- {
- jsonBuilder.Append(""");
- jsonBuilder.Append(dt.Rows[i][j].ToString());
- jsonBuilder.Append("",");
- List<string> ColumnNames = new List<string>();
- string EachTableName = dt.Rows[i][j].ToString();
- ColumnNames = AllTableAndColumnsName.Where(p => p.Table_Names == EachTableName).Select(sw => sw.Column_Names).ToList();
- jsonBuilder.Append(""");
- foreach (var EachColName in ColumnNames)
- {
- jsonBuilder.Append(EachColName);
- jsonBuilder.Append(",");
- }
- jsonBuilder.Append("",");
- }
- jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
- jsonBuilder.Append("]},");
- }
- jsonBuilder.Remove(jsonBuilder.Length - 1, 1);
- jsonBuilder.Append("]");
- jsonBuilder.Append("}");
- return jsonBuilder.ToString();
- }
- }
- public class SchemaCreation
- {
- public string Table_Names { get; set; }
- public string Column_Names { get; set; }
- public List<string> ColumnNames { get; set; }
- }
- }
- colModel: [
- { name: 'TableName', index: 'TableName', width: 40, align: 'left',
- Style:'padding-left:15px;'},
- { name: 'ColumnName', index: 'ColumnName', width: 40, align: 'left',
- width: 80, formatter: 'select', type:'select', edittype: 'select',
- editoptions: { value: "1:One;2:Two" } }
- ]
- colModel: [
- { name: 'Table_Names', index: 'Table_Names', width: 40, align: 'left', Style:'padding-left:15px;'},
- { name: 'Column_Names', index: 'Column_Names', width: 40, align: 'left', width: 80, formatter: 'select', type:'select', edittype: 'select',
- editoptions:{
- dataUrl: '/Schema/GetGridData?DataSource=' + '@ViewData["ServerName"]' + '&InitialCatalog=' + '@ViewData["DataBaseName"]' + '&UserName=' + '@ViewData["UserName"]' + '&PassWord=' + '@ViewData["PassWord"]',
- buildSelect: function (data) {
- var response, s = '<select>', i;
- response = jQuery.parseJSON(data);
- if (response && response.length) {
- $.each(response, function (i) {
- s += '<option value="' + this.Column_Names + '">' + this.Column_Names+ '</option>';
- });
- }
- return s + '</select>';
- }
- }}]
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement