现在我要用.net(C#)实现一个查询功能,其实查询很简单,但是现在因为要涉及调用sqlserver中的别人写好的存储过程,我就傻眼啦?
我的要求是页面中一个textbox(输入查询信息),一个button(查询)
然后.cs文件中执行查询,整个代码不知道怎样写?
调用了存储过程就不用连接数据库了么?诚恳赐教代码……存储过程如下:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOALTER PROCEDURE CXYH(
@USER_NUM VARCHAR(20),
@FALG VARCHAR(10)
)
AS
BEGIN
DECLARE @FLAG VARCHAR(10)
DECLARE @TEMP VARCHAR(500)
DECLARE @TEMP1 VARCHAR(200)
DECLARE @TEMP2 VARCHAR(200)
DECLARE @CW_STATUS VARCHAR(20)
DECLARE @CW_ORDERDATE DATETIME
DECLARE @CW_ORDERDATE_O VARCHAR(20)
DECLARE @TD_DATE DATETIME
DECLARE @TD_DATE_O VARCHAR(20)
DECLARE @DATE DATETIME
DECLARE @DATE_O VARCHAR(20)
DECLARE @DELIVER VARCHAR(140)
DECLARE @STATUS_ VARCHAR(20)
DECLARE @NUM VARCHAR(10)
DECLARE @@DESTNATION_ID VARCHAR(10)
DECLARE @MSG_LENGHT VARCHAR(10)
DECLARE @MSG_CONTENT VARCHAR(140)
DECLARE @SERVICE_ID VARCHAR(10)
DECLARE @CMPP_SUBMIT VARCHAR(100)
DECLARE @HOST_NAME CHAR(20) SELECT @NUM = SUBSTRING(@USER_NUM,11,1)
SELECT @HOST_NAME = 'PC:' + HOST_NAME()
IF @FALG = 'MF'
IF NOT EXISTS (SELECT * FROM FREE_NUM WHERE USER_NUM = @USER_NUM)
BEGIN
INSERT INTO FREE_NUM(USER_NUM,WAY)
VALUES(@USER_NUM,@HOST_NAME)
SELECT 执行结果 = '免费用户添加成功!'
END
ELSE
SELECT 执行结果 = '此用户已为免费用户!' ELSE IF @FALG = 'TD'
IF EXISTS (SELECT * FROM FSNL_MOBILE_MEM WHERE MOBILE_MEM=@USER_NUM AND CW_STATUS !=1)
BEGIN
EXEC KBM03000 @USER_NUM,'03000','03000','00000','',0
INSERT INTO TD_NUM(USER_NUM,WAY)
VALUES(@USER_NUM,@HOST_NAME)
SELECT 执行结果 = '用户退定成功!'
END
ELSE
SELECT 执行结果 = '无此用户,或用户已退定!' ELSE IF @FALG = 'MG'
IF NOT EXISTS (SELECT * FROM FILTER_INFO WHERE KEY_VALUE = @USER_NUM)
BEGIN
INSERT INTO FILTER_INFO(KEY_VALUE,WAY)
VALUES(@USER_NUM,@HOST_NAME)
SELECT 执行结果 = '敏感用户添加成功!'
END
ELSE
SELECT 执行结果 = '此用户已为敏感用户!'
ELSE IF @FALG = 'SC'
IF EXISTS (SELECT * FROM FSNL_MOBILE_MEM WHERE MOBILE_MEM=@USER_NUM)
BEGIN
DELETE FSNL_MOBILE_MEM WHERE MOBILE_MEM=@USER_NUM
DELETE CWQR_SQ WHERE LY_NUM=@USER_NUM
DELETE CWQR_SQ WHERE SQ_NUM=@USER_NUM
INSERT INTO SC_NUM(USER_NUM,WAY)
VALUES(@USER_NUM,@HOST_NAME)
SELECT 执行结果 = '用户删除成功!'
END
ELSE
SELECT 执行结果 = '无此用户,或用户已删除!'
ELSE IF @FALG = 'CX'
BEGIN
IF EXISTS (SELECT* FROM FSNL_MOBILE_MEM WHERE MOBILE_MEM=@USER_NUM) --用户存在
BEGIN
SELECT TOP 1 @DATE=CUR_TIME,
@DELIVER=MSG_CONTENT
FROM CMPP_DELIVER
WHERE SRCTERMINAL_ID=@USER_NUM --order by cur_time desc
SELECT @STATUS_=CW_STATUS,@TD_DATE = CWQR_DATE, @CW_ORDERDATE = CW_ORDERDATE FROM FSNL_MOBILE_MEM WHERE MOBILE_MEM = @USER_NUM
IF @STATUS_ =1
SELECT @CW_STATUS = '用户已退定'
ELSE
SELECT @CW_STATUS = '用户还未退定'
SELECT @DATE_O = CONVERT(VARCHAR(20),@DATE,(20))
SELECT @TD_DATE_O = CONVERT(VARCHAR(20),@TD_DATE,(20))
IF @TD_DATE_O IS NULL PRINT 3
SELECT @TEMP='用户:'+@USER_NUM+char(13)+char(13)+char(13)+'订制时间:'+@DATE_O+char(13)+char(13)+char(13)+'上行内容:'+@DELIVER+char(13)+char(13)+char(13)+'状态:'+@CW_STATUS+char(13)+char(13)+char(13)+
'退定时间:'+ @TD_DATE_O PRINT @TEMP
SELECT MOBILE_MEM,
CW_ORDERDATE,
CWQR_DATE,
STATUS_,
CW_STATUS
FROM FSNL_MOBILE_MEM
WHERE MOBILE_MEM = @USER_NUM SELECT DESTNATION_ID,
MSG_LENGTH,
MSG_CONTENT,
CUR_TIME
FROM CMPP_DELIVER
WHERE SRCTERMINAL_ID = @USER_NUM ORDER BY CUR_TIME IF @NUM = 0 SELECT SERVICE_ID,
FEECODE,
SRC_TERMINAL_ID,
MSG_CONTENT,
CUR_TIME
FROM CMPP_SUBMIT_0
WHERE DEST_TERMINAL_ID = @USER_NUM ORDER BY CUR_TIME ELSE IF @NUM = 1
SELECT SERVICE_ID,
FEECODE,
SRC_TERMINAL_ID,
MSG_CONTENT,
CUR_TIME
FROM CMPP_SUBMIT_1
WHERE DEST_TERMINAL_ID = @USER_NUM ORDER BY CUR_TIME
ELSE IF @NUM = 2
SELECT SERVICE_ID,
FEECODE,
SRC_TERMINAL_ID,
MSG_CONTENT,
CUR_TIME
FROM CMPP_SUBMIT_2
WHERE DEST_TERMINAL_ID = @USER_NUM ORDER BY CUR_TIME
ELSE IF @NUM = 3
SELECT SERVICE_ID,
FEECODE,
SRC_TERMINAL_ID,
MSG_CONTENT,
CUR_TIME
FROM CMPP_SUBMIT_3
WHERE DEST_TERMINAL_ID = @USER_NUM ORDER BY CUR_TIME
ELSE IF @NUM = 4
SELECT SERVICE_ID,
FEECODE,
SRC_TERMINAL_ID,
MSG_CONTENT,
CUR_TIME
FROM CMPP_SUBMIT_4
WHERE DEST_TERMINAL_ID = @USER_NUM ORDER BY CUR_TIME
ELSE IF @NUM = 5
SELECT SERVICE_ID,
FEECODE,
SRC_TERMINAL_ID,
MSG_CONTENT,
CUR_TIME
FROM CMPP_SUBMIT_5
WHERE DEST_TERMINAL_ID = @USER_NUM ORDER BY CUR_TIME
ELSE IF @NUM = 6
SELECT SERVICE_ID,
FEECODE,
SRC_TERMINAL_ID,
MSG_CONTENT,
CUR_TIME
FROM CMPP_SUBMIT_6
WHERE DEST_TERMINAL_ID = @USER_NUM ORDER BY CUR_TIME
ELSE IF @NUM = 7
SELECT SERVICE_ID,
FEECODE,
SRC_TERMINAL_ID,
MSG_CONTENT,
CUR_TIME
FROM CMPP_SUBMIT_7
WHERE DEST_TERMINAL_ID = @USER_NUM ORDER BY CUR_TIME
ELSE IF @NUM = 8
SELECT SERVICE_ID,
FEECODE,
SRC_TERMINAL_ID,
MSG_CONTENT,
CUR_TIME
FROM CMPP_SUBMIT_8
WHERE DEST_TERMINAL_ID = @USER_NUM ORDER BY CUR_TIME
ELSE IF @NUM = 9
SELECT SERVICE_ID,
FEECODE,
SRC_TERMINAL_ID,
MSG_CONTENT,
CUR_TIME
FROM CMPP_SUBMIT_9
WHERE DEST_TERMINAL_ID = @USER_NUM ORDER BY CUR_TIME
END
ELSE --用户不存在
BEGIN
SELECT 运行结果 = '无此用户'
SELECT * FROM FSNL_MOBILE_MEM WHERE MOBILE_MEM = @USER_NUM
END
ENDEND
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
我的要求是页面中一个textbox(输入查询信息),一个button(查询)
然后.cs文件中执行查询,整个代码不知道怎样写?
调用了存储过程就不用连接数据库了么?诚恳赐教代码……存储过程如下:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOALTER PROCEDURE CXYH(
@USER_NUM VARCHAR(20),
@FALG VARCHAR(10)
)
AS
BEGIN
DECLARE @FLAG VARCHAR(10)
DECLARE @TEMP VARCHAR(500)
DECLARE @TEMP1 VARCHAR(200)
DECLARE @TEMP2 VARCHAR(200)
DECLARE @CW_STATUS VARCHAR(20)
DECLARE @CW_ORDERDATE DATETIME
DECLARE @CW_ORDERDATE_O VARCHAR(20)
DECLARE @TD_DATE DATETIME
DECLARE @TD_DATE_O VARCHAR(20)
DECLARE @DATE DATETIME
DECLARE @DATE_O VARCHAR(20)
DECLARE @DELIVER VARCHAR(140)
DECLARE @STATUS_ VARCHAR(20)
DECLARE @NUM VARCHAR(10)
DECLARE @@DESTNATION_ID VARCHAR(10)
DECLARE @MSG_LENGHT VARCHAR(10)
DECLARE @MSG_CONTENT VARCHAR(140)
DECLARE @SERVICE_ID VARCHAR(10)
DECLARE @CMPP_SUBMIT VARCHAR(100)
DECLARE @HOST_NAME CHAR(20) SELECT @NUM = SUBSTRING(@USER_NUM,11,1)
SELECT @HOST_NAME = 'PC:' + HOST_NAME()
IF @FALG = 'MF'
IF NOT EXISTS (SELECT * FROM FREE_NUM WHERE USER_NUM = @USER_NUM)
BEGIN
INSERT INTO FREE_NUM(USER_NUM,WAY)
VALUES(@USER_NUM,@HOST_NAME)
SELECT 执行结果 = '免费用户添加成功!'
END
ELSE
SELECT 执行结果 = '此用户已为免费用户!' ELSE IF @FALG = 'TD'
IF EXISTS (SELECT * FROM FSNL_MOBILE_MEM WHERE MOBILE_MEM=@USER_NUM AND CW_STATUS !=1)
BEGIN
EXEC KBM03000 @USER_NUM,'03000','03000','00000','',0
INSERT INTO TD_NUM(USER_NUM,WAY)
VALUES(@USER_NUM,@HOST_NAME)
SELECT 执行结果 = '用户退定成功!'
END
ELSE
SELECT 执行结果 = '无此用户,或用户已退定!' ELSE IF @FALG = 'MG'
IF NOT EXISTS (SELECT * FROM FILTER_INFO WHERE KEY_VALUE = @USER_NUM)
BEGIN
INSERT INTO FILTER_INFO(KEY_VALUE,WAY)
VALUES(@USER_NUM,@HOST_NAME)
SELECT 执行结果 = '敏感用户添加成功!'
END
ELSE
SELECT 执行结果 = '此用户已为敏感用户!'
ELSE IF @FALG = 'SC'
IF EXISTS (SELECT * FROM FSNL_MOBILE_MEM WHERE MOBILE_MEM=@USER_NUM)
BEGIN
DELETE FSNL_MOBILE_MEM WHERE MOBILE_MEM=@USER_NUM
DELETE CWQR_SQ WHERE LY_NUM=@USER_NUM
DELETE CWQR_SQ WHERE SQ_NUM=@USER_NUM
INSERT INTO SC_NUM(USER_NUM,WAY)
VALUES(@USER_NUM,@HOST_NAME)
SELECT 执行结果 = '用户删除成功!'
END
ELSE
SELECT 执行结果 = '无此用户,或用户已删除!'
ELSE IF @FALG = 'CX'
BEGIN
IF EXISTS (SELECT* FROM FSNL_MOBILE_MEM WHERE MOBILE_MEM=@USER_NUM) --用户存在
BEGIN
SELECT TOP 1 @DATE=CUR_TIME,
@DELIVER=MSG_CONTENT
FROM CMPP_DELIVER
WHERE SRCTERMINAL_ID=@USER_NUM --order by cur_time desc
SELECT @STATUS_=CW_STATUS,@TD_DATE = CWQR_DATE, @CW_ORDERDATE = CW_ORDERDATE FROM FSNL_MOBILE_MEM WHERE MOBILE_MEM = @USER_NUM
IF @STATUS_ =1
SELECT @CW_STATUS = '用户已退定'
ELSE
SELECT @CW_STATUS = '用户还未退定'
SELECT @DATE_O = CONVERT(VARCHAR(20),@DATE,(20))
SELECT @TD_DATE_O = CONVERT(VARCHAR(20),@TD_DATE,(20))
IF @TD_DATE_O IS NULL PRINT 3
SELECT @TEMP='用户:'+@USER_NUM+char(13)+char(13)+char(13)+'订制时间:'+@DATE_O+char(13)+char(13)+char(13)+'上行内容:'+@DELIVER+char(13)+char(13)+char(13)+'状态:'+@CW_STATUS+char(13)+char(13)+char(13)+
'退定时间:'+ @TD_DATE_O PRINT @TEMP
SELECT MOBILE_MEM,
CW_ORDERDATE,
CWQR_DATE,
STATUS_,
CW_STATUS
FROM FSNL_MOBILE_MEM
WHERE MOBILE_MEM = @USER_NUM SELECT DESTNATION_ID,
MSG_LENGTH,
MSG_CONTENT,
CUR_TIME
FROM CMPP_DELIVER
WHERE SRCTERMINAL_ID = @USER_NUM ORDER BY CUR_TIME IF @NUM = 0 SELECT SERVICE_ID,
FEECODE,
SRC_TERMINAL_ID,
MSG_CONTENT,
CUR_TIME
FROM CMPP_SUBMIT_0
WHERE DEST_TERMINAL_ID = @USER_NUM ORDER BY CUR_TIME ELSE IF @NUM = 1
SELECT SERVICE_ID,
FEECODE,
SRC_TERMINAL_ID,
MSG_CONTENT,
CUR_TIME
FROM CMPP_SUBMIT_1
WHERE DEST_TERMINAL_ID = @USER_NUM ORDER BY CUR_TIME
ELSE IF @NUM = 2
SELECT SERVICE_ID,
FEECODE,
SRC_TERMINAL_ID,
MSG_CONTENT,
CUR_TIME
FROM CMPP_SUBMIT_2
WHERE DEST_TERMINAL_ID = @USER_NUM ORDER BY CUR_TIME
ELSE IF @NUM = 3
SELECT SERVICE_ID,
FEECODE,
SRC_TERMINAL_ID,
MSG_CONTENT,
CUR_TIME
FROM CMPP_SUBMIT_3
WHERE DEST_TERMINAL_ID = @USER_NUM ORDER BY CUR_TIME
ELSE IF @NUM = 4
SELECT SERVICE_ID,
FEECODE,
SRC_TERMINAL_ID,
MSG_CONTENT,
CUR_TIME
FROM CMPP_SUBMIT_4
WHERE DEST_TERMINAL_ID = @USER_NUM ORDER BY CUR_TIME
ELSE IF @NUM = 5
SELECT SERVICE_ID,
FEECODE,
SRC_TERMINAL_ID,
MSG_CONTENT,
CUR_TIME
FROM CMPP_SUBMIT_5
WHERE DEST_TERMINAL_ID = @USER_NUM ORDER BY CUR_TIME
ELSE IF @NUM = 6
SELECT SERVICE_ID,
FEECODE,
SRC_TERMINAL_ID,
MSG_CONTENT,
CUR_TIME
FROM CMPP_SUBMIT_6
WHERE DEST_TERMINAL_ID = @USER_NUM ORDER BY CUR_TIME
ELSE IF @NUM = 7
SELECT SERVICE_ID,
FEECODE,
SRC_TERMINAL_ID,
MSG_CONTENT,
CUR_TIME
FROM CMPP_SUBMIT_7
WHERE DEST_TERMINAL_ID = @USER_NUM ORDER BY CUR_TIME
ELSE IF @NUM = 8
SELECT SERVICE_ID,
FEECODE,
SRC_TERMINAL_ID,
MSG_CONTENT,
CUR_TIME
FROM CMPP_SUBMIT_8
WHERE DEST_TERMINAL_ID = @USER_NUM ORDER BY CUR_TIME
ELSE IF @NUM = 9
SELECT SERVICE_ID,
FEECODE,
SRC_TERMINAL_ID,
MSG_CONTENT,
CUR_TIME
FROM CMPP_SUBMIT_9
WHERE DEST_TERMINAL_ID = @USER_NUM ORDER BY CUR_TIME
END
ELSE --用户不存在
BEGIN
SELECT 运行结果 = '无此用户'
SELECT * FROM FSNL_MOBILE_MEM WHERE MOBILE_MEM = @USER_NUM
END
ENDEND
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CommandText设置为你的存储过程名称
CommandType设置为CommandType.StoredProcedure
其他没什么区别
datagrid的帮定查询结果都一样?
try
{
conn.open();
cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
Response.Write(ex.Message);
}
finally
{
conn.close();
}
cmd.Parameters.Add("@YourParamName",YourParamValue);
.....
然后就可以调用存储过程了
conn.open
cmd.CommandType.StoredProcedure
cmd = new SqlCommand("ProcedureName",conn);
SqlCommand cmd;con=new SqlConnection(connectionstring);
cmd=new SqlCommand(存储过程名,con);
cmd.CommandType=CommandType.StoredProcedure;
带参数的就加
cmd.Parametera.add("@name",SqlDbType.VarChar,10);
cmd.Parameters["@name"]="123";
//Direction设置参数类型,默认是输入的
cmd.Parameters["@name"].Direction=ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();注意参数的名称必须与存储过程的名称相同。。
你的存储过程太长他复杂了,没仔细看:)
改为cmd.Parameters["@name"].value="123";to : qiushuangqun(秋秋双飞)
cmd.Parameters["@name"].value=textbox1.Text;
就这样
private void Button1_Click(object sender, System.EventArgs e)
{
Application["strconn"]="Server=localhost;database=sms;uid=sa;pwd=123";
SqlConnection m_objConn=new SqlConnection(Application["strconn"].ToString());//连接数据库
SqlCommand m_objCmd = new SqlCommand("cxyh", m_objConn);
m_objConn.Open();
m_objCmd.Connection = m_objConn;
m_objCmd.CommandText = "cxyh";//存储过程名
m_objCmd.CommandType = CommandType.StoredProcedure;//定义为存储过程
m_objCmd.Parameters.Add(new SqlParameter("@cxyh",SqlDbType.Int)).Value = TextBox1.Text;//存储过程有一个外部变量
m_objCmd.Parameters["@cxyh"].Direction=ParameterDirection.ReturnValue;
m_objCmd.ExecuteNonQuery();
SqlDataReader result = m_objCmd.ExecuteReader(CommandBehavior.CloseConnection);
DataGrid1.DataSource=result.ToString();//设置datagrid的数据源不知道对不对?
DataGrid1.DataBind();
m_objConn.Close();
}
多谢!!!
“SELECT 执行结果”?
大哥们来看看呀?
(@newsid int)
as
delete from news where newsid=@newsid
go相应的asp.net中删除过程:
dim cn as new sqlconnection("server=xxx;database=xxx;uid=xxx;pwd=xxx")
cn.opendim cmd as new sqlcommand("news_del",cn)
cmd.contentType=CommandType.StoredProcedure
cmd.parameters.add(new sqlparameters("@news_id"),sqldatatype.int)
cmd.parameters("@newsid").values=newsid --获取要删除的newsid值
cmd.ExecuteNonQuery()cn.close()先把原理弄明白就好整了!
因为存储过程没有Output变量。在net里调用时没有得到变量的返回值。
最好把存储过程加个参数如下:
ALTER PROCEDURE CXYH(
@USER_NUM VARCHAR(20),
@FALG VARCHAR(10),
------------------------
@ReturnVal VARCHAR(100) OUTPUT
)
然后在返回的结果中把 “SELECT 执行结果”的值用
“SET @ReturnVal = '结果'”来获得值。这样比较规范。
在你的代码中要得到这个返回值,可以如下:
1、加个参数
m_objCmd.Parameters.Add(new SqlParameter("@ReturnVal",SqlDbType.VarChar, 100)).Direction=ParameterDirection.ReturnValue;
2、获得值
string strValue = m_objCmd.Parameters["@ReturnVal"].Value;
以上
直接数据源设成
m_objCmd.ExecuteReader()
不就得了吗?呵呵,让楼上几位给讲糊涂了!
现在把思路理一下,高手们看看对不对?
我现在的存储过程有三个外部变量,一个输入手机号码@USER_NUM,一个选择查询类型:@FALG
另外一个@ReturnVal 用来返回结果
在web页中
我用一个RadioButtonList1供选择查询类型
一个TextBox1供输入手机号码
一个Button1来进行查询
一个Label1用来显示返回值
然后后台代码如下:
private void Button1_Click(object sender, System.EventArgs e)
{
Application["strconn"]="Server=localhost;database=sms;uid=sa;pwd=123";
SqlConnection m_objConn=new SqlConnection(Application["strconn"].ToString());//连接数据库
SqlCommand m_objCmd = new SqlCommand("cxyh", m_objConn);
m_objConn.Open();
m_objCmd.Connection = m_objConn;
m_objCmd.CommandText = "cxyh";//存储过程名
m_objCmd.CommandType = CommandType.StoredProcedure;//定义为存储过程
m_objCmd.Parameters.Add(new SqlParameter("@cxyh",SqlDbType.Int)).Value = TextBox1.Text;//存储过程的第一个外部变量
m_objCmd.Parameters.Add(new SqlParameter("@FALG",SqlDbType.Int)).Value = RadioButtonList1.SelectedValue;//存储过程的第二个外部变量
m_objCmd.Parameters.Add(new SqlParameter("@ReturnVal",SqlDbType.VarChar, 100)).Direction=ParameterDirection.ReturnValue;//;//存储过程的返回值变量
string strValue = m_objCmd.Parameters["@ReturnVal"].Value;
Label1.Text=strValue;
m_objCmd.ExecuteNonQuery();
}
上面的思路和代码不知道是否有误,还请各位指点!!!