public static bool IsExist(string tablename, string key, object value)
{
List <SqlParameter> spList = new List <System.Data.SqlClient.SqlParameter>();
spList.Add(new System.Data.SqlClient.SqlParameter(key, value));
string sql = string.Format("select count({0}) from {1} where [{0}] = @{0}", key, tablename);
return(LSParse.ToInt(ExecuteScalar(sql, spList.ToArray())) > 0);
}
public static int DeleteData(string tablename, List <SqlHelperDeleteParameter> SqlHelperParameters)
{
string sql = "";
string sqlwhere = "";
List <SqlParameter> spList = new List <System.Data.SqlClient.SqlParameter>();
for (int i = 0; i < SqlHelperParameters.Count; i++)
{
SqlHelperDeleteParameter sqlp = SqlHelperParameters[i];
object sqlval = null;
switch (sqlp.sqltype)
{
case SqlType.Int:
sqlval = LSParse.ToInt(sqlp.value);
break;
case SqlType.String:
sqlval = LSParse.ToString(sqlp.value);
break;
case SqlType.Long:
sqlval = LSParse.ToLong(sqlp.value);
break;
case SqlType.DateTime:
sqlval = LSParse.ToDateTime(sqlp.value);
break;
case SqlType.Double:
sqlval = LSParse.ToDouble(sqlp.value);
break;
case SqlType.Boolean:
sqlval = LSParse.ToBoolean(sqlp.value);
break;
default:
break;
}
if (sqlp.IsCondtion)
{
sqlwhere += string.Format("{0}=@{0} , ", sqlp.key);
}
}
sql = string.Format(" DELETE FROM {0} WHERE {1}", tablename, sqlwhere.TrimEnd(',').Replace(",", "and"));
return(ExecuteNonQuery(sql, spList.ToArray()));
}
public static int IsExist(string tablename, List <SqlHelperAddParameter> _SqlHelperAddParameter)
{
List <SqlParameter> spList = new List <System.Data.SqlClient.SqlParameter>();
string sqlwhere = "";
for (int i = 0; i < _SqlHelperAddParameter.Count; i++)
{
SqlHelperAddParameter shaps = _SqlHelperAddParameter[i];
spList.Add(new System.Data.SqlClient.SqlParameter(shaps.key, shaps.value));
sqlwhere += string.Format("[{0}]= @{0},", shaps.key);
}
string sql = string.Format("select count(*) from {0} where {1}", tablename, sqlwhere.TrimEnd(',').Replace(",", " and "));
return(LSParse.ToInt(ExecuteScalar(sql, spList.ToArray())));
}
public void Add(string key, object value)
{
keys.Add(key);
values.Add(LSParse.ToString(value));
}
/// <summary>
/// 已放弃不建议使用该方法用SaveData
/// </summary>
/// <param name="tablename"></param>
/// <param name="PRIMARY_Key"></param>
/// <param name="SqlHelperParameters"></param>
/// <param name="IsReturnPRIMARY_Value"></param>
/// <returns></returns>
public static object AddData(Parameters parameters, bool Update = true, bool IsReturnPRIMARY_Value = false)
{
List <SqlHelperAddParameter> SqlHelperParameters = parameters.SqlHelperAddParameters;
string tablename = parameters.TableName;
string sql = "";
string sqladdkey = "";
string sqladdvalue = "";
string sqlupdate = "";
string sqlwhere = "";
string sqlreturn = "";
string PRIMARY_Key = "";
object PRIMARY_Value = null;
List <SqlHelperAddParameter> _IsExistParameters = new List <SqlHelperAddParameter>();
List <SqlParameter> spList = new List <System.Data.SqlClient.SqlParameter>();
for (int i = 0; i < SqlHelperParameters.Count; i++)
{
SqlHelperAddParameter sqlp = SqlHelperParameters[i];
object sqlval = null;
switch (sqlp.sqltype)
{
case SqlType.Int:
sqlval = LSParse.ToInt(sqlp.value);
break;
case SqlType.String:
sqlval = LSParse.ToString(sqlp.value);
break;
case SqlType.Long:
sqlval = LSParse.ToLong(sqlp.value);
break;
case SqlType.DateTime:
sqlval = LSParse.ToDateTime(sqlp.value);
break;
case SqlType.Double:
sqlval = LSParse.ToDouble(sqlp.value);
break;
case SqlType.Boolean:
sqlval = LSParse.ToBoolean(sqlp.value);
break;
default:
break;
}
///这是主键
if (sqlp.IsPRIMARY)
{
_IsExistParameters.Add(new SqlHelperAddParameter {
key = sqlp.key, value = sqlp.value, sqltype = sqlp.sqltype
});
if (String.IsNullOrEmpty(PRIMARY_Key))
{
PRIMARY_Key = sqlp.key;
PRIMARY_Value = sqlp.value;
sqlwhere += string.Format(" [{0}]=@{0} ", sqlp.key);
}
else
{
sqlwhere += string.Format(" and [{0}]=@{0} ", sqlp.key);
}
}
if (sqlval != null)
{
spList.Add(new System.Data.SqlClient.SqlParameter(sqlp.key, sqlval));
sqladdkey += string.Format("[{0}],", sqlp.key);
sqladdvalue += string.Format("@{0},", sqlp.key);
sqlupdate += string.Format("[{0}]=@{0},", sqlp.key);
}
}
if (IsReturnPRIMARY_Value)
{
sqlreturn = " select @@IDENTITY ";
}
//判断该数据是否存在,并且要有where条件
int isexist = IsExist(tablename, _IsExistParameters);
if (isexist > 0 && !String.IsNullOrEmpty(sqlwhere))
{
if (Update)
{
sql = string.Format(@"UPDATE {0} SET {1} WHERE {2} ", tablename, sqlupdate.TrimEnd(','), sqlwhere);
if (ExecuteNonQuery(sql, spList.ToArray()) > 0)
{
if (IsReturnPRIMARY_Value)
{
return(PRIMARY_Value);
}
}
else
{
return(-1);
}
}
else
{
return(-1);
}
}
else
{
sql = string.Format(@"INSERT INTO {0}({1}) VALUES ({2})
{3} ", tablename, sqladdkey.TrimEnd(','), sqladdvalue.TrimEnd(','), sqlreturn);
if (IsReturnPRIMARY_Value)
{
return(ExecuteScalar(sql, spList.ToArray()));
}
else
{
return(ExecuteNonQuery(sql, spList.ToArray()));
}
}
return(-1);
}
