存储过程:(用向导建立)
CREATE PROCEDURE [insert_pinglun]
(@qyid [int],
@content [ntext],
@plname [nvarchar](50),
@qq [char](20),
@email [nvarchar](50),
@ip [char](20),
@time1 [datetime])
AS INSERT INTO [cne71].[dbo].[pinglun]
( [qyid],
[content],
[plname],
[qq],
[email],
[ip],
[time1])
VALUES
( @qyid,
@content,
@plname,
@qq,
@email,
@ip,
@time1)
return 1
GO
数据层代码:(调用存储过程)
public int insertpinglun(qiyeVAO qiyeVO)
{
int rowsAffected;
int returnValue;
SqlParameter[] Parameters={
new SqlParameter("@qyid",SqlDbType.Int,6),
new SqlParameter("@content",SqlDbType.NText),
new SqlParameter("@plname",SqlDbType.NVarChar,50),
new SqlParameter("@qq",SqlDbType.Char,20),
new SqlParameter("@email",SqlDbType.NVarChar,50),
new SqlParameter("@ip",SqlDbType.Char,20),
new SqlParameter("@time1",SqlDbType.DateTime)
};
Parameters[0].Value=qiyeVO.Id;
Parameters[1].Value=qiyeVO.Content;
Parameters[2].Value=qiyeVO.Plname;
Parameters[3].Value=qiyeVO.Qq;
Parameters[4].Value=qiyeVO.Email;
Parameters[5].Value=qiyeVO.Ip;
Parameters[6].Value=qiyeVO.Time1;
core.DbObject db1= new DbObject();
returnValue = (int)db1.RunProcedure("insert_pinglun",Parameters,out rowsAffected);
return returnValue;
}
类代码:(core/DbObject .cs)
public class DbObject
{
protected SqlConnection Connection;
private string connectionString;
public DbObject()
{
connectionString = System.Configuration.ConfigurationSettings.AppSettings["sqlConnection"];
Connection = new SqlConnection( connectionString );
}
public SqlCommand BuildIntCommand(string storedProcName,IDataParameter[] parameters)
{
SqlCommand command = BuildQueryCommand ( storedProcName,parameters );
command.Parameters.Add(new SqlParameter("ReturnValue",SqlDbType.Int,4,ParameterDirection.ReturnValue,false,0,0,string.Empty,DataRowVersion.Default,null));
return command;
}
public SqlCommand BuildQueryCommand ( string storedProcName,IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand( storedProcName,Connection);
command.CommandType = CommandType.StoredProcedure;
foreach ( SqlParameter parameter in parameters )
{
command.Parameters.Add( parameter );
//System.Web.HttpContext.Current.Response.Write( parameter.ParameterName);
//System.Web.HttpContext.Current.Response.Write( parameter.SqlDbType);
//System.Web.HttpContext.Current.Response.Write( parameter.Value);
}
//System.Web.HttpContext.Current.Response.Write (command.Parameters.Count);
return command;
}
protected string ConnectionString
{
get
{
return connectionString;
}
}
public int RunProcedure ( string storedProcName ,IDataParameter[] parameters, out int rowsAffected )
{
int result;
Connection.Open();
SqlCommand command = BuildIntCommand ( storedProcName ,parameters );
rowsAffected = command.ExecuteNonQuery();//没有影响就是-1
result = (int)command.Parameters["ReturnValue"].Value;
Connection.Close();
Connection.Dispose();
return result;
}
}