CREATE PROCEDURE UpdateTEST
(
@id int,
@test1 varchar(50)
)
AS
UPDATE test SET test1=@test1
WHERE ID=@ID
GO
查询分析起里执行:UpdateTEST 3,'123'执行 OK
如果用c#这样写对吗?每次要写这么多呀:
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(SQL, con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param; param = cmd.Parameters.Add("@test1", SqlDbType.VarChar, 50);
param.Value = "Beverages";
param = cmd.Parameters.Add("@ID", SqlDbType.Int);
param.Value = 1;
con.Open();
int rowsAffected = cmd.ExecuteNonQuery();
con.Close();
------------------------------------------------------------------------------------------能否做一个类,里面做一个调用存储过程的方法,方法接受两个参数,
一个是存储过程的名字,
另一个参数是一个HashTable(里面有所有参数的键值对)。 然后这样来调用:DataAccess.ExecRestore("InsertRecord",参数表集合) ,这样是不是可以通用呢
------------------------------------------------------------------------------------------
(
@id int,
@test1 varchar(50)
)
AS
UPDATE test SET test1=@test1
WHERE ID=@ID
GO
查询分析起里执行:UpdateTEST 3,'123'执行 OK
如果用c#这样写对吗?每次要写这么多呀:
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(SQL, con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter param; param = cmd.Parameters.Add("@test1", SqlDbType.VarChar, 50);
param.Value = "Beverages";
param = cmd.Parameters.Add("@ID", SqlDbType.Int);
param.Value = 1;
con.Open();
int rowsAffected = cmd.ExecuteNonQuery();
con.Close();
------------------------------------------------------------------------------------------能否做一个类,里面做一个调用存储过程的方法,方法接受两个参数,
一个是存储过程的名字,
另一个参数是一个HashTable(里面有所有参数的键值对)。 然后这样来调用:DataAccess.ExecRestore("InsertRecord",参数表集合) ,这样是不是可以通用呢
------------------------------------------------------------------------------------------
Data Access Application Block for .NETv1 and v2
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnanchor/html/Anch_EntDevAppArchPatPrac.aspv3
http://www.gotdotnet.com/Community/Workspaces/viewuploads.aspx?id=c20d12b0-af52-402b-9b7c-aaeb21d1f431
在SQLServer查询分析器里
select * from INFORMATION_SCHEMA.PARAMETERS where SPECIFIC_NAME=存储过程名
order by ORDINAL_POSITION 可以看到该存储过程的信息.
主要能用到的字段:
SPECIFIC_NAME 存储过程的名字
PARAMETER_NAME 该存储过程中参数的名字
DATA_TYPE 该参数的类型
ORDINAL_POSITION 参数的位置
PARAMETER_MODE 参数输入/输出方式(IN or Out)
我的::;
/// <summary>
/// 单
/// </summary>
/// 列表
/// </summary>
public SqlDataReader GetAssinAllSubPro(string vCommandType,string vMainProID)
{
SqlConnection sqlcon = new SqlConnection(Configuration.ConnectionString);
SqlCommand sqlcom =new SqlCommand();
sqlcon.Open();
sqlcom.Connection = sqlcon;
sqlcom.CommandType = CommandType.StoredProcedure;
sqlcom.Parameters.Add(new SqlParameter("@CommandType",SqlDbType.VarChar,20));
sqlcom.Parameters["@CommandType"].Value =vCommandType;
sqlcom.Parameters.Add(new SqlParameter("@MainProjectID",SqlDbType.VarChar,20));
sqlcom.Parameters["@MainProjectID"].Value = vMainProID;
sqlcom.CommandText ="AssinGetAllSubPro" ;
SqlDataReader my = null;
try
{
my = sqlcom.ExecuteReader();
}
catch (Exception e)
{
throw(e);
}
finally
{
}
return my; }别的地方调用
using DataAcess;
public SqlDataReader GetAllSubPro(string vCommandType,string vMainProjectID)
{
return (new AssignPro()).GetAssinAllSubPro(vCommandType,vMainProjectID);
}
像这样执行SQL语句:SQLCOMMAND.TEXT="EXEC 存储过程名 参数1,参数2.....";
中已经定义了,有个sqlHelper类。
using System;
using System.Configuration;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using System.Collections;namespace Service.SqlDataProvider
{ /// <summary>
/// The SqlHelper class is intended to encapsulate high performance, scalable best practices for
/// common uses of SqlClient.
/// </summary>
public sealed class SqlHelper
{
#region private utility methods & constructors //Since this class provides only static methods, make the default constructor private to prevent
//instances from being created with "new SqlHelper()".
private SqlHelper() {} /// <summary>
/// This method is used to attach array of SqlParameters to a SqlCommand.
///
/// This method will assign a value of DbNull to any parameter with a direction of
/// InputOutput and a value of null.
///
/// This behavior will prevent default values from being used, but
/// this will be the less common case than an intended pure output parameter (derived as InputOutput)
/// where the user provided no input value.
/// </summary>
/// <param name="command">The command to which the parameters will be added</param>
/// <param name="commandParameters">an array of SqlParameters tho be added to command</param>
private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
{
foreach (SqlParameter p in commandParameters)
{
//check for derived output value with no value assigned
if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
} /// <summary>
/// This method assigns an array of values to an array of SqlParameters.
/// </summary>
/// <param name="commandParameters">array of SqlParameters to be assigned values</param>
/// <param name="parameterValues">array of Components holding the values to be assigned</param>
private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
{
if ((commandParameters == null) || (parameterValues == null))
{
//do nothing if we get no data
return;
} // we must have the same number of values as we pave parameters to put them in
if (commandParameters.Length != parameterValues.Length)
{
throw new ArgumentException("参数与值数量不匹配.");
} //iterate through the SqlParameters, assigning the values from the corresponding position in the
//value array
for (int i = 0, j = commandParameters.Length; i < j; i++)
{
commandParameters[i].Value = parameterValues[i];
}
} /// <summary>
/// This method opens (if necessary) and assigns a connection, transaction, command type and parameters
/// to the provided command.
/// </summary>
/// <param name="command">the SqlCommand to be prepared</param>
/// <param name="connection">a valid SqlConnection, on which to execute this command</param>
/// <param name="transaction">a valid SqlTransaction, or 'null'</param>
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
/// <param name="commandText">the stored procedure name or T-SQL command</param>
/// <param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param>
private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters)
{
//if the provided connection is not open, we will open it
if (connection.State != ConnectionState.Open)
{
connection.Open();
} //associate the connection with the command
command.Connection = connection; //set the command text (stored procedure name or SQL statement)
command.CommandText = commandText; //if we were provided a transaction, assign it.
if (transaction != null)
{
command.Transaction = transaction;
} //set the command type
command.CommandType = commandType; //attach the command parameters if they are provided
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
} return;
}
#endregion private utility methods & constructors #region DataHelpers public static string CheckNull(object obj)
{
return (string) obj;
} public static string CheckNull(DBNull obj)
{
return null;
} #endregion