Sqlserver数据库学习

数据库相关技术学习

1.sqlserver创建索引

CREATE INDEX PersonIndex ON 表名 (字段名)

2.调用Sqlserver存储过程

 /// <summary>
        /// 运行存储过程,返回DataSet
        /// </summary>
        /// <param name="ProcedureName">存储过程名称</param>
       /// <param name="sps">参数</param>
        /// <returns></returns>
        public static DataSet runProcedure(string ProcedureName, List<sp_param> sps)
        {
            string connstr = Properties.Settings.Default.lswxConnectionString;
            SqlCommand cm = new SqlCommand();
            cm.Connection = new SqlConnection(connstr);
            cm.Connection.Open();
            cm.CommandText = ProcedureName;

            if (sps != null && sps.Count > 0)
            {
                foreach (var sp in sps)
                {
                    SqlParameter sparam = new SqlParameter(sp.param_name, sp.param_value);
                    cm.Parameters.Add(sparam);
                }
            }

            cm.CommandType = CommandType.StoredProcedure;
            DataSet DS = null;
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(cm);
                DS = new DataSet();
                da.Fill(DS);
                da.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cm.Connection.Close();
                cm.Dispose();
            }
            return DS;
        }

3.创建触发器

Insert触发器

create trigger stu_insert
on student
for insert
as
update class set class_num=class_num+1
where class_id=(select class_id from inserted)

Delete触发器

create trigger stu_delete
on student
for delete
as
update class set class_num=class_num-1
where class_id=(select class_id from deleted)

Update触发器

create trigger stu_update
on student
instead of update
as
print '修改学生表'
drop trigger stu_update
--测试
update student set stu_id='0601004'where stu_name='鲁斌'