One:理论
A:定义
存储过程(stored procedure)是一组为了完成特定功能的SQL语句集合,经编译后存储在服务器端的数据库中,利用存储过程可以加速SQL语句的执行。
存储过程分为系统存储过程和自定义存储过程。
1.系统存储过程在master数据库中,但是在其他的数据库中可以直接调用,并且在调用时不必在存储过程前加上数据库名,因为在创建一个新数据库时,系统存储过程
在新的数据库中会自动创建
2.自定义存储过程,由用户创建并能完成某一特定功能的存储过程,存储过程既可以有参数又有返回值,但是它与函数不同,存储过程的返回值只是指明执行是否成功,
并不能像函数那样被直接调用,只能利用execute来执行存储过程。
B:优点
1.提高应用程序的通用性和可移植性:存储过程创建后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句。并且数据库专业人员可以随时对存储过程进行
修改,且对程序源代码没有影响,这样就极大的提高了程序的可移植性。
2.可以更有效的管理用户操作数据库的权限:在Sql Server数据库中,系统管理员可以通过对执行某一存储过程的权限进行限制,从而实现对相应的数据访问进行控制,
避免非授权用户对数据库的访问,保证数据的安全。
3.可以提高SQL的速度,存储过程是编译过的,如果某一个操作包含大量的SQL代码或分别被执行多次,那么使用存储过程比直接使用单条SQL语句执行速度快的多。
4.减轻服务器的负担:当用户的操作是针对数据库对象的操作时,如果使用单条调用的方式,那么网络上还必须传输大量的SQL语句,如果使用存储过程,
则直接发送过程的调用命令即可,降低了网络的负担。
C:语法
SQL Server存储过程语法:
create procedure 过程名
@parameter 参数类型
@parameter 参数类型
。。。asbegin
end
执行存储过程:execute 过程名
Two:应用
A:数据库存储
--查询不带参数的存储过程
ALTER procedure [dbo].[GetUser]asbegin
Select Theserialnumber, UserID, UserName, UserSet,
Userphone, work.UserworkType,Details.WorkDetailsSituation,
[level].UserleverlType,[type].UserType, UserCreationtime, hobby.Userhobbyfrom[User]
inner join Work on Work.UserworkID=[User].UserworkID
inner join [level] on [level].UserlevelID=[user].UserlevelID
inner join UserTypeas [type] on [type].UserTypeID=[USER].UserTypeID
inner join WorkDetailsas Details on Details.WorkDetailsID=Work.WorkDetailsID
inner join Userhobbyas hobby on hobby.UserhobbyID=[user].UserhobbyID
end--查询带参数的存储过程
ALTER procedure [dbo].[GetUserID]--创建存储过程名
@UserID varchar(50) --参数asbeginselect * from [User] where UserID=@UserID --命令行或者命令块
end
B:项目程序
//根据提供的strSQL语句 访问数据库,返回List集合
public List GetDataSet(stringstrSQL)
{
Connection();try{
SqlDataAdapter da= newSqlDataAdapter(strSQL, con);
DataSet ds= newDataSet();
da.Fill(ds);
List list = new List();if (ds.Tables.Count > 0)
{for (int i = 0; i < ds.Tables.Count; i++)
{foreach (DataRow dr inds.Tables[i].Rows)
{
User obj= newUser();if (ds.Tables[i].Columns.Contains("UserID")) obj.UserID = Convert.ToString(dr["UserID"]);if (ds.Tables[i].Columns.Contains("UserName")) obj.UserName = Convert.ToString(dr["UserName"]);if (ds.Tables[i].Columns.Contains("UserSet")) obj.UserSet = Convert.ToString(dr["UserSet"]);if (ds.Tables[i].Columns.Contains("Userphone")) obj.Userphone = Convert.ToString(dr["Userphone"]);if (ds.Tables[i].Columns.Contains("UserworkType")) obj.UserworkType = Convert.ToString(dr["UserworkType"]);if (ds.Tables[i].Columns.Contains("WorkDetailsSituation")) obj.WorkDetailsSituation = Convert.ToString(dr["WorkDetailsSituation"]);if (ds.Tables[i].Columns.Contains("UserleverlType")) obj.UserleverlType = Convert.ToString(dr["UserleverlType"]);if (ds.Tables[i].Columns.Contains("UserType")) obj.UserType = Convert.ToString(dr["UserType"]);if (ds.Tables[i].Columns.Contains("UserCreationtime")) obj.UserCreationtime = Convert.ToDateTime(dr["UserCreationtime"]);if (ds.Tables[i].Columns.Contains("Userhobby")) obj.Userhobby = Convert.ToString(dr["Userhobby"]);
list.Add(obj);
}
}
}returnlist;
}catch(Exception)
{throw;
}
}///
///执行返回DataTable的查询/// 存储过程名称
/// 存储过程参数
public static DataTable GetDataTableByProc(string procName, paramsIDataParameter[] parameters)
{
Connection();
DataTable dt= newDataTable();
SqlCommand cmd= newSqlCommand();try{
cmd.Connection=con;
cmd.CommandText=procName;
cmd.CommandType=CommandType.StoredProcedure;for (int i = 0; i < parameters.Length; i++)
{
cmd.Parameters.Add(parameters[i]);
}
SqlDataAdapter dp= newSqlDataAdapter(cmd);
dp.Fill(dt);
con.Close();returndt;
}catch(Exception ex)
{throwex;
}
}///执行返回DataSet的查询/// 存储过程名称
/// 存储过程参数
public static DataSet GetDataSetByProc(string procName, paramsIDataParameter[] parameters)
{
Connection();
DataSet ds= newDataSet();
SqlCommand cmd= newSqlCommand();try{
cmd.Connection=con;
cmd.CommandText=procName;
cmd.CommandType=CommandType.StoredProcedure;
con.Open();for (int i = 0; i < parameters.Length; i++)
{
cmd.Parameters.Add(parameters[i]);
}
SqlDataAdapter dp= newSqlDataAdapter(cmd);
dp.Fill(ds);
con.Close();returnds;
}catch(Exception ex)
{throwex;
}
}///
///根据条件查询///
/// 存储过程名称/sql语句
/// 存储过程参数/参数
///
public static DataTable GetDataTable(string SqlStr, paramsIDataParameter[] parameters)
{#regionConnection();
DataTable dt= newDataTable();
SqlDataAdapter da= newSqlDataAdapter(SqlStr, con);try{
da.SelectCommand.Parameters.Clear();foreach (SqlParameter pam inparameters)
{
da.SelectCommand.Parameters.Add(EncodeParam(pam));
}
da.Fill(dt);
da.SelectCommand.Parameters.Clear();
da.Dispose();
}catch(Exception e)
{
Console.Write(SqlStr+ ",SQL异常:" +e.Message);
}finally{
con.Dispose();
}returndt;#endregion}
C:调用
//、、、、、、、、存储过程、、、、、、、、、//查询全部
public static ListGetUser()
{return db.GetDataSet("GetUser");
}///
///根据条件查询///
/// 参数
public static DataTable GetUserID(stringUserID)
{
SqlParameter[] parameters={new SqlParameter("@UserID",SqlDbType.VarChar,50)
};
parameters[0].Value =UserID;return DBbase.GetDataTableByProc("GetUserID", parameters);
}
//存储过程
List u =DAL.Family.GetUser();
DataTable d= DAL.Family.GetUserID("2020020901494949");for (int i = 0; i < d.Rows.Count; i++)
{foreach (DataRow dr ind.Rows)
{var ID = dr["UserID"].ToString();
}
}