public void CreateTable(SpList list)
{
string query = string.Format("Use [{0}] \n ", SelectedDatabase);
query += string.Format("IF OBJECT_ID('[dbo].[{0}]', 'U') IS NULL \n ", list.Title);
query += "BEGIN \n ";
query += string.Format("CREATE TABLE [dbo].[{0}](", list.Title);
var cols = list.Columns.Where(x => x.IsChecked).ToList();
if (cols.Count <= 0)
{
return;
}
foreach (Column cl in cols)
{
query += string.Format("[{0}] {1} {2} ,\n ", cl.InternalName, cl.Type.ToSqlDataType(), cl.Required.IsNull());//row
}
query += ")";
query += " END \n ";
string constr = GetConnection;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
Logs.Instance.Info(string.Format("Create Table {0} ", list.Title));
}
protected string GetListPermsValue()
{
try
{
return(SpList.DoesUserHavePermissions(SPBasePermissions.ManagePermissions).ToString().ToLower());
}
catch (Exception ex)
{
Trace.TraceError(ex.ToString());
return(bool.FalseString);
}
}
public void InsertToHelpTable(SpList list)
{
StringBuilder query = new StringBuilder();
query.Append(string.Format("Use [{0}] \n ", SelectedDatabase));
//foreach (ListItem item in items)
//{
StringBuilder values = new StringBuilder();
var cols = list.Columns.Where(x => x.IsChecked).ToList();
foreach (Column column in cols)
{
query.AppendLine();
values.Clear();
values.Append(string.Format("INSERT INTO [dbo].[{0}]( ", HelpTableName));
values.Append("[TableName],[Title],[InternalName],[DisplayName] ");
values.Append(" ) VALUES (");
values.Append(string.Format(" N'{0}' ,", list.Title));
values.Append(string.Format(" N'{0}' ,", column.Title));
values.Append(string.Format(" N'{0}' ,", column.InternalName));
values.Append(string.Format(" N'{0}' )", column.DisplayName));
query.Append(values);
query.AppendLine();
}
string constr = GetConnection;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query.ToString()))
{
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
query.Clear();
query.Append(string.Format("Use {0} \n ", SelectedDatabase));
//}
}
public bool IsExistTable(SpList list)
{
string constr = GetConnection;
using (SqlConnection cn =
new SqlConnection(constr))
{
cn.Open();
using (var cmd = new SqlCommand(string.Format("Use [{0}] \n IF OBJECT_ID('[dbo].[{1}]', 'U') IS NULL select 1 else select 2", SelectedDatabase, list.Title), cn))
{
var result = cmd.ExecuteScalar();
var exist = int.Parse(result.ToString());
if (exist == 1)
{
return(false);
}
return(true);
}
}
}
public void InsertToTable(SpList list, List <ListItem> items)
{
StringBuilder query = new StringBuilder();
query.Append(string.Format("Use [{0}] \n ", SelectedDatabase));
foreach (ListItem item in items)
{
StringBuilder[] values = new StringBuilder[2];
values[0] = new StringBuilder();
values[1] = new StringBuilder();
query.Append(string.Format("INSERT INTO [dbo].[{0}](", list.Title));
var cols = list.Columns.Where(x => x.IsChecked).ToList();
foreach (Column column in cols)
{
values[0].Append(string.Format(" [{0}] ,", column.InternalName));
values[1].Append(string.Format(" {0} ,", item[column.InternalName].GetSharepointFieldValue(column.Type)));
}
query.Append(values[0].ToString().TrimEnd(',') + " ) VALUES (" + values[1].ToString().TrimEnd(',') + ")");
string constr = GetConnection;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query.ToString()))
{
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
query.Clear();
query.Append(string.Format("Use {0} \n ", SelectedDatabase));
}
}
public void DropTable(SpList list)
{
string query = string.Format("Use [{0}] \n ", SelectedDatabase);
query += string.Format("IF OBJECT_ID('[dbo].[{0}]', 'U') IS NOT NULL \n ", list.Title);
query += "BEGIN \n ";
query += string.Format("DROP TABLE [dbo].[{0}] \n ", list.Title);
query += "END \n ";
string constr = GetConnection;
using (SqlConnection con = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand(query))
{
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
Logs.Instance.Info(string.Format("Drop Table {0} ", list.Title));
}
Class1() {
myUT.SiteUrl = "https://yoursharepoint.com/yoursite";
myUT.Credentials = new NetworkCredential("USERNAME", "PASSWORD");
SpList listA = myUT.Get_SpList_By_Title("LISTNAME");
}
