数据库操作通用类
⑴ asp.net中数据库连接的公共类的调用方法
下面的例子就是调用通用类的数据库操作方法（数据库的链接与关闭都在通用类中），不懂得花可以发例子给你。using System;
using System.Collections.Generic;
using System.Text;using TroubledTimes.Models;
using System.Data;
using System.Data.sqlClient;namespace TroubledTimes.DAL
{
/// <summary>
/// 官方活动信息数据访问类
/// </灶兄summary>
public static class FunctionsService
{
/// <summary>
/// 1.根据不同情况查询活动信息
/// </summary>
//敬模/ <param name="type">活动类型</param>
/// <param name="state">设置状态</param>
/// <param name="name">活动名称</param>
/// <param name="flag">控制变量</param>
/// <returns>活动信息对象的集合</returns>
public static IList<Functions> GetAllFunctions(string type,string state,string name,int flag)
{
string sql = "Select * from Functions where State =1";
if(type!="" && flag==1)
sql += " and FunState='" + type + "'";
else if (state != "" && flag == 2)
sql += " and SetState='" + state + "'";
else if (name!="" && flag==3)
sql += " and FunctionName like '%" + name + "%'";
else if (flag == 4)
sql += " and FunState='" + type + "隐稿袭' and SetState='" + state + "'";
else if (flag == 5)
sql += " and FunState='" + type + "' and FunctionName like '%" + name + "%'";
else if (flag == 6)
sql += " and SetState='" + state + "' and FunctionName like '%" + name + "%'";
else if (flag == 7)
sql += " and FunState='" + type + "' and SetState='" + state + "' and FunctionName like '%" + name + "%'";
sql += " order by FunNumber Desc";
IList<Functions> list = new List<Functions>();
try
{
// DataTable dt = DBHelper.GetScalar("up_SelectFunctions");
DataTable dt = DBHelper.GetDataTable(sql);
foreach (DataRow row in dt.Rows)
{
Functions function = new Functions();
function.FunctionName = (string)row["FunctionName"];
function.FId = (int)row["FId"];
function.FunctionUrl = (string)row["FunctionUrl"];
function.FunctionImg = (string)row["FunctionImg"];
function.FunctionContent = (string)row["FunctionContent"];
function.FunctionTime = (DateTime)row["FunctionTime"];
function.FunAdminUrl = (string)row["FunAdminUrl"];
function.FunState = (int)row["FunState"]; //--活动类型（游戏活动/官网活动,0:游戏）<后加>
function.SetState = (int)row["SetState"]; //--设置状态（设置中/预设置,0:预设置）<后加>
function.FunNumber = (int)row["FunNumber"]; //--活动支持率（仅官网）<后加>
function.State = (int)row["State"]; //--存贮状态（0/1）
list.Add(function);
}
return list;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return null;
}
} /// <summary>
/// 2.根据活动类型获取活动信息
/// </summary>
/// <param name="id">活动类型</param>
/// <returns>该活动类型的数量</returns>
public static int GetFunctionsByType(int type)
{
IList<Functions> list = new List<Functions>();
try
{
string sql = "select count(*) from Functions where SetState = 1 and FunState='" + type+ "'";
return DBHelper.Sanlar(sql); }
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return 0;
}
} /// <summary>
/// 3.根据活动ID修改活动信息
/// </summary>
/// <param name="f">活动信息类对象</param>
/// <returns>数据库中受影响的行数</returns>
public static int ModifyFunctionsById(Functions f)
{
try
{
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@FId",f.FId),
new SqlParameter("@FunctionName",f.FunctionName),
new SqlParameter("@FunctionUrl",f.FunctionUrl),
new SqlParameter("@FunctionImg",f.FunctionImg),
new SqlParameter("@FunctionContent",f.FunctionContent),
new SqlParameter("@FunctionTime",f.FunctionTime),
new SqlParameter("@function.FunAdminUrl",f.FunAdminUrl),
new SqlParameter("@FunState",f.FunState),
new SqlParameter("@FunNumber",f.FunNumber),
new SqlParameter("@SetState",f.SetState),
new SqlParameter("@State",f.State)
};
return DBHelper.ExecuteProc("up_AmendFunctions", para);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return 0;
}
} /// <summary>
/// 4.添加活动信息
/// </summary>
/// <param name="f">活动信息类对象</param>
/// <returns>数据库中受影响的行数</returns>
public static int AddFunctions(Functions f)
{
try
{
SqlParameter[] para = new SqlParameter[]
{
new SqlParameter("@FunctionName",f.FunctionName),
new SqlParameter("@FunctionUrl",f.FunctionUrl),
new SqlParameter("@FunctionImg",f.FunctionImg),
new SqlParameter("@FunctionContent",f.FunctionContent),
new SqlParameter("@FunctionTime",f.FunctionTime),
new SqlParameter("@FunAdminUrl",f.FunAdminUrl),
new SqlParameter("@FunState",f.FunState),
new SqlParameter("@FunNumber",f.FunNumber),
new SqlParameter("@SetState",f.SetState),
new SqlParameter("@State",f.State) };
return DBHelper.ExecuteProc("up_AddFunctions", para);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return 0;
} }
/// <summary>
/// 5、根据id批量删除活动信息(修改)
/// </summary>
/// <param name="id">活动信息id</param>
/// <returns>返回受影响的行数</returns>
public static int DeleteFunById(string ids)
{
//string sql = "update Functions set State = 0 where FId in('"+ids+"')";
string sql = "update Functions set State = 0 where FId ='" + ids + "'";
try
{
return DBHelper.ExecuteCommand(sql);
}
catch(Exception ex)
{
throw ex;
}
}
/// <summary>
/// 6、根据id修改设置状态
/// </summary>
/// <param name="id">活动信息id</param>
/// <returns>返回受影响的行数</returns>
public static int UpdateSetById(int id, int setState)
{
string sql = "Update Functions set SetState = "+setState+" where FId="+id;
try
{
return DBHelper.ExecuteCommand(sql);
}
catch(Exception ex)
{
throw ex;
}
}
}
}
⑵ DBFactory一个通用的数据库操作类，可以访问SQL,Oracle数据库。
Public Function ConnectionString(ByVal ProviderName As String) As String
Dim ConnStr As String = ""
Select Case ProviderName
Case "System.Data.OleDb"
ConnStr = "Provider=SQLOLEDB;Server=" & My.Settings.ServerName & ";Database=" & My.Settings.DBName & ";UID=" & My.Settings.DBUser & ";PWD=" & My.Settings.DBPWD
Case "System.Data.SqlClient"
ConnStr = "Data Source=" & My.Settings.ServerName & ";Initial Catalog=" & My.Settings.DBName & ";Persist Security Info=True;User ID=" & My.Settings.DBUser & ";Password=" & My.Settings.DBPWD
Case "System.Data.OracleClient"
ConnStr = "Data Source=" & My.Settings.ServerName & ";Password=" & My.Settings.DBPWD & ";User ID=" & My.Settings.DBUser
End Select
Return ConnStr
End Function
Public Function GetData(ByVal SqlString As String) As DataTable
Try
Dim mProvider As DbProviderFactory = DbProviderFactories.GetFactory(My.Settings.Provider)
Dim mConnection As DbConnection = mProvider.CreateConnection
mConnection.ConnectionString = ConnectionString(My.Settings.Provider)
Dim mCmd As DbCommand = mProvider.CreateCommand
mCmd.CommandText = SqlString
mCmd.Connection = mConnection
Dim mAda As DbDataAdapter = mProvider.CreateDataAdapter
mAda.TableMappings.Add("table", "temp")
mAda.SelectCommand = mCmd
mConnection.Open()
mCmd.ExecuteNonQuery()
mConnection.Close()
Dim dst As New DataSet
mAda.Fill(dst, "temp")
Return dst.Tables("temp")
Catch ex As Exception
Return New DataTable
End Try
End Function
⑶ 想写一个操作数据库的通用dll类库，该注意什么
1.dll的入口只有一处，出口也只有一处，保证完整性。
2.能进行所有的数据库操作。
3.所有的函数需要有返回值，并且返回值需要的是最简单的数据类型，其他的应按照ref传值
4.所有资源做到有申明就有释放，有打开就有关闭。
5.不要用static这种东西
6.安全性问题，什么是私有的，那些是可被外部调用的，受保护的.
⑷ 什么是数据库操作类
数据库操作类是自己写的一个类，主要功能是对数据库进行增删查改。
在其他地方调用这个类中的增删查改方法即可以操作数据库。