C#使用SQL存储过程完整流程

    xiaoxiao2021-12-15  5

    先简单的说说什么是存储过程:存储过程就是固化在SQL数据库系统内部的SQL语句,这样做的好处是可以提高执行效率、提高数据库的安全性、减少网络流量。接下来就讲解如何在数据库中建立一个存储过程。

          打开SQL2055数据库,展开“数据库”节点,找到你使用的数据库(目的数据库),展开该数据库节点,找到“可编程性”节点并展开,就可以看到“存储过程”了,在“存储过程”上点击右键,新建存储过程。然后会弹出查询分析器,在这输入创建代码就可以了。

    这个存储过程可以向SystemUsers表中插入一条记录。

    ///------------------------------------------------------------------------------------------------------------------------------------------

    http://www.cnblogs.com/mgod/archive/2008/08/02/1258585.html --T-SQL不带参数 if exists(select * from sysobjects where name='SelectStu') drop proc SelectStu GO create proc SelectStu as select * from student GO

    --c# SqlCommand cmd = new SqlCommand("SelectStu",con);             cmd.CommandType = CommandType.StoredProcedure;             SqlDataReader dr = cmd.ExecuteReader();             while(dr.Read())             {                 Console.WriteLine(dr["LoginId"]);             }

     

    --T-SQL 带一个参数 if exists(select * from sysobjects where name='SelectStu') drop proc SelectStu GO create proc SelectStu @name varchar(50) as select * from student where loginid=@name GO

    --C# SqlCommand cmd = new SqlCommand("SelectStu",con);             cmd.CommandType = CommandType.StoredProcedure;             cmd.Parameters.Add("@name", "LiDifei");             SqlDataReader dr = cmd.ExecuteReader();             while(dr.Read())             {                 Console.WriteLine(dr["LoginId"]);             }

    --T-SQL 有返回值 if exists(select * from sysobjects where name='SelectStu') drop proc SelectStu GO create proc SelectStu @name varchar(50), @id int output as select @id=studentid from student where loginid=@name return @id GO

    --C# SqlCommand cmd = new SqlCommand("SelectStu",con);             cmd.CommandType = CommandType.StoredProcedure;             cmd.Parameters.Add("@name", "LiDifei");             cmd.Parameters.Add("@id",SqlDbType.Int);             cmd.Parameters["@id"].Direction = ParameterDirection.Output;             cmd.ExecuteScalar();             Console.WriteLine(cmd.Parameters["@id"].Value);

     

    -------------------------------------------------------------------------------------

     

    //以前写过的一个调用存储过程的方法

     public static int regInsert(string Pwd, int Friend, string NickName, int FaceID, string Sex, int Age, string Name, int starId, int BloodTypeId)         {             try             {                 SqlConnection conn = createConnection();                 conn.Open();                 SqlCommand cmd = new SqlCommand("proc_reg", conn);                 cmd.CommandType = CommandType.StoredProcedure;                 cmd.Parameters.Add("@id",SqlDbType.Int);                 cmd.Parameters["@id"].Direction = ParameterDirection.Output;                 cmd.Parameters.AddWithValue("@LoginPwd",Pwd);                 cmd.Parameters.AddWithValue("@FriendshipPolicyId",Friend);                 cmd.Parameters.AddWithValue("@NickName",NickName);                 cmd.Parameters.AddWithValue("@FaceID",FaceID);                 cmd.Parameters.AddWithValue("@Sex",Sex);                 cmd.Parameters.AddWithValue("@Age",Age);                 cmd.Parameters.AddWithValue("@Name",Name);                 cmd.Parameters.AddWithValue("@starId",starId);                 cmd.Parameters.AddWithValue("@BloodTypeId", BloodTypeId);

                                                      cmd.ExecuteNonQuery();                 return (int)cmd.Parameters["@id"].Value;                                              }             catch (SqlException ex)             {                 Console.WriteLine(ex.Message);             }             return 0;                    }

    转载请注明原文地址: https://ju.6miu.com/read-1000186.html

    最新回复(0)