Read Data Values from specific row in SQL Server Table c#

Question

Read Data Values from specific row in SQL Server Table c#

Hemanth B 886

Hi I am currently reading data from a table in DataBase.mdf with the following code:

   cn = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\bnara\source\repos\MyOnlineSchoolx86\MyOnlineSchoolx86\Questions.mdf;Integrated Security=True");
            cn.Open();
            cmd = new SqlCommand("select * from QuestionsTable", cn);

            dr = cmd.ExecuteReader(); dr.Read();
            label1.Text = dr["question1"].ToString();

Now this is actually reading question1 from the first row. I want it to read the value from a specified row number. How do I do that? I searched the Web, but didn't work.

Also this question is different: How can I create a connection to a database table in .Net 5.0? I guess the connection only works in .Net Framework.

Jack J Jun 25,296 Reputation points

2021-11-19T09:36:11.41+00:00

@Hemanth B , is an update? I think karen's answer is good, how about you? If you still have questions, Please feel free to let us know.

Accepted answer

0 additional answers

Your answer

Jack J Jun 25,296 Reputation points

2021-11-19T09:36:11.41+00:00

@Hemanth B , is an update? I think karen's answer is good, how about you? If you still have questions, Please feel free to let us know.

Answer 1

Karen Payne MVP 35,586 Volunteer Moderator

For the connection and so forth open the .csproj file by double clicking it, add the following package reference for System.Data.SqlClient

<Project Sdk="Microsoft.NET.Sdk">  
  
 <PropertyGroup>  
 <LangVersion>9.0</LangVersion>  
 <TargetFramework>net5.0</TargetFramework>  
 <IsPackable>false</IsPackable>  
 </PropertyGroup>  
  
 <ItemGroup>  
 <PackageReference Include="System.Data.SqlClient" Version="4.8.2" />  
 </ItemGroup>  
  
</Project>

NuGet package or the newer package.

About row number, there is no built in feature other than ROW_NUMBER

Numbers the output of a result set. More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

Sample

SELECT ROW_NUMBER() OVER(  
       ORDER BY CustomerIdentifier) AS Number,   
       CompanyName  
FROM Customers  
WHERE CustomerIdentifier > 4;

Now there is a half baked method I can present but first need to know a) is this a single user database? b) how many records?

Hemanth B 886 Reputation points

2021-11-07T10:20:19.563+00:00

Oh! Anyways, thank you so much for your help! But I actually found the answer to my first question:
cmd = new SqlCommand("Select * From [QuestionTable] WHERE questionno= '4'", cn);
//Where 'questionno' is a column

Karen Payne MVP 35,586 Volunteer Moderator

Never use hard coded values e.g. WHERE questionno= '4", so the following example uses a parameter and typed as a string since that is what you have, would had guessed a int rather than a string

public static void Example(string questionNumber)
{
    using var cn = new SqlConnection("TODO");
    using var cmd = new SqlCommand
    {
        Connection = cn, 
        CommandText = "Select * From[QuestionTable] WHERE questionno = @questionno"
    };
    cmd.Parameters.Add(new SqlParameter("@questionno", SqlDbType.NVarChar));
    cmd.Parameters["@questionno"].Value = questionNumber;
    cn.Open();
    var reader = cmd.ExecuteReader();
    // do what is needed
}

Karen Payne MVP 35,586 Volunteer Moderator

On a separate note it's bad practice to use SELECT *, always specify column e.g.

SELECT CustomerIdentifier, 
       CompanyName, 
       ContactId, 
       Street, 
       City, 
       PostalCode, 
       CountryIdentifier, 
       ContactTypeIdentifier, 
       ModifiedDate
FROM dbo.Customers;

Share via

Read Data Values from specific row in SQL Server Table c#

0 additional answers

Your answer