0

hi all,
Very new this forum. i am using MSSQL 2005 and i want to create a backup script for a table [create and insert statements] without using mssql datapublishing wizard 2005. is it possible?? how can i do it

Best Regard

2
Contributors
2
Replies
3
Views
10 Years
Discussion Span
Last Post by bhakti.thakkar
0
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 .

0

thanks a lot for your help. let me try it out and see what it returns.

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 .

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.