here is the complete example
I have used Northing Database tables as sample. you just need to write logic to send email using smtp client by praparing email body.( see string build hjs order Id)
C# code..
add this code at start up
ProductDataStore datastore = new ProductDataStore();
List<Customer> list = datastore.SendEmails(); //which will call GetCustomers inside sendemails Method.........
public class ProductDataStore
{
public void SendEmails()
{
List<Customer> list = GetCustomers();
StringBuilder sb = new StringBuilder();
foreach (var Customer in list)
{
sb.Append("Customr Id: = " + Customer.CustId);
foreach (var orderitem in Customer.Orders)
{
sb.Append("Order Id " + orderitem.orderId);
}
//call sent email login using smtp class
//System.Net.Mail.SmtpClient client = new System.Net.Mail.SmtpClient();
}
}
public List<Customer> GetCustomers()
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["NwConn"].ConnectionString);
cmd.Connection = conn;
cmd.CommandText = "GetCustomerWithOrder";
conn.Open();
SqlDataReader Custreader = cmd.ExecuteReader();
string strCustId = null;
List<Customer> custlist = new List<Customer>();
Customer cust = null;
while (Custreader.Read())
{
cust = new Customer();
strCustId = Convert.ToString(Custreader["CustomerId"]);
cust.CustId = strCustId;
SqlCommand cmd1 = new SqlCommand();
cmd1.Connection = conn;
cmd1.CommandText = "GetOrdersByCustId";
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.Parameters.AddWithValue("@CustomerID", strCustId);
SqlDataReader Orderreader = cmd1.ExecuteReader();
if (Orderreader.HasRows)
{
List<Order> orderlist = new List<Order>();
while (Orderreader.Read())
{
Order orders = new Order();
orders.orderId = Convert.ToInt32(Orderreader["OrderID"]);
orderlist.Add(orders);
}
cust.Orders = orderlist;
}
custlist.Add(cust);
Orderreader.Close();
}
Custreader.Close();
conn.Close();
return custlist;
}
}
public class Order
{
public int orderId { get; set; }
}
public class Customer
{
public string CustId { get; set; }
public string CustName { get; set; }
public string EmailId { get; set; }
public List<Order> Orders { get; set; }
}
SQl procedures
create proc dbo.GetCustomerWithOrder
as
begin
select distinct(cust.CustomerId ) from customers cust
inner join orders ord
on ord.CustomerId =cust.CustomerId
end
--exec GetOrdersByCustId 'ALFKI'
Create proc dbo.GetOrdersByCustId
@CustomerID varchar(10)
as
begin
select * from orders
where CustomerId =@CustomerID
end
Go
also..make sure you use this type of connection string
<connectionStrings >
<add name="NwConn" connectionString="Data Source=DHIRENDRA-1 ;Initial Catalog=NorthWind; User Id=usder; Password=;MultipleActiveResultSets=True;" providerName="System.Data.SqlClient"/>
</connectionStrings>
Gopal_nivas, if this helps please login to Mark As Answer. | Alert Moderator