using System; using System.Data; using System.Data.SqlClient;namespace ConsoleApplication1 { class Program { static void Main(string[] args) { using (SqlConnection conn = new SqlConnection("server=.;database=sql2005;user id=sa;password=sqlsa;min pool size=4;max pool size=100;Connection Lifetime=30;Enlist=true;")) { conn.Open(); using (SqlCommand cmd = new SqlCommand("SELECT Name FROM Student WHERE Id=@Id And Age=@Age", conn)) { cmd.Parameters.Add("@Id", SqlDbType.Int); cmd.Parameters[0].Value = "14"; cmd.Parameters.Add("@Age", SqlDbType.Int); cmd.Parameters[1].Value = "21"; Console.WriteLine(cmd.ExecuteScalar()); //输出完整SQL,SELECT Name FROM Student WHERE Id=14 And Age=21 Console.WriteLine(GetCommandText(cmd.CommandText, cmd.Parameters)); } conn.Close(); }
/// </summary>
/// <param name="message">执行【DbCommand】的函数名。</param>
/// <param name="command">命令对象。</param>
/// <param name="ret">执行【DbCommand】的返回值。可以为【null】。</param>
[System.Diagnostics.Conditional("DEBUG")]
public static void WriteToDebug(string message, DbCommand command, object ret)
{
string commS = command.CommandText; foreach (DbParameter item in command.Parameters)
{
commS = System.Text.RegularExpressions.Regex.Replace(commS, item.ParameterName, "'" + (item.Value ?? "null").ToString() + "'", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
}
WriteToDebug("【页面】:" + (System.Web.HttpContext.Current == null ? "非Web应用程序" : System.Web.HttpContext.Current.Request.RawUrl) +
"\r\n【执行】:" + message +
"\r\n【语句】:" + commS +
"\r\n【返回】:" + (ret == null ? "可能执行失败" : ret));
}
配置sql profiler另外还可看联机丛书等
否则在SQL Server的服务器端编程了。
using System.Data;
using System.Data.SqlClient;namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
using (SqlConnection conn = new SqlConnection("server=.;database=sql2005;user id=sa;password=sqlsa;min pool size=4;max pool size=100;Connection Lifetime=30;Enlist=true;"))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("SELECT Name FROM Student WHERE Id=@Id And Age=@Age", conn))
{
cmd.Parameters.Add("@Id", SqlDbType.Int);
cmd.Parameters[0].Value = "14";
cmd.Parameters.Add("@Age", SqlDbType.Int);
cmd.Parameters[1].Value = "21"; Console.WriteLine(cmd.ExecuteScalar()); //输出完整SQL,SELECT Name FROM Student WHERE Id=14 And Age=21
Console.WriteLine(GetCommandText(cmd.CommandText, cmd.Parameters));
}
conn.Close();
}
Console.Read(); } /// <summary>
/// 从参数化查询获取完整SQL
/// </summary>
/// <param name="commandtext"></param>
/// <param name="parameters"></param>
/// <returns></returns>
static string GetCommandText(string commandtext, SqlParameterCollection parameters)
{
foreach (SqlParameter parameter in parameters)
commandtext = commandtext.Replace(parameter.ParameterName, parameter.Value is String ? parameter.Value.ToString() : parameter.ParameterName); return commandtext;
} }
}