例如在向数据库添加新数据时,需要检测是否有重复
本例介绍如何把这个检测的过程放在存储过程中,并用程序调用检测的结果做出反应。
存储过程如下
CREATE PROCEDURE DInstitute_Insert
@InstituteNO nvarchar ( 6 ), @InstituteName nvarchar ( 40 )
AS
declare @return int , @count int
if ( ltrim ( rtrim ( @InstituteName )) = '' or ltrim ( rtrim ( @InstituteNO )) = '' )
select @return = 3 -- 返回3表示提交的数据有空值
else
begin
select @count = count ( 1 ) from DInstitute where InstituteNO = @InstituteNO
if ( @count > 0 )
select @return = 1 -- 返回1表示编号有重复
else
begin
insert into DInstitute (InstituteNO,InstituteName) values ( @InstituteNO , @InstituteName )
if ( @@error > 0 )
select @return = 2 -- 返回2表示数据操作错误
else
select @return = 0 -- 返回0表示数据操作成功
end
end
return @return
GO
本例介绍如何把这个检测的过程放在存储过程中,并用程序调用检测的结果做出反应。
存储过程如下
CREATE PROCEDURE DInstitute_Insert
@InstituteNO nvarchar ( 6 ), @InstituteName nvarchar ( 40 )
AS
declare @return int , @count int
if ( ltrim ( rtrim ( @InstituteName )) = '' or ltrim ( rtrim ( @InstituteNO )) = '' )
select @return = 3 -- 返回3表示提交的数据有空值
else
begin
select @count = count ( 1 ) from DInstitute where InstituteNO = @InstituteNO
if ( @count > 0 )
select @return = 1 -- 返回1表示编号有重复
else
begin
insert into DInstitute (InstituteNO,InstituteName) values ( @InstituteNO , @InstituteName )
if ( @@error > 0 )
select @return = 2 -- 返回2表示数据操作错误
else
select @return = 0 -- 返回0表示数据操作成功
end
end
return @return
GO
其中DInstitute 是一个学院信息表。只有InstituteNO(学院编号)、InstituteName(学院名称)两个字段。
在C#中调用本存储过程的代码如下:
//
执行插入操作
SqlCommand com1 = new SqlCommand("DInstitute_Insert", DBcon);
if (com1.Connection.State == ConnectionState.Closed)
com1.Connection. Open ();
com1.CommandType = CommandType.StoredProcedure;
com1.Parameters. Add (new SqlParameter(" @InstituteNO ",SqlDbType. NVarChar , 6 ));
com1.Parameters. Add (new SqlParameter(" @InstituteName ", SqlDbType. NVarChar , 40 ));
com1.Parameters. Add (new SqlParameter(" @return ", SqlDbType. Int ));
com1.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
com1.Parameters [ "@InstituteNO" ] .Value = t_NO. Text ;
com1.Parameters [ "@InstituteName" ] .Value = t_name. Text ;
try
{
com1.ExecuteScalar();
}
catch(SqlException ee)
{
DB.msgbox("操作失败!" + ee.Message.ToString());
return ;
}
finally
{
com1.Connection. Close ();
}
string temp = com1.Parameters [ "@return" ] .Value.ToString();
// 返回0表示数据操作成功
// 返回1表示编号有重复
// 返回2表示数据操作错误
// 返回3表示提交的数据有空值
switch ( temp )
{
case " 0 ":
DB.msgbox("添加成功!");
break ;
case " 1 ":
DB.msgbox("编号有重复!");
break ;
case " 2 ":
DB.msgbox("数据操作错误!");
break ;
case " 3 ":
DB.msgbox("提交的数据有空值!");
break ;
}
Binding(); // 刷新datagrid
SqlCommand com1 = new SqlCommand("DInstitute_Insert", DBcon);
if (com1.Connection.State == ConnectionState.Closed)
com1.Connection. Open ();
com1.CommandType = CommandType.StoredProcedure;
com1.Parameters. Add (new SqlParameter(" @InstituteNO ",SqlDbType. NVarChar , 6 ));
com1.Parameters. Add (new SqlParameter(" @InstituteName ", SqlDbType. NVarChar , 40 ));
com1.Parameters. Add (new SqlParameter(" @return ", SqlDbType. Int ));
com1.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
com1.Parameters [ "@InstituteNO" ] .Value = t_NO. Text ;
com1.Parameters [ "@InstituteName" ] .Value = t_name. Text ;
try
{
com1.ExecuteScalar();
}
catch(SqlException ee)
{
DB.msgbox("操作失败!" + ee.Message.ToString());
return ;
}
finally
{
com1.Connection. Close ();
}
string temp = com1.Parameters [ "@return" ] .Value.ToString();
// 返回0表示数据操作成功
// 返回1表示编号有重复
// 返回2表示数据操作错误
// 返回3表示提交的数据有空值
switch ( temp )
{
case " 0 ":
DB.msgbox("添加成功!");
break ;
case " 1 ":
DB.msgbox("编号有重复!");
break ;
case " 2 ":
DB.msgbox("数据操作错误!");
break ;
case " 3 ":
DB.msgbox("提交的数据有空值!");
break ;
}
Binding(); // 刷新datagrid
---------------------------------------------- 补充其它方法。
System.Data.SqlClient.SqlConnection scon
=
new
System.Data.SqlClient.SqlConnection(
"
server=Netangel;uid=etopsUS;pwd=etops;database=etops
"
);
System.Data.SqlClient.SqlCommand scom = new System.Data.SqlClient.SqlCommand( "" ,scon);
scon.Open();
scom.CommandText = " p_CorpClassAdd " ;
scom.CommandType = System.Data.CommandType.StoredProcedure;
scom.Parameters.Add(
new SqlParameter( " ReturnValue " , SqlDbType.Int, 4 ,
ParameterDirection.ReturnValue, false , 0 , 0 ,
string .Empty, DataRowVersion.Default, null )) // 增加存储过程的返回值参数
scom.ExecuteNonQuery();
Response.Write(scom.Parameters[ " ReturnValue " ].Value.ToString()); // 输出存储过程
System.Data.SqlClient.SqlCommand scom = new System.Data.SqlClient.SqlCommand( "" ,scon);
scon.Open();
scom.CommandText = " p_CorpClassAdd " ;
scom.CommandType = System.Data.CommandType.StoredProcedure;
scom.Parameters.Add(
new SqlParameter( " ReturnValue " , SqlDbType.Int, 4 ,
ParameterDirection.ReturnValue, false , 0 , 0 ,
string .Empty, DataRowVersion.Default, null )) // 增加存储过程的返回值参数
scom.ExecuteNonQuery();
Response.Write(scom.Parameters[ " ReturnValue " ].Value.ToString()); // 输出存储过程
cmd
=
New SqlCommand(
"
sp_del_office_byID
"
, conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add( " @OfficeID " , SqlDbType.Int)
cmd.Parameters( " @OfficeID " ).Value = officeid_pk
cmd.Parameters.Add( " s " , SqlDbType.Int)
cmd.Parameters( " s " ).Direction = ParameterDirection.ReturnValue
cmd.ExecuteNonQuery()
s = cmd.Parameters( " s " ).Value
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add( " @OfficeID " , SqlDbType.Int)
cmd.Parameters( " @OfficeID " ).Value = officeid_pk
cmd.Parameters.Add( " s " , SqlDbType.Int)
cmd.Parameters( " s " ).Direction = ParameterDirection.ReturnValue
cmd.ExecuteNonQuery()
s = cmd.Parameters( " s " ).Value
通用的,输入输出参数各1个,调用时只要传入参数和存储过程名即可返回结果
//
程序部分
public bool Execute( string Procname,SqlParameter []parameter, out string procout)
{
SqlConnection Sqlcon = new SqlConnection(Configuration.ConnectionString);
SqlCommand Sqlcom = new SqlCommand();
Sqlcon.Open();
Sqlcom.Connection = Sqlcon;
Sqlcom.CommandText = Procname;
Sqlcom.CommandType = CommandType.StoredProcedure;
Sqlcom.Parameters.Add( new SqlParameter( " @m_return " ,SqlDbType.Int));
for ( int i = 0 ;i < parameter.Length;i ++ )
Sqlcom.Parameters.Add(parameter[i]);
Sqlcom.Parameters[ " @m_return " ].Direction = ParameterDirection.ReturnValue;
Sqlcom.ExecuteNonQuery();
procout = Sqlcom.Parameters[ " @m_return " ].Value.ToString();
return true ;
}
private void button2_Click( object sender, System.EventArgs e)
{
SqlParameter[] parameter = { new SqlParameter( " @ID " ,SqlDbType.Int)};
parameter[ 0 ].Value = 1 ;
string UC;
bool retVal = Execute( " 管理员_CheckExist " ,parameter, out UC);
button2.Text = UC.ToString();
}
// 存储过程部分
ALTER PROCEDURE dbo.管理员_CheckExist
@ID int
AS
declare @matchCount int
select @matchCount = count( * ) from 管理员 where ID = @ID
return @matchCount
public bool Execute( string Procname,SqlParameter []parameter, out string procout)
{
SqlConnection Sqlcon = new SqlConnection(Configuration.ConnectionString);
SqlCommand Sqlcom = new SqlCommand();
Sqlcon.Open();
Sqlcom.Connection = Sqlcon;
Sqlcom.CommandText = Procname;
Sqlcom.CommandType = CommandType.StoredProcedure;
Sqlcom.Parameters.Add( new SqlParameter( " @m_return " ,SqlDbType.Int));
for ( int i = 0 ;i < parameter.Length;i ++ )
Sqlcom.Parameters.Add(parameter[i]);
Sqlcom.Parameters[ " @m_return " ].Direction = ParameterDirection.ReturnValue;
Sqlcom.ExecuteNonQuery();
procout = Sqlcom.Parameters[ " @m_return " ].Value.ToString();
return true ;
}
private void button2_Click( object sender, System.EventArgs e)
{
SqlParameter[] parameter = { new SqlParameter( " @ID " ,SqlDbType.Int)};
parameter[ 0 ].Value = 1 ;
string UC;
bool retVal = Execute( " 管理员_CheckExist " ,parameter, out UC);
button2.Text = UC.ToString();
}
// 存储过程部分
ALTER PROCEDURE dbo.管理员_CheckExist
@ID int
AS
declare @matchCount int
select @matchCount = count( * ) from 管理员 where ID = @ID
return @matchCount