Saqlangan protseduralarga ega bir nechta SQL serverlari mavjud, masalan, Microsoft SQL Server yoki PostgreSQL. Shuningdek, saqlangan protseduralar chaqiruvlarini amalga oshiradigan bir nechta mijoz tomoni ob'ektlari mavjud (Delphida TADOStoredProc
, .NET Frameworkda SqlCommand
va boshqalar).
Men har doim so'ramoqchi bo'lgan savol:
Saqlangan protseduralar har doim o'z parametrlarining ikkilik ko'rinishi bilan maxsus samarali tarzda bajariladi yoki saqlangan protseduralar parametrlarini ifodalovchi o'ta ilg'or ob'ektlar har doim oddiy matn qatoriga aylantiriladi va saqlangan protsedura doimo ushbu oddiy matn qatorini SQL serveriga yuborish orqali bajariladi. ? (Masalan, bitta texnologiyani olaylik - u SQL Server va ADO.NET bo'lsin).
ADO.NET protsedurasi uchun parametr nomlari hech qanday ma'noga ega emasligini payqadim - faqat ularni yaratish tartibi muhim, bu meni oddiy matn qatori bilan g'oya haqida o'ylashga majbur qiladi.
@Alex K. uchun yangilanish
Men .NET da quyidagi kodni sinab ko'rdim:
CREATE PROCEDURE paramtest
@par1 nvarchar(50),
@par2 nvarchar(50),
@par3 nvarchar(50)
AS
SELECT Res = '@par1 = ' + @par1 + '; @par2 = ' + @par2 + '; @par3 = ' + @par3
RETURN 555
using System;
using System.Data.SqlClient;
using System.Data;
namespace SqlParamTest
{
class Program
{
private static void addParam(SqlCommand cmd, string parameterName, ParameterDirection direction, SqlDbType dbType, int size, object value)
{
SqlParameter par = new SqlParameter(parameterName, dbType, size);
par.Direction = direction;
par.Value = value;
cmd.Parameters.Add(par);
}
static void Main(string[] args)
{
using (SqlConnection conn = new SqlConnection(@"Data Source=localhost\sqlexpress;Initial Catalog=test;Integrated Security=True"))
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "paramtest";
addParam(cmd, "@par3", ParameterDirection.Input, SqlDbType.NVarChar, 50, "third");
addParam(cmd, "@par2", ParameterDirection.Input, SqlDbType.NVarChar, 50, "second");
addParam(cmd, "@par1", ParameterDirection.Input, SqlDbType.NVarChar, 50, "first");
addParam(cmd, "@Return", ParameterDirection.ReturnValue, SqlDbType.Int, 0, null);
conn.Open();
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
if (rdr.Read()) Console.WriteLine((string)rdr["Res"]);
rdr.Close();
Console.WriteLine("Return value: {0}", cmd.Parameters["@Return"].Value);
}
Console.ReadKey();
}
}
}
va ha, u parametrlarni to'g'ri saqlaydi, lekin menimcha, parametrlarga qo'shimcha tekshiruvlar qo'shadigan .NET, chunki Delphida quyidagi kod:
procedure TMyClass.Test(Conn: TADOConnection);
var SP:TADOStoredProc;
begin
SP := TADOStoredProc.Create(nil);
try
SP.Connection := Conn;
SP.ProcedureName := 'paramtest';
SP.Parameters.CreateParameter('@whatthehell', ftString, pdInput, 50, 'one');
SP.Parameters.CreateParameter('@AnotherCrap', ftString, pdInput, 50, 'two');
SP.Parameters.CreateParameter('?', ftString, pdInput, 50, 'three');
SP.ExecProc;
finally
SP.Free;
end;
end;
qaytaradi:
@par1 = one; @par2 = two; @par3 = three
va etishmayotgan parametrlar haqida shikoyat qilmaydi.
pdReturnValue faqat ushbu parametr boshqa parametrlardan oldin yaratilgan bo'lsa ishlaydi.