最近在做一个项目,要求把DataSet里的数据导出到Excel文件当中,使用OleDb的链接方法。然后,当中数据的列数比较多,直接使用列举参数的方法,显得不安全和代码冗余。于是提高了要求,全部用Parameters来写。下面把整理的代码放在这:
Code
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties='Excel 12.0 Xml;HDR=YES';");
using (conn)
{
conn.Open();
OleDbCommand DBCmd = new OleDbCommand();
DBCmd.Connection = conn;
DataSet ds = Load_DataSet();
DataTable result = ds.Tables["Search Report"];
OleDbParameter[] Params = new OleDbParameter[result.Columns.Count];
string[] ParamNames = new string[result.Columns.Count];
for (int i = 0; i < ParamNames.Length; i++)
ParamNames[i] = "@in_data" + i.ToString();
DBCmd.CommandText = "INSERT INTO [Sheet1$] values(";
for (int i = 0; i < ParamNames.Length - 1; i++)
DBCmd.CommandText += ParamNames[i] + ",";
DBCmd.CommandText += ParamNames[ParamNames.Length - 1] + ")";
for (int i = 0; i < result.Rows.Count; i++)
{
for (int j = 0; j < result.Columns.Count; j++)
{
Params[j] = new OleDbParameter(ParamNames[j], OleDbType.VarChar);
DBCmd.Parameters.Add(Params[j]);
DBCmd.Parameters[ParamNames[j]].Value = result.Rows[i][j].ToString();
}
DBCmd.ExecuteNonQuery();
}
一开始使用OleDbCommand.Parameters 属性采用命名的参数的形式,虽然编译可以通过,但是只与参数顺序有关,而不能赋值给参数,还以为是微软的Bug,后才知道自己错了.查MSDN整理下:
<1>关于OleDbCommand.Parameters 属性
如果 CommandType 设置为 Text,OLE DB .NET 提供程序不支持将参数传递给 OleDbCommand 调用的 SQL 语句或存储过程的命名参数。在这种情况下,必须使用问号 (?) 占位符。例如:
SELECT * FROM Customers WHERE CustomerID = ?
因此,OleDbParameter 对象添加到 OleDbParameterCollection 的顺序必须直接对应于命令文本中参数的问号占位符的位置。
<2>SqlCommand.Parameters 属性
SQL Server 的 Microsoft .NET Framework 数据提供程序不支持在向通过 CommandType.Text 的命令调用的 SQL 语句或存储过程传递参数时使用问号 (?) 占位符。在这种情况下,必须使用命名的参数。例如:
Code
SELECT * FROM Customers WHERE CustomerID = @CustomerID
public void CreateSqlCommand(SqlConnection myConnection,
string queryString, SqlParameter[] paramArray)
{
SqlCommand command = new SqlCommand(queryString, myConnection);
command.CommandText =
"SELECT CustomerID, CompanyName FROM Customers "
+ "WHERE Country = @Country AND City = @City";
command.Parameters.Add(paramArray);
for (int j=0; j<paramArray.Length; j++)
{
command.Parameters.Add(paramArray[j]) ;
}
string message = "";
for (int i = 0; i < command.Parameters.Count; i++)
{
message += command.Parameters[i].ToString() + "\n";
}
Console.WriteLine(message);
}