Database Components Hxj.data (vii) (SQL article)

The previous section describes the delete operation, and this section describes how to execute the SQL statement directly.

Executing SQL statements directly is using the Fromsql method.

DBSESSION.DEFAULT.FROMSQL ("SELECT * FROM Products"). Todatatable ();

It looks so much kinder that direct SQL can be executed.

Of course, you can add the parameters of AH.

DBSESSION.DEFAULT.FROMSQL ("SELECT * from the products where productid=pid"). Addinparameter ("pid", Dbtype.int32, 1). Todatatable ();

The query condition is that productid=1 returns a record.

Here SQL statements in the PID to ensure that the only, otherwise will be replaced with parameters.

For example, select * from the products where Productid=productid is replaced under SQL Server with the SELECT * from the products where @productid = @productid

This is just a simple substitution, so make sure that the argument uniqueness is declared.

When multiple parameters can be written as follows:

DbParameter[]　parameters　=　new　DbParameter[2];
parameters[0]　=　DbSession.Default.Db.DbProviderFactory.CreateParameter();
parameters[0].DbType　=　DbType.Int32;
parameters[0].ParameterName　=　"pid";
parameters[0].Value　=　1;
parameters[1]　=　DbSession.Default.Db.DbProviderFactory.CreateParameter();
parameters[1].DbType　=　DbType.Int32;
parameters[1].ParameterName　=　"cid";
parameters[1].Value　=　2;
DbSession.Default.FromSql("select　*　from　products　where　productid=pid　or　categoryid=cid")
　　 .AddParameter(parameters)
　　 .ToDataTable();

It seems like a lot of trouble to write, and the more concise wording is as follows:

DbSession.Default.FromSql("select　*　from　products　where　productid=pid　or　categoryid=cid")
　　　　　　　　 .AddInParameter("pid",　DbType.Int32,　1)
　　　　　　　　 .AddInParameter("cid",　DbType.Int32,　2)
　　　　　　　　 .ToDataTable();

This is much more refreshing.

The execution of SQL statements is also straightforward.

The next section describes the execution of the stored procedure.