DbHelper-SQL数据库访问助手

using System.Data;

using System.Data.SqlClient;

namespace Whir.Software.Framework.Ultimate

{

/// <summary>

///     数据库访问助手

/// </summary>

public class DbHelper

{

#region 判断连接是否成功

/// <summary>

///     判断连接是否成功！

/// </summary>

/// <param name="con"> 链接字符串</param>

/// <returns>true 表示链接成功，false表示连接失败</returns>

public static bool IsConnected(string con)

{

bool flag;

var conn = new SqlConnection(con);

try

{

conn.Open();

flag = true;

}

catch (Exception)

{

flag = false;

}

finally

{

conn.Close();

}

return flag;

}

#endregion

#region 执行不带参数sql语句

/// <summary>

///     执行不带参数sql语句

/// </summary>

/// <param name="sql">增，删，改sql语句</param>

/// <param name="con"></param>

/// <returns>返回所影响的行数</returns>

public static bool Execute(string sql, string con)

{

var cmd = new SqlCommand();

var connection = new SqlConnection(con);

try

{

using (connection)

{

cmd.Connection = connection;

cmd.CommandText = sql;

connection.Open();

cmd.ExecuteNonQuery();

return true;

}

}

catch (Exception)

{

return false;

}

}

#endregion

#region 执行SQL语句返回DataTable

/// <summary>

///     执行SQL语句返回DataTable

/// </summary>

/// <param name="sql"></param>

/// <param name="con"></param>

/// <returns></returns>

public static DataTable ExcuteDataTable(string sql, string con)

{

var cmd = new SqlCommand();

var connection = new SqlConnection(con);

try

{

using (connection)

{

cmd.Connection = connection;

cmd.CommandText = sql;

connection.Open();

var da = new SqlDataAdapter(cmd);

var ds = new DataSet();

da.Fill(ds);

return ds.Tables[0];

}

}

catch (Exception ex)

{

var dt = new DataTable();

dt.Columns.Add("异常信息");

DataRow row = dt.NewRow();

row["异常信息"] = ex.Message;

dt.Rows.Add(row);

return dt;

}

}

#endregion

#region 执行SQL语句查询单条记录

/// <summary>

///     执行SQL语句查询单条记录

/// </summary>

/// <param name="sql"></param>

/// <param name="con"></param>

/// <returns></returns>

public static object ExecuteScalar(string sql, string con)

{

var cmd = new SqlCommand();

var connection = new SqlConnection(con);

try

{

using (connection)

{

cmd.Connection = connection;

cmd.CommandText = sql;

connection.Open();

return cmd.ExecuteScalar();

}

}

catch (Exception)

{

return string.Empty;

}

}

#endregion

#region 取得表最大Id

/// <summary>

///     取得表最大Id

/// </summary>

/// <param name="tableName"></param>

/// <param name="fieldName"></param>

/// <param name="con"></param>

/// <returns></returns>

public static int GetMaxId(string tableName, string fieldName, string con)

{

string sql = "SELECT NVL(MAX({0}),0)+1 FROM {1}";

try

{

sql = string.Format(sql, fieldName, tableName);

int result;

Int32.TryParse(ExecuteScalar(sql, con).ToString(), out result);

return result;

}

catch (Exception)

{

return -1;

}

}

#endregion

}

}