Question

c#与数据库的连接程序 要后台连接的

Answer 1

public class SqlDbHelper
{
public static SqlConnection Conn
{
get
{
return new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["mssql"].ConnectionString);
}
}
public static SqlDataReader GetReader(string sql)
{
SqlCommand cmd = new SqlCommand(sql, Conn);
cmd.Connection.Open();
try
{
return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
catch
{
cmd.Connection.Close();
throw;
}
}
public static DataTable GetTable(string sql)
{
SqlConnection sqlcon = Conn;
SqlDataAdapter da = new SqlDataAdapter(sql, sqlcon);
DataTable dt = new DataTable();
try
{
sqlcon.Open();
da.Fill(dt);
sqlcon.Close();
return dt;
}
catch
{
sqlcon.Close();
throw;
}
}
public static int ExecuteNonQuery(string sql)
{
SqlCommand cmd = new SqlCommand(sql, Conn);
cmd.Connection.Open();
try
{
return cmd.ExecuteNonQuery();
}
finally
{
cmd.Connection.Close();
}
}
public static object ExecuteScalar(string sql)
{
SqlCommand cmd = new SqlCommand(sql, Conn);
cmd.Connection.Open();
try
{
return cmd.ExecuteScalar();
}
finally
{
cmd.Connection.Close();
}
}
private static int GetPageRecord(string sql)
{
sql = System.Text.RegularExpressions.Regex.Replace(sql, "ORDER BY.*", "");
if (sql.Contains("rollWordage") || sql.Contains("imgInfo"))
{
sql = System.Text.RegularExpressions.Regex.Replace(sql, "as.*", "");
}
sql = "select count(*) from (" + sql + ") as temp";
SqlCommand cmd = new SqlCommand(sql, Conn);
cmd.Connection.Open();
int recordcount = (int)cmd.ExecuteScalar();
return recordcount;
}
public static DataSet GetPage(string sql, int currentPage, int pagesize, out int recordcount)
{
SqlDataAdapter ada = new SqlDataAdapter(sql, Conn);
DataSet ds = new DataSet();
int startRow = (currentPage - 1) * pagesize;
ada.Fill(ds, startRow, pagesize, "table");
recordcount = GetPageRecord(sql);
return ds;
}
}

这是与Sql Server数据库连接的一个类，通用的，我平常用的也是这个，包括分页什么的。

Answer 2

class DBHelper
{
private static SqlCommand cmd = null;
private static SqlDataReader dr = null;

public int RowCount { get; private set; }

SqlConnection sqlCnn = new SqlConnection();
SqlCommand sqlCmd = new SqlCommand();

//数据库连接字符串（自己设置是否通过设置文件读取）
// private static string connectionString = "Server = 127.0.0.1; Database = visitinfo; Trusted_Connection=true";
private static string connectionString = System.Configuration.ConfigurationManager.AppSettings["sqlconn"].ToString();

//数据库连接Connection对象
public static SqlConnection connection = new SqlConnection(connectionString);

public DBHelper()
{ }

#region 返回结果集

public static SqlDataReader GetResult(string sql)
{
try
{
cmd = new SqlCommand();
cmd.CommandText = sql;
cmd.Connection = connection;
cmd.Connection.Open();
dr = cmd.ExecuteReader();
return dr;
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message);
return null;
}
finally
{
//dr.Close();
//cmd.Connection.Close();
}
}

#endregion

#region 对Select语句,返回int型结果集

public static int GetSqlResult(string sql)
{
try
{
cmd = new SqlCommand();
cmd.CommandText = sql;
cmd.Connection = connection;
cmd.Connection.Open();

int a = (int)cmd.ExecuteScalar();
return a;
}
catch (Exception ex)
{
//MessageBox.Show(ex.Message);
return -1;

}
finally
{
cmd.Connection.Close();
}
}

#endregion

public static DataSet GetDS(string sql)
{
SqlConnection conn = new SqlConnection(connectionString);
try
{
// SqlConnection conn = new SqlConnection(sqlconn);
SqlDataAdapter da = new SqlDataAdapter(sql, connectionString);

DataSet ds = new DataSet();
da.Fill(ds);
return ds;

}
catch (Exception ex)
{
return null;
}
finally
{
//dr.Close();
conn.Close();

}
}

#region 对Update,Insert和Delete语句，返回该命令所影响的行数

public static int GetDsqlResult(string sql)
{
try
{
cmd = new SqlCommand();
cmd.CommandText = sql;
cmd.Connection = connection;
cmd.Connection.Open();

cmd.ExecuteNonQuery();
return 1;
}
catch (Exception ex)
{
return -1;
}
finally
{
cmd.Connection.Close();
}
}

#endregion

}
}

Answer 3

string strSql = "server=.;database=xxx;uid=sa;pwd=sa";
SqlConnection con = new SqlConnection (strSql);
con.open();

Answer 4

上面已经很全面了

Answer 5

对的