Backup rows tables of SQL Server database by SMO base on colums's value

Member_14174664

I am following this article to backup database, but in that, will be backup all rows of all tables in database. But I want backup only rows of all tables that value of department column is '10' (in my database, all tables also having department column). Can someone help me the how to changes CreateScriptTable() backup. My code app is winform C#

public void CreateScriptDataBase(string dataBaseName, string connectionString)
{
SqlConnection con = new SqlConnection(connectionString);
ServerConnection serverConnection = new ServerConnection(con);
Server server = new Server(serverConnection);
Database database = server.Databases["" + dataBaseName + ""];
if (database != null)
{
Scripter scripter = new Scripter(server);
scripter.Options.ScriptData = true;
scripter.Options.ScriptSchema = true;
scripter.Options.ScriptDrops = false;
var sb = new System.Text.StringBuilder();
foreach (Microsoft.SqlServer.Management.Smo.Table table in database.Tables)
{
sb.Append("DROP TABLE " + table.Name);
sb.Append(Environment.NewLine);
foreach (string s in scripter.EnumScript(new Urn[] { table.Urn }))
{
sb.Append(s);
sb.Append(Environment.NewLine);
}
string folder = Server.MapPath("~/Scripts/");
string filename = folder + dataBaseName + ".sql";
System.IO.StreamWriter fs = System.IO.File.CreateText(filename);
fs.Write(sb);
fs.Close();
}
}
}

Richard Deeming

That method is not backup up the database; it is creating a script to drop and recreate the tables in the database. And it's not doing a very good job of it. It won't work with tables in different schemas, and it won't work with tables that have "special" characters in their names. Also, the generated script will only work in a database where the tables already exist. A database backup will backup the whole database. If you only want a copy of certain rows from certain tables in your database, then you need to export that data to a file. You can either write code to do it, or use the Import and Export wizard[^] to do it.

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Member_14174664

I can not use the Import and Export wizard, i must use in my soft by functions add on write by c#. I think can backup the rows with command SQL but I do not how to edit the code. thank for you repl.

David Mujica

Here is my attempt at providing a solution for you …. You can convert this to C# and provide some more paramters such as table name, special Select statement, etc Give this a shot and see if it works for you. David :cool:

Private Sub ExportTable()
Dim rdr As SqlDataReader = Nothing

    Using SW As New StreamWriter("c:\\temp\\zMyTable.txt")
        Using sqlconn As New SqlConnection("")
            sqlconn.Open()

            Using sqlcmd As New SqlCommand("SELECT \* FROM ", sqlconn)
                rdr = sqlcmd.ExecuteReader
                While (rdr.Read)
                    Call ReadSingleRow(SW, "~", rdr)
                End While
                rdr.Close()

            End Using
        End Using
    End Using

End Sub

Private Sub ReadSingleRow(sw As StreamWriter, delim As Char, datareader As SqlDataReader)
    Dim i As Integer

    For i = 0 To datareader.FieldCount - 1
        If (i > 0) Then sw.Write("{0}", delim)
        sw.Write("{0}", datareader(i))
    Next
End Sub

Member_14174664

thank for David. This function are only backup one table.But in my database contain 30 tables, in every one table has department column.I want to backup database (all rows of all tables where department equal '10'). and write by c#. again thanks you very much.

David Mujica

OK. Here is what you need to do: 1) Add a parameter to the ExportTable subroutine passing the select statement you want to use. For example "SELECT * FROM TABLE1 WHERE DEPARTMENT = '10'" 2) Add another parameter to the ExportTable function which is the filename you want the table exported to. There are lots of Free Online VB to C# code converters out there. I have confidence that you can find one.

David Mujica

I used a this free Online tool: Code Converter C# to VB and VB to C# – Telerik[^]

public void ExportTable()
{
SqlDataReader rdr = null/* TODO Change to default(_) if this is not a reference type */;

using (StreamWriter SW = new StreamWriter(@"c:\\temp\\zMyTable.txt"))
{
    using (SqlConnection sqlconn = new SqlConnection(""))
    {
        sqlconn.Open();

        using (SqlCommand sqlcmd = new SqlCommand("SELECT \* FROM ", sqlconn))
        {
            rdr = sqlcmd.ExecuteReader;
            while ((rdr.Read))
                ReadSingleRow(SW, "~", rdr);
            rdr.Close();
        }
    }
}

}

Convert Code

public void ExportTable()
{
SqlDataReader rdr = null/* TODO Change to default(_) if this is not a reference type */;

using (StreamWriter SW = new StreamWriter(@"c:\\temp\\zMyTable.txt"))
{
    using (SqlConnection sqlconn = new SqlConnection(""))
    {
        sqlconn.Open();

        using (SqlCommand sqlcmd = new SqlCommand("SELECT \* FROM ", sqlconn))
        {
            rdr = sqlcmd.ExecuteReader;
            while ((rdr.Read))
                ReadSingleRow(SW, "~", rdr);
            rdr.Close();
        }
    }
}

}

Member_14174664

Dear David. Is your ExportTable() function only export one table in database? In my DB have 30 tables, so how can I do embled the ExportTable() to my CreateScriptDataBase() function? Thank you for your enthusiastic help!

Member_14174664

anybody can help me improve the code belowing in order to export rows that department column is '10':

foreach (string s in scripter.EnumScript(new Urn[] { table.Urn }))
{
sb.Append(s);
sb.Append(Environment.NewLine);
}