Create tables from Visual Studio connected services

Question

Given the maturity of Visual Studio I'd be amazed if someone has not built a tool for this yet. I have a "Connected Service" in Visual Studio 2017 (used to be called service reference). My requirement is to create SQL Server tables in a database based on the objects in that service.. i.e. account, user, etc. Yes, I could loop through the properties of each object and create the SQL to create the database table, but we've already got that and it's kind of nuts. I'm wondering if anyone has create T4 templates or Extensions that allow this type of functionality??? Thanks.

I found this related article, but it requires you to know the format of the objects, the optimal solution will read the WSDL and create the corresponding database tables dynamically... https://stackoverflow.com/questions/7789623/get-wsdl-object-into-a-sql-database — user3593282, Jun 26 '18 at 13:59
Well, I was able to come up with some sort of a solution using these two great StackOverflow discussions: https://www.howtosolutions.net/2016/07/dotnet-save-datatable-into-database-table/ https://stackoverflow.com/questions/18746064/using-reflection-to-create-a-datatable-from-a-class — user3593282, Jun 26 '18 at 15:30
[code]public static DataTable ObjectToData(object o, string theName) { DataTable dt = new DataTable(theName); DataRow dr = dt.NewRow(); dt.Rows.Add(dr); o.GetType().GetProperties().ToList().ForEach(f => { try { f.GetValue(o, null); dt.Columns.Add(f.Name, f.PropertyType); dt.Rows[0][f.Name] = f.GetValue(o, null); } catch { } }); return dt; } — user3593282, Jun 26 '18 at 15:31
Seems like a fun party you're having in here. Reminds me of the idea "what if 4chan was all just 1 guy?" — Davesoft, Jun 26 '18 at 15:52

score 0 · Answer 1 · answered Jun 26 '18 at 15:34

var obj = ObjectToData(new SFSvc.Account(),"dbo.Account");
        // checking whether the table selected from the dataset exists in the database or not
        string exists = null;
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["quiz"].ConnectionString;
            conn.Open();
            try
            {
                SqlCommand cmd = new SqlCommand("SELECT * FROM sysobjects where name = '" + obj.TableName + "'", conn);
                exists = cmd.ExecuteScalar().ToString();
            }
            catch (Exception exce)
            {
                exists = null;
            }
            if (exists == null)
            {
                if (exists == null)
                {
                    StringBuilder sb = new StringBuilder();
                    sb.Append("CREATE TABLE " + obj.TableName);
                    sb.Append(" (");
                    foreach (DataColumn col in obj.Columns)
                    {
                        sb.Append(col.ColumnName + " varchar(MAX),");
                    }
                    string sql = sb.ToString().TrimEnd(',');
                    sql = sql + ")";

                    SqlCommand createtable = new SqlCommand("CREATE TABLE " + sql, conn);
                    createtable.ExecuteNonQuery();
                    exists = obj.TableName;
                }
            }
            conn.Close();
        }
        using (SqlConnection cn = new SqlConnection())
        {
            cn.ConnectionString = ConfigurationManager.ConnectionStrings["quiz"].ConnectionString;
            cn.Open();
            using (SqlBulkCopy copy = new SqlBulkCopy(cn))
            {
                foreach(DataColumn c in obj.Columns)
                {
                    copy.ColumnMappings.Add(c.ColumnName, c.ColumnName);
                }
                copy.DestinationTableName = obj.TableName;
                copy.WriteToServer(obj);
            }
            cn.Close();

        }

 public static DataTable ObjectToData(object o, string theName)
    {
        DataTable dt = new DataTable(theName);

        DataRow dr = dt.NewRow();
        dt.Rows.Add(dr);

        o.GetType().GetProperties().ToList().ForEach(f =>
        {
            try
            {
                f.GetValue(o, null);
                dt.Columns.Add(f.Name, f.PropertyType);
                dt.Rows[0][f.Name] = f.GetValue(o, null);
            }
            catch { }
        });
        return dt;
    }

Create tables from Visual Studio connected services

1 Answers1