Kết nối với SQL Server với Query

* Bài viết cùng nhóm:

1. Cấu trúc
2. Giải thích tham số ,
     cách sử dụng SqlParameter
3. Cách goi 1 PROCEDURE
4. Cách dùng SQL Insert, Update, Delete
      Insert , Update , Delete
      Update có sử dụng JSON
      Khai báo biến trong SQL, dùng nhiều lệnh,có update, có @@ROWCOUNT có trả về kết quả
      Xử lý biến DbNull trong SQL
      Xử lý biến ngày khi gởi đến SQL Server
      Xử lý Config khi select trả về nhiều table, để đổi tên table

----------------------

1. Cấu trúc

* Tạo 1 Query
<Query>
<ID>Q1</ID>
<Src>TênKếtNối</Src>
<Type>Sql</Type>
<Cmd>Câu lệnh SQL</Cmd>
<SqlParameter>
Ten<pr&/>GiaTri<pr&/>Kieu
Ten<pr&/>GiaTri<pr&/>Kieu
</SqlParameter>
<SqlConfig>
CommandType:GiaTri
Call:GiaTri
ConvertSave:GiaTri
</SqlConfig>
</Query>

Cú pháp đầy đủ:
<SqlParameter>
Ten<pr&/>GiaTri<pr&/>Kieu<pr&/>add<pr&/>setNullIf
Ten<pr&/>GiaTri<pr&/>Kieu<pr&/>add<pr&/>setNullIf
</SqlParameter>

2. Giải thích tham số

* Src:
    Nhập là số thì kết nối giá trị đến sysConfig
    Ví dụ: <Src>12</Src> thì kết nối giá trị connect string ở key 12
    Nhập là chữ thì kết nối giá trị tại Web.config hệ thống
    <Src>cnSQL_HDND</Src>
Thì tại web.config
<appSettings>
    <add key="cnSQL_HDND" value="server=xxx;database=xx;uid=xx;pwd=x;" />
</appSettings>

* Cmd: câu lệnh truy vấn
Select * From tbHangHoa Where TenHangHoa='a'
Cách viết này sẽ bảo mật kém
** Nếu bảo mật phải dùng tham số SqlParameter
** Cách sử dụng tham số SqlParameter
<SqlParameter>
TênThamSố<pr&/>GiáTrị<pr&/>Kiểu
</SqlParameter>
Kiểu: như quy định trong SQL, để trống kiểu mặc định là nvarchar
các kiểu khác như int,real, bit, float, datetime ..

<SqlParameter>
TenHangHoa<pr&/>A
TonKho<pr&/>1<pr&/>int
</SqlParameter>

Ví dụ: Query có lấy tham số url
<Query>
<ID>HDND_CT</ID>
<Src>cnSQL_HDND</Src>
<Type>Sql</Type>
<Cmd>
SELECT   * From tbHD
Where HopDong_Key=@ma
</Cmd>
<SqlParameter>
ma<pr&/><iif-D>Url:id</iif-D>
</SqlParameter>
</Query>

