插入记录时单引号的处理

由于Content,Title中可能包含单引号,直接使用sql的insert命令会报错,对此有两种C#
强烈推介IDEA2020.2破解激活,IntelliJ IDEA 注册码,2020.2 IDEA 激活码

由于Content, Title中可能包含单引号,直接使用sql的insert命令会报错,对此有两种处理方法,一种将单引号替换成两个单引号,第2种方法是使用存储过程。

表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、将单引号用两个单引号替换:

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";

//单引号用"''"替换,以插入'到SQL Server中;

string Title=TextBox1.Text.Replace("'","''");

string Content=TextBox2.Text.Replace("'","''");

if(Title.Trim()==""||Content.Trim()=="")return;

string insertCMD =@"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、使用存储过程来插入

1) 创建存储过程:

Create proc InsertMyBBSProc(@Title char(160), @Author char(20), @Content ntext)

AS

Insert into myBBS(Title,Author,Content) Values(@Title, @Author, @Content)

2) 查询分析器中测试存储过程:

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#中通过SqlCommand执行存储过程:

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中插入数据的Procedure:

SqlCommand insertCMD = new SqlCommand("InsertMyBBSProc",coreDB);

insertCMD.CommandType=CommandType.StoredProcedure;//命令类型为存储过程;下面定义参数对象:

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参数:

insertCMD.Parameters.Add(prm1);

insertCMD.Parameters.Add(prm2);

insertCMD.Parameters.Add(prm3);

//为SQL参数赋值:

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(); 

 

本文来源21aspnet,由javajgs_com转载发布,观点不代表Java架构师必看的立场,转载请标明来源出处:https://javajgs.com/archives/2859

发表评论