110,536
社区成员
发帖
与我相关
我的任务
分享
public int InsertApplet(DataTable dt,List<string> lstr,string from)
{
int Num = 0;
string InsertSQL;
SqlTransaction tran;
con = new SqlConnection(DbConnStr());
con.Open();
tran = con.BeginTransaction();
InsertSQL = "INSERT INTO "+from.ToString()+" ([username],[sex],[moble],[Phone],[City],[CardType],[Address],[Funds],[Funds2],[Credential],[Credit],[Notes],[ComeFrom],[creat_time],[Status]) VALUES (@username,@sex,@moble,@Phone,@City,@CardType,@Address,@Funds,@Funds2,@Credential,@Credit,@Notes,@ComeFrom,@creat_time,@Status)";
try
{
if (dt.Rows.Count > 1)
{
for (int i = 0; i < dt.Rows.Count;i++ )
{
Num = Num + 1;
SqlParameter[] arParams = new SqlParameter[18];
if (dt.Columns.Count>0 &&dt.Rows[i][0].ToString() != "" && lstr.Contains("0") )
{
arParams[1] = new SqlParameter("@username", dt.Rows[i][0].ToString());
}
else
{
arParams[1] = new SqlParameter("@username",DBNull.Value );
}
if (dt.Columns.Count > 1&&dt.Rows[i][1].ToString() != "" && lstr.Contains("1") )
{
arParams[2] = new SqlParameter("@sex", dt.Rows[i][1].ToString());
}
else
{
arParams[2] = new SqlParameter("@sex", DBNull.Value);
}
if ( dt.Columns.Count > 2&&dt.Rows[i][2].ToString() != "" && lstr.Contains("2"))
{
arParams[3] = new SqlParameter("@moble", dt.Rows[i][2].ToString());
}
else
{
arParams[3] = new SqlParameter("@moble", DBNull.Value);
}
if (dt.Columns.Count > 3&&dt.Rows[i][3].ToString() != "" && lstr.Contains("3") )
{
arParams[4] = new SqlParameter("@Phone", dt.Rows[i][3].ToString());
}
else
{
arParams[4] = new SqlParameter("@Phone", DBNull.Value);
}
if (dt.Columns.Count > 4&&dt.Rows[i][4].ToString() != "" && lstr.Contains("4"))
{
arParams[5] = new SqlParameter("@City", dt.Rows[i][4].ToString());
}
else
{
arParams[5] = new SqlParameter("@City", DBNull.Value);
}
if (dt.Columns.Count >5&& dt.Rows[i][5].ToString() != "" && lstr.Contains("5"))
{
arParams[6] = new SqlParameter("@CardType", dt.Rows[i][5].ToString());
}
else
{
arParams[6] = new SqlParameter("@CardType", DBNull.Value);
}
if ( dt.Columns.Count>6&&dt.Rows[i][6].ToString() != "" && lstr.Contains("6") )
{
arParams[7] = new SqlParameter("@Address", dt.Rows[i][6].ToString());
}
else
{
arParams[7] = new SqlParameter("@Address", DBNull.Value);
}
if (dt.Columns.Count>7&&dt.Rows[i][7].ToString() != "" && lstr.Contains("7") )
{
arParams[8] = new SqlParameter("@Funds", dt.Rows[i][7].ToString());
}
else
{
arParams[8] = new SqlParameter("@Funds", DBNull.Value);
}
if (dt.Columns.Count>8&&dt.Rows[i][8].ToString() != "" && lstr.Contains("8") )
{
arParams[9] = new SqlParameter("@Funds2", dt.Rows[i][8].ToString());
}
else
{
arParams[9] = new SqlParameter("@Funds2", DBNull.Value);
}
if (dt.Columns.Count>9&&dt.Rows[i][9].ToString() != "" && lstr.Contains("9") )
{
arParams[10] = new SqlParameter("@Credential", dt.Rows[i][9].ToString());
}
else
{
arParams[10] = new SqlParameter("@Credential", DBNull.Value);
}
if (dt.Columns.Count>10&& dt.Rows[i][10].ToString() != "" && lstr.Contains("10"))
{
arParams[11] = new SqlParameter("@Credit", dt.Rows[i][10].ToString());
}
else
{
arParams[11] = new SqlParameter("@Credit", DBNull.Value);
}
if ( dt.Columns.Count >11&&dt.Rows[i][11].ToString() != "" && lstr.Contains("11") )
{
arParams[12] = new SqlParameter("@Notes", dt.Rows[i][11].ToString());
}
else
{
arParams[12] = new SqlParameter("@Notes", DBNull.Value);
}
if (dt.Columns.Count >12&& dt.Rows[i][12].ToString() != "" && lstr.Contains("12"))
{
arParams[13] = new SqlParameter("@ComeFrom", dt.Rows[i][12].ToString());
}
else
{
arParams[13] = new SqlParameter("@ComeFrom", DBNull.Value);
}
arParams[14] = new SqlParameter("@creat_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
arParams[15] = new SqlParameter("@Status","1");
SqlHelper.ExecuteNonQuery(tran, CommandType.Text, InsertSQL, arParams);
}
}
tran.Commit();
return Num;