public static Dictionary <string, string> GetColumns(string tableName)
{
var ds = SQLModule.GetTableData("select COLUMN_NAME,DATA_TYPE from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='" + tableName + "'");
string primaryKeyName = GetPrimaryKey(tableName);
//first element will always be primary key
Dictionary <string, string> ColumnsList = new Dictionary <string, string>();
ColumnsList.Add(primaryKeyName, "System.Int32");
if (ds != null && ds.Rows.Count > 0)
{
//prepare primary key; keeping it in a separate loop , not sure what can be the position
for (int i = 0; i < ds.Rows.Count; i++)
{
var ColumnName = ds.Rows[i]["COLUMN_NAME"].ToString();
var dataType = ds.Rows[i]["DATA_TYPE"].ToString();
if (ColumnName != primaryKeyName)
{
ColumnsList.Add(ColumnName, dataType);
}
}
}
return(ColumnsList);
}
public static DataTable GetListOfRecords(string tableName)
{
string records = tableName;
return(SQLModule.GetTableData(records));
//return this.Content(returntext, "application/json");
}
public static string GetJsonFields(String querydata, string tableName)
{
string sqlCommand = querydata;
string primaryKeyName = GetPrimaryKey(tableName);
var ds = SQLModule.GetTableData("select COLUMN_NAME,DATA_TYPE from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='" + tableName + "'");
if (ds != null && ds.Rows.Count > 0)
{
//prepare structure for jtable
var ListObj = new List <columns>();
ListObj.Add(new columns
{
caption = primaryKeyName + "(KEY)",
dataField = primaryKeyName,
allowEditing = false,
});
for (int i = 0; i < ds.Rows.Count; i++)
{
var ColumnName = ds.Rows[i]["COLUMN_NAME"].ToString();
var dataType = ds.Rows[i]["DATA_TYPE"].ToString();
if (ColumnName != primaryKeyName)
{
dataType = GetDataType(dataType);
ListObj.Add(new columns
{
caption = ColumnName + "(" + dataType + ")",
dataField = ColumnName,
allowEditing = true,
dataType = dataType
});
}
}
//foreach (DataColumn col in ds.Columns)
//{
// if (col.ColumnName != primaryKeyName)
// {
// string dataType = GetDataType(col);
// ListObj.Add(new columns
// {
// caption = col.ColumnName + "(" + dataType + ")",
// dataField = col.ColumnName,
// allowEditing = true,
// dataType = dataType
// });
// }
//}
return(JsonConvert.SerializeObject(ListObj));
}
return(string.Empty);
}
public ActionResult DataViewer(int ID)
{
using (Models.Planner2Entities db = new Models.Planner2Entities())
{
var item = db.DataViewers.Where(v => v.ID == ID).FirstOrDefault();
DataTable data = new DataTable();
data = SQLModule.GetTableData(item.QueryData);
var json = ToJson(data);
return(JsonMax(json));
}
}
public static string GetPrimaryKey(string tableName)
{
tableName = tableName.Replace("[", "");
tableName = tableName.Replace("]", "");
string primaryKeyQuery = "SELECT column_name as primarykeycolumn FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC " +
"INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME and ku.table_name='{0}' " +
"ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION;";
primaryKeyQuery = String.Format(primaryKeyQuery, tableName);
var data = SQLModule.GetTableData(primaryKeyQuery);
string primaryKeyName = "";
if (data != null && data.Rows.Count > 0)
{
primaryKeyName = data.Rows[0][0].ToString();
}
return(primaryKeyName);
}