1:查询表中vchFundType 为 02,vchSubFundType 位11的数据,显示vchFundName和vchFundID。
public DataSet GetFundInfoName()
{
DataSet ds = new DataSet();
string sql = @"select vchFundName,
vchFundID
from FundInfo
where
vchFundType = '02'
and
vchSubFundType = '11'";
SqlDataAdapter sd = new SqlDataAdapter(sql,_conn);
sd.Fill(ds);
_conn.Close();
return ds;
}
2:带条件
public DataSet ModifyFundPortfolioInfoData(string fundId,
string assetId,
string portfolioId,
string portfolioName,
string portfolioDesc,
string portfolioType,
string recordStatus)
{
DataSet ds = new DataSet();
string sqlString = "select Count(1) from FundPortfolioInfo where vchPortfolioId=('" + portfolioId + "')";
SqlCommand cmd1 = new SqlCommand(sqlString, _conn);
int count = (int)cmd1.ExecuteScalar();
if (count >= 1)
{
throw new Exception("组合代码具有唯一性,请重新输入");
}
else
{
string sql = @"
insert into FundPortfolioInfo(
vchFundId,
vchAssetUnitId,
vchPortfolioId,
vchPortfolioName,
vchPortfolioDesc,
nPortfolioType,
tiRecordStatus)
values(
'" + fundId + "', '"
+ assetId + "', '"
+ portfolioId + "', '"
+ portfolioName + "', '"
+ portfolioDesc + "', '"
+ portfolioType + "', '"
+ recordStatus + "')";
SqlDataAdapter da = new SqlDataAdapter(sql, _conn);
da.Fill(ds);
_conn.Close();
}
return ds;
}
3:多表关联
public DataSet GetFundIDData(string fundId)
{
DataSet ds = new DataSet();
string sqlString = @"
SELECT t1.nPortfolioSerialId ,
t1.vchPortfolioId ,
t1.vchPortfolioName ,
t1.vchPortfolioDesc ,
t1.nPortfolioType,
t1.vchFundId ,
t2.vchFundName,
t1.vchAssetUnitId ,
t3.vchAssetUnitName,
t1.dtInsertDate ,
t1.vchInsertBy ,
t1.dtUpdateDate ,
t1.vchUpdateBy ,
t1.tiRecordStatus
FROM dbo.FundPortfolioInfo t1
LEFT OUTER JOIN dbo .FundInfo t2 ON t2.vchFundId = t1.vchFundId
LEFT OUTER JOIN dbo.AssetUnitInfo t3 ON t3.vchAssetUnitId = t1.vchAssetUnitId
WHERE T1.vchFundId = '{0}'
AND T1.tiRecordStatus = 1
";
sqlString = String.Format(sqlString,fundId);
SqlDataAdapter sd = new SqlDataAdapter(sqlString, _conn);
sd.Fill(ds);
_conn.Close();
return ds;
}
4public class FundTradeData
{
private static DBHelper _dbHelper = QTGlobal.DBHelper;
SqlConnection _conn = new SqlConnection();
public FundTradeData()
{
if (_conn != null)
{
//暂时使用,过后带入config.
string s = "server='IP地址';database='QT_Main';uid='QT.DEV';pwd='111111'";//连接数据库的字符串
_conn = new SqlConnection(s);
if (_conn.State == ConnectionState.Closed)
{
_conn.Open();
}
}
}
}