数据库 visual studio

publicclassStudentService
{

//从配置文件中读取数据库连接字符串
privatereadonlystaticstringconnString=ConfigurationManager.ConnectionStrings["accpConnectionString"].ToString();
privatereadonlystaticstringdboOwner=ConfigurationManager.ConnectionStrings["DataBaseOwner"].ToString();
AdoNetModels.Studentmodel=newStudent();
//conststringspName=".usp_DeleteStudent";

#region删除数据1
publicintDeleteStudent(intstuID)
{
intresult=0;
//数据库连接Connection对象
SqlConnectionconnection=newSqlConnection(connString);
//构建删除的sql语句
stringsql=string.Format("DeleteFromStudentWherestuID={0}",stuID);
//定义command对象
SqlCommandcommand=newSqlCommand(sql,connection);

try
{
connection.Open();
result=command.ExecuteNonQuery();//执行命令
}
catch(Exceptionex)
{

Console.WriteLine(ex.Message);
}
finally
{
connection.Close();
}
returnresult;
}
#endregion

#region删除数据2

publicintDeleteStudent2(intstuID)
{
intresult=0;
//构建删除的sql语句使用参数
stringsql="DeleteFromStudentWherestuID=@stuID";

using(SqlConnectionconnection=newSqlConnection(connString))
{
SqlCommandobjCommand=newSqlCommand(sql,connection);

objCommand.Parameters.Add("@stuID",SqlDbType.Int).Value=stuID;

connection.Open();
result=objCommand.ExecuteNonQuery();
}
returnresult;

}
#endregion

publicintInsertStudent(Studentmodel)
{

intresult=0;
SqlConnectionconnection=newSqlConnection(connString);
//构建插入的sql语句
stringsql=string.Format("INSERTINTOStudent(stuName,age)values('{0}','{1}')",
model.StuName,model.Age);
//定义command对象
SqlCommandcommand=newSqlCommand(sql,connection);

try
{
connection.Open();
result=command.ExecuteNonQuery();//执行命令

}
catch(Exceptionex)
{

Console.WriteLine(ex.Message);
}
finally
{
connection.Close();
}

returnresult;
}

publicintInsertStudent2(Studentmodel)
{

intresult=0;
//构建插入的sql语句
stringsql="INSERTINTOStudent(age,stuName)values(@age,@stuName)";
using(SqlConnectionconnection=newSqlConnection(connString))
{
SqlCommandobjCommand=newSqlCommand(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();
}
returnresult;
}

publicintInsertStudent3(Studentmodel)
{
intresult=0;
using(SqlConnectionconnection=newSqlConnection(connString))
{
SqlCommandobjCommand=newSqlCommand(dboOwner+".usp_InsertStudent",connection);
objCommand.CommandType=CommandType.StoredProcedure;

objCommand.Parameters.Add("@age",SqlDbType.Int).Value=model.Age;
objCommand.Parameters.Add("@stuName",SqlDbType.NVarChar,50).Value=model.StuName;

connection.Open();
result=objCommand.ExecuteNonQuery();
}
returnresult;

}

publicintInsertStudent5(Studentmodel)
{
intoutputResult=0;
intreturnvalue=0;
using(SqlConnectionconnection=newSqlConnection(connString))
{
SqlCommandobjCommand=newSqlCommand(dboOwner+".usp_InsertStudent",connection);
objCommand.CommandType=CommandType.StoredProcedure;

objCommand.Parameters.Add("@age",SqlDbType.Int).Value=model.Age;
objCommand.Parameters.Add("@stuName",SqlDbType.NVarChar,50).Value=model.StuName;
//定义输出参数
SqlParameterparameter=newSqlParameter("@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;
}
returnreturnvalue;

}

publicIListGetAllStudents1()
{

IListdataList=newList();
DataSetdataSet=newDataSet(); //声明并初始化DataSet
SqlDataAdapterdataAdapter; //声明DataAdapter

using(SqlConnectionconn=newSqlConnection(connString))
{
//定义command对象
SqlCommandcommand=newSqlCommand(dboOwner+".usp_SelectStudentsAll",conn);
command.CommandType=CommandType.StoredProcedure;
//Command定义带参数的SQL语句的参数
//command.Parameters.Add("@stuID",SqlDbType.Int);
//给输入参数赋值
//command.Parameters["@stuID"].Value=5;
conn.Open();
//初始化DataAdapter
dataAdapter=newSqlDataAdapter(command);

//填充DataSet
dataAdapter.Fill(dataSet,"dataSetName");

//处理数据集中的数据
foreach(DataRowrowindataSet.Tables[0].Rows)
{
Studentmodel=newStudent();
model.StuId=Convert.ToInt32(row["stuID"]);
model.StuName=Convert.ToString(row["stuName"]);
dataList.Add(model);

}

}
returndataList;

}

publicvoidMoreResult()
{
DataSetdataSet=newDataSet(); //声明并初始化DataSet
SqlDataAdapterdataAdapter; //声明DataAdapter

//定义查询语句
stringsql=string.Format("SELECT*FROMstudentwherestuid>50;SELECT*FROMstudent");
SqlConnectionconnection=newSqlConnection(connString);
try
{
connection.Open();
//初始化DataAdapter
dataAdapter=newSqlDataAdapter(sql,connection);

//填充DataSet
dataAdapter.Fill(dataSet,"dataSetName");

//处理数据集中的数据
foreach(DataRowrowindataSet.Tables[0].Rows)
{
//intgradeId=(int)row["GradeID"];

}
}
catch(Exceptionex)
{

Console.WriteLine(ex.Message);
}
finally
{
connection.Close();
}

}

}
}