//-----------------------------------------------------------------//All Rights Reserved , Copyright (C) 2012 , Hairihan TECH, Ltd.//-----------------------------------------------------------------
using System;
using System.Data;
using System.Collections.Generic;
using DotNet.Utilities;
using DotNet.Business;
//DbTools///静态数据库访问的方法程序//修改纪录//2012-02-14 版本:1.0 JiRiGaLa 整理例子程序功能。//版本:1.0/JiRiGaLa///2012-02-14//publicpartialclass DbTools : System.Web.UI.Page
{
protectedvoid Page_Load(object sender, EventArgs e)
{
//静态方法调用数据库的方法this.StaticMethod();
}
privatevoid StaticMethod()
{
//设置数据库连接 DotNet.Utilities.DbHelper.DbConnection = "Data Source=localhost;Initial Catalog=UserCenterV36;User Id = sa ; Password = Password@1234;";
//设置数据库类型 DotNet.Utilities.DbHelper.DbType = CurrentDbType.SqlServer;
//DotNet.Utilities.DbHelper.DbType = CurrentDbType.Oracle;//DotNet.Utilities.DbHelper.DbType = CurrentDbType.MySql;//DotNet.Utilities.DbHelper.DbType = CurrentDbType.DB2;//DotNet.Utilities.DbHelper.DbType = CurrentDbType.Access;//DotNet.Utilities.DbHelper.DbType = CurrentDbType.SQLite;
this.Fill();
this.ExecuteNonQuery();
this.ExecuteScalar();
this.ExecuteReader();
}
//Fill///静态方法调用数据库的方法///privatevoid Fill()
{
//1:直接执行string commandText = @"SELECT *
FROM BASE_USER
WHERE DeletionStateCode = 0";
DataTable dataTable = DotNet.Utilities.DbHelper.Fill(commandText);
//2:防注入的安全的参数化运行方式执行查询 commandText = @"SELECT *
FROM BASE_USER
WHERE Code =" + DotNet.Utilities.DbHelper.GetParameter("Code")
+ "AND DeletionStateCode =" + DotNet.Utilities.DbHelper.GetParameter("DeletionStateCode");
//这里是生成安全参数的方法 List dbParameters = new List();
dbParameters.Add(DotNet.Utilities.DbHelper.MakeParameter("Code", "jirigala"));
dbParameters.Add(DotNet.Utilities.DbHelper.MakeParameter("DeletionStateCode", 0));
DotNet.Utilities.DbHelper.Fill(commandText, dbParameters.ToArray());
//3:执行存储过程,假设也是需要传递这2个参数,类似功能的一个存储过程//我们不建议用存储过程,因为会对系统移植,多数据库设计等上会遇到很多麻烦 commandText = "GET_USER";
dataTable = DotNet.Utilities.DbHelper.Fill(commandText, dbParameters.ToArray(), CommandType.StoredProcedure);
}
//ExecuteNonQuery///静态方法调用数据库的方法//影响行数privateint ExecuteNonQuery()
{
int returnValue = 0;
//1:直接执行string commandText = @"UPDATE BASE_USER
SET DELETIONSTATECODE = 0";
returnValue = DotNet.Utilities.DbHelper.ExecuteNonQuery(commandText);
//2:防注入的安全的参数化运行方式执行查询 commandText = @"UPDATE BASE_USER
SET DELETIONSTATECODE = 0
WHERE CODE =" + DotNet.Utilities.DbHelper.GetParameter("Code")
+ "AND DELETIONSTATECODE =" + DotNet.Utilities.DbHelper.GetParameter("DeletionStateCode");
//这里是生成安全参数的方法 List dbParameters = new List();
dbParameters.Add(DotNet.Utilities.DbHelper.MakeParameter("Code", "jirigala"));
dbParameters.Add(DotNet.Utilities.DbHelper.MakeParameter("DeletionStateCode", 0));
returnValue = DotNet.Utilities.DbHelper.ExecuteNonQuery(commandText, dbParameters.ToArray());
//3:执行存储过程,假设也是需要传递这2个参数,类似功能的一个存储过程//我们不建议用存储过程,因为会对系统移植,多数据库设计等上会遇到很多麻烦 commandText = "UPDATE_USER";
returnValue = DotNet.Utilities.DbHelper.ExecuteNonQuery(commandText, dbParameters.ToArray(), CommandType.StoredProcedure);
return returnValue;
}
//ExecuteScalar///静态方法调用数据库的方法//结果privateobject ExecuteScalar()
{
object returnValue = null;
//1:直接执行string commandText = @"SELECT CODE
FROM BASE_USER
WHERE DELETIONSTATECODE = 0";
returnValue = DotNet.Utilities.DbHelper.ExecuteScalar(commandText);
//2:防注入的安全的参数化运行方式执行查询 commandText = @"SELECT CODE
FROM BASE_USER
WHERE CODE =" + DotNet.Utilities.DbHelper.GetParameter("Code")
+ "AND DELETIONSTATECODE =" + DotNet.Utilities.DbHelper.GetParameter("DeletionStateCode");
//这里是生成安全参数的方法 List dbParameters = new List();
dbParameters.Add(DotNet.Utilities.DbHelper.MakeParameter("Code", "jirigala"));
dbParameters.Add(DotNet.Utilities.DbHelper.MakeParameter("DeletionStateCode", 0));
returnValue = DotNet.Utilities.DbHelper.ExecuteScalar(commandText, dbParameters.ToArray());
//3:执行存储过程,假设也是需要传递这2个参数,类似功能的一个存储过程//我们不建议用存储过程,因为会对系统移植,多数据库设计等上会遇到很多麻烦 commandText = "GET_USER";
returnValue = DotNet.Utilities.DbHelper.ExecuteScalar(commandText, dbParameters.ToArray(), CommandType.StoredProcedure);
return returnValue;
}
//ExecuteReader///静态方法调用数据库的方法///privatevoid ExecuteReader()
{
//1:直接执行string commandText = @"SELECT *
FROM BASE_USER
WHERE DELETIONSTATECODE = 0";
DotNet.Utilities.DbHelper.ExecuteReader(commandText);
//2:防注入的安全的参数化运行方式执行查询 commandText = @"SELECT *
FROM BASE_USER
WHERE CODE =" + DotNet.Utilities.DbHelper.GetParameter("Code")
+ "AND DELETIONSTATECODE =" + DotNet.Utilities.DbHelper.GetParameter("DeletionStateCode");
//这里是生成安全参数的方法 List dbParameters = new List();
dbParameters.Add(DotNet.Utilities.DbHelper.MakeParameter("Code", "jirigala"));
dbParameters.Add(DotNet.Utilities.DbHelper.MakeParameter("DeletionStateCode", 0));
DotNet.Utilities.DbHelper.ExecuteReader(commandText, dbParameters.ToArray());
//3:执行存储过程,假设也是需要传递这2个参数,类似功能的一个存储过程//我们不建议用存储过程,因为会对系统移植,多数据库设计等上会遇到很多麻烦 commandText = "GET_USER";
DotNet.Utilities.DbHelper.ExecuteReader(commandText, dbParameters.ToArray(), CommandType.StoredProcedure);
}
}