C#資料庫操作特殊字元單引號三種處理方式

方法一：逸出字元

使用單引號作為逸出字元，即連續使用兩個單引號。

 

select * from jq_jjjl where bt like '%女子''%'

上述代碼會匹配jq_jjjl表中所有bt欄位包含
女子'的記錄。（注意單引號）

 

方法二：SqlDataAdapter

 

string constr = "Server=" + DBConfig.DBAPP_IP + ";user id=" + DBConfig.DBAPP_USER + ";password=" + DBConfig.DBAPP_PASSWD + ";Database=" + DBConfig.DBAPP_DBNAME + ";Connect Timeout=30";string cmdstr = "SELECT * FROM WIRELESS_POLICE_T";// Create the adapter with the selectCommand txt and the connection stringSqlDataAdapter adapter = new SqlDataAdapter(cmdstr, constr);// Create the builder for the adapter to automatically generate the Command when neededSqlCommandBuilder builder = new SqlCommandBuilder(adapter);// Create and fill the DataSet using the WIRELESS_POLICE_TDataSet dataset = new DataSet();adapter.Fill(dataset, "WIRELESS_POLICE_T");// Get the WIRELESS_POLICE_T table from the datasetDataTable table = dataset.Tables["WIRELESS_POLICE_T"];// Indicate DataColumn WLPid is unique, This is required by the SqlCommandBuilder to update the WIRELESS_POLICE_T tabletable.Columns["WLPid"].Unique = true;// New row from the WIRELESS_POLICE_T tableDataRow row = table.NewRow();// Update a column//row["xxx"] = xxx;// 你的指派陳述式// Now update the WIRELESS_POLICE_T using the adapter// The OracleCommandBuilder will create the UpdateCommand for the adapter to update the WIRELESS_POLICE_T tableadapter.Update(dataset, "WIRELESS_POLICE_T");

方法三：構造SQL語句（類似java中的PreparedStatement）

 

 

            string constr = "Server=" + DBConfig.DBAPP_IP + ";user id=" + DBConfig.DBAPP_USER + ";password=" + DBConfig.DBAPP_PASSWD + ";Database=" + DBConfig.DBAPP_DBNAME + ";Connect Timeout=30";            SqlConnection conn = new SqlConnection(constr);            // 此處可能存在sql語句中含有單引號的問題            /**            string cmdstr = "update WIRELESS_PERSON_T set PersonName='"+person.getPersonName()                +"', PersonSex='"+person.getPersonSex()+"', YID='"+person.getYID()                +"', caseinfoid='"+person.getCaseinfoid()+"', Kind='"+person.getKind()                +"', caseremark='"+person.getCaseremark()+"', ArrivalKind='"+person.getArrivalKind()                +"' where personId="+person.getPersonId();             * */            string cmdstr = "update WIRELESS_PERSON_T set PersonName=@PersonName, PersonSex='" + person.getPersonSex()                 + "', YID=@YID, caseinfoid='" + person.getCaseinfoid() + "', Kind='" + person.getKind()                + "', caseremark=@Caseremark, ArrivalKind='" + person.getArrivalKind() + "' where PersonId=" + person.getPersonId();            Console.WriteLine(cmdstr);            //SqlCommand command = new SqlCommand(cmdstr, conn);            SqlCommand command = conn.CreateCommand();            command.CommandText = cmdstr;            command.Parameters.Add(new SqlParameter("PersonName", person.getPersonName()));            command.Parameters.Add(new SqlParameter("YID", person.getYID()));            command.Parameters.Add(new SqlParameter("Caseremark", person.getCaseremark()));            try            {                conn.Open();                command.ExecuteNonQuery();                Console.WriteLine("儲存資訊成功！");            }            catch (Exception e2)            {                MessageBox.Show("儲存出錯！" + e2.Message);                return;            }            finally            {                conn.Close();            }

上述代碼中person為一個對象執行個體。