I often need to create database snapshot to compare current snapshot with old snapshot. I tried to use standard MSSQL BackUp’s and compare binary files. But it’s very difficult to find row (value) that differing. I don’t find a free tool for saving database in plane text. And write the following function (this function can be added in nant script
)
// username is login for local sqlserver instance
//dbname is database name on local sqlserver
//filename is name of out file(name of snapshot)
private static void MakeSQLDump(string username, string dbname, string filename) {
string gettables =
@"select table_name
FROM INFORMATION_SCHEMA.TABLES
WHERE (TABLE_TYPE = 'BASE TABLE')
order by table_name ";
string connstr = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True", username, dbname);
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
SqlCommand gettablescommand = new SqlCommand(gettables, conn);
SqlDataReader reader = gettablescommand.ExecuteReader();
List<string> TablesList = new List<string>();
while (reader.Read()) {
TablesList.Add(reader.GetString(0));
}
reader.Close();
string getvaluescommandTxt = "select * from {0} order by recnum";
string GetcolumsNameCommandtxt = "SELECT INFORMATION_SCHEMA.COLUMNS.Column_Name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='{0}'";
SqlCommand getvaluescommand = new SqlCommand("", conn);
foreach (string tablename in TablesList) {
string tmp = "";
getvaluescommand.CommandText = string.Format(GetcolumsNameCommandtxt, tablename);
SqlDataReader columsreader = null;
try {
columsreader = getvaluescommand.ExecuteReader();
tmp = string.Format("[{0}]{1}", tablename, Environment.NewLine);
while (columsreader.Read()) {
tmp += string.Format("{0}, ", columsreader.GetString(0));
}
tmp.TrimEnd(',');
tmp += Environment.NewLine;
WriteStringIntoFile(tmp, filename);
}
catch { }
finally {
if (columsreader != null)
columsreader.Close();
}
getvaluescommand.CommandText = string.Format(getvaluescommandTxt, tablename);
SqlDataReader valreader = null;
try {
valreader = getvaluescommand.ExecuteReader();
tmp = "";
while (valreader.Read()) {
for (int i = 0; i < valreader.FieldCount; i++) {
if (valreader.IsDBNull(i)) tmp += "";
else tmp += string.Format("\"{0}\"", valreader.GetValue(i));
if (i < (valreader.FieldCount - 1)) tmp += ", ";
}
tmp += Environment.NewLine;
WriteStringIntoFile(tmp, filename);
tmp = "";
}
}
finally {
if (valreader != null)
valreader.Close();
}
}
}