首页新闻找找看学习计划

C#调用存储过程

0
悬赏园豆:15 [已解决问题] 解决于 2013-12-17 17:19
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO.Ports;
using System.Collections;
using System.Threading;
using System.Timers;
using System.Xml;
using System.Runtime.InteropServices;
using System.Data.SqlClient;
using System.Configuration;

namespace 串口通信
{
    public partial class Form1 : Form
    {
        public bool isFirstTimeToStart = true;
        private int timeStamp = 50;

        public Form1()
        {   
            InitializeComponent();   
        }
        /// <summary>
        /// 初始化串口属性
        /// </summary>
        public void InitializeSerialPort()
        {
            if (isFirstTimeToStart == true)
            {
                port.DataBits = 8;
                port.PortName = ConfigurationManager.AppSettings["port"].ToString();
                port.BaudRate = 19200;
                port.DiscardNull = false;
                port.DtrEnable = false;
                port.Handshake = Handshake.None;
                port.Parity = Parity.None;
                port.ParityReplace = Convert.ToByte("63");
                port.RtsEnable = false;
                port.StopBits = StopBits.One;
                isFirstTimeToStart = false;
            }
        }

        private void comboBox1_DropDown(object sender, EventArgs e)
        {
            ///加载计算机上所有的COM串口
            comboBox1.Items.Clear();
            string[] ports = SerialPort.GetPortNames();
            foreach (string p in ports)
            {
                comboBox1.Items.Add(p);
            }
        }

        //打开串口
        private void button1_Click(object sender, EventArgs e)
        {
            InitializeSerialPort();
            port.Open();
            port.DataReceived += new SerialDataReceivedEventHandler(port_DataReceived);
        }

        //执行串口方法
        void port_DataReceived(object sender, SerialDataReceivedEventArgs e)
        {
            ArrayList array = new ArrayList();
            READAGAIN:
                while (port.BytesToRead > 0)
                {
                    array.Add((byte)port.ReadByte());
                }
                Thread.Sleep(timeStamp);
                if (port.BytesToRead > 0)
                {
                    goto READAGAIN;
                }
                InitializedListView2(array);
        }
        delegate void SetInfo(ArrayList infos);


