This article describes one approach to upload data from a backend database to a frontend SQL database hosted on a web server using FtpWebRequest, GZipStream and SqlBulkInsert classes in .NET. The solution consists of two different parts; one application running on one machine; for fetching the data from the backend database, compressing it and then sending the data to the server using FtpWebRequest. This could be a console application running on AT commands. The other application, running on the receiving server is in this case a Windows Service. This handles new files posted to the server, importing them using SqlBulkInsert to the SQL server table.
The basic idea is that the SQL statement fetching the data from the back end database meets a corresponding SQL Server datatable "column wise" and the datatable in the xml file needs to have the same name as the SQL Server table.
Fetching data and writing the XML file
We use a simple table, just illustrating the creation of the XML file being compressed and sent to the server:
// The current execution path is the location for the files
string appDir = Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase);
appDir = appDir.Replace("file:\\", "");
string c = "Data Source=the_local_SQL_server;Initial Catalog=the_db_itself;Integrated Security=SSPI;";
using (SqlConnection conn = new SqlConnection(c))
{
string cmd = "SELECT customerID, customerName from customers as customers";
SqlDataAdapter daImport = new SqlDataAdapter(cmd, conn);
using (DataSet ds = DataSet())
{
sqlConn.Open();
daImport.Fill(ds, "customers");
sqlConn.Close();
ds.Tables[0].WriteXml(appDir + "\\" + ds.Tables[0].TableName + ".xml");
}
}
This SQL statement meets a table having two columns; customerID and customerName on the SQL Server.
Okay, now we have the customer.xml file in the current application folder, and now we want to compress it and send it using FtpWebRequest.
First, compress the file
foreach (string filePath in Directory.GetFiles(appDir, "*.xml"))
{
FileInfo f = new FileInfo(filePath);
//Rename the remote file so that we know it's a compressed file
string shortfilename = f.FileName.Replace(".xml", ".gzipxml");
//Read the original xml file into a filestream
using (FileStream fsOrg = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.Read))
{
//Create a new filestream for the compressed file
using (FileStream fsNew = new FileStream(appDir + "\\" + shortfilename, FileMode.Create, FileAccess.ReadWrite))
{
byte[] buffer = new byte[fsOrg.Length];
int count = fsOrg.Read(buffer, 0, buffer.Length);
fsOrg.Close();
//Create a gzipstream from the new filestream
using (GZipStream fsZip = new GZipStream(fsNew, CompressionMode.Compress))
{
fsZip.Write(buffer, 0, buffer.Length
fsZip.Close();
}
}
}
//Delete the original xml file
File.Delete(filePath);
}
The directory now contains of a compressed XML file named customers with extension gzipxml.
Create the request:
FtpWebRequest request = (FtpWebRequest)WebRequest.Create("ftp://www.microsoft.com/imp/tmp/" + remoteFileName);
request.KeepAlive = false;
request.Method = WebRequestMethods.Ftp.UploadFile;
request.Credentials = new NetworkCredential("bill", "gates");
This code snippet creates the request against the remote FTP server and as we normally don't use anonymous access, we fabricate the NetworkCredential with user name and password for a famous person.
Writing the FileStream to the server
using (FileStream fs = new FileStream(appDir + "\\" + shortfilename, FileMode.Open, FileAccess.Read, FileShare.Read))
{
byte[] fileContents = new byte[fs.Length];
fs.Read(fileContents, 0, fileContents.Length);
request.ContentLength = fileContents.Length;
using (Stream requestStream = request.GetRequestStream())
{
requestStream.Write(fileContents, 0, fileContents.Length);
requestStream.Close();
}
fs.Close();
fileContents = null;
}
FtpWebResponse response = (FtpWebResponse)request.GetResponse();
This places the file in the tmp folder as stated above and we want to move it from tmp to the import folder, making it available for insertion into SQL server table customers.
request = null;
request = (FtpWebRequest)WebRequest.Create("ftp://www.microsoft.com/imp/" + shortfilename);
request.KeepAlive = false;
request.Method = System.Net.WebRequestMethods.Ftp.Rename;
request.Credentials = new NetworkCredential("bill", "gates");
request.RenameTo = @"..\" + shortfilename;
response = (FtpWebResponse)request.GetResponse();
Okay, that's it. Now we placed to compressed xml (gzipxml) file in the import folder on the FTP server.
Decompressing and inserting the xml data into SQL Server table.
The application on the server is a Windows service that in its OnStart method starts a timer which has an ElapsedEventHandler checking the import folder for new files.
private System.Timers.Timer tmrCheckForFiles;
protected override void OnStart(string[] args)
{
// Start looking for files
tmrCheckForFiles = new System.Timers.Timer(30000);
tmrCheckForFiles.Elapsed += new
System.Timers.ElapsedEventHandler(checkForFiles);
tmrCheckForFiles.Start();
}
The method checkForFiles looks in the import folder and enumerates thru all of the files, decompressing them if they are zipped and inserts them to the corresponding SQL Server datatable.
First, we need to decompress the gzipxml files.
foreach (string f in Directory.GetFiles(localPath))
{
if (f.IndexOf(".gzipxml") != -1)
{
FileInfo fi = new FileInfo(f);
string new_file = fi.Filename.Replace(".xml",".gzipxml");
using (FileStream fsOrg = new FileStream(f, FileMode.Open, FileAccess.Read, FileShare.Read))
{
using (FileStream fsNew = new FileStream(localPath + "\\" + new_file, FileMode.Create, FileAccess.Write, FileShare.Read))
{
using (GZipStream fsZip = new GZipStream(fsOrg, CompressionMode.Decompress, true))
{
const int buffersize = 4096;
byte[] buffer = new byte[buffersize];
int count = 0;
while (true)
{
count = fsZip.Read(buffer, 0, buffersize);
if (count != 0)
{
fsNew.Write(buffer, 0, count);
}
if (count != buffersize)
{ break; }
}
fsZip.Close();
}
fsNew.Close();
fsOrg.Close();
}
}
File.Delete(f);
}
Now we have the xml file back to its original format, ready for insertion to SQL Server table.
Reading the xml file and inserting it
foreach (string xml_file in Directory.GetFiles(localPath))
{
using (System.Data.DataSet dsImport = new DataSet())
{
string c = "here goes your connectionstring to your SQL Server";
// Read in the file to memory
dsImport.ReadXml(xml_file);
// This method truncates the receiving table before insertion
// This is left out to shorten the example.
clearTable(dsImport.Tables[0].TableName);
if (dsImport.Tables[0].Rows.Count != 0)
{
System.Data.SqlClient.SqlBulkCopy sqlBulk = new
System.Data.SqlClient.SqlBulkCopy(c,
SqlBulkCopyOptions.TableLock);
// Just make sure that the datatable in the xml file
// has the same name as the receiving table
sqlBulk.DestinationTableName = dsImport.Tables[0].TableName;
sqlBulk.WriteToServer(dsImport.Tables[0]);
sqlBulk.Close();
}
// Delete the xml file
File.Delete(FileName);
}
That's it!
Please comment and give your thoughts and ideas to improve this solution, thanks!