public static int GetData()
{
string sqlstr = "select totalNub FROM [dbo].[TicketTest] with(NOLOCK)";
int totalNub = (int)SQLHelps.ExecuteScalar(sqlstr);
Console.WriteLine($"获取现在剩余票数:{totalNub}");
return(totalNub);
}
/// <summary>
/// Union
/// </summary>
/// <returns></returns>
public static SqlDataReader Union()
{
//检查
string sql = "select * from BaseTable union select * from Tables";
//不检查
string sql1 = "select * from BaseTable union select * from Tables";
return(SQLHelps.ExecuteReader(sql));
}
public static void UpdateNo()
{
List <string> list = new List <string>();
string sql = "update BaseTable set name='小七' where name= '张三'";
string sql1 = "update BaseTable set name='小八' where name= '李四'";
list.Add(sql);
list.Add(sql1);
SQLHelps.ExecuteNonQueryNo(list);
}
/// <summary>
/// 连接查询
/// </summary>
/// <returns></returns>
public static SqlDataReader Join()
{
//内连接
string sql = "select * from BaseTable join tabname on BaseTable.id=tabname.id";
//左连接
string sql1 = "select * from BaseTable left join tabname on BaseTable.id=tabname.id";
//右连接
string sql2 = "select * from BaseTable right join tabname on BaseTable.id=tabname.id";
//完全外连接
string sql3 = "select * from BaseTable full join tabname on BaseTable.id=tabname.id";
//交叉外连接
string sql4 = "select * from BaseTable cross join tabname ";
return(SQLHelps.ExecuteReader(sql));
}
public static void SqlUPDLOCK2()
{
Console.WriteLine("开始抢票！抢票有延迟时间期间别人不可修改数据只可查询");
string threadId = System.Threading.Thread.CurrentThread.ManagedThreadId.ToString();
Console.WriteLine($"线程{threadId}开始抢票");
//更新锁延迟
string sqlstr = "declare @flag as TimeStamp SELECT @flag=VersionNum FROM[dbo].[TicketTest] where id='0001' waitfor delay '00:00:05'update TicketTest set totalNub = totalNub - 1 where id='0001' and versionNum=@flag";
int executeRow = SQLHelps.ExecuteNonQuery(sqlstr);
if (executeRow > 0)
{
Console.WriteLine($"-------------线程{threadId}抢票成功-------------");
}
else
{
Console.WriteLine($"线程{threadId}抢票失败");
}
}
/// <summary>
/// 悲观锁
/// </summary>
public static void SqlUPDLOCK()
{
Console.WriteLine("开始抢票！抢票有延迟时间期间别人不可修改数据只可查询");
string threadId = System.Threading.Thread.CurrentThread.ManagedThreadId.ToString();
Console.WriteLine($"线程{threadId}开始抢票");
//更新锁延迟
string sqlstr = "SELECT *FROM[dbo].[TicketTest] with(UPDlock) where id='0001' waitfor delay '00:00:05'update TicketTest set totalNub = totalNub - 1 where id='0001' and totalNub > 0";
//行级锁
//string sqlstr = "update [dbo].[TicketTest] with(ROWLOCK) set totalNub=totalNub-1 where id = '0001'";
int executeRow = SQLHelps.ExecuteNonQuery(sqlstr);
if (executeRow > 0)
{
Console.WriteLine($"-------------线程{threadId}抢票成功-------------");
}
else
{
Console.WriteLine($"线程{threadId}抢票失败");
}
}
/// <summary>
/// 根据条件模糊查询指定表总数
/// </summary>
/// <param name="table">查询的表</param>
/// <param name="value">条件值</param>
/// <returns></returns>
public static SqlDataReader SearchLike(string table, string value)
{
string sql = "select count(*) from " + table + " where name like '%" + value + "%'";
return(SQLHelps.ExecuteReader(sql));
}
/// <summary>
/// 查询指定表的数据总数
/// </summary>
/// <param name="table"></param>
public static int Search(string table)
{
string sql = "select count(*) from " + table;
return((int)SQLHelps.ExecuteScalar(sql));
}
/// <summary>
/// 添加数据
/// </summary>
public static int Add()
{
string sql = "insert into BaseTable(name,age) values(@name,@age)";
return(SQLHelps.ExecuteNonQuery(sql, new SqlParameter("@name", "张三"), new SqlParameter("@age", 18)));
}
public static SqlDataReader SearchColumn(string table)
{
string sql = "select name from syscolumns where id=object_id('" + table + "')";
return(SQLHelps.ExecuteReader(sql));
}
/// <summary>
/// 查询数据库所有表名
/// </summary>
/// <returns></returns>
public static SqlDataReader SearchTable()
{
string sql = "select name from sysobjects where type='U'";
return(SQLHelps.ExecuteReader(sql));
}
/// <summary>
/// 添加列
/// </summary>
public static int AddCloumn(string column, string type)
{
string sql = "Alter table tabname add " + column + " " + type;
return(SQLHelps.ExecuteNonQuery(sql));
}
/// <summary>
/// 删除表
/// </summary>
public static int DropTable()
{
string sql = "drop table tabname";
return(SQLHelps.ExecuteNonQuery(sql));
}
/// <summary>
/// Intersect
/// </summary>
/// <returns></returns>
public static SqlDataReader Intersect()
{
string sql = "select id from BaseTable intersect select id from tabname";
return(SQLHelps.ExecuteReader(sql));
}
/// <summary>
/// 提交事务
/// </summary>
public static void Save()
{
SQLHelps.ExecuteNonQueryNo(SqlList);
}
/// <summary>
/// 根据条件修改指定表字段的值
/// </summary>
/// <param name="table">表名</param>
/// <param name="column1">修改的列</param>
/// <param name="value1">修改的值</param>
/// <param name="column2">条件列</param>
/// <param name="value2">条件值</param>
/// <returns></returns>
public static int Update(string table, string column1, string value1, string column2, string value2)
{
string sql = "update " + table + " set " + column1 + "=" + value1 + " where " + column2 + "=" + value2;
return((int)SQLHelps.ExecuteNonQuery(sql));
}
/// <summary>
/// 创建表
/// </summary>
public static int AddTable()
{
string sql = "create table tabname(id int primary key )";
return(SQLHelps.ExecuteNonQuery(sql));
}
public static SqlDataReader ProcBaseTable(string procname)
{
return(SQLHelps.ExecuteReaderSP(procname, new SqlParameter("@name", "张三"), new SqlParameter("@age", "18")));
}
/// <summary>
/// 查询范围
/// </summary>
/// <param name="table">表名</param>
/// <param name="column">列名</param>
/// <param name="value1">开始值</param>
/// <param name="value2">结束值</param>
/// <returns></returns>
public static SqlDataReader Between(string table, string column, string value1, string value2)
{
string sql = "select * from " + table + " where " + column + " between " + value1 + " and " + value2;
return(SQLHelps.ExecuteReader(sql));
}
/// <summary>
/// 求平均数
/// </summary>
/// <param name="table"></param>
/// <param name="column"></param>
/// <returns></returns>
public static int Avg(string table, string column)
{
string sql = "select avg(" + column + ") as avgvalue from " + table;
return((int)SQLHelps.ExecuteScalar(sql));
}