        public void InitializedListView2(ArrayList arrayList)
        {
            try
            {
                if (this.label2.InvokeRequired)
                {
                    SetInfo ss = new SetInfo(InitializedListView2);
                    this.Invoke(ss, new object[] { arrayList });
                }
                else
                {
                    byte[] data = new byte[arrayList.Count + 1];
                    string str = "";
                    for (int i = 0; i < arrayList.Count; i++)
                    {
                        data[i] = (byte)(arrayList[i]);
                        string aa = data[i].ToString("X");
                        str += (data[i].ToString("X").Length == 2 ? data[i].ToString("X") : "0" + data[i].ToString("X")) + " ";
                    }
                    if (str.Trim().Equals("00 1E 98"))

                    {
                        label2.Text = "离开红外感应!";
                    }
                    else if (str.Trim().Equals("00 1E 9E"))
                    {
                        label2.Text = "进入红外感应!";
                        MessageBox.Show("警告", "确认", MessageBoxButtons.OKCancel,MessageBoxIcon.Warning);
                        int sleepTime = Convert.ToInt32(ConfigurationManager.AppSettings["sleepTime"]);
                        Thread.Sleep(sleepTime);
                    }
                    else
                    {
                        label2.Text = "错误!";
                    }
                    textBox1.Text += str + "\r\n";
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }

        protected void DataReceived(string info)
        {
           // rtbSerialInfo.Text += (SignalToHexCode(info).ToUpper()) + "\r\n";
            switch (info)
            {
                case "00 1E 98":
                    label2.Text = "离开红外感应!";
                    break;
                case "00 1E 9E":
                    label2.Text = "进入红外感应!";
                    break;
                default:
                    label2.Text = "错误!";
                    break;
            }
        }

        private void notifyIcon1_MouseDoubleClick(object sender, MouseEventArgs e)
        {
            if (e.Button == MouseButtons.Left)
            {
                this.Show();
                this.WindowState = FormWindowState.Normal;
                this.Activate();
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            string con = ConfigurationManager.AppSettings["Sqlconn"].ToString();
            port.PortName = ConfigurationManager.AppSettings["port"].ToString();
            SqlConnection conn = new SqlConnection(con);
            conn.Open();
            SqlDataReader returnReader;
            SqlCommand cmd = new SqlCommand();
            cmd.CommandType = CommandType.StoredProcedure;        //设置cmd的类型为存储过程
            returnReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            cmd.CommandText = "EXEC usp_CallSceneStep";       //存储过程名
            cmd.Connection = conn;

            //SqlParameter pCustomerID = new SqlParameter();
            //pCustomerID.ParameterName = "@CustomerID";
            //pCustomerID.SqlDbType = SqlDbType.NChar;
            //pCustomerID.Value = "ALFKI";
            //cmd.Parameters.Add(pCustomerID);
          
            if (this.WindowState == FormWindowState.Minimized)
            {
                this.Visible = false;
            }
        }

        private static void OnTimedEvent(object source, ElapsedEventArgs e)
        {
            Console.WriteLine("The Elapsed event was raised at {0}", e.SignalTime);
        }

        //鼠标双击图标,显示界面
        private void notifyIcon1_MouseClick(object sender, MouseEventArgs e)
        {
            this.Visible = true;
            this.TopMost = true;
            this.WindowState = FormWindowState.Normal;
            this.Activate();
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)  //关闭按钮事件
        {
            e.Cancel = true;
            this.Hide();
        }

        //最小化到托盘
        private void Form1_SizeChanged(object sender, EventArgs e)     //最小化事件按钮
        {
            this.Hide();
        }

        //托盘右键显示
        private void hideMenuItem_Click(object sender, EventArgs e)
        {
            this.Show();
            this.WindowState = FormWindowState.Normal;
            this.Activate();
        }

        //托盘右键隐藏
        private void showMenuItem_Click(object sender, EventArgs e)
        {
            this.Hide();
        }

        //托盘右键退出
        private void exitMenuItem_Click_1(object sender, EventArgs e)
        {
            if (MessageBox.Show("你确定要退出终端服务程序吗?", "确认", MessageBoxButtons.OKCancel,
                MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == DialogResult.OK)
            {
                notifyIcon1.Visible = false;
                this.Close();
                this.Dispose();
                Application.Exit();
            }      
        }
    }   
}

 

需求要写一个存储过程,

存储过程名称为:EXEC usp_CallSceneStep,

情景号:96,-1

Provider=SQLOLEDB.1;
Persist Security Info=False;
User ID=sa;Password=vrmedia&dfjx;
Initial Catalog=vrmedia_db;
Data Source=192.168.0.2,2433

 

如何去调用?要代码。

问题补充:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.IO.Ports;
using System.Collections;
using System.Threading;
using System.Timers;
using System.Xml;
using System.Runtime.InteropServices;
using System.Data.SqlClient;
using System.Configuration;

namespace 串口通信
{
    public partial class Form1 : Form
    {
        public bool isFirstTimeToStart = true;
        private int timeStamp = 50;

        public Form1()
        {   
            InitializeComponent();   
        }
        /// <summary>
        /// 初始化串口属性
        /// </summary>
        public void InitializeSerialPort()
        {
            if (isFirstTimeToStart == true)
            {
                port.DataBits = 8;
                port.PortName = ConfigurationManager.AppSettings["port"].ToString();
                port.BaudRate = 19200;
                port.DiscardNull = false;
                port.DtrEnable = false;
                port.Handshake = Handshake.None;
                port.Parity = Parity.None;
                port.ParityReplace = Convert.ToByte("63");
                port.RtsEnable = false;
                port.StopBits = StopBits.One;
                isFirstTimeToStart = false;

            }
        }

        //private void comboBox1_DropDown(object sender, EventArgs e)
        //{
        //    ///加载计算机上所有的COM串口
        //    comboBox1.Items.Clear();
        //    string[] ports = SerialPort.GetPortNames();
        //    foreach (string p in ports)
        //    {
        //        comboBox1.Items.Add(p);
        //    }
        //}

        ////打开串口
        //private void button1_Click(object sender, EventArgs e)
        //{
        //    InitializeSerialPort();
        //    port.Open();
        //    port.DataReceived += new SerialDataReceivedEventHandler(port_DataReceived);
        //}

        //执行串口方法
        void port_DataReceived(object sender, SerialDataReceivedEventArgs e)
        {
            ArrayList array = new ArrayList();
            READAGAIN:
                while (port.BytesToRead > 0)
                {
                    array.Add((byte)port.ReadByte());
                }
                Thread.Sleep(timeStamp);
                if (port.BytesToRead > 0)
                {
                    goto READAGAIN;
                }
                InitializedListView2(array);
        }
        delegate void SetInfo(ArrayList infos);


        public void InitializedListView2(ArrayList arrayList)
        {
            try
            {
                if (this.label2.InvokeRequired)
                {
                    SetInfo ss = new SetInfo(InitializedListView2);
                    this.Invoke(ss, new object[] { arrayList });
                }
                else
                {
                    byte[] data = new byte[arrayList.Count + 1];
                    string str = "";
                    for (int i = 0; i < arrayList.Count; i++)
                    {
                        data[i] = (byte)(arrayList[i]);
                        string aa = data[i].ToString("X");
                        str += (data[i].ToString("X").Length == 2 ? data[i].ToString("X") : "0" + data[i].ToString("X")) + " ";
                    }
                    if (str.Trim().Equals("00 1E 98"))

                    {
                        label2.Text = "离开红外感应!";
                    }
                    else if (str.Trim().Equals("00 1E 9E"))
                    {
                        label2.Text = "进入红外感应!";
                        //初始化方法名,并调用方法
                        Operater op = new Operater();
                        op.QueryStuNameById();
                       
                    }
                    else
                    {
                        label2.Text = "";
                    }
                    //通过App.config调用程序休眠时间
                    int sleepTime = Convert.ToInt32(ConfigurationManager.AppSettings["sleepTime"]);
                    Thread.Sleep(sleepTime);
                    textBox1.Text += str + "\r\n";
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message);
            }
        }

        protected void DataReceived(string info)
        {
            switch (info)
            {
                case "00 1E 98":
                    label2.Text = "离开红外感应!";
                    break;
                case "00 1E 9E":
                    label2.Text = "进入红外感应!";
                    break;
                default:
                    label2.Text = "错误!";
                    break;
            }
        }

        private void notifyIcon1_MouseDoubleClick(object sender, MouseEventArgs e)
        {
            if (e.Button == MouseButtons.Left)
            {
                this.Show();
                this.WindowState = FormWindowState.Normal;
                this.Activate();
            }
        }
       
        private void Form1_Load(object sender, EventArgs e)
        {
            InitializeSerialPort();
            port.Open();
            port.DataReceived += new SerialDataReceivedEventHandler(port_DataReceived);
            string con = ConfigurationManager.AppSettings["Sqlconn"].ToString();

            if (this.WindowState == FormWindowState.Minimized)
            {
                this.Visible = false;
            }
        }
        private static void OnTimedEvent(object source, ElapsedEventArgs e)
        {
            Console.WriteLine("The Elapsed event was raised at {0}", e.SignalTime);
        }

        //鼠标双击图标,显示界面
        private void notifyIcon1_MouseClick(object sender, MouseEventArgs e)
        {
            this.Visible = true;
            this.TopMost = true;
            this.WindowState = FormWindowState.Normal;
            this.Activate();
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)  //关闭按钮事件
        {
            e.Cancel = true;
            this.Hide();
        }

        //最小化到托盘
        private void Form1_SizeChanged(object sender, EventArgs e)     //最小化事件按钮
        {
            this.Hide();
        }

        //托盘右键显示
        private void hideMenuItem_Click(object sender, EventArgs e)
        {
            this.Show();
            this.WindowState = FormWindowState.Normal;
            this.Activate();
        }

        //托盘右键隐藏
        private void showMenuItem_Click(object sender, EventArgs e)
        {
            this.Hide();
        }

        //托盘右键退出
        private void exitMenuItem_Click_1(object sender, EventArgs e)
        {
            if (MessageBox.Show("你确定要退出终端服务程序吗?", "确认", MessageBoxButtons.OKCancel,
                MessageBoxIcon.Question, MessageBoxDefaultButton.Button2) == DialogResult.OK)
            {
                notifyIcon1.Visible = false;
                this.Close();
                this.Dispose();
                Application.Exit();
            }      
        }
        public class Operater
        {
            private string ConStr = ConfigurationManager.AppSettings["Sqlconn"].ToString();
            private SqlConnection sqlCon = null;
            private SqlCommand sqlComm = null;
            public void QueryStuNameById()
            {
                try
                {
                    using (sqlCon = new SqlConnection(ConStr))
                    {
                        sqlCon.Open();
                        sqlComm = new SqlCommand("usp_CallSceneStep", sqlCon);
                        //设置命令的类型为存储过程
                        sqlComm.CommandType = CommandType.StoredProcedure;
                        //设置参数
                        sqlComm.Parameters.Add("@SceneID", SqlDbType.Int);
                        //注意输出参数要设置大小,否则size默认为0,
                        sqlComm.Parameters.Add("@Step", SqlDbType.Int);
                        //为参数赋值
                        sqlComm.Parameters["@SceneID"].Value = ConfigurationManager.AppSettings["qjh"].ToString();
                        sqlComm.Parameters["@Step"].Value = "-1";
                        //执行
                        sqlComm.ExecuteNonQuery();
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.ToString());
                }
            }
        }
    }   
}

代码完成了。给大家贴出来学习下~哈哈哈
董毛毛的主页 董毛毛 | 初学一级 | 园豆:122
提问于:2013-12-16 17:14
< >
分享
最佳答案
0
private string connectionString="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=vrmedia&dfjx;Initial Catalog=vrmedia_db;Data Source=192.168.0.2,2433"  

public static SqlDataReader RunProcedure(string storedProcName, IDataParameter[] parameters) //存储过程名称, 参数数组
{
SqlConnection connection = new SqlConnection(connectionString);
SqlDataReader returnReader;
connection.Open();
SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters);  //自己写的一个sqlcommand的构造方法,见下面
command.CommandType = CommandType.StoredProcedure;  //设置为存储过程
returnReader = command.ExecuteReader(CommandBehavior.CloseConnection);
return returnReader;

}

 //准备SqlCommand

private static SqlCommand BuildQueryCommand(SqlConnection connection, string storedProcName, IDataParameter[] parameters)
{
SqlCommand command = new SqlCommand(storedProcName, connection);
command.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
if (parameter != null)
{

if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
command.Parameters.Add(parameter);
}
}

return command;
}



调用:
RunProcedure("usp_CallSceneStep",null);

 

收获园豆:15
mushishi | 菜鸟二级 |园豆:230 | 2013-12-17 00:20

这个是方法  你connectionString赋进去 就可以直接用了

mushishi | 园豆:230 (菜鸟二级) | 2013-12-17 00:21

@mushishi: 在哪块赋进去呢?

董毛毛 | 园豆:122 (初学一级) | 2013-12-17 10:17

@mushishi:   private string connectionString=ConfigManager.ConnectionStrings.ConnectionStrings["连接字符串的key"];  加上这段

mushishi | 园豆:230 (菜鸟二级) | 2013-12-17 10:26

@董毛毛:  在方法外定义一个string类型的 connectionString 值的话就是你的连接字符串

mushishi | 园豆:230 (菜鸟二级) | 2013-12-17 10:29

@董毛毛: 

private string connectionString="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=vrmedia&dfjx;Initial Catalog=vrmedia_db;Data Source=192.168.0.2,2433"


调用就直接RunProcedure("usp_CallSceneStep",null); 就OK了呀
mushishi | 园豆:230 (菜鸟二级) | 2013-12-17 14:08

@董毛毛: 

    string con = ConfigurationManager.AppSettings["Sqlconn"].ToString();
               SqlConnection conn = new SqlConnection(con);
        conn.Open();
        SqlDataReader returnReader;
        SqlCommand cmd = new SqlCommand();
        //cmd的属性设置要在executeReader之前
        cmd.CommandText = "EXEC usp_CallSceneStep";     //SQL语句 不是存储过程名字
        cmd.Connection = conn;

     //   cmd.CommandType = CommandType.StoredProcedure;        //这个不用了" EXEC usp_CallSceneStep "是SQL语句
        returnReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
mushishi | 园豆:230 (菜鸟二级) | 2013-12-17 15:20
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册