How to use "?" Replace parameters to simplify database operations

I have briefly introduced how to emulate Java in SQL? The implementation scheme that replaces the parameter location, in programming and development, we will implement some strange simple and complex ideas with our own programming concepts, every step from rough to beautiful can make us proud. Although it may be a silly or even brainless idea in the eyes of others, as a programmer, I always think: "programming is not only a technology, but also an art. Even the most boring coding, we also need to complete it with a passionate coding behavior. ", Every attempt and practice can benefit us a lot. Although not every attempt is so correct, at least you can find that this road is impossible, and this is experience.

A little off the question. Let's continue with what we want to talk about.

The SQL we originally needed to write may be in the following format:

 
 

  
  
Public UserInfo GetUserInfoById (Guid id ){
  
  
String SQL = "SELECT name, age, address, desc FROM UserInfo WHERE id = @ id ";
  
  
Sqlparameter param = new SqlParameter ("@ id", id );
  
  
// The code for processing UserInfo is omitted
  
  
}
 
 

However? The new code format is changed:

 
 

  
  
Public UserInfo GetUserInfoById (Guid id ){
  
  
String SQL = "SELECT name, age, address, desc FROM UserInfo WHERE id =? ";
  
  
UnknowSqlParameter castUnknow = new UnknowSqlParameter ();
  
  
Sqlparameter [] parameters = castUnknow. CastUnknowMark (ref SQL, id );
  
  
// The code for processing UserInfo is omitted
  
  
}
 
 

It seems that the second type is not much simpler or even a little more troublesome than the first one, but if we add the extension method, it will become easier. For example, the original SQL connection class is as follows:

 
 

  
  
Public static class SQLHelper
  
  
{
  
  
# Region Connection
  
  
 
  
  
/// <Summary>
  
  
/// Database connection
  
  
/// </Summary>
  
  
Private static SqlConnection connection;
  
  
 
  
  
/// <Summary>
  
  
/// Open the database connection
  
  
/// </Summary>
  
  
/// <Param name = "connectionString"> connection string </param>
  
  
Private static void OpenConnection (string connectionString)
  
  
{
  
  
Connection = new SqlConnection (connectionString );
  
  
Connection. Open ();
  
  
}
  
  
 
  
  
/// <Summary>
  
  
/// Obtain the database connection
  
  
/// </Summary>
  
  
/// <Returns> </returns>
  
  
Public static SqlConnection GetConnection ()
  
  
{
  
  
String connectionstring = ConfigurationManager. ConnectionStrings ["SQL"]. ConnectionString;
  
  
If (connection = null | connection. State = ConnectionState. Closed)
  
  
{
  
  
OpenConnection (connectionstring );
  
  
}
  
  
Else if (connection. State = ConnectionState. Broken)
  
  
{
  
  
Connection. Close ();
  
  
OpenConnection (connectionstring );
  
  
}
  
  
Return connection;
  
  
}
  
  
 
  
  
# Endregion
  
  
 
  
  
# Region Commond
  
  
 
  
  
/// <Summary>
  
  
/// Set SqlCommond
  
  
/// </Summary>
  
  
/// <Param name = "com"> sqlCommond </param>
  
  
/// <Param name = "type"> CommondType </param>
  
  
/// <Param name = "values"> parameter </param>
  
  
Private static void SetSqlCommond (SqlCommand cmd, string SQL, CommandType comment type, SqlParameter [] values)
  
  
{
  
  
Cmd. Parameters. Clear ();
  
  
Cmd. CommandText = SQL;
  
  
Cmd. CommandType = primitive type;
  
  
If (values! = Null) cmd. Parameters. AddRange (values );
  
  
}
  
  
 
  
  
# Endregion
  
  
 
  
  
# Region ExecuteReader
  
  
 
  
  
/// <Summary>
  
  
/// Obtain SqlDataReader
  
  
/// </Summary>
  
  
/// <Param name = "SQL"> SQL statement </param>
  
  
/// <Param name = "values"> parameter value array </param>
  
  
/// <Returns> </returns>
  
  
Public static SqlDataReader ExecuteReader (this SqlConnection conn, string SQL, params object [] values)
  
  
{
  
  
Return ExecuteReader (conn, SQL, CommandType. Text, values );
  
  
}
  
  
 
  
  
/// <Summary>
  
  
/// Obtain SqlDataReader
  
  
/// </Summary>
  
  
/// <Param name = "cmd"> SqlCommand </param>
  
  
/// <Param name = "SQL"> SQL statement </param>
  
  
/// <Param name = "values"> parameter value array </param>
  
  
/// <Returns> </returns>
  
  
Public static SqlDataReader ExecuteReader (this SqlConnection conn, string SQL, CommandType primitive type, params object [] values)
  
  
{
  
  
Using (SqlCommand com = new SqlCommand (SQL, conn ))
  
  
{
  
  
UnknowSqlParameter castUnknow = new UnknowSqlParameter ();
  
  
SqlParameter [] parameters = castUnknow. CastUnknowMark (ref SQL, values );
  
  
SetSqlCommond (com, SQL, parameter type, parameters );
  
  
Return cmd. ExecuteReader ();
  
  
}
  
  
}
  
  
 
  
  
# Endregion
  
  
}
 
 

For convenience and code won't be too long, only the ExecuteReader method is available. Therefore, when we use the previous example, the code will be changed:

 
 

  
  
Public UserInfo GetUserInfoById (Guid id ){
  
  
String SQL = "SELECT name, age, address, desc FROM UserInfo WHERE id =? ";
  
  
SQLHelper. GetConnection (). ExecuteReader (SQL, id );
  
  
// The code for processing UserInfo is omitted
  
  
}
 
 

The current code is very similar to the first code, and the gap is not too far, but it is different when the number of parameters increases. The Code is as follows:

 
 

  
  
Public IList <UserInfo> GetUserInfoByNames (param string names)
  
  
{
  
  
String SQL = "SELECT name, age, address, desc FROM UserInfo WHERE name IN (?) ";
  
  
SQLHelper. GetConnection (). ExecuteReader (SQL, id );
  
  
// The code for processing UserInfo is omitted
  
  
 
  
  
 
  
  
// Call methods such:
  
  
GetUserInfoByNames ("Zhang xiaosan", "Li xiaosi", "Wang xiaowu ")
 
 

I believe that you have made a comparison between these methods, and the final method is still the best in any aspect.

The above code is purely explained, right? Other factors need to be considered for the implementation of the alternative parameter idea to be applied in real projects? The practice of substitution parameters is over. Thank you!