从C＃运行SQL Server参数查询 [英] Run SQL Server Parameter Query From C#

问题描述

从C＃运行sql server参数查询并返回数据集的正确方法是什么？ 我尝试了以下语法 - 但似乎不是一个完整的数据集。

What is proper way to run a sql server parameter query from C# and return a dataset?  I have tried the below syntax - but does not appear to be a complete dataset that is returned.

private void CboBoxChangeEvent(object sender, EventArgs e)

        {

            DataSet ds;

            DataSet ds1 = new DataSet();

            txtcn.Visible = true;

            lblcn.Visible = true;



            if (cboNewOrOld.Text == "New")

            {                

                ds = dal.RunSQLServerStoredProcedure("_GETInfo1");

                AIN = ds.Tables[0];

            }

 



            if (cboNewOrOld.Text == "OLD")

            {

                SqlConnection sqlconn = new SqlConnection(StartupForm.ServerAddressToUse);

                SqlDataAdapter sda = new SqlDataAdapter();

                SqlCommand sqlcmd = new SqlCommand("_GETnfocn", sqlconn);

                sqlcmd.CommandType = CommandType.StoredProcedure;

                sqlcmd.Parameters.AddWithValue("@cn", txtcn.Text);

                sda.SelectCommand = sqlcmd;

                sda.Fill(ds1, "cam");

                dtArtInfoCampaign = ds1.Tables[0];

            }

        }



        private void cnInput(object sender, EventArgs e)

        {

            if (txtcn.Text != "")

            {

                dgvai.Visible = true;

                btnExportToExcel.Visible = true;

                btnSaveEdits.Visible = true;

                if (cboNewOrOld.Text == "New")

                {


                   System.Data.DataColumn newColumn = new System.Data.DataColumn("cn", typeof(System.String));

                   newColumn.DefaultValue = txtcn.Text;

                   AIN.Columns.Add(newColumn);


                   dgvai.DataSource = AIN;

                   dgvai.AutoResizeColumns();

                }

                if (cboNewOrOld.Text == "OLD")

                {

                    dgvai.DataSource = AIN;

                    dgvai.AutoResizeColumns();

                }

            }

        }

推荐答案

很高兴看到SP定义和dal.RunSQLServerStoredProcedure的代码。这很简单。请参阅以下演示并不关心它是基于Web的，因为桌面应用程序中的代码逻辑是相同的。

Well it would be good to see the SP definition and the code for dal.RunSQLServerStoredProcedure. It's really simple. See the following demo don't concern yourself that it's web based as the code logic is the same in desktop apps.

https://www.aspsnippets.com/Articles/Load-Fill -Populate-DataTable-from-Stored-Procedure-in-ASPNet-using-C-and-VBNet.aspx

https://www.aspsnippets.com/Articles/Load-Fill-Populate-DataTable-from-Stored-Procedure-in-ASPNet-using-C-and-VBNet.aspx

或者这是类似的代码路径但是有一个DataTable（如果你是对多个表不感兴趣）

or this which is similar code path but does a DataTable (if you are not interested in more than one table)

DataTable table = new DataTable();
using(var con = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString))
   using(var cmd = new SqlCommand("usp_GetABCD", con))
      using(var da = new SqlDataAdapter(cmd))
      {
         cmd.CommandType = CommandType.StoredProcedure;
         da.Fill(table);
      }
   }
}

这篇关于从C＃运行SQL Server参数查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！