从SQL Server DB导出PDF并将映射写入文本文件 [英] Exporting PDF's from SQL Server DB and writing a Map to a Text File
问题描述
我需要编写一个应用程序或查询以将大量PDF导出到文件中，并创建一个带分隔符的文本文件，以显示文件所在的位置并包含记录的ID.
I need to write an application or Query to export a large amount of PDF's to file and create a delimited text file that shows where the files are and contains the ID of the record.
我正在考虑做的事情是使用一个控制台应用程序，该应用程序将从数据库中导出PDF后将条目写入文本文件，这样，在编写文本文件时，我将所有信息都收集在一起，这样我就可以确保定界文本文件中的所有数据都是正确的.
what I was thinking about doing was using a console application that would write the entry to the text file after exporting the PDF from the DB, that way I would have all the information together when writing the Text File so that I could make sure that all the Data in the Delimited text file was accurate.
起初我在考虑使用数据集执行此操作，但是将有50,000行以上的数据.我不太确定DataTable会更好
at first I was thinking about using a Dataset to do this, but there are going to be more than 50,000 rows of Data. I am not so sure that a DataTable would be any better
我也在考虑使用BCP实用程序，但是从我阅读的内容来看，导出并不能从数据中返回PDF文件，这是真的吗?
I was also looking into using the BCP utility, but from what I was reading the export doesn't give me a PDF File back from the Data, is this true?
我可能会认为自己是一个初学者，正在编写类似这样的内容. 这样的数据结构应该使用什么?我会使用游标吗?如果是的话，我将如何设置它以适合我的工作?
I would probably consider myself a beginner, programming something like this. what should I use for a Data Structure like this? would I use a Cursor, and if so how would I set it up to fit what I am doing?
更新
我将尝试使用DataSet选项，但使用do while循环一次将其限制为1天的数据，这样我就可以遍历从数据开始到今天的每一天.所以我将进行一天的数据处理，然后删除数据集，然后再进行下一次约会.
I am going to try and use the DataSet option, but limit it to 1 days worth of data at a time using a do while loop, so that I can loop through every day from the beginning of the data until today's date. so I will do a days worth of Data and then get rid of the DataSet, then do the next date.
有人在我的逻辑中看到任何会引起问题的东西吗?
does anyone see anything in my Logic that would cause issues?
推荐答案
当我最终完成研究该问题的所有不同方法时，编写代码确实非常简单.我根本没有使用BCP.
when I finally finished researching all the different approaches to the problem, it was really quite simple to code. I did not use BCP at all.
我为要在文本文件中提取的信息创建了变量.
I created Variables for the information that I was extracting that I wanted inside the Text File.
- 文件名
- 日期(从SQL表获取原始创建日期)
- 案例编号(要链接到的第三方程序的内部标识符)
- 描述(源自SQL表以描述文档)
然后我将应用程序投入使用，一次将代码写入PDF
Then I put the application to work Writing the Code to PDF one at a time
using (SqlConnection Conn = new SqlConnection(strSQLConn))
{
//open the connection
Conn.Open();
Console.WriteLine("the connection is open");
//Variables needed for looping
DateTime Today = System.DateTime.Now;
DateTime StartDate = Convert.ToDateTime("2008-06-11 00:00:00");
//DateTime StartDate = Today.AddDays(-10);
Console.WriteLine("Converting the Documents from " + StartDate.ToString() + " - TO - " + Today.ToString());
Console.WriteLine("Press Any Key to continue.");
Console.ReadLine();
int RecordCount = 0;
ulong ByteCount = 0;
int i = 1;
foreach (DateTime day in EachDay(StartDate, Today))
{
String strDay = day.ToString();
// Create a SQLCommand to retrieve Data
SqlCommand getRecords = new SqlCommand("spRecapturePDF", Conn);
getRecords.CommandType = CommandType.StoredProcedure;
getRecords.Parameters.Add(new SqlParameter("@OneDay", strDay));
SqlDataReader reader = getRecords.ExecuteReader();
//stuff exporting the binary code to the PDF format
FileStream fs;
BinaryWriter bw;
int buffersize = 100;
byte[] outbyte = new byte[buffersize];
long retval;
long startIndex = 0;
int j = 1;
while (reader.Read())
{
strFileName = reader.GetString(0) + "-" + i + "-" + j;
strDock_no = reader.GetString(0);
dtFiledate = reader.GetDateTime(2);
strDescription = reader.GetString(4);
fs = new FileStream("c:\\FolderName\\" + strFileName + ".pdf", FileMode.OpenOrCreate, FileAccess.Write);
bw = new BinaryWriter(fs);
startIndex = 0;
retval = reader.GetBytes(1,startIndex,outbyte,0,buffersize);
while (retval == buffersize)
{
bw.Write(outbyte);
bw.Flush();
startIndex += buffersize;
retval = reader.GetBytes(1,startIndex,outbyte,0,buffersize);
}
//write the remaining buffer.
bw.Write(outbyte,0,(int)retval);
ByteCount = ByteCount + Convert.ToUInt64(fs.Length);
bw.Flush();
//close the output file
bw.Close();
fs.Close();
//need to write to the Text file here.
TextWriter tw = new StreamWriter(path,true);
tw.WriteLine(strDock_no + "~" + dtFiledate.ToString() + "~" + "c:\\FolderName\\" + strFileName + ".pdf" + "~" + strDescription);
tw.Close();
// increment the J variable for the Next FileName
j++;
RecordCount++;
}
//close the reader and the connection
reader.Close();
i++;
}
Console.WriteLine("Number of Records Processed: " + RecordCount.ToString());
Console.WriteLine("for a Total of : " + ByteCount + " Bytes");
Decimal MByteCount = new Decimal(2);
MByteCount = Convert.ToDecimal(ByteCount) / 1024 / 1024;
Decimal GByteCount = new Decimal(2);
GByteCount = MByteCount / 1024;
Console.WriteLine("Total MBs : " + MByteCount.ToString() + " MB");
Console.WriteLine("Total GBs : " + GByteCount.ToString() + " GB");
Console.WriteLine("Press Enter to Continue ...");
Console.ReadLine();
}
此代码包含在foreach语句中，该语句从开始日期到结束日期每天都在变化.在该foreach语句中，应用程序调用了一个存储过程，该存储过程在指定的日期被调用以调用当天输入的记录.
this Code was enclosed in a foreach statement that went day by day, from a starting date to an end date. inside that foreach statement the Application called a stored procedure that was given the specified day to call the records that were entered that day.
变量i和j是因为即使我具有相同的案例编号，我也需要具有唯一的文件名. i代表一天(因为我在select语句中一天一天地走)，而j代表select语句当天的记录编号.
variables i and j were created because I needed to have a unique Filename even if I had the same Case Number. i represented the day (because I went day by day in my select statement) and j represented the record number for that day from the select statement.
foreach和while循环被包含在using(conn)中，因此无论最终什么连接都将关闭.
the foreach and the while loops were enclosed in a using(conn) so that no matter what the connection would be closed finally.
我写了文本文件. Text文件是在所有循环之外创建的，因此我可以仅附加文件而不是覆盖它.该代码是:
at the end of the while loop I wrote to the Text File. the Text file was created outside of all the loops so that I could just append the file rather than overwrite it. that code is:
string path = @"c:\\FolderName\\TextFile.txt";
if (!File.Exists(path))
{
TextWriter tw = new StreamWriter(path, false);
tw.WriteLine("Dock_No~Date~FileName(Location)~Description");
tw.Close();
}
我希望这对其他人有帮助.我遗漏了我想要的功能所不需要的所有Console.Writeline和Console.ReadLine代码.我还添加了一些代码，该代码将计算已写入的字节数，并添加一些代码以计算已处理的记录.这只是一些有趣的东西，我需要最后整理一下有趣的东西.
I hope that this helps someone else. I left out all the Console.Writeline and Console.ReadLine code that wasn't necessary to the functionality i was looking for. I had added some code also that would count the bytes that were written and some code to count the records processed. this is just fun stuff to know, I need to clean up the Fun stuff at the end.
这些是从SQL Server的Blob字段中完成大量PDF提取所花费的勇气，减去一些Connection Mumbo Jumbo
these are the Guts of what it took to accomplish a mass Extract of PDFs from a Blob Field in SQL Server, minus some Connection Mumbo Jumbo
设置圣诞节日
这是我用来使foreach以我想要的方式工作的代码.
this is the Code that I used to make the foreach work the way that I wanted it to.
static public IEnumerable<DateTime> EachDay(DateTime Startdate, DateTime EndDate)
{
for (var day = Startdate.Date; day.Date <= EndDate.Date; day = day.AddDays(1))
yield return day;
}
这篇关于从SQL Server DB导出PDF并将映射写入文本文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
