I want to create excel file by oledb, and my code is
int index = 0;
foreach (DataColumn col in dt.Columns)
{
datatype[index] = $"[{col.ColumnName}]" + " String";
index++;
}
string query = string.Join(",", datatype);
cmd.CommandText = $"CREATE TABLE [{sheetName}] ({query});";
cmd.ExecuteNonQuery();
and I have column name like Product material[EN]
I used break point and check my command string
CREATE TABLE [products]
(
[another Title] String,
[Use of product[EN]] String,
[Product material[EN]] String,
[another Title] String
);
I've tried
Product material[EN]Product material[[EN]]][Product material[EN]]'[Product material[EN]]'Product material[[]EN]['Product material[EN]']- let all columns in query without bracket
CREATE TABLE [products]
(
another Title String,
Use of product[EN] String,
Product material[EN] String,
another Title String
);
- let all value in query without bracket
CREATE TABLE [products]
(
another Title String,
Use of product EN String,
Product material EN String,
another Title String
);
but the above syntax all received Syntax error in field definition.
If using parameter and modify the code
cmd.CommandText = $"CREATE TABLE [{sheetName}] (";
for (int i = 0; i < dt.Columns.Count; i++)
{
cmd.CommandText = cmd.CommandText + "[@var" + i.ToString() + "] String,";
cmd.Parameters.AddWithValue("@var" + i.ToString(), datatype[i]);
}
cmd.CommandText = cmd.CommandText.Remove(cmd.CommandText.Length - 1, 1) + ");";
or change
cmd.Parameters.AddWithValue("@var" + i.ToString(), datatype[i]);
to
cmd.Parameters.Add(new OleDbParameter("@var" + i.ToString(), datatype[i]));
and get the command string
CREATE TABLE [products]
(
[@var0] String,
...
[@varN] String
);
will get a successful excel file but with @varxx column names
change to Product material(EN) is work but I need to use bracket []
how do I add bracket into column name use oledb ?