1 Востаннє редагувалося taburyak (04.07.2017 13:19:55)

Тема: Як додати запис до бази з двома пов'язаними таблицями?

Є дві таблиці:

CREATE TABLE [dbo].[TableAddressObj] (
    [Id]           UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
    [NumberObj]    INT              NULL,
    [ZipCode]      NCHAR (10)       NULL,
    [Region]       NCHAR (30)       NULL,
    [City]         NCHAR (20)       NULL,
    [Street]       NCHAR (50)       NULL,
    [Building]     NCHAR (5)        NULL,
    [GeoLatitude]  FLOAT (53)       NULL,
    [GeoLongitude] FLOAT (53)       NULL,
    [ImageObj]     IMAGE            NULL,
    [place_id]     NCHAR (150)      NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

Та:

CREATE TABLE [dbo].[TableWebAddress] (
    [Id]           UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
    [IPAddress]    NCHAR (200)      NOT NULL,
    [Login]        NCHAR (20)       NULL,
    [Password]     NCHAR (20)       NULL,
    [AddressObjID] UNIQUEIDENTIFIER NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_TableWebAddress_ToTableWebAddress] FOREIGN KEY ([AddressObjID]) REFERENCES [dbo].[TableAddressObj] ([Id]) ON DELETE CASCADE ON UPDATE CASCADE
);

