Владимир СА, кода много, если не утомит, то базовый клас адаптера (похож на стандартный типизированый от студии) таков:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
[System.ComponentModel.ToolboxItem(false)]
public abstract class IPY_TableAdapter : System.ComponentModel.Component
{
protected System.Data.SqlClient.SqlDataAdapter _adapter;
protected System.Data.SqlClient.SqlConnection _connection;
protected System.Data.SqlClient.SqlCommand[] _commandCollection;
protected bool _clearBeforeFill;
public IPY_TableAdapter()
{
this.ClearBeforeFill = true;
}
public IPY_TableAdapter(System.Data.SqlClient.SqlConnection Cn) : this()
{
this.Connection = Cn;
}
// цей метод необхідно перектрити в нащадках !!!
// переписувати з InitAdapter() згенерованого в DataSet
protected abstract void InitAdapter();
// цей метод потрібно перектрити в нащадках, якщо в запиті будуть обраховані поля
// переписувати з InitCommandCollection() згенерованого в DataSet
protected virtual void InitCommandCollection()
{
this._commandCollection = new System.Data.SqlClient.SqlCommand[1];
this._commandCollection[0] = new System.Data.SqlClient.SqlCommand();
this._commandCollection[0].Connection = this.Connection;
this._commandCollection[0].CommandText = "SELECT * FROM dbo." + this.Adapter.TableMappings[0].DataSetTable;
this._commandCollection[0].CommandType = System.Data.CommandType.Text;
}
public System.Data.SqlClient.SqlDataAdapter Adapter {
get {
if ((this._adapter == null)) this.InitAdapter();
return this._adapter;
}
}
protected System.Data.SqlClient.SqlConnection Connection {
get {
return this._connection;
}
set {
this._connection = value;
if ((this.Adapter.InsertCommand != null))
this.Adapter.InsertCommand.Connection = value;
if ((this.Adapter.DeleteCommand != null))
this.Adapter.DeleteCommand.Connection = value;
if ((this.Adapter.UpdateCommand != null))
this.Adapter.UpdateCommand.Connection = value;
for (int i = 0; (i < this.CommandCollection.Length); i = (i + 1))
{
if ((this.CommandCollection[i] != null))
((System.Data.SqlClient.SqlCommand)(this.CommandCollection[i])).Connection = value;
}
}
}
protected System.Data.SqlClient.SqlCommand[] CommandCollection {
get {
if ((this._commandCollection == null))
this.InitCommandCollection();
return this._commandCollection;
}
}
public bool ClearBeforeFill {
get {return this._clearBeforeFill;}
set {this._clearBeforeFill = value;}
}
public virtual int Fill(DataTable dataTable)
{
int returnValue = -1;
this.Adapter.SelectCommand = this.CommandCollection[0];
if ((this.ClearBeforeFill == true)) dataTable.Clear();
this.PrepareParameters(0);
try
{
returnValue = this.Adapter.Fill(dataTable);
}
finally
{
this.Connection.Close();
}
return returnValue;
}
public virtual int FillByStrFLTR(DataTable dataTable, string FLTR, string SORT)
{
this.Adapter.SelectCommand = this.CommandCollection[0].Clone();
if (!string.IsNullOrEmpty(FLTR))
this.Adapter.SelectCommand.CommandText = this.Adapter.SelectCommand.CommandText
+ "\r\nWHERE " + FLTR;
if (!string.IsNullOrEmpty(SORT))
this.Adapter.SelectCommand.CommandText = this.Adapter.SelectCommand.CommandText
+ "\r\nORDER BY " + SORT;
if ((this.ClearBeforeFill == true)) dataTable.Clear();
int returnValue = -1;
try
{
this.PrepareParameters(0);
returnValue = this.Adapter.Fill(dataTable);
}
catch (System.Exception e)
{
IPY_Util.YNBox(this.Adapter.SelectCommand.CommandText + "\n error: " + e.ToString());
}
return returnValue;
}
public virtual int Get_Cnt(string FLTR)
{
// Визначє кількість рядків, яку поверне запит при фільтрі FLTR
int returnValue = 0;
try
{
if ((FLTR != null) && (FLTR.Trim().Length > 0)) FLTR = " WHERE " + FLTR;
this.Adapter.SelectCommand = this.CommandCollection[0].Clone();
this.Adapter.SelectCommand.CommandText = string.Format("SELECT COUNT(*) FROM dbo.{0} {1}"
, this.Adapter.TableMappings[0].DataSetTable, FLTR);
this.PrepareParameters(0);
this.Adapter.SelectCommand.Connection.Open();
returnValue = (int)this.Adapter.SelectCommand.ExecuteScalar();
}
catch (System.Exception e)
{
IPY_Util.YNBox(this.Adapter.SelectCommand.CommandText + "\n error: " + e.ToString());
}
finally
{
this.Connection.Close();
}
return returnValue;
}
public virtual int Get_RIndexOf(string FLTR, string SORT, NameValue[] NV_fnd)
{
// Визначє номер по-порядку рядка з ключем NV_fnd, при фільтрі FLTR і сортуванні SORT
int returnValue = -1;
try
{
if ((FLTR != null) && (FLTR.Trim().Length > 0)) FLTR = " WHERE " + FLTR;
if ((SORT != null) && (SORT.Trim().Length > 0)) SORT = " ORDER BY " + SORT;
this.Adapter.SelectCommand = this.CommandCollection[0].Clone();
this.Adapter.SelectCommand.CommandText = string.Format(
"DECLARE @R TABLE(RowN int identity not null,id int null);"
+ "\r\n INSERT INTO @R (id) SELECT {3} as id FROM [dbo].{0} {1} {2};"
+ "\r\n SELECT IsNull((SELECT TOP 1 RowN FROM @R WHERE Id={4}),-1) as RowN"
, this.Adapter.TableMappings[0].DataSetTable, FLTR, SORT, NV_fnd[0].Name, NV_fnd[0].Value.ToString());
this.PrepareParameters(0);
this.Adapter.SelectCommand.Connection.Open();
returnValue = (int)this.Adapter.SelectCommand.ExecuteScalar();
}
catch (System.Exception e)
{
IPY_Util.YNBox(this.Adapter.SelectCommand.CommandText + "\n error: " + e.ToString());
}
finally
{
this.Connection.Close();
}
return returnValue;
}
public virtual int FillPg(DataTable dataTable, string FLTR, string SORT, int R1, int R2)
{
// Повинен завантажити рядки з номерами від R1 до R2, при фільтрі FLTR і сортуванні SORT
// повинен перекриватись в нащадках, адже в цій реалізації завантажує ВСІ рядки
return this.FillByStrFLTR(dataTable, FLTR, SORT);
}
public virtual int Update(DataTable dataTable)
{
this.PrepareParameters(0);
return this.Adapter.Update(dataTable);
}
public virtual int Update(System.Data.DataRow dataRow)
{
return this.Adapter.Update(new System.Data.DataRow[] {dataRow});
}
public virtual int Update(System.Data.DataRow[] dataRows)
{
this.PrepareParameters(0);
return this.Adapter.Update(dataRows);
}
protected virtual void PrepareParameters(int Num)
{
// Повинна підготувати значення параметрів перед Fill та Update
/* наприклад:
this.Adapter.SelectCommand.Parameters["@DType"].Value = 5;
this.Adapter.DeleteCommand.Parameters["@DType"].Value = 5;
this.Adapter.InsertCommand.Parameters["@DType"].Value = 5;
this.Adapter.UpdateCommand.Parameters["@DType"].Value = 5;
*/
}
}
а это реализация для определенного грида:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
[System.ComponentModel.ToolboxItem(false)] // ========================================================
public class ta_AL : IPY_TableAdapter
{
public ta_AL(System.Data.SqlClient.SqlConnection Cn) : base(Cn) { }
protected override void InitAdapter()
{
this._adapter = new System.Data.SqlClient.SqlDataAdapter();
System.Data.Common.DataTableMapping tableMapping = new System.Data.Common.DataTableMapping();
tableMapping.SourceTable = "Table";
tableMapping.DataSetTable = "AL";
this._adapter.TableMappings.Add(tableMapping);
System.Data.SqlClient.SqlCommand c;
this._adapter.DeleteCommand = new System.Data.SqlClient.SqlCommand();
c = this._adapter.DeleteCommand;
c.Connection = this.Connection;
c.CommandText = "DELETE FROM dbo.AL WHERE (Id_L=@Id_L)";
c.CommandType = System.Data.CommandType.Text;
c.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Id_L", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "Id_L", System.Data.DataRowVersion.Original, false, null, "", "", ""));
this._adapter.InsertCommand = new System.Data.SqlClient.SqlCommand();
c = this._adapter.InsertCommand;
c.Connection = this.Connection;
c.CommandText = "Ins_AL";
c.CommandType = System.Data.CommandType.StoredProcedure;
c.Parameters.Add(new System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.ReturnValue, 10, 0, null, System.Data.DataRowVersion.Current, false, null, "", "", ""));
c.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Id", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.InputOutput, 10, 0, "Id_L", System.Data.DataRowVersion.Current, false, null, "", "", ""));
c.Parameters.Add(new System.Data.SqlClient.SqlParameter("@KFO", System.Data.SqlDbType.VarChar, 0, System.Data.ParameterDirection.Input, 10, 0, "KFO", System.Data.DataRowVersion.Current, false, null, "", "", ""));
c.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Id_G", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "Id_G", System.Data.DataRowVersion.Current, false, null, "", "", ""));
c.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Id_Org", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "Id_Org", System.Data.DataRowVersion.Current, false, null, "", "", ""));
c.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Id_Adr", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "Id_Adr", System.Data.DataRowVersion.Current, false, null, "", "", ""));
c.Parameters.Add(new System.Data.SqlClient.SqlParameter("@TypKA", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "TypKA", System.Data.DataRowVersion.Current, false, null, "", "", ""));
c.Parameters.Add(new System.Data.SqlClient.SqlParameter("@BDat", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "BDat", System.Data.DataRowVersion.Current, false, null, "", "", ""));
c.Parameters.Add(new System.Data.SqlClient.SqlParameter("@EDat", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "EDat", System.Data.DataRowVersion.Current, false, null, "", "", ""));
this._adapter.UpdateCommand = new System.Data.SqlClient.SqlCommand();
c = this._adapter.UpdateCommand;
c.Connection = this.Connection;
c.CommandText = "UPDATE dbo.AL"
+ "\r\n SET KFO=@KFO,Id_G=@Id_G,Id_Org=@Id_Org,Id_Adr=@Id_Adr,TypKA=@TypKA,BDat=@BDat,EDat=@EDat"
+ "\r\n WHERE (Id_L=@Id_L);"
+ "\r\n SELECT * FROM dbo.AL WHERE (Id_L=@Id_L)";
c.CommandType = System.Data.CommandType.Text;
c.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Id_L", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "Id_L", System.Data.DataRowVersion.Current, false, null, "", "", ""));
c.Parameters.Add(new System.Data.SqlClient.SqlParameter("@KFO", System.Data.SqlDbType.VarChar, 20, System.Data.ParameterDirection.Input, 0, 0, "KFO", System.Data.DataRowVersion.Current, false, null, "", "", ""));
c.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Id_G", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "Id_G", System.Data.DataRowVersion.Current, false, null, "", "", ""));
c.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Id_Org", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "Id_Org", System.Data.DataRowVersion.Current, false, null, "", "", ""));
c.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Id_Adr", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "Id_Adr", System.Data.DataRowVersion.Current, false, null, "", "", ""));
c.Parameters.Add(new System.Data.SqlClient.SqlParameter("@TypKA", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "TypKA", System.Data.DataRowVersion.Current, false, null, "", "", ""));
c.Parameters.Add(new System.Data.SqlClient.SqlParameter("@BDat", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "BDat", System.Data.DataRowVersion.Current, false, null, "", "", ""));
c.Parameters.Add(new System.Data.SqlClient.SqlParameter("@EDat", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, "EDat", System.Data.DataRowVersion.Current, false, null, "", "", ""));
}
}
[System.ComponentModel.ToolboxItem(false)]
public class ta_AL_01 : ta_AL
{
protected int _dat;
public int dat // дата (int) на яку "витягати" абонента
{
get { return _dat; }
set
{
_dat = value;
if (_dat >= 0)
{
this._adapter.UpdateCommand.Parameters["@Dat"].Value = _dat;
this._commandCollection[0].Parameters["@Dat"].Value = _dat;
}
else
{
this._adapter.UpdateCommand.Parameters["@Dat"].Value = DBNull.Value;
this._commandCollection[0].Parameters["@Dat"].Value = DBNull.Value;
}
}
}
public ta_AL_01(System.Data.SqlClient.SqlConnection Cn)
: this(Cn, DateTime.Today.Subtract(IPY_Util.MyZeroDate).Days){ }
public ta_AL_01(System.Data.SqlClient.SqlConnection Cn, int Dat) : base(Cn)
{
dat = Dat;
}
protected override void InitAdapter()
{
base.InitAdapter();
this._adapter.UpdateCommand.CommandText = "UPDATE dbo.AL"
+ "\r\n SET KFO=@KFO,Id_G=@Id_G,Id_Org=@Id_Org,Id_Adr=@Id_Adr,TypKA=@TypKA,BDat=@BDat,EDat=@EDat"
+ "\r\n WHERE (Id_L=@Id_L);"
+ "\r\n SELECT AL.*, AG.KFOG, KA.KAName as OrgName"
+ "\r\n ,Adr.ZipCode,Adr.SCode,Adr.HNum,Adr.HLit,Adr.HKor,Adr.KNum,Adr.KLit"
+ "\r\n ,Cities.CName, VUL.SName"
+ "\r\n ,dbo.Get_KA_LA(AL.Id_L,@Dat) AS Id_KA, dbo.Get_KAName_LA(AL.Id_L,@Dat) AS KAName"
+ string.Format(
"\r\n ,Bns=CASE WHEN EXISTS(SELECT Id_BA FROM dbo.AGBA WHERE AGBA.TypA={0} and AGBA.Id_A=AL.Id_L and AGBA.BDat<=@Dat and (AGBA.EDat>=@Dat or AGBA.EDat is NULL)) THEN 1 ELSE 0 END", Program.MyTypAL)
+ "\r\n FROM dbo.AL"
+ "\r\n LEFT JOIN dbo.AG ON (AG.Id_G=AL.Id_G)"
+ "\r\n LEFT JOIN dbo.KA ON (KA.Id_KA=AL.Id_Org)"
+ "\r\n LEFT JOIN dbo.Adr ON (Adr.Id_Adr=AL.Id_Adr)"
+ "\r\n LEFT JOIN dbo.Cities ON (Cities.ZipCode=Adr.ZipCode)"
+ "\r\n LEFT JOIN dbo.VUL ON (VUL.SCode=Adr.SCode)"
+ "\r\n WHERE (AL.Id_L=@Id_L)";
this._adapter.UpdateCommand.Parameters.Add(new System.Data.SqlClient.SqlParameter("@Dat", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, null, System.Data.DataRowVersion.Current, false, null, "", "", ""));
}
protected override void InitCommandCollection()
{
this._commandCollection = new System.Data.SqlClient.SqlCommand[1];
this._commandCollection[0] = new System.Data.SqlClient.SqlCommand();
this._commandCollection[0].Connection = this.Connection;
this._commandCollection[0].CommandText = "SELECT AL.*, AG.KFOG, KA.KAName as OrgName"
+ "\r\n ,Adr.ZipCode,Adr.SCode,Adr.HNum,Adr.HLit,Adr.HKor,Adr.KNum,Adr.KLit"
+ "\r\n ,Cities.CName, VUL.SName"
+ "\r\n ,dbo.Get_KA_LA(AL.Id_L,@Dat) AS Id_KA, dbo.Get_KAName_LA(AL.Id_L,@Dat) AS KAName"
+ string.Format(
"\r\n ,Bns=CASE WHEN EXISTS(SELECT Id_BA FROM dbo.AGBA WHERE AGBA.TypA={0} and AGBA.Id_A=AL.Id_L and AGBA.BDat<=@Dat and (AGBA.EDat>=@Dat or AGBA.EDat is NULL)) THEN 1 ELSE 0 END", Program.MyTypAL)
+ "\r\n FROM dbo.AL"
+ "\r\n LEFT JOIN dbo.AG ON (AG.Id_G=AL.Id_G)"
+ "\r\n LEFT JOIN dbo.KA ON (KA.Id_KA=AL.Id_Org)"
+ "\r\n LEFT JOIN dbo.Adr ON (Adr.Id_Adr=AL.Id_Adr)"
+ "\r\n LEFT JOIN dbo.Cities ON (Cities.ZipCode=Adr.ZipCode)"
+ "\r\n LEFT JOIN dbo.VUL ON (VUL.SCode=Adr.SCode)";
this._commandCollection[0].CommandType = CommandType.Text;
this._commandCollection[0].Parameters.Add(new System.Data.SqlClient.SqlParameter("@Dat", System.Data.SqlDbType.Int, 0, System.Data.ParameterDirection.Input, 0, 0, null, System.Data.DataRowVersion.Current, false, null, "", "", ""));
}
public override int Get_Cnt(string FLTR)
{
int returnValue = 0;
try
{
if ((FLTR != null) && (FLTR.Trim().Length > 0)) FLTR = " WHERE " + FLTR;
this.Adapter.SelectCommand = this.CommandCollection[0].Clone();
this.Adapter.SelectCommand.CommandText = string.Format(
"SELECT COUNT(*) FROM dbo.AL"
+ "\r\n LEFT JOIN dbo.AG ON (AG.Id_G=AL.Id_G)"
+ "\r\n LEFT JOIN dbo.KA ON (KA.Id_KA=AL.Id_Org)"
+ "\r\n LEFT JOIN dbo.Adr ON (Adr.Id_Adr=AL.Id_Adr)"
+ "\r\n LEFT JOIN dbo.Cities ON (Cities.ZipCode=Adr.ZipCode)"
+ "\r\n LEFT JOIN dbo.VUL ON (VUL.SCode=Adr.SCode)"
+ "\r\n {0}", FLTR);
this.PrepareParameters(0);
this.Adapter.SelectCommand.Connection.Open();
returnValue = (int)this.Adapter.SelectCommand.ExecuteScalar();
}
catch (System.Exception e)
{
IPY_Util.YNBox(this.Adapter.SelectCommand.CommandText + "\n error: " + e.ToString());
}
finally
{
this.Connection.Close();
}
return returnValue;
}
public override int Get_RIndexOf(string FLTR, string SORT, NameValue[] NV_fnd)
{
int returnValue = -1;
try
{
if ((FLTR != null) && (FLTR.Trim().Length > 0)) FLTR = " WHERE " + FLTR;
if ((SORT != null) && (SORT.Trim().Length > 0)) SORT = " ORDER BY " + SORT;
this.Adapter.SelectCommand = this.CommandCollection[0].Clone();
this.Adapter.SelectCommand.CommandText = string.Format(
"DECLARE @R TABLE(RowN int identity not null,id int null);"
+ "\r\n INSERT INTO @R (id) SELECT {2} as id FROM dbo.AL"
+ "\r\n LEFT JOIN dbo.AG ON (AG.Id_G=AL.Id_G)"
+ "\r\n LEFT JOIN dbo.KA ON (KA.Id_KA=AL.Id_Org)"
+ "\r\n LEFT JOIN dbo.Adr ON (Adr.Id_Adr=AL.Id_Adr)"
+ "\r\n LEFT JOIN dbo.Cities ON (Cities.ZipCode=Adr.ZipCode)"
+ "\r\n LEFT JOIN dbo.VUL ON (VUL.SCode=Adr.SCode)"
+ "\r\n {0} {1};"
+ "\r\n SELECT IsNull((SELECT TOP 1 RowN FROM @R WHERE Id={3}),-1) as RowN"
, FLTR, SORT, NV_fnd[0].Name, NV_fnd[0].Value.ToString());
this.PrepareParameters(0);
this.Adapter.SelectCommand.Connection.Open();
returnValue = (int)this.Adapter.SelectCommand.ExecuteScalar();
}
catch (System.Exception e)
{
IPY_Util.YNBox(this.Adapter.SelectCommand.CommandText + "\n error: " + e.ToString());
}
finally
{
this.Connection.Close();
}
return returnValue;
}
public override int FillPg(DataTable dataTable, string FLTR, string SORT, int R1, int R2)
{
int returnValue = -1;
if ((FLTR != null) && (FLTR.Trim().Length > 0)) FLTR = " WHERE " + FLTR;
if ((SORT != null) && (SORT.Trim().Length > 0)) SORT = " ORDER BY " + SORT;
this.Adapter.SelectCommand = this.CommandCollection[0].Clone();
this.Adapter.SelectCommand.CommandText = string.Format(
"DECLARE @R TABLE(RowN int identity not null,id int null);"
+ "\r\n INSERT INTO @R (id) SELECT TOP {3} [Id_L]"
+ "\r\n FROM dbo.AL"
+ "\r\n LEFT JOIN dbo.AG ON (AG.Id_G=AL.Id_G)"
+ "\r\n LEFT JOIN dbo.KA ON (KA.Id_KA=AL.Id_Org)"
+ "\r\n LEFT JOIN dbo.Adr ON (Adr.Id_Adr=AL.Id_Adr)"
+ "\r\n LEFT JOIN dbo.Cities ON (Cities.ZipCode=Adr.ZipCode)"
+ "\r\n LEFT JOIN dbo.VUL ON (VUL.SCode=Adr.SCode)"
+ "\r\n {0} {1};"
+ "\r\n SELECT AL.*, AG.KFOG, KA.KAName as OrgName"
+ "\r\n ,Adr.ZipCode,Adr.SCode,Adr.HNum,Adr.HLit,Adr.HKor,Adr.KNum,Adr.KLit"
+ "\r\n ,Cities.CName, VUL.SName"
+ "\r\n ,dbo.Get_KA_LA(AL.Id_L,@Dat) AS Id_KA, dbo.Get_KAName_LA(AL.Id_L,@Dat) AS KAName"
+ string.Format("\r\n ,Bns=CASE WHEN EXISTS(SELECT Id_BA FROM dbo.AGBA WHERE AGBA.TypA={0} and AGBA.Id_A=AL.Id_L and AGBA.BDat<=@Dat and (AGBA.EDat>=@Dat or AGBA.EDat is NULL)) THEN 1 ELSE 0 END", Program.MyTypAL)
+ "\r\n FROM @R INNER JOIN dbo.AL ON (AL.Id_L=Id)"
+ "\r\n LEFT JOIN dbo.AG ON (AG.Id_G=AL.Id_G)"
+ "\r\n LEFT JOIN dbo.KA ON (KA.Id_KA=AL.Id_Org)"
+ "\r\n LEFT JOIN dbo.Adr ON (Adr.Id_Adr=AL.Id_Adr)"
+ "\r\n LEFT JOIN dbo.Cities ON (Cities.ZipCode=Adr.ZipCode)"
+ "\r\n LEFT JOIN dbo.VUL ON (VUL.SCode=Adr.SCode)"
+ "\r\n WHERE RowN between {2} and {3}"
+ "\r\n ORDER BY RowN"
, FLTR, SORT, R1, R2);
try
{
returnValue = this.Adapter.Fill(dataTable);
}
catch (System.Exception e)
{
IPY_Util.YNBox(this.Adapter.SelectCommand.CommandText + "\n error: " + e.ToString());
}
return returnValue;
}
}
|
