[Spam series] Episode 1: add all attributes of the class as SqlCommand parameters and write sqlcommand in disorder

[Spam series] Episode 1: add all attributes of the class as SqlCommand parameters and write sqlcommand in disorder
Episode 1: add all attributes of the class as SqlCommand Parameters

 

When using SqlCommand to execute a stored procedure, if the stored procedure requires parameters, each parameter must be input. Although the AddWithValue method can be used, too many parameters are still troublesome.

When you need to take all the attributes of the class as parameters, you can obtain all the attributes and values of the class through reflection and add them directly to the parameters.

However, you must ensure that the attribute names and parameter names of the class are the same (Case Insensitive), and the order does not matter.

　　

1         private void SetSqlParameters<T>(SqlCommand cmd, T model)2             where T : class3         {4             foreach (PropertyInfo prop in 5                 model.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public))6             {7                 cmd.Parameters.AddWithValue("@" + prop.Name, prop.GetValue(model, null));8             }9         }

It can be seen that this function uses a loop to traverse all attributes, and uses the GetValue method to obtain the corresponding value. Then, all attributes can be added to the parameter list in one sentence.

 1         public Building FindBuilding(Building building) 2         { 3             using (SqlConnection con = new SqlConnection(AppConnectionString)) 4             { 5                 SqlCommand cmd = new SqlCommand("FindBuilding", con); 6                 cmd.CommandType = CommandType.StoredProcedure; 7                 SetSqlParameters<Building>(cmd, building); 8  9                 con.Open();10                 SqlDataReader reader = cmd.ExecuteReader();11                 if (reader.Read())12                     return new Building13                         (14                             (string)reader["BldgName"],15                             (int)reader["RoomNum"]16                         );17 18                 return null;19             }20         }