public class SqlConnect
{
/// <summary>
/// 连接字符串获取
/// </summary>
private static string connectString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings["ConnStr"].ToString();
/// <summary>
/// 建立数据库连接
/// </summary>
/// <returns></returns>
public static SqlConnection getConn()
{
try
{
SqlConnection con = new SqlConnection(connectString);
return con;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw;
}
}
}
接收返回值对象的Model类
public class VOSWordListView
{
public long Id { get; set; }
public string UID { get; set; }
public string ChineseName { get; set; }
public string LargeClass { get; set; }
public string Message { get; set; }
public string Title { get; set; }
public string ModelName { get; set; }
public string Brand { get; set; }
public string RegionName { get; set; }
public string Province { get; set; }
}
接收返回值对象的Model类
public List<VOSWordListView> select()
{
VOSWordListView v = null;
List<VOSWordListView> vl = new List<VOSWordListView>();
try
{
String InsertStr = @"
SELECT top 10
t.ID,
t.ChineseName,
t.LargeClass,
(SELECT top 1 UID from T_VOS WHERE UID = t.UID ORDER BY UID ) as UID,
(SELECT top 1 Title from T_VOS WHERE UID = t.UID ORDER BY UID ) as Title,
(SELECT top 1 Message from T_VOS WHERE UID = t.UID ORDER BY UID ) as Message,
(SELECT top 1 Name from T_VOS_Model_Matching WHERE UID = t.UID ORDER BY[Index] ) as ModelName,
(SELECT top 1 Brand from T_VOS_Model_Matching WHERE UID = t.UID ORDER BY[Index] ) as Brand,
(SELECT top 1 Name from T_VOS_Region_Matching WHERE UID = t.UID ORDER BY[Index] ) as RegionName,
(SELECT top 1 Province from T_VOS_Region_Matching WHERE UID = t.UID ORDER BY[Index] ) as Province
FROM T_VOS_Quality_Matching t
ORDER BY t.ID DESC";
using (var con = SqlConnect.getConn())
{
con.Open();
SqlCommand command = new SqlCommand(InsertStr, con);
using (var reader = command.ExecuteReader())
{
//int clumn = reader.FieldCount;
while (reader.Read())
{
v = new VOSWordListView()
{
Brand = reader["Brand"].ToString(),
ChineseName = reader["ChineseName"].ToString(),
Id = Convert.ToInt64(reader["Id"]),
LargeClass = reader["LargeClass"].ToString(),
Message = reader["Message"].ToString(),
ModelName = reader["ModelName"].ToString(),
Province = reader["Province"].ToString(),
RegionName = reader["RegionName"].ToString(),
Title = reader["Title"].ToString(),
UID = reader["UID"].ToString()
};
vl.Add(v);
}
}
}
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
finally
{
}
return vl;
}
一个方法完成对数据库的操作
public int count()
{
int count = 0;
String InsertStr = @"SELECT COUNT(*)test FROM (SELECT
t.ID,
t.ChineseName,
t.LargeClass,
(SELECT top 1 UID from T_VOS WHERE UID = t.UID ORDER BY UID ) as UID,
(SELECT top 1 Title from T_VOS WHERE UID = t.UID ORDER BY UID ) as Title,
(SELECT top 1 Message from T_VOS WHERE UID = t.UID ORDER BY UID ) as Message,
(SELECT top 1 Name from T_VOS_Model_Matching WHERE UID = t.UID ORDER BY[Index] ) as ModelName,
(SELECT top 1 Brand from T_VOS_Model_Matching WHERE UID = t.UID ORDER BY[Index] ) as Brand,
(SELECT top 1 Name from T_VOS_Region_Matching WHERE UID = t.UID ORDER BY[Index] ) as RegionName,
(SELECT top 1 Province from T_VOS_Region_Matching WHERE UID = t.UID ORDER BY[Index] ) as Province
FROM T_VOS_Quality_Matching t
) t";
SqlConnection con = new SqlConnection("这里写连接字符串connectionString");
con.Open();
SqlCommand command = new SqlCommand(InsertStr, con);
var reader = command.ExecuteReader();
while (reader.Read())
{
count = Convert.ToInt32(reader["test"]);
}
return count;
}
}