Archive for the ‘資料庫系統實務(99)’ Category

bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark

private void button3_Click(object sender, EventArgs e)
{
    string SID = textBox1.Text;
    if (SID == StudentID)
    {
        MessageBox.Show("此學號是目前的學生資料,不能新增", "不能新增");
        return;
    }
 
    if (MessageBox.Show("確定是否新增資料", "新增", MessageBoxButtons.YesNo) != DialogResult.Yes)
        return;
    string SName = textBox2.Text;
    int Lang = int.Parse(textBox3.Text);
    int Math = int.Parse(textBox4.Text);
 
    InsertStudent(SID, SName, Lang, Math);
    DataSet ds = ReadStudents();
    dataGridView1.DataSource = ds.Tables[0];
}
int InsertStudent(string sID, string sNAME, int mATH, int lANG)
{
    string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=D:\\db1.mdb";
    System.Data.IDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString);
 
    string queryString = "INSERT INTO [Student] ([SID], [SNAME], [MATH], [LANG]) VALUES (@SID, @SNAME, @MAT" +
        "H, @LANG)";
    System.Data.IDbCommand dbCommand = new System.Data.OleDb.OleDbCommand();
    dbCommand.CommandText = queryString;
    dbCommand.Connection = dbConnection;
 
    System.Data.IDataParameter dbParam_sID = new System.Data.OleDb.OleDbParameter();
    dbParam_sID.ParameterName = "@SID";
    dbParam_sID.Value = sID;
    dbParam_sID.DbType = System.Data.DbType.String;
    dbCommand.Parameters.Add(dbParam_sID);
    System.Data.IDataParameter dbParam_sNAME = new System.Data.OleDb.OleDbParameter();
    dbParam_sNAME.ParameterName = "@SNAME";
    dbParam_sNAME.Value = sNAME;
    dbParam_sNAME.DbType = System.Data.DbType.String;
    dbCommand.Parameters.Add(dbParam_sNAME);
    System.Data.IDataParameter dbParam_mATH = new System.Data.OleDb.OleDbParameter();
    dbParam_mATH.ParameterName = "@MATH";
    dbParam_mATH.Value = mATH;
    dbParam_mATH.DbType = System.Data.DbType.Int32;
    dbCommand.Parameters.Add(dbParam_mATH);
    System.Data.IDataParameter dbParam_lANG = new System.Data.OleDb.OleDbParameter();
    dbParam_lANG.ParameterName = "@LANG";
    dbParam_lANG.Value = lANG;
    dbParam_lANG.DbType = System.Data.DbType.Int32;
    dbCommand.Parameters.Add(dbParam_lANG);
 
    int rowsAffected = 0;
    dbConnection.Open();
    try
    {
        rowsAffected = dbCommand.ExecuteNonQuery();
    }
    finally
    {
        dbConnection.Close();
    }
 
    return rowsAffected;
}

bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark

private void button2_Click(object sender, EventArgs e)
{
    if (MessageBox.Show("確定是否刪除資料", "刪除", MessageBoxButtons.YesNo) != DialogResult.Yes)
        return;
    DeleteStudent(StudentID);
    DataSet ds = ReadStudents();
    dataGridView1.DataSource = ds.Tables[0];
}
int DeleteStudent(string sID)
{
    string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=D:\\db1.mdb";
    System.Data.IDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString);
 
    string queryString = "DELETE FROM [Student] WHERE ([Student].[SID] = @SID)";
    System.Data.IDbCommand dbCommand = new System.Data.OleDb.OleDbCommand();
    dbCommand.CommandText = queryString;
    dbCommand.Connection = dbConnection;
 
    System.Data.IDataParameter dbParam_sID = new System.Data.OleDb.OleDbParameter();
    dbParam_sID.ParameterName = "@SID";
    dbParam_sID.Value = sID;
    dbParam_sID.DbType = System.Data.DbType.String;
    dbCommand.Parameters.Add(dbParam_sID);
 
    int rowsAffected = 0;
    dbConnection.Open();
    try
    {
        rowsAffected = dbCommand.ExecuteNonQuery();
    }
    finally
    {
        dbConnection.Close();
    }
 
    return rowsAffected;
}

bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark

1
十一月

資料庫系統實務11/01上課:資料的更新

   Posted by: admin Tags: , ,

bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark

1
十一月

資料庫系統實務11/01程式:資料的更新

   Posted by: admin Tags: , ,

private void button3_Click(object sender, EventArgs e)
{
    if (MessageBox.Show("確定是否更新資料", "更新", MessageBoxButtons.YesNo) != DialogResult.Yes)
        return;
    string SName = textBox2.Text;
    int Math = int.Parse(textBox3.Text);
    int Lang = int.Parse(textBox4.Text);
 
    UpdateStudent(SID, SName, Math, Lang);
}
int UpdateStudent(string sID, string sNAME, int mATH, int lANG)
{
    string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=C:\\Documents an" +
        "d Settings\\Bio\\My Documents\\db1.mdb";
    System.Data.IDbConnection dbConnection = new System.Data.OleDb.OleDbConnection(connectionString);
 
    string queryString = "UPDATE [Student] SET [SNAME]=@SNAME, [MATH]=@MATH, [LANG]=@LANG WHERE ([Student]." +
        "[SID] = @SID)";
    System.Data.IDbCommand dbCommand = new System.Data.OleDb.OleDbCommand();
    dbCommand.CommandText = queryString;
    dbCommand.Connection = dbConnection;
 
 
    System.Data.IDataParameter dbParam_sNAME = new System.Data.OleDb.OleDbParameter();
    dbParam_sNAME.ParameterName = "@SNAME";
    dbParam_sNAME.Value = sNAME;
    dbParam_sNAME.DbType = System.Data.DbType.String;
    dbCommand.Parameters.Add(dbParam_sNAME);
    System.Data.IDataParameter dbParam_mATH = new System.Data.OleDb.OleDbParameter();
    dbParam_mATH.ParameterName = "@MATH";
    dbParam_mATH.Value = mATH;
    dbParam_mATH.DbType = System.Data.DbType.Int32;
    dbCommand.Parameters.Add(dbParam_mATH);
    System.Data.IDataParameter dbParam_lANG = new System.Data.OleDb.OleDbParameter();
    dbParam_lANG.ParameterName = "@LANG";
    dbParam_lANG.Value = lANG;
    dbParam_lANG.DbType = System.Data.DbType.Int32;
    dbCommand.Parameters.Add(dbParam_lANG);
    System.Data.IDataParameter dbParam_sID = new System.Data.OleDb.OleDbParameter();
    dbParam_sID.ParameterName = "@SID";
    dbParam_sID.Value = sID;
    dbParam_sID.DbType = System.Data.DbType.String;
    dbCommand.Parameters.Add(dbParam_sID);
    int rowsAffected = 0;
    dbConnection.Open();
    try
    {
        rowsAffected = dbCommand.ExecuteNonQuery();
    }
    finally
    {
        dbConnection.Close();
    }
 
    return rowsAffected;
}

bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark bookmark