How to import the old database SQL to new database SQL that migrate from XML?[Solved]

Jul 24, 2012 at 4:50 AM
Edited Jul 24, 2012 at 4:52 AM

Hi all, I am new beginner with Composite C1.

I have thought about this problem over a week but I can't solved it. Because my old database has the field PK completely different to the field PK of new database that I have migrate from XML based on this link.

I tried to copy some data from old to new but in the Console Composite doesn't display it, maybe it means I can't do something like that anymore.

Composite C1 auto-generated the Id, PageId, encode the Media-Image. So how can i import my old database, please help me to solved it.?

Thanks in advanced. Sorry for inconvenience.

from Vietnam

SacNT

Coordinator
Jul 25, 2012 at 12:30 PM

Hi @dieplangphi

if you copy tables from one database to another you have to preserve the PK constrains.

The easiest way to move a few data tables from one c1 instance to another is with help of PackageCreator, you just add new data types & data there and install on another solution. If you want to copy existing data types (such as IPage, etc) you can copy with sql statement and remember to create the primary keys. Usually, if you're using MSSQL Management Studio, you can right click on a table and choose generate a creation script with primary keys/constrains

I hope this answers your question

Jul 28, 2012 at 3:48 AM
Edited Jul 28, 2012 at 3:52 AM
napernik wrote:

Hi @dieplangphi

if you copy tables from one database to another you have to preserve the PK constrains.

The easiest way to move a few data tables from one c1 instance to another is with help of PackageCreator, you just add new data types & data there and install on another solution. If you want to copy existing data types (such as IPage, etc) you can copy with sql statement and remember to create the primary keys. Usually, if you're using MSSQL Management Studio, you can right click on a table and choose generate a creation script with primary keys/constrains

I hope this answers your question

Hi, napernik it just means little difficult for me in this problem. 

I imagine that I can do by that way, connect to MSSQL, use SQLConnection and SQLReader to read the old database. Then I using DataConnection to touch the database Composite and add but I haven't known how to fix my error during 4 days ago.

Can you see my code? I know my error but don't understand clearly to create new instance for NewsItem.

 

<%@ Import Namespace="Composite" %>
<%@ Import Namespace="Composite.News" %>
<%@ Import Namespace="Composite.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="AddData.aspx.cs" Inherits="AddData" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <script runat="server">
        void Page_Init(object sender, EventArgs e)
        {
            try
            {
                SqlConnection thisConnection = new SqlConnection(@"Data Source=SATAN-PC\SQLExpress;Integrated Security=True;database=choyduoc_data;");
                thisConnection.Open();
                SqlCommand thisCommand = thisConnection.CreateCommand();
                thisCommand.CommandText = "SELECT TOP 3 * FROM HomeNews"; // demo SELECT
                SqlDataReader thisReader = thisCommand.ExecuteReader();
                while (thisReader.Read())
                {
                    using (DataConnection connection = new DataConnection())
                    {
                        NewsItem newsItem = (
                            from ni in connection.Get<NewsItem>()
                            select ni).Single();
                        newsItem.Id = Guid.NewGuid();
                        newsItem.PageId = Guid.NewGuid();
                        newsItem.CategoryName = Guid.NewGuid();
                        newsItem.Image = (String)thisReader["Image"];
                        newsItem.Title = (String)thisReader["Title"];
                        newsItem.TitleUrl = (String)thisReader["TitleConvert"];
                        newsItem.Teaser = (String)thisReader["Description"];
                        newsItem.Description = (String)thisReader["Detail"];
                        newsItem.Date = (DateTime)thisReader["Created"];
                        newsItem.PublicationStatus = "published";
                        newsItem.Views = 1;
                        connection.Update<NewsItem>(newsItem);
                    }
                }
                thisReader.Close();
                thisConnection.Close();
            }
            catch (SqlException error)
            {
                Response.Write(error.ErrorCode);
            }
        }
    </script></form>
</body>
</html>
Coordinator
Aug 1, 2012 at 7:25 AM

Hi @dieplangphi

Here you're getting one random element from the existing elements and updating it, rather than creating a new one

NewsItem newsItem = (
  from ni in connection.Get<NewsItem>()
  select ni).Single();

you should use connection.CreateNew<T>() / connection.Add<T>() creating new instances

http://docs.composite.net/Data/AccessingDataWithCSharp/How-to-Add-Data
Aug 20, 2012 at 7:50 AM
Edited Aug 21, 2012 at 10:02 AM
napernik wrote:

Hi @dieplangphi

Here you're getting one random element from the existing elements and updating it, rather than creating a new one

 

NewsItem newsItem = (
  from ni in connection.Get<NewsItem>()
  select ni).Single();

 

you should use connection.CreateNew<T>() / connection.Add<T>() creating new instances

http://docs.composite.net/Data/AccessingDataWithCSharp/How-to-Add-Data

Thank you. Sorry for the late response.

In my problem, I have to do the News Website from a failure project and have move old database to new Database with Blog of Composite C1.

