大家好,小弟刚学.net不久,想请教一个问题?
在.net中我每次操作数据库(查询,增添等,调存储过程)都是,先写sql语句。然后OracleConnection,OraDataAdapter,DataSet,Fill,return dataset之类的,都是重复手写,非常麻烦。不知道有没有什么好的方法,操作数据库之类的。比如,框架,或者写好的类等。希望大侠给与指导,谢谢!
在.net中我每次操作数据库(查询,增添等,调存储过程)都是,先写sql语句。然后OracleConnection,OraDataAdapter,DataSet,Fill,return dataset之类的,都是重复手写,非常麻烦。不知道有没有什么好的方法,操作数据库之类的。比如,框架,或者写好的类等。希望大侠给与指导,谢谢!
public class Dbcon
{
SqlConnection conn;
string context = null;
OleDbConnection odbccon; public Dbcon()
{
context = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
conn = new SqlConnection(context);
}
public Dbcon(int i)
{
if(i==1)
context = ConfigurationManager.ConnectionStrings["con1"].ConnectionString;
conn = new SqlConnection(context);
}
//查询数据
SqlDataAdapter sda = null;
public DataSet getdata(string sql)
{
sda = new SqlDataAdapter(sql, conn);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
public int getint(string sql)
{
conn.Open();
com = new SqlCommand(sql, conn);
int i = 0;
try
{
i = int.Parse(com.ExecuteScalar().ToString());
}
catch { }
conn.Close();
return i;
}
//修改数据集
SqlCommandBuilder scb;
public void setdata(DataSet ds)
{
scb = new SqlCommandBuilder(sda);
sda.Update(ds);
}
// 查询
SqlCommand com = null;
public SqlDataReader getreader(string sql)
{
conn.Open();
com = new SqlCommand(sql, conn);
SqlDataReader sdr = com.ExecuteReader();
this.close();
return sdr; }
//添加,删除,修改
public int UpdateData(string sql)
{
conn.Open();
com = new SqlCommand(sql, conn);
int i = com.ExecuteNonQuery();
close();
return i;
}
//关闭连接
public void close()
{
com.Dispose();
conn.Close();
}
//使用事务
public bool sqlTran(System.Collections.ArrayList sqlarr)
{
bool b = true;
conn.Open();
SqlTransaction sqlTran = conn.BeginTransaction();
SqlCommand command = conn.CreateCommand();
command.Transaction = sqlTran;
try
{
for (int i = 0; i < sqlarr.Count; i++)
{
command.CommandText += " " + sqlarr[i].ToString();
}
command.ExecuteNonQuery();
sqlTran.Commit();
}
catch
{
b = false;
sqlTran.Rollback();
}
conn.Close();
return b;
}
//使用参数
public int sqlstreamParameters(string sql, string[] parName, object[] parvalue)
{
com = new SqlCommand(sql, conn);
for (int i = 0; i < parName.Length; i++)
{
if (parvalue[i] != null)
{
com.Parameters.Add(parName[i], SqlDbType.Image);
com.Parameters[parName[i]].Value = parvalue[i];
}
}
conn.Open();
int res = com.ExecuteNonQuery();
close();
return res;
}
//从EXCEL查询数据
public DataSet getExcelData(string strsql)
{
try
{
OleDbDataAdapter oda = new OleDbDataAdapter(strsql, odbccon);
DataSet ods = new DataSet();
oda.Fill(ods);
return ods;
}
catch
{
return null;
}
}
//获取EXCEL连接
public OleDbConnection getExcelCon(String filename)
{
context = "Provider=Microsoft.Jet.Oledb.4.0;Data Source= " + filename + ";" + "Extended Properties=Excel 8.0";
odbccon = new OleDbConnection(context);
return odbccon;
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;namespace Utility
{
public class DBHelper
{
public static SqlConnection _sqlCon; public static SqlConnection SqlCon
{
get
{
//判断sql的装态
if (_sqlCon == null)
{
_sqlCon = new SqlConnection(
ConfigurationManager.ConnectionStrings["con"].ConnectionString);
}
if (_sqlCon.State == ConnectionState.Closed)
{
_sqlCon.Open();
}
if (_sqlCon.State == ConnectionState.Broken)
{
_sqlCon.Close();
_sqlCon.Open();
}
return DBHelper._sqlCon;
}
}
/// <summary>
///
/// </summary>
/// <param name="strSql"></param>
/// <param name="sqlPar"></param>
/// <returns></returns>
public static SqlDataReader ExecuteDataReader(string strSql, SqlParameter[] sqlPar)
{
SqlCommand sqlCommand = new SqlCommand(strSql, SqlCon);
if (sqlPar != null)
sqlCommand.Parameters.AddRange(sqlPar); //把绑定的参数添加到数组中
return sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
/// <summary>
/// 执行增删改时选择的命令
/// </summary>
/// <param name="strSql">执行的sql语句</param>
/// <param name="sqlPar">绑定的参数</param>
/// <returns></returns>
public static int ExecuteNonQuery(string strSql, SqlParameter[] sqlPar)
{
int result = 0;
SqlCommand sqlCommand = new SqlCommand(strSql, SqlCon);
if (sqlPar != null)
//把绑定的参数添加到数组中
sqlCommand.Parameters.AddRange(sqlPar);
result = sqlCommand.ExecuteNonQuery();
_sqlCon.Close();
return result;
}
/// <summary>
/// 返回一个dataset类型的方法
/// </summary>
/// <param name="strSql"></param>
/// <param name="sqlPar"></param>
/// <param name="tableName"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet(string strSql, SqlParameter[] sqlPar, string tableName)
{
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = new SqlCommand(strSql, SqlCon);
if (sqlPar != null)
{
//把绑定的参数添加到数组中
sqlDataAdapter.SelectCommand.Parameters.AddRange(sqlPar);
}
DataSet dataSet = new DataSet();
sqlDataAdapter.Fill(dataSet, tableName);
return dataSet;
} /// <summary>
/// Linq学习新增的方法,读取一张或多张表
/// </summary>
/// <param name="strSql"></param>
/// <param name="sqlPar"></param>
/// <returns></returns>
public static DataSet ExecuteDataSets(string strSql, SqlParameter[] sqlPar)
{
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = new SqlCommand(strSql, SqlCon);
if (sqlPar != null)
{
//把绑定的参数添加到数组中
sqlDataAdapter.SelectCommand.Parameters.AddRange(sqlPar);
}
DataSet dataSet = new DataSet();
sqlDataAdapter.Fill(dataSet); //唯一修改的只是去掉了tableName,原语句(sqlDataAdapter.Fill(dataSet, tableName);)
return dataSet;
}
/// <summary>
/// 返回一个dataset类型的存储过程
/// </summary>
/// <param name="procName">存储过程的名字</param>
/// <param name="sqlPar">sql语句</param>
/// <param name="tableName">表的名字</param>
/// <returns></returns>
public static DataSet ExecuteDataSetByProc(string procName, SqlParameter[] sqlPar, string tableName)
{
SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
sqlDataAdapter.SelectCommand = new SqlCommand();
sqlDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
sqlDataAdapter.SelectCommand.CommandText = procName;
sqlDataAdapter.SelectCommand.Connection = SqlCon;
if (sqlPar != null)
sqlDataAdapter.SelectCommand.Parameters.AddRange(sqlPar);
DataSet dataSet = new DataSet();
sqlDataAdapter.Fill(dataSet, tableName);
return dataSet;
}
/// <summary>
/// 执行查询,并返回第一行第一列
/// </summary>
/// <param name="strSql"> sql语句</param>
/// <returns></returns>
public static int ExecuteScalar(string strSql)
{
SqlCommand sqlCommand = new SqlCommand(strSql, SqlCon);
int result = Convert.ToInt32(sqlCommand.ExecuteScalar());
_sqlCon.Close();
return result;
}
/// <summary>
/// 返回一个datatable类型的方法
/// </summary>
/// <param name="strSql"> 数据库的sql语句</param>
/// <param name="sqlPar">需要绑定的参数</param>
/// <returns></returns>
public static DataTable DataTable(string strSql, SqlParameter[] sqlPar)
{
SqlDataAdapter sda = new SqlDataAdapter(strSql, SqlCon);
if (sqlPar != null)
sda.SelectCommand.Parameters.AddRange(sqlPar);
DataTable dt = new DataTable();
sda.Fill(dt);
return dt;
}
}
}
--------
或者在DBhelper里面写下面的这句话
private static string conStr = @"Persist Security Info=False;Integrated Security=true;Initial Catalog=数据库名;";========这两句都是连接数据库的!
用.NET操作数据库就离不开ADO.NET.
这个建议LZ把ADO.NET 好好学学....这个就几个类,很容易学的
public string CnString = "server=localhost;Initial Catalog=danger; Integrated Security=True "; //
public int Exc(string cmdstr)
{
int tmp = 0;
SqlConnection cns = new SqlConnection();
cns.ConnectionString = CnString;
// cns.ConnectionString = ConnectionString;
cns.Open();
if (cns.State == ConnectionState.Open)
{
SqlCommand mycmd = new SqlCommand();
mycmd.Connection = cns;
mycmd.CommandText = cmdstr;
tmp = mycmd.ExecuteNonQuery();
mycmd.Dispose();
}
cns.Close();
cns.Dispose();
return tmp;
}