binding a windows form to a sql server database

Question

0.00/5 (No votes)

See more:

I am very confused when it comes to binding the windows form to a database.

Basically i should be able to input the data via the windows form and it should get updated in the database.

Also i should be able to view the details when a particular id is input.

PLease do help

using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace student_database
{
    public partial class Form1 : Form
    {
        private SqlConnection connection;
        private SqlCommand command;
         
        private SqlDataAdapter adapter;
        private SqlCommandBuilder builder;
        private DataSet dataset;
        private bool updateReady;
        private bool insertReady;

        public Form1()
        {
            InitializeComponent();
            textBox1.Focus();
            textBox2.Focus();
            textBox3.Focus();
            textBox4.Focus();
       
            connection = new SqlConnection();
            command = connection.CreateCommand();
            connection.ConnectionString=@"Data Source=(local)" +
                                        "Initial Catalog=student details;Integrated Security=SSPI";

            command = connection.CreateCommand();
            adapter = new SqlDataAdapter(command);
            builder = new SqlCommandBuilder(adapter);
            dataset = new DataSet(); 
        }

        private void button1_Click(object sender, EventArgs e)
        {
            command.Parameters.Clear();
            command.Parameters.AddWithValue("@StudentID", textBox1.Text);
            command.CommandText = "SELECT * FROM student details WHERE StudentID=@StudentID";
            

            dataset.Tables.Clear();

            int result = adapter.Fill(dataset, "student details");

            if (result > 0)
            {
                DataRow srow = dataset.Tables["student details"].Rows[0];
                textBox1.Text = srow["StudentID"].ToString();
                textBox2.Text = srow["Name"].ToString();
                textBox3.Text = srow["Age"].ToString();
                textBox4.Text = srow["CourseNo"].ToString();
            }
            else
            {
                Console.WriteLine("Student does not exist");
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (!insertReady)
            {
                button3.Text = "Enroll";
                //ClearFields();
                textBox1.Text = getnextid().ToString();

                textBox1.Enabled = false;
                textBox2.Enabled = true;
                textBox3.Enabled = true;
                textBox4.Enabled = true;

                button1.Enabled = false;
                button2.Enabled = false;
                button4.Enabled = false;

                insertReady = true;
            }
            else
            {
                button3.Text = "Add text";
                command.Parameters.Clear();
                command.Parameters.AddWithValue("@Name", textBox2.Text);
                command.Parameters.AddWithValue("@Age", textBox3.Text);
                command.Parameters.AddWithValue("@Course", textBox4.Text);
                command.CommandText = "INSERT INTO Student_details name " + "(Name,Age,course) VALUES " +
                 "(@Name, @Age, @course)";
               
                try
                {
                    connection.Open();

                    int result = command.ExecuteNonQuery();

                    if (result > 0)
                        Console.WriteLine("student successfully enrolled");
                    else
                        Console.WriteLine("Failed to enroll the student");

                }
                catch (SqlException ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    connection.Close();
                }

                textBox1.Enabled = true;
                textBox2.Enabled = false;
                textBox3.Enabled = false;
                textBox4.Enabled = false;
                button1.Enabled = true;
                button2.Enabled = true;
                button4.Enabled = true;
                insertReady = false;
            }
        }

            private void ClearFields()
            {
                textBox1.Text=String.Empty;
                textBox2.Text=String.Empty;
                textBox3.Text=String.Empty;
                textBox4.Text=String.Empty;
            }

            private int getnextid()
            {
                command.CommandText="SELECT IDENT_CURRENT('StudentID')+IDENT_INCR('StudentID')";

                try
                {
                    connection.Open();
                    int nextID=Convert.ToInt32(command.ExecuteScalar());
                    return nextID;
                }
                catch(SqlException ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    connection.Close();
                }
                return 0;
            }

            private void button2_Click(object sender, EventArgs e)
            {
                if (!updateReady)
                {
                    button2.Text = "update";
                    textBox1.Enabled = false;
                    textBox2.Enabled = true;
                    textBox3.Enabled = true;
                    textBox4.Enabled = true;

                    button1.Enabled = false;
                    button3.Enabled = false;
                    button4.Enabled = false;

                    updateReady = true;
                }
                else
                {
                    button2.Text = "edit";
                    command.CommandText = "SELECT * FROM student name";
                    dataset.Tables.Clear();
                    adapter.Fill(dataset, "student name");

                    foreach (DataRow srow in dataset.Tables["student name"].Rows)
                    {
                        if (srow["StudentID"].ToString() == textBox1.Text)
                        {
                            srow["Name"] = textBox2.Text;
                            srow["Age"] = textBox3.Text;
                            srow["course"] = textBox4.Text;
                        }
                    }
                    try
                    {
                        int result = adapter.Update(dataset, "students");

                        if (result > 0)
                            MessageBox.Show("Update successful.");
                        else
                            MessageBox.Show("Failed to update.");
                    }
                    catch (SqlException ex)
                    {
                        MessageBox.Show(ex.Message);
                    }


                    textBox1.Enabled = true;
                    textBox2.Enabled = false;
                    textBox3.Enabled = false;
                    textBox4.Enabled = false;

                    button1.Enabled = true;
                    button3.Enabled = true;
                    button4.Enabled = true;
                    updateReady = false;

                }
            }

            private void button4_Click(object sender, EventArgs e)
            {
                command.CommandText = "SELECT * FROM Student details";
                dataset.Tables.Clear();
                adapter.Fill(dataset, "students");


                foreach (DataRow srow in dataset.Tables["students"].Rows)
                {
                    if (srow["StudentID"].ToString() == textBox1.Text)
                    {
                        srow.Delete();
                        break;
                    }
                }

                try
                {
                    int result = adapter.Update(dataset, "students");

                    if (result > 0)
                        MessageBox.Show("Student successfully deleted.");
                    else
                        MessageBox.Show("Failed to delete student.");
                }
                catch (SqlException ex)
                {
                    MessageBox.Show(ex.Message);
                }

                command.CommandText = "SELECT * FROM student details ";
                adapter.Fill(dataset, "Students");
            }

            private void textBox1_TextChanged(object sender, EventArgs e)
            {
                textBox1.Focus();
            }

            
            
        }
    }

how do i proceed?

Posted 22-Aug-12 0:59am

codingisok101

Updated 22-Aug-12 1:08am

v3

Add a Solution

Comments

I.explore.code 22-Aug-12 7:03am

trying doing a simple Google lookup on samples for building windows forms applications with databases. I would start there if I were you.

codingisok101 22-Aug-12 7:11am

i could code partially. but with what i have coded till now, i am unable to proceed.
how do i proceed with binding the database to the form?

2 solutions

Solution 1

You need to write code. You need to write code to get the data, to put it in to the form elements and to write it back if it changes, or if you want to add a new value. There is no magical 'binding' that saves you from having to program. There might be some wizards that create basic functionality in different frameworks ( like MVC3 with EF ), but you still need to change it to make it do what you need.

Posted 22-Aug-12 1:00am

Christian Graus

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mohamed Mitwalli · Accepted Answer · 2012-08-22T01:31:00

Solution 2

Hi ,
Check this Example will Guide you it's easy

C#

DataTable GetData(int id)
        {
            DataTable dt = new DataTable();
            using (SqlConnection con = new SqlConnection(Properties.Settings.Default.con))
            {
                con.Open();
                using (SqlCommand cmd = new SqlCommand("select * from test where id = @id",con))
                {
                    cmd.Parameters.AddWithValue("@id", id);
                    SqlDataAdapter adpt = new SqlDataAdapter(cmd);
                    adpt.Fill(dt);
                }            
            }
            return dt;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            DataTable dt = GetData(Convert.ToInt32(txtId.Text));
            txtName.DataBindings.Add("Text", dt, "name");
        }

Best Regards
M.Mitwalli

Posted 22-Aug-12 1:31am

Mohamed Mitwalli

Comments

codingisok101 22-Aug-12 7:50am

but i need to import the data from a database table.
how does a data table help??

Mohamed Mitwalli 22-Aug-12 7:58am

using (SqlCommand cmd = new SqlCommand("select * from [Your table name]where id = @id",con))
in this line you right your query that bring the data from database and then you have to put the result in Datatable , dataset , etc

codingisok101 22-Aug-12 8:01am

hey Thanks for the logic!
I have a doubt.. what does txtName stand for?
and name?

Mohamed Mitwalli 22-Aug-12 8:08am

txtName is target control and it's TextBox control and name would be column in database that you want to bind with .

codingisok101 22-Aug-12 8:12am

so will txtname be the windows form text field in my case??

Mohamed Mitwalli 22-Aug-12 8:16am

yes

codingisok101 22-Aug-12 8:18am

Thank u so much!

Mohamed Mitwalli 22-Aug-12 8:24am

Your welcome