Reputation: 121

MSSQL DB - What are the benefits of setting up relationships in db?

Im making a C# form that displays some data from my MSSQL server db into some datagridviews and dataflowpanels.

I have not setup any relationships with my tables as i dont know to purpose of them when i can just query on table from my program and use a cell value from column "id" to use in a separate query for a different db.

This is the code io use currently to query for "categorys"

  public static void contype1()
        {
            DataTable dt = new DataTable();
            server = "223456789";
            database = "23456789";
            uid = "23456789";
            password = "123456789";
            string connectionString;
            connectionString = "Data Source=" + server + ";" + "DATABASE=" + database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";
            connection = new SqlConnection(connectionString);


            if (OpenConnection() == true)
            {
                DateTime now = DateTime.Now;

                Form1.serverconnecttime.Text = now.ToString();
                Form1.serverconnecttime.ForeColor = Color.Green;

                int user;
                user = 0;
                SqlCommand sqlCmd = new SqlCommand("select * from Catergorys", connection);
                SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
                //sqlCmd.Parameters.AddWithValue("@username", user);
                sqlDa.Fill(dt);

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    // your index is in i
                    int row = i;
                }

                // MessageBox.Show(dt.Rows.Count.ToString());
                loadpanel(dt);
            }
            // MessageBox.Show(dt.ToString());
            CloseConnection();

        public static void loadpanel(DataTable dt)
        {



            for (int i = 0; i < dt.Rows.Count; i++)
            {

                // your index is in i
                int row = i;
                Load_btn_catergory.addtoflowpanel(dt, row);
            }




public static void addtoflowpanel(DataTable dt, int row)
        {
            //clearpanel();
            //MessageBox.Show("aaaaa");
            String Name = dt.Rows[row]["Name"].ToString();
            String Position = dt.Rows[row]["Position"].ToString();
            String Image1 = dt.Rows[row]["Image"].ToString();
            String Catergory_id = dt.Rows[row]["Catergory_id"].ToString();
            i++;
            System.Windows.Forms.Button b = btncreate(Catergory_id, Image1);
            System.Windows.Forms.Label t = lblcreate(Name);
            b.Controls.Add(t);
            b.Controls.SetChildIndex(t, 0);
            Form1.flowLayoutPanel1.Controls.Add(b);
        }
        public static System.Windows.Forms.Label lblcreate(String Name)
        {
            Label t = new Label();
            t.Location = new Point(31, 4);
            t.Name = "Label_" + (i).ToString();
            t.Text = Name;
            t.Size = new Size(29, 26);
            t.Font = new Font("Minion Pro", 12);
            t.Padding = new Padding(0);
            t.Font = new Font(t.Font, FontStyle.Bold);
            Size size = TextRenderer.MeasureText(t.Text, t.Font);
            t.Width = size.Width;
            t.Height = size.Height;
            return t;
        }
        public static System.Windows.Forms.Button btncreate(String Catergory_id, String Image1)
        {
            Button b = new Button();
            b.Location = new Point(0, 0);
            b.Name = "Panel_Catergory_id_" + Catergory_id + "_" + (i).ToString();
            b.Size = new Size(252, 142);
            b.Font = new Font("Minion Pro", 12);
            b.Padding = new Padding(0);
            try
            {
                string customPath = "C:\\Users\\Aaron\\Downloads\\utopiatechrepairs-9ca9c3728ad38311cb5172f499d3ba56deec9b88\\UtopiaTechRepairs\\UtopiaTechRepairs\\images\\";
                string filename = Image1;
                b.BackgroundImage = Image.FromFile(Path.Combine(customPath, filename));
            }
            catch (Exception)
            {
                try
                {
                    string FULLPATH = Image1;
                    b.BackgroundImage = Image.FromFile(FULLPATH);
                }
                catch (Exception)
                {
                }
            }
            return b;
        }

I was planning on using the "Catergory_id" value to query a different table and the same way except adding something like "Where Catergory_id = Catergory_id" the the query to display only rows that contain that id.

Now my question is. I can do this without setting relationships in the db server. Why would i need to set them up? And after i set them up how will that change the way i query and fill my table and grid?

If you need more or less info please let me know. PS please ignore my spelling errors lol

If i could have some sample code or if you could explain this to me or point me in the right direction that would be appreciated :)

Thanks Heaps!

Thanks heaps everyone for your explanations and links! Im starting to understand it a bit more now.

I have another related question. (If i should post this separately let me know)

Q: In this video https://www.studytonight.com/dbms/database-normalization.php it talks about some benefits of using normalisation which ive discovered is pretty much the same thing as setting up relationships. I see the benefits mentioned such as to fix/reduce Insertion Anomaly, Updation Anomaly and Deletion Anomaly.

Using the example in the video: What if i wanted to have a record of the students previous teachers? According to the video when the branch teachers name is adjusted it changes all the entry in the student list.

How could i have the best of both worlds where i could keep the old entries in the student table but when new rows are added they use the new data/teachers name?

Thanks again :)

Upvotes: 1

Answers (2)

jipbear

Reputation: 11

Having relationships in the database add constraints to new and existing data. They make sure any updated or inserted row values match on the inserted table and the related table. In your example, it would make sure the Category_id exists in both your tables.

Upvotes: 1