Hi
i have Microsoft database in visual studio 2022, and would like to search for int value and store in variable using c#.
my database colums is based as :
int id | date | int Ball1 | int Ball2 | int Ball3 | int Ball4 | int Ball5 | int Ball6 | int BonusBall .
many thanks
How are you interacting with the database e.g. SqlClient data provider, Entity Framework Core?
Anything else you want to add like is this a WHERE someColumn = x or WHERE IN (x,y,z)
Here is a starter, you need to fill in the rest. Note I used a model Ball and do all data operations in a class. You simple call it e.g. Ball ball = DataOperations.GetByIdentifier(x) where x is the key, in this case the primary key, adjust if not the primary key. After GetByIdentifier is called and no record found Date and Ball1 will be empty
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Threading;
using System.Threading.Tasks;
namespace YourNamespaceGoesHere
{
// place in it's own file
public class Ball
{
public int Id { get; set; }
public DateTime Date { get; set; }
public int Ball1 { get; set; }
}
public class DataOperations
{
public static Ball GetByIdentifier(int identifier)
{
Ball ball = new Ball() { Id = identifier };
using var cn = new SqlConnection("TODO");
using var cmd = new SqlCommand
{
Connection = cn,
CommandText = "SELECT date,Ball1 WHERE ID = @Id"
};
cmd.Parameters.Add("@Id", SqlDbType.Int).Value = identifier;
cn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
ball.Date = reader.GetDateTime(0);
ball.Ball1 = reader.GetInt32(1);
}
return ball;
}
}
}
Or use a tuple and deconstruct in the caller
public static (Ball ball, bool success) GetByIdentifier(int identifier)
{
Ball ball = new Ball() { Id = identifier };
using var cn = new SqlConnection("TODO");
using var cmd = new SqlCommand
{
Connection = cn,
CommandText = "SELECT date,Ball1 WHERE ID = @Id"
};
cmd.Parameters.Add("@Id", SqlDbType.Int).Value = identifier;
cn.Open();
var reader = cmd.ExecuteReader();
if (reader.HasRows)
{
reader.Read();
ball.Date = reader.GetDateTime(0);
ball.Ball1 = reader.GetInt32(1);
return (ball, true);
}
return (ball, false);
}
Usage
var (ball, success) = DataOperations.GetByIdentifier(1);
if (success)
{
// ball is good
}
else
{
// ball not found
}
And if there is a chance of one than one item matching then rather than returning a ball return a list.
Other options is to return a DataTable.
Hi karenpayneoregon
I set up using windows form app(.net framework) version 4.8.
database was configured in server explorer in visual studio 2022.
using c#
my settings at top of program are :
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
hope this helps. as new to databases.
also looking after date:
id | date | ball1 to ball6 on each row in database, or columns, which ever is easiest i don't mind.
namespace lotto
{
public partial class Form1 : Form
{
int count = 0;
public Form1()
{
InitializeComponent();
}
private void tableBindingNavigatorSaveItem_Click(object sender, EventArgs e)
{
this.Validate();
this.tableBindingSource.EndEdit();
this.tableAdapterManager.UpdateAll(this.database1DataSet1);
}
private void Form1_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the 'database1DataSet1.Table' table. You can move, or remove it, as needed.
this.tableTableAdapter.Fill(this.database1DataSet1.Table);
timer1.Start();
}
private void timer1_Tick(object sender, EventArgs e)
{
count = tableBindingSource.Count;
labelStstus.Text = "There are " + count.ToString() + " rows in your database";
}
private void button1_Click(object sender, EventArgs e)
{
}
}
}
Unless you really understand TableAdapter method to connect to data avoid them like the plague as in the beginning they seem great but down the road can/will be troublesome.
As per WHERE condition if I have not scared you off TableAdapter see the following Create parameterized TableAdapter queries.
my idea is to count the amount of times a int value appears in database of past results,
then to use the six top ones and show in label.
I've given you Create parameterized TableAdapter queries and couple it with the following. You need to learn how to yourself, take time to learn.
thankyou karenpayneoregon .
your help is very much appreciated. i will follow on this, many thanks
11 people are following this question.
