public IList <Shreet> GetAllShreet(string sql, SqlParameter[] paras)
{
IList <Shreet> list = null;
DataTable table = SQLDBHelper.GetDataTable(sql, paras);
if (table != null)
{
if (table.Rows.Count > 0)
{
list = new List <Shreet>();
foreach (DataRow row in table.Rows)
{
Shreet shreet = new Shreet();
shreet.ShreetId = int.Parse(row["shreetId"].ToString());
shreet.ShreetName = row["shreetName"].ToString();
shreet.DistrictId = int.Parse(row["districtId"].ToString());
if (ds.GetDistrictByID(shreet.DistrictId) != null)
{
shreet.District = ds.GetDistrictByID(shreet.DistrictId)[0];
}
list.Add(shreet);
}
}
}
return(list);
}
public static string AutoCreatID(string TableName, string FieldName)
{
string dbDateTime = DateTime.Now.ToString("yyyyMMdd");
string Str = "select max(" + FieldName + ") as id from " + TableName; //查询表中的字段
object obj = SQLDBHelper.GetSingle(Str); //把查询字符串Str放到GetSingle()中执行查询(Command)并返回查询结果（对象）
string maxID = "";
if (obj != null)//返回（查询字符串）对象
{
maxID = SQLDBHelper.GetSingle(Str).ToString();
}
string Result = "";
if (maxID == "") //没有最大编号
{
Result = dbDateTime + "0001"; //CG200902250001
}
else
{
//截取字符
//string strFirstEight = maxID.Substring(4, 8);
string strLastFour = maxID.Substring(8, 4);
//if (dbDateTime == strFirstEight)//截取的最大编号（20090225）是否和数据库服务器系统时间相等
//{
string strNewFour = (Convert.ToInt32(strLastFour) + 1).ToString("0000"); //0000+1
Result = dbDateTime + strNewFour; //CG200902250001
//}
//else
//{
// Result = dbDateTime + "0001";
//}
}
return(Result);
}
public IList <District> GetAllDistrict(string sql, SqlParameter[] paras)
{
IList <District> list = null;
DataTable table = SQLDBHelper.GetDataTable(sql, paras);
if (table != null)
{
if (table.Rows.Count > 0)
{
list = new List <District>();
foreach (DataRow row in table.Rows)
{
District district = new District();
district.DistrictId = int.Parse(row["districtId"].ToString());
district.DistrictName = row["districtName"].ToString();
district.CityId = int.Parse(row["cityId"].ToString());
if (cs.GetCityByID(district.CityId) != null)
{
district.City = cs.GetCityByID(district.CityId)[0];
}
list.Add(district);
}
}
}
return(list);
}
public DataTable GetShreetByDistrictIdTwo(int districtId)
{
string sql = "SELECT * FROM Shreet where DistrictId=@DistrictId";
SqlParameter[] parameters =
{
new SqlParameter("@DistrictId", SqlDbType.Int, 4)
};
parameters[0].Value = districtId;
return(SQLDBHelper.GetDataTable(sql, parameters));
}
public DataTable GetShreetByKeyNameTooo(string KeyName)
{
string sql = "SELECT * FROM Shreet where ShreetName like '%" + KeyName + "%'";
SqlParameter[] parameters =
{
new SqlParameter("@ShreetName", SqlDbType.VarChar, 20)
};
parameters[0].Value = KeyName;
return(SQLDBHelper.GetDataTable(sql, parameters));
}
public DataTable GetDistrictByCityIDTwo(int cityId)
{
string sql = "SELECT * FROM District where CityId=@CityId";
SqlParameter[] parameters =
{
new SqlParameter("@CityId", SqlDbType.Int, 4)
};
parameters[0].Value = cityId;
return(SQLDBHelper.GetDataTable(sql, parameters));
}
/// <summary>
/// 删除一条数据
/// </summary>
public bool Delete(int ShreetId)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from Shreet ");
strSql.Append(" where ShreetId=@ShreetId ");
SqlParameter[] parameters =
{
new SqlParameter("@ShreetId", SqlDbType.Int, 4)
};
parameters[0].Value = ShreetId;
return(SQLDBHelper.ExcuteSql(strSql.ToString(), parameters));
}
public static int GetMaxID(string FieldName, string TableName)
{
string strsql = "select max(" + FieldName + ")+1 from " + TableName;
object obj = SQLDBHelper.GetSingle(strsql);
if (obj == null)
{
return(1);
}
else
{
return(int.Parse(obj.ToString()));
}
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(City model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update City set ");
strSql.Append("CityName=@CityName ");
strSql.Append(" where CityId=@CityId ");
SqlParameter[] parameters =
{
new SqlParameter("@CityName", SqlDbType.VarChar, 50),
new SqlParameter("@CityId", SqlDbType.Int, 4)
};
parameters[0].Value = model.CityName;
parameters[1].Value = model.CityId;
return(SQLDBHelper.ExcuteSql(strSql.ToString(), parameters));
}
/// <summary>
/// 增加一条数据
/// </summary>
public bool Add(City model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into City(");
strSql.Append("CityName)");
strSql.Append(" values (");
strSql.Append("@CityName)");
SqlParameter[] parameters =
{
new SqlParameter("@CityName", SqlDbType.VarChar, 50)
};
parameters[0].Value = model.CityName;
return(SQLDBHelper.ExcuteSql(strSql.ToString(), parameters));
}
/// <summary>
/// 增加一条数据
/// </summary>
public bool Add(Shreet model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into Shreet(");
strSql.Append("ShreetName,DistrictId)");
strSql.Append(" values (");
strSql.Append("@ShreetName,@DistrictId)");
SqlParameter[] parameters =
{
new SqlParameter("@ShreetName", SqlDbType.VarChar, 20),
new SqlParameter("@DistrictId", SqlDbType.Int, 4)
};
parameters[0].Value = model.ShreetName;
parameters[1].Value = model.DistrictId;
return(SQLDBHelper.ExcuteSql(strSql.ToString(), parameters));
}
/// <summary>
/// 更新一条数据
/// </summary>
public bool Update(Shreet model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update Shreet set ");
strSql.Append("ShreetName=@ShreetName,");
strSql.Append("DistrictId=@DistrictId ");
strSql.Append(" where ShreetId=@ShreetId ");
SqlParameter[] parameters =
{
new SqlParameter("@ShreetName", SqlDbType.VarChar, 20),
new SqlParameter("@DistrictId", SqlDbType.Int, 4),
new SqlParameter("@ShreetId", SqlDbType.Int, 4)
};
parameters[0].Value = model.ShreetName;
parameters[1].Value = model.DistrictId;
parameters[2].Value = model.ShreetId;
return(SQLDBHelper.ExcuteSql(strSql.ToString(), parameters));
}
public IList <City> GetAllCity(string sql, SqlParameter[] paras)
{
IList <City> list = null;
DataTable table = SQLDBHelper.GetDataTable(sql, paras);
if (table != null)
{
if (table.Rows.Count > 0)
{
list = new List <City>();
foreach (DataRow row in table.Rows)
{
City city = new City();
city.CityId = int.Parse(row["cityId"].ToString());
city.CityName = row["cityName"].ToString();
list.Add(city);
}
}
}
return(list);
}
public IList <Shreet> GetShreetTx()
{
string sql = "SELECT * FROM Shreet";
IList <Shreet> list = null;
DataTable table = SQLDBHelper.GetDataTable(sql, null);
if (table != null)
{
if (table.Rows.Count > 0)
{
list = new List <Shreet>();
foreach (DataRow row in table.Rows)
{
Shreet shreet = new Shreet();
shreet.ShreetId = int.Parse(row["shreetId"].ToString());
shreet.ShreetName = row["shreetName"].ToString();
shreet.DistrictId = int.Parse(row["districtId"].ToString());
list.Add(shreet);
}
}
}
return(list);
}
public static string AutoNumber(string TableName, string FieldName)
{
string Str = "select max(" + FieldName + ") as id from " + TableName; //查询表中的字段
object obj = SQLDBHelper.GetSingle(Str); //把查询字符串Str放到GetSingle()中执行查询(Command)并返回查询结果（对象）
string maxID = "";
if (obj != null)//返回（查询字符串）对象
{
maxID = SQLDBHelper.GetSingle(Str).ToString();
}
string Result = "";
if (maxID == "")//没有最大编号
{
Result = "0001";
}
else
{
string strNewFour = (Convert.ToInt32(maxID) + 1).ToString("0000"); //0000+1
Result = strNewFour; //CG200902250001
}
return(Result);
}
