Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Mckenzo101
Starting Member
2 Posts |
 Posted - 2015-03-15 : 20:52:30
|
Edit
me and a colleague are working on an assignment that requires a simple data transfer of a .SQL Server to MS Excel; using C#. I was able to develop the "foundation" of what I believe works/ However I'm unable to run the program. I know its a simple task but assistance would be appreciated.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.Odbc;
using System.IO;
using System.Configuration;
//Project LAB1
namespace ProjectLab1
{
class Program
{
protected void page_load(object sender, EventArgs e)
{
}
protected void btnExport_Click(object sender, EventArgs e)
{
string strDelimiter = ddlExportFormat.SelectedValue == "COMMA DELIMITED" ? " ," : "|";
string conString = "Driver={MySQL ODBC 5.3 ANSI Driver};"
+ "Server=devry.edupe.net;Port=4300;"
+ "Database=Inventory_3556;"
+ "uid=3556;pwd=@@Ron61686";
StringBuilder sb = new StringBuilder();
using (OdbcConnection connection = new OdbcConnection(conString))
connection.Open();
{
string theQuery = "SELECT * FROM item i, inventory v where i.invent_id=v.invent_id";
OdbcDataAdapter DataAdapter = new OdbcDataAdapter(theQuery, connection);
DataSet ds = new DataSet();
DataAdapter.Fill(ds, "items");
ds.Tables[0].TableName = "ITEM";
ds.Tables[1].TableName = "QUANT";
ds.Tables[2].TableName = "SIZE";
ds.Tables[3].TableName = "COLOR";
ds.Tables[4].TableName = "PRICE\n";
}
foreach (DataRow itemDR in ds.Table["ITEMS"].Rows)
{
int itemId = Comvert.ToInt32(itemDR["ITEMS"]);
sb.Append(itemId.ToString() + strDelimiter);
sb.Append(itemDR["ITEMS"].ToString() + strDelimiter);
sb.Append(itemDR["QUANT"].ToString() + strDelimiter);
sb.Append(itemDR["SIZE"].ToString() + strDelimiter);
sb.Append(itemDR["COLOR"].ToString() + strDelimiter);
sb.Append(itemDR["PRICE\n"].ToString() + strDelimiter);
sb.Append("\r\n");
}
{
string strFileName = "thefile.xls";
StreamWriter file = new StreamWriter(@"C:\Users\debom_000\Desktop\Data\" + strFileName);
file.WriteLine(sb.ToString());
File.Close();
connection.Close(); // Close connection
//Have program pause to keep from closing console window
}
}
}
} |
|
|
|
|
|
Export SQL Server Data to Excel Using C#?