存储过程的调用和Parameters数组的调用

记录一些杂记吧

declare @d datetime
set @d=GETDATE()

select * from [ACC_B2B].[dbo].ORDER_Head --where 1=1

select datediff(ms,@d,getdate()) --2453 --2406


给查询条件加上where 1=1 速度会快一点；

更多详细的看看这个sql语句中where 1=1和1=0的作用

里面的1=0快速建表：create table newtable as select * from oldtable where 1=0; 创建一个新表，而新表的结构与查询的表的结构是一样的。

 

引用地址：http://www.398588.com/Article/T5/46.aspx
返回一个存储过程的返回值。
方法代码：
public int SqlParm(string cmdText,SqlParameter[] Prams) 
　  {
　　　  SqlConnection Conn = new SqlConnection(ConfigurationManager.ConnectionStrings[0].ConnectionString);
　　　  Conn.Open();
　　　  SqlCommand Cmd = new SqlCommand(cmdText, Conn);
　　　  Cmd.CommandType = CommandType.StoredProcedure;
　　　  foreach (SqlParameter P in Prams) 
　　　  {
　　　　　  Cmd.Parameters.Add(P);
　　　  }
　　　  Cmd.Parameters.Add(new SqlParameter("Return", SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0, string.Empty, DataRowVersion.Default, null));
　　　  Cmd.ExecuteNonQuery();
　　　  int RowNum = int.Parse(Cmd.Parameters["Return"].Value.ToString());
　　　  return RowNum;
　  }
调用方法代码：
  SqlParameter[] Paramrs={new SqlParameter("@Q",SqlDbType.VarChar,50)};
  Paramrs[0].Value = "abc"; //给＠Q值。
　Response.Write(SqlParm("过程名称",Paramrs);
以上仅供参考，本站原创文章，如有转载请带链接注明出自 网络自由人

1           SqlParameter[] paras=
2             {
3                new SqlParameter("@name","这里应该放@name的类型")
4             };
5             SqlParameter parm = new SqlParameter("select_Con", "73468969715");
6             DataTable myset = ExecuteStoredProcedure("select_Con", parm);

 

现在，我们要改写这样的语句，使用SqlParameter来做 

SqlCommand SqlCmd = new SqlCommand(sql, SqlConn);

SqlParameter _userid = new SqlParameter("uid", SqlDbType.Int);

_userid.Value = Request.QueryString["u_id"];

SqlCmd.Parameters.Add(_userid);

　　

   public static DataTable ExecuteStoredProcedure(string ProcNme, params SqlParameter[] paramemters)
        {
            SqlDataReader reader = null;
            DataTable dt = new DataTable();
            using (SqlConnection conn=new SqlConnection(ConfigurationManager.ConnectionStrings["Connection B2B"].ToString()))
            {
                conn.Open();
                using (SqlCommand cmd=conn.CreateCommand())
                {
                    SqlTransaction st = conn.BeginTransaction();
                    cmd.Transaction = st;
                    try
                    {
                        cmd.CommandText = ProcNme;// 存储过程与Command牵连在一起了
                        cmd.CommandType = CommandType.StoredProcedure;//command执行的类型是存储过程，不再是sql语句
                        
                        cmd.Parameters.AddRange(paramemters);
                        reader = cmd.ExecuteReader();//返回发的是DataReader 下面的方法将reader转成datatable
                      dt=   ConvertDataReaderToDataTable(reader);
                      return dt;

                      //cmd.Parameters.AddRange(paras);
                      //SqlDataAdapter da = new SqlDataAdapter();
                      //da.SelectCommand = cmd;
                      //DataSet ds = new DataSet();
                      //da.Fill(ds, "StuInfo");
                      //dataGridView1.DataSource = ds.Tables["StuInfo"];
                    }
                    catch (Exception)
                    {
                        
                        throw;
                    }
                }
            }
        }

        public static DataTable ConvertDataReaderToDataTable(SqlDataReader reader)
        {
            try
            {
                DataTable objDataTable = new DataTable();
                int intFieldCount = reader.FieldCount;
                for (int intCounter = 0; intCounter < intFieldCount; ++intCounter)
                {
                    objDataTable.Columns.Add(reader.GetName(intCounter), reader.GetFieldType(intCounter));
                }

                objDataTable.BeginLoadData();

                object[] objValues = new object[intFieldCount];
                while (reader.Read())
                {
                    reader.GetValues(objValues);
                    objDataTable.LoadDataRow(objValues, true);
                }
                reader.Close();
                objDataTable.EndLoadData();

                return objDataTable;

            }
            catch (Exception ex)
            {
                throw new Exception("转换出错!", ex);
            }

        }

 

  public override bool ValidateUser(string username, string password)
        {
            SqlConnection sqlconn = new SqlConnection("Data Source=12345;Initial Catalog=TestDB01;Integrated Security=True");
            SqlCommand sqlcmd = new SqlCommand("select UserID, UserName from [UserInfo] where UserName = @userName and UserAge = @userAge", sqlconn); 
            try
            {
                sqlconn.Open(); 9 
                sqlcmd.Parameters.Add(new SqlParameter("@userName", SqlDbType.NVarChar, 50));
11             sqlcmd.Parameters["@userName"].Value = username.Trim();
            sqlcmd.Parameters.Add(new SqlParameter("@userAge", SqlDbType.SmallInt, 2));
13             sqlcmd.Parameters["@userAge"].Value = password;

                SqlDataReader sqlRd = sqlcmd.ExecuteReader();
                if (sqlRd.HasRows)
                {
                    return true;
                }

                return false;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

 

 public static DataTable SpReturnTable(string storedProcName, SqlParameter[] parameters, string connstring)
        {
            try
            {
                using (SqlConnection conStr = new SqlConnection(connstring))
                {
                    DataTable dt = new DataTable();
                    conStr.Open();
                    SqlTransaction trans = null; //创建事务
                    trans = conStr.BeginTransaction();
                    try
                    {
                        SqlCommand comStr = new SqlCommand(storedProcName, conStr);
                        comStr.Transaction = trans;
                        comStr.CommandType = CommandType.StoredProcedure; //设置执行类型为存储过程  
                        foreach (SqlParameter parameter in parameters)
                        {
                            comStr.Parameters.Add(parameter);
                        }
                        using (SqlDataAdapter sda = new SqlDataAdapter())
                        {
                            sda.SelectCommand = comStr;
                            sda.Fill(dt);
                        }
                        trans.Commit(); //提交事务
                        return dt;
                    }
                    catch (Exception ex)
                    {
                        string a = ex.Message;
                        trans.Rollback(); //回滚事务
                        return null;
                    }
                    finally
                    {
                        conStr.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }