public class DbOperation
{
private static string connectionString = ConfigurationManager.AppSettings["Connection"];
private static SqlDataAdapter myAdapter = new SqlDataAdapter();
/// <summary>
/// Initializes a new instance of the <see cref="DbOperation"/> class.
/// </summary>
public DbOperation()
{
}
/// <summary>
/// 查找柜号，层号，通道号是否已经被占用.
/// </summary>
/// <param name="oldNum">旧柜号.</param>
/// <param name="oldFloor">旧层数.</param>
/// <param name="oldAisle">旧通道号.</param>
/// <returns>如果是0则没被占用.</returns>
public int SelectCabinet(int oldNum, int oldFloor, int oldAisle)
{
int i = 0;
SqlConnection myConnection = new SqlConnection(connectionString);
string queryString = "select * from cabinet where CAB_NUM = @num and CAB_FLOOR = @floor and CAB_AISLE= @aisle";
try
{
myConnection.Open();
SqlCommand myCommand = new SqlCommand(queryString, myConnection);
SqlParameter[] sqlParameters =
{
new SqlParameter("@num", oldNum),
new SqlParameter("@floor", oldFloor),
new SqlParameter("@aisle", oldAisle),
};
myCommand.Parameters.AddRange(sqlParameters);
SqlDataReader ret = myCommand.ExecuteReader();
// myCommand.ExecuteNonQuery
if (ret.Read())
{
MessageBox.Show("该柜号，层号，通道号已被占用，请重新输入！！！");
return 1;
}
else
{
return 0;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
myConnection.Close();
}
return i;
}
/// <summary>
/// 通过柜号,判断是修改或者添加的柜号和柜名称 与现有的柜号和柜名称是否冲突.
/// </summary>
/// <param name="num">柜号.</param>
/// <param name="name">柜名.</param>
/// <returns> 如果是0则没被占用.</returns>
public int SelectName(int cabNum, string cabName)
{
int i = 0;
string nameString = null;
SqlConnection myConnection = new SqlConnection(connectionString);
string queryString = "select CAB_NAME from cabinet where CAB_NUM = @num ";
try
{
myConnection.Open();
SqlCommand myCommand = new SqlCommand(queryString, myConnection);
myCommand.Parameters.Add(new SqlParameter("@num", SqlDbType.Int, 10));
myCommand.Parameters["@num"].Value = cabNum;
SqlDataReader reader = myCommand.ExecuteReader();
if (reader.Read())
{
nameString = reader.GetValue(0).ToString().Trim();
}
// 不存在柜名，直接添加
if (nameString == null)
{
i = 0;
}
// 存在 判断名字是否一致
else
{
// 判断查到的柜号对应的name与输入的name是否一致
if (cabName.Equals(nameString))
{
i = 0; // 添加或者编辑后的柜号与柜名称与原来一致，则添加
}
else
{
i = 1; // 添加或者编辑后的柜号与柜名称与原来不一致，不添加
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
myConnection.Close();
}
return i;
}
/// <summary>
/// 通过柜名,判断是修改或者添加的柜号和柜名称 与现有的柜号和柜名称是否冲突.
/// </summary>
/// <param name="num">柜号.</param>
/// <param name="name">柜名.</param>
/// <returns> 如果是0则没被占用.</returns>
public int SelectNum(int cabNum, string cabName)
{
int i = 0;
int num1 = 0;
SqlConnection myConnection = new SqlConnection(connectionString);
string queryString = "select CAB_NUM from cabinet where CAB_NAME = @name ";
try
{
myConnection.Open();
SqlCommand myCommand = new SqlCommand(queryString, myConnection);
myCommand.Parameters.Add("@name", SqlDbType.VarChar, 50);
myCommand.Parameters["@name"].Value = cabName;
SqlDataReader reader = myCommand.ExecuteReader();
if (reader.Read())
{
int.TryParse(reader.GetValue(0).ToString().Trim(), out int numString);
num1 = numString;
}
// 不存在柜号，直接添加
if (num1 == 0)
{
i = 0;
}
// 存在 判断柜号是否一致
else
{
// 判断查到的柜号对应的num与输入的num是否一致
if (cabNum.Equals(num1))
{
i = 0; // 添加或者编辑后的柜号与柜名称与原来一致，则添加
}
else
{
i = 1; // 添加或者编辑后的柜号与柜名称与原来不一致，不添加
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
myConnection.Close();
}
return i;
}
public bool SelectNameAndNum(int cabNum, string cabName)
{
SqlConnection myConnection = new SqlConnection(connectionString);
List<Cabinet> cabinetsList = new List<Cabinet>(10);
string queryString = "select distinct CAB_NUM,CAB_NAME from cabinet where CAB_NUM = @num or CAB_NAME = @name ";
try
{
myConnection.Open();
SqlCommand myCommand = new SqlCommand(queryString, myConnection);
SqlParameter[] sqlParameters =
{
new SqlParameter("@num", cabNum),
new SqlParameter("@name", cabName),
};
myCommand.Parameters.AddRange(sqlParameters);
SqlDataReader cabinetReader = myCommand.ExecuteReader();
// 返回true则是可以直接添加, false代表不可添加
if (cabinetReader.HasRows)
{
while (cabinetReader.Read())
{
Cabinet cabinet = new Cabinet();
cabinet.Num = cabinetReader.GetInt32(0);
cabinet.Name = cabinetReader.GetString(1);
cabinetsList.Add(cabinet);
}
var result1 = (from n in cabinetsList
where n.Num == cabNum
select n).FirstOrDefault();
var result2 = (from n in cabinetsList
where n.Name == cabName
select n).FirstOrDefault();
// 如果有一个不存在，则返回false
if (cabName.Equals(result1?.Name) || cabNum.Equals(result2?.Num))
{
return true;
}
return false;
}
return true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return false;
}
finally
{
myConnection.Close();
}
}
// 添加数据
public int AddCabinet(int cabNum, string cabName, int cabFloor, int cabAisle)
{
SqlConnection conn = new SqlConnection(connectionString);
int i = 0;
try
{
conn.Open();
string sql = "insert into cabinet(CAB_NUM,CAB_NAME,CAB_FLOOR,CAB_AISLE) values(@num,@name,@floor,@aisle)";
SqlCommand insertCommand = new SqlCommand(sql, conn);
SqlParameter[] sqlParameter =
{
new SqlParameter("@num", cabNum),
new SqlParameter("@name", cabName),
new SqlParameter("@floor", cabFloor),
new SqlParameter("@aisle", cabAisle),
};
insertCommand.Parameters.AddRange(sqlParameter);
i = insertCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show("插入失败 " + ex.Message);
}
finally
{
conn.Close();
}
return i;
}
// 删除选定行数据
public bool DeleteCabinet(int cabNum, int cabFloor, int cabAisle)
{
SqlConnection conn = new SqlConnection(connectionString);
int i = 0;
try
{
conn.Open();
string sql = "delete from cabinet where CAB_NUM = @num and CAB_FLOOR = @floor and CAB_AISLE= @aisle";
SqlCommand deleteCommand = new SqlCommand(sql, conn);
SqlParameter[] sqlParameters =
{
new SqlParameter("@num", cabNum),
new SqlParameter("@floor", cabFloor),
new SqlParameter("@aisle", cabAisle),
};
deleteCommand.Parameters.AddRange(sqlParameters);
i = deleteCommand.ExecuteNonQuery();
if (i > 0)
{
return true;
}
return false;
}
catch (Exception)
{
return false;
}
finally
{
conn.Close();
}
}
// 编辑数据
public int UpdateData(Cabinet cabinetOld, Cabinet cabinetNew)
{
SqlConnection conn = new SqlConnection(connectionString);
int i = 0;
try
{
conn.Open();
string sql = "update cabinet set CAB_NUM = @newNum ,CAB_NAME = @newName,CAB_FLOOR = @newFloor,CAB_AISLE = @newAisle" +
" where CAB_NUM = @oldNum and CAB_FLOOR = @oldFloor and CAB_AISLE= @oldAisle";
SqlCommand updateCommand = new SqlCommand(sql, conn);
SqlParameter[] sqlParameters =
{
new SqlParameter("@newNum", cabinetNew.Num),
new SqlParameter("@newName", cabinetNew.Name),
new SqlParameter("@newFloor", cabinetNew.Floor),
new SqlParameter("@newAisle", cabinetNew.Aisle),
new SqlParameter("@oldNum", cabinetOld.Num),
new SqlParameter("@oldFloor", cabinetOld.Floor),
new SqlParameter("@oldAisle", cabinetOld.Aisle),
};
updateCommand.Parameters.AddRange(sqlParameters);
i = updateCommand.ExecuteNonQuery();
}
catch (Exception)
{
}
finally
{
conn.Close();
}
return i;
}
// 查询表中所有数据
public DataSet GetQueryCabinet()
{
SqlConnection myConnection = new SqlConnection(connectionString);
string queryString = "select * from cabinet order by CAB_NAME asc";
DataSet myDataSet = new DataSet();
try
{
myConnection.Open();
SqlCommand myCommand = new SqlCommand(queryString, myConnection);
myAdapter.SelectCommand = myCommand;
myAdapter.Fill(myDataSet, "cabinet");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
myConnection.Close();
}
return myDataSet;
}
// 批量删除
public bool BatchDelete(List<Cabinet> cabinetList)
{
int i;
List<Cabinet> cabinetDeleteList = cabinetList;
for (i = 0; i < cabinetDeleteList.Count; i++)
{
if (this.DeleteCabinet(cabinetDeleteList[i].Num, cabinetDeleteList[i].Floor, cabinetDeleteList[i].Aisle))
{
continue;
}
else
{
MessageBox.Show("数据出错!");
}
}
if (i == cabinetDeleteList.Count)
{
return true;
}
return false;
}
}