insert前先check数据库是否存在此数据: string sql="select count(1) from T where Field1=3"; ExectueScale()方法 如果返回值 >0 则不能insert 反之则可insert
when case还是什么来着忘了.看下文档
/// <summary> /// 判断添加的角色名称是否重复 /// </summary> /// <param name="name">角色名称</param> /// <returns></returns> public bool Exist(string name) { AdoHelper helper = AdoHelper.CreateHelper(); string strsql = "select count(*) from t_role where name='"+name+"'"; int i =int.Parse( helper.ExecuteScalar(strsql).ToString()); if (i > 0) { return true; } else { return false; } }
用case when 例子:SELECT grade, COUNT (CASE WHEN sex = 1 THEN 1 /*sex 1为男生,2位女生*/ ELSE NULL END) 男生数, COUNT (CASE WHEN sex = 2 THEN 1 ELSE NULL END) 女生数 FROM students GROUP BY grade; 本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/lanmao100/archive/2010/06/03/5644266.aspx
select count(*) from tb where sys_id=@id .. int i=(int)cmd.ExecuteScalar() 或if not exists ... insert
CREATE PROCEDURE [DBO].[InsertTest] @UserName varchar(20), @IsHave int output AS if exists(Select ID From tb Where UserName = @UserName) set @IsHave = 0 --存在 else
Go
一个注册的示例CREATE PROCEDURE RegPro @Uname varchar(20),@Pwd varchar(30),@IsInclude int output AS if(exists(select * from [user] Where UserName=@Uname)) set @IsInclude = 0 --存在 else begin set @IsInclude = 1 --不存在 insert into [user](UserName,[PassWord])values(@Uname,@Pwd) end GO conn.Open(); SqlCommand comm = new SqlCommand("RegPro", conn); comm.CommandType = CommandType.StoredProcedure;//声明为存储过程 //声明并添加参数 SqlParameter UserName = new SqlParameter("@Uname", SqlDbType.VarChar); SqlParameter PassWord = new SqlParameter("@Pwd", SqlDbType.VarChar); SqlParameter IsInclude = new SqlParameter("@IsInclude", SqlDbType.Int); UserName.Value = TextBox1.Text; PassWord.Value = TextBox2.Text; comm.Parameters.Add(UserName); comm.Parameters.Add(PassWord); comm.Parameters.Add(IsInclude); //声明为出参 IsInclude.Direction = ParameterDirection.Output; comm.ExecuteNonQuery(); if (int.Parse(IsInclude.Value.ToString()) == 0) { Response.Write("已经存在此用户!"); } else { Response.Write("注册成功!"); } conn.Close();
要在sql中判断的话 存储过程方便点 不过我觉得还是在后台判断比较好的
在存储过程里面判断Create Proc IsExists ( @Value1 nvarchar(200) ) begin if not exists( select * from table1 where 字段=@Value1 ) begin insert into table values(@Value1 ) end end这是目前最便捷的方法了
string sql="select count(1) from T where Field1=3";
ExectueScale()方法
如果返回值 >0 则不能insert
反之则可insert
/// 判断添加的角色名称是否重复
/// </summary>
/// <param name="name">角色名称</param>
/// <returns></returns>
public bool Exist(string name)
{
AdoHelper helper = AdoHelper.CreateHelper();
string strsql = "select count(*) from t_role where name='"+name+"'";
int i =int.Parse( helper.ExecuteScalar(strsql).ToString());
if (i > 0)
{
return true;
}
else
{
return false;
}
}
例子:SELECT grade, COUNT (CASE WHEN sex = 1 THEN 1 /*sex 1为男生,2位女生*/
ELSE NULL
END) 男生数,
COUNT (CASE WHEN sex = 2 THEN 1
ELSE NULL
END) 女生数
FROM students GROUP BY grade;
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/lanmao100/archive/2010/06/03/5644266.aspx
..
int i=(int)cmd.ExecuteScalar()
或if not exists ...
insert
@UserName varchar(20),
@IsHave int output
AS
if exists(Select ID From tb Where UserName = @UserName)
set @IsHave = 0 --存在
else
Go
@Uname varchar(20),@Pwd varchar(30),@IsInclude int output
AS
if(exists(select * from [user] Where UserName=@Uname))
set @IsInclude = 0 --存在
else
begin
set @IsInclude = 1 --不存在
insert into [user](UserName,[PassWord])values(@Uname,@Pwd)
end
GO
conn.Open();
SqlCommand comm = new SqlCommand("RegPro", conn);
comm.CommandType = CommandType.StoredProcedure;//声明为存储过程
//声明并添加参数
SqlParameter UserName = new SqlParameter("@Uname", SqlDbType.VarChar);
SqlParameter PassWord = new SqlParameter("@Pwd", SqlDbType.VarChar);
SqlParameter IsInclude = new SqlParameter("@IsInclude", SqlDbType.Int);
UserName.Value = TextBox1.Text;
PassWord.Value = TextBox2.Text;
comm.Parameters.Add(UserName);
comm.Parameters.Add(PassWord);
comm.Parameters.Add(IsInclude);
//声明为出参
IsInclude.Direction = ParameterDirection.Output;
comm.ExecuteNonQuery();
if (int.Parse(IsInclude.Value.ToString()) == 0)
{
Response.Write("已经存在此用户!");
}
else
{
Response.Write("注册成功!");
}
conn.Close();
在存储过程里面判断Create Proc IsExists
(
@Value1 nvarchar(200)
)
begin
if not exists( select * from table1 where 字段=@Value1 )
begin
insert into table values(@Value1 )
end
end这是目前最便捷的方法了