visual studio做的窗口应用程序，已经连接数据库，如何删除，updata数据
发布网友
发布时间：2022-05-27 15:30
共1个回答
热心网友
时间：2023-10-30 23:24
public class StudentService
{
//从配置文件中读取数据库连接字符串
private readonly static string connString = ConfigurationManager.ConnectionStrings["a*ConnectionString"].ToString();
private readonly static string dboOwner = ConfigurationManager.ConnectionStrings["DataBaseOwner"].ToString();
AdoNetModels.Student model = new Student();
// const string spName = ".usp_DeleteStudent";
#region 删除数据1
public int DeleteStudent(int stuID)
{
int result = 0;
// 数据库连接 Connection 对象
SqlConnection connection = new SqlConnection(connString);
// 构建删除的sql语句
string sql = string.Format("Delete From Student Where stuID={0}", stuID);
// 定义command对象
SqlCommand command = new SqlCommand(sql, connection);
try
{
connection.Open();
result = command.ExecuteNonQuery(); // 执行命令
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
connection.Close();
}
return result;
}
#endregion
#region 删除数据2
public int DeleteStudent2(int stuID)
{
int result = 0;
// 构建删除的sql语句使用参数
string sql = "Delete From Student Where stuID=@stuID";
using (SqlConnection connection = new SqlConnection(connString))
{
SqlCommand objCommand = new SqlCommand(sql, connection);
objCommand.Parameters.Add("@stuID", SqlDbType.Int).Value = stuID;
connection.Open();
result = objCommand.ExecuteNonQuery();
}
return result;
}
#endregion
public int InsertStudent(Student model)
{
int result = 0;
SqlConnection connection = new SqlConnection(connString);
// 构建插入的sql语句
string sql = string.Format("INSERT INTO Student (stuName,age) values('{0}','{1}')",
model.StuName, model.Age);
// 定义command对象
SqlCommand command = new SqlCommand(sql, connection);
try
{
connection.Open();
result = command.ExecuteNonQuery(); // 执行命令
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
connection.Close();
}
return result;
}
public int InsertStudent2(Student model)
{
int result = 0;
// 构建插入的sql语句
string sql = "INSERT INTO Student (age,stuName) values(@age,@stuName)";
using (SqlConnection connection = new SqlConnection(connString))
{
SqlCommand objCommand = new SqlCommand(sql, connection);
objCommand.Parameters.Add("@age", SqlDbType.Int).Value = model.Age;
objCommand.Parameters.Add("@stuName", SqlDbType.NVarChar, 50).Value = model.StuName;
connection.Open();
result = objCommand.ExecuteNonQuery();
}
return result;
}
public int InsertStudent3(Student model)
{
int result = 0;
using (SqlConnection connection = new SqlConnection(connString))
{
SqlCommand objCommand = new SqlCommand(dboOwner + ".usp_InsertStudent", connection);
objCommand.CommandType = CommandType.StoredProcere;
objCommand.Parameters.Add("@age", SqlDbType.Int).Value = model.Age;
objCommand.Parameters.Add("@stuName", SqlDbType.NVarChar, 50).Value = model.StuName;
connection.Open();
result = objCommand.ExecuteNonQuery();
}
return result;
}
public int InsertStudent5(Student model)
{
int outputResult = 0;
int returnvalue = 0;
using (SqlConnection connection = new SqlConnection(connString))
{
SqlCommand objCommand = new SqlCommand(dboOwner + ".usp_InsertStudent", connection);
objCommand.CommandType = CommandType.StoredProcere;
objCommand.Parameters.Add("@age", SqlDbType.Int).Value = model.Age;
objCommand.Parameters.Add("@stuName", SqlDbType.NVarChar, 50).Value = model.StuName;
//定义输出参数
SqlParameter parameter = new SqlParameter("@stuID", SqlDbType.Int);
parameter.Direction = ParameterDirection.Output;
objCommand.Parameters.Add(parameter);
//定义ReturnValue参数
objCommand.Parameters.Add("ReturnValue", SqlDbType.Int);
objCommand.Parameters["ReturnValue"].Direction = ParameterDirection.ReturnValue;
connection.Open();
objCommand.ExecuteNonQuery();// 执行命令
//获取输出参数的值在命令执行以后
outputResult = (int)objCommand.Parameters["@stuID"].Value;
//存储过程中为定义return值默认为0
returnvalue = (int)objCommand.Parameters["ReturnValue"].Value;
}
return returnvalue;
}
public IList<Student> GetAllStudents1()
{
IList<Student> dataList = new List<Student>();
DataSet dataSet = new DataSet(); // 声明并初始化DataSet
SqlDataAdapter dataAdapter; // 声明DataAdapter
using (SqlConnection conn = new SqlConnection(connString))
{
// 定义command对象
SqlCommand command = new SqlCommand(dboOwner + ".usp_SelectStudentsAll", conn);
command.CommandType = CommandType.StoredProcere;
//Command定义带参数的SQL语句的参数
//command.Parameters.Add("@stuID", SqlDbType.Int);
//给输入参数赋值
//command.Parameters["@stuID"].Value = 5;
conn.Open();
// 初始化 DataAdapter
dataAdapter = new SqlDataAdapter(command);
// 填充 DataSet
dataAdapter.Fill(dataSet, "dataSetName");
// 处理数据集中的数据
foreach (DataRow row in dataSet.Tables[0].Rows)
{
Student model = new Student();
model.StuId = Convert.ToInt32(row["stuID"]);
model.StuName = Convert.ToString(row["stuName"]);
dataList.Add(model);
}
}
return dataList;
}
public void MoreResult()
{
DataSet dataSet = new DataSet(); // 声明并初始化DataSet
SqlDataAdapter dataAdapter; // 声明DataAdapter
// 定义查询语句
string sql = string.Format("SELECT * FROM student where stuid>50;SELECT * FROM student ");
SqlConnection connection = new SqlConnection(connString);
try
{
connection.Open();
// 初始化 DataAdapter
dataAdapter = new SqlDataAdapter(sql, connection);
// 填充 DataSet
dataAdapter.Fill(dataSet, "dataSetName");
// 处理数据集中的数据
foreach (DataRow row in dataSet.Tables[0].Rows)
{
//int gradeId = (int)row["GradeID"];
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
connection.Close();
}
}
}
}
