Stored procedure specific procedures and SQL database calls and program code calls

1, stored procedures, non-parametric stored procedures

Creating a parameterless stored stored procedure

Create Procedure Dcemremr_template
As
SELECT TOP [filename],[filetitle],[filecontent] from [DCEMR]. [dbo]. [Emr_template];

Calling a parameterless stored stored procedure

The amount in the SQL database is called exec dcemremr_template;

SQL program code calls

No parameter stored procedure
String connecting = "Data source=localhost;integrated security=sspi;initial catalog=dcemr";
SqlConnection theconnect = new SqlConnection (connecting);
Theconnect.open ();
SqlCommand Thecommand = Theconnect.createcommand ();
Thecommand.commandtext = "Dcemremr_template";
Thecommand.commandtype = CommandType.StoredProcedure;
SqlDataReader Thereader = Thecommand.executereader ();
while (Thereader.read ())
{
string xx = thereader.getstring (0). ToString ();
}
Theconnect.close ();

2, there are parameter stored procedures, no return value

Create a stored procedure with parameters, no return value

Go
Create Procedure DCEMREMRTEMPLATE100
@filename nvarchar (500)
As
SELECT [filename],[filetitle],[filecontent] from [DCEMR]. [dbo]. [Emr_template] where [filename][email protected];

Call a parameter stored procedure with no return value

The amount in the SQL database calls the EXEC DCEMREMRTEMPLATE100 ' new directory ';

SQL program code calls

There are parameter stored procedures, no return values
String connecting = "Data source=localhost;integrated security=sspi;initial catalog=dcemr";
SqlConnection theconnect = new SqlConnection (connecting);
Theconnect.open ();
SqlCommand Thecommand = Theconnect.createcommand ();
Thecommand.commandtext = "DCEMREMRTEMPLATE101";
Thecommand.commandtype = CommandType.StoredProcedure;
THECOMMAND.PARAMETERS.ADD ("@filename", SqlDbType.NVarChar);
thecommand.parameters["@filename"]. Value = "New Directory";
SqlDataReader Thereader = Thecommand.executereader ();
while (Thereader.read ())
{
string xx = thereader.getstring (0). ToString ();
}
Theconnect.close ();

3, has the parameter stored procedure, has the return value (parameter @filename, returns the parameter @rowcount)

Create a parameter stored procedure with a return value

Go
Create Procedure DCEMREMRTEMPLATE101
@filename nvarchar (500),
@Rowcount int Output
As
SELECT [filename],[filetitle],[filecontent] from [DCEMR]. [dbo]. [Emr_template] where [filename][email protected]
Set @[email protected];

Call parameters to store stored procedures with return values

The amount in the SQL database called exec DCEMREMRTEMPLATE101 ' new directory ', 2;

SQL program code calls

Stored Procedures with parameters
String connecting = "Data source=localhost;integrated security=sspi;initial catalog=dcemr";
SqlConnection theconnect = new SqlConnection (connecting);
Theconnect.open ();
SqlCommand Thecommand = Theconnect.createcommand ();
Thecommand.commandtext = "DCEMREMRTEMPLATE101";
Thecommand.commandtype = CommandType.StoredProcedure;
THECOMMAND.PARAMETERS.ADD ("@filename", SqlDbType.NVarChar);
thecommand.parameters["@filename"]. Value = "New Directory";
THECOMMAND.PARAMETERS.ADD ("@Rowcount", SqlDbType.Int);
thecommand.parameters["@Rowcount"]. Direction = ParameterDirection.Output;
thecommand.parameters["@Rowcount"]. Value = 2;

Thecommand.executenonquery ();
Object SS = Thecommand.executescalar ();
MessageBox.Show (thecommand.parameters["@Rowcount"). Value.tostring ());
SqlDataReader Thereader = Thecommand.executereader ();
while (Thereader.read ())
{
string xx = thereader.getstring (0). ToString ();
}
Theconnect.close ();

Stored procedure specific procedures and SQL database calls and program code calls