/// <summary>
/// 判断数据表中记录的主键值是否存在外键约束
/// </summary>
/// <param name="strPrimaryTable">主键表</param>
/// <param name="strPrimaryValue">数据表中某条记录主键的值</param>
/// <returns></returns>
public bool IsExistConstraint(string strPrimaryTable, string strPrimaryValue)
{
bool booIsExist = false;
string strSql = null;
string strForeignColumn = null;
string strForeignTable = null;
SqlDataReader sdr = null; try
{
//创建SqlParameter对象,并赋值
SqlParameter param = new SqlParameter("@PrimaryTable", SqlDbType.VarChar);
param.Value = strPrimaryTable;
//创建泛型
List<SqlParameter> parameters = new List<SqlParameter>();
parameters.Add(param);
//把泛型中的元素复制到数组中
SqlParameter[] inputParameters = parameters.ToArray();
//通过存储过程得到外键表的相关数据
DataTable dt = db.GetDataTable("P_QueryForeignConstraint", inputParameters); //循环这些相关数据
foreach (DataRow dr in dt.Rows)
{
strForeignTable = dr["ForeignTable"].ToString();
strForeignColumn = dr["ForeignColumn"].ToString();
strSql = "Select " + strForeignColumn + " From " + strForeignTable + " Where " + strForeignColumn + " = '" + strPrimaryValue + "'";
sdr = db.GetDataReader(strSql); if (sdr.HasRows)
{
booIsExist = true;
sdr.Close();
//跳出循环
break;
} sdr.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "系统提示");
throw ex;
} return booIsExist;
}求这段代码的存储过程该怎么写?
/// 判断数据表中记录的主键值是否存在外键约束
/// </summary>
/// <param name="strPrimaryTable">主键表</param>
/// <param name="strPrimaryValue">数据表中某条记录主键的值</param>
/// <returns></returns>
public bool IsExistConstraint(string strPrimaryTable, string strPrimaryValue)
{
bool booIsExist = false;
string strSql = null;
string strForeignColumn = null;
string strForeignTable = null;
SqlDataReader sdr = null; try
{
//创建SqlParameter对象,并赋值
SqlParameter param = new SqlParameter("@PrimaryTable", SqlDbType.VarChar);
param.Value = strPrimaryTable;
//创建泛型
List<SqlParameter> parameters = new List<SqlParameter>();
parameters.Add(param);
//把泛型中的元素复制到数组中
SqlParameter[] inputParameters = parameters.ToArray();
//通过存储过程得到外键表的相关数据
DataTable dt = db.GetDataTable("P_QueryForeignConstraint", inputParameters); //循环这些相关数据
foreach (DataRow dr in dt.Rows)
{
strForeignTable = dr["ForeignTable"].ToString();
strForeignColumn = dr["ForeignColumn"].ToString();
strSql = "Select " + strForeignColumn + " From " + strForeignTable + " Where " + strForeignColumn + " = '" + strPrimaryValue + "'";
sdr = db.GetDataReader(strSql); if (sdr.HasRows)
{
booIsExist = true;
sdr.Close();
//跳出循环
break;
} sdr.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "系统提示");
throw ex;
} return booIsExist;
}求这段代码的存储过程该怎么写?
@strForeignTable nvarchar(10)
@strPrimaryValue nvarchar(10)
AS
Select @strForeignColumn From @strForeignTable Where @strForeignColumn = @strPrimaryValue
GO
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[P_QueryForeignConstraint]
@PrimaryTable varchar(50)
AS
SELECT(SELECT NAME
FROM syscolumns
WHERE colid=b.rkey AND id=rkeyid) AS PrimaryColumn,
OBJECT_NAME(b.fkeyid) AS foreignTable,
(SELECT NAME
From syscolumns
WHERE colid=b.rkey AND id=b.fkeyid) AS foreignColumn
FROM sysobjects a INNER JOIN
sysforeignkeys b ON a.id = b.constid INNER JOIN
sysobjects c ON a.parent_obj=c.id
WHERE (a.xtype='f') AND (c.xtype='U') AND (OBJECT_NAME(b.rkeyid)=@PrimaryTable)
RETURN