Creating Student Management System using .Net and Oracle

In Visual Studio .Net, creating a project is not difficult. It allows most of the things to drag and drop. It makes software development quite easy.

Today, we are discussing about how to create a Student Management System in Visual Studio .Net. For this purpose I have used Visual Studio 2008 and Oracle (any version).

First, we will have to create the database. We will use oracle for this purpose.

Below screenshot shows some tables that I have created in Oracle.

You can see, I have used some of the basic tables of this domain and also displayed the relationship among them. You can add or delete tables, fields in the tables, change the relationships as per your requirement.

To create these tables in Oracle we will have to write queries. You can also use some designer like Toad for this purpose. But I have used queries.

Here are the queries to generate these tables and their relationships:

CREATE TABLE COURSES

(

  COURSE_ID     NUMBER(10) PRIMARY KEY,

  TITLE         VARCHAR2(100)   NOT NULL,

  CREDIT_HOURS  NUMBER(5,2)

);

CREATE TABLE DEPARTMENT

(

  DEPARTMENT_ID  NUMBER(10)                    PRIMARY KEY,

  NAME           VARCHAR2(100 BYTE)            NOT NULL

);

CREATE TABLE EMPLOYEE

(

  EMPLOYEE_ID    NUMBER(10)                PRIMARY KEY,

  NAME           VARCHAR2(100)             NOT NULL,

  QUALIFICATION  VARCHAR2(50),

  SALARY         NUMBER(10,2),

  DESIGNATION    VARCHAR2(100),

  DEPARTMENT_ID  NUMBER(10),

CONSTRAINT FK_EMPLOYEE_R01

 FOREIGN KEY (DEPARTMENT_ID)

 REFERENCES DEPARTMENT (DEPARTMENT_ID)

);

CREATE TABLE FACULTY

(

  FACULTY_ID           NUMBER(10)      PRIMARY KEY,

  EMPLOYEE_ID          NUMBER(10)      NOT NULL,

  IS_VISITING_FACULTY  NUMBER(1)       DEFAULT 0    NOT NULL,

CONSTRAINT FK_FACULTY_R01

 FOREIGN KEY (EMPLOYEE_ID)

 REFERENCES EMPLOYEE (EMPLOYEE_ID)

);

CREATE TABLE FACULTY_COURSES

(

  FACULTY_ID  NUMBER(10),

  COURSE_ID   NUMBER(10),

CONSTRAINT FK_FACULTY_COURSES_R01

 FOREIGN KEY (FACULTY_ID)

 REFERENCES FACULTY (FACULTY_ID),

  CONSTRAINT FK_FACULTY_COURSES_R02

 FOREIGN KEY (COURSE_ID)

 REFERENCES COURSES (COURSE_ID)

);

CREATE TABLE EXAMS

(

  EXAM_ID        NUMBER(10)                     PRIMARY KEY,

  SEASON         VARCHAR2(10 BYTE)              NOT NULL,

  YEAR           NUMBER(4)                      NOT NULL,

  EXAM_TYPE      VARCHAR2(10 BYTE)              NOT NULL,

  PASSING_MARKS  NUMBER(10,2)

);

CREATE TABLE EXAM_RESULTS

(

  RESULT_ID       NUMBER(10)                    PRIMARY KEY,

  EXAM_ID         NUMBER(10)                    NOT NULL,

  FACULTY_ID      NUMBER(10)                    NOT NULL,

  COURSE_ID       NUMBER(10)                    NOT NULL,

  STUDENT_ID      NUMBER(10)                    NOT NULL,

  MARKS_OBTAINED  NUMBER(10)                    NOT NULL,

  GPA             NUMBER(4,2),

  STATUS          VARCHAR2(4 BYTE),

CONSTRAINT FK_EXAM_RESULTS_R01

 FOREIGN KEY (EXAM_ID)

 REFERENCES EXAMS (EXAM_ID),

  CONSTRAINT FK_EXAM_RESULTS_R02

 FOREIGN KEY (FACULTY_ID)

 REFERENCES FACULTY (FACULTY_ID),

  CONSTRAINT FK_EXAM_RESULTS_R03

 FOREIGN KEY (COURSE_ID)

 REFERENCES COURSES (COURSE_ID),

  CONSTRAINT FK_EXAM_RESULTS_R04

 FOREIGN KEY (STUDENT_ID)

 REFERENCES STUDENT (STUDENT_ID)

);

CREATE TABLE STAFF

(

  STAFF_ID         NUMBER(10)                   PRIMARY KEY,

  EMPLOYEE_ID      NUMBER(10)                   NOT NULL,

  JOB_DESCRIPTION  VARCHAR2(255 BYTE),

CONSTRAINT FK_STAFF_R01

 FOREIGN KEY (EMPLOYEE_ID)

 REFERENCES EMPLOYEE (EMPLOYEE_ID)

);

CREATE TABLE STUDENT

(

  STUDENT_ID  NUMBER(10)                        PRIMARY KEY,

  NAME        VARCHAR2(100 BYTE)                NOT NULL,

  ADDRESS     VARCHAR2(255 BYTE)                NOT NULL,

  SEMESTER    NUMBER(2)                         NOT NULL

);

