I want to send sms for each row. how to get solution pls?
I Have display data in the datagridview and i wrote the code following way
private DataTable GetDateSheet()
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["conStr"].ConnectionString);
string myqry = @"DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(ExamDate) from tblDateSheet
group by ExamDate order by ExamDate FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT ROW_NUMBER() OVER(ORDER BY(AdmissionNo))Row,AdmissionNo,SName,FName,FPhone,ExamType,ClassName,SectionName,STime,' + @cols + ' from
(
select AdmissionNo,SName,FName,FPhone,ExamType,ClassName,SectionName,SubjectName,STime,ExamDate
from tblDateSheet as dts inner join tblSetExam as se on dts.SetExamID=se.SetExamID
inner join tblStdReg as sr on dts.ClassID=sr.ClassID and dts.SectionID=sr.SectionID
inner join tblDefClass as dc on dts.ClassID=dc.ClassID
inner join tblDefSection as ds on ds.SectionID=dts.SectionID
inner join tblAssignSubjects as si on dts.SubjectID=si.ASID
inner join tblDefSubject as dsb on si.SubjectID=dsb.SubjectID
where dts.SetExamID='+@EId+' and dts.ClassID='+@CId+' and dts.SectionID='+@SId+'
) x
pivot
(
max(SubjectName)
for ExamDate in (' + @cols + ')
) p '
execute(@query);";
SqlCommand cmd = new SqlCommand(myqry, con);
cmd.Parameters.Add(new SqlParameter("@EId", this.cmbExam.SelectedValue.ToString()));
cmd.Parameters.Add(new SqlParameter("@CId", this.cmbClass.SelectedValue.ToString()));
cmd.Parameters.Add(new SqlParameter("@SId", this.cmbSection.SelectedValue.ToString()));
SqlDataAdapter adapt = new SqlDataAdapter();
DataTable sTable = new DataTable();
adapt.SelectCommand = cmd;
//adapt.Fill(sTable);
ds = new DataSet();
adapt.Fill(ds, "tblDateSheet");
sTable = ds.Tables["tblDateSheet"];
dGVSession.DataSource = ds.Tables["tblDateSheet"].DefaultView;
con.Close();
return sTable;
}
private void btnSMS_Click(object sender, EventArgs e)
{
try
{
string dynamicVal = string.Empty;
string Number = string.Empty;
DataTable dt = GetDateSheet();
foreach (DataGridViewRow row in dGVSession.Rows)
{
Number = row.Cells["FPhone"].Value.ToString();
DataRow[] dr = dt.Select("AdmissionNo='" + row.Cells[1].Value + "'");
for (int i = 0; i < dr.Length; i++)
{
for (int k = 0; k < dt.Columns.Count - 1; k++)
{
string columnName = dt.Columns[k + 1].ColumnName;
string value = dr[i].ItemArray[k].ToString() == "" ? "Null" : dr[i].ItemArray[k].ToString();
if (k >= 9)
{
dynamicVal += value + " on " + Convert.ToDateTime(columnName).ToString("dd.MM.yy") + ".";
}
else
{
dynamicVal += value + ",";
}
}
dynamicVal += "\n";
}
}
MessageBox.Show("Result is: " + dynamicVal);
}
catch (Exception ex)
{
MessageBox.Show("Following Error: " + ex.ToString());
}
}