c# - 使用 C# 插入字段名称为 TA/TJ 的数据库

Question

最近我被分配到一个项目，我们必须将旧的 VB3 进程迁移到 C#，这个进程从 Access 97 文件中获取数据并将其插入 SQL Server，问题是一些“天才”调用字段“Ta/Tj”和“/”破坏了我们的代码，我们尝试了不同的方法但失败了。我们现在无法更改字段名称，因为我们不知道究竟有多少程序使用该表。

这是我的代码

    OleDbConnection connectionEpsOle = new OleDbConnection();
        SqlConnection connectionEpsSql = (SqlConnection)GetConexion.GetConnectionIus();
        DbDataAdapter dataAdapter;
        long nIus;
        long nIdGenealogia;
        string sRubroStr;

        DataSet dataSet = new DataSet();
        DataRow dr;

        string sqlCadena = "SELECT * FROM Tesis WHERE ius =0";

        dataAdapter = new SqlDataAdapter();
        dataAdapter.SelectCommand = new SqlCommand(sqlCadena, connectionEpsSql);

        dataAdapter.Fill(dataSet, "Tesis");

        sRubroStr = tesisDto.RUBRO;
        sRubroStr = MiscFunct.QuitaCarCad(sRubroStr);
        sRubroStr = MiscFunct.ConvMay(sRubroStr);
        sRubroStr = MiscFunct.QuitaDblEspacio(sRubroStr);
        sRubroStr = sRubroStr.Trim();

        if (sRubroStr.Length > 250)
        {
            sRubroStr = sRubroStr.Substring(0, 250);
        }

        dr = dataSet.Tables["Tesis"].NewRow();

        dr["IUS"] = tesisDto.IUS;
        dr["Parte"] = 200;
        dr["Consec"] = 0;
        dr["Rubro"] = tesisDto.RUBRO;
        dr["Texto"] = tesisDto.TEXTO;
        dr["Prec"] = tesisDto.PRECEDENTES;
        dr["Epoca"] = tesisDto.Epoca;
        dr["Sala"] = tesisDto.Sala;
        dr["Fuente"] = tesisDto.Fuente;
        dr["Volumen"] = tesisDto.Volumen;
        dr["Tesis"] = tesisDto.Tesis;
        dr["Pagina"] = tesisDto.Pagina;
        dr["TA/TJ"] = tesisDto.TATJ;
        dr["Materia1"] = tesisDto.Materia1;
        dr["Materia2"] = tesisDto.Materia2;
        dr["Materia3"] = tesisDto.Materia3;
        dr["IdGenealogia"] = tesisDto.idGenealogia;
        dr["ConsecIndx"] = 0;
        dr["IdTCC"] = 0;
        dr["InfAnexos"] = 0; 
        dr["LocAbr"] = " ";
        dr["NumLetra"] = 0;
        dr["ConsecLetra"] = 0;
        dr["Instancia"] = 0;
        dr["ConsecInst"] = 0;
        dr["LocExp"] = " ";
        dr["RIndx"] = "RRR";
        dr["TIndx"] = "TTT";
        dr["PIndx"] = "PPP";
        dr["LIndx"] = "LLL";
        dr["Certificada"] = 0;
        dr["IdSubVolumen"] = 0;

        dataSet.Tables["Tesis"].Rows.Add(dr);

        //dataAdapter.UpdateCommand = connectionEpsSQL.CreateCommand();
        dataAdapter.InsertCommand = connectionEpsSql.CreateCommand();
        dataAdapter.InsertCommand.CommandText = "INSERT INTO Tesis(IUS,Parte,Consec,Rubro,Texto,Prec," +
                                                "Epoca,Sala,Fuente,Volumen,Tesis,Pagina,[TA/TJ],Materia1,Materia2,Materia3," +
                                                "IdGenealogia,ConsecIndx,IdTCC,InfAnexos,LocAbr,NumLetra,ConsecLetra,Instancia," +
                                                "ConsecInst,LocExp,RIndx,TIndx,PIndx,LIndx,Certificada,IdSubVolumen)" +
                                                " VALUES(@IUS,@Parte,@Consec,@Rubro,@Texto,@Prec," +
                                                "@Epoca,@Sala,@Fuente,@Volumen,@Tesis,@Pagina,@[TA/TJ],@Materia1,@Materia2,@Materia3," +
                                                "@IdGenealogia,@ConsecIndx,@IdTCC,@InfAnexos,@LocAbr,@NumLetra,@ConsecLetra," +
                                                "@Instancia,@                                                ConsecInst,@LocExp,@RIndx,@TIndx,@PIndx," +
                                                "@LIndx,@Certificada,@IdSubVolumen)";

        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@IUS", SqlDbType.BigInt, 0, "IUS");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@Parte", SqlDbType.Int, 0, "Parte");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@Consec", SqlDbType.Int, 0, "Consec");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@Rubro", SqlDbType.Text, 0, "Rubro");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@Texto", SqlDbType.Text, 0, "Texto");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@Prec", SqlDbType.Text, 0, "Prec");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@Epoca", SqlDbType.Int, 0, "Epoca");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@Sala", SqlDbType.Int, 0, "Sala");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@Fuente", SqlDbType.Int, 0, "Fuente");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@Volumen", SqlDbType.Int, 0, "Volumen");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@Tesis", SqlDbType.VarChar, 0, "Tesis");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@Pagina", SqlDbType.VarChar, 0, "Pagina");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@[TA/TJ]", SqlDbType.Int, 0, "TA/TJ");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@Materia1", SqlDbType.Int, 0, "Materia1");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@Materia2", SqlDbType.Int, 0, "Materia2");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@Materia3", SqlDbType.Int, 0, "Materia3");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@IdGenealogia", SqlDbType.BigInt, 0, "IdGenealogia");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@ConsecIndx", SqlDbType.Int, 0, "ConsecIndx");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@idTCC", SqlDbType.SmallInt, 0, "idTCC");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@InfAnexos", SqlDbType.TinyInt, 0, "InfAnexos");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@LocAbr", SqlDbType.NVarChar, 0, "LocAbr");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@NumLetra", SqlDbType.TinyInt, 0, "NumLetra");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@ConsecLetra", SqlDbType.Int, 0, "ConsecLetra");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@Instancia", SqlDbType.SmallInt, 0, "Instancia");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@ConsecInst", SqlDbType.Int, 0, "ConsecInst");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@LocExp", SqlDbType.NText, 0, "LocExp");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@RIndx", SqlDbType.NText, 0, "RIndx");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@TIndx", SqlDbType.NText, 0, "TIndx");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@PIndx", SqlDbType.NText, 0, "PIndx");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@LIndx", SqlDbType.NText, 0, "LIndx");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@Certificada", SqlDbType.TinyInt, 0, "Certificada");
        ((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@IdSubVolumen", SqlDbType.Int, 0, "IdSubVolumen");


        dataAdapter.Update(dataSet, "Tesis");

        dataSet.Dispose();
        dataAdapter.Dispose();
        connectionEpsOle.Close();

Ta/Tj 列包含tinyint 值

这是 SqlException 消息

Line 1: Incorrect syntax near 'int'. Must declare the variable '@'.

Answer 1

您可以让列和变量处于它们不幸的状态。只需将传递给 OLE 兼容的参数名称更改为:

在 VALUES 列表中:使用 @TA_TJ 而不是 @[TA/TJ]

在参数列表中更改为:

((SqlDataAdapter)dataAdapter).InsertCommand.Parameters.Add("@TA_TJ", SqlDbType.Int, 0, "TA/TJ");