使用aspx将存储过程中的数据导出到txt文件
本文关键字：txt 文件 数据 aspx 存储 存储过程 过程中 使用 | 更新日期: 2023-09-27 18:01:07
我想通过从aspx调用存储过程来导出文件，并将数据保存到.txt文件中。每个列都必须具有需要设置的特定列长度。下面是我的代码，但当我运行程序时，输出只显示列名。。。并且没有数据出现。看起来程序没有读取行语句。请帮我
protected void Page_Load(object sender, EventArgs e)
{
Cursor.Current = Cursors.WaitCursor;
string str = "Server=KABS;Database=HOT;uid=sa;pwd=DDD;Connection Timeout=6000";
if (Request.QueryString["ProcessName"] != null)
{
using (SqlConnection con = new SqlConnection(str))
{
if (Request.QueryString["ProcessName"].ToString().Equals("Ebill"))
{
using (SqlCommand cmd = new SqlCommand("AR_Ebill_claim", con))
{
cmd.CommandType = CommandType.StoredProcedure;
string compcode = null;
DateTime dateFrom = DateTime.Now;
DateTime dateTo = DateTime.Now;
string episType = null;
string debtorCode = null;
if (Request.QueryString["compcode"] != null)
{
if (string.IsNullOrEmpty(Convert.ToString(Request.QueryString["compcode"])))
{
compcode = null;
}
else
{
compcode = Convert.ToString(Request.QueryString["compcode"]);
}
}
if (Request.QueryString["dateFrom"] != null)
{
DateTime dtFrom = DateTime.Parse(Request.QueryString["dateFrom"]);
dtFrom.ToString("dd-MMM-yyyy");
if (dtFrom == null)
{
dateFrom = DateTime.Now;
}
else
{
dateFrom = dtFrom;
}
}
if (Request.QueryString["dateTo"] != null)
{
DateTime dtTo = DateTime.Parse(Request.QueryString["dateTo"]);
dtTo.ToString("dd-MMM-yyyy");
if (dtTo == null)
{
dateTo = DateTime.Now;
}
else
{
dateTo = dtTo;
}
}
if (Request.QueryString["episType"] != null)
{
if (string.IsNullOrEmpty(Convert.ToString(Request.QueryString["episType"])))
{
episType = null;
}
else
{
episType = Convert.ToString(Request.QueryString["episType"]);
}
}
if (Request.QueryString["debtorCode"] != null)
{
if (string.IsNullOrEmpty(Convert.ToString(Request.QueryString["debtorCode"])))
{
debtorCode = null;
}
else
{
debtorCode = Convert.ToString(Request.QueryString["debtorCode"]);
}
}
cmd.Parameters.Add("@compcode", SqlDbType.VarChar, 100);
cmd.Parameters["@compcode"].Value = compcode;
cmd.Parameters.Add("@dateFrom", SqlDbType.SmallDateTime);
cmd.Parameters["@dateFrom"].Value = dateFrom;
cmd.Parameters.Add("@dateTo", SqlDbType.SmallDateTime);
cmd.Parameters["@dateTo"].Value = dateTo;
cmd.Parameters.Add("@episType", SqlDbType.VarChar, 40);
cmd.Parameters["@episType"].Value = episType;
cmd.Parameters.Add("@debtorCode", SqlDbType.VarChar, 100);
cmd.Parameters["@debtorCode"].Value = debtorCode;
con.Open();
//cmd.ExecuteNonQuery();
//gdBill.EmptyDataText = "No Records Found";
//gdBill.DataSource = cmd.ExecuteReader();
//gdBill.DataBind();
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
da.SelectCommand = cmd;
da.Fill(dt);
string txt = string.Empty;
if (dt.Columns.Count > 0)
{
foreach (DataColumn column in dt.Columns)
{
//Add the Header row for Text file.
txt += column.ColumnName + "'t't";
}
}
//Add new line.
txt += "'r'n";
if (dt.Rows.Count > 0)
{
foreach (DataRow row in dt.Rows)
{
foreach (DataColumn column in dt.Columns)
{
//Add the Data rows.
txt += row[column.ColumnName].ToString() + "'t't";
}
//Add new line.
txt += "'r'n";
}
}
Response.Clear();
Response.Buffer = true;
Response.AddHeader("content-disposition", "attachment;filename=E-Billing.txt");
Response.Charset = "";
Response.ContentType = "application/text";
Response.Output.Write(txt);
Response.Flush();
Response.End();
Cursor.Current = Cursors.AppStarting;
}
}
//cmd.Dispose();
con.Close();
}
}
}
我测试了您的代码，它正常工作。问题是存储过程AR_Ebill_claim返回零行。但是没有错误，所以列名确实会被导出。
我认为问题出在参数上。首先检查这些，并测试存储过程是否在SQL server studio或类似程序中提供了所需的结果。
我已经得到了解决方案，如下代码所示：(
if (Request.QueryString["ProcessName"].ToString().Equals("Ebill"))
{
using (SqlCommand cmd = new SqlCommand("AR_Ebill_claim", con))
{
cmd.CommandType = CommandType.StoredProcedure;
string compcode = null;
DateTime dateFrom = DateTime.Now;
DateTime dateTo = DateTime.Now;
string episType = null;
string debtorCode = null;
if (Request.QueryString["compcode"] != null)
{
if (string.IsNullOrEmpty(Convert.ToString(Request.QueryString["compcode"])))
{
compcode = null;
}
else
{
compcode = Convert.ToString(Request.QueryString["compcode"]);
}
}
if (Request.QueryString["dateFrom"] != null)
{
DateTime dtFrom = DateTime.Parse(Request.QueryString["dateFrom"]);
dtFrom.ToString("dd-MMM-yyyy");
if (dtFrom == null)
{
dateFrom = DateTime.Now;
}
else
{
dateFrom = dtFrom;
}
}
if (Request.QueryString["dateTo"] != null)
{
DateTime dtTo = DateTime.Parse(Request.QueryString["dateTo"]);
dtTo.ToString("dd-MMM-yyyy");
if (dtTo == null)
{
dateTo = DateTime.Now;
}
else
{
dateTo = dtTo;
}
}
if (Request.QueryString["episType"] != null)
{
if (string.IsNullOrEmpty(Convert.ToString(Request.QueryString["episType"])))
{
episType = null;
}
else
{
episType = Convert.ToString(Request.QueryString["episType"]);
}
}
if (Request.QueryString["debtorCode"] != null)
{
if (string.IsNullOrEmpty(Convert.ToString(Request.QueryString["debtorCode"])))
{
debtorCode = null;
}
else
{
debtorCode = Convert.ToString(Request.QueryString["debtorCode"]);
}
}
cmd.Parameters.Add("@compcode", SqlDbType.VarChar, 100);
cmd.Parameters["@compcode"].Value = compcode;
cmd.Parameters.Add("@dateFrom", SqlDbType.SmallDateTime);
cmd.Parameters["@dateFrom"].Value = dateFrom;
cmd.Parameters.Add("@dateTo", SqlDbType.SmallDateTime);
cmd.Parameters["@dateTo"].Value = dateTo;
cmd.Parameters.Add("@episType", SqlDbType.VarChar, 40);
cmd.Parameters["@episType"].Value = episType;
cmd.Parameters.Add("@debtorCode", SqlDbType.VarChar, 100);
cmd.Parameters["@debtorCode"].Value = debtorCode;
con.Open();
//cmd.ExecuteNonQuery();
//gdBill.EmptyDataText = "No Records Found";
//gdBill.DataSource = cmd.ExecuteReader();
//gdBill.DataBind();
string outputFilePath = Server.MapPath("~/Documents/EClaim.txt");
if (File.Exists("~/Documents/EClaim.txt"))
{
File.Delete("~/Documents/EClaim.txt");
}
SqlDataAdapter da = new SqlDataAdapter();
DataTable dt = new DataTable();
da.SelectCommand = cmd;
da.Fill(dt);
int[] maxLengths = new int[dt.Columns.Count];
for (int i = 0; i < dt.Columns.Count; i++)
{
maxLengths[i] = dt.Columns[i].ColumnName.Length;
foreach (DataRow row in dt.Rows)
{
if (!row.IsNull(i))
{
int length = row[i].ToString().Length;
if (length > maxLengths[i])
{
maxLengths[i] = length;
}
}
}
}
using (StreamWriter sw = new StreamWriter(outputFilePath, false))
{
//for (int i = 0; i < dt.Columns.Count; i++)
//{
// sw.Write(dt.Columns[i].ColumnName.PadRight(maxLengths[i] + 2));
//}
sw.WriteLine();
foreach (DataRow row in dt.Rows)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
if (!row.IsNull(i))
{
sw.Write(row[i].ToString().PadRight(maxLengths[i] + 1));
}
else
{
sw.Write(new string(' ', maxLengths[i] + 1));
}
}
sw.WriteLine();
}
sw.Close();
}
//string filePath = "~/Documents/EBilling.txt";
//Response.ContentType = "application/text";
//Response.AddHeader("Content-Disposition", "attachment;filename='"" + filePath + "'"");
//Response.TransmitFile(Server.MapPath(filePath));
////Response.End();
}