C#获取一个即返回结果集，又返回值的存储过程

秒速五厘米 2022-01-10 11:55 211阅读 0赞

方法：

string View_Name = "";
                            try
                            {
                                // 在此处放置用户代码以初始化页面
                                String DBConnStr;
                                DataSet MyDataSet = new DataSet();
                                System.Data.SqlClient.SqlDataAdapter DataAdapter = new System.Data.SqlClient.SqlDataAdapter();
                                string serverIP = ConfigurationManager.AppSettings["ServerIP"];
                                string serverUser = ConfigurationManager.AppSettings["ServerUser"];
                                string dbName = ConfigurationManager.AppSettings["DbName"];
                                string userPwd = ConfigurationManager.AppSettings["UserPwd"];
                                DBConnStr = "server=" + serverIP + ";database=" + dbName + ";pwd=" + userPwd + ";uid=" + serverUser + "";
                                System.Data.SqlClient.SqlConnection myConnection = new System.Data.SqlClient.SqlConnection(DBConnStr);
                                if (myConnection.State != ConnectionState.Open)
                                {
                                    myConnection.Open();
                                }
                                System.Data.SqlClient.SqlCommand myCommand = new System.Data.SqlClient.SqlCommand("GetXiaFa_Record_Tow", myConnection);
                                myCommand.CommandType = CommandType.StoredProcedure;
                                //添加输入查询参数、赋予值
                                myCommand.Parameters.Add("@PipeIDs", SqlDbType.VarChar);
                                myCommand.Parameters["@PipeIDs"].Value = pips;
                                myCommand.Parameters.Add("@DateStart", SqlDbType.VarChar);
                                myCommand.Parameters["@DateStart"].Value = sbutract;
                                myCommand.Parameters.Add("@DateEnd", SqlDbType.VarChar);
                                myCommand.Parameters["@DateEnd"].Value = currentDate;
                                myCommand.Parameters.Add("@cnt", SqlDbType.VarChar);
                                myCommand.Parameters["@cnt"].Value = cnt;
                                myCommand.Parameters.Add("@Status", SqlDbType.VarChar);
                                myCommand.Parameters["@Status"].Value = SendStatus;
                                myCommand.Parameters.Add("@Destination_no", SqlDbType.VarChar);
                                myCommand.Parameters["@Destination_no"].Value = this.txtDesno.Text;
                                myCommand.Parameters.Add("@Sms_SendContents", SqlDbType.VarChar);
                                myCommand.Parameters["@Sms_SendContents"].Value = this.txtSmsContents.Text;
                                myCommand.Parameters.Add("@user_code", SqlDbType.VarChar);
                                myCommand.Parameters["@user_code"].Value = selectUser;
                                myCommand.Parameters.Add("@MessageType", SqlDbType.VarChar);
                                myCommand.Parameters["@MessageType"].Value = "1";
                                myCommand.Parameters.Add("@IsStatus", SqlDbType.VarChar);
                                myCommand.Parameters["@IsStatus"].Value = userInfo.IsStatus;
                                myCommand.Parameters.Add("@pageIndex", SqlDbType.VarChar);
                                myCommand.Parameters["@pageIndex"].Value = this.PageInfo.CurrentPageIndex - 1;
                                myCommand.Parameters.Add("@pageSizes", SqlDbType.VarChar);
                                myCommand.Parameters["@pageSizes"].Value = this.PageInfo.PageSize;
                                //添加输出参数
                                myCommand.Parameters.Add("@view_name", SqlDbType.VarChar, 50);
                                myCommand.Parameters["@view_name"].Direction = ParameterDirection.Output;
    
    
                                myCommand.ExecuteNonQuery();
                                DataAdapter.SelectCommand = myCommand;
    
                                DataAdapter.Fill(MyDataSet, "table");
    
                                //获取数据条数
                                if (MyDataSet != null && MyDataSet.Tables.Count > 0)
                                {
                                    for (int i = 0; i < MyDataSet.Tables[0].Rows.Count; i++)
                                    {
                                        PageCount += int.Parse(MyDataSet.Tables[0].Rows[i]["Counts"].ToString());
                                    }
                                }
                                MyDataSet.Dispose();
                                //得到存储过程输出参数
                                View_Name = myCommand.Parameters["@view_name"].Value.ToString();
                                if (myConnection.State == ConnectionState.Open)
                                {
                                    myConnection.Close();
                                }
                            }
                            catch (Exception ex)
                            {
                                Base.WriteErrorLog("下发记录查询3天内——创建视图异常：" + ex.Message.Replace(Environment.NewLine, string.Empty), HttpContext.Current.Server.MapPath("ErrorLog\\"));
                            }
                            if (View_Name != "")
                            {
                                Dictionary<string, object> dicTo = new Dictionary<string, object>();
                                dicTo.Add("viewName", View_Name);
                                dicTo.Add("pageIndex", int.Parse(this.PageInfo.CurrentPageIndex.ToString()) - 1);
                                dicTo.Add("pageSizes", this.PageInfo.PageSize);
                                DataSet ds = Base.GetDataSet("dbo.GetXiaFa_Record_View", dicTo);
                                if (ds != null && ds.Tables.Count > 0)
                                {
                                    dt2 = ds.Tables[0];
                                }
                            }