public HttpResponseMessage Get(string report)
{
var connection = SqlLib.getConnection();
var commandText = @"SELECT * FROM [dbo].[Reports] WHERE [name] = @name";
HttpResponseMessage result = new HttpResponseMessage(HttpStatusCode.OK);
SqlCommand command = new SqlCommand(commandText, connection);
command.Parameters.AddWithValue("@name", report);
try
{
connection.Open();
using (var sqlQueryResult = command.ExecuteReader())
if (sqlQueryResult != null)
{
Debug.WriteLine("SqlResult is successfulr");
var reportObj = sqlQueryResult.Read();
byte[] template = (byte[])sqlQueryResult["template"];
var startRow = Int32.Parse(sqlQueryResult["startRow"].ToString());
var table = sqlQueryResult["table"].ToString();
var map = sqlQueryResult["map"].ToString();
using (var fs = new FileStream(@"c:\temp\Sample.xlsx", FileMode.Create, FileAccess.Write))
fs.Write(template, 0, template.Length);
DataTable dt = getColumns(table, map);
result = writeToExcel(dt);
}
}
catch (Exception ex)
{
Debug.WriteLine("Ëxception is thrown");
Debug.WriteLine(ex.Message);
}
return(result);
}
public HttpResponseMessage GetCategoryId(string table)
{
List <object> columnNames = new List <object>();
var sqlConnection = SqlLib.getConnection();
var commandText = "select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @table AND TABLE_SCHEMA='dbo'";
SqlCommand command = new SqlCommand(commandText, sqlConnection);
command.Parameters.AddWithValue("@table", table);
try
{
sqlConnection.Open();
using (var sqlQueryResult = command.ExecuteReader())
if (sqlQueryResult != null)
{
Debug.WriteLine("SqlResult is successfull");
while (sqlQueryResult.Read())
{
var tableName = sqlQueryResult.GetValue(0).ToString();
columnNames.Add(tableName);
}
Debug.WriteLine("Printing all the tableNames...");
Debug.WriteLine(columnNames);
Debug.WriteLine("finished");
}
}
catch (Exception ex)
{
Debug.WriteLine("Ëxception is thrown");
Debug.WriteLine(ex.Message);
}
var response = Request.CreateResponse(HttpStatusCode.OK);
response.Content = new ObjectContent <List <object> >(columnNames, Configuration.Formatters.JsonFormatter, "application/json");
return(response);
}
private DataTable getColumns(string tableName, string colArray)
{
DataTable dt = new DataTable();
List <string> colNames = new List <string>();
colArray = colArray.Replace("[", "");
colArray = colArray.Replace("]", "");
Debug.WriteLine(colArray);
string[] newColArray = colArray.Split(',');
Debug.WriteLine(newColArray);
for (int i = 0; i < newColArray.Length; i++)
{
var column = JObject.Parse(newColArray[i]);
Debug.WriteLine(column["name"].ToString());
colNames.Add(column["name"].ToString());
}
string colQuery = string.Join(",", colNames.ToArray());
Debug.WriteLine(colNames);
Debug.WriteLine("Printed colNames.......");
Debug.WriteLine(colQuery);
var connection = SqlLib.getConnection();
var commandText = @"SELECT " + colQuery + " FROM [dbo].[" + tableName + "]";
SqlCommand command = new SqlCommand(commandText, connection);
command.Parameters.AddWithValue("@tableName", tableName);
command.Parameters.AddWithValue("@colQuery", colQuery);
try
{
connection.Open();
using (var sqlQueryResult = command.ExecuteReader())
if (sqlQueryResult != null)
{
Debug.WriteLine("SqlResult is successfulr");
for (int i = 0; i < colNames.Count; i++)
{
dt.Columns.Add(colNames[i]);
}
int count = 0;
while (sqlQueryResult.Read() && count < 300)
{
DataRow dataRow = dt.NewRow();
for (int i = 0; i < colNames.Count; i++)
{
dataRow[colNames[i]] = sqlQueryResult[colNames[i]].ToString();
}
count++;
dt.Rows.Add(dataRow);
}
//var reportObj = sqlQueryResult.Read();
//var template = sqlQueryResult["template"];
//var startRow = Int32.Parse(sqlQueryResult["startRow"].ToString());
//var table = sqlQueryResult["table"].ToString();
//var map = sqlQueryResult["map"].ToString();
// DataTable dt = getColumns(table, map);
}
}
catch (Exception ex)
{
Debug.WriteLine("Ëxception is thrown");
Debug.WriteLine(ex.Message);
}
foreach (DataRow dataRow in dt.Rows)
{
foreach (var item in dataRow.ItemArray)
{
Debug.WriteLine(item);
}
}
return(dt);
}