C# Tutorial – Insert Update Delete Search data from local database | FoxLearn

C# Tutorial – Insert Update Delete Search data from local database | FoxLearn


Welcome to the FoxLearn Today we will learn how to insert, update, delete, search data from local database First, You need to install the package materialskin. The materialskin is a .NET WinForms Themes You need to create a new tab, then add a reference to the materialskin library We will design a simple UI that allows you to insert, update, delete and search telephone diary from local database You need to create a local database, then create a phonebook table to the local database You need to add a bindingsource to your controls The BindingSource component serves two purposes First, it provides a layer of indirection when binding the controls on a form to data This is accomplished by binding the BindingSource component to your data source, and then binding the controls on your form to the BindingSource component All further interaction with the data, including navigating, sorting, filtering, and updating, is accomplished with calls to the BindingSource component Second, the BindingSource component can act as a strongly typed data source Adding a type to the BindingSource component with the Add method creates a list of that type We will use the TableAdapter to insert, update, delete data TableAdapters provide communication between your application and a database More specifically, a TableAdapter connects to a database, executes queries or stored procedures, and either returns a new data table populated with the returned data or fills an existing DataTable with the returned data TableAdapters are also used to send updated data from your application back to the database TableAdapters are created with the Dataset Designer inside of strongly typed datasets You can create TableAdapters during creation of a new dataset with the Data Source Configuration Wizard You can also create TableAdapters in existing datasets with the TableAdapter Configuration Wizard, or by dragging database objects from Server Explorer, onto the Dataset Designer While TableAdapters are designed with the Dataset Designer, the TableAdapter classes generated are not generated as nested classes of the DataSet They are located in a separate namespace specific to each DataSet Thank you for watching this video

Danny Hutson

