有關於大數據量導入導出的工具BCP的具體語法,可以參見SQLServer的幫助文檔代碼
/// <summary>
/// 寫入全部數據到txt文件
/// </summary>
/// <param name="fileName">txt文件全路勁</param>
public void WriteAllToFile(string fileName)
{
List<string> queryString = new List<string>();
//開啟xp_cmdshell
string xp_cmdshellOpen1 = "EXEC sp_configure 'show advanced options', 1;RECONFIGURE;";
string xp_cmdshellOpen2 = "EXEC sp_configure 'xp_cmdshell', 1;RECONFIGURE;";
//導出數據String為bcp SQLServer2005DB.dbo.test out e:\test.txt -c -U sa -P sa123 -S ET07\SQLEXPRESS
string[] messageInfo = PubConstant.MessageInfo();
string serverName = messageInfo[0];
string dbName = messageInfo[1];
string userName = messageInfo[2];
string passWord = messageInfo[3];
string exportStr =
string.Format("exec {0}..xp_cmdshell 'bcp {1}.dbo.Department out {2} -c -U {3} -P {4} -S {5}'", dbName, dbName, fileName, userName, passWord, serverName);
//關閉xp_comshell
string xp_cmdshellClose1 = "EXEC sp_configure 'show advanced options', 1;RECONFIGURE;";
string xp_cmdshellClose2 = "EXEC sp_configure 'xp_cmdshell', 0;RECONFIGURE;";
queryString.Add(xp_cmdshellOpen1);
queryString.Add(xp_cmdshellOpen2);
queryString.Add(exportStr);
queryString.Add(xp_cmdshellClose1);
queryString.Add(xp_cmdshellClose2);
foreach (string item in queryString)
{
DbHelperSQL.ExecuteSql(item);
}
}
//開啟xp_cmdshell與關閉xp_cmdshell,是為了方便執行bcp命令行的。使用完之後關閉命令。
然後就是執行sql語句,DbHelperSQL在我上一篇隨筆中有源碼。就是簡單的執行sql的幫助類。
用bcp工具的好處是,備份大數據量的數據比較快。具體你可以做單元測試測試一番。
接下來要介紹的是一個很好的工具,OPENROWSET
也是SQLServer2005的工具,可以直接讀取Access數據庫,或者直接連接遠程的數據庫服務器。
下面是代碼實現。
代碼
/// <summary>
/// 直接讀取Access數據庫
/// </summary>
/// <param name="fileName"></param>
public void ReadFromAccess(string fileName)
{
List<string> queryString = new List<string>();
//開啟
string xp_cmdshellOpen1 = "exec sp_configure 'show advanced options',1 reconfigure";
string xp_cmdshellOpen2 = "exec sp_configure 'Ad Hoc Distributed QuerIEs',1 reconfigure";
string exportStr = @"SELECT CustomerID, CompanyName FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','C:\testFile\Northwind.mdb';'admin';'',Customers)";
//關閉
string xp_cmdshellClose1 = "exec sp_configure 'Ad Hoc Distributed QuerIEs',0 reconfigure";
string xp_cmdshellClose2 = "exec sp_configure 'show advanced options',0 reconfigure";
queryString.Add(xp_cmdshellOpen1);
queryString.Add(xp_cmdshellOpen2);
queryString.Add(exportStr);
queryString.Add(xp_cmdshellClose1);
queryString.Add(xp_cmdshellClose2);
foreach (string item in queryString)
{
DbHelperSQL.ExecuteSql(item);
}
}