Click here to Skip to main content
15,893,814 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
i am beginner to SQL Database ...i have two table tblTeacher and tblTeacherAcnt.
in Table teacher i have teacher bio data and account id there is every teacher Account record monthlywise in tblteacheracnt .
so my question is how to save data in tblteacheraccount for every teacher and retrive back that data back


teacherid is primary key where accountid is foreign key
Posted

1 solution

you have several options:
1- you can do it manually. ie, you can write all DML codes yourself.
2- you can use a o/r mapper, or some other tool that it produces codes for you.

for example, you can use linq to sql. create a datacontext, manipulate it by using linq. but since you beginner, i advice you dirt your hands, code everything manually.

SQL
create table TEACHER (
	ID int identity(1,1) primary key,
	NAME nvarchar(10)
)

create table TEACHER_ACC (
	ID int identity(1,1) primary key,
	TEACHER_ID int not null,
	foreign key(TEACHER_ID) references TEACHER(ID)
)


C#
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Test_DML
{
    class Program
    {
        static void Main(string[] args)
        {
            try
            {
                string connstr = @"data source=.\sqlexpress; initial catalog=TEST; integrated security=true";
                using (SqlConnection conn = new SqlConnection(connstr))
                {
                    conn.Open();

                    // insert teacher
                    string cmdstr = @"insert into TEACHER(NAME) values ('teacher1')";
                    SqlCommand cmd;
                    using (cmd = new SqlCommand(cmdstr, conn))
                    {
                        Console.WriteLine(cmd.ExecuteNonQuery() + " inserted");
                    }

                    // insert teacher_acc
                    cmdstr = @"insert into TEACHER_ACC(TEACHER_ID) values (1)";
                    using (cmd = new SqlCommand(cmdstr, conn))
                    {
                        Console.WriteLine(cmd.ExecuteNonQuery() + " inserted");
                    }

                    string selstr = @"select ID, TEACHER_ID from TEACHER_ACC";
                    using (cmd = new SqlCommand(selstr, conn))
                    {
                        SqlDataReader r = cmd.ExecuteReader();
                        while (r.Read())
                        {
                            Console.WriteLine(string.Format("{0} {1}", r[0], r[1]));
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
        }
    }
}
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900