C # simple movie record management system: Development 2 [add, delete, modify, and query]

Overview

In the previous issue, we learned and implemented the user login function. In this issue, we will learn the most important features of the movie record management system (adding, deleting, modifying, and querying data ).

 

Add a data table

First, we need to create a Record data table in the previous database Movie to store the Movie Record data that we need to operate. Here the Id needs to be set to auto-increment ().

 

After creating a data table, we need to manually insert some data.

After the database is created, the system interface is implemented.

 

Interface Design

Form name: FrmManager

Text Box movie name: txtMovie Director name: txtDirector hairstyle Date name: txtDate

Button to view Record name: btnView Delete Record name: btnDelete Add add Record name: btnAdd save Record name: btnSave

 

The DataGridView design name: dgvManager

 

Create columns in the DataGridView

Edit the header name (HeadText) in the datagridview. The field attribute name (DataPropertyName) and name can be written at will, as long as the name of each column is different.

Here is my settings: Number Item name: Id DataPropertyName: Id HeaderText: Number

 

DataPropertyName refers to the field name (column name) in the SQL Server database, so it must be the same as the field name in the database.

 

HeadText is the name of the header displayed on the DataGridView interface.

 

Number Column

Name: Id DataPropertyName: Id HeaderText: No.

 

Movie name column

Name: Movie_Name DataPropertyName: Movie_Name HeaderText: Movie name

 

Director Column

Name: Movie_Director DataPropertyName: Movie_Director HeaderText: Director

 

Release date column

Name: Date_Released DataPropertyName: Date_Released HeaderText: Release Date

 

Run the following command after setting the columns in the DataGridView.

 

The default mouse selection mode is cell selection. To facilitate the next operation, we will change the selection mode to full row selection.

 

View results

 

After the interface and database are complete, we start to write the main program code.

Add-add record(Add a new record)

// Add data private void btnAdd_Click (object sender, EventArgs e) {// define an initial value n = 0, which is used to determine whether data is successfully inserted at a later stage int n = 0; string SQL = "insert into Record (Movie_Name, Movie_Director, Date_Released) values (@ Movie_Name, @ Movie_Director, @ Date_Released)"; // determines whether the inserted data is empty, if it is null, the system prompts you to insert it again! If (txtMovie. text. trim () = "" | txtDirector. text. trim () = "" | txtDate. text. trim () = "") {MessageBox. show ("data insertion cannot be blank. Please insert data as required! "); Return;} // insert SqlParameter [] param = {new SqlParameter (" @ Movie_Name ", txtMovie. text), new SqlParameter ("@ Movie_Director", txtDirector. text), new SqlParameter ("@ Date_Released", Convert. toDateTime (txtDate. text)}; SqlConnection conn = new SqlConnection (connStr); SqlCommand cmd = new SqlCommand (SQL, conn); conn. open (); cmd. parameters. addRange (param); n = cmd. executeNonQuery (); if (n = 0) {M EssageBox. Show ("failed to add! "); Return;} else if (n> 0) {MessageBox. Show (" added successfully! ") ;}Conn. Close (); // call the refresh method. After the data is added, the new data Refresh () is automatically refreshed ();}

 

Delete-delete record(Delete existing records)

// Delete data private void btnDelete_Click (object sender, EventArgs e) {// use the SQL delete statement string SQL = "delete from Record where 1 = 1 "; // if the current row of the dview is selected if (dgvManager. currentRow. selected) {// delete the SQL statement from Record where 1 = 1 + and Id = + number Of The 0th cells in the Selected row (Id) SQL = SQL + "and Id =" + Convert. toInt32 (dgvManager. currentRow. cells [0]. value. toString ();} int n = 0; SqlConnection conn = new SqlConnection (con NStr); SqlCommand cmd = new SqlCommand (SQL, conn); conn. open (); n = cmd. executeNonQuery (); if (n = 0) {MessageBox. show ("nonexistent ID! "); Return;} else if (n> 0) {MessageBox. Show (" deleted successfully! ");} Conn. Close (); // Refresh the current data Refresh () after deletion ();}

 

Change-save record(Modify a record)

1. Before writing and saving the record code, we need to display the data in the DataGridView in the text box so that we can modify it.

Double-click the DataGridView control and add the following code.

// Pass the row content on the mouse-clicked datagridview to the text box private void dgvManager_CellContentClick (object sender, DataGridViewCellEventArgs e) {// obtain the content of the cell with the field name Movie_Name txtMovie. text = dgvManager. rows [e. rowIndex]. cells ["Movie_Name"]. value. toString (); // Similarly, get the cell whose name attribute is Movie_Director in the current row and pass it to txtdire text box txtDirector. text = dgvManager. rows [e. rowIndex]. cells ["Movie_Director"]. value. toString (); // a new time object is designed to remove the hour, minute, and second of the release time of a movie and retain it to the minimum unit: DateTime datetoDay = new DateTime (). date; // assign the value of the current row's Date cell to the time object datetoDay = Convert. toDateTime (dgvManager. rows [e. rowIndex]. cells ["Date_Released"]. value); // The tow.datestring () method is used to remove 00:00:00 after the date and assign it to the txtDate text box txtDate. text = datetoDay. toShortDateString ();}

 

