我在进行数据库中的text字段更新时,我是这样调用的:private SqlProcedure sp;
。。
this.sp = new SqlProcedure(this.connstr, this.xproctext);
this.sp.Call(new Object[] { this.uid, this.textstr });textstr 是 string 类型数据库中存储过程是采用了 writetext语句来写的,测试没有问题,传入的参数是
@txt text (类型)结果是,只要textstr 稍为大一点(估计在300个字符以上,没精确统计),数据表中的text字段重写结果就是“”(没有其他字符),小的话就没问题。不知道是什么原因?请各位帮分析下:以下是我的存储过程调用类:
using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;/// 存储过程的返回值纪录类/// DataSet : 表示返回的表/// Output : 存储过程的输出参数/// Value : 存储过程的返回值/// </summary>public class SqlResult
{ public int Value; public Hashtable Output; public DataSet dataSet; public SqlResult()
{ Value = 0; Output = new Hashtable(); dataSet = new DataSet(); }}/// <summary>/// 用于调用数据库中的存储过程,返回一个DataSet、Output、Value的SqlResult类/// </summary>public class SqlProcedure
{ // Fields
private string connectionStr;
private SqlCommand myCommand;
private SqlConnection myConnection;
private SqlParameter myParameter;
private string sp_name; public string ProcedureName
{ get { return this.sp_name; } set { this.sp_name = value; } } public SqlProcedure(string connstr, string sp_name)
{
this.connectionStr = connstr;
this.ProcedureName = sp_name;
}
public SqlResult Call(params object[] parameters)
{
SqlResult result1 = new SqlResult();
this.myConnection = new SqlConnection(this.connectionStr);
this.myCommand = new SqlCommand(this.ProcedureName, this.myConnection);
this.myCommand.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adapter1 = new SqlDataAdapter(this.myCommand);
try
{
this.myConnection.Open();
this.GetProcedureParameter(parameters);
adapter1.Fill(result1.dataSet, "Table");
this.GetOutputValue(result1);
}
catch (Exception exception1)
{
throw exception1;
}
finally
{
adapter1.Dispose();
this.myCommand.Dispose();
this.myConnection.Close();
this.myConnection.Dispose();
}
return result1;
}
。。
this.sp = new SqlProcedure(this.connstr, this.xproctext);
this.sp.Call(new Object[] { this.uid, this.textstr });textstr 是 string 类型数据库中存储过程是采用了 writetext语句来写的,测试没有问题,传入的参数是
@txt text (类型)结果是,只要textstr 稍为大一点(估计在300个字符以上,没精确统计),数据表中的text字段重写结果就是“”(没有其他字符),小的话就没问题。不知道是什么原因?请各位帮分析下:以下是我的存储过程调用类:
using System;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Web;/// 存储过程的返回值纪录类/// DataSet : 表示返回的表/// Output : 存储过程的输出参数/// Value : 存储过程的返回值/// </summary>public class SqlResult
{ public int Value; public Hashtable Output; public DataSet dataSet; public SqlResult()
{ Value = 0; Output = new Hashtable(); dataSet = new DataSet(); }}/// <summary>/// 用于调用数据库中的存储过程,返回一个DataSet、Output、Value的SqlResult类/// </summary>public class SqlProcedure
{ // Fields
private string connectionStr;
private SqlCommand myCommand;
private SqlConnection myConnection;
private SqlParameter myParameter;
private string sp_name; public string ProcedureName
{ get { return this.sp_name; } set { this.sp_name = value; } } public SqlProcedure(string connstr, string sp_name)
{
this.connectionStr = connstr;
this.ProcedureName = sp_name;
}
public SqlResult Call(params object[] parameters)
{
SqlResult result1 = new SqlResult();
this.myConnection = new SqlConnection(this.connectionStr);
this.myCommand = new SqlCommand(this.ProcedureName, this.myConnection);
this.myCommand.CommandType = CommandType.StoredProcedure;
SqlDataAdapter adapter1 = new SqlDataAdapter(this.myCommand);
try
{
this.myConnection.Open();
this.GetProcedureParameter(parameters);
adapter1.Fill(result1.dataSet, "Table");
this.GetOutputValue(result1);
}
catch (Exception exception1)
{
throw exception1;
}
finally
{
adapter1.Dispose();
this.myCommand.Dispose();
this.myConnection.Close();
this.myConnection.Dispose();
}
return result1;
}
{ SqlCommand myCommand2 = new SqlCommand(); myCommand2.Connection = this.myConnection; myCommand2.CommandText = "select * from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME='" + this.ProcedureName + "' order by ORDINAL_POSITION"; SqlDataReader reader = null; try
{ reader = myCommand2.ExecuteReader(); myParameter = new SqlParameter(); myParameter.ParameterName = "@Value"; myParameter.SqlDbType = SqlDbType.Int; myParameter.Direction = ParameterDirection.ReturnValue; myCommand.Parameters.Add(myParameter); int i = 0; while (reader.Read())
{ myParameter = new SqlParameter(); myParameter.ParameterName = reader["PARAMETER_NAME"].ToString(); myParameter.Direction = reader["PARAMETER_MODE"].ToString() == "IN" ? ParameterDirection.Input : ParameterDirection.Output; switch (reader["DATA_TYPE"].ToString())
{ case "bit": if (myParameter.Direction == ParameterDirection.Input) myParameter.Value = (bool)parameters[i]; myParameter.SqlDbType = SqlDbType.Bit; break; case "bigint": if (myParameter.Direction == ParameterDirection.Input) myParameter.Value = (long)parameters[i]; myParameter.SqlDbType = SqlDbType.BigInt; break; case "int": if (myParameter.Direction == ParameterDirection.Input) myParameter.Value = (int)parameters[i]; myParameter.SqlDbType = SqlDbType.Int; break; case "decimal": if (myParameter.Direction == ParameterDirection.Input) myParameter.Value = (double)parameters[i]; myParameter.SqlDbType = SqlDbType.Decimal; myParameter.Precision = (byte)reader["NUMERIC_PRECISION"]; myParameter.Scale = (byte)reader["NUMERIC_SCALE"]; break; case "nvarchar": if (myParameter.Direction == ParameterDirection.Input) myParameter.Value = (string)parameters[i]; myParameter.Size = (int)reader["CHARACTER_MAXIMUM_LENGTH"]; myParameter.SqlDbType = SqlDbType.NVarChar; break; case "varchar": if (myParameter.Direction == ParameterDirection.Input) myParameter.Value = (string)parameters[i]; myParameter.Size = (int)reader["CHARACTER_MAXIMUM_LENGTH"]; myParameter.SqlDbType = SqlDbType.VarChar; break; case "nchar": if (myParameter.Direction == ParameterDirection.Input) myParameter.Value = (string)parameters[i]; myParameter.Size = (int)reader["CHARACTER_MAXIMUM_LENGTH"]; myParameter.SqlDbType = SqlDbType.NChar; break; case "char": if (myParameter.Direction == ParameterDirection.Input) myParameter.Value = (string)parameters[i]; myParameter.Size = (int)reader["CHARACTER_MAXIMUM_LENGTH"]; myParameter.SqlDbType = SqlDbType.Char; break; case "ntext": if (myParameter.Direction == ParameterDirection.Input) myParameter.Value = parameters[i].ToString(); myParameter.SqlDbType = SqlDbType.NText; break; case "text": if (myParameter.Direction == ParameterDirection.Input) myParameter.Value = parameters[i].ToString(); myParameter.SqlDbType = SqlDbType.Text; break; case "datetime": if (myParameter.Direction == ParameterDirection.Input) myParameter.Value = (DateTime)parameters[i]; myParameter.SqlDbType = SqlDbType.DateTime; break; case "smalldatetime": if (myParameter.Direction == ParameterDirection.Input) myParameter.Value = (DateTime)parameters[i]; myParameter.SqlDbType = SqlDbType.DateTime; break; case "image": if (myParameter.Direction == ParameterDirection.Input)
{ HttpPostedFile PostedFile = (HttpPostedFile)parameters[i]; Byte[] FileByteArray = new Byte[PostedFile.ContentLength]; Stream StreamObject = PostedFile.InputStream; StreamObject.Read(FileByteArray, 0, PostedFile.ContentLength); myParameter.Value = FileByteArray; } myParameter.SqlDbType = SqlDbType.Image; break; case "uniqueidentifier": //myParameter.Value = (string)parameters[i]; myParameter.SqlDbType = SqlDbType.UniqueIdentifier; break; default: break; } i++; myCommand.Parameters.Add(myParameter); } } catch (Exception e)
{ throw e;
//Console.Write("sqlproc error"); } finally
{ if (reader != null) reader.Close(); myCommand2.Dispose(); } } private void GetOutputValue(SqlResult result)
{
result.Value = (int)this.myCommand.Parameters["@Value"].Value;
foreach (SqlParameter parameter1 in this.myCommand.Parameters)
{
if (parameter1.Direction == ParameterDirection.Output)
{
result.Output.Add(parameter1.ParameterName, parameter1.Value);
}
}
}
}
可我才写了300多个字符啊,超过8000个字符就可以采用text类型来存放,我觉得还是我的这个存储过程调用类有问题,但不知道问题在哪里。。
检查case "text": if (myParameter.Direction == ParameterDirection.Input) myParameter.Value = parameters[i].ToString(); myParameter.SqlDbType = SqlDbType.Text; parameters[i].ToString();超过300后的值是什么