Thoroughly Study sqlhelper (non-stored procedure)

081229

Parameter names can be distinguished using [J. ID]

1. Dbhelper. getparameter ("@ ID", sqldbtype. Int, 4, "[J. ID]", ID) // multiple Commas are correct,

1. sqlparameter usage

Two constructor types:

Sqlparameter Param = new sqlparameter (paramname, paramtype, paramsize, colname );

Sqlparameter Param = new sqlparameter (paramname, paramtype );
Param. value = paramvalue;

The above is the column corresponding to the data table. To summarize the following usage:

Public static sqlparameter getparameter (string paramname, sqldbtype paramtype, int32 paramsize, string colname, object paramvalue)
{
Sqlparameter Param = new sqlparameter (paramname, paramtype, paramsize, colname );
Param. value = paramvalue;
Return Param;
}

 

Dbhelper. getparameter ("@ gid", sqldbtype. Int, 4, "[GID]", GID)

When multiple tables are queried, the parameters are the same and can be distinguished directly in the constructor.

Public datatable getlist (INT ocheck, string UID)
{
Stringbuilder sb = new stringbuilder ();
SB. append ("select OID,. gid, B. gname,. onum,. summary, B. gspec from t_takeout_temp A, t_goods B where. gid = B. GID and ocheck = @ ocheck and. uid = @ uid ");

Sqlparameter [] Param =
{
Dbhelper. getparameter ("@ ocheck", sqldbtype. Int, 4, "[ocheck]", ocheck ),
Dbhelper. getparameter ("@ uid", sqldbtype. varchar, 50, "A. [uid]", UID) // use a. [uid] Here
};
Return dbhelper. executedt (sb. tostring (), Param );
}

I don't quite understand the principle, but I will use it.

2. Skills with single value.

1. /// <Summary>
2. /// Obtain the personnel name based on the personnel ID
3. /// </Summary>
4. /// <Param name = "rydm"> </param>
5. /// <Returns> </returns>
6. Public String getrymc (string rydm)
7. {
8. Datatable dt = getuser (rydm );
9. Return DT. Rows [0] ["rymc"]. tostring ();
10. }
11. /// <Summary>
12. /// Query by personnel ID
13. /// </Summary>
14. /// <Param name = "rydm"> </param>
15. /// <Returns> </returns>
16. Public datatable getuser (string rydm)
17. {
18. Stringbuilder sb = new stringbuilder ();
19. SB. append ("select *");
20. SB. append ("from p_rydm where [rydm] = @ rydm ");
21. Sqlparameter [] Param = {dbhelper. getparameter ("@ rydm", sqldbtype. varchar, 50, "[rydm]", rydm )};
22. Return dbhelper. executedt (sb. tostring (), Param );
23. }

First, write a method to get the datatable, and then directly call DT. Rows [0] ["field name"]. tostring ()

1. Datatable table = dbhelper. executedt (sb. tostring ());
2. If (table. Rows. Count> 0)
3. {
4. If (table. Rows [0] ["Sid"]. tostring ()! = "")
5. Return table. Rows [0] ["Sid"]. tostring ();
6. Else
7. Return "1001 ";
8. }
9. Else
10. {
11. Return "0 ";
12. }

Here, the rows. Count judgment is necessary. In addition, null characters may be returned, so it is also necessary to determine whether it is ""

3. dbhelper. executedt () has two kinds of overloading:

Execute SQL statements with or without parameters.

4. The most common statements do not need to be blurred.

Query select [gname], [gspec] from goods where [GID] = @ [GID] and [OID] = @ OID

Update goods set [gname] = @ gname, [gspec] = @ gspec where [GID] = @ GID

Delete Delete from goods where [GID] = @ GID

Add insert into goods ([gname], [gspec]) values (@ gname, @ gspec)

5. Accumulation of methods used

SB. append ("Update t_goods set [Storage] = [Storage]-@ num where [GID] = @ gid ");

Dbhelper. getparameter ("@ num", sqldbtype. Int, 4, "[num]", num)

[Num] is not a field in the data table, that is, @ num does not need a column name,

However, there is no constructor without the corresponding column name, so I have to add a corresponding column.

The strange thing is that sqlparameter does not have the corresponding column constructor, and even the length is gone.

It seems that the length does not need to be written. There is a default value, and you have time to try it.

5.2

Select distinct Sid from storein where sdate between @ start and @ end

6. dbhelper. executesql (sb. tostring (), Param );

This method does not have any overload without parameters. You have time to write it yourself.

The solution is to add a parameter without using this parameter.

7. In addition to searching for additions, deletions, and changes with executedt (), executesql () is used ()

In addition, the self-written executeds () and the method for batch SQL Execution executesqls () will be studied later and will not be used for the time being.

The two methods are sufficient.

End