I am using the following code to export some data from a SQL table to an Excel spread sheet.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;
using System.IO;
namespace ImportExport
{
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
//Make a copy of the template file
string templatePath = Server.MapPath("/excel_templates/");
string generatedPath = Server.MapPath("/exports/excel/");
File.Copy(templatePath + "template.xlsx", generatedPath + "generated.xlsx", true);
//Open up the copied template workbook
using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(generatedPath + "generated.xlsx", true))
{
//Access the main Workbook part, which contains all references
WorkbookPart workbookPart = myWorkbook.WorkbookPart;
//Grab the first worksheet
WorksheetPart worksheetPart = workbookPart.WorksheetParts.First();
//SheetData will contain all the data
SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
//Connect to database
AdventureWorksDataContext db = new AdventureWorksDataContext();
//My data starts at row 2
int index = 2;
//Select * from SalesTerritoties table
var territoryQuery =
from t in db.SalesTerritories
select t;
//For each row in my database add a row to my spreadsheeet
foreach (var item in territoryQuery)
{
string territoryName = item.Name;
decimal salesLastYear = Math.Round(item.SalesLastYear, 2);
decimal salesThisYear = Math.Round(item.SalesYTD, 2);
//Add a new row
Row contentRow = CreateContentRow(index, territoryName, salesLastYear, salesThisYear);
index++;
//Append new row to sheet data
sheetData.AppendChild(contentRow);
}
}
}
string[] headerColumns = new string[] { "A", "B", "C" };
Row CreateContentRow(int index, string territory, decimal salesLastYear, decimal salesThisYear)
{
//Create new row
Row r = new Row();
r.RowIndex = (UInt32)index;
//First cell is a text cell, so create it and append it
Cell firstCell = CreateTextCell(headerColumns[0], territory, index);
r.AppendChild(firstCell);
//Create cells that contain data
for (int i = 1; i < headerColumns.Length; i++)
{
Cell c = new Cell();
c.CellReference = headerColumns[i] + index;
CellValue v = new CellValue();
if (i == 1)
{
v.Text = salesLastYear.ToString();
}
else
{
v.Text = salesThisYear.ToString();
c.AppendChild(v);
r.AppendChild(c);
}
}
return r;
}
Cell CreateTextCell(string header, string text, int index)
{
//Create new inline string cell
Cell c = new Cell();
c.DataType = CellValues.InlineString;
c.CellReference = header + index;
//Add text to text cell
InlineString inlineString = new InlineString();
Text t = new Text();
t.Text = text;
inlineString.AppendChild(t);
c.AppendChild(inlineString);
return c;
}
}
}
I don't use Linq at all but am quite gfamiliar with ADO.NET.
I'd appreciate some help - if the task is not too large - to convert this code so that it uses pure ADO.NET and no Linq at all.
As far as I see, I think the file——to be very honest, really belongs to that of Linq. However, it doesn't seem that you've used AdventureWorkDataContext, so you can just leave it anyway.
//Connect to database
AdventureWorksDataContext db = new AdventureWorksDataContext();
//My data starts at row 2
int index = 2;
//Select * from SalesTerritoties table
var territoryQuery =
from t in db.SalesTerritories
select t;
//For each row in my database add a row to my spreadsheeet
foreach (var item in territoryQuery)
{
string territoryName = item.Name;
decimal salesLastYear = Math.Round(item.SalesLastYear, 2);
decimal salesThisYear = Math.Round(item.SalesYTD, 2);
//Add a new row
Row contentRow = CreateContentRow(index, territoryName, salesLastYear, salesThisYear);
index++;
//Append new row to sheet data
sheetData.AppendChild(contentRow);
}
}
}
Hi,
If you mean this, I think you can use SqlDataAdapter.Fill into a DataTable and then use foreach to loop each row and fetch specific column's value. Something looks like:
using(SqlDataAdapter adapter = new SqlDataAdapter(……，……))
{
DataTable dt = new DataTable();
adapter.Fill(dt);
foreach(DataRow row in dt.Rows)
{
//Assigning……：XXX = row[specific column index];
}
}
Bear with me with what I am about to say as the whole Linq process (using the Linq to Sql method of dragging tables into the code, and the pages it produces, etc) is alien to me.
Your code takes a small section of the file Default.aspx.cs. This is the code I posetd first in this thread. Default.aspx has no code, everything happens in its Page_Load and I end up with an Excel file in a folder in the site.
Are you suggesting that, if I replace the section in Default.aspx.cs that you have quoted, with your code, that is all I need? I.e.. I can do away with AdventureWorks.dbml (and its 2 files: AdventureWorks.dbml.layout & AdventureWorks.designer.cs). The latter
file mentioned here was the file I posted second in this thread.
banksidepoet
Participant
774 Points
862 Posts
Linq conversion to ADO.NET
Nov 04, 2012 04:52 PM|LINK
Hi,
I am using the following code to export some data from a SQL table to an Excel spread sheet.
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; using DocumentFormat.OpenXml; using System.IO; namespace ImportExport { public partial class Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { //Make a copy of the template file string templatePath = Server.MapPath("/excel_templates/"); string generatedPath = Server.MapPath("/exports/excel/"); File.Copy(templatePath + "template.xlsx", generatedPath + "generated.xlsx", true); //Open up the copied template workbook using (SpreadsheetDocument myWorkbook = SpreadsheetDocument.Open(generatedPath + "generated.xlsx", true)) { //Access the main Workbook part, which contains all references WorkbookPart workbookPart = myWorkbook.WorkbookPart; //Grab the first worksheet WorksheetPart worksheetPart = workbookPart.WorksheetParts.First(); //SheetData will contain all the data SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>(); //Connect to database AdventureWorksDataContext db = new AdventureWorksDataContext(); //My data starts at row 2 int index = 2; //Select * from SalesTerritoties table var territoryQuery = from t in db.SalesTerritories select t; //For each row in my database add a row to my spreadsheeet foreach (var item in territoryQuery) { string territoryName = item.Name; decimal salesLastYear = Math.Round(item.SalesLastYear, 2); decimal salesThisYear = Math.Round(item.SalesYTD, 2); //Add a new row Row contentRow = CreateContentRow(index, territoryName, salesLastYear, salesThisYear); index++; //Append new row to sheet data sheetData.AppendChild(contentRow); } } } string[] headerColumns = new string[] { "A", "B", "C" }; Row CreateContentRow(int index, string territory, decimal salesLastYear, decimal salesThisYear) { //Create new row Row r = new Row(); r.RowIndex = (UInt32)index; //First cell is a text cell, so create it and append it Cell firstCell = CreateTextCell(headerColumns[0], territory, index); r.AppendChild(firstCell); //Create cells that contain data for (int i = 1; i < headerColumns.Length; i++) { Cell c = new Cell(); c.CellReference = headerColumns[i] + index; CellValue v = new CellValue(); if (i == 1) { v.Text = salesLastYear.ToString(); } else { v.Text = salesThisYear.ToString(); c.AppendChild(v); r.AppendChild(c); } } return r; } Cell CreateTextCell(string header, string text, int index) { //Create new inline string cell Cell c = new Cell(); c.DataType = CellValues.InlineString; c.CellReference = header + index; //Add text to text cell InlineString inlineString = new InlineString(); Text t = new Text(); t.Text = text; inlineString.AppendChild(t); c.AppendChild(inlineString); return c; } } }I don't use Linq at all but am quite gfamiliar with ADO.NET.
I'd appreciate some help - if the task is not too large - to convert this code so that it uses pure ADO.NET and no Linq at all.
Anyone willing to take that on for me?
(I've posted the whole page as I'm not sure which sections will need reworking - it may be just a small section of this whole. If it helps, the code is taken from here: http://msdn.microsoft.com/en-us/library/office/dd452407(v=office.12).aspx)
Thanks
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Linq conversion to ADO.NET
Nov 05, 2012 04:19 AM|LINK
Hi,
I see your codes without any things about LINQ……
Can you elebrate it more?
banksidepoet
Participant
774 Points
862 Posts
Re: Linq conversion to ADO.NET
Nov 05, 2012 07:04 AM|LINK
Apologies. Shows how little I know about Linq.
Here's the .cs file: is this all you need?
#pragma warning disable 1591 //------------------------------------------------------------------------------ // <auto-generated> // This code was generated by a tool. // Runtime Version:4.0.30319.269 // // Changes to this file may cause incorrect behavior and will be lost if // the code is regenerated. // </auto-generated> //------------------------------------------------------------------------------ namespace ImportExport { using System.Data.Linq; using System.Data.Linq.Mapping; using System.Data; using System.Collections.Generic; using System.Reflection; using System.Linq; using System.Linq.Expressions; using System.ComponentModel; using System; [global::System.Data.Linq.Mapping.DatabaseAttribute(Name="AdventureWorks2008")] public partial class AdventureWorksDataContext : System.Data.Linq.DataContext { private static System.Data.Linq.Mapping.MappingSource mappingSource = new AttributeMappingSource(); #region Extensibility Method Definitions partial void OnCreated(); partial void InsertSalesTerritory(SalesTerritory instance); partial void UpdateSalesTerritory(SalesTerritory instance); partial void DeleteSalesTerritory(SalesTerritory instance); #endregion public AdventureWorksDataContext() : base(global::System.Configuration.ConfigurationManager.ConnectionStrings["AdventureWorks2008ConnectionString"].ConnectionString, mappingSource) { OnCreated(); } public AdventureWorksDataContext(string connection) : base(connection, mappingSource) { OnCreated(); } public AdventureWorksDataContext(System.Data.IDbConnection connection) : base(connection, mappingSource) { OnCreated(); } public AdventureWorksDataContext(string connection, System.Data.Linq.Mapping.MappingSource mappingSource) : base(connection, mappingSource) { OnCreated(); } public AdventureWorksDataContext(System.Data.IDbConnection connection, System.Data.Linq.Mapping.MappingSource mappingSource) : base(connection, mappingSource) { OnCreated(); } public System.Data.Linq.Table<SalesTerritory> SalesTerritories { get { return this.GetTable<SalesTerritory>(); } } } [global::System.Data.Linq.Mapping.TableAttribute(Name="Sales.SalesTerritory")] public partial class SalesTerritory : INotifyPropertyChanging, INotifyPropertyChanged { private static PropertyChangingEventArgs emptyChangingEventArgs = new PropertyChangingEventArgs(String.Empty); private int _TerritoryID; private string _Name; private string _CountryRegionCode; private string _Group; private decimal _SalesYTD; private decimal _SalesLastYear; private decimal _CostYTD; private decimal _CostLastYear; private System.Guid _rowguid; private System.DateTime _ModifiedDate; #region Extensibility Method Definitions partial void OnLoaded(); partial void OnValidate(System.Data.Linq.ChangeAction action); partial void OnCreated(); partial void OnTerritoryIDChanging(int value); partial void OnTerritoryIDChanged(); partial void OnNameChanging(string value); partial void OnNameChanged(); partial void OnCountryRegionCodeChanging(string value); partial void OnCountryRegionCodeChanged(); partial void OnGroupChanging(string value); partial void OnGroupChanged(); partial void OnSalesYTDChanging(decimal value); partial void OnSalesYTDChanged(); partial void OnSalesLastYearChanging(decimal value); partial void OnSalesLastYearChanged(); partial void OnCostYTDChanging(decimal value); partial void OnCostYTDChanged(); partial void OnCostLastYearChanging(decimal value); partial void OnCostLastYearChanged(); partial void OnrowguidChanging(System.Guid value); partial void OnrowguidChanged(); partial void OnModifiedDateChanging(System.DateTime value); partial void OnModifiedDateChanged(); #endregion public SalesTerritory() { OnCreated(); } [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_TerritoryID", AutoSync=AutoSync.OnInsert, DbType="Int NOT NULL IDENTITY", IsPrimaryKey=true, IsDbGenerated=true)] public int TerritoryID { get { return this._TerritoryID; } set { if ((this._TerritoryID != value)) { this.OnTerritoryIDChanging(value); this.SendPropertyChanging(); this._TerritoryID = value; this.SendPropertyChanged("TerritoryID"); this.OnTerritoryIDChanged(); } } } [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_Name", DbType="NVarChar(50) NOT NULL", CanBeNull=false)] public string Name { get { return this._Name; } set { if ((this._Name != value)) { this.OnNameChanging(value); this.SendPropertyChanging(); this._Name = value; this.SendPropertyChanged("Name"); this.OnNameChanged(); } } } [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_CountryRegionCode", DbType="NVarChar(3) NOT NULL", CanBeNull=false)] public string CountryRegionCode { get { return this._CountryRegionCode; } set { if ((this._CountryRegionCode != value)) { this.OnCountryRegionCodeChanging(value); this.SendPropertyChanging(); this._CountryRegionCode = value; this.SendPropertyChanged("CountryRegionCode"); this.OnCountryRegionCodeChanged(); } } } [global::System.Data.Linq.Mapping.ColumnAttribute(Name="[Group]", Storage="_Group", DbType="NVarChar(50) NOT NULL", CanBeNull=false)] public string Group { get { return this._Group; } set { if ((this._Group != value)) { this.OnGroupChanging(value); this.SendPropertyChanging(); this._Group = value; this.SendPropertyChanged("Group"); this.OnGroupChanged(); } } } [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_SalesYTD", DbType="Money NOT NULL")] public decimal SalesYTD { get { return this._SalesYTD; } set { if ((this._SalesYTD != value)) { this.OnSalesYTDChanging(value); this.SendPropertyChanging(); this._SalesYTD = value; this.SendPropertyChanged("SalesYTD"); this.OnSalesYTDChanged(); } } } [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_SalesLastYear", DbType="Money NOT NULL")] public decimal SalesLastYear { get { return this._SalesLastYear; } set { if ((this._SalesLastYear != value)) { this.OnSalesLastYearChanging(value); this.SendPropertyChanging(); this._SalesLastYear = value; this.SendPropertyChanged("SalesLastYear"); this.OnSalesLastYearChanged(); } } } [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_CostYTD", DbType="Money NOT NULL")] public decimal CostYTD { get { return this._CostYTD; } set { if ((this._CostYTD != value)) { this.OnCostYTDChanging(value); this.SendPropertyChanging(); this._CostYTD = value; this.SendPropertyChanged("CostYTD"); this.OnCostYTDChanged(); } } } [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_CostLastYear", DbType="Money NOT NULL")] public decimal CostLastYear { get { return this._CostLastYear; } set { if ((this._CostLastYear != value)) { this.OnCostLastYearChanging(value); this.SendPropertyChanging(); this._CostLastYear = value; this.SendPropertyChanged("CostLastYear"); this.OnCostLastYearChanged(); } } } [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_rowguid", DbType="UniqueIdentifier NOT NULL")] public System.Guid rowguid { get { return this._rowguid; } set { if ((this._rowguid != value)) { this.OnrowguidChanging(value); this.SendPropertyChanging(); this._rowguid = value; this.SendPropertyChanged("rowguid"); this.OnrowguidChanged(); } } } [global::System.Data.Linq.Mapping.ColumnAttribute(Storage="_ModifiedDate", DbType="DateTime NOT NULL")] public System.DateTime ModifiedDate { get { return this._ModifiedDate; } set { if ((this._ModifiedDate != value)) { this.OnModifiedDateChanging(value); this.SendPropertyChanging(); this._ModifiedDate = value; this.SendPropertyChanged("ModifiedDate"); this.OnModifiedDateChanged(); } } } public event PropertyChangingEventHandler PropertyChanging; public event PropertyChangedEventHandler PropertyChanged; protected virtual void SendPropertyChanging() { if ((this.PropertyChanging != null)) { this.PropertyChanging(this, emptyChangingEventArgs); } } protected virtual void SendPropertyChanged(String propertyName) { if ((this.PropertyChanged != null)) { this.PropertyChanged(this, new PropertyChangedEventArgs(propertyName)); } } } } #pragma warning restore 1591Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Linq conversion to ADO.NET
Nov 05, 2012 07:40 AM|LINK
Hi,
As far as I see, I think the file——to be very honest, really belongs to that of Linq. However, it doesn't seem that you've used AdventureWorkDataContext, so you can just leave it anyway.
banksidepoet
Participant
774 Points
862 Posts
Re: Linq conversion to ADO.NET
Nov 05, 2012 09:09 AM|LINK
If you take a brief look at the link I posted, you'll see how the file was created. It is indeed Linq. I would rather pull the data out using ADO.NET.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Linq conversion to ADO.NET
Nov 05, 2012 11:38 AM|LINK
Hi,
If you mean this, I think you can use SqlDataAdapter.Fill into a DataTable and then use foreach to loop each row and fetch specific column's value. Something looks like:
using(SqlDataAdapter adapter = new SqlDataAdapter(……，……)) { DataTable dt = new DataTable(); adapter.Fill(dt); foreach(DataRow row in dt.Rows) { //Assigning……：XXX = row[specific column index]; } }banksidepoet
Participant
774 Points
862 Posts
Re: Linq conversion to ADO.NET
Nov 06, 2012 10:42 AM|LINK
Thank you for this reply and for perservering.
Bear with me with what I am about to say as the whole Linq process (using the Linq to Sql method of dragging tables into the code, and the pages it produces, etc) is alien to me.
Your code takes a small section of the file Default.aspx.cs. This is the code I posetd first in this thread. Default.aspx has no code, everything happens in its Page_Load and I end up with an Excel file in a folder in the site.
Are you suggesting that, if I replace the section in Default.aspx.cs that you have quoted, with your code, that is all I need? I.e.. I can do away with AdventureWorks.dbml (and its 2 files: AdventureWorks.dbml.layout & AdventureWorks.designer.cs). The latter file mentioned here was the file I posted second in this thread.
Thanks.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: Linq conversion to ADO.NET
Nov 06, 2012 11:12 AM|LINK
Yes, because from your codes it seems that you wanna fetch values from specific record and its values.