一、从Sysobjects里查到该存储过程的id; 二、从syscolumns查该id对应的列。 CREATE PROCEDURE DBO.GETUSERTABLES @XTYPE CHAR(2) AS SELECT NAME,ID,XTYPE FROM SYSOBJECTS WHERE( XTYPE = @XTYPE OR XTYPE = 'V' OR XTYPE = 'U' OR XTYPE = 'P' AND ID NOT IN (SELECT ID FROM SYSCOLUMNS)) AND STATUS >= 0 ORDER BY NAMEGO 'P'表示存储过程。CREATE PROCEDURE DBO.GETUSERCOLUMNS @TABLEID INTEGER AS SELECT NAME,ID,XTYPE,TYPESTAT,XUSERTYPE,LENGTH,COLID,XPREC,XSCALE,COLSTAT,ISOUTPARAM FROM SYSCOLUMNS WHERE ID = @TABLEIDGO
look into SqlCommandBuilder.DeriveParameters method or OleDbCommandBuilder.DeriveParameters method
// 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;
}
二、从syscolumns查该id对应的列。
CREATE PROCEDURE DBO.GETUSERTABLES @XTYPE CHAR(2) AS
SELECT NAME,ID,XTYPE FROM SYSOBJECTS
WHERE( XTYPE = @XTYPE OR XTYPE = 'V' OR XTYPE = 'U' OR XTYPE = 'P' AND ID NOT IN (SELECT ID FROM SYSCOLUMNS)) AND STATUS >= 0 ORDER BY NAMEGO
'P'表示存储过程。CREATE PROCEDURE DBO.GETUSERCOLUMNS @TABLEID INTEGER AS
SELECT NAME,ID,XTYPE,TYPESTAT,XUSERTYPE,LENGTH,COLID,XPREC,XSCALE,COLSTAT,ISOUTPARAM FROM SYSCOLUMNS
WHERE ID = @TABLEIDGO