动态拼接sql语句

 private const string SQL_SELECT_ArchvID= "SELECT  @FiledNames FROM @TableName WHERE ArchvID=  @FiledValues ";

 

        /// <summary>
        /// 案卷录入字段集合
        /// </summary>
        public ArchvInputFields ArchvInputFields
        {
            get { return m_ArchvInputFields; }
            set { m_ArchvInputFields = value; }
        }

 

if (pFlagOK)

                {

                    //"INSERT INTO @TableName (LCDH,AJSXH,AJJS,LJR,JCR,WorkStatus,LRID) VALUES (@LCDH,@AJSXH,@AJJS)";

                    //拼接SQL语句中的字段名称字符串、字段值字符串
                    string filedStr = "DH,XM,XB,DWMC,JSFZH,SFZH,DACSNY,CJGZSJ,YLBXSC,";
                    string filedValues = "@ArchvID,";
                    for (int i = 0; i < pArchv.ArchvInputFields.Count; i++)
                    {
                        filedStr = filedStr + pArchv.ArchvInputFields[i].FieldName + ",";
                        filedValues = filedValues + "@" + pArchv.ArchvInputFields[i].FieldName + ",";
                    }

                    //去掉末尾的 ,
                    if (filedStr.Length > 0)
                    {
                        filedStr = filedStr.Substring(0, filedStr.Length - 1);
                        filedValues = filedValues.Substring(0, filedValues.Length - 1);
                    }

                    List<SqlParameter> parmsList = new List<SqlParameter>();

                    SqlParameter parm = null;

                    //parm = new SqlParameter(PARM_ArchvID, SqlDbType.Int);
                    //parm.Value = pID;
                    //parmsList.Add(parm);

                    for (int i = 0; i < pArchv.ArchvInputFields.Count; i++)
                    {
                        parm = new SqlParameter("@" + pArchv.ArchvInputFields[i].FieldName, GetFieldDBType(pArchv.ArchvInputFields[i].FieldType));
                        parm.Value = pArchv.ArchvInputFields[i].Value;
                        parmsList.Add(parm);
                    }

                    string sql = "";
                    sql = SQL_SELECT_ArchvID.Replace(PARM_TableName, m_DALX.ArchvTable).

                       Replace(PARM_FiledNames, filedStr).

                       Replace(PARM_FiledValues, filedValues).Replace(PARM_ArchvID, pID);
                 
                    IList<Archv> pArchvs = SqlHelper.ExecuteReader(ConnectionString, CommandType.Text, sql, parmsList, new         ArchvMapperForWF(m_DALX));

                    //返回查询结果
                    if (pArchvs.Count > 0)
                    {
                        pArchv.CloneFrom(pArchvs[0]);
                    }

                    //销毁局部对象
                    pArchvs = null;

                    parm = null;
                    parmsList.Clear();
                    parmsList = null;

                }