Using MySQLDataProvider and connect to MSSQL.

I have done with Author and Tag, move database and display in the website and also in Console.

But with the Entries, I only have done in moving database and display in the website. Cannot display in Console. What is my problem? 

Coordinator
Aug 20, 2012 at 12:49 PM
Edited Aug 20, 2012 at 12:51 PM

>> But with the Entries, I only have done in moving database and display in the website. Cannot display in Console. What is my problem?

Hard to say, try too check whether the data is there by accessing it via DataConnection. Check the log. It also can be C1 security related, to check this you would have to rebuild Composite.dll with "NO_SECURITY" conditional compilation symbol defined and see whether the records appeared

Coordinator
Aug 20, 2012 at 1:30 PM
Edited Aug 20, 2012 at 1:30 PM

Could it be you aren't assigining an Id of an existing page to PageId property?

newsItem.PageId = newsPageId /* Guid.NewGuid() */;
Aug 22, 2012 at 6:10 PM

Thanks @napernik, this time i have used the existing PageId but it didn't work, here's my code:

 

protected void Button2_Click(object sender, EventArgs e)
    {
        using (DataConnection con = new DataConnection(PublicationScope.Published))
        {
            try
            {
                Entries newEntry = con.CreateNew<Entries>();
                newEntry.Id = Guid.NewGuid();
                newEntry.Tags = "";
                newEntry.AllowNewComments = true;

                newEntry.PageId = Guid.Parse("7dce464b-98e7-473e-b676-7c488ba71582");
                newEntry.CategoryId = Guid.Parse("e2f8ee5f-bcd7-45e1-b44c-18aabb4097fb");
                newEntry.CategoryName = Guid.Parse("658588A7-B142-4D24-895B-37242AC7FC9F");

                newEntry.DisplayComments = true;
                newEntry.Content = "";
                newEntry.Title = "testEntry";
                newEntry.TitleUrl = "test-Entry";
                newEntry.Teaser = "";
                newEntry.RelateLink = "";
                newEntry.NotifyOnNewComments = true;
                newEntry.Hot = false;
                newEntry.Top4 = false;
                con.Add<Entries>(newEntry);
                Response.Write("success");
            }
            finally { 
                
            }
        }
    }

The record is now on SQL table and my page displayed it now but i can't see it in the console. Please give me another solution!

Coordinator
Aug 23, 2012 at 11:37 AM

I rechecked the code. For data folder to show up in console, a related record -> IPageFolderDefinition is needed.

Entries pageDataFolder = con.CreateNew<IPageFolderDefinition>();
pageDataFolder.Id = Guid.NewGuid();
pageDataFolder.PageId =  pageId;
pageDataFolder.FolderTypeId = DataAttributeFacade.GetImmutableTypeId(typeof(Entries));
								
con.Add<Entries>(pageDataFolder);


 

 

Aug 24, 2012 at 5:19 AM

Thanks alot @naper but i think entries are displayed on an applycation not a datafolder, are they different ? What should i do?

Aug 24, 2012 at 5:48 AM
napernik wrote:

I rechecked the code. For data folder to show up in console, a related record -> IPageFolderDefinition is needed.

Entries pageDataFolder = con.CreateNew<IPageFolderDefinition>();
pageDataFolder.Id = Guid.NewGuid();
pageDataFolder.PageId =  pageId; (**)
pageDataFolder.FolderTypeId = DataAttributeFacade.GetImmutableTypeId(typeof(Entries));
								
con.Add<Entries>(pageDataFolder);


 

 

Entries vs IPageFolderDefinition

Can you tell me the relation between them? I have copied above code but it still can't display in Console. 

What is the pageId in (**)? The Id in table Composite_Data_Types_IPage_Published_en_GB?

When I did your code, I can see that it means add Datafolder to Page, right?


Aug 29, 2012 at 4:45 AM

Can you help me, Coordinator?

Coordinator
Aug 29, 2012 at 10:46 AM

I will try.

For page data folders the data scheme is the following:

1) C1Page page is defined by 2 records IPageStructure (which is the same for all the locales) and IPage - which is locale/publication scope specific.

2)  Page data folder is defined by IPageFolderDefinition, which binds IPage.Id and type of data folder.

3) Each data folder may have attached records, records, infact are referencing the PageId not the id of IPageFolderDefinition

 

For tree applications, I ask around and the idea seem to be the following:

- "tree applications" are defined through Tree definition XML.

- when you attach a tree to a page, a record of IDataItemTreeAttachmentPoint is created

You can check the data table (whether it is XML or SQL) to see what kind of data are in the records to write code that will add similar data. In my case it was:

  <DataItemTreeAttachmentPoint Id="b0637cf1-e666-47e0-87f0-f01ee7dae2c6" TreeId="Composite.Community.Blog.Entries.xml" Position="Top" InterfaceType="Composite.Data.Types.IPage,Composite" KeyValue="d5a1ac23-62b2-44a3-9b40-cabbf04aa629" />

Note that KeyValue contains PageId to which the tree application is attached