请问我从存储过程的创建,类的写法,最后存储过程的调用是否正确,写法是否有待改写,谢谢指点

Question

/****************************************************/
 --   数据表名：  Custominformation                   /****************************************************/  3 Customid varchar 50 0
0 Custompassword varchar 50 0
0 Customname varchar 50 1
0 Customphone varchar 50 1
0 Customadress varchar 50 1
0 Custompost varchar 50 1
0 Customfax varchar 50 1
0 CustomEmail varchar 50 1
0 Createtime datetime 8 1
/****************************************************/
 --   存储过程名：  ProcNewCustom                    /****************************************************/ 
CREATE PROCEDURE ProcNewCustom
@customid varchar(50),
@custompassword varchar(50),
@customname varchar(50),
@customphone varchar(50),
@customaddress varchar(50),
@custompost varchar(50),
@customfax varchar(50),
@customemail varchar(50)
AS
----指定当 Transact-SQL 语句产生运行时错误时，Microsoft? SQL Server? 是否自动回滚当前事务。
set xact_abort on
----开始事务
begin transaction t1
----插入记录
      insert into custominformation values(@customid,@custompassword,@customname,@customphone,@customaddress,@custompost,@customfax,@customemail,getdate())
----结束事务
commit transaction t1GO
/****************************************************/ /****************************************************/
--   asp.net类名：  ProcNewCustom                    
/*****************************************************/using System;
using System.Data;
using System.Data.SqlClient ;namespace WebAppPowerDataGridTest
{//********************************************//
 //类名称：CallProcedure。
 //用途：  调用后台所有用户级存储过程。 //********************************************// public class UCallProcedure
{
public enum UProcedurename 
{

ProcNewCustom, /// (新建客户)
......                          /// （Test）
         
}
private string procname;
                private string[] parasvalue;
private SqlCommand cmd;
private SqlConnection conn;
private string strconn;
private data dat;

public UCallProcedure()
{
}
public int Call(UProcedurename name,string[] UParames)
{
procname=Convert.ToString(name);
parasvalue=UParames;
dat=new data ();
strconn=dat.DbPath ();
conn=new SqlConnection (strconn);
conn.Open ();
cmd=new SqlCommand ();
cmd.Connection =conn;
switch(procname)
{

/******************新建客户******************************/
case"ProcNewCustom":
{
cmd.CommandType = CommandType.StoredProcedure ;
cmd.CommandText ="ProcDeleteServer";
cmd.Parameters.Add("@customid",SqlDbType.VarChar,50);
cmd.Parameters.Add("@custompassword",SqlDbType.VarChar,50);
cmd.Parameters.Add("@customname",SqlDbType.VarChar,50);
cmd.Parameters.Add("@customphone",SqlDbType.VarChar,50);
cmd.Parameters.Add("@customaddress",SqlDbType.VarChar,50);
cmd.Parameters.Add("@custompost",SqlDbType.VarChar,50);
cmd.Parameters.Add("@customfax",SqlDbType.VarChar,50);
cmd.Parameters.Add("@customemaile",SqlDbType.VarChar,50);
cmd.Parameters["@customid"].Value=parasvalue[0];
cmd.Parameters["@custompassword"].Value=parasvalue[0];
cmd.Parameters["@customname"].Value=parasvalue[0];
cmd.Parameters["@customphone"].Value=parasvalue[0];
cmd.Parameters["@customaddress"].Value=parasvalue[0];
cmd.Parameters["@custompost"].Value=parasvalue[0];
cmd.Parameters["@customfax"].Value=parasvalue[0];
cmd.Parameters["@customemail"].Value=parasvalue[0];
return(cmd.ExecuteNonQuery());

}

case"test":
{
//..................................................

}
};
  return(1);


}
}
}
************************************************************************          
  **************************************************************
  aspx.cs页面中这样来调用:
  
  **************************************************************
  //实例华存储过程
  UCallProcedure callproc=new UCallProcedure ();
  //定义存储过程需要的参数
  string Customid="test1";
  string Custompassword="test2";
  string Customname="test3";
  string Customadress="test4";
  string Custompost="test5";
  string Customfax="test6";
  string CustomEmail="test7";
  string Createtime="test8";

  string[] values={customid,custompassword,customname,customphone,customaddress,custompost,customfax,customemail,Createtime};
 //调用存储过程     
 callproc.Call(UCallProcedure.UProcedurename.ProcNewCustom,values); **************************************************************************

Answer 1

写的不错
private string m_User;
private string m_Pwd;
// connection to data source
private SqlConnection con;

