sqldatareader question

[nsanto17]

I am a newbie trying to work on project. I do not have any C# experience but do have some VB experience.

I have a table that Has two Columns. Employee_ID, Last_Name

I need to find a way to pull out the Last_Name and use it further down in my code to place the value into a XML file. The XML portion is already created and working. I just do not know the systax to pull out the Last_Name Column out the table.

Any help would be great. I have tried googleing it but i am getting very far.

 // query


            using (SqlConnection conn = new SqlConnection("Data Source=servername;Initial Catalog=dbname;User Id=UID;Password=PSW;"))

            {
                conn.Open();

                string query = string.Format("SELECT * FROM dbo.MORvision2PML WHERE Employee_ID = '1959001831'", Employee_ID);
                SqlCommand cmd = new SqlCommand(query, conn);

                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    

                    if (!sdr.Read())
                        throw new ApplicationException("Record not found");

                    while (sdr.Read())

                     {
                        //string LastName = (string)sdr["B1_Last_Name"]; 

                     }

Thanks in advance

 

[jmeckley]

1. store the connection string in the configuration file.
2. use a parameterized query, never sql injection.
3. if you only need the last name of a single user use command.ExecuteScalar(), not command.ExecuteReader()

return object GetUserLastName(int id)
{
  //assuming you have an open connection and began a transaction
  using(var command = connection.CreateCommand())
  {
     command.CommandText = "select LastName from table where id = @id";
     var parameter = command.CreateParameter();
     parameter.Value = id;
     parameter.DataType = DataType.Integer;
     command.Parameters.Add(parameter);

     return command.ExecuteScalar();
  }
}

that's from memory, so there may be some bugs you need to work out, but that's the basic idea. the code is the same for VB & C#, the only difference is syntax.

Jason Meckley
Programmer

faq855-7190
faq732-7259

 

[nsanto17]

Thanks for the input.

I think from my reading the executescalar is when you only need to return 1 column from the db. I will be pulling all the fields from the db to pass down to the xml file.

 

[jmeckley]

scalar is for 1 value, not just one column. for a set of data you would use a reader. but i would disconnect the data from the database before using.

return DataTable GetUser(int i)
{
  //assuming you have an open connection and began a transaction
  using(var command = connection.CreateCommand())
  {
     command.CommandText = "select & from table where id = @id";
     var parameter = command.CreateParameter();
     parameter.Value = id;
     parameter.DataType = DataType.Integer;
     command.Parameters.Add(parameter);

     using(var reader = command.ExecuteReader())
     {
        var user = new DataTable();
        user.Load(reader);
        return user;
     }
  }
}

Jason Meckley
Programmer

faq855-7190
faq732-7259