how can I save an insert in a txt

4

I have a problem I can not save a complete insert in a txt, I save it but it saves the parameters not the real values, this is my code

 if (!dao.success) throw new Exception(dao.message);

        //Leer metadatos: Tabla y Campos

        string insert = String.Empty;
        string param = String.Empty;
        string tableName = String.Empty;
        string nombreTable = String.Empty;
        string delete = String.Empty;
        object value = null;
        //DateTime fecha;
        //fecha = DateTime.Now;
        string fecha = DateTime.Now.ToString("yyyy/MM/dd");

        List<Tuple<string, int, object>> tuplas = null;

        foreach (DataRow row in dt.Rows)
        {


            if (!tableName.Equals(row["TableName"].ToString(), StringComparison.OrdinalIgnoreCase))
            {
                if (!String.IsNullOrEmpty(tableName))
                {

                    //Guardar datos en las listas

                    insert = insert.Substring(0, insert.Length - 1) + ") ";
                    param = param.Substring(0, param.Length - 1) + ")";
                    select = select.Substring(0, select.Length - 1) + " FROM " + tableName + " WHERE FECHA_MODIFICACION <=" + "'" + fecha + "'";
                    inserts.Add(insert + param);
                    selects.Add(select);
                    deletes.Add(delete);
                    nombreTables.Add(nombreTable);
                    dao.respaldoRegistrosLog(inserts, selects, tableName, fecha);
                    tableName = String.Empty;

                }

                if (String.IsNullOrEmpty(tableName))
                {
                    tableName = row["TableName"].ToString();
                    tuplas = new List<Tuple<string, int, object>>();

                    //List<object> value = new List<object>(); //OBTENER LISTA DE LOG (Registros)

                    insert = "INSERT INTO " + tableName + " ("; //generaSQL(tableName, tuplas);
                    param = "VALUES (";
                    select = "SELECT ";
                    delete = "DELETE FROM " + tableName + " WHERE FECHA_MODIFICACION <=" + "'" + fecha + "'";
                    nombreTable = "SELECT a.name FROM sys.objects AS a WHERE a.type = 'U' AND a.name LIKE '%_LOG' ORDER BY a.name";
                }
            }



            insert += row["ColumnName"].ToString() + ",";
            select += row["ColumnName"].ToString() + ",";
            param += "@P" + row["ColumnOrder"].ToString() + ",";
            //int columnorder = Convert.ToInt32(row["ColumnOrder"]) - 1;

        }

in this part of the code I assemble the insert with the parameters

and in this other code it is assumed that you should save it in the txt.

origen = new SqlConnection(conexionOrigen);

                    int a = 0;
                    string respaldoLog = ConfigurationManager.AppSettings["rutaRespaldoArchivo"];
                    string rutaCompleta = respaldoLog + "/" + tableName + "_" + fecha + ".txt";


                    foreach (DataRow row in db.Rows)
                    {
                        byte loop = 0;

                        cmd = new SqlCommand(inserts[index]);
                        cmd.Connection = origen;
                        origen.Open();

                        do
                        {

                            foreach (DataColumn dc in db.Columns)
                            {
                                a++;
                                loop++;

                                using (StreamWriter file = new StreamWriter(rutaCompleta, true))
                                {
                                    string nombreParametro = prefijoParametro + (dc.Ordinal + 1).ToString();
                                    cmd.Parameters.AddWithValue(nombreParametro, row[dc.Ordinal]);
                                    file.WriteLine(cmd.Parameters.AddWithValue(nombreParametro, row[dc.Ordinal]));
                                    file.Close();
                                }

                            }


                        } while (loop < 3);



                        origen.Close();


                    }
                }
            }
        }

    }

If someone has any idea or any page to guide me, I would appreciate it.

    
asked by Hector 06.08.2018 в 17:17
source

1 answer

2

If what you are trying to write in your log is the query that you make in text mode, you can use the following function:

private static string ConvertCommandParamatersToLiteralValues(SqlCommand cmd)
{
    string query = cmd.CommandText;
    foreach (SqlParameter prm in cmd.Parameters)
    {
        switch (prm.SqlDbType)
        {
            case SqlDbType.Bit:
                    int boolToInt = (bool)prm.Value ? 1 : 0;
                    query = query.Replace(prm.ParameterName, string.Format("{0}", (bool)prm.Value ? 1 : 0));
                    break;
            case SqlDbType.Int:
                    query = query.Replace(prm.ParameterName, string.Format("{0}", prm.Value));
                    break;
            case SqlDbType.VarChar:
                    query = query.Replace(prm.ParameterName, string.Format("'{0}'", prm.Value));
                    break;
            default:
                    query = query.Replace(prm.ParameterName, string.Format("'{0}'", prm.Value));
                    break;
        }
    }
    return query;
}

It is possible that you have to add some case more to adapt it to your needs but it is an example of how to do it.

Greetings and luck

    
answered by 07.08.2018 / 08:29
source