Question

用C#实现简单的一个表的增删该查，并且要用数据库分页设置，怎么样做简单方便，有具体例子的请发的谢谢。

问题如题

Answer 1

连接数据库的类：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
namespace LibraryDB
{
class DBConnection
{
internal SqlConnection Connection()
{
string connectionString = "连接数据库的信息";
SqlConnection connection= new SqlConnection(connectionString);
connection.Open();
return connection;
}
}
}

建表类：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
namespace LibraryDB
{
class CreatTable
{
DBConnection dbConnection = new DBConnection();
internal void creatTable()
{
using (dbConnection.Connection())
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = dbConnection.Connection();
try
{
cmd.CommandText = "create table Library (ID int not null, bookname varchar(100), picture image, ISBN nvarchar(100), publicationdate datetime, price varchar(100), author varchar(100), abstract varchar(100))";
cmd.ExecuteNonQuery();
Console.WriteLine("Library创建成功！");
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
}
}
}
}
}

sqlsever类：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
namespace LibraryDB
{
class SQLServe
{
DBConnection dbConnection = new DBConnection();
internal void Serve(string sqlString)
{
using (dbConnection.Connection())
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = dbConnection.Connection();
cmd.CommandText = sqlString;
cmd.ExecuteNonQuery();
}
}
}
}

主函数类：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace LibraryDB
{
class Program
{
static void Main(string[] args)
{
string sqlString = null;
CreatTable creatTable = new CreatTable();
creatTable.creatTable();
SQLServe sqlServe = new SQLServe();
Console.WriteLine("是否需要查看输入语法规则？y/n");
string n = Console.ReadLine();
switch (n)
{
case "y":
Console.WriteLine("例如:\n查找:select bookname,ISBN from Library where 条件表达式");
Console.WriteLine("删除:delete from Library where 条件表达式");
Console.WriteLine("增加:insert into Library(字段1,字段2,字段3...) values (值1,值2,值3...):字符串，汉字加单引号");
Console.WriteLine("修改:update Library set 字段1=值1,字段2=值2,字段3=值3... where 条件表达式");
break;
case "n": break;
default:Console.WriteLine("还是按n比较好。"); break;
}
while (true)
{
Console.WriteLine("请输入SQL语句执行相应的操作,想退出程序，请按2：");
sqlString = Console.ReadLine();
sqlServe.Serve(sqlString);
switch (sqlString[0])
{
case 'd': Console.WriteLine("删除成功！"); break;
case 'i': Console.WriteLine("添加成功！"); break;
case 'u': Console.WriteLine("更新成功！"); break;
case 's': Console.WriteLine("查找成功！"); break;
default: Console.WriteLine("请仔细核对后重新输入！"); break;
}
}
// Console.ReadKey();
}
}
}

Answer 2

你说的是什么数据库?
SQl server分页:
select top 10 * form table1 where indexID not in (select top 20 indexID from table1 )
Oracle分页:
select * from(select rownum r,* from(select * from table1) where rownum<20) where r>10
MySQL分页
select * from tablename limit 5,10--返回第5至10条记录
MSSQL通用存储过程分页
以下是在网上搜索到的一个MS SQL存储过程分页的示例. 由于采用了游标,故效率会低一些,但是比较通用. 适合一些需要快速开发的小项目.
测试：15万条数据,用时1s.
--=====调用示例====
declare @str varchar(200);
set @str = ''select * from t_user order by id''
exec pagination @str,4,2
--=====存储过程======
alter procedure pagination
@sqlstr nvarchar(4000), --查询字符串
@currentpage int, --第N页
@pagesize int --每页行数
as
set nocount on
declare @P1 int, --P1是游标的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
declare @totalPage int --总页数
set @totalpage = ceiling(1.0*@rowcount/@pagesize)

--判断当前页是否大于总页数,如果大于总页数.当前页就是最后一页
if (@currentpage>=@totalpage)
set @currentpage = @totalpage
if(@currentpage<1) --如果当前页小于1,则默认当前页是1
set @currentpage=1
print ''当前页''
print @currentpage
select @totalpage as 总页数,@currentpage as 当前页 ,@rowcount as 总记录数--,@rowcount as 总行数,@currentpage as 当前页
declare @begin int --从哪条记录开始的
set @begin = (@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@begin,@pagesize
exec sp_cursorclose @P1
set nocount off