C # Stored Procedure

Using System;
Using System. Data;
Using System. Data. SqlClient;

This is a namespace. You don't need to explain it more.

Public class DAL // define a class
{
String cnstr = "data source =.; initial catalog = company; persist security info = False; user id = sa; pwd = sa ;";

// Define the connection string to connect to the database
Private SqlCommand cm = new SqlCommand (); // create a Command object

// Define a global Command object.

Public SqlCommand getCommand // return the Command object
{
Get {return cm ;}
}

Public DAL () // Constructor
{
Cm. Connection = new SqlConnection (cnstr );
}

//---------------------------------------------------------
// Add Parameters
//---------------------------------------------------------
 

To execute a new stored procedure, clear the parameters of the previously added stored procedure.

// Clear parameters
Public void ClearParameter ()
{Cm. Parameters. Clear ();}

This is a common function for adding parameters.

// All Parameters
Public void addNewParameter (string ParameterName, string ParameterValue, SqlDbType sqlType, int size, string Direction)
{
Cm. Parameters. Add (ParameterName, sqlType, size); // Add the Parameters of the stored procedure
Cm. Parameters [ParameterName]. Value = ParameterValue; // negative Value
Cm. Parameters [ParameterName]. Direction = getDirection (Direction); // set the Direction
}

// The direction is the input
Public void addNewParameter (string ParameterName, string ParameterValue, SqlDbType sqlType, int size)
{
Cm. Parameters. Add (ParameterName, sqlType, size); // Add the Parameters of the stored procedure
Cm. Parameters [ParameterName]. Value = ParameterValue; // negative Value
// Cm. Parameters [ParameterName]. Direction = getDirection (Direction); // set the Direction

}

The following describes several common data types for easy operation.

This is a specialized intData Type Parameters

// Add int Parameters
Public void addNewParameter (string ParameterName, int ParameterValue)
{
Cm. Parameters. Add (ParameterName, SqlDbType. Int, 4); // Add the Parameters of the stored procedure
Cm. Parameters [ParameterName]. Value = ParameterValue; // negative Value
// Set the direction to the default value -- Input
}

This is dedicated to adding nvarCharData Type Parameters

// Add nvarChar Parameters
Public void addNewParameter (string ParameterName, string ParameterValue, int size)
{
Cm. Parameters. Add (ParameterName, SqlDbType. NVarChar, size); // Add the Parameters of the stored procedure
Cm. Parameters [ParameterName]. Value = ParameterValue; // negative Value
// Set the direction to the default value-Input

}

This is specially added BitData Type Parameters

// Add bit Parameters
Public void addNewParameter (string ParameterName, bool ParameterValue)
{
Cm. Parameters. Add (ParameterName, SqlDbType. Bit); // Add the Parameters of the stored procedure
Cm. Parameters [ParameterName]. Value = ParameterValue; // negative Value
// Set the direction to the default value-Input

}

After adding the parameters, you can run the stored procedure.

//-------------------------------------------------------------
// Run and return record set
//-------------------------------------------------------------

The stored procedure is executed by passing in the stored procedure name, and the returned record set is placed in DataSet. If multiple record sets exist, in Table [0], Table [1], Table [2]…

// Run the stored procedure and return DataSet
Public DataSet runSPDataSet (string StoredProcedureName)
{
// Cm. Connection = new SqlConnection (cnstr );
Cm. CommandText = StoredProcedureName;
Cm. CommandType = CommandType. StoredProcedure;
Try
{
SqlDataAdapter da = new SqlDataAdapter (cm );
DataSet DS = new DataSet ();
Da. Fill (DS );
Return DS;
}
Catch (Exception ex)
{
Throw ex;
}
Finally
{
Cm. Connection. Close ();
}
}

The stored procedure is executed by passing in the stored procedure name. The first record in the first returned record set is placed in the Object array. This is often used to display details, because in most cases, only one record is used. If it is null, returnStrValue [0] = "null ".

// Run the stored procedure and return the array of the first record
Public Object [] runSPItems (string StoredProcedureName)
{
Object [] strValue = new Object [1];
Cm. CommandText = StoredProcedureName;
Cm. CommandType = CommandType. StoredProcedure;
Try
{
Cm. Connection. Open ();
SqlDataReader r = cm. ExecuteReader (CommandBehavior. CloseConnection );
If (r. Read ())
{
StrValue = new Object [r. FieldCount];
R. GetValues (strValue );
}
Else
{
StrValue [0] = "null ";
} R. Close ();
}
Catch (Exception ex)
{
Throw ex;
}
Finally
{
Cm. Connection. Close ();
}
Return strValue;
}

If you want to use DataReader, you can use this function to return the Command object, use Command. ExecuteReader, and then use Command. Connection. Close () to Close the Connection. There should be better methods. In short, I seldom use DataReader.

// Add the parameters required by the stored procedure and return the command
Public SqlCommand getSPCommand (string StoredProcedureName)
{
// Cm. Connection = new SqlConnection (cnstr );
Cm. CommandText = StoredProcedureName;
Cm. CommandType = CommandType. StoredProcedure;
Return cm;
}

If you only add a record and do not need to return a record set, you can use the following function.

// When a stored procedure is run, no record set is returned, which is used to add a record or return data through parameters of the stored procedure.
Public string runSP (string ParameterName)
{
// Cm. Connection = new SqlConnection (cnstr );
Cm. CommandType = CommandType. StoredProcedure;
Cm. CommandText = ParameterName;
Try
{
Cm. Connection. Open ();
Cm. ExecuteNonQuery ();
Cm. Connection. Close ();
Return "true ";
}
Catch (Exception ex)
{
Throw ex;
}
Finally
{
Cm. Connection. Close ();
}

}

// Return the parameter value by serial number. It is generally used after the stored procedure is executed.
Public string getParameter (int ParameterIndex)
{
Return cm. Parameters [ParameterIndex]. Value. ToString ();
}

// Return parameter values by name
Public string getParameter (string ParameterName)
{
Return cm. Parameters [ParameterName]. Value. ToString ();
}