[Pro-test] C# führt gespeicherte Prozeduren aus und gibt den GUID-Primärschlüssel zurück

Kleiner Abschaum · Veröffentlicht am 05.01.2016 15:09:07

public class DBHelper
{
private static readonly string ConnStr = ConfigurationManager.AppSettings["Test"];
/// <summary>
/// 执行sql语句
/// </summary>
/// <param name="strSql"></param>
/// <returns>返回影响行数</returns>
public int ExecuteNonQuery(string strSql)
{
int result = 0;
using (SqlConnection con = new SqlConnection(ConnStr))
{
if (con.State == ConnectionState.Closed)
con.Open();
using (SqlCommand cmd = new SqlCommand(strSql, con))
{
result = cmd.ExecuteNonQuery();
}
con.Close();
return result;
}
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="strProcedure"></param>
/// <param name="param"></param>
/// <returns>返回影响行数</returns>
public int ExecuteNonQuery(string strProcedure, SqlParameter[] param)
{
int result = 0;
using (SqlConnection con = new SqlConnection(ConnStr))
{
if (con.State == ConnectionState.Closed)
con.Open();
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = strProcedure;
cmd.Connection = con;
foreach (var item in param)
{
cmd.Parameters.Add(item);
}
result = cmd.ExecuteNonQuery();
}
con.Close();
return result;
}
}
/// <summary>
/// 查询
/// </summary>
/// <param name="strSql"></param>
/// <returns>返回DataSet</returns>
public DataSet ExecuteDataSet(string strSql)
{
using (DataSet ds = new DataSet())
{
using (SqlConnection con = new SqlConnection(ConnStr))
{
if (con.State == ConnectionState.Closed)
con.Open();
using (SqlCommand cmd = new SqlCommand(strSql, con))
{
using (SqlDataAdapter sda = new SqlDataAdapter())
{
sda.SelectCommand = cmd;
sda.Fill(ds);
}
}
con.Close();
}
return ds;
}
}
public DataSet ExecuteDataSet(string strProcedure, SqlParameter[] param)
{
using (DataSet ds = new DataSet())
{
using (SqlConnection con = new SqlConnection(ConnStr))
{
if (con.State == ConnectionState.Closed)
con.Open();
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = strProcedure;
cmd.Connection = con;
foreach (var item in param)
{
cmd.Parameters.Add(item);
}
using (SqlDataAdapter sda = new SqlDataAdapter())
{
sda.SelectCommand = cmd;
sda.Fill(ds);
}
}
con.Close();
}
return ds;
}
}
public int ExecuteNonQueryReturnRecordID(string strProcedure, SqlParameter[] param)
{
int result = 0;
using (SqlConnection con = new SqlConnection(ConnStr))
{
if (con.State == ConnectionState.Closed)
con.Open();
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = strProcedure;
cmd.Connection = con;
foreach (var item in param)
{
cmd.Parameters.Add(item);
}
cmd.ExecuteNonQuery();
result = int.Parse(cmd.Parameters["ID"].Value.ToString());
}
con.Close();
return result;
}
}
public SqlParameterCollection ExecuteNonQueryReturns(string strProcedure, SqlParameter[] param)
{
SqlParameterCollection spc = null;
using (SqlConnection con = new SqlConnection(ConnStr))
{
if (con.State == ConnectionState.Closed)
con.Open();
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = strProcedure;
cmd.Connection = con;
foreach (var item in param)
{
cmd.Parameters.Add(item);
}
cmd.ExecuteNonQuery();
spc = cmd.Parameters;
}
con.Close();
return spc;
}
}
}

Code kopieren

Zunächst einmal ist die Methode DBHelp angehängt
Das gespeicherte Verfahren ist wie folgt:

ALTER PROCEDURE [dbo].[pAddsp_UserInfoByEmail]
@ID VARCHAR(50),
@PASSLOGIN VARCHAR(32),
@GUID UNIQUEIDENTIFIER OUTPUT
AS
BEGIN
SELECT @GUID=NEWID()
INSERT INTO sp_UserInfo([GUID],[PasswordLogin],[Email],[IsEmailValidate],[CreateDate],[Statas]) VALUES (@GUID,@PASSLOGIN,@ID,1,GETDATE(),1)
END
GO

Code kopieren

Code ausgeführt:

public class pAddsp_UserInfoByEmail:DBHelper
{
private string Email;
private string PassLogin;
public pAddsp_UserInfoByEmail(string mail, string passlogin)
{
this.Email = mail;
this.PassLogin = passlogin;
}
public string ExecutionProcedure()
{
try
{
var param = new SqlParameter[3];
param[0] = new SqlParameter("ID", this.Email);
param[1] = new SqlParameter("PASSLOGIN", this.PassLogin);
param[2] = new SqlParameter("GUID", SqlDbType.UniqueIdentifier);
param[2].Direction = ParameterDirection.Output;
var sp=base.ExecuteNonQueryReturns("pAddsp_UserInfoByEmail", param);
return sp["GUID"].Value.ToString();
}
catch (Exception ex)
{
return null;
}
}

Code kopieren

[Quelle] [Pro-test] C# führt gespeicherte Prozeduren aus und gibt den GUID-Primärschlüssel zurück

Verwandte Beiträge

Betrachtete Abschnitte