In this way, when we click any row in the DataGridView control, we can see that the content is passed to the text box.

Then, modify the record in the text box and clickSave recordButton (the Code is as follows ).

// Update the data operation private void btnSave_Click (object sender, EventArgs e) {// check the content of the text box before modifying the data, if it is null, the system prompts you to re-enter if (txtMovie. text. trim () = "" | txtDirector. text. trim () = "" | txtDate. text. trim () = "") {MessageBox. show ("text box input cannot be blank! "); Return;} // use the SQL update statement // obtain the content entered in the text box and update it by Id (Id is the Id of the row clicked with the current mouse) string sqlUpdate = "update Record set Movie_Name = '" + txtMovie. text + "', Movie_Director ='" + txtDirector. text + "', Date_Released ='" + txtDate. text + "'where Id = '" + dgvManager. currentRow. cells [0]. value. toString () + "'"; SqlConnection conn = new SqlConnection (connStr); SqlCommand cmdUpdate = new SqlCommand (sqlUpdate, conn); c Onn. Open (); int n = cmdUpdate. ExecuteNonQuery (); if (n = 0) {// MessageBox. Show ("update failed! "); Return; // and return} else if (n> 0) {// otherwise MessageBox. Show (" Congratulations! Update successful! ");} // After the data update operation is completed, you need to disable the database to save resources conn. close (); // call the Refresh method after the update, and display the updated data on the datagridview Refresh ();}

 

Query-view records(View all current records)

// View and refresh all data private void btnView_Click (object sender, EventArgs e) {string SQL = "select Id, Movie_Name, Movie_Director, Date_Released from Record "; sqlConnection conn = new SqlConnection (connStr); SqlCommand cmd = new SqlCommand (SQL, conn); DataTable dt = new DataTable (); SqlDataAdapter sda = new SqlDataAdapter (cmd); sda. fill (dt); dgvManager. dataSource = dt ;}

 

After the code is written, some people may say that many database operation codes are repeated, which is difficult to see. Indeed, we can separate the repeated parts, write a SQLHelper class to include these methods. Then, call the corresponding methods where data operations are required, which saves both the amount of code and the appearance.

Here are my methods for adding, deleting, modifying, and querying. You can just input the corresponding parameters when calling them. I will not discuss how to use them here.

Execute add, delete, and modify Methods

Static string connStr = ConfigurationManager. connectionStrings ["str"]. connectionString; static SqlConnection conn = new SqlConnection (connStr ); /// <summary> /// ExcuteNonQuery is used to execute the addition, deletion, modification, and modification method. /// </summary> /// <param name = "strSql"> add, delete, and modify SQL statements </param> /// <param name = "paras"> SQL parameter array </param> /// <returns> returns an integer, used to determine whether the operation is successful </returns> public static int ExcuteNonQuery (string strSql, params SqlParameter [] paras) {SqlCommand cmd = new SqlCommand (strSql, conn ); // to execute the SQL command, two parameters must be input for calling the SQL query statement and SQL connection cmd. parameters. addRange (paras); // Add the conn parameter array for query statement execution. open (); // Open the database connection int n = cmd before execution. executeNonQuery (); // number of rows returned by the successful operation by executing the cmd command conn. close (); // when the database is used up to save resources return n; // The number of rows that return successful operations}

 

Query Method

/// <Summary> /// ExecuteDataTable is used to execute the query method // </summary> /// <param name = "strSql"> SQL Select statement </param>/ // <returns> return query result table </returns> public static DataTable ExecuteDataTable (string strSql) {SqlCommand cmd = new SqlCommand (strSql, conn); // to execute an SQL command, you need to input two parameter SQL query statements and connect SQL to SqlDataAdapter da = new SqlDataAdapter (cmd ); // use the SqlDataAdapter data adapter to load the cmd Operation Command DataTable dt = new DataTable (); // create a DataTable da. fill (dt); // Fill in the result set obtained by SqlDataAdapter to the DataTable return dt; // return DataTable}

 

Microsoft provides a very good data operation class SQLHelper. cs for SQL Server database operations. [For details, refer to here]

In this way, we can add, delete, modify, and query the main program. Next we will learn about the password modification function of the movie record management system, so stay tuned.

 

Add source code

MovieRecordManagementSystem02.zip

Related Recommendations [click here to view the Directory]

• C # simple movie record management system: developer 1 [user logon]
• C # simple movie record management system: Development 3 [Password modification]
• C # simple movie record management system: Development 4 [log view]
• C # simple movie record management system: developer 5 [User Registration]
• C # simple movie record management system: Development 6 [data search]
• C # simple movie record management system: Development 7 [User Classification]
• C # simple movie record management system: Development 8 [data backup]
• C # simple movie record management system: Development 9 [data recovery]
• C # simple movie record management system: Conclusion [permission Assignment]