sql表值参数
using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace SQLServerDAL //可以修改成实际项目的命名空间名称
{
public abstract class DbHelperSQL
{
protected static string connectionString = @"Data Source=.;Initial Catalog=SGPZ;User Id=sa;Password=abc.123";
public DbHelperSQL()
{
}
public static DataSet RunProc(DataTable dt)
{
SqlConnection userConnection = new SqlConnection(connectionString);
SqlCommand userCommand = new SqlCommand("p_student", userConnection);
userCommand.CommandType = CommandType.StoredProcedure;//采用存储过程
userCommand.Parameters.Add("@tstudent", SqlDbType.Structured);//存储过程参数
userCommand.Parameters["@tstudent"].Value = dt;//给参数赋值
userCommand.Connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter(userCommand);
DataSet ds = new DataSet();
adapter.Fill(ds);
return ds;
}
/**/
/// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public static DataSet Query(string SQLString)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
DataSet ds = new DataSet();
try
{
connection.Open();
SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
command.Fill(ds, "ds");
}
catch (System.Data.SqlClient.SqlException ex)
{
throw new Exception(ex.Message);
}
return ds;
}
}
}
}
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.Diagnostics;
using System.Data.SqlClient;
namespace WindowsFormsApplication11
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
}
private void button1_Click(object sender, EventArgs e)
{
System.Diagnostics.Stopwatch stopwatch = new Stopwatch();
stopwatch.Start(); // 开始监视代码运行时间
DataSet dt = SQLServerDAL.DbHelperSQL.Query("select * from student ");
dataGridView1.DataSource = SQLServerDAL.DbHelperSQL.RunProc( dt.Tables[0]).Tables[0].DefaultView;
stopwatch.Stop(); // 停止监视
TimeSpan timespan = stopwatch.Elapsed; // 获取当前实例测量得出的总时间
double hours = timespan.TotalHours; // 总小时
double minutes = timespan.TotalMinutes; // 总分钟
double seconds = timespan.TotalSeconds; // 总秒数
double milliseconds = timespan.TotalMilliseconds; // 总毫秒数
MessageBox.Show(seconds.ToString());
}
}
}
USE [SGPZ]
GO
/****** Object: StoredProcedure [dbo].[p_student] Script Date: 07/05/2013 17:19:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[p_student]
@tstudent tstudent READONLY
AS
begin
SELECT * INTO #tstudent FROM @tstudent
UPDATE #tstudent SET STUNAME='周杰伦'
SELECT * FROM #tstudent
DROP TABLE #tstudent
END
