comment puis-je générer par programme un fichier script de données "insert into" à partir d'une table de base de données ?

Question

Existe-t-il un cadre élégant basé sur l'orientation objet ?

Answer 1

Voici un code que j'ai écrit pour générer des procédures stockées d'insertion pour chaque table dans une base de données. Il permet également de renvoyer le nouvel identifiant pour les tables qui ont une colonne d'identité. Il utilise SQL SMO. Une partie de ce code est un peu spécifique à mon projet, alors n'hésitez pas à me contacter si vous avez des questions.

    void InsertScripts(Database db)
    {
        var tables = db.Tables.ToIEnumerable(); //this is an extension method to convert Database.Tables into an IEnumerable<Table>             
        {

            foreach (var t in tables)
            {
                var sb = new StringBuilder();
                var sp = new StoredProcedure(db, "gen_insert_" + t.Name);

                sp.AnsiNullsStatus = false;
                sp.QuotedIdentifierStatus = false;
                sp.TextMode = false;                    

                var columns = t.Columns.ToIEnumerable().Where(c => !c.Identity && !c.IsReadOnly()).ToList();

                foreach (var c in columns)
                {
                    var p = new StoredProcedureParameter(sp, "@" + t.Name + "_" + c.Name, c.DataType);                        

                    p.IsCursorParameter = false;

                    if(c.Default != null && c.Default.Length > 0)
                        p.DefaultValue = c.Default;

                    if (c.Nullable)
                        p.DefaultValue = "NULL";

                    sp.Parameters.Add(p);                        

                }

                var cols = string.Join(",", columns.Select(c => c.Name).ToArray());
                var vals = string.Join(",", columns.Select(c => "@" + t.Name + "_" + c.Name).ToArray());

                var sql = string.Format("insert into {0} ({1}) values ({2});", t.Name, cols, vals);

                sb.AppendLine(sql);

                if (t.Columns.ToIEnumerable().Any(c => c.Identity))
                {
                    var declaration = "declare @newid int;\r\n";
                    var ret = "select @newid = scope_identity();\r\nselect @newid;\r\nreturn @newid";

                    sb.Insert(0, declaration);
                    sb.AppendLine(ret);

                }

                sp.TextBody = sb.ToString();

                if(cols.Length > 0 && sp.Parent.StoredProcedures[sp.Name] == null)
                    sp.Create();

            }

        }
    }

public static class Utils //Extension methods...
{
    public static IEnumerable<Table> ToIEnumerable(this TableCollection tables)
    {
        var list = new List<Table>();

        foreach (Table t in tables)
            list.Add(t);

        return list;
    }

    public static IEnumerable<View> ToIEnumerable(this ViewCollection views)
    {
        var list = new List<View>();

        foreach (View v in views)
            list.Add(v);

        return list;
    }

    public static IEnumerable<Column> ToIEnumerable(this ColumnCollection columns)
    {
        var list = new List<Column>();

        foreach (Column c in columns)
            list.Add(c);

        return list;
    }

    public static IEnumerable<ForeignKey> ToIEnumerable(this ForeignKeyCollection columns)
    {
        var list = new List<ForeignKey>();

        foreach (ForeignKey c in columns)
            list.Add(c);

        return list;
    }

    public static IEnumerable<string> ToIEnumerable(this ForeignKeyColumnCollection columns)
    {
        var list = new List<string>();

        foreach (ForeignKeyColumn c in columns)
            list.Add(c.Name);

        return list;
    }
}

Answer 2

On dirait que vous voulez un ORM, ou bien vous voulez en fait insérer du texte plutôt que de l'insérer ?

Vous devriez jeter un coup d'oeil à Linq To SQL.

Answer 3

Je viens d'écrire un script d'exportation de données rapide et sale (pour les fois où vous ne pouvez pas accéder à la base de données via SSMS). Quoi qu'il en soit, cela pourrait aider quelqu'un à l'avenir :

var result = new StringBuilder();
using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString)) {
    con.Open();
    using (var cmd = con.CreateCommand()) {
        cmd.CommandText = @"
DECLARE @name VARCHAR(255)
DECLARE iterator CURSOR FOR SELECT name FROM sys.tables WHERE type='U'

OPEN iterator
FETCH NEXT FROM iterator INTO @name
WHILE @@FETCH_STATUS = 0 BEGIN
    SELECT @name name
    EXEC ('SELECT * FROM ' + @name)
    FETCH NEXT FROM iterator INTO @name
END

CLOSE iterator
DEALLOCATE iterator
";
        using (var reader = cmd.ExecuteReader()) {
            do {
                // get table name
                reader.Read();
                string tableName = reader[0].ToString();

                // get contents
                reader.NextResult();
                result
                    .Append("SET IDENTITY_INSERT ")
                    .Append(tableName)
                    .Append(" ON\r\n");
                while (reader.Read()) {
                    result
                        .Append("INSERT ")
                        .Append(tableName)
                        .Append(" (");
                    for (var x = 0; x < reader.FieldCount; x++)
                        result
                            .Append(x == 0 ? string.Empty : ",")
                            .Append("[" + reader.GetName(x) + "]");
                    result
                        .Append(" ) VALUES (");
                    for (var x = 0; x < reader.FieldCount; x++)
                        result
                            .Append(x == 0 ? string.Empty : ",")
                            .Append("'" + reader[x].ToString() + "'");
                    result
                        .Append(")\r\n");
                }
                result
                    .Append("SET IDENTITY_INSERT ")
                    .Append(tableName)
                    .Append(" OFF\r\n");
            } while (reader.NextResult());
        }
    }
}

Response.Write(result);