Programming | network
1. Use the method of keeping a connection to write a program that calculates the average grade of each year and displays the results.
Troubleshooting
Using System;
Using System.Collections.Generic;
Using System.ComponentModel;
Using System.Data;
Using System.Drawing;
Using System.Text;
Using System.Windows.Forms;
Using System.Data.SqlClient;
Namespace Exercise 8_6_1
{
public partial class Form1:form
{
Public Form1 ()
{
InitializeComponent ();
}
Add button buttons to display results in ListBox
private void Button1_Click (object sender, EventArgs e)
{
LISTBOX1.ITEMS.ADD ("Grade average score");
string connectionString = Properties.Settings.Default.MyDatabaseConnectionString;
Create a SqlConnection instance from a connection string
SqlConnection conn = new SqlConnection (connectionString);
Create a SqlCommand instance and set the SQL statement and the connection instance used
SqlCommand cmd = new SqlCommand ();
Cmd.commandtext = "Select substring (school number, 1,2) as grade, AVG (score) as average score from MyTable2 GROUP by substring (school number, 1,2)";
Cmd. Connection = conn;
Try
{
Conn. Open ();
SqlDataReader r = cmd. ExecuteReader ();
while (r.read () = = True)
{
LISTBOX1.ITEMS.ADD (String. Format ("{0} level {1}", R[0], r[1]));
}
R.close ();
}
catch (Exception err)
{
MessageBox.Show (Err. Message, "computational Failure");
}
Finally
{
Conn. Close ();
}
}
}
}
2. The use of the way to keep connected to write programs, inquiries MyTable2 students in the school number, name, sex, achievement. And the results are displayed in the listbox.
Troubleshooting
Using System;
Using System.Collections.Generic;
Using System.ComponentModel;
Using System.Data;
Using System.Drawing;
Using System.Text;
Using System.Windows.Forms;
Using System.Data.SqlClient;
Namespace Exercise 8_6_2
{
public partial class Form1:form
{
Public Form1 ()
{
InitializeComponent ();
}
private void Button1_Click (object sender, EventArgs e)
{
LISTBOX1.ITEMS.ADD ("School Number name gender score");
string connectionString = Properties.Settings.Default.MyDatabaseConnectionString;
Create a SqlConnection instance from a connection string
SqlConnection conn = new SqlConnection (connectionString);
Create a SqlCommand instance and set the SQL statement and the connection instance used
SqlCommand cmd = new SqlCommand ();
Cmd.commandtext =
"Select number, name, gender, achievement from MyTable2 Where (result <60)";
Cmd. Connection = conn;
Try
{
Conn. Open ();
SqlDataReader r = cmd. ExecuteReader ();
while (r.read () = = True)
{
LISTBOX1.ITEMS.ADD (String. Format ("{0} {1} {2} {3}", R[0], r[1], r[2], r[3));
}
R.close ();
}
catch (Exception err)
{
MessageBox.Show (Err. Message, "Query results failed");
}
Finally
{
Conn. Close ();
}
}
}
}
3. Write the program to display the contents of MyTable1 in the comboBox1 with the "[encoded] name" style.
Troubleshooting
Using System;
Using System.Collections.Generic;
Using System.ComponentModel;
Using System.Data;
Using System.Drawing;
Using System.Text;
Using System.Windows.Forms;
Using System.Data.SqlClient;
Namespace Exercise 8_6_3
{
public partial class Form1:form
{
Public Form1 ()
{
InitializeComponent ();
}
private void Form1_Load (object sender, EventArgs e)
{
string connectionString = Properties.Settings.Default.MyDatabaseConnectionString;
Create a SqlConnection instance from a connection string
SqlConnection conn = new SqlConnection (connectionString);
Create a SqlCommand instance and set the SQL statement and the connection instance used
SqlCommand cmd = new SqlCommand ();
Cmd.commandtext = "SELECT * from MyTable1";
Cmd. Connection = conn;
Try
{
Conn. Open ();
SqlDataReader r = cmd. ExecuteReader ();
while (r.read () = = True)
{
COMBOBOX1.ITEMS.ADD (String. Format ("[{0}] {1}", R[0], r[1]));
}
Combobox1.selectedindex = 0;
R.close ();
}
catch (Exception err)
{
MessageBox.Show (Err. Message, "Show Data Failed");
}
Finally
{
Conn. Close ();
}
}
}
}
4. Fill in the appropriate content at the drawing line, make the program become correct and complete.
String connstring= "server=localhost;integrated security=sspi;database=pubs";
SqlConnection conn=____________________________
String Strsql= "select * from MyTable2";
SqlDataAdapter Adapter=new SqlDataAdapter (_____________);
Dataset=new DataSet ();
Adapter. Fill (________________, "MyTable2");
This.datagridview1.datasource=dataset. tables["MyTable2"];
Troubleshooting
String connstring= "server=localhost;integrated security=sspi;database=pubs";
SqlConnection conn= New SqlConnection (Properties.Settings.Default.MyDatabaseConnectionString);
String Strsql= "select * from MyTable2";
SqlDataAdapter Adapter=new SqlDataAdapter (conn);
Dataset=new DataSet ();
Adapter. Fill (DataSet, "MyTable2");
This.datagridview1.datasource=dataset. tables["MyTable2"];
5. A person table is defined in a known database, and the data structure of the table is as follows:
Field Name field Type field meaning
ID Number number
XM Text Name
XB Text Gender
NL digital Age
Zip text zip code
The code is written in DataGridView to display all records in the datasheet that are older than 18, sorted in ascending numbers, and require users to be prevented from editing the data.
Troubleshooting
Using System;
Using System.Collections.Generic;
Using System.ComponentModel;
Using System.Data;
Using System.Drawing;
Using System.Text;
Using System.Windows.Forms;
Using System.Data.SqlClient;
Namespace Exercise 8_6_5
{
public partial class Form1:form
{
Public Form1 ()
{
InitializeComponent ();
}
private void Button1_Click (object sender, EventArgs e)
{
string connectionstring = Properties.Settings.Default.MyDatabaseConnectionString;
SqlConnection conn = new SqlConnection (connectionstring);
Try
{
Conn. Open ();
SqlDataAdapter adapter = new SqlDataAdapter (
"Select id,xm,xb,nl from person where nl > ID", conn);
DataSet DataSet = new DataSet ();
If you do not specify a table name, the system automatically generates a default table name
Adapter. Fill (DataSet, "person");
Tables that can be generated by using index references
Datagridview1.datasource = DataSet. tables["Person"];
Adapter. Dispose ();
}
catch (Exception err)
{
MessageBox.Show (Err. message);
}
Finally
{
Conn. Close ();
}
}
private void Form1_Load (object sender, EventArgs e)
{
Do not allow users to add new rows directly to the bottom line
Datagridview1.allowusertoaddrows = false;
Do not allow users to delete rows directly by pressing the DELETE key
Datagridview1.allowusertodeleterows = false;
}
}
}
6. For example 8-18, in the stored procedure definition, replace "@surname nvarchar (2)," to "@surname nchar (2)," and whether it is still possible to get the correct result, why?
Troubleshooting
Not necessarily. Because if the parameter value passed is "King", it will automatically become "king" in the stored procedure.
7. Call the stored procedures, the design program completes the following functions: arbitrary give a Chinese character, statistics MyTable2 all contain the number of characters, and display statistical results.
Troubleshooting
Using System;
Using System.Collections.Generic;
Using System.ComponentModel;
Using System.Data;
Using System.Drawing;
Using System.Text;
Using System.Windows.Forms;
Using System.Data.SqlClient;
Namespace Exercise 8_6_7
{
public partial class Form1:form
{
Public Form1 ()
{
InitializeComponent ();
}
private void Button1_Click (object sender, EventArgs e)
{
SqlConnection conn =
New SqlConnection (Properties.Settings.Default.MyDatabaseConnectionString);
SqlCommand cmd = new SqlCommand ();
Cmd. Connection = conn;
Set SQL statement to stored procedure name, command type is stored procedure
Cmd.commandtext = "Selectfilterstudentsnum";
Cmd.commandtype = CommandType.StoredProcedure;
Add the initial values required by the parameters in the stored procedure, noting that the parameter names are the same as the parameter names defined by the stored procedure
if (textbox1.text== "")
{
MessageBox.Show ("Please enter valid information", "error");
Textbox1.focus ();
return;
}
Cmd. Parameters.addwithvalue ("@surname", TextBox1.Text);
Cmd. Parameters.addwithvalue ("@record", 0);
Specify which parameters need to return results
Cmd. parameters["@record"]. Direction = ParameterDirection.Output;
Try
{
Conn. Open ();
Executing stored procedures
Cmd. ExecuteNonQuery ();
Displays the results returned
MessageBox.Show (String. Format ("Records with {0} bars containing {1}")
Cmd. parameters["@record"]. Value,textbox1.text));
}
catch (Exception err)
{
MessageBox.Show (Err. message);
}
Finally
{
Conn. Close ();
}
}
}
}