Description:-
in this Example we explain that how to pass String Array as a Parameter in Sql Query Dynamically or pass String Array as a Parameter to be used in Sql Query using the “IN” command.
in this Example we explain that how to pass String Array as a Parameter in Sql Query Dynamically or pass String Array as a Parameter to be used in Sql Query using the “IN” command.
In my application that is a security is that if Admin login
then Admin is Exists in all type of ACL so Admin can show all the reports and
do some changes. But when other user is login then we have to first check the
List of the ACL in which user are Exists and then display only those reports
that are related to ACl.
For Example :- there are two ACL Account and IT/Software
then user xyz have exists in Account ACL then when xyz user is login then only
Account related reports are shown IT/software report are not show.
This is the real problem I have faced in my life is that I
want to pass the List of ACL to sql query Dynamicaly as a string array and I
want to fetch the type of reports that ACL name is same as the we pass as a
string array so user can display only those reports that type is equals to
those ACL contain.
Syntax:-
StringBuilder query = new StringBuilder("Select * from Employee where emp_name in (");
for (int i = 0; i < empNames.length; i++) {
if (i > 0) {
query.append(",");
}
query.append(empNames[i].toString());
}
query.append(")");
StringBuilder query = new StringBuilder("Select * from Employee where emp_name in (");
for (int i = 0; i < empNames.length; i++) {
if (i > 0) {
query.append(",");
}
query.append(empNames[i].toString());
}
query.append(")");
to show Example of How to Create a Paging in DataList click here Paging in DataList
to show Example of How to Export Gridview Data to PDF File Export Gridview Rows to PDF file
Fetch List of ACL in
which user EXISTS:-
string[] acllist = GetAclList(Session["userid"].ToString());
public string[]
GetAclList(string userid)
{
string[]
acllist = null;
string
query = "select distinct acl.Name from
userinfo ui inner join userroleinfo uri on ui.UserId = uri.UserId inner join
SecurityInfo si on uri.RoleId = si.RoleId inner join AclInfo acl on si.AclId =
acl.AclId where ui.UserId='" + Session["userid"].ToString()
+ "'";
SqlConnection
cn = new SqlConnection(ConfigurationManager.ConnectionStrings["Security"].ToString());
SqlCommand
cmd = new SqlCommand(query,cn);
cn.Open();
DataTable
dt = new DataTable();
SqlDataAdapter
sa = new SqlDataAdapter();
sa.SelectCommand = cmd;
cmd.ExecuteNonQuery();
sa.Fill(dt);
acllist = new
string[dt.Rows.Count];
for
(int i = 0; i < dt.Rows.Count; i++)
{
acllist[i] =
dt.Rows[i][0].ToString();
}
return
acllist;
}
Get Reports that User Have Permission:-
Report
ReportObj = new Report();
Reports =
ReportObj.GetAllReports("ReportID",
SortingOrder.ASC,acllist);
public DataTable
GetAllReports(string orderbyField, SortingOrder sortOrder, string[]
acllist)
{
try
{
if
(acllist.Count() != 0)
{
StringBuilder
query = new StringBuilder();
string
query1 = "select ReportID as
ID,Replace(Name,'\"','''')as
Name,Type,ACL,Convert(varchar,DateCreated,101)as
DateCreated,Convert(varchar,DateModified,101)as
DateModified,CreatedBy,ModifiedBy from ReportInfo Where ParentChartID is null
and ACL in(";
query.Append(query1);
for
(int i = 0; i < acllist.Count(); i++)
{
query.Append("'");
query.Append(acllist[i].ToString());
query.Append("'");
if (i != acllist.Count() - 1)
query.Append(",");
}
query.Append(")");
return
SqlClient.GetData(Configurations.CONNECTION_STRING,
query.ToString());
}
else
{
return
null;
}
}
catch
(Exception ex)
{
throw ex;
}
}
0 comments:
Post a Comment