数据库操作,保存一些配置信息到配置表中 就一条记录
保存前,首先判断符合条件的记录存在不存在,如果不存在,则添加一条记录,如果存在
则直接更新。改用什么方法操作 最简单。
表结构ExtrInfo
int varchar(50) varchar(50) varchar(50) varchar(50) varchar(50)
Id Header SubItem1 SubItem2 SubItem3 SubItem4
Id 自增字段。查询索引 sql="select * from extrainfo where header='标志'
以前ADO的时候,直接用下面操作就可以了
_RecordsetPtr pRst;
pRst.CreateInstance(__uuidof(Recordset));
pRst->Open((_variant_t)sql,_variant_t((IDispatch*)连接实例,true),adOpenStatic,adLockOptimistic,adCmdText);
if(pRst->adoEOF)
pRst->AddNew();
pRst->PutCollect("Header",_variant_t(sFlag));
pRst->PutCollect("SubItem1",_variant_t(str1));
pRst->PutCollect("SubItem2",_variant_t(str2));
pRst->PutCollect("SubItem3",_variant_t(str3));
pRst->PutCollect("SubItem4",_variant_t(str4));
pRst->Update();
pRst->Close();
在C#下 改怎么简单的实现?
保存前,首先判断符合条件的记录存在不存在,如果不存在,则添加一条记录,如果存在
则直接更新。改用什么方法操作 最简单。
表结构ExtrInfo
int varchar(50) varchar(50) varchar(50) varchar(50) varchar(50)
Id Header SubItem1 SubItem2 SubItem3 SubItem4
Id 自增字段。查询索引 sql="select * from extrainfo where header='标志'
以前ADO的时候,直接用下面操作就可以了
_RecordsetPtr pRst;
pRst.CreateInstance(__uuidof(Recordset));
pRst->Open((_variant_t)sql,_variant_t((IDispatch*)连接实例,true),adOpenStatic,adLockOptimistic,adCmdText);
if(pRst->adoEOF)
pRst->AddNew();
pRst->PutCollect("Header",_variant_t(sFlag));
pRst->PutCollect("SubItem1",_variant_t(str1));
pRst->PutCollect("SubItem2",_variant_t(str2));
pRst->PutCollect("SubItem3",_variant_t(str3));
pRst->PutCollect("SubItem4",_variant_t(str4));
pRst->Update();
pRst->Close();
在C#下 改怎么简单的实现?
if exists(select * from extrainfo where header='标志')
begin
update extrainfo set xxx = 'ss' where header='标志';
end
else
begin
insert extrainfo values('ss','标志');
end;
直接执行这个SQL语句,不论在C++中还是在C#中都可以使用。
//使用的GuId
//参数化SQL, 他的本质是MSSQL的sp_executesql
//所以ASP或其他语言也可以实现这样的写法.
using (SqlConnection aConn = new SqlConnection()) {
aConn.ConnectionString = "Server=127.0.0.1;DataBase=Master;UId=sa;Pwd=123";
using (SqlCommand aRs = new SqlCommand()) {
aRs.Connection = aConn;
aRs.CommandType = CommandType.Text;
aRs.CommandText = ""
+ "\nIf NOT EXISTS(SELECT * FROM [Table] WHERE CONVERT(varchar(36), [GuId]) = @GuId) BEGIN"
+ "\nINSERT INTO [Table] ([GuId], [CreateUser], [CreateTime], [UpdateUser], [UpdateTime]) VALUES (@GuId, @User, GETDATE(), @User, GETDATE())"
+ "\nEND"
+ "\nUPDATE [Table] SET"
+ "\n [GuId] = @GuId"
+ "\n,[Title] = @Title"
+ "\n,[Data] = @Data"
+ "\n,[UpdateUser] = @User"
+ "\n,[UpdateTime] = GETDATE()"
+ "\nWHERE [GuId] = @GuId"
;
String aGuId = this.Request.QueryString["GuId"];
aGuId = aGuId == null ? "" : aGuId;
aGuId = aGuId == "" ? Guid.NewGuid().ToString() : aGuId; aRs.Parameters.Add(new SqlParameter("@GuId", aGuId));
aRs.Parameters.Add(new SqlParameter("@Title", "标题"));
aRs.Parameters.Add(new SqlParameter("@Data", "内容"));
aRs.Parameters.Add(new SqlParameter("@User", "admin"));
aConn.Open();
using (SqlDataReader aData = aRs.ExecuteReader()) {
while (aData.Read()) {
Response.Write(aData["name"].ToString() + "<br>");
}
}
}
}
//使用的是自增长的Int
using (SqlConnection aConn = new SqlConnection()) {
aConn.ConnectionString = "Server=127.0.0.1;DataBase=Master;UId=sa;Pwd=123";
using (SqlCommand aRs = new SqlCommand()) {
aRs.Connection = aConn;
aRs.CommandType = CommandType.Text;
aRs.CommandText = ""
+ "\nIf NOT EXISTS(SELECT * FROM [Table] WHERE [Id] = @Id) BEGIN"
+ "\nINSERT INTO [Table] ([Id], [CreateUser], [CreateTime], [UpdateUser], [UpdateTime]) VALUES (@Id, @User, GETDATE(), @User, GETDATE())"
+ "\nSET @Id = @@IDENTITY"
+ "\nEND"
+ "\nUPDATE [Table] SET"
+ "\n [Id] = @Id"
+ "\n,[Title] = @Title"
+ "\n,[Data] = @Data"
+ "\n,[UpdateUser] = @User"
+ "\n,[UpdateTime] = GETDATE()"
+ "\nWHERE [Id] = @Id"
;
Int32 aId = 0;
Int32.TryParse(this.Request.QueryString["GuId"], out aId); aRs.Parameters.Add(new SqlParameter("@Id", aId.ToString()));
aRs.Parameters.Add(new SqlParameter("@Title", "标题"));
aRs.Parameters.Add(new SqlParameter("@Data", "内容"));
aRs.Parameters.Add(new SqlParameter("@User", "admin"));
aConn.Open();
using (SqlDataReader aData = aRs.ExecuteReader()) {
while (aData.Read()) {
Response.Write(aData["name"].ToString() + "<br>");
}
}
}
}
/// 判断是否存在记录
/// </summary>
/// <param name="psql"></param>
/// <returns></returns>
public static bool isExists(string psql) {
bool r = false;
SqlConnection conn = new SqlConnection(ConnectInfo.connstr);
SqlCommand cmd = null;
try {
conn.Open();
cmd = MakeSqlCmd(conn, psql);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read()) {
r = true;
}
dr.Close();
}
catch (Exception ex) {
string src = string.Format("{0}{1}{2}()\r\n{3}",Tools.MyClassMethodName,mNAME,"isExists",mksql(psql));
Tools.NextError(ex, ex.Message, src);
}
finally {
Close(conn, cmd);
}
return r;
}
/// <summary>
/// 判断是否存在记录
/// </summary>
/// <param name="psql"></param>
/// <returns></returns>
public static bool isExists(string psql) {
bool r = false;
SqlConnection conn = new SqlConnection(ConnectInfo.connstr);
SqlCommand cmd = null;
try {
conn.Open();
cmd = MakeSqlCmd(conn, psql);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read()) {
r = true;
}
dr.Close();
}
catch (Exception ex) {
string src = string.Format("{0}{1}{2}()\r\n{3}",Tools.MyClassMethodName,mNAME,"isExists",mksql(psql));
Tools.NextError(ex, ex.Message, src);
}
finally {
Close(conn, cmd);
}
return r;
}
/// <summary>
/// 判断是否存在记录
/// </summary>
/// <param name="psql"></param>
/// <returns></returns>
public static bool isExists(string psql) {
bool r = false;
SqlConnection conn = new SqlConnection(ConnectInfo.connstr);
SqlCommand cmd = null;
try {
conn.Open();
cmd = MakeSqlCmd(conn, psql);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read()) {
r = true;
}
dr.Close();
}
catch (Exception ex) {
string src = string.Format("{0}{1}{2}()\r\n{3}",Tools.MyClassMethodName,mNAME,"isExists",mksql(psql));
Tools.NextError(ex, ex.Message, src);
}
finally {
Close(conn, cmd);
}
return r;
}
/// 判断是否存在记录
/// </summary>
/// <param name="psql"></param>
/// <returns></returns>
public static bool isExists(string psql) {
bool r = false;
SqlConnection conn = new SqlConnection(ConnectInfo.connstr);
SqlCommand cmd = null;
try {
conn.Open();
cmd = MakeSqlCmd(conn, psql);
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read()) {
r = true;
}
dr.Close();
}
catch (Exception ex) {
string src = string.Format("{0}{1}{2}()\r\n{3}",Tools.MyClassMethodName,mNAME,"isExists",mksql(psql));
Tools.NextError(ex, ex.Message, src);
}
finally {
Close(conn, cmd);
}
return r;
}
多谢,我的意思是指 每次操作表的时候,都需要判断
1)改记录是否已经存在 这样就必须操作一次数据库 之后再用Update语句
2)如果判断没有话,使用Insert语句,如果表的字段很多的时候,就需要写很长的插入SQL语句。
有没有办法直接给字段写值,然后update到表里面去的用法吗?
如果不想每次都判断的话,或者可以在插入前,先做一次del?
不太明白,更新的时候,难道不需要写一条SQL语句吗??? “update 表 set 参数1=‘’,参数2-
where 条件.....";
再插入的时候,也需要写一个SQL语句 " insert 表(字段1,字段n)values(字段1。,,字段n)不麻烦吗??? ADO不需要这些操作啊