protected string getCountryInXml()
{
dbManager = new DBConnection();
try
{
SqlCommand command = new SqlCommand();
command.Connection = dbManager.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "[dbo].[get_country_list]";
XmlReader reader = command.ExecuteXmlReader();
DataSet ds = new DataSet();
ds.ReadXml(reader);
return ds.GetXml();
}
finally
{
dbManager.Close();
}
}
/// <summary>
/// Loads the bread crumbs.
/// </summary>
private void LoadBreadCrumbs()
{
breadCrumbs = Store.Caching.CategoryCache.FetchCategoryBreadCrumbs(categoryId);
xmlDataSource.EnableCaching = false;
RewriteService.AddRewriteNameSpaceForXslt(xmlDataSource);
xmlDataSource.Data = breadCrumbs.GetXml();
}
public static string displayNpt(List<string> _arr)
{
DataTable dummy = new DataTable();
dummy.Columns.Add("Task Name");
dummy.Columns.Add("Status");
SystemObjects.OPT _opt = new SystemObjects.OPT();
_opt.TeamID = Convert.ToInt32(_arr[0].ToString());
DataSet ds = new DataSet();
try
{
dummy.Merge(_opt.DisplayNptTask());
if (dummy.Rows.Count < 1)
{
dummy.Rows.Add();
}
ds.Tables.Add(dummy);
}
catch (Exception ex)
{
}
return ds.GetXml();
}
protected void Page_Load(object sender, EventArgs e)
{
DataSet ds = new DataSet();
string connStr = @"Data Source=Talha-PC\SQLExpress;Initial Catalog=LocalTestDB;User Id=talha;Password=talha123;Trusted_Connection=True;";
using (SqlConnection conn = new SqlConnection(connStr))
{
string sql = "Select MenuID, Text,Description, ParentID from Menu";
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
da.Fill(ds);
da.Dispose();
}
ds.DataSetName = "Menus";
ds.Tables[0].TableName = "Menu";
DataRelation relation = new DataRelation("ParentChild",
ds.Tables["Menu"].Columns["MenuID"],
ds.Tables["Menu"].Columns["ParentID"], true);
relation.Nested = true;
ds.Relations.Add(relation);
XmlDataSource1.Data = ds.GetXml();
if (Request.Params["Sel"] != null)
Page.Controls.Add(new System.Web.UI.LiteralControl("You selected " + Request.Params["Sel"]));
}
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string sql = "";
string strId = Request.QueryString["id"];
if (strId != null && strId != "undefined")
{
if (strId != "All")
{
strId = strId.Replace(",", "','");
sql = @"SELECT Id, ItemSKU, ItemName FROM Goods WHERE Id IN ('" + strId + "')";
}
else
{
sql = @"SELECT Id, ItemSKU, ItemName FROM Goods";
}
DataSet ds = new DataSet();
DataTable dt = db.RunTable<OrderType>(sql);
dt.DefaultView.Sort = "Id asc";
ds.Tables.Add(dt.DefaultView.ToTable());
string xml = ds.GetXml();
Utilities.ResponseXml(this, ref xml, false);
}
}
}
protected void Button1_Click(object sender, EventArgs e)
{
string str = "";
DataSet Ds = new DataSet();
DataTable dt = new DataTable();
dt.Columns.Add("Customer_ID");
dt.Columns.Add("Service_ID");
//dt.Columns.Add("Order_ID");
//for (int i = 0; i < GridView1.Rows.Count; i++)
//{
// var row = dt.NewRow();
// row["Customer_ID"] = "28";
// row["Service_ID"] = "6";
// //row["Order_ID"] = Convert.ToString(GridView1.Rows[i].Cells[0].Text);
// dt.Rows.Add(row);
//}
var row = dt.NewRow();
row["Customer_ID"] = Session["ed"].ToString();
row["Service_ID"] = "6";
dt.Rows.Add(row);
Ds.Tables.Add(dt);
str = Ds.GetXml();
Session["STR"] = str;
Response.Redirect("Disconnect_Due.aspx");
}
public static string displayemployee(List<string> _arr)
{
DataTable dummy = new DataTable();
dummy.Columns.Add("No");
dummy.Columns.Add("WorkdayID");
dummy.Columns.Add("Name");
SystemObjects.HeadCount _hc = new SystemObjects.HeadCount();
_hc.TeamID = Convert.ToInt32(_arr[0]);
_hc.Date = Convert.ToInt32(_arr[1]);
_hc.Year = Convert.ToInt32(_arr[2]);
DataSet ds = new DataSet();
try
{
dummy.Merge(_hc.DisplayEmployee());
if (dummy.Rows.Count < 1)
{
dummy.Rows.Add();
}
ds.Tables.Add(dummy);
}
catch (Exception ex)
{
}
return ds.GetXml();
}
/// <summary>
/// Converts the specified AccessDB file to it's equivalent XmlDocument
/// </summary>
/// <param name="sFilePath"></param>
/// <param name="sAccessDbQuery"></param>
/// <returns></returns>
private static XmlDocument ConvertAccessDatabaseFile(string sFilePath)
{
XmlDocument xmlRaw = null;
System.Data.OleDb.OleDbConnection oleConnection = null;
try
{
string sProviderInfo = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + sFilePath + ";";
oleConnection = new System.Data.OleDb.OleDbConnection(sProviderInfo);
System.Data.OleDb.OleDbDataAdapter oleCommand = new System.Data.OleDb.OleDbDataAdapter(AccessDatabaseQuery, oleConnection);
System.Data.DataSet dsAccess = new System.Data.DataSet();
oleCommand.Fill(dsAccess);
xmlRaw = CleanupRawXml(dsAccess.GetXml());
}
catch (Exception ex)
{
string sErrorMessage = ex.Message;
if (ex.InnerException != null)
{
sErrorMessage = sErrorMessage + " Details: " + ex.InnerException.Message;
}
throw new Exception(sErrorMessage);
}
finally
{
oleConnection.Close();
}
return(xmlRaw);
}
static void Main(string[] args)
{
DataTable table1 = new DataTable("Patients");
table1.Columns.Add("Name");
table1.Columns.Add("Id");
table1.Rows.Add("Sam", 1);
table1.Rows.Add("Mark", 2);
DataTable table2 = new DataTable("Medications");
table2.Columns.Add("Id");
table2.Columns.Add("Medication");
table2.Rows.Add(1, "atenolol");
table2.Rows.Add(2, "amoxicillin");
DataSet set = new DataSet("Hospital");
set.Tables.Add(table1);
set.Tables.Add(table2);
Console.WriteLine(set.GetXml());
Console.ReadKey();
}
public static string DateDisplay(Int32 teamid)
{
DateTime datestart, dateend;
SystemObjects.TableF _tablef = new SystemObjects.TableF();
_tablef.TeamID = teamid;
DataSet ds = new DataSet();
try
{
DataTable dt = new DataTable();
dt = _tablef.displayRatingsDate();
DataTable dtdate = new DataTable();
dtdate.Columns.Add("Dates");
dtdate.Rows.Add("Please select Month - Year...");
datestart = Convert.ToDateTime(dt.Rows[0]["RateDateMin"].ToString());
dateend = Convert.ToDateTime(dt.Rows[0]["RateDateMax"].ToString());
while (datestart <= dateend)
{
DateTime datestart_ = Convert.ToDateTime(datestart.Month.ToString() + "/01/" + datestart.Year.ToString());
dtdate.Rows.Add(String.Format("{0:MMMM - yyyy}", datestart_));
datestart = datestart.AddMonths(1);
}
ds.Tables.Add(dtdate);
}
catch (Exception ex)
{
}
return ds.GetXml();
}
public static void CreateExcelOther(Page page, DataSet ds, string colHeaders, string typeid, string FileName)
{
HttpResponse resp;
resp = page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.ContentType = "application/ms-excel";
resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
int i = 0;
System.Data.DataTable dt = ds.Tables[0];
string bodyData = ExportTable(colHeaders, ds);
if (typeid == "1")
{
resp.Write(bodyData);
}
else
{
if (typeid == "2")
{
//从DataSet中直接导出XML数据并且写到HTTP输出流中
resp.Write(ds.GetXml());
}
}
//写缓冲区中的数据到HTTP头文件中
resp.End();
}
/// <summary>
/// Converts the specified CSV file to it's equivalent XmlDocument
/// </summary>
/// <param name="sFilePath"></param>
/// <returns></returns>
private static XmlDocument ConvertCsvFile(string sFilePath)
{
XmlDocument xmlRaw = null;
System.Data.OleDb.OleDbConnection oleConnection = null;
try
{
FileInfo fiInfo = new FileInfo(sFilePath);
oleConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fiInfo.DirectoryName + ";Extended Properties=\"Text;HDR=no;FMT=Delimited\";");
System.Data.OleDb.OleDbDataAdapter oleCommand = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + fiInfo.Name + "]", oleConnection);
System.Data.DataSet dsCsvFile = new System.Data.DataSet();
oleCommand.Fill(dsCsvFile);
xmlRaw = CleanupRawXml(dsCsvFile.GetXml());
}
catch (Exception ex)
{
string sErrorMessage = ex.Message;
if (ex.InnerException != null)
{
sErrorMessage = sErrorMessage + " Details: " + ex.InnerException.Message;
}
throw new Exception(sErrorMessage);
}
finally
{
oleConnection.Close();
}
return(xmlRaw);
}
public static string ToXml(System.Data.DataTable dt, string RootElement, string ItemElement)
{
string ret = string.Empty;
bool ok = dt != null && !string.IsNullOrEmpty(RootElement) && !string.IsNullOrEmpty(ItemElement);
if (ok)
{
// prepare the supplied DataTable
if (dt != null)
{
dt.TableName = ItemElement;
}
System.Data.DataSet ds = new System.Data.DataSet(RootElement);
if (dt != null)
{
ds.Tables.Add(dt);
}
// XML con atributos, no elementos
if (ds.Tables.Count > 0)
{
foreach (System.Data.DataColumn column in ds.Tables[0].Columns)
{
column.ColumnMapping = System.Data.MappingType.Attribute;
}
}
ret = ds.GetXml();
}
return(ret);
}
protected string GetDocketsForUserIdInXML(string userId)
{
dbManager = new DBConnection();
try
{
SqlCommand command = new SqlCommand();
command.Connection = dbManager.Connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "[dbo].[get_dockets_for_user]";
command.Parameters.AddWithValue("@user_id", userId);
XmlReader reader = command.ExecuteXmlReader();
DataSet ds = new DataSet();
ds.ReadXml(reader);
return ds.GetXml();
}
finally
{
dbManager.Close();
}
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
string sql = "";
string Id = Request.QueryString["Id"];
if (Id != null)
{
OrderPrintRecordType oprt = OrderPrintRecordType.findById(cvt.ToInt(Id));
if (oprt != null)
{
sql = @"select (select COUNT(1) from PackageGoods where PackageGoods.PackageNo=p.PackageNo ) as GCount,p.PackageNo,o.TxnId,o.UserNameForm,o.OrderNo,o.OrderAmount,o.OrderCurrencyCode,o.OrderForm,o.OrderNote,p.LogisticsMode,
pg.Sku,pg.ItemQty,o.NowOrderType,b.*,g.ItemName
from Package p
left join Orders o on p.OrderID=o.Id
left join BuyerAddress b on o.AddressId=b.Id
left join PackageGoods pg on p.PackageNo=pg.PackageNo
left join Goods g on pg.Sku=g.ItemSku
where o.Id in (" + oprt.OrderIds + ") and p.PackageStatus='未发货'";
DataSet ds = new DataSet();
DataTable dt = db.RunTable<OrderType>(sql);
dt.DefaultView.Sort = "GCount,PackageNo asc";
ds.Tables.Add(dt.DefaultView.ToTable());
string xml = ds.GetXml();
Utilities.ResponseXml(this, ref xml, false);
}
}
}
}
public static string GetAllDetails(List<string> backArrayParam)
{
DataSet data = new DataSet();
Modules.Ninja ninja = new Modules.Ninja();
data.Tables.Add(ninja.GetAllDetails());
return data.GetXml();
}
public void CE(string typeid = "1", string FileName = "test.csv")
{
DataSet ds = new DataSet();
DataTable dt = ds.Tables.Add();
dt.Columns.Add("姓名");
dt.Columns.Add("年龄");
dt.Columns.Add("民族");
dt.Columns.Add("性别");
dt.Rows.Add("张三", "18", "汉族", "男");
dt.Rows.Add("张三5", "22", "汉族", "女");
dt.Rows.Add("张三7", "64", "汉族", "不详");
HttpResponseBase resp;
resp = HttpContext.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-Disposition", "attachment;filename=" + FileName);
string colHeaders = "", ls_item = "";
int i = 0;
//定义表对象与行对像，同时用DataSet对其值进行初始化
// typeid=="1"时导出为EXCEL格式文件；typeid=="2"时导出为XML格式文件
if (typeid == "1")
{
//取得数据表各列标题，各标题之间以\t分割，最后一个列标题后加回车符
for (i = 0; i < dt.Columns.Count; i++)
{
colHeaders += dt.Columns[i].Caption.ToString() + ",";
//colHeaders += dt.Columns[i].Caption.ToString() + "\n";
}
//向HTTP输出流中写入取得的数据信息
resp.Write(colHeaders + "\r\n");
//逐行处理数据
foreach (DataRow row in dt.Rows)
{
//在当前行中，逐列获得数据，数据之间以\t分割，结束时加回车符\n
for (i = 0; i < dt.Columns.Count; i++)
{
ls_item += row[i].ToString() + ",";
//ls_item += row[i].ToString() + "\n";
}
//当前行数据写入HTTP输出流，并且置空ls_item以便下行数据
resp.Write(ls_item + "\r\n");
ls_item = "";
}
}
else
{
if (typeid == "2")
{
//从DataSet中直接导出XML数据并且写到HTTP输出流中
resp.Write(ds.GetXml());
}
}
//写缓冲区中的数据到HTTP头文件中
resp.End();
}
public XmlDocument sendXml()
{
XmlDocument sendXML = new XmlDocument();
DataTable dt = CreateDataSource();
DataSet ds = new DataSet();
ds.Tables.Add(dt);
sendXML.LoadXml(ds.GetXml());
return sendXML;
}
public void BindData(DataTable Dt)
{
DataSet ds = new DataSet();
DataTable DtTemp = new DataTable();
DtTemp = Dt.Copy();
DtTemp.TableName = "Table";
ds.Tables.Add(DtTemp);
BindData(ds.GetXml(), Server.MapPath("~/Xslts/SubscriptionDetails.xslt"));
}
protected void btnToXML_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
DataTable dt = CreateDataSource();
ds.Tables.Add(dt);
System.Xml.XmlDocument menuds = new System.Xml.XmlDocument();
menuds.LoadXml(ds.GetXml());
menuds.Save(@"C:\xmlData.xml");
}
public String ExecuteXml(String commStr)
{
try
{
SqlDataAdapter da = new SqlDataAdapter(commStr, sqlCon);
DataSet ds = new DataSet();
da.Fill(ds);
return ds.GetXml();
}
catch (Exception e)
{
return null;
}
}
static void Main(string[] args)
{
DataTable table1 = new DataTable("Patients");
table1.Columns.Add("Name");
table1.Columns.Add("Id");
table1.Rows.Add("sam", 1);
DataSet set = new DataSet("Hospital");
set.Tables.Add(table1);
set.Namespace = "y";
set.Prefix = "x";
Console.WriteLine(set.GetXml());
Console.ReadKey();
set.Tables.Add(GetPrescriptionTable());
Console.WriteLine(set.GetXml());
Console.ReadKey();
}
public static string displayMakeAdmin(Int32 questid)
{
SBObj _sbobj = new SBObj();
_sbobj.UserNo = questid;
DataSet ds = new DataSet();
try
{
ds.Tables.Add(_sbobj.DisplayMakeAdmin());
}
catch (Exception ex)
{
}
return ds.GetXml();
}
public static string checkifnotAnswered(Int32 questid)
{
SBObj _sbobj = new SBObj();
_sbobj.QuestionID = questid;
DataSet ds = new DataSet();
try
{
ds.Tables.Add(_sbobj.CheckifnotAnswered());
}
catch (Exception ex)
{
}
return ds.GetXml();
}
public static string loadTop1(Int32 userno)
{
SBObj _sbobj = new SBObj();
_sbobj.UserNo = userno;
DataSet ds = new DataSet();
try
{
ds.Tables.Add(_sbobj.LoadTop1());
}
catch (Exception ex)
{
}
return ds.GetXml();
}
public static string alertCorrectAnswer(Int32 questid)
{
SBObj _sbobj = new SBObj();
_sbobj.QuestionID = questid;
DataSet ds = new DataSet();
try
{
ds.Tables.Add(_sbobj.AlertCorrectAnswer());
}
catch (Exception ex)
{
}
return ds.GetXml();
}
public static string MetricSpecificDisplay(Int32 metricid)
{
SystemObjects.TableF _tablef = new SystemObjects.TableF();
_tablef.MetricID = metricid;
DataSet ds = new DataSet();
try
{
ds.Tables.Add(_tablef.displayMetricSpecific());
}
catch (Exception ex)
{
}
return ds.GetXml();
}
public static string displayQuestionairesModal(Int32 questid)
{
SBObj _sbobj = new SBObj();
_sbobj.QuestionID = questid;
DataSet ds = new DataSet();
try
{
ds.Tables.Add(_sbobj.DisplayQuestionairesModal());
}
catch (Exception ex)
{
}
return ds.GetXml();
}
protected void Page_Load(object sender, EventArgs e)
{
var table1 = new DataTable("table1");
table1.Columns.Add("VerifyOpt");
table1.Columns.Add("IsGroupShoppingShow");
table1.Columns.Add("SoSysNoName");
table1.Columns.Add("GCSysNoShow");
table1.Columns.Add("SOID");
table1.Columns.Add("PayTypeName");
table1.Columns.Add("PayAmount");
table1.Columns.Add("SourceName");
table1.Columns.Add("ApproveUserName");
table1.Columns.Add("ApproveTime");
table1.Columns.Add("InputUserName");
table1.Columns.Add("InputTime");
table1.Columns.Add("StatusName");
table1.Columns.Add("AlipayTradeNo");
table1.Columns.Add("MPSysNo");
table1.Rows.Add(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15);
var ds = new DataSet("test");
ds.Tables.Add(table1);
DataGrid1.DataSource = ds;
DataGrid1.DataBind();
var titles = new List<string>();
var rtv = new DataTable("result");
foreach (BoundColumn col in DataGrid1.Columns)
{
titles.Add(col.HeaderText);
rtv.Columns.Add(col.HeaderText);
}
for (int i = 0; i < DataGrid1.Items.Count; i++)
{
DataRow oneRow = rtv.NewRow();
Int32 oneRowIndex = 0;
for (int j = 0; j < DataGrid1.Items[i].Cells.Count; j++)
{
oneRow[oneRowIndex] = DataGrid1.Items[i].Cells[j].Text.Replace(" ", "");
oneRowIndex++;
}
rtv.Rows.Add(oneRow);
}
DataSet set = new DataSet("office");
set.Tables.Add(rtv);
TextBox1.Text = set.GetXml();
}
public static string displayApps(String concern)
{
DataSet ds = new DataSet();
ReARObj _rear = new ReARObj();
_rear.ConcernType = concern;
try
{
ds.Tables.Add(_rear.displayApplication());
}
catch (Exception ex)
{
}
return ds.GetXml();
}
public static string displayAction(Int32 scopeid)
{
DataSet ds = new DataSet();
ReARObj _rear = new ReARObj();
_rear.ScopeID = scopeid;
try
{
ds.Tables.Add(_rear.displayAction());
}
catch (Exception ex)
{
}
return ds.GetXml();
}
//protected System.Web.UI.WebControls.DataGrid DG;
protected void Page_Load(object sender, EventArgs e)
{ //Response.Write("hola");
conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ustti_db_migration::connectionstring::target"].ConnectionString);
ds = new DataSet("schema comparison test");
comm = new SqlCommand(GetSqlString(), conn);
adapter = new SqlDataAdapter(comm);
adapter.Fill(ds);
Console.Write(ds.GetXml());
//DG = new DataGrid();
DG.DataSource = ds;
DG.DataBind();
Cleanup();
}
protected void Page_Load(object sender, EventArgs e)
{
XmlDocument xml = new XmlDocument();
DataTable dt = CreateDataSource();
DataSet ds = new DataSet("testDS");
ds.Tables.Add(dt);
xml.LoadXml(ds.GetXml());
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.ContentType = "text/xml ";
HttpContext.Current.Response.Charset = "UTF-8 ";
XmlTextWriter writer = new XmlTextWriter(HttpContext.Current.Response.OutputStream, System.Text.Encoding.UTF8);
writer.Formatting = Formatting.Indented;
xml.WriteTo(writer);
writer.Flush();
HttpContext.Current.Response.End();
}
public static string displayparentmodal(List<string> _arr)
{
DataSet ds = new DataSet();
SystemObjects.OPT _opt = new SystemObjects.OPT();
_opt.TeamID = Convert.ToInt32(_arr[0].ToString());
try
{
ds.Tables.Add(_opt.DisplaySettingParentWorktypes());
}
catch (Exception ex)
{
}
return ds.GetXml();
}
public void WriteToDatabase(string errorMessage, string errorType, System.Data.DataSet DatasetWhichGenratedException)
{
try
{
System.Data.DataSet dsError = new System.Data.DataSet();
System.Data.DataTable DtErrorMessage = new System.Data.DataTable("ErrorMessages");
System.Data.DataRow drError = DtErrorMessage.NewRow();
DtErrorMessage.Columns.Add("ErrorMessage");
DtErrorMessage.Columns.Add("VerboseInfo");
DtErrorMessage.Columns.Add("DatasetInfo");
DtErrorMessage.Columns.Add("ErrorType");
DtErrorMessage.Columns.Add("CreatedBy");
DtErrorMessage.Columns.Add("CreatedDate");
drError["ErrorMessage"] = errorMessage; // FilteredEntry;
drError["VerboseInfo"] = ""; // VerboseInformation;
if (DatasetWhichGenratedException != null)
{
drError["DatasetInfo"] = DatasetWhichGenratedException.GetXml();
}
else
{
drError["DatasetInfo"] = null;
}
drError["ErrorType"] = errorType;
drError["CreatedBy"] = "sa";//Streamline.ProviderAccess.CommonFunctions.UserName;
drError["CreatedDate"] = DateTime.Now;
DtErrorMessage.Rows.Add(drError);
dsError.Tables.Add(DtErrorMessage);
Streamline.DataService.StaticLogManager.WriteToDatabase(dsError);
}
catch (Exception ex)
{
Streamline.DataService.StaticLogManager.WriteToDatabase(ex.Message.ToString(), "In Exception handling of Global.asax", "General", "sa");
string msg = ex.Message;
//throw ex;
}
}
/// <summary>
/// 获取存取在DataSet的数据的xml表现形式
/// </summary>
/// <param name="dataSet">数据源DataSet</param>
/// <param name="mappingType">列的映射方式</param>
/// <returns>DataSet中的数据的xml表现形式</returns>
public static string GetXml(this DataSet dataSet, MappingType mappingType)
{
FrameDataSetExtends.SetDataSetColumnElementToAttribute(dataSet, mappingType);
return(dataSet.GetXml());
}
/// <summary>
/// Converts the specified Excel file to it's equivalent XmlDocument
/// </summary>
/// <param name="sFilePath"></param>
/// <param name="sExcelFileQuery"></param>
/// <returns></returns>
private static XmlDocument ConvertExcelFile(string sFilePath)
{
XmlDocument xmlRaw = null;
System.Data.OleDb.OleDbConnection oleConnection = null;
try
{
oleConnection = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + sFilePath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1;\"");
string[] sWorksheets = GetExcelWorksheets(sFilePath);
if (sWorksheets.Length <= 0)
{
throw new Exception("Excel spreadsheet contains no valid worksheets!");
}
string sWorksheetName = string.Empty;
foreach (string sTableName in sWorksheets)
{
// initially set the data worksheet to the first/0th worksheet
if (sWorksheetName == string.Empty)
{
sWorksheetName = sTableName;
}
// loop thru until we find the one called 'DataEntry', in case it's not the first one
if (sTableName.Trim().ToUpper() == ExcelWorksheetName.ToUpper())
{
sWorksheetName = sTableName;
break;
}
}
if (m_sDebugOutputDirectory.Trim() != string.Empty)
{
FileInfo fiDebug = new FileInfo(sFilePath);
string sDebugFilePath = Path.Combine(m_sDebugOutputDirectory, fiDebug.Name);
if (File.Exists(sDebugFilePath) == true)
{
FileUtilities.DeleteFile(sDebugFilePath);
}
File.Copy(sFilePath, sDebugFilePath);
}
string sExcelDataQuery = string.Format("SELECT * FROM [{0}]", sWorksheetName);
System.Data.OleDb.OleDbDataAdapter oleCommand = new System.Data.OleDb.OleDbDataAdapter(sExcelDataQuery, oleConnection);
System.Data.DataSet dsExcel = new System.Data.DataSet();
oleCommand.Fill(dsExcel);
xmlRaw = CleanupRawXml(dsExcel.GetXml());
}
catch (Exception ex)
{
string sErrorMessage = ex.Message;
if (ex.InnerException != null)
{
sErrorMessage = sErrorMessage + " Details: " + ex.InnerException.Message;
}
throw new Exception(sErrorMessage);
}
finally
{
oleConnection.Close();
}
return(xmlRaw);
}
