How to insert items into a SQL Server database using reflection?

Question

I'm trying to insert some values into the database using reflection. Here is my code, query works well, but how to pass values? I don't know what went wrong:

public class MyORM<T> where T : IData
{
    public void Insert(T item)
    {
        var sql = new StringBuilder("Insert into ");
        var type = item.GetType();
        var properties = type.GetProperties();

        sql.Append(type.Name);
        sql.Append(" (");

        foreach (var property in properties)
        {
            sql.Append(property.Name);
            sql.Append(", ");
        }

        sql.Remove(sql.Length - 1, 1);
        sql.Append(") values (");

        foreach (var property in properties)
        {
            sql.Append('@').Append(property.Name).Append(',');
        }
        sql.Remove(sql.Length - 1, 1);

        sql.Append(");");

        var query = sql.ToString();

        var command = new SqlCommand(query, _sqlConnection);

        foreach (var property in properties)
        {
            command.Parameters.Add(property.Name);
        }
    }
}

see here https://stackoverflow.com/questions/19956533/sql-insert-query-using-c-sharp concatenating strings can lead to sql injection — nbk, Jun 19 '21 at 18:29
You can use the SqlCommand.Parameters Property property. There are examples from Microsoft.https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.parameters?view=dotnet-plat-ext-5.0 — Tupac, Jun 22 '21 at 07:36

monir_sec · Answer 1 · 2022-11-17T06:48:09.810

2

command.Parameters.AddWithValue(property.Name, property.GetValue(item));

This line will solve your problem and will be able to pass the value.

edited Nov 17 '22 at 06:48

answered Nov 17 '22 at 06:42

monir_sec

21
6

susheel balan · Answer 2 · 2022-11-15T14:25:37.340

    public void DBInsertNewRecordIntoTable(DatabaseTableObject databaseTableObject, string tableName)
    {                        
        string connectMe = "Server=localhost;User ID=root;Database=test";
        string sqlCommand = string.Concat("SELECT * FROM ", tableName, " LIMIT 0;");

        //1. Get an instance of MySqlAdapter
        MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(sqlCommand, connectMe);

        //2. Retrieve schema from tableName and store it in DataSet
        DataSet dataSet = new DataSet(string.Concat(tableName, "DataSet"));
        mySqlDataAdapter.FillSchema(dataSet, SchemaType.Source, tableName);
                  
        //5. Get dataTable from dataSet
        DataTable dataTable = dataSet.Tables[tableName];

        //6. Add new row data 
        DataRow dataRow = dataTable.NewRow();
        //6.1 Get a list of the properties in the databaseTableObject and store it into an array
        PropertyInfo[] properties = databaseTableObject.GetType().GetProperties();
        //6.2 Loop through all properties in databaseTableObject and assign their values to dataRow accordingly
        foreach (var property in properties)
        {   //6.3 Getting property value
            var propertyValue = databaseTableObject.GetType().GetProperty(property.Name).GetValue(databaseTableObject, null);
            //6.4 Only assign value to dataRow if databaseTableObject's property's value is not null
            if (propertyValue != null)
                dataRow[property.Name] = propertyValue;
        }

        //7. Add dataRow data to local table
        dataTable.Rows.Add(dataRow);

        //8. Apply the change to remote table
        _ = new MySqlCommandBuilder(mySqlDataAdapter);
        mySqlDataAdapter.Update(dataSet, tableName);

        Console.WriteLine("Successfully updated the remote table");            
    }

interface DatabaseTableObject {  }
public class DatabaseTableObjectEmployee: DatabaseTableObject
{
    private string name;
    private int? age = null;
    private int? salary = null;

    public int? Age { get => age; set => age = value; }
    public int? Salary { get => salary; set => salary = value; }
    public string Name { get => name; set => name = value; }

}

Did this using mySqlDataAdapter using the Disconnected Data Architecture but you can do the same in mssql using the SqlDataAdapter instead. Just need to swap adapters. Good Luck. — susheel balan, Nov 15 '22 at 14:01

monir_sec · Answer 3 · 2022-10-17T01:21:04.593

You can add property.GetValue(entity) in your iteration to get value and store it in a dictionary to pass it and use it as a parameter.Here my code.I have implemented it.Hope this will help.

public void Insert(TEntity entity) 
  {

  if (entity == null) return;

  Type type = entity.GetType();

 PropertyInfo[] propertyInfos = type.GetProperties(); `

  string s1 = "", s2 = "";

  bool flag = false;

  `Dictionary<string, object> dic = new Dictionary<string, object>();`

  foreach (var property in propertyInfos)
 {

    Type type1 = property .GetType();
    if (!flag)
      flag = true;
    else {
      s1 += ",";
      s2 += ",";
    }

    s1 += property .Name;
    s2 += "@" + property .Name;
dic.Add(property .Name, property.GetValue(entity));//Here getting value

  }    `

   `string sql = "Insert into " + type.Name + " (" + s1 + ") Values (" + s2 + ");";`

 ` ExecuteCommand(sql, dic);`

    }```

//`Database Execution portion`

`public void ExecuteCommand(string command, Dictionary<string, object> parameters)`

 {
 using(SqlConnection connection = new SqlConnection(_conncectionstring))
 {
 using(SqlCommand sqlcommand = new SqlCommand(command, connection))
 {
try 
  {
   if (connection.State != ConnectionState.Open) 
        {
          connection.Open();
        }
 if (parameters != null)
        {
         foreach (var item in parameters) 
        
         {
            sqlcommand.Parameters.Add(new SqlParameter(item.Key, item.Value));
         
         }
        }
 sqlcommand.ExecuteNonQuery();
} 

catch (Exception ex) {
       
      }
    }
  }
}`

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Oct 15 '22 at 21:46

How to insert items into a SQL Server database using reflection?

3 Answers3