asp.net種使用c#, 向coredb.mybbs表中插入記錄值(title, content)【文章的標(biāo)題和內(nèi)容】，由于content, title中可能包含單引號(hào)，直接使用sql的insert命令會(huì)報(bào)錯(cuò)，對(duì)此有兩種處理方法，一種將單引號(hào)替換成兩個(gè)單引號(hào)，第2種方法是使用存儲(chǔ)過(guò)程。
表mybbs的格式定義如下：
create table [dbo].[mybbs] (
[id] [bigint] identity (1, 1) not null ,
[title] [char] (160) collate chinese_prc_ci_as null ,
[author] [char] (20) collate chinese_prc_ci_as null ,
[date_of_created] [datetime] null ,
[abstract] [char] (480) collate chinese_prc_ci_as null ,
[content] [ntext] collate chinese_prc_ci_as not null
) on [primary] textimage_on [primary]
1、將單引號(hào)用兩個(gè)單引號(hào)替換：
sqlconnection coredb=new sqlconnection();
coredb.connectionstring= "workstation id=/"gqa-eric-lv/";packet size=4096;integrated security=sspi;" +
"data source=/"gqa-eric-lv/";persist security info=false;initial catalog=coredb";
//單引號(hào)用"''"替換，以插入'到sql server中；
string title=textbox1.text.replace("'","''");
string content=textbox2.text.replace("'","''");
if(title.trim()==""||content.trim()=="")return;
string insertcmd [email protected]"insert into mybbs (title,content) values('"+ title + "','" +content+"')";
sqlcommand mycommand = new sqlcommand(insertcmd,coredb);
coredb.open();
sqldatareader myreader = mycommand.executereader();
myreader.close();
coredb.close();
2、使用存儲(chǔ)過(guò)程來(lái)插入
1) 創(chuàng)建存儲(chǔ)過(guò)程：
create proc insertmybbsproc(@title char(160), @author char(20), @content ntext)
as
insert into mybbs(title,author,content) values(@title, @author, @content)
2) 查詢(xún)分析器中測(cè)試存儲(chǔ)過(guò)程：
declare @title char(160)
declare @author char(20)
declare @content char(600)
set @title='test title 3'
set @author='david euler 3'
set @content='it is the content 3'
exec insertmybbsproc @title, @author, @content
3) c#中通過(guò)sqlcommand執(zhí)行存儲(chǔ)過(guò)程：
sqlconnection coredb=new sqlconnection();
coredb.connectionstring= "workstation id=/"gqa-eric-lv/";packet size=4096;integrated security=sspi;" +
"data source=/"gqa-eric-lv/";persist security info=false;initial catalog=coredb";
string title=textbox1.text;
string content=textbox2.text;
if(title.trim()==""||content.trim()=="")return;
//insertmybbsproc是向mybbs中插入數(shù)據(jù)的procedure：
sqlcommand insertcmd = new sqlcommand("insertmybbsproc",coredb);
insertcmd.commandtype=commandtype.storedprocedure;//命令類(lèi)型為存儲(chǔ)過(guò)程;下面定義參數(shù)對(duì)象：
sqlparameter prm1=new sqlparameter("@title", sqldbtype.char,160);
sqlparameter prm2=new sqlparameter("@author", sqldbtype.char,20);
sqlparameter prm3=new sqlparameter("@content",sqldbtype.ntext,1073741823);
prm1.direction=parameterdirection.input;
prm2.direction=parameterdirection.input;
prm3.direction=parameterdirection.input;
//為insertcmd添加sql參數(shù)：
insertcmd.parameters.add(prm1);
insertcmd.parameters.add(prm2);
insertcmd.parameters.add(prm3);
//為sql參數(shù)賦值：
prm1.value=title;
prm2.value="david euler";
prm3.value=content;
coredb.open();
int recordsaffected=insertcmd.executenonquery();
if(recordsaffected==1)response.write("<script>alert('"+ "插入成功" +"');</script>");
coredb.close();
