来自Microsof SQL Server 2005的存储过程 [英] Stored Procedures from Microsof SQL Server 2005

问题描述

我有一个在Microsoft SQL Server 2005中创建的名为" Workers "的表.它具有4列- ID ，名字， LastName 和 JobTitle ....它们已经填充了一些值，例如1，Vicky，Sriv，QA Lead.然后，我编写了一个非常简单的从Workers中选择*的存储过程，并将其命名为" usp_Displayallusers ".
直到上述步骤对我来说一切正常，.....我可以使用" exec usp_displayallusers "
运行相同的过程
但是，我真正遇到的问题是在Microsoft Visual Studio 2008(C#Windows项目/解决方案)中调用它.我知道可以通过DataTable，DataSets或DataGrids调用它.我在这里阅读了非常好的文章，这些文章确实使我对此有所了解.但是我仍然无法解决这个问题，所以请告诉我完整的代码，以便使我的笨拙的大脑中有一些东西，大声笑:)

I have a table named "Workers" created in Microsoft SQL Server 2005. It has 4 Columns - ID, FirstName, LastName, and JobTitle.... They are already filled with some values like 1, Vicky, Sriv, QA Lead. Then i wrote a very simple stored procedure of select * from Workers and named it as "usp_Displayallusers".
Till the above said steps everything worked fine for me..... I can run the same procedure using "exec usp_displayallusers"

But the real problem i got is calling it in Microsoft Visual Studio 2008 (C# Windows Project/Solution). I know it can be called via DataTable, DataSets or DataGrids. I read very good articles here that really made me aware about them. But still i was not able to solve this problem, so please tell me complete code, so that my dumb brain might get something in it, lol :)

推荐答案

check以下示例:

http://www.java2s.com/Tutorial/CSharp/0560__ADO.Net/Callstoredprocedurewithnoparameter.htm [ ^ ]

check the following example:

http://www.java2s.com/Tutorial/CSharp/0560__ADO.Net/Callstoredprocedurewithnoparameter.htm[^]

我想您想在GridView中显示所有记录，如果是，请尝试以下操作:

I guess you want to display all records in a GridView, if yes then try this:

 string strcon = "Data Source=ABC-PC\\SQLEXPRESS;Initial Catalog=mydb;Integrated Security=True";
        SqlConnection conn;

protected void Page_Load(object sender, EventArgs e)
        {
            try
            {
                conn = new SqlConnection(strcon);
                conn.Open();
                SqlCommand cmd = new SqlCommand("usp_Displayallusers", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
                DataSet ds = new DataSet();

da.Fill(ds);
                this.GridView1.DataSource = ds;
                this.GridView1.DataBind();

            }
            catch(Exception ex)
            {
                Response.Clear();
                Response.Write("<h4>" + ex.Message.ToString() + "</h4>");
            }
        }

希望对您有所帮助:)

对于更多查询，请在此处进行评论！！！

hope it helps :)

for further queries comment here!!

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace CallingStoredProcedures
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        DataTable table = new DataTable();
        int i=0;
        int MaxRows; 
        private void btnGet_Click(object sender, EventArgs e)
        {

            if (i < MaxRows)
            {
                textBox1.Text = table.Rows[i][0].ToString();
                textBox2.Text = table.Rows[i][1].ToString();
                textBox3.Text = table.Rows[i][2].ToString();
                i++;
            }           
     }
        public DataTable GetTable()
        {            
            table.Columns.Add("FirstName", typeof(string));
            table.Columns.Add("LastName", typeof(string));
            table.Columns.Add("JobTitle", typeof(string));
            return table;
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            GetTable();
            SqlConnection con = new SqlConnection("Persist Security Info=False;database=TestDB;server=xyz;Connect Timeout=30;user=abc;pwd=****;");
            con.Open();
            SqlCommand command = new SqlCommand("usp_displayallusers", con);
            command.CommandType = CommandType.StoredProcedure;
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                table.Rows.Add(reader[1].ToString(), reader[2].ToString(), reader[3].ToString());
            }
            MaxRows = table.Rows.Count;
        }
   }
}

我得到的结果是浏览我的SQL数据库表"Workers"中已经存在的记录.
每次单击时，它都会在我的可视表单上显示下一行.

The result i am getting is navigation of my records that are already in my SQL Database table "Workers".
On every click, it shows next row on my Visual Form.

这篇关于来自Microsof SQL Server 2005的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！