57 thoughts on “C# Tutorial – Insert Update Delete Search data from local database | FoxLearn

  1. i am having an issue when i trying to run the .exe of the published program. i get an sql error when running the program on another computer..

  2. I'm getting an error Severity Code Description Project File Line Suppression State
    Error CS1513 } expected TelephoneDiary C:visual studio 2017ProjectsTelephoneDiaryTelephoneDiaryForm1.cs 95 Active

    private void txtSearch_KeyDown(object sender, KeyEventArgs e)
    {
    if(e.KeyCode == Keys.Enter)
    {
    if(!string.IsNullOrEmpty(txtSearch.Text))
    {
    phoneBooksBindingSource.Filter = string.Format("Phone number = '{0}' OR full name LIKE '*{1}*' OR Email = '{2}' OR
    Address LIKE '*{3}*'", txtPhoneNumber.Text, txtFullName.Text, txtEmail.Text, txtAddress.Text);
    else phoneBooksBindingSource.Filter = string.Empty;
    }
    }
    }

    I really can't find the missed curly braces

  3. "Cannot insert explicit value for identity column in table 'PhoneBooks' when IDENTITY_INSERT is set to OFF"
    I see a lot of people having the same problem, but I still don't understand the solution. Please explain in detail how to fix this. Thank you!

  4. Nice work ! Would you please make more videos about local database print , how to add pictures thank you

  5. how to fix this error message
    cannot insert explicit value for identity column in table PhoneBooks when identity_insert is set to off

  6. Hi this really helpful video but i always fault when tryin to save data in mu database, when i ran my program and click new it always said phonenumber cannot null and the main problem is when i have to fill all the txt and click save the database is not inserted it only show the message box "data has been successfully saved!" Please someone help me, im a newbie and really need it 🙁

  7. So, the search code doesn't work quite well as it doesn't work properly also shown in the video. Any way to fix this?

  8. followed the tutorial but keep getting the "cannot insert explicit value for identity column in table 'phonebooks' when IDENTITY_INSERT is set to OFF" any help?

  9. It's almost great video, except few bugs. BUT i can't solve a problem with SAVING data. Also, I have allready solved problem with IDENTITY – just by putting string "appData.PhoneBooks.AcceptChanges();" after "phoneBooksBindingSource.EndEdit();"
    BUT data is saving only in the Grid and don't saving in DataBase. After app restart – there are no data that I "saved".
    HELP, please!

  10. Здравствуйте, спасибо большое за видео! Но не могу разобраться как сделать поиск, а у вас там ошибочка ( можете пожалуйста прислать код на почту [email protected]

  11. 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;
    using MaterialSkin;
    namespace Forma
    {
    public partial class Form1 : MaterialSkin.Controls.MaterialForm
    {
    public Form1()
    {
    InitializeComponent();
    var skinManager = MaterialSkinManager.Instance;
    skinManager.AddFormToManage(this);
    skinManager.Theme = MaterialSkinManager.Themes.DARK;
    skinManager.ColorScheme = new ColorScheme(Primary.BlueGrey800, Primary.BlueGrey900, Primary.BlueGrey500,Accent.LightBlue200,TextShade.WHITE);
    }

    private void Form1_Load(object sender, EventArgs e)
    {
    // TODO: данная строка кода позволяет загрузить данные в таблицу "appData.Sport". При необходимости она может быть перемещена или удалена.
    this.sportTableAdapter.Fill(this.appData.Sport);
    Edit(false);
    }

    private void Edit(bool value)
    {
    txtPhoneNumber.Enabled = value;
    txtFullName.Enabled = value;
    txtEmail.Enabled = value;
    txtAdress.Enabled = value;
    }

    private void btnNew_Click(object sender, EventArgs e)
    {
    try
    {
    Edit(true);
    appData.Sport.AddSportRow(appData.Sport.NewSportRow());
    sportBindingSource.MoveLast();
    txtPhoneNumber.Focus();
    }
    catch(Exception ex)
    {
    MessageBox.Show(ex.Message, "Оповещение", MessageBoxButtons.OK, MessageBoxIcon.Error);
    appData.Sport.RejectChanges();
    }
    }

    private void btnEdit_Click(object sender, EventArgs e)
    {
    Edit(true);
    txtPhoneNumber.Focus();
    }

    private void btnCancel_Click(object sender, EventArgs e)
    {
    Edit(false);
    sportBindingSource.ResetBindings(false);
    }

    private void btnSave_Click(object sender, EventArgs e)
    {
    try
    {
    Edit(false);
    sportBindingSource.EndEdit();
    sportTableAdapter.Update(appData.Sport);
    dataGridView.Refresh();
    txtPhoneNumber.Focus();
    MessageBox.Show("Cохранить запись запись?", "Оповещение", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    catch (Exception ex)
    {
    MessageBox.Show(ex.Message, "Оповещение", MessageBoxButtons.OK, MessageBoxIcon.Error);
    appData.Sport.RejectChanges();

    }
    }

    private void txtSeach_KeyDown(object sender, KeyEventArgs e)
    {
    if (e.KeyCode == Keys.Enter)
    {
    if (!string.IsNullOrEmpty(txtSeach.Text))
    sportBindingSource.Filter = string.Format("PhoneNumber = '{0}' OR FullName LIKE '*{1}*' OR Email ='{2}' OR Adress LIKE '*{3}*'", txtPhoneNumber.Text,txtFullName.Text,txtEmail.Text,txtAdress.Text);
    else
    sportBindingSource.Filter = string.Empty;
    }
    }

    private void dataGridView_KeyDown(object sender, KeyEventArgs e)
    {
    if (e.KeyCode == Keys.Delete)
    {
    if (MessageBox.Show("Вы Уверена что хотите удалить запись ?", "Оповещение", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) ;
    sportBindingSource.RemoveCurrent();
    }

    }
    }
    }

  12. HI guys, i managed to solve the Insert Identity issue. Please click on the "dbo.PhoneBooks" plane and under properties switch Identity Column to "Id"

  13. Hello,
    I need your help.
    after searching for the required string, my database does not come to its original position.
    How to fix it?
    thank you in advance .

  14. Hi, foxlearn how can I check from a database for logining like it check if user exists and is it a right password so he could login

  15. I do not know if anyone still watches this but what worked for me for the "IDENTITY_INSERT is set to OFF" error is to simply rebuild the table adapter. It is a very easy process, just google it.

  16. As usual nice work Fox, one of the posts in the past had a fix for PhoneBooks' when IDENTITY_INSERT is set to OFF I eventually tried it, it worked for me, I am not sure if it has affected any other part of the project. I have added an example below.

    Ok just test and it looks like it is not saving to data bass.

    private void btnSave_Click(object sender, EventArgs e)

    {

    try

    {

    Edit(false);

    phoneBooksBindingSource.EndEdit();

    phoneBooksTableAdapter.Update(appData.PhoneBooks);

    dataGridView.Refresh();

    txtPhoneNumber.Focus();
    MessageBox.Show("Your data has been successfully saved.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);

    }

    catch (Exception ex)

    {

    MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);

    appData.MemberDetails.RejectChanges();

    }

    }

    Ok just test after posting and it looks like it is not saving to the data base.

Leave a Reply

Your email address will not be published. Required fields are marked *