MVC 调用SQL存储过程

 
 
1、首先在Web.Config文件中配置一个与数据库的连接connectionStrings
  <connectionStrings    >
    <add name ="Conn" connectionString ="server=(local);uid=sa;pwd=123456;database=ShoesShop;" providerName ="System.Data.SqlClient"/>
  </connectionStrings>
参数就不必说是什么意思了吧

2、采用存储过程mvc3的访问数据库的通用类
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

namespace ShoesShop.DBUility
{
    public class MsSql
    {
        //通过根据存储过程提取所有的函数
        public DataSet getAllRecord(string storename, string Conn)
        {
            DataSet ds = new DataSet();
            SqlConnection conn = new SqlConnection(StaticDataConnClass.getSQLDataConn(Conn));
            try
            {
                conn.Open();
                SqlDataAdapter MyAd = new SqlDataAdapter(storename, conn);
                MyAd.SelectCommand.CommandType = CommandType.StoredProcedure;//调用存储过程
                MyAd.Fill(ds, "newtable");
            }
            catch
            {
                ds = null;
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }
            return ds;
        }

        // 通用数据选择代码,需要有返回值result
        public DataSet usuSelProc(string storename, string Conn, string[] strParams, object[] strValues)
        {
            DataSet ds = new DataSet();
            SqlConnection MyConn = new SqlConnection(StaticDataConnClass.getSQLDataConn(Conn));
            try
            {
                if ((strParams == null))// && (strParams.Length != strValues.Length))
                {
                    ds = null;
                }
                else
                {
                    MyConn.Open();
                    SqlDataAdapter MyAd = new SqlDataAdapter(storename, MyConn);//设置SQL命令
                    MyAd.SelectCommand.CommandType = CommandType.StoredProcedure;//调用存储过程

                    if (strParams != null)
                    {
                        for (int i = 0; i < strParams.Length; i++)
                            MyAd.SelectCommand.Parameters.AddWithValue(strParams[i], strValues[i]);
                    }
                    SqlParameter rst = MyAd.SelectCommand.Parameters.Add("@result", SqlDbType.Int);
                    rst.Direction = ParameterDirection.Output;

                    MyAd.Fill(ds, "newtable");

                }
            }
            catch
            {
                ds = null;
            }
            finally
            {
                MyConn.Close();
                MyConn.Dispose();
            }

            return ds;
        }

        // 通过数据插入与更新代码
        public int usuInsProc(string storename, string Conn, String[] strParams, Object[] strValues)
        {
            int result = -1;

            SqlConnection MyConn = new SqlConnection(StaticDataConnClass.getSQLDataConn(Conn));

            {
                if ((strParams != null) && (strParams.Length != strValues.Length))
                {
                    result = -2;
                }
                else
                {
                    MyConn.Open();
                    SqlCommand cmd = new SqlCommand(storename, MyConn);//设置SQL命令
                    cmd.CommandType = CommandType.StoredProcedure;//调用存储过程

                    try
                    {
                        if (strParams != null)
                        {
                            for (int i = 0; i < strParams.Length; i++)
                                cmd.Parameters.AddWithValue(strParams[i], strValues[i]);
                        }
                        SqlParameter rst = cmd.Parameters.Add("@result", SqlDbType.Int);
                        rst.Direction = ParameterDirection.Output;

                        cmd.Prepare();
                        result = cmd.ExecuteNonQuery();

                        cmd.Dispose();

                    }
                    catch
                    {
                        result = -1;
                    }
                    finally
                    {
                        MyConn.Close();
                        MyConn.Dispose();
                    }
                }
            }

            return result;
        }
    }
}

把这两个地方创建好后,上层的就可以调用这个通用的访问数据库的类实现增删改查了! 
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页