110,533
社区成员
发帖
与我相关
我的任务
分享
public class SqlHelper
{
public SqlHelper()
{
//
//TODO: 在此处添加构造函数逻辑
//
}
/// <summary>
/// 连接数据库
/// </summary>
/// <param name="server"></param>
/// <param name="database"></param>
/// <param name="username"></param>
/// <param name="password"></param>
/// <returns></returns>
public static string GetConnectString(string server, string database, string username, string password)
{
return "Server=" + server + ";Database=" + database + ";UID=" + username + ";PWD=" + password;
}
}
SqlHelper sqlHelper = null;
string _ConnectionStr = String.Empty;
SqlConnection sqlConnection;
SqlCommand sqlCommand;
SqlDataAdapter sqlDataAdapter;
public Service()
{
//如果使用设计的组件,请取消注释以下行
//InitializeComponent();
_ConnectionStr = SqlHelper.GetConnectString("ch2", "GGG", "用户名", "密码");
sqlHelper = new SqlHelper();
}
public int IsExist(int CustomerID, string BarcodeNO)
{
try
{
sqlConnection = new SqlConnection(_ConnectionStr);
sqlConnection.Open();
}
catch (Exception ex)
{
return 0;
}
string sql;
sql = "select CB_ID from Tab_CustomerBarcode where CB_CustomerID=@CustomerID and CB_BarcodeNO=@BarcodeNO";
SqlCommand cmd = new SqlCommand(sql, sqlConnection);
cmd.Parameters.Add(new SqlParameter("@CustomerID", SqlDbType.Int, 4));
cmd.Parameters["@CustomerID"].Value = CustomerID;
cmd.Parameters.Add(new SqlParameter("@BarcodeNO", SqlDbType.VarChar, 50));
cmd.Parameters["@BarcodeNO"].Value = BarcodeNO;
if (cmd.ExecuteScalar() != null)//存在CustomerID和BarcodeNO同时相同,则不执行插入
{
return 1;
}
return 0;
}
[WebMethod]
public int AddGpsBarcode(int CustomerID, string BarcodeNO, int Type)
{
if (this.IsExist(CustomerID, BarcodeNO) == 1)
{
sqlConnection.Close();
return 0;//存在CustomerID和BarcodeNO同时相同,返回0
}
else
{
try
{
string sqlStr = "insert into Tab_CustomerBarcode(CB_ID,CB_CustomerID,CB_BarcodeNO,CB_Type,CB_State,CB_UpdateTime) values (@newGuid,@CustomerID,@BarcodeNO,@Type,0,@UpdateTime)";
sqlCommand = new SqlCommand(sqlStr, sqlConnection);
sqlCommand.Parameters.Add(new SqlParameter("@newGuid", SqlDbType.UniqueIdentifier, 16));
sqlCommand.Parameters["@newGuid"].Value = Guid.NewGuid();
sqlCommand.Parameters.Add(new SqlParameter("@CustomerID", SqlDbType.Int, 4));
sqlCommand.Parameters["@CustomerID"].Value = CustomerID;
sqlCommand.Parameters.Add(new SqlParameter("@BarcodeNO", SqlDbType.VarChar, 50));
sqlCommand.Parameters["@BarcodeNO"].Value = BarcodeNO;
sqlCommand.Parameters.Add(new SqlParameter("@Type", SqlDbType.Int, 4));
sqlCommand.Parameters["@Type"].Value = Type;
sqlCommand.Parameters.Add(new SqlParameter("@UpdateTime", SqlDbType.DateTime, 8));
sqlCommand.Parameters["@UpdateTime"].Value = DateTime.Now;
return sqlCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlCommand != null)
{
sqlCommand.Dispose();
}
sqlConnection.Close();
}
}
}
[WebMethod]
public DataTable QueryGpsBarcodeByState(int CustomerID, int State, DateTime UpdateStartTime, DateTime UpdateEndTime)
{
try
{
sqlConnection = new SqlConnection(_ConnectionStr);
sqlConnection.Open();
}
catch (Exception ex)
{
throw ex;
}
try
{
DataTable dt = new DataTable();
string sqlStr = "select CB_CustomerID,CB_BarcodeNO,CB_VehicleNO,CB_Position,CB_Type,CB_State,CB_UpdateTime from Tab_CustomerBarcode where CB_CustomerID=@CustomerID and CB_UpdateTime between @UpdateStartTime and @UpdateEndTime and CB_State=@State ";
sqlCommand = new SqlCommand(sqlStr, sqlConnection);
sqlCommand.Parameters.Add(new SqlParameter("@CustomerID", SqlDbType.Int, 4));
sqlCommand.Parameters["@CustomerID"].Value = CustomerID;
sqlCommand.Parameters.Add(new SqlParameter("@State", SqlDbType.Int, 4));
sqlCommand.Parameters["@State"].Value = State;
sqlCommand.Parameters.Add(new SqlParameter("@UpdateStartTime", SqlDbType.DateTime, 8));
sqlCommand.Parameters["@UpdateStartTime"].Value = UpdateStartTime;
sqlCommand.Parameters.Add(new SqlParameter("@UpdateEndTime", SqlDbType.DateTime, 8));
sqlCommand.Parameters["@UpdateEndTime"].Value = UpdateEndTime;
sqlCommand.ExecuteNonQuery();
sqlDataAdapter = new SqlDataAdapter(sqlCommand);
sqlDataAdapter.Fill(dt);
dt.TableName = "MyTable";//否则报错---无法序列化 DataTable。未设置 DataTable 名称
return dt;
}
catch (Exception ex)
{
return null;
throw ex;
}
finally
{
sqlDataAdapter.Dispose();
sqlCommand.Dispose();
sqlConnection.Close();
}
}
[WebMethod]
public int UpdateGpsBarcodeState(int CustomerID, string BarcodeNO, int State)
{
if (this.IsExist(CustomerID, BarcodeNO) == 0)
{
sqlConnection.Close();
return 0;//不存在输入的CustomerID和BarcodeNO则返回0
}
try
{
string sqlStr = "update Tab_CustomerBarcode set CB_State=@State ,CB_UpdateTime=@UpdateTime where CB_CustomerID=@CustomerID and CB_BarcodeNO=@BarcodeNO";
sqlCommand = new SqlCommand(sqlStr, sqlConnection);
sqlCommand.Parameters.Add(new SqlParameter("@CustomerID", SqlDbType.Int, 4));
sqlCommand.Parameters["@CustomerID"].Value = CustomerID;
sqlCommand.Parameters.Add(new SqlParameter("@BarcodeNO", SqlDbType.VarChar, 50));
sqlCommand.Parameters["@BarcodeNO"].Value = BarcodeNO;
//sqlCommand.Parameters.AddWithValue(@BarcodeNO, BarcodeNO);也可(string)
sqlCommand.Parameters.Add(new SqlParameter("@State", SqlDbType.Int, 4));
sqlCommand.Parameters["@State"].Value = State;
sqlCommand.Parameters.Add(new SqlParameter("@UpdateTime", SqlDbType.DateTime, 8));
sqlCommand.Parameters["@UpdateTime"].Value = DateTime.Now;
return sqlCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlCommand.Dispose();
sqlConnection.Close();
}
}