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()); } } } } } 代码完成了。给大家贴出来学习下~哈哈哈
最佳答案
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
这个是方法 你connectionString赋进去 就可以直接用了
@mushishi: 在哪块赋进去呢？
@mushishi: private string connectionString=ConfigManager.ConnectionStrings.ConnectionStrings["连接字符串的key"]; 加上这段
@董毛毛: 在方法外定义一个string类型的 connectionString 值的话就是你的连接字符串
@董毛毛:
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了呀
@董毛毛:
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);
