我正在尝试使用WCF服务在SQLServer中插入值。有5行要插入。
其给出的例外:
参数的长度超过了 128个字符的限制
是的,它的长度超过128个字符。但我已经申报了NVarChar(4000)的大小。
我已经搜索了这个网站,和其他网站,以了解和摆脱这个例外
MedicineTestName = "1,crocin,2,aspirin,2,naproxen,3,ibuprofen,3,Ketaprofen";
代码:
public static string InsertHistory(string PatientId, string MedicineTestName, string CreateBy)
{
DataSet objdata;
object objSubjectReader = new object();
try
{
StringBuilder sb = new StringBuilder();
string[] wordswithcomma = MedicineTestName.Split(',');
for (int i = 0; i < wordswithcomma.Length -1 ; i=i+2)
{
string MedicineType = wordswithcomma[i];
string MedicineName = wordswithcomma[i + 1];
sb.Append("insert into tblMedicineHistory values(" + PatientId + "," + "'" + MedicineName + "'" + "," + MedicineType + ",GETDATE()," + "'" + CreateBy + "'" + ",GETDATE(),0);");
}
string sbo = sb.ToString();
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter(sbo, System.Data.SqlDbType.NVarChar, 4000);
param[0].Value = sbo;
objdata = SqlHelper.ExecuteDataset(ConString, CommandType.StoredProcedure, "SP_MedicineHistory", param);
return JsonConvert.SerializeObject(objdata, Newtonsoft.Json.Formatting.Indented);
//return sbo;
}
catch (SqlException ex)
{
return objSubjectReader.ToString();
}
}
谢谢。
我认为它在抱怨参数的名称;您将传递sbo
作为参数名称,但sbo
是完全组合的TSQL,它将是…长。注意:您的SQL是完全不安全的-这是而不是如何参数化SQL!
正确参数化单行的SQL类似于:
const string sql = @"
insert into tblMedicineHistory (PatientID, MedicineName, MedicineType, ...)
values(@patientId, @medicineName, @medicineType, ...)";
var args = new[] {
new SqlParameter("@patientId", PatientId),
new SqlParameter("@medicineName", MedicineName),
new SqlParameter("@medicineType", MedicineType),
...
}