using System;
创新互联建站长期为1000多家客户提供的网站建设服务，团队从业经验10年，关注不同地域、不同群体，并针对不同对象提供差异化的产品和服务；打造开放共赢平台，与合作伙伴共同营造健康的互联网生态环境。为嫩江企业提供专业的网站制作、做网站，嫩江网站改版等技术服务。拥有十载丰富建站经验和众多成功案例,为您定制开发。
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using System.Collections;
namespace DataBase.Data.SqlClient
{
public sealed class SqlHelper
{
#region private utility methods constructors
private SqlHelper() { }
private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters)
{
foreach (SqlParameter p in commandParameters)
{
if ((p.Direction == ParameterDirection.InputOutput) (p.Value == null))
{
p.Value = DBNull.Value;
}
command.Parameters.Add(p);
}
}
private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues)
{
if ((commandParameters == null) || (parameterValues == null))
{
return;
}
if (commandParameters.Length != parameterValues.Length)
{
throw new ArgumentException("Parameter count does not match Parameter Value count.");
}
for (int i = 0, j = commandParameters.Length; i j; i++)
{
commandParameters[i].Value = parameterValues[i];
}
}
private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters)
{
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
command.Connection = connection;
command.CommandText = commandText;
command.CommandTimeout = 20;
if (transaction != null)
{
command.Transaction = transaction;
}
command.CommandType = commandType;
if (commandParameters != null)
{
AttachParameters(command, commandParameters);
}
return;
}
#endregion private utility methods constructors
#region ExecuteNonQuery
public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText)
{
return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null);
}
public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
cn.Open();
return ExecuteNonQuery(cn, commandType, commandText, commandParameters);
}
}
public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues)
{
if ((parameterValues != null) (parameterValues.Length 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName);
}
}
public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText)
{
return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null);
}
public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
int retval = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return retval;
}
public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues)
{
if ((parameterValues != null) (parameterValues.Length 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName);
}
}
public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText)
{
return ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter[])null);
}
public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
int retval = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return retval;
}
public static int ExecuteNonQuery(SqlTransaction transaction, string spName, params object[] parameterValues)
{
if ((parameterValues != null) (parameterValues.Length 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteNonQuery(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion ExecuteNonQuery
#region ExecuteDataSet
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
{
return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null);
}
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
cn.Open();
return ExecuteDataset(cn, commandType, commandText, commandParameters);
}
}
public static DataSet ExecuteDataset(string connectionString, string spName, params object[] parameterValues)
{
if ((parameterValues != null) (parameterValues.Length 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteDataset(connectionString, CommandType.StoredProcedure, spName);
}
}
public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText)
{
return ExecuteDataset(connection, commandType, commandText, (SqlParameter[])null);
}
public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
public static DataSet ExecuteDataset(SqlConnection connection, string spName, params object[] parameterValues)
{
if ((parameterValues != null) (parameterValues.Length 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteDataset(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteDataset(connection, CommandType.StoredProcedure, spName);
}
}
public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText)
{
return ExecuteDataset(transaction, commandType, commandText, (SqlParameter[])null);
}
public static DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
cmd.Parameters.Clear();
return ds;
}
public static DataSet ExecuteDataset(SqlTransaction transaction, string spName, params object[] parameterValues)
{
if ((parameterValues != null) (parameterValues.Length 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteDataset(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteDataset(transaction, CommandType.StoredProcedure, spName);
}
}
#endregion ExecuteDataSet
#region ExecuteDatatable
public static DataTable ExecuteDatatable(string connectionString, CommandType commandType, string commandText)
{
return ExecuteDatatable(connectionString, commandType, commandText, (SqlParameter[])null);
}
public static DataTable ExecuteDatatable(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
cn.Open();
return ExecuteDatatable(cn, commandType, commandText, commandParameters);
}
}
public static DataTable ExecuteDatatable(string connectionString, string spName, params object[] parameterValues)
{
if ((parameterValues != null) (parameterValues.Length 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteDatatable(connectionString, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteDatatable(connectionString, CommandType.StoredProcedure, spName);
}
}
public static DataTable ExecuteDatatable(SqlConnection connection, CommandType commandType, string commandText)
{
return ExecuteDatatable(connection, commandType, commandText, (SqlParameter[])null);
}
public static DataTable ExecuteDatatable(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
cmd.Parameters.Clear();
return dt;
}
public static DataTable ExecuteDatatable(SqlConnection connection, string spName, params object[] parameterValues)
{
if ((parameterValues != null) (parameterValues.Length 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection.ConnectionString, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteDatatable(connection, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteDatatable(connection, CommandType.StoredProcedure, spName);
}
}
public static DataTable ExecuteDatatable(SqlTransaction transaction, CommandType commandType, string commandText)
{
return ExecuteDatatable(transaction, commandType, commandText, (SqlParameter[])null);
}
public static DataTable ExecuteDatatable(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
cmd.Parameters.Clear();
return dt;
}
public static DataTable ExecuteDatatable(SqlTransaction transaction, string spName, params object[] parameterValues)
{
if ((parameterValues != null) (parameterValues.Length 0))
{
SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(transaction.Connection.ConnectionString, spName);
AssignParameterValues(commandParameters, parameterValues);
return ExecuteDatatable(transaction, CommandType.StoredProcedure, spName, commandParameters);
}
else
{
return ExecuteDatatable(transaction, CommandType.StoredProcedure, spName);
}
}
事务分显示事务和隐式事务,
隐式事务 在SQLServer 语句执行完成就自动结束；
显示事务语法：
BEGIN TRAN/TRANSACTION
--这里写事务内容
callback TRAN/TRANSACTION --回滚事务
commit TRAN/TRANSACTION --提交事务
callback 和commit 都是结束事务的语句，不通之处在于，callback是回滚使用，即事务中所有执行语句都无效化(在事务中某处有错误时使用)； commit 是提交事务，即完成所有事务内容。
你好， 非常高兴为你解答
现在你建了两个表主键都是xuehao， 也就是说学好是1001的在这两个表里都只能存在一条
这就是一对一的关系， 不用再有什么约束，不知道你还想要什么结果， 如果有能举个例子吗