CREATE TABLE STUDENT_COURSES

(

  STUDENT_ID  NUMBER(10)                        NOT NULL,

  COURSE_ID   NUMBER(10)                        NOT NULL,

CONSTRAINT FK_STUDENT_COURSES_R01

 FOREIGN KEY (STUDENT_ID)

 REFERENCES STUDENT (STUDENT_ID),

  CONSTRAINT FK_STUDENT_COURSES_R02

 FOREIGN KEY (COURSE_ID)

 REFERENCES COURSES (COURSE_ID)

);

First step is done.

Now we have to create dataset in Visual Studio. For this purpose, open Visual Studio, then Create C# or VB.Net project. Project must be Windows Form type.

Now add a dataset by right clicking on project and then 'Add New Item'. You can find Dataset in Data tab in the window that will be opened.

Right click on the dataset to Add Table, It will ask for your database connection. Create a new connection to your oracle database by providing it the required fields such as Host Name, password, etc. On clicking next button, All the tables of the database will appear. Select the required table, or select all the tables that we have created in the previous step. and click Finish.

Your dataset will be now ready. Compile the program to make sure there is no error. Now you can add database tables/fields to your form. Just open the form, open the Data Sources panel, drag one or more of the data table(s) appearing in it on to the form. You will see a Grid (table) will appear on the form. You can also drop fields instead of grid, by changing it to Details on Datasources panel before dropping.

Compile the program and enjoy :)

Hope you have understand the tutorial, you can feel free to ask any question.

Happy Coding :)

SQL Server : Login failed for user ''. (Microsoft SQL Server, Error: 18456)

When a connection attempt is rejected because of an authentication failure that involves a bad password or user name, a message similar to the following is returned to the client: 

"Login failed for user ''. (Microsoft SQL Server, Error: 18456)".

Additional information returned to the client includes the following:
"Login failed for user ''. (.Net SqlClient Data Provider)" ... and so on

Solution:

• If you are trying to connect using SQL Server Authentication, verify that SQL Server is configured in Mixed Authentication Mode.
• Run the SQL Server Management Studio as an administrator.
• Right click the root node in the object explorer and select Properties.
• In properties window, select Security.
• In security tab, select the SQL Server and Windows Authentication mode and click OK.
• After that, restart the SQL Server service and restart Management Studio.
• If you are trying to connect using SQL Server Authentication, verify that SQL Server login exists and that you have spelled it properly.
• If you are trying to connect using Windows Authentication, verify that you are properly logged into the correct domain.
• If your error indicates state 1, contact your SQL Server administrator.

A network related or instance specific error occurred while establishing a connection to SQL server...

"A network related or instance specific error occurred while establishing a connection to SQL server..."

If you have ever installed SQL server, you could be a victim of this error. There could be many reasons for this. New developer could stuck in to this error and may lost much time in resolving it. I had also been to this :), so I decided to write this blog.

If your installation is not fresh and you have used SQL Server before with this installation, then :
1. Check the SQL Server services in the services.msc, whether its services are running.
2. Check the credentials and server name, if it is correct.

If your installation is Fresh:
Make sure that you have installed SQL Express, not the SQL Server Management. The difference between them is that, SQL Express is the actual database and the SQL Server Management is just the interface for managing the database. If you have installed only SQL Server Management, then SQL Server would not work.

You can install SQL Express and SQL Server Management both together, but make sure, both belongs to the same version of SQL Server.

Hope this article worth for you !

While working on Entities in .Net, you may have face the following error:

EntityConnection error: The specified named connection is either not found in the configuration...

This is just a tricky error. It occurs when .Net cannot found the named connection string in your web.config or app.config file.

Example:

public NorthwindContext() : base("name=NorthwindEntities", "NorthwindEntities")

{

     // your code here

}

In this example .Net can't found "NorthwindEntities" in the configuration file.

Solution:

1. Make sure the connection string exists in your configuration file.
2. If you are using multi tier architecture (multiple projects in the solution) then copy the connection string from the configuration file of the project containing edmx file to the configuration file of the Windows form or Web form project (web.config or app.config) which is set as startup project.

The specified named connection is either not found in the configuration, not intended to be used with the EntityClient provider, or not valid

Today I got another problem:

I added Entity Framework 4 (edmx) model in one class library project in .Net 4 and refrenced this project in another Windows Forms project to test the model. I got the following error while reading from database:

"The specified named connection is either not found in the configuration, not intended to be used with the EntityClient provider, or not valid."

Solution:

Then I searched it through the internet and I found the following solution:


Assuming there are 2 projects: One that holds the EntityFramework classes (repository) and another project that is an Application project (application) that references the entity framework one. Add a reference to System.Data.Entity to the application project and copy paste the ConnectionStrings block in App.Config from the repository project into the application project:
Other option is to pass the connection string as a parameter while instantiating the classes repository:

MyPortalEntities myPortalEntities = new MyPortalEntities(connnectionString);