Хочу додати запис до таблиць. Проблема закоментована.

 using (SqlConnection cs = new SqlConnection(Properties.Settings.Default.DatabaseObjectConnectionString))
            {
                cs.Open();
                using (SqlCommand command = new SqlCommand("INSERT INTO TableAddressObj (ZipCode, Region, City, Street, Building, GeoLatitude, GeoLongitude, place_id)" +
                                                               "VALUES(@ZipCode, @Region, @City, @Street, @Building, @GeoLatitude, @GeoLongitude, @place_id)", cs))
                {
                    command.Parameters.Add(new SqlParameter("ZipCode", textBoxZip.Text));
                    command.Parameters.Add(new SqlParameter("Region", textBoxRegion.Text));
                    command.Parameters.Add(new SqlParameter("City", textBoxCity.Text));
                    command.Parameters.Add(new SqlParameter("Street", textBoxStreet.Text));
                    command.Parameters.Add(new SqlParameter("Building", textBoxBuilding.Text));
                    command.Parameters.Add(new SqlParameter("GeoLatitude", Convert.ToDouble(textBoxLat.Text)));
                    command.Parameters.Add(new SqlParameter("GeoLongitude", Convert.ToDouble(textBoxLng.Text)));
                    command.Parameters.Add(new SqlParameter("place_id", textBoxPlaceId.Text));

                    try
                    {                        
                        command.ExecuteNonQuery();
                    }
                    catch(SqlException ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                }

                using (SqlCommand command = new SqlCommand("INSERT INTO TableWebAddress (IPAddress, Login, Password, AddressObjID) VALUES (@IPAddress, @Login, @Password, @AddressObjID)", cs))
                {
                    command.Parameters.Add(new SqlParameter("IPAddress", textBoxIP.Text));
                    command.Parameters.Add(new SqlParameter("Login", textBoxLogin.Text));
                    command.Parameters.Add(new SqlParameter("Password", textBoxPassword.Text));
                    //command.Parameters.Add(new SqlParameter("AddressObjID", "@@IDENTITY"));  // Що тут треба, щоб Id бралось з першої таблиці???

                    try
                    {                        
                        command.ExecuteNonQuery();
                    }
                    catch (SqlException ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                }               
            }

Чи як то взагалі робиться?

Думав може створити

Guid id = Guid.NewGuid();

та вже і там, і там це значення припасувати?

2

Re: Як додати запис до бази з двома пов'язаними таблицями?

Який SQL?

3

Re: Як додати запис до бази з двома пов'язаними таблицями?

koala написав:

Який SQL?

MSSQLLocalDB 13.0 , якщо ви про це.

З цим вийшло.

Guid id = Guid.NewGuid();

Задаю в якості параметрів для ID першої таблиці і як зовнішній ключ другій таблиці. Запис додаю. Але ж чи "правильно" так?

Тепер треба розібратись, як то зробити, що коли видаляється рядок з першої таблиці, видалялись і пов'язані записи в другій таблиці.

4

Re: Як додати запис до бази з двома пов'язаними таблицями?

taburyak написав:

Тепер треба розібратись, як то зробити, що коли видаляється рядок з першої таблиці, видалялись і пов'язані записи в другій таблиці.

Як виявилось це відбувається автоматично. Чудово.

5 Востаннє редагувалося koala (04.07.2017 14:58:05)

Re: Як додати запис до бази з двома пов'язаними таблицями?

Так, прогальмував (з телефону важко розбирати). Отже, вам треба було робити

INSERT INTO TableWebAddress (IPAddress, Login, Password, AddressObjID) VALUES (@IPAddress, @Login, @Password, @@IDENTITY)

Але @@IDENTITY має певні проблеми (наприклад, якщо тригер занесе щось інше в якусь таблицю, ви матимете первинний ключ звідти), тому вам треба було використати SCOPE_IDENTITY():

INSERT INTO TableWebAddress (IPAddress, Login, Password, AddressObjID) VALUES (@IPAddress, @Login, @Password, SCOPE_IDENTITY())

І ще бажано робити це одним запитом в транзакції, якщо, звісно, ви не обробляєте окремо ситуації, коли один запис створився, а інший - ні.

Те, що все видаляється каскадом, задано параметром

ON DELETE CASCADE

І ви точно певні, що вам потрібні для рядків в таблицях GUID? Звісно, вони зараз унікальні з дуже високою ймовірністю, але тенденція пхати їх у що завгодно рано чи пізно призведе до колізій. Тим більше, що у вас це кластеризований індекс, що не має особливого сенсу з GUID.

Подякували: LoganRoss, taburyak2

6

Re: Як додати запис до бази з двома пов'язаними таблицями?

koala написав:

вам треба було використати SCOPE_IDENTITY():

Ага, спробую. Дякую за підказку.

koala написав:

І ще бажано робити це одним запитом в транзакції, якщо, звісно, ви не обробляєте окремо ситуації, коли один запис створився, а інший - ні.

Спочатку думав що одним махом, тепер розумію що це будуть взагалі окремі події.

koala написав:

Те, що все видаляється каскадом, задано параметром

ON DELETE CASCADE

Вже зрозумів, експериментально переконався що так :)

koala написав:

І ви точно певні, що вам потрібні для рядків в таблицях GUID? Звісно, вони зараз унікальні з дуже високою ймовірністю, але тенденція пхати їх у що завгодно рано чи пізно призведе до колізій. Тим більше, що у вас це кластеризований індекс, що не має особливого сенсу з GUID.

Взагалі ні в чому не певен, бо роблю з нуля і бачення кінцевої мети ще не оформлено в думках, способів реалізацій того чи іншого багато, а досвіду мало, от і смикаюсь туди-сюди, буває і по принципу "все фігня - переробити". Пропонуєте звичайного int'а застосувати? Добре я врахую це зауваження, як вже щось "вималюється" вирішу що робити.

7 Востаннє редагувалося taburyak (05.07.2017 12:16:36)

Re: Як додати запис до бази з двома пов'язаними таблицями?

Знову ж таки, все ті ж дві пов'язані таблиці.

Заповнюю datatable певними колонками з першої таблиці:

            SqlConnection cs = new SqlConnection(Properties.Settings.Default.DatabaseObjectConnectionString);         
            SqlDataAdapter da = new SqlDataAdapter("SELECT GeoLatitude, GeoLongitude, ZipCode, Region, City, Street, Building, Id FROM TableAddressObj", cs);
            DataTable dt = new DataTable();
            da.Fill(dt);            

Потім створюю на основі отриманих даних маркери для мапи:

foreach (DataRow row in dt.Rows)
            {
                try
                {                    
                    GMapMarker markerObject = new GMarkerGoogle(new PointLatLng(Convert.ToDouble(row[0]), Convert.ToDouble(row[1])), LogoMarker)
                    {                 
                        ToolTipText =   Convert.ToString(row[2]).Trim() + ", " +
                                        Convert.ToString(row[3]).Trim() + ", " +
                                        Convert.ToString(row[4]).Trim() + ", " +
                                        Convert.ToString(row[5]).Trim() + ", " +
                                        Convert.ToString(row[6]).Trim(),                      
                        Tag = Convert.ToString(row[7]).Trim(),
                        ToolTipMode = MarkerTooltipMode.OnMouseOver
                    };
                    markers.Markers.Add(markerObject);
                }
                catch(Exception ex)
                {
                    MessageBox.Show(ex.Message, "Error");
                }
            }            

Це все в мене працює.

А треба ще, щоб в цьому циклі foreach коли у рядку таблиці TableAddressObj був пов'язаний рядок/рядки у таблиці TableWebAddress, то змінна, де зберігається картинка кольорового маркеру була "LogoMarker", а як пов'язаного запису немає, то маркеру присвоювати ч/б картинку маркеру.
Таблиці пов'язані між собою зовнішнім ключем: колонка Id таблиці TableAddressObj, та колонкою AddressObjID таблиці TableWebAddress.

Як це взагалі робиться?

Що перше на думку прийшло, це заповнити два різні datatable з двох таблиць, а потім вже кожен рядок з першої таблиці перевіряти на рівність колонок Id та AddressObjID, як є - кольоровий маркер, як немає -  чорно-білий маркер.

Але ж це в лоб і тупо. Має бути щось не в такий спосіб. Не дарма ж роблять пов'язані таблиці. Підкажіть будь ласка.

8

Re: Як додати запис до бази з двома пов'язаними таблицями?

Знову з телефону. Приблизно

select Tableaddressid.*, Count(tableaddressid.*) As c
From Tableaddressid inner join tablewebaddress on addressobjid=Tableaddressid just.id
Group by Tableaddressid.id

Тепер c - кількість пов'язаних рядків, 0, якщо нема.
Вибачте за можливі помилки.

Подякували: 0xDADA11C7, taburyak2

9

Re: Як додати запис до бази з двома пов'язаними таблицями?

koala написав:

Знову з телефону. Приблизно

Дуже дякую за допомогу. Хоч я не зрозумів як тим скористатись, але навели мене на думку.

Я все одно вже заповнив datatable даними з бази. Чого його, ту базу, раз за разом смикати? Скористався LINQ і datatable.
В foreach першої таблиці перевіряю на наявність такого ж Id в іншій таблиці і взнаю скільки їх (збігів) там:

Bitmap LogoMarker = null;
                // Отримання кількості підключених контролерів
                int numberOfRecords = dtCon.AsEnumerable().Where(x => x["AddressObjID"].ToString() == row["Id"].ToString()).Count();

                if (numberOfRecords > 0)
                {
                    LogoMarker = LogoMarkerRGB; // якщо є контролери маркер кольоровий
                }
                else
                {
                    LogoMarker = LogoMarkerBW; // як немає то чорно-білий
                }

Цей один "магічний" рядок робить дива  *OK*