public static SqlConnection getSqlConn()
{
string sqlconstr = “SqlConStr = "server=119.119.119.119,1888;uid=sa;pwd=123456;database=Word”;
SqlConnection sqlconn = new SqlConnection(sqlconstr);
return sqlconn;
}
/// <summary>
/// 通过SQL语句取得相应的DataSet数据对象
/// </summary>
/// <param name="sqlString"></param>
/// <returns></returns>
public static DataSet Query(string sqlString)
{
LogUtil.show(sqlString);
DataSet ds = new DataSet();
using (SqlConnection sqlconn = getSqlConn())
{
sqlconn.Open();
SqlCommand cmd = new SqlCommand(sqlString, sqlconn);
try
{
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(ds);
return ds;
}
catch (System.Exception ex)
{
throw new Exception(ex.Message);
}
finally
{
sqlconn.Close();
}
}
}
/// <summary>增加操作
/// 往UnitWord表中插入Word与Unit的关联信息
/// insert into UnitWord(UnitId, WordId, vocCode)values(36,247640,'53983D47-AD7A-457A-8FA5-D7ED177BFD38')
/// </summary>
/// <param name="unitWord"></param>
/// <returns></returns>
public static bool insertUnitWord(UnitWord unitWord) {
SqlConnection conn = getSqlConn();
conn.Open();
string sql = "insert into UnitWord(UnitId, WordId, vocCode)values("+unitWord.UnitId+","+unitWord.WordId+",'"+unitWord.vocCode+"')";
LogUtil.show(sql);
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
int result = cmd.ExecuteNonQuery();
if (result == 1)
{
return true;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.StackTrace);
Console.WriteLine(ex.Message);
return false;
}
finally
{
cmd.Dispose();
conn.Close();
}
return false;
}
/// <summary>删除操作
/// 通过单词ID删除单词及其关联
/// </summary>
/// <param name="Id"></param>
/// <returns></returns>
public static bool deleteWord(int Id) {
int result = 0;
SqlConnection conn = getSqlConn();
conn.Open();
string sql1 = "delete from Word where Id="+Id;
string sql2 = "delete from UnitWord where WordId=" + Id;
SqlCommand cmd1 = new SqlCommand(sql1, conn);
SqlCommand cmd2 = new SqlCommand(sql2, conn);
try
{
result += cmd1.ExecuteNonQuery();
result += cmd2.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine(ex.StackTrace);
Console.WriteLine(ex.Message);
return false;
}
finally
{
cmd1.Dispose();
cmd2.Dispose();
conn.Close();
}
if (result > 1)
{
return true;
}
else {
return false;
}
}
/// 通过vocCode更新一个单词
/// </summary>
/// <param name="mode"></param>
/// <param name="value"></param>
/// <param name="vocCode"></param>
/// <returns></returns>
public static Boolean updateWord(string mode,string oldSpelling,string value,string vocCode)
{
SqlConnection conn = getSqlConn();
conn.Open();
string sql=“ update Word set spelling='notes' where vocCode='8A108CB7-4C56-483D-014C-77F9C7E05F41' and spelling='note'”;
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
int num = cmd.ExecuteNonQuery();
if (num > 0)
{
return true;
}
else {
return false;
}
}
catch (Exception ex)
{
Console.WriteLine(ex.StackTrace);
Console.WriteLine(ex.Message);
return false;
}
finally
{
conn.Close();
}
}
/// <summary>查询操作
/// 通过vocCode取得一个单词/// </summary>
/// <param name="vocCode"></param>
/// <returns></returns>
public static Word getWordByVocCode(string vocCode) {
Word word = new Word();
SqlConnection conn = getSqlConn();
conn.Open();
string sql = "select Id,vocCode,spelling,meaning,soundMarkUs from Word where vocCode='" + vocCode + "'";
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
word.Id = reader.GetInt32(reader.GetOrdinal("Id"));
word.vocCode = reader.GetGuid(reader.GetOrdinal("vocCode")).ToString();
word.spelling = reader.GetString(reader.GetOrdinal("spelling")).ToString();
word.meaning = reader.GetString(reader.GetOrdinal("meaning")).ToString();
word.soundMarkUs = reader.GetString(reader.GetOrdinal("soundMarkUs")).ToString();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.StackTrace);
Console.WriteLine(ex.Message);
return null;
}
finally
{
cmd.Dispose();
conn.Close();
}
return word;
}