* Click xem cách dùng lệnh giá tr thay thế tham số Parama
Ví dụ:
<SqlParameter>
_SYS_KEY<pr&/>{#NewGuid}
UserName<pr&/>{#SysUser-UserName}
HoTen<pr&/>{#SysUser-Name}
Ngay<pr&/>{#SysUser-Name}
</SqlParameter>

* Có thể gọi 1 Query con
<SqlParameter>
TenHangHoa<pr&/><getQuery>LaySo/->/HD23/--/0000</getQuery>
</SqlParameter>
Bên Query con chú ý
Tại mục
<SqlParameter>
KyHieu<pr&/>[input>1]
DinhDang<pr&/>[input>2]
</SqlParameter>

* Như vậy khi gọi mới truyền HD23 vào input_1 và 0000 và input_2

* Theo cú pháp đầy đủ

Ten<pr&/>GiaTri<pr&/>Kieu<pr&/>add<pr&/>setNullIf

add: thêm lệnh
len:30;cv:urldecode / htmldecode

HttpUtility.UrlDecode(value);
HttpUtility.HtmlDecode(value);

setNullIf: nếu giá trị bằng thì set DbNull
Ví dụ: ==undefine
       Nếu bằng chính xác undefined thì set DbNull
           %%undefine
       Nếu có chứa từ thì set DbNull

Ví dụ:

* Sau khi test query thấy giá trị bị null trả về là undefined-undefined- thì ta thêm == để bằng tuyệt đối
<Query>
<ID>Q1</ID>
<Src>cnSQL_HDND</Src>
<Type>Sql</Type>
<Cmd>
UPDATE tbHopDong
SET
SoBan_Chay=@SoBan_Chay,
NgayThueMatBang=@NgayThueMatBang
WHERE
_SYS_KEY=@_SYS_KEY
</Cmd>
<SqlParameter>
_SYS_KEY<pr&/><iif-DGVL>Url:key</iif-DGVL>
NgayThueMatBang<pr&/><iif-DGVL>Form:NgayThueMatBang</iif-DGVL><pr&/>DateTime<pr&/><pr&/>==undefined-undefined-
</SqlParameter>
<SqlConfig>
Call:ExecuteNonQuery
</SqlConfig>
</Query>

* giá trị cột NgayThueMatBang sẽ set bằng DbNull nếu trong CSDL cho phép gán null
* Copy đoạn sau vào sau DateTime
<pr&/><pr&/>==undefined-undefined-
<pr&/><pr&/>%%undefined-undefined-

**************

<SqlConfig>
CommandType:Text/TableDirect/StoredProcedure
Call: Fill/ExecuteScalar/ExecuteNonQuery
ConvertSave:urldecode/htmldecode
</SqlConfig>

   - >Tham khảo thêm ConvertSave

CommandType: để trống, trong TH muốn thực thi 1 Procedure có liên quan Update, Insert, Delete thì chọn StoredProcedure
Call: để trống mặc định là Fill (trả về 1 DataSet gồm nhiều table)
        ExecuteNonQuery: TH thực hiện Update, Insert, Delete
                  Trả về tổng số bảng ghi thực thi được
                  Trả về -1 nếu lỗi
        ExecuteScalar khi gọi trả về 1 giá trị từ SQL
                  Trả về giá trị get

Ví dụ 1: Select
<Query>
<ID>Q1</ID>
<Src>1</Src>
<Type>Sql</Type>
<Cmd>Select * From tbHangHoa Where TenHangHoa=@TenHH</Cmd>
<SqlParameter>
TenHH<pr&/><iif-DGVL>Url:name</iif-DGVL>
</SqlParameter>
</Query>

Giải thích:   <Src>12</Src> , 12 là ID trong sysConfig

Ví dụ 2:
<Query>
<ID>Q1</ID>
<Src>1</Src>
<Type>Sql</Type>
<Cmd>Select * From tbHangHoa</Cmd>
</Query>
<Query>
<ID>Q2</ID>
<Src>1</Src>
<Type>Sql</Type>
<Cmd>Select * From tbBangGia</Cmd>
</Query>
* Kết quả trả về 1 DataSet gồm 2 Table có tên Q1 và Q2

** TH câu SQL không sort được, thêm <Sort>Cot Asc</Sort>

Ví dụ 2: Select nhiều bảng cách nhau dấu ; trong câu select
<Query>
<ID>Q1</ID>
<Src>1</Src>
<Type>Sql</Type>
<Cmd>Select * From tbHangHoa;Select * From tbBangGia;Select * From tbKhachHang</Cmd>
</Query>

Giải thích: thì trả về 1 Query có 3 DataTable tên Q1 và Q1_2 và Q1_3 (Q1_1 sẽ bị bỏ đi _1)

* Ta có thể chia làm 3 Query, kết quả vẫn trả về 3 DataTable của DataSet nhưng trong TH cần thiết ta gộp lại với nhau bằng dấu ; tốc độ sẽ tối ưu hơn vì không mở và đóng kết nối SQL liên tục.

3. Cách goi 1 PROCEDURE
* Gọi 1 PROCEDURE không có tham siis
<Cmd>Tên_PROCEDURE </Cmd>
Ví dụ:
<Query>
<ID>Q1</ID>
<Src>12</Src>
<Type>Sql</Type>
<Cmd>[AUC_st_Get_BangGia]</Cmd>
</Query>

Chú ý: Có tham số phải thêm Exec

<Query>
<ID>Q1</ID>
<Src>12</Src>
<Type>Sql</Type>
<Cmd>Exec [AUC_st_Get_BangGia] @Ten,@Tuoi</Cmd>
<SqlParameter>
Ten<pr&/>A
Tuoi<pr&/>30<pr&/>int
</SqlParameter>
<SqlConfig>
Call:ExecuteNonQuery
</SqlConfig>
</Query>

Call: Fill/ExecuteScalar/ExecuteNonQuery

** Kết hợp nhiều lệnh SQL

<Query>
<ID>Q2</ID>
<Src>cnSQL_HDND</Src>
<Type>Sql</Type>
<Cmd>
UPDATE tbHopDong_NoiDung_CT
SET
PS1=@PS1
WHERE
_SYS_KEY=@_SYS_KEY
Exec [Tinh_SuDung_BBBGDVT] @_SYS_KEY
</Cmd>
<SqlParameter>
_SYS_KEY<pr&/><iif-DGVL>Url:key</iif-DGVL>
PS1<pr&/><iif-DGVL>Form:PS1</iif-DGVL><pr&/>float
</SqlParameter>
</Query>

4. Cách dùng SQL Insert, Update, Delete

Ví dụ: Insert, chèn 1 bản ghi vào bảng

<Query>
<ID>Q1</ID>
<Src>12</Src>
<Type>Sql</Type>
<Cmd>INSERT INTO a_test(Ten, Tuoi,HSL) VALUES (@Ten,@Tuoi,@HSL); </Cmd>
<SqlParameter>
Ten<pr&/>A
Tuoi<pr&/>30<pr&/>int
HSL<pr&/>1.1<pr&/>float
</SqlParameter>
<SqlConfig>
Call:ExecuteNonQuery
</SqlConfig>
</Query>

** Chèn bảng ghi sử dụng dữ liệu json


<Query>

<ID>chungtu_chitiet</ID>

<Src>cnSQL_BidaKaraokeNHCF</Src>

<Type>Sql</Type>

<Cmd>

Declare @_key_sys nvarchar(50)

Set  @_key_sys=LOWER( NEWID() )



Declare @NumOrder int

Set @NumOrder = (Select IsNull( Max(NumOrder),0) From dbo.tbChungTu_ChiTiet Where _SYS_KEY_ChungTu=@_SYS_KEY_ChungTu) +1



Insert tbChungTu_ChiTiet(_SYS_KEY,_SYS_KEY_ChungTu,NumOrder,NgayThem,MaNhomSanPham,TenSanPham,DVT,SoLuong,DonGia,ThanhTien)

Values(@key,@_SYS_KEY_ChungTu,@NumOrder,GetDate(),@MaNhomSanPham,@TenSanPham,@DVT,@SoLuong,@DonGia,@SoLuong*@DonGia)

</Cmd>

<SqlParameter>

key<pr&/>{#NewGuid}

_SYS_KEY_ChungTu<pr&/><iif-D>Url:key_chungtu</iif-D>

SoLuong<pr&/>ipsl->SL_Them<pr&/>Float

MaNhomSanPham<pr&/>ipsl->MaNhom

TenSanPham<pr&/>ipsl->TenSanPham

DVT<pr&/>ipsl->DVT

DonGia<pr&/>ipsl->DonGia_01<pr&/>Float

</SqlParameter>

<SqlConfig>

Call:ExecuteNonQuery

</SqlConfig>

</Query>

ipsl-> là lấy json dạng mảng truyền qua từ html

Html


  $scope.ThemSanPham_OK = function (ev) {

           var js_chon = $scope.sanpham_loc.filter

            (

                obj =>  obj.SL_Them>0

            ); 

          

          //alert( JSON.stringify( result )  );

          

          //js_chon

         key_chungtu=  urlGetValuePara("key_chungtu")

          $scope.loading=true;

        loadding_show("Đang tải..");

        var url=  "/adminxml/Query_Do.aspx?name=BanHang_iif_sv/ChungTu_ChiTiet_Add&out=json&key_chungtu="+key_chungtu;

        //alert(url);

        //alert( JSON.stringify( $scope.sanpham[0] )  );

        //alert( JSON.stringify( $scope.sanpham )  );

        

        $http({

            method : "POST",

            data: js_chon ,

            url : url

        }).then(function mySuccess(response)

            {

               alert( JSON.stringify( response.data )  );

               $scope.loadData_ChungTu();

               loadding_hide();

               

               //

                for (x in $scope.sanpham_loc) {

                      $scope.sanpham_loc[x].SL_Them=0;

                          

               }

               //

               

               $(".close").trigger("click");

              

             

            }, function myError(response)

            {

                   alert( "LỖI:"+ response.statusText );

            }

        );

          

          

     };  //ThemSanPham_OK

Ví dụ: update, cập nhật 1 bản ghi vào bảng

<Query>
<ID>Q1</ID>
<Src>12</Src>
<Type>Sql</Type>
<Cmd>Update a_test Set Ten=@Ten,Tuoi=@Tuoi,HSL=@HSL Where Ma=@Ma; </Cmd>
<SqlParameter>
Ma<pr&/>001
Ten<pr&/>A
Tuoi<pr&/>30<pr&/>int
HSL<pr&/>1.1<pr&/>float
</SqlParameter>
<SqlConfig>
Call:ExecuteNonQuery
</SqlConfig>
</Query>

Ví dụ:

Tạo Query:
<Query>
<ID>Q1</ID>
<Src>12</Src>
<Type>Sql</Type>
<Cmd>Update tbBanHang_SV Set MaKhachHang=@MaKhachHang,TenKhachHang=@TenKhachHang,GhiChu=@GhiChu Where _SYS_KEY=@_SYS_KEY; </Cmd>
<SqlParameter>
_SYS_KEY<pr&/><iif-D>Url:idDH</iif-D>
MaKhachHang<pr&/><iif-D>Form:MaKhachHang</iif-D>
TenKhachHang<pr&/><iif-D>Form:TenKhachHang</iif-D>
GhiChu<pr&/><iif-D>Form:GhiChu</iif-D>
</SqlParameter>
<SqlConfig>
Call:ExecuteNonQuery
</SqlConfig>
</Query>

$("#saveDonHang").click(function(){

        var url="/adminxml/Query_Do.aspx?name=BanHang_iif/DonHang_Update_Frm&out=txt&idDH=<iif-D>Url:idDH</iif-D>";
        //alert(url);
            $.post( url,
            $("#DonHangChiTiet").serialize()
        )
        .done(function( data ) {
            //alert( "Data Loaded: " + data );
            if(data=="1")
            {
                window.parent.alert_pIIF('thông báo','Lưu thành công !');
                window.parent.IIF_f_Close_Popup();
            }

       });
});
</script>

Ví dụ: delete, xóa 1 bản ghi từ bảng

<Query>
<ID>Q1</ID>
<Src>12</Src>
<Type>Sql</Type>
<Cmd>Delete From a_test Where Ma=@Ma ); </Cmd>
<SqlParameter>
Ma<pr&/>001
</SqlParameter>
<SqlConfig>
Call:ExecuteNonQuery
</SqlConfig>
</Query>

Gọi từ html:

$("#Button1").click(function(){
url="/adminxml/Query_Do.aspx?name=NAME&out=json&_SYS_KEY=vl";
          $.get(url, function(data, status){
           if(data.Q1[0].CountDo >0 )
                   $scope.loadData();      // sửa lại hàm loaddata
          });
});

** Có sử dụng JSON

<Query>
<ID>chungtu_chitiet</ID>
<Src>cnSQL_BidaKaraokeNHCF</Src>
<Type>Sql</Type>
<Cmd>
Update tbChungTu_ChiTiet
Set
SoLuong=@SoLuong
,DonGia=@DonGia
Where _SYS_KEY=@key
</Cmd>
<SqlParameter>
key<pr&/>ips->_SYS_KEY
SoLuong<pr&/>ips->SoLuong<pr&/>Float
DonGia<pr&/>ips->DonGia<pr&/>Float
</SqlParameter>
<SqlConfig>
Call:ExecuteNonQuery
</SqlConfig>
</Query>

Trong

Bên HTML SqlParameter sử dụng ips->_SYS_KEY là tên cột trong json

Json truyền:

{"_SYS_KEY":"2","_SYS_KEY_ChungTu":"1","NumOrder":2,"NgayThem":null,"MaNhomSanPham":"HangHoa","TenSanPham":"Mỳ tôm","DVT":"Cái","SoLuong":333,"DonGia":30000,"ThanhTien":90000}


$scope.loading=true;

        loadding_show("Đang tải..");

        var url=  "/adminxml/Query_Do.aspx?name=BanHang_iif_sv/ChungTu_ChiTiet_Save&out=json";

        //alert(url);

        alert( JSON.stringify( $scope.objSANPHAM )  );

        $http({

            method : "POST",

            data:  $scope.objSANPHAM,

            url : url

        }).then(function mySuccess(response)

            {

               alert( JSON.stringify( response.data.chungtu_chitiet )  );

               loadding_hide();

             

            }, function myError(response)

            {

                   alert( "LỖI:"+ response.statusText );

            }

        );

** Query xóa và trả về bảng
<Query>
<ID>Q1</ID>
<Src>12</Src>
<Type>Sql</Type>
<Cmd>
Delete FROM tbDongBo_Client
SELECT @@ROWCOUNT AS N'Xóa thành công';
</Cmd>
</Query>

Khi chạy query
/adminxml/xml_getdata_q.aspx?file=BanHang_iif/tbDongBo_Client_DEL

Ví dụ về lấy giá trị 1 cột 1 dòng
<Query>
<ID>Q1</ID>
<Src>12</Src>
<Type>Sql</Type>
<Cmd>select count(*) from tbhanghoa Where Loai=@Loai</Cmd>
<SqlParameter>
Loai<pr&/>A
</SqlParameter>
<SqlConfig>
Call:ExecuteScalar
</SqlConfig>
</Query>

** Khai báo biến trong SQL, dùng nhiều lệnh có trả về kết quả
Ví du 1: tạo lên SQL cập nhật xong trả kết quả về

<Query>
<ID>Q_TinhLaiTongTien</ID>
<Src>12</Src>
<Type>Sql</Type>
<Cmd>
   Declare @key nvarchar(50)
   Declare @Tong_ThanhTien float

   set @key='c033222f-f45d-4e85-83e7-9a78dfa447ca'
   Set @Tong_ThanhTien= (Select IsNull(SUM(ThanhTien),0) From dbo.tbBanHang_SV_CT Where _SYS_KEY_P=@key)
   Update tbBanHang_SV Set TongTienHang=@Tong_ThanhTien Where _SYS_KEY=@key

   -- Nếu có gọi Produce sử dụng tiền tố EXEC TenPro..
   SELECT @Tong_ThanhTien
</Cmd>
<SqlConfig>
Call:Fill
</SqlConfig>
</Query>

Ví dụ 2:

<Query>
<ID>Q1</ID>
<Src>12</Src>
<Type>Sql</Type>
<Cmd>
Declare @id int
Declare @sl int
Declare @HinhAnh nvarchar(150)
set @HinhAnh ='[*input_2]'
set @id=[*input_1]
set @sl = ( Select Count(*) From tbHangHoa_SV Where ID_HangHoa=@id )
if @sl=0
   INSERT INTO tbHangHoa_SV(ID_HangHoa,HinhAnh) VALUES (@id,@HinhAnh);
else
   Update tbHangHoa_SV Set HinhAnh=@HinhAnh Where ID_HangHoa=@id
</Cmd>
<SqlConfig>
Call:Fill
</SqlConfig>
</Query>

Ví dụ 3: số mới tăng lên 1

<Query>
<ID>Q1</ID>
<SetVar>
name:key
value:{#NewGuid}
</SetVar>
</Query>

<Query>
<ID>Q2</ID>
<Src>12</Src>
<Type>Sql</Type>
<Cmd>
Declare @SoMoi nvarchar(10)
set @SoMoi = ( Select [dbo].[Format_Number_Zero]( ISNULL( Max(So),0)+1,'0000') From tbHopDong )
INSERT INTO tbHopDong(_SYS_KEY,So) VALUES (@_SYS_KEY,@SoMoi); </Cmd>
<SqlParameter>
_SYS_KEY<pr&/>{g:has/->key:g}
</SqlParameter>
<SqlConfig>
Call:ExecuteNonQuery
</SqlConfig>
</Query>

Ví dụ 4: Trả về kết quả tổng số bảng ghi xóa, update thành công

<Query>
<ID>Q2</ID>
<Src>12</Src>
<Type>Sql</Type>
<Cmd>
delete
from mytable
where datefield = '5-Oct-2008'
select @@ROWCOUNT
</Cmd>
</Query>

Xử lý Config khi select trả về nhiều table, để đổi tên table

Ví dụ: ví dụ bên dưới sẽ trả về 2 table có tên sanpham và sanpham_2 (nếu ko có config)
Ta có thể đổi lại tên table để sử dụng ở json trả về bằng cách
TableRename:TEN_1,TEN_2
* TEN_1 / TEN_2 nếu trống sẽ lấy tên trong hệ thống
Nếu chỉ đổi tên 2 thì để
TableRename:,TEN_2

TableRename:sanpham,nhom

<Query>
<ID>sanpham</ID>
<Src>cnSQL_BidaKaraokeNHCF</Src>
<Type>Sql</Type>
<Cmd>
Select *,0 as SL_Them From dbo.tbSanPham
Select Distinct(Nhom) From dbo.tbSanPham
</Cmd>
<Config>
TableRename:sanpham,nhom
</Config>
</Query>

Xử lý biến ngày khi gởi đến Query

khi gởi kiểu ngày đến server thì ta gởi 1 chuổi có dạng nam/thang/ngay gio:phut

Nếu là biến Date Obj javascript thì dùng hàm

convertDateObjToStr_saveDB()

Nếu là biến Date Json thì dùng hàm

xxxxxxxxxxx

Mục liên quan:

0935999617