/// <summary>
/// Constructor
/// </summary>
public Database(string uname,string upwd)
{
m_User=uname;
m_Pwd=upwd;
}
/// <summary>
/// Run stored procedure,return int.
/// </summary>
/// <param name="procName">Name of stored procedure.</param>
/// <returns>Stored procedure return value.</returns>
public int RunProcINT(string procName) 
{
SqlCommand cmd = CreateCommand(procName, null);
cmd.ExecuteNonQuery();
this.Close();
return (int)cmd.Parameters["ReturnValue"].Value;
} /// <summary>
/// Run stored procedure,have params,return int.
/// </summary>
/// <param name="procName">Name of stored procedure.</param>
/// <param name="prams">Stored procedure params.</param>
/// <returns>Stored procedure return value.</returns>
public int RunProcINT(string procName, SqlParameter[] prams) {
SqlCommand cmd = CreateCommand(procName, prams);
cmd.ExecuteNonQuery();
this.Close();
return (int)cmd.Parameters["ReturnValue"].Value;
}
/// <summary>
/// Run proc,return value.
/// </summary>
public object RunProcValue(string procName, SqlParameter[] prams)
{
SqlCommand cmd = CreateCommand(procName, prams);
object id=cmd.ExecuteScalar();
this.Close();
return id;
}

/// <summary>
/// Run stored procedure,return DataSet.
/// </summary>
/// <param name="procName">Name of stored procedure.</param>
/// <param name="dataSet">Return result of procedure.</param>
public DataSet RunProcDS(string procName) {
SqlCommand cmd = CreateCommand(procName, null);
SqlDataAdapter ada =new SqlDataAdapter(cmd);
DataSet ds=new DataSet();
ada.Fill(ds,"t1");
return ds;
}

/// <summary>
/// Run stored procedure,have params,return DataSet.
/// </summary>
/// <param name="procName">Name of stored procedure.</param>
/// <param name="prams">Stored procedure params.</param>
/// <param name="dataSet">Return result of procedure.</param>
public DataSet RunProcDS(string procName, SqlParameter[] prams) {
SqlCommand cmd = CreateCommand(procName, prams);
SqlDataAdapter ada =new SqlDataAdapter(cmd);
DataSet ds=new DataSet();
ada.Fill(ds,"t1");
return ds;
}
/// <summary>
/// Run Sql State,return DataReader.
/// </summary>
public SqlDataReader RunSqlDR(string Sql) 
{
SqlCommand cmd = CreateCommand2(Sql, null);
return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
} public DataSet RunSqlDS(string Sql)
{
SqlCommand cmd=CreateCommand2(Sql,null);
SqlDataAdapter sda=new SqlDataAdapter(cmd);
DataSet ds=new DataSet();
sda.Fill(ds);
return ds;
}

Answer 2

private SqlCommand CreateCommand(string procName, SqlParameter[] prams) {
// make sure connection is open
Open(); //command = new SqlCommand( sprocName, new SqlConnection( ConfigManager.DALConnectionString ) );
SqlCommand cmd = new SqlCommand(procName, con);
cmd.CommandType = CommandType.StoredProcedure; // add proc parameters
if (prams != null) {
foreach (SqlParameter parameter in prams)
cmd.Parameters.Add(parameter);
}

  // return param
cmd.Parameters.Add(
new SqlParameter("ReturnValue", SqlDbType.Int, 4,
ParameterDirection.ReturnValue, false, 0, 0,
string.Empty, DataRowVersion.Default, null)); return cmd;
}

Answer 3

VS studio C#标准写法:
#region 获得还没有面积收费的信息的列表
/// <summary>
///  功能表述
/// </summary>
/// <param name="vCommandType">命令类型</param>
/// <param name="vKeyWords">查询关键字</param>
/// <param name="vUserID">用户ID</param>
/// <returns></returns>
public SqlDataReader AreaChargeNoProArr(string vCommandType,string vKeyWords)
{
SqlConnection sqlcon  = new SqlConnection(Configuration.ConnectionString);
SqlCommand sqlcom  =new SqlCommand();
sqlcon.Open();
sqlcom.Connection = sqlcon;
sqlcom.CommandType = CommandType.StoredProcedure;

sqlcom.Parameters.Add(new SqlParameter("@CommandType", SqlDbType.NVarChar, 20));
sqlcom.Parameters.Add(new SqlParameter("@KeyWords", SqlDbType.NVarChar, 20));
//sqlcom.Parameters.Add(new SqlParameter("@UserID",SqlDbType.NVarChar,20));
//sqlcom.Parameters.Add(new SqlParameter("@UserPower",SqlDbType.Int)); sqlcom.Parameters["@CommandType"].Value = vCommandType;
sqlcom.Parameters["@KeyWords"].Value=  vKeyWords;
//sqlcom.Parameters["@UserID"].Value = vUserID;
//sqlcom.Parameters["@UserPower"].Value = vDUserPower;
sqlcom.CommandText = "AreaChargeNoProArray";
try
{
return sqlcom.ExecuteReader();
}
catch(Exception e)
{
//return null;
throw(e);
}
}
#endregion
别的类调用你的函数会有详细记的tip说明!

Answer 4

