对数据库的操作总结

时间字符串存入数据表datatime字段处理：convert(char(10),queuetime,120)

如：//选出数据库Queueid字段的最大值 public int SelectQueueidMax(string queuetime) { SqlConnection con = new SqlConnection(consql); SqlCommand cmd = new SqlCommand("select top 1 * from Table_Queue where convert(char(10),queuetime,120) = '" + queuetime + "' order by Queueid desc", con); con.Open(); SqlDataReader sdrqueueid = cmd.ExecuteReader(CommandBehavior.CloseConnection); sdrqueueid.Read(); int result = (Int32)sdrqueueid["Queueid"]; return result; }

 

插入： public int InsertTable_Cuspatient(string Cosid, string CosName, string Cosage, string Cossex, DateTime Costime, string Cosdotime) { SqlConnection con = new SqlConnection(consql); string insertTable_Cuspatient = string.Format("insert into Table_Cuspatient values ('{0}','{1}','{2}','{3}','{4}','{5}');select @@identity;", @Cosid, @CosName, @Cosage, @Cossex, @Costime, @Cosdotime); SqlCommand cmd = new SqlCommand(insertTable_Cuspatient, con); cmd.Parameters.Add("@Cosid", SqlDbType.VarChar, 50).Value = Cosid; cmd.Parameters.Add("@CosName", SqlDbType.VarChar, 50).Value = CosName; cmd.Parameters.Add("@Cosage", SqlDbType.VarChar, 50).Value = Cosage; cmd.Parameters.Add("@Cossex", SqlDbType.VarChar, 50).Value = Cossex; cmd.Parameters.Add("@Costime", SqlDbType.DateTime).Value = Costime; cmd.Parameters.Add("@Cosdotime", SqlDbType.VarChar,50).Value = Cosdotime; con.Open(); object result = cmd.ExecuteScalar(); con.Close(); if (result == null) { return -1; } else { return 1; } }

更改：

//修改数据 public int UpdateTable_gongzi(string bumen, string xingming,decimal jibengongzi, decimal gangweigongzi, decimal jintie, decimal yingfagongzi, decimal koukuan, decimal gesuoshui, decimal koukuanheji, decimal shifagongzi, int id) { SqlConnection con = new SqlConnection(SqlCon); string update = string.Format("update Table_gongzi set bumen = '{0}',xingming ='{1}',jibengongzi = '{2}',gangweigongzi = '{3}',jintie = '{4}',yingfagongzi = '{5}',koukuan = '{6}',gesuoshui = '{7}',koukuanheji = '{8}',shifagongzi = '{9}' where id = '{10}'", @bumen, @xingming, @jibengongzi, @gangweigongzi, @jintie, @yingfagongzi, @koukuan, @gesuoshui, @koukuanheji, @shifagongzi, @id); SqlCommand cmd = new SqlCommand(update, con); cmd.Parameters.Add("@bumen", SqlDbType.VarChar, 50).Value = bumen; cmd.Parameters.Add("@xingming", SqlDbType.VarChar, 50).Value = xingming; cmd.Parameters.Add("@jibengongzi", SqlDbType.Decimal).Value = jibengongzi; cmd.Parameters.Add("@gangweigongzi", SqlDbType.Decimal).Value = gangweigongzi; cmd.Parameters.Add("@jintie", SqlDbType.Decimal).Value = jintie; cmd.Parameters.Add("@yingfagongzi", SqlDbType.Decimal).Value = yingfagongzi; cmd.Parameters.Add("@koukuan", SqlDbType.Decimal).Value = koukuan; cmd.Parameters.Add("@gesuoshui", SqlDbType.Decimal).Value = gesuoshui; cmd.Parameters.Add("@koukuanheji", SqlDbType.Decimal).Value = koukuanheji; cmd.Parameters.Add("@shifagongzi", SqlDbType.Decimal).Value = shifagongzi; cmd.Parameters.Add("@id", SqlDbType.Int).Value = id; con.Open(); int result = cmd.ExecuteNonQuery(); con.Close(); return result; }

查询： //从Table_Queue表选单天的历史队列信息 public DataSet SelectOld_Table_Queue(string olddate) { SqlConnection con = new SqlConnection(consql); SqlCommand cmd = new SqlCommand("select * from Table_Queue where queuetime between cast('" + olddate + " 00:00:00' as datetime) and cast('" + olddate + " 23:59:59' as datetime) order by patientpk desc,queuetime asc", con); SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds, "Table_Queue"); return ds; }

删除： //删除选中行数据 public int DeleteTable_gongzi(int id) { SqlConnection con = new SqlConnection(SqlCon); SqlCommand cmd = new SqlCommand("delete from Table_gongzi where id='" + id + "'", con); con.Open(); int result = cmd.ExecuteNonQuery(); con.Close(); return result; }