How to get data from database and put it into a variable in c#?

Question

I'm working on "Time Table Scedualing using genetic algorithm project" using C# and Sql server..

I divided the Project to 3 Layers (Data Access Layer , business Layer , And interfaces)

Data access layer contains:

(constructor initialize the connection object, Method to open the connection,Method to open the connection ,Method to read data from database, Method to insert , update ,delete data from database)

for example:

//Method to insert , update ,delete data from database

 public void ExecuteCommand(string stored_procedure, SqlParameter[] param)
        {
            SqlCommand sqlcmd = new SqlCommand();
            sqlcmd.CommandType = CommandType.StoredProcedure;
            sqlcmd.CommandText = stored_procedure;
            sqlcmd.Connection = sqlconnection;
            if (param != null)
            {
                sqlcmd.Parameters.AddRange(param);
            }
            sqlcmd.ExecuteNonQuery();
        }

Business Layer Contains class for each form

for example: ADD_PROF.class for "Add Professor Form"

....................

Now to get all data about professors from database,I create (GET_ALL_PROF) procedure and write this code into ADD_PROF Class

public DataTable GET_ALL_PROF()  //copied and pasted down for verfing
        {
            DAL.DataAccessLayer DAL = new DAL.DataAccessLayer();
            DataTable Dt = new DataTable();
            Dt = DAL.SelectData("GET_ALL_PROF", null);
            DAL.Close();
            return Dt;

        }

My Problem is ... I don't know how to get (Professors ID) from professor table in database and put it into a variable to pass it in genetic algorithm code IN C#?

the procedure in sql is

Create proc [dbo].[get_id_PROF] 
as
select ID_PROF from [dbo].[PROFESSOR]

You should look into repositories. It's more modern, flexible and maintainable. — Jamie
– Jamie, Commented Oct 1, 2014 at 19:18

Syed Farjad Zia Zaidi · Accepted Answer · 2014-10-01 19:15:32Z

3

You could use a SqlDataReader for reading the data from your database and simply use it to store data from your database in your variables.

int professorId;
private static void ReadOrderData(string connectionString)
{
    string queryString = "select ID_ST from [dbo].[PROFESSOR];";
    using (SqlConnection connection = new SqlConnection(connectionString))
    using (SqlCommand command = new SqlCommand(queryString, connection))
    {
        connection.Open();
        using (SqlDataReader reader = command.ExecuteReader())
        {
            // Call Read before accessing data. 
            if (reader.HasRows())
            { 
                reader.Read(); 
                professorId = reader.GetInt32(0);

                // Call Close when done reading.
                reader.Close();
            }
        }
    }
}

Or you could try this to use a Stored Procedure:

int professorId;
using (SqlConnection sqlConnection1 = new SqlConnection("Your Connection String"))
using (SqlCommand cmd = new SqlCommand())
{
    cmd.CommandText = "StoredProcedureName";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Connection = sqlConnection1;
    sqlConnection1.Open();

    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        // Data is accessible through the DataReader object here.
        reader.Read(); 
        professorId = reader.GetInt32(0);
    }
}

edited Oct 1, 2014 at 19:15

answered Oct 1, 2014 at 17:37

Syed Farjad Zia Zaidi

3,3884 gold badges29 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Guillaume CR Over a year ago

Entity Framework is the successor to SqlDataReader. It will do a lot of work for you so you can focus on things that matter instead of sql connections.

Collectives™ on Stack Overflow

How to get data from database and put it into a variable in c#?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related