现有存储过程:ProcCreateNewTransactionCREATE PROCEDURE ProcCreateNewTransaction 
@Departmentname varchar(50),
@Customname varchar(50),
@linkmaname varchar(50),
@customphone varchar(50),
@customaddress varchar(50),
@serverid varchar(50),
@servername varchar(50),
@charge varchar(50),
@acceptance varchar(50),
@statue varchar(20),
@class varchar(20),
@resume varchar(1000),
@Information varchar(3000),
@jockey varchar(50),
@wake varchar(50),
@attion varchar(1000),
@uniteflag varchar(20),
@unitetype varchar(50)=NULL,
@rapid varchar(20),
@transactidout varchar(100) output,
@customidout varchar(50) output,
@custompwdout varchar(100) output
AS
declare @Departmentablename varchar(50),@accepttime datetime,@lasttime datetime,@waketime datetime,@custompwd varchar(50),@accessoriesid varchar(100),@transactid varchar(100),@departmentid varchar(50),@i int
set @Departmentablename=(select departmenttable from departmentdescribe where departmentname=@departmentname)
set @Departmentid=(select departmentid from departmentdescribe where departmentname=@departmentname)
set @accepttime=getdate()
set @lasttime=getdate()+cast(@acceptance as int)
set @waketime=getdate()+cast(@wake as int)
set @transactid=@Departmentid+cast(cast((rand(cast(getdate()as binary))*1000000000000000)as bigint)as varchar(100))
print(@acceptance)
if exists (select customid from custominformation where customid=@linkmaname)
   begin
        -----------我想问题是出在这里,但就是找不出来郁闷啊
exec('insert into '+@departmentablename+' values('''+@transactid+''','''+@departmentname+''','''+@linkmaname+''','''+@customname+''','''+@linkmaname+''','''+@customphone+''','''+@serverid+''','''+@servername+''',cast('+@charge+ 'as int),cast('+@acceptance+' as int) ,'''+@accepttime+''',NULL,'''+@statue+''','''+@class+''','''+@resume+''','''+@jockey+''','''+@information+''','''+@lasttime+''',cast('+@wake+ 'as int),'''+@waketime+''','''+@attion+''',0,NULL,'''+@uniteflag+''','''+@unitetype+''',0,0,cast('+@rapid+'as bit),NULL)')
        set @transactidout=@transactid  
   end
else
   begin
set @i=0
while(@i>=0)
   begin
         if(@i=2000)
                begin
         set @custompwd=cast(cast((rand(cast(getdate()as binary))*1000000000000000)as bigint)as varchar(100))
         set @customidout=@linkmaname
    set @custompwdout=@custompwd
    break
end
     else 
         begin
set @i=@i+1
     continue
         end
end
exec('SET XACT_ABORT ON 
        begin transaction t1
insert into custominformation values('''+@linkmaname+''','''+@custompwd+''','''+@customname+''','''+@customphone+''','''+@customaddress+''',NULL,NULL,NULL,getdate())
       insert into '+@departmentablename+' values('''+@transactid+''','''+@departmentname+''','''+@linkmaname+''','''+@customname+''','''+@linkmaname+''','''+@customphone+''','''+@serverid+''','''+@servername+''',cast('+@charge+ 'as int),cast('+@acceptance+' as int) ,'''+@accepttime+''',NULL,'''+@statue+''','''+@class+''','''+@resume+''','''+@jockey+''','''+@information+''','''+@lasttime+''',cast('+@wake+ 'as int),'''+@waketime+''','''+@attion+''',0,NULL,'''+@uniteflag+''','''+@unitetype+''',0,0,cast('+@rapid+'as bit),NULL)
insert into charginginformation select a.transactid,a.transactid+b.selfid,b.chargename,b.chargeaccording,0  from '+@departmentablename+' a right join servermoney b on a.serverid=b.serverid where a.transactid='+@transactid+'
        commit transaction t1   
            ')
   end
GO
-----------------------------------------------------------------------------------aspx.cs中这样来调用存储过程______________________________________________________
string[] values={Departmentname,text1.Value,text2.Value,text3.Value,text4.Value,text5.Value,text6.Value,text7.Value,text8.Value,"待办",jian,text9.Value,text10.Value,Username,"1","1","1","1",rapid};
UCallProcedure callproc=new UCallProcedure ();
callproc.Call(UCallProcedure.UProcedurename.ProcCreateNewTransaction,values);
为什么text2.Value只能输入1,输入其他的数值的时候都报同样错误:
_________________________________________________________________________将截断字符串或二进制数据。 15 
说明: 执行当前 Web 请求期间，出现未处理的异常。请检查堆栈跟踪信息，以了解有关该错误以及代码中导致错误的出处的详细信息。 异常详细信息: System.Data.SqlClient.SqlException: 将截断字符串或二进制数据。 15
_________________________________________________________________________请问如下的存储过程哪里错啦,谢谢指点.其中text2.Value对应是表中的linkmaname的值
_________________________________________________________________________

Answer 5

parasvalue[0]为什么都是取数组的首个值啊?

Answer 6

callproc.Call(UCallProcedure.UProcedurename.ProcNewCustom,values);第一个参数我不明白.
能解释一下吗

Answer 7

楼主,你上面说的只能输1输其它的要报错,我也遇到过,你检查一下,你的返回量里有没有返回null值的,
如果返回有null值，而你又把它付给了一个类型不同的量，就要出错哦！！！