I download the data from COM_Address table using kentico API in C#

Ram G asked on August 13, 2019 10:32

I used below below code for download the data form Address table to excel file, excel file will be downloading but data is not coming.

var addresses = AddressInfoProvider.GetAddresses() .Columns("AddressName", "AddressLine1", "AddressLine2", "AddressCity", "AddressZip", "AddressPhone") ;

foreach (AddressInfo addressInfo in addresses) { string AddressName = addressInfo.AddressName; string AddressLine1 = addressInfo.AddressLine1; string AddressLine2 = addressInfo.AddressLine2; string AddressCity = addressInfo.AddressZip; string AddressPhone = addressInfo.AddressPhone;}

var websitePath = System.Web.HttpContext.Current.Server.MapPath("~/"); var FileName = "addresses-upload-template.xlsx"; var exportFilePath = FileHelper.GetFullFilePhysicalPath(ImportExportHelper.GetSiteUtilsFolder()) + FileName; WriteFileToResponse(FileName);

Recent Answers


Sagar Tajpara answered on August 13, 2019 10:56 (last edited on August 13, 2019 10:57)

I have used below code to download the content for the order item's. For that you need to create the string builder text and then you can write it as bellow. This is also dynamically create the header according to the column name.

DataSet ds = addresses.Result;
DataTable dt = ds.Tables[0];
string filePath = "FILE PATH";
StringBuilder sb = new StringBuilder();
List<string> CsvRow = new List<string>();

//ADD HEADER

foreach (DataColumn c in dt.Columns)
{
    CsvRow.Add(c.ColumnName);
}

sb.AppendLine(string.Join(",", CsvRow));

//ADD DATA

foreach (DataRow r in dt.Rows)
{
    CsvRow.Clear();
    foreach (DataColumn c in dt.Columns)
    {
        CsvRow.Add(r[c].ToString());
    }
    sb.AppendLine(string.Join(",", CsvRow));
}

File.WriteAllText(filePath, sb.ToString());
2 votesVote for this answer Mark as a Correct answer

Dragoljub Ilic answered on August 13, 2019 11:11 (last edited on August 13, 2019 11:12)

Hi,

You can use already existing helpers that Kentico provides to store it in excel file. Here is code sample how to achieve that:

var addresses = AddressInfoProvider.GetAddresses().Columns("AddressName", "AddressLine1", "AddressLine2", "AddressCity", "AddressZip", "AddressPhone");
var fileName = "addresses-upload-template.xlsx";
var exportFilePath = FileHelper.GetFullFilePhysicalPath(ImportExportHelper.GetSiteUtilsFolder()) + fileName;

var dataExportHelper = new DataExportHelper(addresses.Result);
using (System.IO.FileStream output = new System.IO.FileStream(exportFilePath, FileMode.Create))
{
     dataExportHelper.ExportData(DataExportFormatEnum.XLSX, output);
}

Prerequisite is that 'CMSSiteUtils' folder exist in your solution.

Best regards, Dragoljub

1 votesVote for this answer Mark as a Correct answer

Ram G answered on August 13, 2019 15:23

I need join below three tables. com_Address, com_Customerustomer and cms_user in the 3 tables common column is com_address.CustomerID, com_customer.CustomerID and cms_user.userID. How to use join and where condition in Kentico API.

var userQuery = UserInfoProvider.GetUsers().Columns("UserID"); var customeraddress = CustomerInfoProvider.GetCustomers() .Columns("CustomerID", "CustomerFirstName", "CustomerLastName", "CustomerCompany", "CustomerUserID");

var addresses = AddressInfoProvider.GetAddresses() .Columns("AddressID", "AddressName", "AddressLine1", "AddressLine2", "AddressCity", "AddressZip", "AddressPhone", "Email", "CompanyName");

0 votesVote for this answer Mark as a Correct answer

Sagar Tajpara answered on August 13, 2019 18:16 (last edited on August 13, 2019 18:22)

You have to use Data Query.

Example : var data = new DataQuery().From(new QuerySource("CMS_Users") .LeftJoin("CustomTable_TableName","CMS_Users.UserID","CustomTable_TableName.UserID")) .Where("CustomTable_TableName.UserId", QueryUnaryOperator.IsNull);

refer this link

1 votesVote for this answer Mark as a Correct answer

Ram G answered on August 16, 2019 15:44

The above code is working fine, Thanks. I download the data from com_Address table to excel file, in the table country and states are in ID format only. I need to download the excel in country name and state name.

var addresses = UserInfoProvider.GetUsers() .Source(sourceItem => sourceItem.Join

0 votesVote for this answer Mark as a Correct answer

Sagar Tajpara answered on August 16, 2019 16:23

You have to add column condition which you want in query '.Column("COLUMNAME")'

Example :

var data = new DataQuery().From(new QuerySource("CMS_Users")    .LeftJoin("CustomTable_TableName","CMS_Users.UserID","CustomTable_TableName.UserID"))   .Where("CustomTable_TableName.UserId", QueryUnaryOperator.IsNull).Column("COLUMNAME1","COLUMNAME2");
1 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.