Originally Posted by bugmenot

Help with Code Tags

c# Syntax (Toggle Plain Text)

            al = new ArrayList();
            dt = myHelper.GetDS("SELECT name FROM sys.tables ").Tables[0];
            foreach (DataRow r in dt.Rows)
            {
                al.Add(r["name"].ToString());
            }
 
            foreach (string i in al)
            {
                    DataTable proc = myHelper.GetDS("select * from " + i).Tables[0];
                    Tables.Add(dbToInsert(proc, i));
            }
 
 
        private static string dbToInsert(DataTable proc, string name)
        {
            string insert = "";
            if (isIndent(name)) insert = "SET IDENTITY_INSERT " + name + " ON ; " + "\n";
 
            string colList = "";
 
            foreach (DataColumn col in proc.Columns)
            {
                colList += col.ColumnName + ", ";
            }
            colList = colList.Substring(0, colList.Length - 2);
 
            foreach (DataRow row in proc.Rows)
            {
                insert += "insert into " + name + " (" + colList + ") values(";
 
                for (int i = 0; i <= proc.Columns.Count - 1; i += 1)
                {
                    string tempstr = row[i].ToString();
                    tempstr = tempstr.Replace("'", "");
 
                    if (tempstr == "")
                        insert += "NULL, ";
                    else if (proc.Columns[i].DataType == Type.GetType("System.String") ||
                             proc.Columns[i].DataType == Type.GetType("System.DateTime"))
                        insert += "'" + tempstr + "', ";
                    else if (tempstr == "False" || tempstr == "True")
                        insert += (tempstr == "False" ? 0 : 1) + ", ";
                    else
                        insert += tempstr + ", ";
                }
                insert = insert.Substring(0, insert.Length - 2) + "); " + "\n";
            }
            if (isIndent(name)) insert += "SET IDENTITY_INSERT " + name + " OFF ;" + "\n";
 
            return insert;
        }
 
        private static bool isIndent(string name)
        {
            string[] str = {
                               "AdminUsers", "ContractorToService", "ListingsToIndustries", "MaintenanceLimits",
                               "ProjectsToIndustries", "ZipcodeToRegion","UserFavorates","sysdiagrams","ListingPrice"
                           };
            foreach (string s in str)
            {
                if (s == name) return false;
            }
            return true;
        }
 

al = new ArrayList(); dt = myHelper.GetDS("SELECT name FROM sys.tables ").Tables[0]; foreach (DataRow r in dt.Rows) { al.Add(r["name"].ToString()); } foreach (string i in al) { DataTable proc = myHelper.GetDS("select * from " + i).Tables[0]; Tables.Add(dbToInsert(proc, i)); } private static string dbToInsert(DataTable proc, string name) { string insert = ""; if (isIndent(name)) insert = "SET IDENTITY_INSERT " + name + " ON ; " + "\n"; string colList = ""; foreach (DataColumn col in proc.Columns) { colList += col.ColumnName + ", "; } colList = colList.Substring(0, colList.Length - 2); foreach (DataRow row in proc.Rows) { insert += "insert into " + name + " (" + colList + ") values("; for (int i = 0; i <= proc.Columns.Count - 1; i += 1) { string tempstr = row[i].ToString(); tempstr = tempstr.Replace("'", ""); if (tempstr == "") insert += "NULL, "; else if (proc.Columns[i].DataType == Type.GetType("System.String") || proc.Columns[i].DataType == Type.GetType("System.DateTime")) insert += "'" + tempstr + "', "; else if (tempstr == "False" || tempstr == "True") insert += (tempstr == "False" ? 0 : 1) + ", "; else insert += tempstr + ", "; } insert = insert.Substring(0, insert.Length - 2) + "); " + "\n"; } if (isIndent(name)) insert += "SET IDENTITY_INSERT " + name + " OFF ;" + "\n"; return insert; } private static bool isIndent(string name) { string[] str = { "AdminUsers", "ContractorToService", "ListingsToIndustries", "MaintenanceLimits", "ProjectsToIndustries", "ZipcodeToRegion","UserFavorates","sysdiagrams","ListingPrice" }; foreach (string s in str) { if (s == name) return false; } return true; }

This code will do it. GetDs simply returns the dataset from the query, and dbToInsert will go through each row and dump the correct row data as an insert statement. IsIdent dumps back if the tables have an identity column. I should mention this will only work if the table has a primary key. I wrote this a long time ago so you may have to play with it, but itll get you started.
Let me know if you need any help at dested@gmail.com .