Dbhelper:
/// <Summary>
/// Execute the query
/// </Summary>
/// <Param name = "SQL"> A valid SELECT statement </param>
/// <Returns> return sqldatareader </returns>
Public static sqldatareader executereader (string SQL)
{
Sqlconnection con = new sqlconnection (constring );
Con. open ();
Sqlcommand cmd = new sqlcommand (SQL, con );
Return cmd. executereader (commandbehavior. closeconnection );
}
/// <Summary>
/// Execution query with Parameters
/// </Summary>
/// <Param name = "SQL"> A valid SELECT statement </param>
/// <Returns> return sqldatareader </returns>
Public static sqldatareader executereader (string SQL, sqlparameter parameter)
{
Sqlconnection con = new sqlconnection (constring );
Con. open ();
Sqlcommand cmd = new sqlcommand (SQL, con );
Cmd. Parameters. Add (parameter );
Return cmd. executereader (commandbehavior. closeconnection );
}
/// <Summary>
/// Execute the query with a parameter Array
/// </Summary>
/// <Param name = "SQL"> A valid SELECT statement </param>
/// <Returns> return sqldatareader </returns>
Public static sqldatareader executereader (string SQL, sqlparameter [] parameters)
{
Sqlconnection con = new sqlconnection (constring );
Con. open ();
Sqlcommand cmd = new sqlcommand (SQL, con );
// Add an array to addrange
Cmd. Parameters. addrange (parameters );
Return cmd. executereader (commandbehavior. closeconnection );
}
/// <Summary>
/// Execute add, delete, and modify operations
/// </Summary>
/// <Param name = "SQL"> </param>
/// <Returns> Number of affected rows </returns>
Public static int executenonquery (string SQL)
{
Using (sqlconnection con = new sqlconnection (constring ))
{
Con. open ();
Sqlcommand cmd = new sqlcommand (SQL, con );
Return cmd. executenonquery ();
}
}
Public static int executenonquery (string SQL, sqlparameter [] parameters)
{
Using (sqlconnection con = new sqlconnection (constring ))
{
Con. open ();
Sqlcommand cmd = new sqlcommand (SQL, con );
// Foreach (sqlparameter item in parameters)
//{
// Cmd. Parameters. Add (item );
//}
Cmd. Parameters. addrange (parameters );
Return cmd. executenonquery ();
}
}
Public static int executenonquery (string SQL, sqlparameter parameter)
{
Using (sqlconnection con = new sqlconnection (constring ))
{
Con. open ();
Sqlcommand cmd = new sqlcommand (SQL, con );
Cmd. Parameters. Add (parameter );
Return cmd. executenonquery ();
}
}
Dal:
Public static int insert (Company Model)
{
Stringbuilder strsql = new stringbuilder ();
Strsql. append ("insert into company ");
Strsql. append ("(fullname, shortname, keywords, description, type, property, style, capital, size, details, province, city, address, postalcode, tel, fax, mailbox, URL, link, createtime, poss, linkman, product, userid )");
Strsql. append ("values (");
Strsql. append ("@ fullname, @ shortname, @ keywords, @ description, @ type, @ property, @ style, @ capital, @ size, @ details, @ province, @ city, @ address, @ postalcode, @ Tel, @ fax, @ mailbox, @ URL, @ link, @ createtime, @ poss, @ linkman, @ Product, @ userid )");
Sqlparameter [] parameters = {
New sqlparameter ("@ fullname", sqldbtype. varchar ),
New sqlparameter ("@ shortname", sqldbtype. varchar ),
New sqlparameter ("@ keywords", sqldbtype. varchar ),
..................
};
// New sqlparameter (param_password, password = NULL? (Object) dbnull. Value: (object) password ),
Parameters [0]. value = model. fullname;
Parameters [1]. value = ""; // model. shortname;
Parameters [2]. value = ""; // model. Keywords;
Parameters [3]. value = model. description;
Parameters [4]. value = model. type;
Parameters [5]. value = model. Property;
Parameters [6]. value = model. style;
Parameters [7]. value = model. capital;
Parameters [8]. value = model. size;
// If model. if the details parameter is empty, "@ details parameter is required, but this parameter is not provided" will be reported during execution. Therefore, parameters [9] is not supported. value = model. details; write in this way
Parameters [9]. value = model. Details = NULL? (Object) system. dbnull. Value: model. details;
// Parameters [9]. value = model. details;
...................
Return dbhelper. executenonquery (strsql. tostring (), parameters );
}
Public static list <company> selecttop5 (string type)
{
// Asp.net sqlparameter the problem about the like parameter passing is that SQL automatically adds single quotation marks to the parameter. In SQLCodeIt is parsed into like '% 'type' %' ", so it must be written in the following form
String SQL = "select top 5 * from company where poss = 'using' and type like @ type order by createtime DESC ";
String seach = "%" + Type + "% ";
Sqldatareader reader = dbhelper. executereader (SQL, new sqlparameter ("@ Type", objtostr (seach )));
...................
}
Public static int updatecominfo (Company Model)
{
String sql11 = "Update company set fullname = @ fullname, shortname = @ shortname, KEYWORDS = @ keywords, description = @ description, [type] = @ type, [property] = @ property, style = @ style, Capital = @ capital, [size] = @ size, details = @ details, Province = @ province, city = @ city, address = @ address, postalcode = @ postalcode, tel = @ Tel, fax = @ fax, mailbox = @ mailbox, url = @ URL, link = @ link, createtime = @ createtime, poss = @ poss, linkman = @ linkman, Product = @ Product, userid = @ userid where id = @ ID ";
Sqlparameter [] parameters = {
New sqlparameter ("@ fullname", objtostr (model. fullname )),
New sqlparameter ("@ shortname", objtostr (model. shortname )),
New sqlparameter ("@ keywords", objtostr (model. Keywords )),
...................
};
Return dbhelper. executenonquery (sql11, parameters );
}
Public static company selectbyid (string ID)
{
String SQL = "select * from company where id = @ ID ";
Sqldatareader reader = dbhelper. executereader (SQL, new sqlparameter ("@ ID", objtostr (ID )));
................
}